Cohort analytics via Data Locker – The Enterprise Integration Desk

At a glance: Cohort analytics in Data Locker contains aggregated data of all elements in the advertising hierarchy. Reports contain data of users attributing in the previous 365 days. This report is available to raw data and Cohort API subscribers without the need for a Data Locker subscription.

Cohort analytics in Data Locker

In some cases, attribution raw data in ZendeskConnect is restricted due to media source data sharing policies or your privacy-preserving policies. The restriction includes all attribution-related fields like campaign and adset. Cohort reports provide you with aggregated data that can be used as an alternative to raw data.

Until November 7, 2021, empty fields were populated with N/A. Starting November 8, 2021, empty fields are populated with null. Please take this into consideration in your data loading procedures.

See Example report.

Enable Data Locker

The report is available to Data Locker, Cohort API, and raw data subscribers. To enable the report in Data Locker, complete one of the following procedures.

Enable Data Locker
Do you currently get data via Data Locker?	Procedure
Yes	To add the Cohort reports to Data Locker: In ZendeskConnect, go to Integration > Data Locker. Select all the reports in the Cohort analytics section. Click Save configuration. The reports will be available on the next day by 10:00 UTC.
No	To set up Data Locker: Complete the first-time Data Locker setup. Add the Cohort reports to Data Locker: In ZendeskConnect, go to Integration > Data Locker. Select all the reports in the Cohort analytics section. Click Save configuration. The reports will be available on the next day by 10:00 UTC.

You can load the data into your BI systems and use them as part of your campaign performance and optimization processes.

Metrics available

The metrics available are revenue, unique users performing an event and the number of event occurrences. To calculate cost-related metrics like ROI, and ROAS you need both revenue and cost metrics. Revenue metrics are in Cohort, and cost metrics are provided by Xpend Cost ETL.

Facts

Cohort analytics facts
Reports available	The following reports are available for download. User acquisition: attributed to a user acquisition media source including LTV occurring during re-engagement windows. Retargeting: attributed to a retargeting media source for events occurring: During a re-engagement window As a result of re-attributions Unified: display data under the last touch media source bringing the user according to ZendeskConnect double attribution rules. Meaning that LTV occurring during re-engagements displays under the retargeting media source and not under the UA media source.
Reporting period	Users converting during the previous 365 days. In other words, each day the reports include the user converting during the previous 365 days.
Report structure	The structure of each report is the same. See report structure.
Report delivery	See Enable Cohort reports in Data Locker.
Data freshness	Daily. Metrics are calculated using data avaialble at midnight UTC. In other words, at the end of the day. Reports are available in Data Locker by 10:00 UTC in the h=23 folder. It means that Monday's data is available on Tuesday at 10:00 UTC in the Monday h=23 folder.
Impact of partner retention policies	Consider that some partners implement a data retention policy. In this case, events occurring after the end of the retention period are attributed to organic. Example: SRN A has a data retention policy of 180 days. User events until day 180 are attributed to SRN A. Events that occur after 180 days are attributed to organic.

Report structure

The report is comprised of dimensions and metrics.

The format of the fields is as follows:

Dimensions: String. The maximum length of the string is dynamic and in most instances depends on how you populate the elements of the advertising hierarchy
Metrics: Number. Note! The selected_currency field format is string.

Dimensions

Field name

Description

app_id

media_source

conversion_type

Possible values: install, re-engagement, re-attribution

attributed_touch_type

Possible values: click, impressions, pre-install, unknown, tv, null

days_post_attribution

The number of days elapsed since the attribution event.
Tip! Use this to calculate retention and KPI days

event_date

The date a user performs a given event.
Format: yyyy-mm-dd
Example: The date a user performed a given in-app event. In the case of a conversion.
Note! If the event_name is af_conversion, then the event date and conversion date will be the same.

conversion_date

The date the conversion occurred
Format: yyyy-mm-dd
Example: The install date

event_name

Identifies the event. Some event names have a specific meaning while others relate to in-app events set by the advertiser in the app.


