[Closed beta] Single Source of Truth (SSOT) Data Locker report for marketers

To enable Single Source of Truth or SSOT reports, complete one of the following procedures.

AppsFlyer models data that basic SKAdNetwork reporting cannot provide.

• Null Conversion Values (CV): In SKAN, Apple may replace actual data with a "Null" value to maintain user privacy. To ensure no data is omitted, these null values are modeled in SSOT. Learn more.
• Longer Post-Install Metrics: SKAN's data granularity for post-install periods beyond the first two days is limited, which may lead to inaccuracies. To maintain high data quality, we model revenue and unique in-app event users for days_post_attribution greater than 2. Learn more.

Geo Data: SKAN limits data granularity, often making geo data unavailable. In such cases, we model the data to enable efficient performance analysis. Learn more.

When analyzing recent install days, metrics for extended post-install periods (e.g., 7 days) might not be fully matured. In such cases, the metrics will still be available but will reflect partial data.

Some rules of thumb:

• The report shows any available data as soon as it is available.
• After 5 days, metrics for two days post-install are completed*.
• After 7 days, metrics for seven days post-install are completed. Up to 15 days post-install, the model might refine this value.

Reasons for Partial Data:

• Data maturity: When the time since the install date is shorter than the days being analyzed, the data is still accumulating and not yet complete.
• SKAN delays: SKAN data is received with a random delay. For example, post-install activity for the first two days can be received up to 96 hours (4 days) after installation.
• Modeled data timing: SKAN data for periods greater than 2 days post-install is modeled. Our models generate the first estimated value after 7 days. During this period (days 2 to 7), data from AppsFlyer's classic attribution methods is available, while SKAN modeled data isn’t.

Examples (assuming an install date of January 1st):

• On January 2nd: SKAN data has yet to be received. Data for 2 days post-install will equal data under 7 days post-install, and both are partial, consisting of data from AppsFlyer's classic attribution methods.
• On January 3rd: SKAN data is partially received. Data for 2 days post-install will still be partial, now partially containing SKAN data. Data for 7 days post-install will not include any modeled SKAN data yet (the first modeled value is available after 7 days from installation). Therefore, between 2 to 7 days post-install, we might see a lower value for 7 days post-install relative to 2 days post-install.
• On January 6th: SKAN data for the first two days post-install was fully received, including postbacks with the maximum possible delay. Metrics for two days post-installs are completed. At this point, metrics for 7 days post-installs aren’t completed yet.
• On January 8th: SKAN modeled data for 7 days post-installs is completed. Our model will keep refining the value up to January 16th. 

• The primary folders are organized by the report type. Possible values:
  • ssot_unified
  • ssot_retargeting
  • ssot_user_acquisition
• Within these folders, sub-folders are organized by the install date.

Versioning 

• Each install date folder contains multiple versions, created daily.
• Each version reflects the updated cumulative data for that install date.
• Reports will include all available data for the install date. For example, on April 18, the latest version for each install date contains all data up to that point for April 18.
• Tip: Always process the latest available report version to ensure accuracy.

Each install date is limited to up to 15 versions. After 15 days, all metrics are fully fixed and completed. Learn more.

The path to the report consists of the following format:

<bucket-name>/t=<ssot_unified OR ssot_retargeting OR ssot_user_acquisition>/install_date=<yyyy-mm-dd>/version=<unix timestamp>/<parquet file number>

Example folder hierarchy in advertiser bucket:

bucket
|
└── t=ssot_unified
    |
    ├── install_date=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:

• t: Report topic (type).
• install_date: The installation date of the app. It means that post-install activity will appear based on the install date when the user downloaded the app, not the day the user action occurred.
• version: Unix timestamp indicating when the version was created.

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

• Unified reports: Use is_primary_attribution=true or null field.
• User acquisition reports: Use conversion_type=Install.
• Retargeting reports: Use conversion_type=re-engagement or re-attribution.
• Unified View: If you use the unified view in your data loading process, you can split the data between campaign types:

• Use conversion_type=install, re-engagement, or re-attribution (re-downloads in SKAN).
• Refer to "Double attribution of retargeting events" for more details

• For attributions (installs, re-engagements, etc.), the value is 0.
• For revenue and in-app events, the data is categorized into predefined cohorts, currently limited to “2” days and “7” days post-install.

To accurately analyze in-app events and revenue, you should apply a filter for the days_post_attribution dimension. See the example query below.

An example of a common mistake is to apply a simplified logic that sums the revenue column for a specific set of dimensions. This approach results in incorrect numbers because some revenue data is counted twice (once for days_post_install=2 and again for days_post_install=7). This example is also relevant when analyzing unique users for specific in-app events.

Tips considerations:

