Advanced aggregated reports in Data Locker

Report facts

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 a string.
  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 ROI360 Cost ETL.

Dimensions

Metrics

Directory and filename structure

The path to the report consists of the following folder hierarchy:

With the following format: <bucket-name>/t=cohort_unified_versioned/dt=<yyyy-mm-dd>/version=<unix timestamp>/<parquet file number>

Report folder hierarchy

An example of the cohort versioned report folder hierarchy in the advertiser bucket:

bucket
|
└── t=cohort_unified_versioned
    |
    ├── dt=2024-05-05
    |   |
    |   └── version=1714890235
    |   |    |
    |   |    ├── part-00000-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |    |
    |   |    ├── part-00001-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |    │
    |   |    └── part-00002-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |
    |   |
    |   └── version=1714890286
    |        |
    |        ├── part-00000-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |        |
    |        ├── part-00001-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |        │
    |        └── part-00002-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |   |
    .   . 
    .   . 

Legend:

• dt: Date when the events included in the report occurred.
• t: report type.
• version: Unix timestamp when the version was created.

Report versions and data freshness

• Intraday. Reports are sent every few hours.
• Reports are for all the data currently available for the day. Meaning, on April 18, every report version contains all available data up to that point for April 18.
• Only ingest the latest available report version.

Report facts

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 a string.
  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 ROI360 Cost ETL.

Dimensions

Metrics

Directory and filename structure

The path to the report consists of the following folder hierarchy:

With the following format: <bucket-name>/t=cohort_unified_timezone_versioned/dt=<yyyy-mm-dd>/version=<unix timestamp>/<parquet file number>

Report folder hierarchy

An example of the cohort timezone versioned report folder hierarchy in the advertiser bucket:

bucket
|
└── t=cohort_unified_timezone_versioned
    |
    ├── dt=2024-05-05
    |   |
    |   └── version=1714890235
    |   |    |
    |   |    ├── part-00000-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |    |
    |   |    ├── part-00001-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |    │
    |   |    └── part-00002-4762858-d62a-446e-b499-dd05f2d5434d-c000.csv.gz
    |   |
    |   |
    |   └── version=1714890286
    |        |
    |        ├── part-00000-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |        |
    |        ├── part-00001-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |        │
    |        └── part-00002-1d295376-d024-4321-8d34-1fbb37cbb58d-c000.csv.gz
    |   |
    .   . 
    .   . 

Legend:

• dt: Date when the events included in the report occurred.
• t: report type.
• version: Unix timestamp when the version was created.

Report versions and data freshness

• Intraday. Reports are sent every few hours.
• Reports are for all the data currently available for the day. Meaning, on April 18, every report version contains all available data up to that point for April 18.
• Report use cases may be different depending on your geo and timezone. Learn more
• Only ingest the latest available report version.

Report use cases may be different depending on your geo and timezone. Use the following table to understand which geos match which timezones.

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.

You can 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.
• Calculate 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. 

General considerations

The data of all apps is configurable; it can provided in a single file or separate files per app.

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

1. Calculate 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'
    // If you're querying data from unified reports, edit the line below to: and conversion_type IN ('install', 'install_unified')
    and conversion_type = 'install'
    and app_id = YOUR_APP
group by 1,2

Excel example

2. Calculate 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
    // If you're querying data from unified reports, edit the line below to: and conversion_type IN ('install', 'install_unified')
    and conversion_type in ('re-engagement', 're-attribution')
    and app_id = YOUR_APP
group by 1

Excel example

3. Calculate 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_attribution=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_attribution = true
    and app_id = YOUR_APP
group by 1,2,3,4,5

Excel example

4. Calculate 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'
    // If you're querying data from unified reports, edit the line below to: and conversion_type IN ('install', 'install_unified')
    and conversion_type = 'install'
    and event_name = 'af_conversion'
    and app_id = YOUR_APP
group by 1,2,3,4,5

Excel example

5. Calculate 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. Calculate 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. Calculate 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
    // If you're querying data from unified reports, edit the line below to: and conversion_type IN ('install', 'install_unified')    
    and conversion_type = 'install'
    and app_id = 'YOUR_APP'
group by 1
order by 2 desc
limit 20

Excel example

8. Calculate purchases Day 7 after conversion 

In the following example, we:

• Calculate the cumulative unique users who perform af_purchase events 7 days following their conversion (in unified view).
• Calculate the conversion rate to the event, meaning the share of conversions who make a purchase in the 7 days following their conversion.
• Data is filtered so that conversion_type='install'.
• Data is grouped by app, conversion date, media source, campaign, and adset.

SQL statement

select
    app_id, conversion_date, media_source, campaign, adset,
    sum(if(event_name = 'af_conversion', event_count,0)) as unified_conversions,
    sum(if(event_name = 'af_purchase', first_inapp, 0)) as af_purchase_day_7_cumulative_unique_users,
    concat(
        cast(
            round(
                sum(if(event_name = 'af_purchase', first_inapp, 0)) /
                sum(if(event_name = 'af_conversion', event_count, 0)) * 100.0
            ,2)
        as varchar),
    '%') as af_purchase_day_7_cumulative_unique_users_conversion_rate
from YOUR_DATA_LOCKER_REPORT
where
    conversion_date between '2023-12-01' and '2023-12-31'
    and is_primary_attribution = true
    and days_post_attribution <= 7
    and app_id in (APP_ID1, APP_ID2, ...)
group by 1,2,3,4,5

Excel example

9. Calculate DAU

In the following example, we:

• Calculate the DAU for each day in a chosen time range grouped by media source, campaign, and ad.
• Data is filtered so that event_name = 'af_session'.
• Sum the unique users for 1 day at a time, where each date is its own dimension (column).

Note:

• You can't sum multiple days (for example to get WAU or MAU). 
• DAU is based on the activity of users who converted in the past 1,095 days. This differs from data that may be seen in the Activity dashboard and reports, that don't have this limit. See "Days post-conversion" in the traits and limitations section.

SQL statement

select
media_source,campaign_id, ad_id,
sum(case when event_date = '2024-08-06' then unique_users end) as dau_2024_08_06,
sum(case when event_date = '2024-08-07' then unique_users end) as dau_2024_08_07,
sum(case when event_date = '2024-08-08' then unique_users end) as dau_2024_08_08
from YOUR_DATA_LOCKER_REPORT
where event_date between '2024-08-06' and '2024-08-08'
and event_name = 'af_session'
and app_id = YOUR_APP
group by 1,2,3 

Excel example