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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.