> ## Documentation Index
> Fetch the complete documentation index at: https://data-foundation.rockerbox.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Enrich Attribution with Platform Data

> Combine Rockerbox aggregate attribution metrics with platform-reported performance metrics.

# Description

This analysis demonstrates how to join **Rockerbox attribution data** with **performance metrics reported directly by advertising platforms**.

***

# When to Use This Analysis

* Build unified marketing performance reports combining **ad platform metrics and attribution results**.
* Analyze **clicks, impressions, conversions, and revenue** alongside Rockerbox’s attribution outputs.
* Evaluate duplicated **platform-reported performance metrics** against Rockerbox deduplicated attribution.
* Evaluate differences between **platform attribution windows** and Rockerbox attribution results.

***

# Source Data

This analysis joins two datasets.

| Dataset               | Field                                | Description                                                  |
| --------------------- | ------------------------------------ | ------------------------------------------------------------ |
| `aggregate_mta`       | `date`                               | Date of the attributed conversion                            |
| `aggregate_mta`       | `tier_1`–`tier_5`                    | Marketing channel hierarchy                                  |
| `aggregate_mta`       | `platform_join_key`                  | Identifier used to link attribution records to platform data |
| `aggregate_mta`       | `included_spend`                     | Marketing spend associated with the placement                |
| `aggregate_mta`       | `even`, `normalized`                 | Attribution credit metrics                                   |
| `aggregate_mta`       | `revenue_even`, `revenue_normalized` | Revenue attributed by Rockerbox                              |
| `platform_<platform>` | `mta_tiers_join_key`                 | Join key linking platform data to Rockerbox attribution      |
| `platform_<platform>` | `clicks`, `impressions`              | Platform-reported engagement metrics                         |
| `platform_<platform>` | `purchase_*`                         | Platform-reported conversion metrics                         |

***

# Key Metrics

| Metric               | Description                                           |
| -------------------- | ----------------------------------------------------- |
| `spend`              | Marketing spend reported in Rockerbox                 |
| `even`               | Even-weight attributed conversions                    |
| `normalized`         | Modeled multi-touch attributed conversions            |
| `revenue_even`       | Revenue attributed using even-weight attribution      |
| `revenue_normalized` | Revenue attributed using modeled attribution          |
| `clicks`             | Platform-reported clicks                              |
| `impressions`        | Platform-reported impressions                         |
| `purchase_1d_view`   | Platform-reported purchases attributed to 1-day view  |
| `purchase_7d_click`  | Platform-reported purchases attributed to 7-day click |

***

# Example Queries

## Join Rockerbox Attribution with Facebook Platform Data

This query demonstrates how to join Rockerbox attribution with Facebook platform metrics.

The query operates in two stages:

1. Aggregate hourly Facebook performance data to **daily granularity**
2. Join the aggregated platform dataset to the Rockerbox **Buckets Breakdown dataset**

<Warning>
  Because platform datasets often contain **hourly records**, they must be aggregated to **daily granularity** before joining with Rockerbox datasets, which are stored at the daily level.
</Warning>

```sql theme={null}
-- Step 1: Aggregate Facebook platform data to daily granularity
WITH facebook_daily_agg AS (

    SELECT

        -- Dimensions
        identifier,
        date,
        mta_tiers_join_key,

        -- Platform engagement metrics
        SUM(clicks) AS clicks,
        SUM(impressions) AS impressions,

        -- Platform conversion metrics
        SUM(COALESCE(view_1d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_1d_view,
        SUM(COALESCE(click_7d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_7d_click,

        -- Platform revenue metrics
        SUM(COALESCE(view_value_usd_1d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_revenue_1d_view,
        SUM(COALESCE(click_value_usd_7d:offsite_conversion_fb_pixel_purchase,0)) AS purchase_revenue_7d_click

    FROM {platform_facebook_table}

    WHERE
        date >= {start_date}
        AND date <= {end_date}

    GROUP BY
        1,2,3
)

-- Step 2: Join platform metrics to Rockerbox attribution dataset
SELECT

    -- Dimensions
    a.date,
    a.tier_1,
    a.tier_2,
    a.tier_3,
    a.tier_4,
    a.tier_5,
    a.platform_join_key,

    -- Rockerbox attribution metrics
    b.included_spend,
    b.even,
    b.normalized,
    b.revenue_even,
    b.revenue_normalized,

    -- Platform metrics
    f.clicks,
    f.impressions,
    f.purchase_1d_view,
    f.purchase_7d_click,
    f.purchase_revenue_1d_view,
    f.purchase_revenue_7d_click

FROM aggregate_mta a

-- Join Facebook platform data to Rockerbox attribution
LEFT JOIN facebook_daily_agg f
    ON f.mta_tiers_join_key = a.platform_join_key
    AND f.date = a.date

WHERE
    a.date >= {start_date}
    AND a.date <= {{end_date}}
    AND a.conversion_event_id = {{conversion identifier}}
    AND a.platform ILIKE '%facebook%'

ORDER BY
    1,2,3,4,5,6;
```

## Notes on Join Strategy

A **LEFT JOIN** is recommended when joining platform data to Rockerbox attribution.

This ensures that:

* All attribution records from Rockerbox remain present in the final dataset.
* Platform metrics are appended where matching records exist.

This approach is necessary because Rockerbox attribution may include conversions occurring **after platform spend has stopped**, due to longer attribution windows supported by Rockerbox.