• Last version: Always query the latest version available for each install date to ensure you are working with the most up-to-date and accurate data for your analysis.
• Revenue calculation: Revenue in USD is calculated using the exchange rate on the event day.
• Analyze attributions: Records where event_name=af_conversion reflect attributions (installs, re-attributions (re-downloads in SKAN), and re-engagements). The count of attributions appears under the unique users metric, and the revenue metrics for these fields will be empty. The value under days_post_attribution will be 0.
• Dashboard comparison: When comparing installs to the dashboard, sum both re-download and install conversion types, as the dashboard considers both under the install column.
• Analyze in-app events: Records where event_name does not equal ‘af_conversion’ reflect in-app events. The count of unique users performing the event appears under the unique users metric, while the revenue generated from these events appears under the revenue metrics.
• Conversion type: Differentiate user acquisition and retargeting conversions using this dimension. Note that an additional break for SKAN data is available (installs vs. re-downloads). When comparing data to the SSOT dashboard, both types are considered as installs.
• Ad Revenue Events: These are included where available.
• Data Segregation: All app data is provided in a single file. Use the App ID field to segregate data per app, or set Data Locker to segregate by app.
• Pre-attribution data such as cost, clicks, and impressions, should be taken from the cost ETL report.

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 a sample Excel visual.

SELECT install_date, SUM(unique_users) AS total_attributions
FROM ssot_unified
WHERE event_name = 'af_conversion'
    AND af_c_id = '4475903638579'  -- Change to your specific campaign
    AND app_id = 'YOUR_APP'
GROUP BY install_date;

SELECT install_date, conversion_type, SUM(unique_users) AS total_conversions
FROM ssot_unified
WHERE event_name = 'af_conversion'
    AND af_c_id = '4475903638579'  -- Change to your specific campaign
    AND conversion_type IN ('install', 're-download')  
-- The dashboard sums both under the install field
    AND app_id = 'YOUR_APP'
GROUP BY install_date, conversion_type;

SELECT install_date, conversion_type, geo, SUM(unique_users) AS total_conversions
FROM ssot_unified
WHERE event_name = 'af_conversion'
    AND conversion_type IN ('re-attribution', 're-engagement')  
-- Both types are relevant for re-targeting
    AND app_id = 'YOUR_APP'
GROUP BY install_date, conversion_type, geo;

SELECT install_date, attribution_method, media_source, SUM(unique_users) 
AS total_installs
FROM ssot_unified
WHERE event_name = 'af_conversion'
    AND conversion_type = 'install'
    AND app_id = 'YOUR_APP'
GROUP BY install_date, attribution_method, media_source;

SELECT install_date,
       af_adset,
       SUM(revenue_usd) AS total_revenue_day_7
FROM ssot_unified
WHERE media_source = 'facebook'
    AND days_post_attribution = 7  -- Choose a specific post-install period (cohort)
    AND app_id = 'YOUR_APP'
GROUP BY install_date, af_adset;

SELECT install_date,
       SUM(IF(days_post_attribution = 2, revenue_usd, 0)) AS total_revenue_day2,
       SUM(IF(days_post_attribution = 7, revenue_usd, 0)) AS total_revenue_day7
FROM ssot_unified
WHERE app_id = 'YOUR_APP'
GROUP BY install_date;

SELECT install_date, media_source, SUM(revenue_usd) AS total_purchase_revenue
FROM ssot_unified
WHERE event_name = 'af_purchase'  -- Change to your specific event
    AND days_post_attribution = 7  -- Choose a specific post-install period (cohort)
    AND app_id = 'YOUR_APP'
GROUP BY install_date, media_source;

SELECT install_date,
  SUM(IF(days_post_attribution = 2 AND 
event_name = 'af_complete_tutorial', unique_users, 0)) 
  / SUM(IF(event_name = 'af_conversion', unique_users, 0)) 
AS conversion_rate_day2_af_tutorial_conversion
FROM ssot_unified
WHERE conversion_type = 'install'  
-- Choose the conversion types for the sample group you want to measure
    AND app_id = 'YOUR_APP'
GROUP BY install_date;

SELECT install_date,
       af_c_id,
       SUM(IF(days_post_attribution = 2, revenue_usd, 0)) 
       / SUM(IF(event_name = 'af_conversion', unique_users, 0)) AS ARPU_day2,
       SUM(IF(days_post_attribution = 7, revenue_usd, 0)) 
       / SUM(IF(event_name = 'af_conversion', unique_users, 0)) AS ARPU_day7
FROM ssot_unified
WHERE conversion_type = 'install'  
       -- Choose the conversion types for the sample group you want to measure
    AND app_id = 'YOUR_APP'
GROUP BY install_date, af_c_id;

SELECT install_date, media_source, SUM(skan_duplicates)
FROM ssot_unified
WHERE app_id = 'YOUR_APP'
    AND event_name = 'af_conversion'
GROUP BY install_date, media_source;

SELECT install_date, media_source, SUM(skan_duplicates)
FROM ssot_unified
WHERE app_id = 'YOUR_APP'
GROUP BY install_date, media_source;