Advanced aggregated reports in Data Locker

Premium

At a glance: Advanced aggregated reports in Data Locker contain aggregated data with optimal data freshness, accuracy, granularity, and unlimited volume.

Advanced aggregated reports in Data Locker

Advanced aggregated reports in Data Locker:

  • Provide an efficient and privacy-preserving way to build your internal BI systems based on aggregated data: Attribution, events, and revenue data, with all possible dimensions and metrics.
  • You can load the data into your BI systems and use them as part of your campaign performance and optimization processes.
  • Have the best freshness to accuracy: Data arrives multiple times per day and gets updated with each subsequent version of a report with all available data for that day.
  • Helps you augment raw data that may be limited and restricted due to media source data-sharing policies or your privacy-preserving policies. The restrictions impact attribution-related fields like campaign and adset.

Setup

To get advanced aggregated reports, complete one of the following procedures. 

Do you currently get data via Data Locker?  Procedure

 

Yes 

AppsFlyerAdmin_us-en.pngTo add the reports to Data Locker:

  1. In AppsFlyer, from the side menu, select Export > Data Locker.
  2. Select all the reports you want to get. 
  3. Click Save configuration
    The reports will be available the next day. 
No

AppsFlyerAdmin_us-en.pngTo set up Data Locker:

  1. Complete the first-time Data Locker (advertiser | partner) setup.
  2. Add the reports to Data Locker:
    1. In AppsFlyer, from the side menu, select Export > Data Locker.
    2. Select all the reports you want to get. 
    3. Click Save configuration
      The reports will be available the next day.

Reports available

Advanced aggregated (cohort versioned) reports

Report facts

Overview

The cohort versioned report contains all your aggregated data, cohorted, with all campaign dimension granularity. The report is updated every few hours to maximize data freshness and accuracy.

Download sample report

Reports available

The following reports are available for download. The report types are described in more detail in the Cohort dashboard.

  • 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 AppsFlyer 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 who converted during the previous 1,095 days. In other words, each day, the reports include events of users who converted during the previous 1,095 days. 
Report structure The schema of the report (the dimensions and metrics included) is fixed and can't be edited. 
Data freshness
  • Intraday. Reports are sent every few hours.
  • Reports are for that day's data. Meaning, on April 18, every report version contains all current data for April 18. Learn more
Timezone UTC
Directory and filename structure Learn more
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 considered organic in the Cohort reports.

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 considered 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 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

Field name 

Description

app_id

 

media_source

 

conversion_type

Possible values: install, install-unified (representing installs in the unified report), 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 conversion date (not the specific conversion timestamp).
  • 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
cohort_uninstalls 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

Campaign hierarchy

ad_id

Campaign hierarchy

channel

Campaign hierarchy.

[Updated October 27, 2021] At present, Meta ads 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. 

keywords

Word(s) used in the user's online search. As reported by the ad network.

keyword_id

Keyword ID returned by the ad network.

Metrics

Field name

Description Format

unique_users

Number of on-day 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

first_inapp

  • Number of users performing the event for the first time following their conversion.
  • Summing the first_inapp metrics together gives you the cumulative unique user count for the event.
Number

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.
Version Report includes data that AppsFlyer received by (Time in UTC) Use case Time report is available (Time in UTC)
1 Day 0 at 4 AM Partial data for Day 0 Day 0 at 8 AM
2 Day 0 at 8 AM Partial data for Day 0 Day 0 at 1 PM
3 Day 0 at 12 PM Partial data for Day 0 Day 0 at 6 PM
4 Day 0 at 4 PM Partial data for Day 0 Day 0 at 9 PM
5 Day 0 at 8 PM Partial data for Day 0 Day 0 at 11:59 PM
6 Day 0 at 11:59 PM Complete conversion and in-app event data for Day 0 (excluding S2S events that AppsFlyer receives between Day 0 at 11:59 PM and Day 1 at 2 AM) Day 1 at 4 AM
7 Day 1 at 3 AM Complete conversion and in-app event data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 8 AM
8 Day 1 at 11 AM Complete conversion and in-app event data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 6 PM
9 Day 1 at 5 PM Complete conversion and in-app event data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 11:59 PM
10 Day 8 at 7 AM Complete conversion and in-app event data for Day 0, and complete ad revenue data sent via S2S, accounting for any potential issues that may have occurred on the ad revenue network side Day 8 at 1 PM

