Data Extracts

Overview

The cX::ad Data Extract feature allows data to be exported from the system to be used for analytical reporting or importing into external system for use in reporting, analysis or invoicing. There are currently a number of extracts that can be run on demand, and one that is automatically generated each night. Each extract returns a specific view of the system data.

Data Extracts can also be managed via the Data Extract REST API.

Usage

The Data Extracts feature is available from the Management tab under the Finance section.

Creating Extracts

A extract request is created from the user interface (outlined below), from the REST API or by the extract scheduler and these requests will appear in the Data Extracts list as "Requested". A separate cX::ad process called the Task Server detects the requests and will generate the extract. The Task Server processes the extract requests offline, meaning the UI will remain responsive and the user may continue with other tasks while waiting for the extract to be completed. During the generation process, the Task Server notifies cX::ad of the extracts status to indicate processing progress and finally to indicate the extract generation is complete.

When the extract has been generated successfully the status shown in the Data Extracts list will change to Complete. The extract can now be downloaded or deleted.

If an extracts fails to generate then it will be displayed with a Failed status and can be deleted. Should an extract continue to fail then please contact support so it can be investigated.

Account Totals, Account Transaction, Advertiser Creative Performance, Advertiser Campaign Performance, Shared Product, Inventory Distribution and Operational Finance Extracts

These extracts are created by entering the extract parameters in the Create Extract section of the Data Extract page and clicking Generate. 

• Extract Type - The type of extract to generate.

• Start Date - The date the extract should start reporting from. The time component is 0:00 AM. 

• End Date - The date the extract should end reporting at. The time component is 11:59:59:999 PM.

Account Low Budget Extract

This system extract is automatically generated each day by the extract scheduler.

Downloading Extracts

Extracts with a Completed status may be downloaded. The extract will be downloaded as a zip file with the name of <start date>-<end date>.<extract type>.zip. The extracts are compressed in the zip format as they can be large depending on the type and the date range.

Inside the zip file is the extracts text data file.

These extracts all output data in CSV format with the following properties:

• Tab separated.

• Double quote delimited.

• Unicode 16 LE encoded.

• Compatible with Windows and Mac Excel as well as Open Office and Libre Office Calc.

Consuming Extracts

CSV Extracts

There are a number of ways the extracts and their data may be used.

• Can be filtered and sorted directly in Excel to find or highlight certain types of data.

• Can be used in Excel as the data source for pivot tables for analytical reporting.

• Can be used with Excel and Word to perform mail merge to generate custom reports.

• Can be imported into external systems via simple csv imports.

Data Visibility in Extracts

Data Extracts will show data for the current users home container and all home containers under this. As System Administrators and Business Owner users are the only users with permissions to access the Data Extracts and these users have the top level broker container as their home container, all data will be included in extracts generated.

To limit the data to another users home container, the Temporary Folder Access feature can be used. Simply gain temporary access to the selected users home container and generate extracts as normal (the Data Extract link will be visible under Management as user permissions are retained). The extract system will use the users home container to fetch data from rather than the top level broker container.

Types of Extracts

Account Balances Extract

The Account Balances Extract provides the accounts balance at the extracts start date and the end date, for example the extract can be run monthly to show all account balances at the start and the end of the month.

Account Low Budget Extract

The Account Low Budget extract shows the accounts that we believe will run out of money within the next 10 days. This extract shows the current and minimum balance for those accounts, as well as their average daily spend, and how many days they have left before they run out of budget. Note that this is based on their spend for the last 10 days, and doesn't take in to account campaigns being stopped, or new campaigns coming on line. As such, these numbers are an estimate only, not a guarantee for when an account will run out of budget.

This data can be used to notify accounts that they are running low on budget, and that their ads may stop displaying if they don't top up their account.

Account Totals Extract

The Account Totals Extract provides an overview of account activity over the selected date range. Account details are provided along with financial changes that occurred in the period and the total clicks and impressions recorded for the account over the period.

This data can be used in Excel to provide analytical reporting on an account level or to be imported into an external system to be used for invoicing.

Revenue and Account Balances

The revenue total value is the total amount charged to an account during the extract period by the system. All contract and click charges are included in this value. The revenue value does not reflect any manual credit or debit adjustments or payments (this data is shown in other columns).

The total amount by which an account balance has changed during an extract period can be determined by adding the revenue, adjustments and payment columns together.

Different product types cause charges to be applied to accounts at different times:

• CPC is charged on a per click basis and this charge is applied to the account immediately.

• CPM is charged at the end of the contract period.

• Tenancy is charged at the time of the contract purchase.