event_name	What did the user do
af_conversion	User converted. Use conversion_type to identify if it is an install, re-engagement, or re-attribution.
af_session	Opened the app
af_uninstall	Uninstalled the app
Advertiser defined in-app event	Performed an in-app event in the app

campaign

Campaign hierarchy

Consider: Campaign name changes not supported. Consequently, multiple names can be associated with a given campaign ID.

campaign_id

Campaign hierarchy

adset

Campaign hierarchy

adset_id

Campaign hierarchy

ad_id

Campaign hierarchy

channel

Campaign hierarchy.

[Updated October 27, 2021] At present, Facebook doesn't populate channel in data provided via the Google Install Referrer mechanism.

site_id

Campaign hierarchy

is_primary_ attribution

Use to identify and deduplicate retargeting data.

geo

The ISO country code derived from the user IP address.

agency

Agency transparency supported.
In the case of non-transparent agencies, there can be multiple rows containing the same campaign name if the advertiser and the agency both run campaigns with the same name. Don't worry. The rows aren't duplicated.

install_app_store

Android apps only: The Android store from where the app was downloaded. Populated by advertisers implementing multi-store Android attribution. If blank, it means Google Play Store.

Metrics
Field name	Description	Format
unique_users	Number of unique users performing the event	Number
revenue_usd	Amount of revenue in USD. For example, $100.56 is reflected as 100.56 Maximum of 2 places after the decimal point	Number
event_count	Number of event occurrences	Number
selected_currency	3-letter currency code (USD, EUR) set by you in the app settings. Format ISO-4217. This is the same currency used to display revenue in Cohort in the user interface.	String
revenue_selected_currency	Amount of revenue in the selected currency. For example, if the selected currency is EUR, then €1234.56 is reflected as 1234.56 Maximum of 2 places after the decimal point	Number

BI developer considerations

Scope of data in the report

The reports contain user attributions, installs, re-attributions, and re-engagements, and their related in-app events.

If you use the unified view in your data loading process, you must use logic to split the data between campaign types, which means, user attribution (installs) and retargeting (re-engagements). To do so, use the conversion_type and is_primary_attribution field. See Double attribution of retargeting events.

Field-level considerations

Use post-attribution days to enable easy calculation of retention metrics.
Calculating unique users using campaign name and campaign ID dimensions: If you can disregard campaign name granularity, you can sum unique count on campaign ID and the metrics are correct.
You can aggregate the data using the campaign hierarchy fields.
Revenue in USD is calculated using the exchange rate on the event day.
Ad revenue events are included where available but can update with a lag of up to 5 days.

General considerations

The data of all apps is provided in a single file. Use the App ID field to segregate data per app or set Data Locker to segregate by app.

Calculating the number of unique users per cohort

To calculate the number of unique users per cohort by conversion type, for example, the number of users installing the app you must filter the data so that conversion_type=install and event_name=af_conversion. Sum the event_count column. You can add dimensions by including addtional columns. To calculate the number of installs per day, group by conversion_date.

This is illustrated by the SQL statement and Excel visual that follows.

SQL statement


SELECT   app_id,
         conversion_date,
         media_source,
         event_name,
         conversion_type,
         Sum(event_count) AS total
FROM     'REPLACE_TABLE_NAME'
WHERE    app_id='REPLACE_APP_ID'
AND      conversion_type='install'
AND      event_name='af_conversion'
GROUP BY 1, 2, 3, 4, 5

Excel example

Additional information

Traits and limitations

Trait
Trait
Cost data	Not available. Use Cost ETL.
Campaign name changes	Not supported.
Data freshness	Daily UTC. Reports are in the Data Locker h=23 folder of the reporting day by 10:00 UTC of the next day.
Ad revenue	Available
Currency	USD and app-specific currency are both avaialble per row
Organic data	Available
Days post-conversion (install, re-attribution, re-engagement)	365 days.
Agency transparency	Supported Twitter and Facebook data is always transparent
App segregation	Supported
SKAN data	Not included. Meaning, the data provided by iOS postbacks.
Reinstalls	Not included.