Advanced aggregated (cohort timezone versioned) reports

Report facts

Overview

The cohort timezone versioned report contains all your aggregated data, cohorted, with all campaign dimension granularity, according to localized timezones. The report is updated every few hours to maximize data freshness and accuracy.

Download sample report

Reports available

The following reports are available for download. The reports types are described in more detail in Cohort dashboard.

  • 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 AppsFlyer 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 who converted during the previous 1,095 days. In other words, each day, the reports include events of users who converted during the previous 1,095 days.

Note: If a day hasn't started yet in your local timezone, timezone-versioned reports will arrive without data.

Report structure The schema of the report (the dimensions and metrics included) is fixed and can't be edited. 
Data freshness
  • Intraday. Reports are sent every few hours.
  • Reports are for that day's data. Meaning, on April 18, every report version contains all current data for April 18. Learn more
Timezone Any timezone except UTC. Meaning, the reports leave out data for any apps with UTC timezone settings in AppsFlyer.
Directory and filename structure Learn more
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 disregarded in the Cohort reports.

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 disregarded.

Note: The events display in the Overview dashboard as 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 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

Field name 

Description

app_id

 

media_source

 

conversion_type

Possible values: install, install-unified (representing installs in the unified report), 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 conversion date (not the specific conversion timestamp).
  • 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
cohort_uninstalls Uninstalled the app 
Advertiser defined in-app event Performed an in-app event in the app

event_timezone

The timezone for:

  • days_post_atribution
  • event_date
  • conversion_date

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

Campaign hierarchy

ad_id

Campaign hierarchy

channel

Campaign hierarchy.

[Updated October 27, 2021] At present, Meta ads 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. 

keywords

Word(s) used in the user's online search. As reported by the ad network.

keyword_id

Keyword ID returned by the ad network.

Metrics

Field name

Description Format

unique_users

Number of on-day 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

first_inapp

  • Number of users performing the event for the first time following their conversion.
  • Summing the first_inapp metrics together gives you the cumulative unique user count for the event.
Number

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.
Version Report includes data that AppsFlyer received by (Time in UTC) Use case Time report is available (Time in UTC)
1 Day -1 at 4 AM Eastern geos - partial data for Day 0 Day -1 at 8 AM
2 Day -1 at 8 AM Eastern geos - partial data for Day 0 Day -1 at 1 PM
3 Day -1 at 12 PM Eastern geos - partial data for Day 0 Day -1 at 6 PM
4 Day -1 at 4 PM Eastern geos - partial data for Day 0 Day -1 at 9 PM
5 Day -1 at 8 PM Eastern geos - partial data for Day 0 Day -1 at 11:59 PM
6 Day -1 at 11:59 PM Eastern and Central geos - partial data for Day 0 Day 0 at 4 AM
7 Day 0 at 4 AM All geos - partial data for Day 0 Day 0 at 8 AM
8 Day 0 at 8 AM All geos - partial data for Day 0 Day 0 at 1 PM
9 Day 0 at 12 PM All geos - partial data for Day 0 Day 0 at 6 PM
10 Day 0 at 4 PM All geos - partial data for Day 0 Day 0 at 9 PM
11 Day 0 at 8 PM
  • Eastern geos - Complete conversion and in-app event data for Day 0
  • Central and Western geos - partial data for Day 0
Day 0 at 11:59 PM
12 Day 0 at 11:59 PM

Central and Western geos - partial data for Day 0

Day 1 at 4 AM
13 Day 1 at 4 AM
  • Central geos - Complete conversion and in-app event data for Day 0
  • Western geos - partial data for Day 0
  • Any currently available ad revenue data sent via S2S
Day 1 at 8 AM
14 Day 1 at 8 AM Western geos - partial data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 1 PM
15 Day 1 at 12 PM Western geos - partial data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 6 PM
16 Day 1 at 4 PM Western geos - partial data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 9 PM
17 Day 1 at 6 PM Western geos - partial data for Day 0, and any currently available ad revenue data sent via S2S Day 1 at 11:59 PM
18 Day 1 at 8 PM Western geos - Complete conversion, in-app event data for Day 0, and complete ad revenue data sent via S2S Day 1 at 11:59 PM
19 Day 1 at 11:59 PM Complete conversion and in-app event data for Day 0, and complete ad revenue data sent via S2S, accounting for any potential issues that may have occurred on the ad revenue network side Day 2 at 4 AM
20 Day 8 at 00:00 AM Complete conversion and in-app event data for Day 0, and complete ad revenue data sent via S2S, accounting for any potential issues that may have occurred on the ad revenue network side Day 8 at 6 AM

