Cohort analytics via 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. 

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 a string.

Dimensions

Metrics

Scope of data in the report

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

If you load unified, user acquisition, and retargeting reports separately or together into your BI. If you load them together and want to filter the views on your own: 

• For unified, use the is_primary_attribution=true or null field.
• For user acquisition, use conversion_type=Install.
• For retargeting, use conversion_type=re-engagement or re-attribution.

If you just use the unified view in your data loading process, you can 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=install, install-unified, re-engagement, or re-attribution. 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 3 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.

The following are examples of some popular, practical applications of Cohort data that BI developers can extract via Data Locker. Each example is illustrated by an SQL statement and sample Excel visual. 

1. Calculating retention 

In the following example, we:

• Calculate retention Day 1 and Day 7, as well as the total number of installs per campaign and ad.
• Sum the event count per conversion event by filtering event_nameto be af_conversion.
• Specifically analyze user acquisition campaigns by filtering the data so that conversion_type=install.

SQL statement

select
    campaign_id, ad_id,
    sum(if(event_name = 'af_conversion', event_count,0)) as installs,
    sum(if(event_name = 'af_session' and days_post_attribution = 1, unique_users,0)) / sum(if(event_name = 'af_conversion', event_count,0)) as retention_day1,
    sum(if(event_name = 'af_session' and days_post_attribution = 7, unique_users,0)) / sum(if(event_name = 'af_conversion', event_count,0)) as retention_day7
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-06-01' and '2023-06-08'
    and conversion_type = 'install'
    and app_id = YOUR_APP
group by 1,2

Excel example

2. Calculating ARPU of multiple in-app events

In the following example, we:

• Calculate ARPU of multiple in-app events per campaign.
• Specifically analyze retargeting campaigns by filtering the data so that conversion_type=re-engagement and conversion_type=re-attribution.
• Sum the event count per conversion event by filtering event_nameto be af_conversion.
• Sum the revenue of multiple events, in this case af_purchaseand af_coins.
• Set days_post_attribution to the minimum needed (in this case, 7) to minimize the data processing load.

SQL statement

select
    campaign_id,
    sum(if(days_post_attribution <= 1 and event_name in ('af_purchase', 'af_coins') , revenue_usd, 0)) / sum(if(event_name = 'af_conversion', event_count, 0)) as ARPU_day1,
    sum(if(days_post_attribution <= 3 and event_name in ('af_purchase', 'af_coins') , revenue_usd, 0)) / sum(if(event_name = 'af_conversion', event_count, 0)) as ARPU_day3,
    sum(if(days_post_attribution <= 7 and event_name in ('af_purchase', 'af_coins') , revenue_usd, 0)) / sum(if(event_name = 'af_conversion', event_count, 0)) as ARPU_day7
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-06-01' and '2023-06-08'
    and days_post_attribution <= 7
    and conversion_type in ('re-engagement', 're-attribution')
    and app_id = YOUR_APP
group by 1

Excel example

3. Calculating in-app event conversion rate for a specific cohort day

In the following example, we:

• Calculate the Day 0 in-app event conversion rate for multiple dimensions (in this case, conversion date, geo, campaign, ad, and site ID).
• Analyze unified data (both UA and retargeting campaigns) by filtering the data so that is_primary=true.
• Sum the event count per conversion event by filtering event_nameto be af_conversion.
• Set days_post_attribution to the minimum needed (in this case, 7) to minimize the data processing load.

SQL statement

select
    conversion_date, geo, campaign_id, ad_id, site_id,
    sum(if(days_post_attribution = 0 and event_name = 'af_complete_tutorial', unique_users, 0)) / sum(if(event_name = 'af_conversion', event_count, 0)) as day0_af_tutorial_conversion
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-06-01' and '2023-06-08'
    and is_primary = true
    and app_id = YOUR_APP
group by 1,2,3,4,5

Excel example

4. Calculating daily installs 

In the following example, we:

• Calculate the number of installs per app ID, conversion date, media source, event name, and conversion type.
• Filter the data to show UA installs (not retargeting), by setting conversion_typeto be install.
• Sum the installs by setting event_nameto be af_conversion.

SQL statement

select
    app_id,
    conversion_date,
    media_source,
    event_name,
    conversion_type,
    sum(events_count) as total
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-06-01' and '2023-06-08'
    and conversion_type = 'install'
    and event_name = 'af_conversion'
    and app_id = YOUR_APP
group by 1,2,3,4,5

Excel example

5. Calculating revenue from Facebook Ads 

In the following example, we:

• Calculate Day 3 revenue from Facebook by conversion date and app ID.
• Analyze Facebook data by filtering the data so that media_source='Facebook Ads'.
• Set days_post_attribution to the minimum needed (in this case, 3) to minimize the data processing load.

SQL statement

select
    conversion_date,
    app_id,
    media_source,
    sum(if(days_post_attribution <= 3, revenue_usd, 0)) as revenue_day3
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2022-06-01' and '2023-05-31'
    and days_post_attribution <= 3
    and media_source = 'Facebook Ads'
    and app_id in (APP_ID1, APP_ID2, ...)
group by 1,2,3

Excel example

6. Calculating ARPU per ASA keyword ID for up to 365 cohort days 

In the following example, we:

• Calculate ARPU from Apple Search Ads per keyword ID until cohort day 365.
• Analyze Apple Search Ads data by filtering the data so that media_source='Apple Search Ads'.
• Sum the event count per conversion event by filtering event_nameto be af_conversion.

SQL statement

select
    media_source,
    keyword_id,
    sum(if(days_post_attribution <= 365, revenue_usd,0)) / sum(if(event_name = 'af_conversion', event_count, 0)) as ARPU_day365
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2022-06-01' and '2023-05-31'
    and media_source = 'Apple Search Ads'
    and app_id = YOUR_APP
group by 1

Excel example

7. Calculating ARPU Days 7 by attribution time per geo 

The following example illustrates how to use KPIs by attribution time. In the example, we:

• Calculate ARPU Days 7 by attribution date per geo.
• Results are sorted by the number of conversions, with the top 20 geos displaying.
• Data is filtered so that conversion_type='install'.
• The first column shows geo. The second column shows the total conversions. The columns thereafter show the revenue days 7 for every day specified to be a row in the query.

SQL statement

select
    geo,
    sum(if(event_name = 'af_conversion', event_count, 0)) total_conversions,
    sum(if(cohort_day = '2023-07-11' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-11' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_11,
    sum(if(cohort_day = '2023-07-12' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-12' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_12,
    sum(if(cohort_day = '2023-07-13' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-13' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_13,
    sum(if(cohort_day = '2023-07-14' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-14' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_14,
    sum(if(cohort_day = '2023-07-15' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-15' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_15,
    sum(if(cohort_day = '2023-07-16' and days_post_attribution <= 7, revenue_usd, 0)) / sum(if(cohort_day = '2023-07-16' and event_name = 'af_conversion', event_count, 0)) as ARPU_day7_2023_07_16
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-07-11' and '2023-07-16'
    and days_post_attribution <= 7
    and conversion_type = 'install'
    and app_id = 'YOUR_APP'
group by 1
order by 2 desc
limit 20

Excel example