Cohort analytics via Data Locker

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 advertisers who have raw data or Cohort API, or both as part of their subscription. It means that a Data Locker subscription isn't required for this report.

Cohort analytics in Data Locker

In some cases, attribution raw data in AppsFlyer 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. 

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

Do you currently get data via Data Locker?  Procedure

 

Yes 

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

  1. In AppsFlyer, from the side menu, select Report > Data Locker.
  2. Select all the reports in the Cohort analytics section. 
  3. Click Save configuration
    The reports will be available on the next day by 10:00 UTC. 
No

AppsFlyerAdmin_us-en.pngTo set up Data Locker:

  1. Complete the first-time Data Locker ( advertiser | partner ) setup.
  2. Add the Cohort reports to Data Locker:
    1. In AppsFlyer, from the side menu, select Report > Data Locker.
    2. Select all the reports in the Cohort analytics section. 
    3. 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 ROI360 Cost ETL.

Cohort analytics facts

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 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 schema of each report (the dimensions and metrics included) is fixed and can't be edited. See report structure.
Report delivery  See Enable Cohort reports in Data Locker.
Data freshness
  • Daily.
  • Metrics are calculated using data available at midnight UTC. In other words, at the end of the day. 
  • Reports are available in Data Locker in the date-time (dt) folder by 10:00 UTC. For example, Monday's data is available on Tuesday at 10:00 UTC in the Monday 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 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.

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

[Field available starting April 27, 2022] 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

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

Use cases

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

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

Additional information

Traits and limitations

Trait  
Cost data Not available. Use Cost ETL.
Campaign name changes Not supported. 
Data freshness Daily UTC. Reports are in the Data Locker date-time (dt) folder of the reporting day by 10:00 UTC of the next day. 
Ad revenue
  • Available
  • For af_ad_revenue events, the unique users metric isn't available for dates between October 5, 2022-February 16, 2023.
Currency  USD and app-specific currency are both available per row
Timezone
  • UTC
  • App-specific timezone is available with the beta version.
Organic data Available
Days post-conversion (install, re-attribution, re-engagement)
  • 365 days of post-install data is available.
  • 1,095 days is available with the beta version.
Agency transparency
  • Supported 
  • X Ads and Meta ads data is always transparent
App segregation Supported
SKAN data Not included. Meaning, the data provided by iOS postbacks.
Reinstalls Not included. 

See also