Additional information

Timezone geos

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

Geo Timezone
Eastern UTC+12 - UTC+3
Central UTC+2.5 - UTC-3
Western UTC-3.5 - UTC-12

BI developer considerations

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.
  • 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. 

General considerations

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

Use cases

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

Campaign ID Ad ID Installs Retention Day 1 Retention Day 7
12345678 987654 100 30% 10%
98765432 123456 200 25% 15%
07315466 613770 300 20% 12%

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

Campaign ID Conversion type

ARPU

Day 1

ARPU

Day 3

ARPU

Day 7

12345678 re-engagement 6.23 5.11 2.34
98765432 re-engagement 3.57 1.34 4.86
07315466 re-attribution 7.41 6.79 5.29

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

Conversion date Geo

Campaign ID

Ad ID

Site ID

Day 0 af_complete_tutorial

2022-11-07 US 12345678 123456 site_123 45%
2022-11-05 UK 98765432 null site_654 70%
2022-10-31 KR 07315466 null null 63%

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

App ID Conversion date

Media source

Event name

Total

id123456789 2022-11-07 adnet1_int af_conversion 105
id123456789 2022-11-05 adnet2_int af_conversion 216
id123456789 2022-10-31 adnet3_int af_conversion 327

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

Conversion date App ID

Media source

Revenue

Day 3

2022-11-07 id123456789 adnet1_int 400.45
2022-11-05 id123456789 adnet2_int 99.23
2022-10-31 id123456789 adnet3_int 13.34

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

Media source

Keyword ID

ARPU

Day 365

adnet1_int 123456 57,019.93
adnet2_int 987654 64,867.84
adnet3_int 666854 48,160.02

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

Geo

Total conversions

ARPU Day 7 for 2023-07-11

ARPU Day 7 for 2023-07-12

ARPU Day 7 for 2023-07-13

ARPU Day 7 for 2023-07-14

ARPU Day 7 for 2023-07-15

ARPU Day 7 for 2023-07-16

South Korea 120,660 $7,798.89 $6,997.37 $8,258.95 $9,050.21 $10,018.04 $13,765.73
Canada 35,099 $64,867.84 $7,050.19 $5,656.33 $9,553.75 $8,632.41 $11,308.06
Chile 26,750 $48,160.02 $21,249.55 $22,584.57 $24,033.07 $31,118.91 $41,145.22

8. Calculating 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 = True
    and days_post_attribution <= 7
    and app_id in (APP_ID1, APP_ID2, ...)
group by 1,2,3,4,5

Excel example

App ID

Conversion date

Media source

Campaign

Adset

Unified conversions

D7 af_purchase cumulative

D7 af_purchase conversion rate

id123456789 2024-03-05 adnet1_int campaign_1 adset_1 100 20 20%
id123456789 2024-03-07 adnet2_int campaign_2 adset_2 200 10 5%
id123456789 2024-03-31 adnet3_int campaign_3 adset_3 150 15 10%

Traits and limitations

Trait  
Cost data Not available. Use Cost ETL.
Campaign name changes Not supported. Use campaign ID for grouping and filtering if campaign names were changed.
Data freshness Intraday
Ad revenue

Available

Currency  USD and app-specific currency are both available per row
Timezone

App-specific timezone is available with the timezone versioned report.

Organic data Available
Days post-conversion (install, re-attribution, re-engagement) data is available

1,095 days is available.

Agency transparency
  • Supported 
  • X Ads and Meta ads data is always transparent
App segregation Supported
SKAN data Not included. Meaning, the data is provided by iOS postbacks.
Uninstalls data Uninstalls are processed daily. Thus, they only appear in reports that contain complete data for a day (not partial data).
Reinstalls
  • If post-reinstall events are considered unattributed organic:
    • They are included only as of May 26, 2024. Learn more
    • The install date assigned to unattributed organic events is the date of the first install, with the exception of revenue events. Learn more
  • If they're attributed to the first install, they are always included. Learn more

See also