Only charges that occur during the extract period are reflected in the revenue value. For example, a CPM contract that runs for May and June will not have any cost shown in revenue for a monthly May extract and the June extract will contain the total costs for May and June.

See Understanding Budgeting and Statistics for additional details.

Traffic Cost

The traffic cost value may differ from the revenue value. It reflects a total running cost calculated from the impression and click events processed by the system at the time they occur.

The different product types have different costs associated:

• CPC records the cost of the click was worth at the time.

• CPM records the cost per impression at the time.

• Tenancy is not recorded as there are no per impression or click costs associated. The cost is only reflected in the contract purchase price and hence tenancy products will not affect the traffic cost value.

Traffic cost is calculated in the same way for the advertiser and publisher performance reports, meaning its value will be the same when comparing the two reports for the same period. Any contract that involves a setup fee will not have this fee included in the Traffic Cost or the performance reports cost figures.

Unlike the revenue value which reflects the time and amount an account was charged or credited, the Traffic Cost reflects the cost at the time an impression or click event is processed by the Cxense Advertising system. Using the example above for revenue, this means that Traffic Cost will reflect both May and June's cost.

See Understanding Budgeting and Statistics for additional details.

Advertiser Overage and Revenue Totals

Overage is not included in the revenue totals for advertiser accounts and overage does not effect publisher accounts. Hence there may be differences in this extract between the sum of the advertiser revenue and the sum of the publisher revenue - the difference is overage.

∑ Advertiser Revenue + ∑ Overage = ∑ Publisher Revenue

See Budget Overage for more information.

Account Transactions Extract

The Account Transactions Extract lists all transactions for all accounts that occurred within the extract period. This data can used to monitor payments and adjustments or used in conjunction with the Account Totals Extract to provide data for a invoicing system.

Advertiser Campaign Performance Extract

The Advertiser Campaign Performance Extract shows the same information as the system advertiser performance report at the campaign level on a per day basis.  It will show clicks, impressions and the other statistics defined below, grouped by campaign across your entire ad network from the advertiser perspective.

Advertiser Creative Performance Extract

The Advertiser Creative Performance Extract shows the same information as the system advertiser performance report at the creative level on a per day basis.  It will show clicks, impressions and the other statistics defined below, grouped by creative across your entire ad network from the advertiser perspective.

Shared Product Extract

The Shared Product Extract reports exclusively on Shared Products. Only traffic that involves the Shared Product, Campaigns from the Advertiser who shares the product, and Ad Spaces from the Publisher who accepts the share are reported on.

Inventory Distribution Extract

The Inventory Distribution Extract provides a detailed view of performance over a date range. Data is broken down per day with clicks, impressions and costs shown for each Ad and Creative along with details on the related Product, Ad Space, Campaign and Advertiser/Publisher folders.

Operational Finance Extract

The Operational Finance Extract provides an alternate view of revenue flow. It breaks down data per day by Advertiser Account and Ad Space and other Publisher information.

Cost information

This extract only shows cost data for CPC and CPM products.

General Notes

Email Notification

The system will alert the user who requested an extract via email when the extract generation completes successfully or if it fails.

Folder names

Many extracts include folder columns. These columns typically show what folder a campaign, ad space or account was created under and in many of these cases, the folder is optional. For documents created under a folder, the extract column will show the folders name, and for documents not under a folder, the system labels of container:advertising or container:publishing will be used.

Time Zones, Currency and Locale

Extracts are generated using the time zone, currency and locale of the current user as defined in their User Preferences.

All date and time values as well as the start and end dates for the extract are converted into the users time zone.

All currency amounts are converted into the users currency using the exchange rates defined by the broker.

The user locale is used to determine the extracts output in regards to number formatting, translations etc.

Positive and Negative Currencies

Currency values displayed in extracts are from the perspective of the account holder. Positive currency amounts are credits to the account where as negative values are debits to the account.

Transaction Codes

A number of the extracts make use of financial transactions. Below is the list of the transaction types supported in cX::ad.

Tax

cX::ad only considers tax for payments made by advertisers. The tax withheld for payments is determined by the tax rate set in the advertiser account at the time. Some extracts show tax rates for accounts and this value reflects the current tax rate. Others show the tax rate for transactions and this rate is the rate used at the time of the transaction.

Id Fields

Many extracts include Id fields such as AccountId, PublisherId etc. These Id fields are the cX::ad internal identifiers and are included in the extracts to assist with REST API integration.

Permissions

The Data Extract feature is only available to users with the System Admin or Business Owner permission assigned.

Last updated: December 28, 2021