# How Rockerbox Helps
Source: https://data-foundation.rockerbox.com/approach
Understanding our approach to collecting your data
Rockerbox takes a comprehensive approach to collecting the data you need for your marketing data foundation.
## Our Goal
* Build a data foundation that is accurate, complete and easy to use for every type of marketing analysis
and marketing organization
* Eliminate the data engineering dependency to have automated data collection and a source of truth
* Simplify the process of ensuring you have an accurate, complete view of your marketing.
## Our Approach
Rockerbox goes to the sources generating information marketing teams care about.
We collect the data directly on your behalf or we integrate with the tools that you use to pull the data from them.
Granularity is key. We aim for a user-level approach to data collection, and
strive to collect the data at the lowest granularity possible.
Integrate with the tools marketers and business use. Collect the data and standardize it.
## Our Focus
We make it easy to collect the data from the sources that matter most to your business and make it
easy for your team to use this data to power their analyses and decision making.
### **Advertising Platforms**
* Paid Social (Facebook, TikTok, etc.)
* Paid Search
* Display Networks
* Connected TV/OTT
### **Data and E-commerce Platforms**
* Customer Data Platforms
* E-commerce platforms
### **Powering Analytics & Attribution**
* Data Warehouses (Snowflake, BigQuery, Redshift)
* Business Intelligence Tools
# DV | Rockerbox Documentation
Source: https://data-foundation.rockerbox.com/index
Find the right resources for your team
Technical resources for configuring Rockerbox integrations, data warehousing, and implementation.
* Set up and connect your data warehouse
* Configure pixels and tracking parameters for integrations
* Set up spend ingestion and tracking
* Access technical implementation guides
Resources for using Rockerbox and interpreting your measurement results.
* Explore product use cases and workflows
* Understand Rockerbox measurement methodology
* Learn how to navigate Rockerbox features
* Find general product guidance and best practices
**Not sure where to start?** Visit [help.rockerbox.com](https://help.rockerbox.com) for general product guidance, or contact [support@rockerbox.com](mailto:support@rockerbox.com) for assistance.
# Data Completeness
Source: https://data-foundation.rockerbox.com/motivation/completeness
Integrations help assemble your marketing data foundation
Integrations are a key component of creating a complete foundation for your marketing data. Integrations help create a comprehensive view
of your marketing performance because they span across so many different types of data, sources, and platforms.
The integrations Rockerbox supports enable you to:
* Track both digital and offline marketing channels
* Track spend across multiple platforms
* Track conversions across multiple platforms
* Measure the impact of each marketing campaign
* Understand the full customer journey from first touch to conversion
# Operational Efficiency
Source: https://data-foundation.rockerbox.com/motivation/efficiency
Integrations are designed to create operational efficiency
A tremendous amount of time and money is wasted within organizations because they
(1) are not able to collect the data they need to make decisions
(2) spend too much time solving the marketing data collection problem.
#### The Data Collection Problem
Marketing teams are faced with one of the most challenging data collection problems of nearly an business unit:
* There data is spread across many different platforms and reports
* The data comes in many different formats
* There is no standardization for how data is collected, organized, or stored (for event similar types of data sources)
Often times, marketing teams recognize the inefficiency of this problem and pull resource from data and analytics teams to drive towards
an automated solution to this problem. While this is often initially successful, it typically leads to long-term maintenance and
non-ideal, cross-team dependencies.
#### Organizational Dependencies
These dependencies are often not sustainable and lead to a diversion of resources away from the core engineering and analytics problems
their respective teams are trying to solve.
#### Efficiency
Integrations drive efficiency via two mechanisms:
1. **Automated Data Collection**: The integrations Rockerbox builds empower marketing teams to independently launch and onboard new platforms and data sources.
2. **Operational Efficiency**: The integrations Rockerbox builds empower marketing teams to independently launch and onboard new platforms and data sources.
# Measuring Performance
Source: https://data-foundation.rockerbox.com/motivation/measuring-performance
Rockerbox helps map your marketing investment to business performance
The goal of Rockerbox is to provide marketers with a complete view of marketing performance so that marketing teams can
use data to make the right optimizations and allocation decisions.
To empower performance measurement, marketing teams need to understand both:
* The dollars you spend on media
* The revenue or conversions that result from these marketing efforts
#### Measuring Performance
With these pieces of information, Rockerbox calculates the key ratios that describe marketing performance:
* **ROAS**: Revenue / Spend
* **CPA**: Cost / Conversions
#### Granular Data
Rockerbox takes the approach of collecting granular data about your marketing.
This allows you to understand the impact of each marketing channel and campaign. Specifically, which campaigns and channels:
* Drive the most revenue
* Drive the most conversions
* Drive the most customers
#### Understanding the Customer
In addition to collecting granular data about your marketing, Rockerbox also collects data about the users converting.
This allows you to understanding the different actions that users take before converting. For instance, you can measure:
* Upper funnel events like add to cart, form submission, or page view
* Lower funnel events like purchase, lead capture, or form submission
* Specific product or service conversions
#### Tying spend to conversions
By combining the two types of data (spend and conversions), you can understand the role specific marketing channels play in driving conversions.
This provides you with a more complete picture of the role specific marketing channels play in driving conversions and allows you
to truly understand the impact of your marketing efforts.
# Standardization
Source: https://data-foundation.rockerbox.com/motivation/standardization
How integrations standardize data
Rockerbox focuses on automating and standardizing across all the different sources of data that marketing teams
need to be able to make decisions. For every source that Rockerbox supports, we have a standard way of collecting, storing, and
classifying the data.
This means that regardless of the source of conversion data, marketing events, or spend, the data that marketing teams interact with
is in a consistent format. This allows your team to focus on making decisions on top of your data without having to worry about where
the data came from or how it was collected.
# Introduction
Source: https://data-foundation.rockerbox.com/overview
Leveraging Rockerbox as your marketing data foundation
Rockerbox is built to serve as the central source of truth for marketing organizations by unifying,
standardizing, and activating all marketing data in one place.
This consolidated approach eliminates data silos and provides a complete view of your marketing performance.
Rockerbox brings together data from:
* First-party website, application and commerce data
* CRM and CDP data feeds
* Integrations with over 150+ advertising platforms
* Ingestion for offline marketing channels
* Ad spend and campaign metadata
All this data is automatically standardized and normalized, ensuring consistent:
* Channel naming and hierarchies
* Currency conversions
* Time zones and reporting periods
* Attribution models and metrics
# OpenAI
Source: https://data-foundation.rockerbox.com/supported-integrations/marketing/ai/openai
Set up Rockerbox tracking for OpenAI advertising campaigns
## Overview
Rockerbox supports tracking for OpenAI advertising campaigns. By configuring Rockerbox tracking parameters in OpenAI Ads Manager, you enable Rockerbox to attribute and map spend at the campaign, ad group, and ad level.
Spend ingestion for OpenAI is currently in development. Configuring tracking parameters now ensures that Rockerbox can backfill historical spend data once the integration is fully available.
## Tracking Parameter Setup
We recommend configuring tracking at the **campaign level** so parameters inherit across all ad groups and ads. Ad group or ad-level configuration is available if you need to override specific values.
1. Navigate to **Campaigns**, **Ad Groups**, or **Ads**
2. Open the three-dot menu for the level where you want to configure tracking
3. Select **Edit campaign**, **Edit ad group**, or **Edit ad**
4. Locate the **Landing page query parameters** field
5. Enter the following Rockerbox tracking parameters:
![Landing page query parameters in OpenAI Ads Manager]()
```text wrap theme={null}
cg_campaignid={campaign_id}&cg_adgroupid={ad_group_id}&cg_adid={ad_id}&cg_accountid={ad_account_id}
```
## URL Tracking Parameter Explanation
| Parameter | Template Value | Description |
| --------------- | ----------------- | ------------- |
| `cg_campaignid` | `{campaign_id}` | Campaign ID |
| `cg_adgroupid` | `{ad_group_id}` | Ad Group ID |
| `cg_adid` | `{ad_id}` | Ad ID |
| `cg_accountid` | `{ad_account_id}` | Ad Account ID |
**Note:** All template values are automatically populated by OpenAI when ads are served — no manual replacement is needed.
## Implementation Notes
* Tracking parameters can be configured at the campaign, ad group, or ad level. More specific settings take precedence: Ad URL → Ad → Ad Group → Campaign
* Parameters are appended to the landing page URL as query strings
* This page will be updated with additional configuration steps as the spend integration becomes available
For implementation assistance, contact [support@rockerbox.com](mailto:support@rockerbox.com).
# The Trade Desk
Source: https://data-foundation.rockerbox.com/supported-integrations/marketing/dsp/the-trade-desk
Learn how to set up The Trade Desk spend API integration with Rockerbox
## Overview
Rockerbox provides comprehensive integration with The Trade Desk (TTD) for tracking programmatic advertising across Display, Video, OTT, and Audio inventory. The integration includes both API-based spend reporting and impression/click tracking.
## Prerequisites
Before setting up the integration, you'll need:
* An active The Trade Desk advertiser account
* MyReports API access from The Trade Desk
* Access letter authorizing Rockerbox partnership
* Four authentication credentials: Partner ID, Account ID, Username, Password
## Requesting API Access
1. Contact your TTD representative to request MyReports API access
2. Send the following message:
```text wrap theme={null}
We need our partner, Rockerbox, to access the reporting API in order to report on TTD spend in the Rockerbox platform. We approve Rockerbox to access the API on our behalf. Please send over the access letter so that we can complete this step.
```
3. Request access to these specific Report IDs:
* **Go-forward spend**: 2554829
* **Historical spend**: 2681145
## Setup Process
1. Navigate to the [Advertising Platforms](https://app.rockerbox.com/v3/data/marketing/advertising_platforms/connect) page
2. Find and select The Trade Desk from the list of platforms
3. Click "Connect" and enter credentials:
* Partner ID
* Account ID
* Username
* Password
4. Implement tracking (URL parameters and/or impression pixels)
5. Verify deployment one day post-launch via Digital Advertising page
## Tracking Implementation
### Critical Requirement
**Match pixel to inventory type** - Using the wrong pixel type (e.g., Display pixel on Video inventory) will cause tracking failures.
## URL Tracking Parameter Explanation
| Parameter | Macro | Description |
| ----------------- | ----------------------------- | -------------------------- |
| `td_adgroupid` | `%%TTD_ADGROUPID%%` | Ad Group ID (required) |
| `td_campaignid` | `%%TTD_CAMPAIGNID%%` | Campaign ID (required) |
| `td_creativeid` | `%%TTD_CREATIVEID%%` | Creative ID (required) |
| `td_supplyvendor` | `%%TTD_SUPPLYVENDOR_INT%%` | Supply Vendor (required) |
| `td_site` | `%%TTD_SITE%%` | Site (required) |
| `td_type` | `display` or `video` or `ott` | Inventory Type (hardcoded) |
### Display (Desktop) Inventory
```text wrap theme={null}
td_campaignid=%%TTD_CAMPAIGNID%%&td_adgroupid=%%TTD_ADGROUPID%%&td_creativeid=%%TTD_CREATIVEID%%&td_supplyvendor=%%TTD_SUPPLYVENDOR_INT%%&td_site=%%TTD_SITE%%&td_type=display
```
### Video Inventory
```text wrap theme={null}
td_campaignid=%%TTD_CAMPAIGNID%%&td_adgroupid=%%TTD_ADGROUPID%%&td_creativeid=%%TTD_CREATIVEID%%&td_supplyvendor=%%TTD_SUPPLYVENDOR_INT%%&td_site=%%TTD_SITE%%&td_type=video
```
### OTT (Over-the-Top) Inventory
```text wrap theme={null}
td_campaignid=%%TTD_CAMPAIGNID%%&td_adgroupid=%%TTD_ADGROUPID%%&td_creativeid=%%TTD_CREATIVEID%%&td_supplyvendor=%%TTD_SUPPLYVENDOR_INT%%&td_site=%%TTD_SITE%%&td_type=ott
```
## Impression Pixel Tracking
Impression pixels provide user-level tracking for view-based attribution. **Critical**: Use the correct pixel for your inventory type.
**Parameters:**
| Parameter | Macro | Description |
| -------------- | ----------------------------------------- | ------------------------------- |
| `tier_one` | `ttd-display` or `ttd-video` or `ttd-ott` | Platform identifier (hardcoded) |
| `tier_two` | `%%TTD_CAMPAIGNID%%` | Campaign ID (required) |
| `tier_three` | `%%TTD_ADGROUPID%%` | Ad Group ID (required) |
| `tier_four` | `%%TTD_CREATIVEID%%` | Creative ID (required) |
| `tier_five` | `%%TTD_SUPPLYVENDOR_INT%%` | Supply Vendor (required) |
| `auction_id` | `%%TTD_IMPRESSIONID%%` | Impression ID (required) |
| `referrer` | `%%TTD_SITE%%` | Site (required) |
| `td_partnerid` | `%%TTD_PARTNERID%%` | Partner ID |
### Display Pixel
```text wrap theme={null}
https://metrics.getrockerbox.com/track/v5?source=[SOURCE]&tier_one=ttd-display&tier_two=%%TTD_CAMPAIGNID%%&tier_three=%%TTD_ADGROUPID%%&tier_four=%%TTD_CREATIVEID%%&tier_five=%%TTD_SUPPLYVENDOR_INT%%&auction_id=%%TTD_IMPRESSIONID%%&referrer=%%TTD_SITE%%&td_partnerid=%%TTD_PARTNERID%%
```
**Note:** `[SOURCE]` is a hardcoded value that must be obtained from Rockerbox - it's your company's [Account ID](https://app.rockerbox.com/v3/settings/account).
### Video Pixel
```text wrap theme={null}
https://metrics.getrockerbox.com/track/v5?source=[SOURCE]&tier_one=ttd-video&tier_two=%%TTD_CAMPAIGNID%%&tier_three=%%TTD_ADGROUPID%%&tier_four=%%TTD_CREATIVEID%%&tier_five=%%TTD_SUPPLYVENDOR_INT%%&auction_id=%%TTD_IMPRESSIONID%%&referrer=%%TTD_SITE%%&td_partnerid=%%TTD_PARTNERID%%
```
**Note:** `[SOURCE]` is a hardcoded value that must be obtained from Rockerbox - it's your company's [Account ID](https://app.rockerbox.com/v3/settings/account).
**Note:** Same parameters as Display, but `tier_one=ttd-video`
### OTT/Streaming Audio Pixel
```text wrap theme={null}
https://metrics.getrockerbox.com/track/v5?source=[SOURCE]&tier_one=ttd-ott&tier_two=%%TTD_CAMPAIGNID%%&tier_three=%%TTD_ADGROUPID%%&tier_four=%%TTD_CREATIVEID%%&tier_five=%%TTD_SUPPLYVENDOR_INT%%&auction_id=%%TTD_IMPRESSIONID%%&referrer=%%TTD_SITE%%&td_partnerid=%%TTD_PARTNERID%%
```
**Note:** `[SOURCE]` is a hardcoded value that must be obtained from Rockerbox - it's your company's [Account ID](https://app.rockerbox.com/v3/settings/account).
**Note:** Same parameters as other pixels, but `tier_one=ttd-ott`
## Implementation Notes
* **Macros auto-populate**: All `%%TTD_*%%` macros are automatically filled by The Trade Desk when ads are served - no manual replacement needed
* **Inventory type matching is critical**: Ensure Display pixels are used on Display inventory, Video pixels on Video inventory, etc.
* **URL parameters use platform macros**: Parameters dynamically populate during ad serving
* **Follow standard UTM structure**: Maintain proper URL formatting
**The Trade Desk Resources:**
* [Tracking Tags Overview](https://partner.thetradedesk.com/v3/portal/data/doc/TrackingTagsOverview) (Partner Portal)
* [Static Tracking Tags](https://partner.thetradedesk.com/v3/portal/data/doc/TrackingTagsStatic) (Partner Portal)
* [Creative Specifications](https://www.thetradedesk.com/assets/global/documents/Creative_Specifications-en.pdf) (PDF)
**Note:** Partner Portal documentation requires TTD login access. Contact your Trade Desk representative for implementation assistance.
## Verification
After setup:
1. Confirm API credentials are properly configured
2. Verify impression pixels are firing correctly (check by inventory type)
3. Validate click tracking parameters are populating
4. Check spend data appears in Rockerbox dashboard one day post-launch
5. Use Digital Advertising page in Rockerbox for verification
6. Validate metrics match your Trade Desk reporting
## Troubleshooting
If you encounter any issues:
1. Verify MyReports API access has been granted
2. Confirm all four credentials are correct (Partner ID, Account ID, Username, Password)
3. Check that correct pixel type matches inventory type
4. Validate URL parameter configuration
5. Ensure access to both Report IDs (2554829, 2681145)
6. Contact [support@rockerbox.com](mailto:support@rockerbox.com) for additional assistance
# Overview
Source: https://data-foundation.rockerbox.com/warehousing-overview/overview
Learn how Rockerbox integrates with your data warehouse to serve as your marketing data foundation
## Overview
Rockerbox's data warehouse integration capabilities enable advertisers to seamlessly connect
their marketing data with their broader data infrastructure, establishing Rockerbox as the
foundational layer for marketing analytics and decision-making.
The integration is designed to provide a seamless experience for marketing teams, analytics teams,
and other business stakeholders to share the same foundational data across their organization.
## Benefits
### Marketing Data Foundation
Rockerbox serves as your centralized marketing data foundation, seamlessly integrating back into your
data warehouse. Our platform provides clean, deduplicated data that's ready for immediate analysis.
By combining Rockerbox's comprehensive marketing data with your internal data sources, you'll ensure
consistency across all reporting and analysis tools while eliminating traditional data silos between
marketing and other business functions.
* Bring your centralized marketing data foundation back into your data warehouse
* Provide clean, deduplicated data ready for analysis
* Combine Rockerbox's comprehensive marketing data with your internal data sources
* Ensure consistency across all reporting and analysis tools
* Eliminate data silos between marketing and other business functions
### Enhanced Data Granularity
Through our extensive data collection efforts and integrations with major marketing platforms,
Rockerbox provides the most comprehensive set of user-level marketing touchpoint data available.
This granular data, when combined with your customer-specific information, enables deeper insights
into customer behavior and marketing effectiveness. Our warehousing solution makes this detailed data
readily accessible within your existing infrastructure.
* Rockerbox has the most comprehensive set of user-level marketing touchpoint data through our data
collection efforts and integrations with major marketing platforms.
* Warehousing provides access to this user-level marketing touchpoint data
* This data can be combined with customer-specific information from your warehouse
* Teams can enable deeper insights into customer behavior and marketing effectiveness
### Custom Analysis Capabilities
Take full advantage of your existing BI tools and data infrastructure to unlock powerful insights.
With Rockerbox's data in your warehouse, you can create custom reports and dashboards while performing
advanced analytics using your preferred tools and methodologies.
* Leverage your existing BI tools and data infrastructure
* Create custom reports and dashboards
* Perform advanced analytics using your preferred tools
### Operational Efficiency
Transform your marketing operations with automated data flows between systems, significantly reducing
manual reporting overhead. This streamlined approach to marketing operations allows your team to focus on
strategic initiatives rather than data management tasks.
* Automate data flows between systems
* Reduce manual reporting overhead
* Streamline marketing operations
## Data Synchronization
### Automated Updates
* Daily refreshes for platform data
* Real-time updates for conversion events
* Historical data backfilling available
* Configurable sync schedules
### Data Quality
* Automated validation checks
* Schema consistency monitoring
* Error reporting and alerts
* Data completeness verification
## Getting Started
1. Contact your Rockerbox representative to enable warehouse integration
2. Choose your preferred warehouse platform
3. Complete platform-specific setup steps:
* Snowflake: Configure database and schema sharing
* BigQuery: Set up project and dataset permissions
* Redshift: Configure IAM roles and external schema
4. Select data sets to sync
5. Begin leveraging Rockerbox data in your warehouse
## Support
Our team provides:
* Technical implementation assistance
* Schema documentation
* Best practices guidance
* Ongoing support for data questions
## Security and Compliance
* Encrypted data transmission
* Role-based access controls
* Compliance with data security standards
# Supported Warehouses
Source: https://data-foundation.rockerbox.com/warehousing-overview/platforms
Learn about the platforms supported for data warehouse integration
## Supported Warehouses
Rockerbox integrates with major data warehouse platforms. Each integration is designed to provide a
seamless experience for data warehouse integration.
### Snowflake
* Native [data sharing](https://docs.snowflake.com/en/user-guide/data-sharing-intro) capabilities
* Automated schema management
* Real-time data updates
* Custom database naming
* Nested data structure support
### Google BigQuery
* Project-level integration
* Multi-region support
* Automated table creation
* Nested data structure support
### Amazon Redshift
* IAM role-based security
* External schema configuration
* Automated data syncing
## Snowflake Reader Account for Unsupported Warehouses
If your preferred platform doesn’t have a native integration, Rockerbox can provision a [Snowflake Reader Account](https://docs.snowflake.com/en/user-guide/data-sharing-reader-create) to give you access to Rockerbox datasets. From there, you can egress the data to the platform of your choice.
### Snowflake Reader Account
* No Snowflake account required
* Rockerbox covers all storage and compute
* Monthly credit limit on account to cover egress costs
* Automated schema management
* Real-time data updates
* Nested data structure support
# Use Cases
Source: https://data-foundation.rockerbox.com/warehousing-overview/use-cases
Learn about analysis use cases supported by data warehouse integrations
### Advanced Analytics
* Customer journey analysis
* Cohort performance tracking
* Custom attribution modeling
* LTV optimization
* Contributin margin / profit margin analysis
### Business Intelligence
* Marketing performance dashboards
* ROI reporting
* Cross-channel analysis
* Custom metric creation
### Data Science
* Predictive modeling
* Customer segmentation
* Attribution model development
* Marketing mix optimization
# Aggregate MTA Migration
Source: https://data-foundation.rockerbox.com/warehousing/aggregate-mta-migration-guide
## 🚨 Required Action
All usage of the **Buckets Breakdown schema** must be migrated to the `aggregate_mta` table by **April 30, 2026**. After this date, Buckets Breakdown tables will stop receiving updates (historical data will remain queryable).
***
## What’s Changing
* New table: **aggregate\_mta** replaces all Buckets Breakdown tables in your [data share](https://app.rockerbox.com/v3/data/exports/data_warehouse).
* Rolled out alongside existing tables so you can build and test without impacting production.
* **aggregate\_mta** is fully backfilled to your first clean reporting date in Rockerbox.
## Key Benefits
* Significantly faster and more reliable delivery of aggregate attribution and spend — KPIs are available before your stakeholders start their day.
* Fewer tables to query and manage with all aggregate attribution data across all conversion events available in one streamlined reporting table.
## Migration Timeline
* **Deprecation date:** April 30, 2026 — Buckets Breakdown tables stop receiving updates.
📜 **Historical Access:** Historical data in Buckets Breakdown tables will still be available after the deprecation date. We do not intend to remove these tables at this point in time.
***
## Migration Guidance
### General Notes
* Rockerbox will not drop or rename existing Buckets Breakdown tables so as to not break your production reporting.
* Create views to replicate Buckets Breakdown tables, suffixed with `_v2`.
* Change the table reference in all queries to point to the view.
* If you don't want to create backwards compatible views, then review the Schema Differences section below for a full accounting of the changes.
⚠️ **Important:** If merging aggregate\_mta with historical data sourced from Buckets Breakdown schema, then lowercase all `platform_join_key` and taxonomy columns (`tier_1`–`tier_5`) in the historical data from the **Buckets Breakdown** source table to ensure consistency.
Example:
```sql theme={null}
LOWER(tier_1) AS tier_1
```
***
## Snowflake Instructions
### Pre-Requisites
* Local database and schema where you can define views.
Note: Rockerbox shares data via **Secure Data Sharing**. The shared database is read-only.
### SQL Statement
```sql theme={null}
CREATE OR REPLACE VIEW .. AS
SELECT
advertiser,
currency_code,
date,
SUM(even) AS even,
SUM(first_touch) AS first_touch,
fx_rate_to_usd,
conversion_event_id AS identifier,
SUM(last_touch) AS last_touch,
SUM(normalized) AS normalized,
SUM(ntf_even) AS ntf_even,
SUM(ntf_first_touch) AS ntf_first_touch,
SUM(ntf_last_touch) AS ntf_last_touch,
SUM(ntf_normalized) AS ntf_normalized,
SUM(ntf_revenue_even) AS ntf_revenue_even,
SUM(ntf_revenue_first_touch) AS ntf_revenue_first_touch,
SUM(ntf_revenue_last_touch) AS ntf_revenue_last_touch,
SUM(ntf_revenue_normalized) AS ntf_revenue_normalized,
platform,
LOWER(platform_join_key) AS platform_join_key,
MAX(rb_sync_id) AS rb_sync_id,
report,
SUM(revenue_even) AS revenue_even,
SUM(revenue_first_touch) AS revenue_first_touch,
SUM(revenue_last_touch) AS revenue_last_touch,
SUM(revenue_normalized) AS revenue_normalized,
SUM(included_spend) AS spend,
LOWER(tier_1) AS tier_1,
LOWER(tier_2) AS tier_2,
LOWER(tier_3) AS tier_3,
LOWER(tier_4) AS tier_4,
LOWER(tier_5) AS tier_5,
'attribution' AS type,
MAX(updated_at) AS updated_at
FROM ..
WHERE conversion_event_id = <12345>
GROUP BY
advertiser,
currency_code,
date,
fx_rate_to_usd,
conversion_event_id,
platform,
platform_join_key,
report,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
type;
```
### SQL Parameters
| Parameter | Description |
| --------------------- | --------------------------------------------------------------- |
| `TARGET_DB` | Database where the view will reside |
| `TARGET_SCHEMA` | Schema where the view will reside |
| `VIEW_NAME` | Name of the view (match source table name + suffix, e.g. `_v2`) |
| `CONVERSION_EVENT_ID` | Conversion ID to filter on (match Buckets Breakdown table) |
| `SOURCE_DB` | Rockerbox-shared database |
| `SOURCE_SCHEMA` | Rockerbox-shared schema |
| `SOURCE_TABLE` | Rockerbox Buckets Breakdown source table |
***
## BigQuery Instructions
### Pre-Requisites
* A project created with BigQuery resource enabled.
* A dataset within the aforementioned project.
Note: these are the same pre-requisites for connecting Rockerbox with BigQuery, so there is no need to create a new project and for the specific purpose of creating these compatibility views.
### SQL Statement
```sql theme={null}
CREATE OR REPLACE VIEW `..` AS
SELECT
LOWER(tier_1) AS tier_1,
LOWER(tier_2) AS tier_2,
LOWER(tier_3) AS tier_3,
LOWER(tier_4) AS tier_4,
LOWER(tier_5) AS tier_5,
platform,
LOWER(platform_join_key) AS platform_join_key,
SUM(first_touch) AS first_touch,
SUM(ntf_first_touch) AS ntf_first_touch,
SUM(revenue_first_touch) AS revenue_first_touch,
SUM(ntf_revenue_first_touch) AS ntf_revenue_first_touch,
SUM(last_touch) AS last_touch,
SUM(ntf_last_touch) AS ntf_last_touch,
SUM(revenue_last_touch) AS revenue_last_touch,
SUM(ntf_revenue_last_touch) AS ntf_revenue_last_touch,
SUM(even) AS even,
SUM(ntf_even) AS ntf_even,
SUM(revenue_even) AS revenue_even,
SUM(ntf_revenue_even) AS ntf_revenue_even,
SUM(normalized) AS normalized,
SUM(ntf_normalized) AS ntf_normalized,
SUM(revenue_normalized) AS revenue_normalized,
SUM(ntf_revenue_normalized) AS ntf_revenue_normalized,
SUM(included_spend) AS spend,
currency_code,
fx_rate_to_usd,
MAX(rb_sync_id) AS rb_sync_id,
MAX(updated_at) AS updated_at,
date
FROM `..`
WHERE conversion_event_id = <12345>
GROUP BY
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
platform,
platform_join_key,
currency_code,
fx_rate_to_usd,
date;
```
### SQL Parameters
| Parameter | Description |
| --------------------- | --------------------------------------------------------------- |
| `TARGET_PROJECT` | Project where the view will reside |
| `TARGET_DATASET` | Dataset where the view will reside |
| `VIEW_NAME` | Name of the view (match source table name + suffix, e.g. `_v2`) |
| `CONVERSION_EVENT_ID` | Conversion ID to filter on (match Buckets Breakdown table) |
| `SOURCE_PROJECT` | Rockerbox-shared project |
| `SOURCE_DATASET` | Rockerbox-shared dataset |
| `SOURCE_TABLE` | Rockerbox Buckets Breakdown source table |
***
## Redshift Instructions
### Pre-Requisites
* Local database and schema where you can define views.
* USAGE permissions on Rockerbox external schema (Glue + S3).
Note: External schema used to share data is read-only.
### SQL Statement
```sql theme={null}
CREATE OR REPLACE VIEW .. AS
SELECT
LOWER(tier_1) AS tier_1,
LOWER(tier_2) AS tier_2,
LOWER(tier_3) AS tier_3,
LOWER(tier_4) AS tier_4,
LOWER(tier_5) AS tier_5,
platform,
LOWER(platform_join_key) AS platform_join_key,
SUM(first_touch) AS first_touch,
SUM(ntf_first_touch) AS ntf_first_touch,
SUM(revenue_first_touch) AS revenue_first_touch,
SUM(ntf_revenue_first_touch) AS ntf_revenue_first_touch,
SUM(last_touch) AS last_touch,
SUM(ntf_last_touch) AS ntf_last_touch,
SUM(revenue_last_touch) AS revenue_last_touch,
SUM(ntf_revenue_last_touch) AS ntf_revenue_last_touch,
SUM(even) AS even,
SUM(ntf_even) AS ntf_even,
SUM(revenue_even) AS revenue_even,
SUM(ntf_revenue_even) AS ntf_revenue_even,
SUM(normalized) AS normalized,
SUM(ntf_normalized) AS ntf_normalized,
SUM(revenue_normalized) AS revenue_normalized,
SUM(ntf_revenue_normalized) AS ntf_revenue_normalized,
SUM(included_spend) AS spend,
currency_code,
fx_rate_to_usd,
MAX(rb_sync_id) AS rb_sync_id,
MAX(updated_at) AS updated_at,
"date" AS date
FROM ..
WHERE conversion_event_id = <12345>
GROUP BY
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
platform,
platform_join_key,
currency_code,
fx_rate_to_usd,
"date"
WITH NO SCHEMA BINDING;
```
### SQL Parameters
| Parameter | Description |
| --------------------- | --------------------------------------------------------------- |
| `TARGET_DB` | Database where the view will reside |
| `TARGET_SCHEMA` | Schema where the view will reside |
| `VIEW_NAME` | Name of the view (match source table name + suffix, e.g. `_v2`) |
| `CONVERSION_EVENT_ID` | Conversion ID to filter on (match Buckets Breakdown table) |
| `SOURCE_DB` | Rockerbox external database |
| `SOURCE_SCHEMA` | Rockerbox external schema |
| `SOURCE_TABLE` | Rockerbox Buckets Breakdown source table |
***
## Schema Differences
### Full Schema Diff
| Change | Legacy Schema (Buckets Breakdown) | Legacy Type | New Schema (Aggregate MTA) | New Type | Notes |
| ------------- | --------------------------------- | ----------- | -------------------------- | --------- | ------------------------------------------------------------ |
| Removed | type | str | — | — | Only visible in Snowflake |
| | report | str | report | str | Only visible in Snowflake |
| Added | — | — | version | str | Only visible in Snowflake |
| Added | — | — | partition | str | |
| | advertiser | str | advertiser | str | Only visible in Snowflake |
| Added/Renamed | identifier | int | conversion\_event\_id | int | Redshift/BigQuery: new column; Snowflake: renamed identifier |
| | date | date | date | date | |
| | tier\_1 | str | tier\_1 | str | |
| | tier\_2 | str | tier\_2 | str | |
| | tier\_3 | str | tier\_3 | str | |
| | tier\_4 | str | tier\_4 | str | |
| | tier\_5 | str | tier\_5 | str | |
| | platform | str | platform | str | |
| | platform\_join\_key | str | platform\_join\_key | str | |
| Added | — | — | conversion\_event\_name | str | |
| | first\_touch | int | first\_touch | int | |
| | ntf\_first\_touch | int | ntf\_first\_touch | int | |
| | revenue\_first\_touch | float | revenue\_first\_touch | float | |
| | ntf\_revenue\_first\_touch | float | ntf\_revenue\_first\_touch | float | |
| | last\_touch | int | last\_touch | int | |
| | ntf\_last\_touch | int | ntf\_last\_touch | int | |
| | revenue\_last\_touch | float | revenue\_last\_touch | float | |
| | ntf\_revenue\_last\_touch | float | ntf\_revenue\_last\_touch | float | |
| | even | float | even | float | |
| | ntf\_even | float | ntf\_even | float | |
| | revenue\_even | float | revenue\_even | float | |
| | ntf\_revenue\_even | float | ntf\_revenue\_even | float | |
| | normalized | float | revenue\_even | float | |
| | ntf\_normalized | float | ntf\_normalized | float | |
| | revenue\_normalized | float | revenue\_normalized | float | |
| | ntf\_revenue\_normalized | float | ntf\_revenue\_normalized | float | |
| Renamed | spend | float | included\_spend | float | |
| | currency\_code | str | currency\_code | str | |
| | fx\_rate\_to\_usd | float | fx\_rate\_to\_usd | float | |
| | rb\_sync\_id | int | rb\_sync\_id | int | |
| | updated\_at | timestamp | updated\_at | timestamp | |
### Many Tables > One Table
* **Before:** One table per conversion event.
* N tables: `buckets_breakdown_add_to_cart`, `buckets_breakdown_purchase`
* **After:** One unified table includes `conversion_event_id` and `conversion_event_name` for segmentation.
* One new table: `aggregate_mta`
* **Guidance:** Add a filter to replicate current table behavior.
* `WHERE conversion_event_name = `
### Partitioned Rows
* **Before:** Spend and attributed conversions/revenue in the same row.
* **After:** Attribute conversions/revenue in row where `partition=mta`; spend in separate row where `partition=included_spend`.
* **Guidance:** Aggregate (SUM) metrics across relevant dimension columns (date, tier\_1, tier\_2, etc.) to compute KPIs such as CPA and ROAS.
> 🚨 Rows may look duplicated compared to the Buckets Breakdown schema because attribution and spend data are now split across separate rows. This is expected behavior.
**Sample SQL (works in Redshift, BigQuery, Snowflake):**
```sql theme={null}
SELECT
date,
tier_1,
tier_2,
SUM(even) AS even,
SUM(included_spend) AS spend
FROM aggregate_mta
GROUP BY date, tier_1, tier_2;
```
### Case Differences
* **Issue:** `platform_join_key` and `tier_1` through `tier_5` may have inconsistent case vs. legacy tables in certain instances.
* **Guidance:** Apply `LOWER()` in queries before joining or merging data from `aggregate_mta` with ANY of the legacy schemas.
# Aggregate MTA: Partition Column Removal
Source: https://data-foundation.rockerbox.com/warehousing/aggregate-mta-partition-migration
## 🚨 Action Required by April 13th 2026
Update any queries that reference `aggregate_mta.partition`. After April 13th 2026, the `partition` column will be removed and those queries will fail.
If your queries **do not reference `partition`**, no action is required.
***
## What’s Changing
Rockerbox is updating the `aggregate_mta` table to improve data sync performance during long backfills. As part of this update, the `partition` column is being removed.
***
## Impact
### If you do not use `partition`
No changes are required.
### If you use `partition`
Your queries will fail once the change is applied unless updated beforehand.
***
## Migration Timeline
* **Automatic change applied:** April 13, 2026.
* After this date, `aggregate_mta.partition` will not exist.
***
## Required Updates
### 1. Remove `partition` from `SELECT` statements
### 2. Avoid `SELECT *`
If you currently rely on: `SELECT * FROM aggregate_mta;`
Update to an explicit column list to prevent unexpected breakage when schema changes occur.
### 3. Replace `WHERE partition = ...` Filters
| Use Case | Before | After |
| ------------------ | ------------------------------------ | -------------------------- |
| Conversions filter | `WHERE partition = 'mta'` | `WHERE even > 0` |
| Spend filter | `WHERE partition = 'included_spend'` | `WHERE included_spend > 0` |
## Need Help?
If you’re unsure whether any production queries reference aggregate\_mta.partition, please contact Rockerbox Support.
# Customize Attribution Windows
Source: https://data-foundation.rockerbox.com/warehousing/analytics-customize-attribution-windows
Apply custom attribution window filters to the log-level MTA dataset and rebuild attribution credit across remaining touchpoints.
# Description
This analysis demonstrates how to filter marketing touchpoints from the **Log Level MTA dataset** in order to apply custom attribution logic.
For example, you may want to exclude marketing touchpoints that occur outside a defined attribution window (e.g., more than 30 days before a conversion). After filtering the dataset, attribution credit for conversions must be recalculated across the remaining touchpoints.
The query performs two main steps:
1. **Filter marketing touchpoints** from the log-level MTA dataset based on custom attribution rules.
2. **Reinsert conversion records** for cases where all marketing touchpoints were removed by the filter, ensuring conversions remain represented in the output dataset.
The result is a **customized log-level attribution dataset** that reflects your adjusted attribution logic.
***
# When to Use This Analysis
* Apply a **custom attribution window** to marketing touchpoints.
* Remove interactions that occur outside a defined attribution period.
* Recalculate attribution weights after filtering certain touchpoints.
* Create a modified **log-level attribution dataset** aligned with internal measurement rules.
***
# Source Data
This analysis uses fields from the **Log Level MTA dataset**.
| Field | Description |
| ------------------------------------------------- | ------------------------------------------------------- |
| `conversion_hash_id` | Identifier for a unique conversion event |
| `conversion_key` | Conversion identifier used for joins |
| `timestamp_conv` | Timestamp of the conversion event |
| `timestamp_events` | Timestamp of the marketing touchpoint |
| `sequence_number` | Position of the touchpoint in the conversion path |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy |
| `spend_key` | Identifier linking attribution events to platform spend |
| `normalized`, `even`, `first_touch`, `last_touch` | Attribution credit values |
| `revenue_even` | Revenue attributed under the even attribution model |
| `new_to_file` | Indicator for new customer conversions |
***
# Key Metrics
| Metric | Description |
| -------------------------- | ------------------------------------------------------- |
| `filtered_first_touch` | First-touch attribution after filtering touchpoints |
| `filtered_last_touch` | Last-touch attribution after filtering touchpoints |
| `filtered_even` | Even attribution weight across remaining touchpoints |
| `filtered_normalized` | Modeled attribution weight recalculated after filtering |
| `filtered_sequence_number` | Order of touchpoints after filtering |
| `filtered_total_events` | Number of remaining touchpoints for a conversion |
***
# Example 1: Apply Standard Attribution Window Across All Channels
This query filters marketing touchpoints that occur **more than 30 days before a conversion** and recalculates attribution credit across the remaining touchpoints.
```sql theme={null}
-- CTE: Filter events out of log-level MTA
WITH filtered_mta AS (
SELECT
-- Identity fields
m.action,
m.base_id,
m.uid,
m.hash_ip_events,
m.user_agent_events,
-- Conversion context
m.new_to_file,
m.currency_code,
m.fx_rate_to_usd,
m.conversion_hash_id,
m.conversion_key,
-- Event timestamps
m.date,
m.timestamp_conv,
m.timestamp_events,
-- Session context
m.onsite_count,
m.marketing_type,
m.event_id,
m.matches,
-- URL context
m.original_url,
m.url_parameters,
m.utm_parameters,
m.request_referrer,
-- Marketing hierarchy
m.tier_1,
m.tier_2,
m.tier_3,
m.tier_4,
m.tier_5,
-- Spend identifiers
m.spend_key,
m.platform,
-- First-touch attribution recalculation
CASE
WHEN MIN(m.sequence_number) OVER (PARTITION BY m.date, m.conversion_hash_id) = m.sequence_number
THEN 1
ELSE 0
END AS filtered_first_touch,
-- Last-touch attribution recalculation
CASE
WHEN MAX(m.sequence_number) OVER (PARTITION BY m.date, m.conversion_hash_id) = m.sequence_number
THEN 1
ELSE 0
END AS filtered_last_touch,
-- Even attribution recalculation
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_even,
-- Even attribution revenue allocation
(
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id)
) * m.total_events * m.revenue_even AS filtered_even_revenue,
-- Modeled attribution recalculation
COALESCE(
m.normalized / SUM(m.normalized) OVER (PARTITION BY m.date, m.conversion_hash_id),
0
) AS filtered_normalized,
-- Sequence tracking after filtering
RANK() OVER (
PARTITION BY m.date, m.conversion_hash_id
ORDER BY sequence_number ASC
) AS filtered_sequence_number,
-- Count of remaining events
COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_total_events,
m.rb_sync_id,
m.updated_at
FROM log_level_mta m
WHERE
m.date >= {start_date}
AND m.date <= {end_date}
-- Apply custom attribution window
AND timestamp_events >= DATEADD(day, -30, timestamp_conv)
),
-- CTE: Reinsert conversions when all marketing touchpoints were removed
log_level_final AS (
SELECT
COALESCE(f.identifier, m2.identifier) AS identifier,
COALESCE(f.action, m2.action) AS action,
COALESCE(f.base_id, m2.base_id) AS base_id,
COALESCE(f.uid, m2.uid) AS uid,
COALESCE(f.hash_ip_events, m2.hash_ip_events) AS hash_ip_events,
COALESCE(f.user_agent_events, m2.user_agent_events) AS user_agent_events,
COALESCE(f.new_to_file, m2.new_to_file) AS new_to_file,
COALESCE(f.conversion_hash_id, m2.conversion_hash_id) AS conversion_hash_id,
COALESCE(f.conversion_key, m2.conversion_key) AS conversion_key,
COALESCE(f.date, m2.date) AS date,
COALESCE(f.timestamp_conv, m2.timestamp_conv) AS timestamp_conv,
COALESCE(f.timestamp_events, m2.timestamp_conv) AS timestamp_events,
COALESCE(f.marketing_type, 'conv_only') AS marketing_type,
f.event_id,
COALESCE(f.tier_1, 'Direct') AS tier_1,
f.tier_2,
f.tier_3,
f.tier_4,
f.tier_5,
f.spend_key,
-- Attribution metrics after filtering
COALESCE(f.filtered_first_touch, 1) AS filtered_first_touch,
COALESCE(f.filtered_last_touch, 1) AS filtered_last_touch,
COALESCE(f.filtered_even, 1) AS filtered_even,
COALESCE(f.filtered_normalized, 1) AS filtered_normalized,
COALESCE(f.filtered_sequence_number, 1) AS filtered_sequence_number,
COALESCE(f.filtered_total_events, 1) AS filtered_total_events,
COALESCE(f.rb_sync_id, m2.rb_sync_id) AS rb_sync_id,
COALESCE(f.updated_at, m2.updated_at) AS updated_at
FROM filtered_mta f
RIGHT OUTER JOIN (
-- Ensure each conversion remains represented
SELECT *
FROM log_level_mta m
WHERE
m.first_touch = 1
AND m.date >= {start_date}
AND m.date <= {end_date}
) AS m2
ON m2.date = f.date
AND m2.conversion_key = f.conversion_key
)
SELECT *
FROM log_level_final;
```
# Example 2: Apply a Variable Attribution Window by Channel
Note: This example assumes channels are defined using `tier_1` and `tier_2`. Depending on your taxonomy, you may also need to define lookback windows at the `tier_3` level. If a channel is not included in the lookup table, the query applies a very long fallback window so those touchpoints remain included.
```sql theme={null}
-- CTE: Define channel specific attribution window in days
-- Config assumes exact match on `tier_#` strings
WITH lookback_config AS (
SELECT
column1 AS tier_1,
column2 AS tier_2,
column3 AS lookback_window_days
FROM VALUES
('display', 'prospecting', 60),
('display', 'prospecting', 7),
('display', 'retargeting', 60),
),
-- CTE: Filter events out of log-level MTA
filtered_mta AS (
SELECT
-- Identity fields
m.action,
m.base_id,
m.uid,
m.hash_ip_events,
m.user_agent_events,
-- Conversion context
m.new_to_file,
m.currency_code,
m.fx_rate_to_usd,
m.conversion_hash_id,
m.conversion_key,
-- Event timestamps
m.date,
m.timestamp_conv,
m.timestamp_events,
-- Session context
m.onsite_count,
m.marketing_type,
m.event_id,
m.matches,
-- URL context
m.original_url,
m.url_parameters,
m.utm_parameters,
m.request_referrer,
-- Marketing hierarchy
m.tier_1,
m.tier_2,
m.tier_3,
m.tier_4,
m.tier_5,
-- Spend identifiers
m.spend_key,
m.platform,
-- First-touch attribution recalculation
CASE
WHEN MIN(m.sequence_number) OVER (PARTITION BY m.date, m.conversion_hash_id) = m.sequence_number
THEN 1
ELSE 0
END AS filtered_first_touch,
-- Last-touch attribution recalculation
CASE
WHEN MAX(m.sequence_number) OVER (PARTITION BY m.date, m.conversion_hash_id) = m.sequence_number
THEN 1
ELSE 0
END AS filtered_last_touch,
-- Even attribution recalculation
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_even,
-- Even attribution revenue allocation
(
1.0 / COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id)
) * m.total_events * m.revenue_even AS filtered_even_revenue,
-- Modeled attribution recalculation
COALESCE(
m.normalized / SUM(m.normalized) OVER (PARTITION BY m.date, m.conversion_hash_id),
0
) AS filtered_normalized,
-- Sequence tracking after filtering
RANK() OVER (
PARTITION BY m.date, m.conversion_hash_id
ORDER BY sequence_number ASC
) AS filtered_sequence_number,
-- Count of remaining events
COUNT(*) OVER (PARTITION BY m.date, m.conversion_hash_id) AS filtered_total_events,
m.rb_sync_id,
m.updated_at
FROM log_level_mta m
-- Apply custom attribution window
LEFT JOIN lookback_config c
ON lower(m.tier_1) = lower(c.tier_1)
AND lower(m.tier_2) = lower(c.tier_2)
WHERE
m.date >= {start_date}
AND m.date <= {end_date}
-- Apply the channel-specific attribution window
-- If no configuration exists for a channel, use a large fallback window
AND timestamp_events >= DATEADD(
day,
-COALESCE(c.lookback_window_days, 10000),
timestamp_conv
)
)
SELECT *
FROM filtered_mta;
```
# Enrich Attribution with Platform Data
Source: https://data-foundation.rockerbox.com/warehousing/analytics-join-attribution-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_` | `mta_tiers_join_key` | Join key linking platform data to Rockerbox attribution |
| `platform_` | `clicks`, `impressions` | Platform-reported engagement metrics |
| `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**
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.
```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.
# Build CPA/ROAS From Log Level MTA
Source: https://data-foundation.rockerbox.com/warehousing/analytics-kpis-from-log-mta
Customize attribution logic in the log-level MTA dataset and rebuild aggregate marketing performance reporting such as CPA and ROAS.
# Description
This analysis enables you to customize attribution logic in the **Log Level MTA dataset** and rebuild aggregate marketing performance reporting in your warehouse.
By adjusting log-level attribution records (for example modifying attribution windows, excluding view-through interactions, or adjusting revenue values), you can generate customized performance metrics such as **conversions, revenue, CPA, and ROAS** that better reflect your business context.
The output reproduces the **Buckets Breakdown reporting structure**, grouping attributed conversions, revenue, and spend by marketing channel hierarchy.
***
# When to Use This Analysis
* Adjust attribution logic to better reflect your marketing measurement strategy.
* Shorten or modify attribution windows for specific channels.
* Remove view-through touchpoints to build **click-based attribution metrics**.
* Adjust order-level revenue based on factors not passed to Rockerbox (e.g., margins, discounts, VAT).
* Apply custom attribution calibration based on experiments or internal heuristics.
* Rebuild **aggregate marketing KPIs (CPA, ROAS)** after modifying attribution logic.
***
# Source Data
This analysis combines attribution data from the **Log Level MTA dataset** with spend data from the **Buckets Breakdown dataset**.
| Dataset | Field | Description |
| --------------- | --------------------------------------------------------------------------------- | ----------------------------------------------------------- |
| `log_level_mta` | `date` | Date of the attributed touchpoint |
| `log_level_mta` | `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy |
| `log_level_mta` | `spend_key` | Identifier used to link attribution data to marketing spend |
| `log_level_mta` | `first_touch`, `last_touch`, `even`, `normalized` | Attributed conversions under different attribution models |
| `log_level_mta` | `revenue_first_touch`, `revenue_last_touch`, `revenue_even`, `revenue_normalized` | Attributed revenue under each attribution model |
| `log_level_mta` | `new_to_file` | Indicator for new customers used to calculate NTF metrics |
| `aggregate_mta` | `platform_join_key` | Spend identifier used to match marketing placements |
| `aggregate_mta` | `included_spend` | Marketing spend for the placement |
| `aggregate_mta` | `platform` | Advertising platform associated with the placement |
| `aggregate_mta` | `date` | Date associated with the marketing spend |
***
# Key Metrics
| Metric | SQL Logic | Description |
| ----------------- | --------------------------------------------------- | ---------------------------------------------- |
| `conversions` | `SUM(even)` | Conversions attributed to marketing placements |
| `revenue` | `SUM(revenue_even)` | Revenue attributed to marketing placements |
| `spend` | `SUM(included_spend)` | Marketing spend from advertising platforms |
| `CPA` | `spend / conversions` | Cost per acquisition |
| `ROAS` | `revenue / spend` | Return on ad spend |
| `ntf_conversions` | `SUM(CASE WHEN new_to_file=1 THEN conversions END)` | Conversions from new customers |
***
# Example Queries
## Approach 1: Join Attribution with Spend
This query:
* aggregates attribution from `log_level_mta`
* joins spend from `aggregate_mta`
* outputs conversions, revenue, and spend by marketing tier
```sql theme={null}
WITH log_level_mta_pivot AS (
-- Aggregate attribution from log-level MTA
SELECT
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
spend_key,
-- First Touch Attribution
SUM(
CASE
WHEN new_to_file = 1 THEN first_touch
ELSE 0
END
) AS ntf_first_touch,
SUM(first_touch) AS first_touch,
SUM(
CASE
WHEN new_to_file = 1 THEN revenue_first_touch
ELSE 0
END
) AS ntf_revenue_first_touch,
SUM(revenue_first_touch) AS revenue_first_touch,
-- Last Touch Attribution
SUM(
CASE
WHEN new_to_file = 1 THEN last_touch
ELSE 0
END
) AS ntf_last_touch,
SUM(last_touch) AS last_touch,
SUM(
CASE
WHEN new_to_file = 1 THEN revenue_last_touch
ELSE 0
END
) AS ntf_revenue_last_touch,
SUM(revenue_last_touch) AS revenue_last_touch,
-- Even Attribution
SUM(
CASE
WHEN new_to_file = 1 THEN even
ELSE 0
END
) AS ntf_even,
SUM(even) AS even,
SUM(
CASE
WHEN new_to_file = 1 THEN revenue_even
ELSE 0
END
) AS ntf_revenue_even,
SUM(revenue_even) AS revenue_even,
-- Modeled Attribution
SUM(
CASE
WHEN new_to_file = 1 THEN normalized
ELSE 0
END
) AS ntf_normalized,
SUM(normalized) AS normalized,
SUM(
CASE
WHEN new_to_file = 1 THEN revenue_normalized
ELSE 0
END
) AS ntf_revenue_normalized,
SUM(revenue_normalized) AS revenue_normalized
FROM log_level_mta
WHERE
date >= {{start_date}}
AND date <= {{end_date}}
GROUP BY
1,2,3,4,5,6,7
),
aggregate_mta_spend AS (
-- Gather spend from buckets breakdown
SELECT
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
platform,
platform_join_key,
included_spend AS spend
FROM mta_tiers
WHERE
date >= {{start_date}}
AND date <= {{end_date}}
--choose the same conversion event matching the log level MTA table
AND conversion_event_id = {{conversion identifier}}
AND included_spend > 0
)
-- Combine attribution with spend
SELECT
COALESCE(l.date, s.date) AS date,
COALESCE(l.tier_1, s.tier_1) AS tier_1,
COALESCE(l.tier_2, s.tier_2) AS tier_2,
COALESCE(l.tier_3, s.tier_3) AS tier_3,
COALESCE(l.tier_4, s.tier_4) AS tier_4,
COALESCE(l.tier_5, s.tier_5) AS tier_5,
COALESCE(l.spend_key, s.platform_join_key) AS spend_key,
s.platform,
COALESCE(s.spend,0) AS spend,
COALESCE(l.first_touch,0) AS first_touch,
COALESCE(l.revenue_first_touch,0) AS revenue_first_touch,
COALESCE(l.last_touch,0) AS last_touch,
COALESCE(l.revenue_last_touch,0) AS revenue_last_touch,
COALESCE(l.even,0) AS even,
COALESCE(l.revenue_even,0) AS revenue_even,
COALESCE(l.normalized,0) AS normalized,
COALESCE(l.revenue_normalized,0) AS revenue_normalized
FROM log_level_mta_pivot l
FULL OUTER JOIN aggregate_mta_spend s
ON s.platform_join_key = l.spend_key
AND s.date = l.date;
```
## Approach 2: Combine Attribution and Spend Using a Union
This approach unions attribution data and spend data into a single dataset. Metrics are then aggregated downstream in a BI layer or additional SQL step.
```sql theme={null}
SELECT * FROM log_level_mta_pivot
UNION ALL
SELECT * FROM aggregate_mta_spend;
```
After the union, aggregate metrics by:
```
date
tier_1
tier_2
tier_3
tier_4
tier_5
```
to produce final marketing performance KPIs.
# Evaluate Meta Click vs. View Through Attribution
Source: https://data-foundation.rockerbox.com/warehousing/analytics-mta-eval-clicks-views
This page provides an overview of how to evaluate the impact of view and clicks on Meta attribution.
# When to Use This Analysis
* Understand the contribution of views vs clicks on your Meta CPA / ROAS
* Evaluate the incremental impact of Meta view-through
\--
# Source Data
This analysis primarily uses the following fields from the Log Level MTA schema:
| Field | Description |
| ------------------------------------------------- | ------------------------------------------------------------------------ |
| `date` | Event date (used for filtering) |
| `marketing_type` | Indicates the type of ad interaction - click or view |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Channel hierarchy dimensions |
| `first_touch`, `last_touch`, `even`, `normalized` | Fractional conversion credit based on the chosen attribution methodology |
The `marketing_type` fields indicates the type of ad interaction
| Marketing Type | Description |
| -------------------------- | --------------------------------------------------- |
| `onsite`, `facebook_click` | User clicks on an Meta ad on the path to conversion |
| `facebook_view` | User views an ad on the path to conversion |
***
# Example Query #1: View Through vs. Click Through Attributed Conversions and Revenue
## Snowflake SQL Example
```sql theme={null}
SELECT
tier_1,
tier_2,
tier_3,
sum(CASE WHEN marketing_type != 'facebook_view' THEN even END) AS even_click,
sum(CASE WHEN marketing_type = 'facebook_view' THEN even END) AS even_view,
sum(even) AS even_total,
sum(CASE WHEN marketing_type != 'facebook_view' THEN revenue_even END) AS revenue_even_click,
sum(CASE WHEN marketing_type = 'facebook_view' THEN revenue_even END) AS revenue_even_view,
sum(revenue_even) AS revenue_even_total
FROM
--replace with your mta table
where
date >= dateadd(day, -30, current_date)
--edit this with your customized Facebook reporting hierarchy to filter down to FB campaigns
AND tier_1 = 'Paid Social'
AND tier_2 = 'facebook'
GROUP BY
tier_1,
tier_2,
tier_3
ORDER BY
tier_1,
tier_2,
tier_3;
```
#### How to customize
* Add tier\_2, tier\_3, campaign, or other dimensions to increase granularity
* Change day to hour if you want higher precision
* Expand the date filter to analyze longer time windows
***
# Example Query #2: Meta View vs. Click Based ROAS/CPA
Query Steps:
* Gather view vs. click attribution per Meta AD ID
* Gather spend per Meta AD ID and join with view vs. click attribution,
* Compute CPA/ROAS and aggregate at desired reporting dimensions.
## Snowflake SQL Example
```sql theme={null}
WITH meta_view_vs_click AS (
-- STEP 1: GATHER VIEW VS CLICK ATTRIBUTION PER META AD ID
SELECT
date,
spend_key,
SUM(CASE WHEN marketing_type != 'facebook_view' THEN even END) AS even_click,
SUM(CASE WHEN marketing_type = 'facebook_view' THEN even END) AS even_view,
SUM(even) AS even_total,
SUM(CASE WHEN marketing_type != 'facebook_view' THEN revenue_even END) AS revenue_even_click,
SUM(CASE WHEN marketing_type = 'facebook_view' THEN revenue_even END) AS revenue_even_view,
SUM(revenue_even) AS revenue_even_total
FROM {log_level_mta} --replace with your table name
WHERE date >= dateadd(day, -30, current_date)
GROUP BY
date,
spend_key
),
meta_view_vs_click_spend AS (
-- STEP 2: GATHER SPEND PER META AD ID AND JOIN WITH VIEW VS CLICK ATTRIBUTION
SELECT
a.date,
a.tier_1,
a.tier_2,
a.tier_3,
a.tier_4,
a.tier_5,
a.platform_join_key,
a.platform,
a.included_spend,
m.even_click,
m.even_view,
m.even_total,
m.revenue_even_click,
m.revenue_even_view,
m.revenue_even_total
FROM aggregate_mta a
LEFT JOIN meta_view_vs_click m
ON m.date = a.date
AND m.spend_key = a.platform_join_key
WHERE
a.date >= dateadd(day, -30, current_date)
AND a.platform ILIKE '%facebook%'
AND a.included_spend > 0
AND a.conversion_event_id = --identifier of the conversion you are evaluating
)
-- STEP 3: COMPUTE CPA/ROAS AND AGGREGATE AT DESIRED REPORTING DIMENSIONS
SELECT
tier_1,
tier_2,
tier_3,
SUM(included_spend) AS spend,
SUM(even_click) AS click_conversions,
SUM(even_total) AS all_conversions,
SUM(revenue_even_click) AS click_revenue,
SUM(revenue_even_total) AS all_revenue,
SUM(spend) / NULLIF(SUM(even_click), 0) AS click_cpa,
SUM(spend) / NULLIF(SUM(even_total), 0) AS all_cpa,
SUM(revenue_even_click) / NULLIF(SUM(spend), 0) AS click_roas,
SUM(revenue_even_total) / NULLIF(SUM(spend), 0) AS all_roas
FROM meta_view_vs_click_spend
GROUP BY
tier_1,
tier_2,
tier_3
ORDER BY
tier_1,
tier_2,
tier_3;
```
# Use Cases Overview
Source: https://data-foundation.rockerbox.com/warehousing/analytics-overview
Common analytics workflows using Rockerbox data in your warehouse.
Recreate the UI report in your warehouse to evaluate aggregate performance or just as an onboarding exercise.
Adjust attribution window on the log-level MTA dataset and redistribute attribution credit across remaining touchpoints.
Build aggregate marketing performance KPIs such as CPA and ROAS off of log level MTA.
Combine Rockerbox attribution metrics with platform-reported performance metrics to enrich deduplicated attribution with platform reporting.
Evaluate the impact of different ad interaction types on Meta attribution.
Evaluate time to conversion across different channels.
Extract product-level context stored to support product-level performance and revenue analysis.
Analyze how marketing channels drive site traffic, sessions, and user engagement.
# Product Breakdowns
Source: https://data-foundation.rockerbox.com/warehousing/analytics-product-breakdowns
Extract product-level context stored in the conversions dataset to support product-level performance and revenue analysis.
# Description
Product information can be captured in the Rockerbox **Conversions dataset** within the `additional_attributes` column. This field stores supplemental conversion metadata in **JSON format**, including product-level details passed during the conversion event.
To analyze product-level performance in your warehouse, you must extract the `products` field from the JSON structure using the appropriate JSON parsing functions supported by your data warehouse.
Conversions with product context can then be joined against attribution data in the log level MTA schema using the `date` and `conversion_key`
***
# When to Use This Analysis
* Analyze **product-level performance** from conversion events tracked by Rockerbox.
* Extract product metadata captured during the conversion event.
* Support reporting that breaks out **revenue and conversions by product**.
* Build downstream product-level reporting or join conversion data with product catalogs.
***
# Source Data
This analysis uses fields from the **Conversions dataset**.
| Field | Description |
| -------------------------------- | ----------------------------------------------------------------------------- |
| `conversion_id` | Unique identifier for each conversion event |
| `date` | Date the conversion occurred |
| `additional_attributes` | JSON column containing additional metadata captured with the conversion event |
| `additional_attributes.products` | JSON field containing product information passed with the conversion |
The `products` field must be extracted from the JSON structure before it can be used in reporting.
***
# Key Metrics
| Metric | SQL Logic | Description |
| ---------- | -------------------- | -------------------------------------------------------- |
| `products` | Extracted JSON field | Product information associated with the conversion event |
Note: Product-level revenue allocation may require additional SQL processing if multiple products are associated with a single order.
***
# Example Queries
## Snowflake
Extract the `products` field from the JSON column.
```sql theme={null}
SELECT
*,
additional_attributes:products::varchar AS products
FROM conversions_table;
```
## Redshift
Use the JSON extraction function to retrieve the product field.
```sql theme={null}
SELECT
*,
json_extract_path_text(additional_attributes, 'products')::varchar AS products
FROM conversions_table;
```
## BigQuery
Use JSON functions to extract the product field.
```sql theme={null}
SELECT
*,
JSON_EXTRACT_SCALAR(additional_attributes, '$.products') AS products
FROM conversions_table;
```
# Replicate Cross-Channel Attribution Report
Source: https://data-foundation.rockerbox.com/warehousing/analytics-replicate-cross-channel-ui-report
Recreate the Rockerbox Cross-Channel Attribution report in your warehouse to analyze spend, conversions, CPA, and ROAS across marketing placements.
# When to Use This Analysis
* Evaluate **marketing performance across channels, campaigns, and placements** using the Rockerbox tier hierarchy.
* Analyze **CPA and ROAS across marketing placements** using different attribution methodologies.
* Segment attribution performance by **new vs. repeat customers**.
* Customize reporting beyond the UI by modifying **granularity, attribution model, and reporting time periods**.
* If you're looking to onboard onto the `aggregate_mta` schema and want to be able to sanity check your output against the Rockerbox UI.
***
# Source Data
This analysis uses fields from the **Aggregate MTA** schema, which contains attributed conversions, revenue, and spend aggregated by marketing dimensions.
| Field | Description |
| ------------------------------------------------ | ------------------------------------------------------- |
| `date` | Date of the attributed conversion |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy used to segment performance |
| `even` | Even-weight attributed conversions |
| `revenue_even` | Even-weight attributed revenue |
| `included_spend` | Marketing spend associated with the placement |
***
# Key Metrics
| Metric | SQL Logic | Description |
| ------------- | ------------------------------------------ | --------------------------------------------- |
| `conversions` | `SUM(even)` | Even-weight attributed conversions |
| `revenue` | `SUM(revenue_even)` | Even-weight attributed revenue |
| `spend` | `SUM(included_spend)` | Marketing spend associated with the placement |
| `cpa` | `SUM(spend) / NULLIF(SUM(even),0)` | Cost per acquisition |
| `roas` | `SUM(revenue_even) / NULLIF(SUM(spend),0)` | Return on ad spend |
***
# Attribution Model Reference
Rockerbox supports multiple attribution methodologies. Each method corresponds to a different set of columns in the dataset.
| Attribution Model | Conversions Column | Revenue Column | Description |
| ------------------- | ------------------ | --------------------- | ---------------------------------------------------------------------------------- |
| Even Weight | `even` | `revenue_even` | Distributes conversion credit evenly across all touchpoints in the conversion path |
| Modeled Multi-Touch | `normalized` | `revenue_normalized` | Uses Rockerbox's modeled attribution weights across touchpoints |
| First Touch | `first_touch` | `revenue_first_touch` | Assigns 100% of conversion credit to the first marketing touchpoint |
| Last Touch | `last_touch` | `revenue_last_touch` | Assigns 100% of conversion credit to the last marketing touchpoint |
For **new customer attribution**, use the corresponding **new-to-file (NTF)** fields:
| Attribution Model | Conversions Column | Revenue Column |
| ------------------- | ------------------ | ------------------------- |
| Even Weight | `ntf_even` | `ntf_revenue_even` |
| Modeled Multi-Touch | `ntf_normalized` | `ntf_revenue_normalized` |
| First Touch | `ntf_first_touch` | `ntf_revenue_first_touch` |
| Last Touch | `ntf_last_touch` | `ntf_revenue_last_touch` |
Repeat customer metrics can be derived by subtracting **new customer metrics** from **all customer metrics**.
***
# Example Queries (Snowflake)
## Cross-Channel Attribution Performance
This query replicates the **Cross-Channel Attribution report**, showing marketing spend and conversions mapped to marketing placements.
```sql theme={null}
SELECT
tier_1,
tier_2,
tier_3,
tier_4,
tier_5,
SUM(even) AS conversions,
SUM(revenue_even) AS revenue,
SUM(included_spend) AS spend,
SUM(included_spend) / NULLIF(SUM(even),0) AS cpa,
SUM(revenue_even) / NULLIF(SUM(included_spend),0) AS roas
FROM
aggregate_mta
WHERE
date >= dateadd(day, -30, CURRENT_DATE)
AND date <= dateadd(day, -1, CURRENT_DATE)
AND conversion_event_id = {{insert conversion id}}
GROUP BY
tier_1,
tier_2,
tier_3,
tier_4,
tier_5;
```
### How to Customize
| Adjustment | How |
| --------------------------------------- | --------------------------------------------------------------------------------------------------- |
| Change reporting granularity | Adjust the `tier_1`–`tier_5` fields in the `SELECT` and `GROUP BY` clauses |
| Use a different attribution methodology | Replace attribution fields in the query using the columns listed in the Attribution Model Reference |
| Analyze new customer attribution | Use new-to-file fields such as `ntf_even`, `ntf_normalized`, `ntf_first_touch`, `ntf_last_touch` |
| Analyze repeat customers | Calculate repeat metrics by subtracting new customer metrics from total metrics |
| Report by time period | Convert `date` to daily, weekly, monthly, quarterly, or yearly time buckets |
| Extend analysis window | Modify the `date` filter in the `WHERE` clause |
# Session, Visitor, or Traffic Analysis
Source: https://data-foundation.rockerbox.com/warehousing/analytics-site-traffic
Analyze how marketing channels drive site traffic, sessions, and user engagement using the Clickstream dataset.
# When to Use This Analysis
* Evaluate how marketing activity (promotions, campaigns, spend changes) impacts site traffic.
* Example: What is the impact of my email blast in driving traffic to the site?
* Compare channels based on traffic efficiency metrics such as click-through rate, cost per click, or click-based conversion rate.
* Example: Between channels with similar CPA, which drives traffic more efficiently?
* Get an early signal on the performance of new channels before conversions occur.
* Example: A newly launched channel has not yet driven conversions — is it generating comparable traffic?
* Understand how user engagement varies by marketing channel.
* Example: Which channels drive more engaged sessions or product interactions?
* Compare behavior between converters and non-converters to identify funnel drop-off patterns.
* Example: Do users arriving from channel X abandon carts more frequently than those from channel Y?
***
# Source Data
This analysis uses the following fields from the **Clickstream** schema:
| Field | Description |
| ------------------------------------------------ | ---------------------------------------------------------------------------------- |
| `date` | Event date used to aggregate traffic metrics and filter the analysis window |
| `uid` | Rockerbox user ID cookie used to count unique users |
| `session_start` | Binary flag indicating the first event of a session. Used to count unique sessions |
| `tier_1`, `tier_2`, `tier_3`, `tier_4`, `tier_5` | Marketing channel hierarchy used to segment traffic attribution |
***
# Key Metrics
| Metric | Description |
| --------------- | --------------------------------------------------- |
| `users` | Count of unique visitors (`COUNT(DISTINCT uid)`) |
| `sessions` | Total number of sessions (`SUM(session_start)`) |
| `event_count` | Total number of on-site events (`COUNT(*)`) |
| `session_count` | Number of sessions attributed to marketing channels |
***
# Example Queries
## Attributed Sessions by Marketing Channel
The count of unique sessions attributed to click-based marketing touchpoints.
```sql theme={null}
SELECT
SUM(session_start) AS session_count,
date,
tier_1,
tier_2,
tier_3,
tier_4,
tier_5
FROM `database.schema.table`
WHERE
date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
AND session_start = 1
GROUP BY date, tier_1, tier_2, tier_3, tier_4, tier_5;
```
## Site Traffic and Events Over Time
Tracks overall site traffic trends, including users, sessions, and event activity.
```sql theme={null}
SELECT
date,
COUNT(DISTINCT uid) AS users,
SUM(CASE WHEN session_start = '1' THEN 1 ELSE 0 END) AS sessions,
COUNT(*) AS event_count
FROM `database.schema.table`
WHERE
AND date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
GROUP BY
date
ORDER BY
date;
```
# Time to Conversion
Source: https://data-foundation.rockerbox.com/warehousing/analytics-time-to-convert
This guide explains how to compute **time to conversion** using the **Log Level MTA** dataset.
# When to Use This Analysis
Time to conversion analysis is particularly useful for:
* Setting retargeting windows
* Determining attribution lookback windows
* Evaluating upper-funnel vs lower-funnel performance
* Understanding purchase latency
# Source Data
This analysis uses the following fields from the Log Level MTA schema:
| Field | Description |
| ---------------------------- | --------------------------------------------------------------------------- |
| `timestamp_events` | Timestamp of the marketing touchpoint |
| `timestamp_conv` | Timestamp of the conversion event |
| `first_touch` | Flag indicating whether the touchpoint was the first in the conversion path |
| `tier_1`, `tier_2`, `tier_3` | Channel hierarchy dimensions |
| `date` | Event date (used for filtering) |
Time to conversion is computed as the difference between:
`timestamp_conv` - `timestamp_events`
The exact timestamp function depends on your data warehouse:
| Warehouse | Function |
| --------- | ----------------------------------- |
| Snowflake | `datediff()` or `timestampdiff()` |
| Redshift | `datediff()` |
| BigQuery | `date_diff()` or `timestamp_diff()` |
***
# Example 1: Average Time to Conversion
### First Touch vs Any Touch
This query computes:
* Average time to convert from the **first touch**
* Average time to convert from **any touchpoint**
* Grouped by channel (`tier_1`)
💡 **Note:** Time to convert is typically expressed in **days**, but can be calculated in hours or minutes if desired.
### Snowflake Example
```sql theme={null}
select
-- Grouping dimension
tier_1,
-- Average time to conversion (First Touch only)
avg(
case
when first_touch = 1
then datediff(day, timestamp_events, timestamp_conv)
end
) as first_touch_time_to_convert_days,
-- Average time to conversion (Any Touch)
avg(
datediff(day, timestamp_events, timestamp_conv)
) as any_touch_time_to_convert_days
from ..
where
date >= dateadd('day', -30, current_date)
group by 1
order by 1;
```
#### How to customize
* Add tier\_2, tier\_3, campaign, or other dimensions to increase granularity
* Change day to hour if you want higher precision
* Expand the date filter to analyze longer time windows
***
# Example 2: Time to Convert Bins
This query counts the number of touchpoints that fall into predefined time-to-conversion bins.
Typical reporting buckets:
* 0–7 days
* 8–14 days
* 15–30 days
* 31–60 days
* 61–90 days
* Greater than 90 days
### Snowflake Example
```sql theme={null}
select
tier_1,
sum(case
when datediff(day, timestamp_events, timestamp_conv) between 0 and 7
then 1 else 0
end) as "0_7D",
sum(case
when datediff(day, timestamp_events, timestamp_conv) between 8 and 14
then 1 else 0
end) as "8_14D",
sum(case
when datediff(day, timestamp_events, timestamp_conv) between 15 and 30
then 1 else 0
end) as "15_30D",
sum(case
when datediff(day, timestamp_events, timestamp_conv) between 31 and 60
then 1 else 0
end) as "31_60D",
sum(case
when datediff(day, timestamp_events, timestamp_conv) between 61 and 90
then 1 else 0
end) as "61_90D",
sum(case
when datediff(day, timestamp_events, timestamp_conv) > 90
then 1 else 0
end) as ">90D"
from ..
where date >= dateadd('day', -30, current_date)
group by 1
order by 1;
```
# Connect to Google BigQuery
Source: https://data-foundation.rockerbox.com/warehousing/bigquery
Setup guide to share Rockerbox schemas with your BigQuery project.
## Pre-Requisites
* Google Cloud Platform (GCP) account
* GCP **project** with **BigQuery** resource enabled
* Chosen **region** for the dataset (cloud storage bucket) and BigQuery to reside
* A user with **admin** access to the project’s BigQuery resource
***
## Step 1: Create a BigQuery Dataset
Create a **BigQuery dataset** in your target GCP project. We recommend colocating within a single region, but support multi-region setups.
> ⚠️ **Note on regions:** Google recommends colocating your BigQuery dataset within a single region for better performance. See [Google documentation](https://cloud.google.com/bigquery/docs/external-tables) for details.
***
## Step 2: Connect Rockerbox to BigQuery
1. Open the [Warehousing setup page](https://app.rockerbox.com/v3/data/exports/data_warehouse) Rockerbox and choose **BigQuery** → **Connect to BigQuery**.
2. Enter your **dataset region**, **project ID**, and **dataset ID**, then click **SetupBigQuery**.
***
## Step 3: Copy the Rockerbox Service Account
After initialization, Rockerbox displays a **unique service account email**. Click the icon next to it to copy the address.
***
## Step 4: Grant Dataset Permissions in GCP
In GCP, grant that service account with the **DataEditor role** `roles/bigquery.dataEditor` to your BigQuery dataset so Rockerbox can create tables.
***
## Step 5: Grant Team Access (Optional)
Back in the Rockerbox UI, add **users, groups, and/or service accounts** from your organization that should access the shared data in BigQuery.
***
## Step 6: Notify Rockerbox (One-Time Enablement)
Tell your Rockerbox support rep that you’ve completed the steps above. Due to a **BigQuery limitation**, Rockerbox must **sync underlying datasets** first before you can create tables in the Rockerbox UI. Rockerbox support will confirm when you can proceed to the next step.
***
## Step 7: Sync Rockerbox Data Sets
* Choose which datasets to share:
* **Platform Data**: Select the ad platforms you actively use.
* **Rockerbox Data**: Select **Conversion** and any **Log Level MTA** datasets for each conversion event that you need.
* Click **Sync this dataset** when ready.
📦 `aggregate_mta` and `taxonomy_lookup` tables are automatically created in the data share.
### ⚠️ Backfill Notes
* **Platform Performance Schemas**: No data is automatically backfilled. This can be backfilled on a limited basis upon request to `support@rockerbox.com`.
* **Rockerbox First Party Data Schemas**: For each conversion dataset, Rockerbox will backfill based on the conversion event “First Reporting Date” in Rockerbox\
💡 If no date is set, Rockerbox backfills one day of data. This process can take up to **24 hours**.
***
## Step 8: Query your new data tables
### Sample queries: `aggregate_mta` table
Test setup
```sql theme={null}
SELECT * FROM `..aggregate_mta` LIMIT 1;
```
Report even weight attributed conversions and spend by conversion event + tier\_1 (most broad reporting taxonomy dimension)
```sql theme={null}
SELECT
conversion_event_name,
tier_1,
SUM(even) AS conversions_even,
SUM(included_spend) AS spend
FROM `..aggregate_mta`
WHERE
date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
AND date < CURRENT_DATE();
```
***
## BigQuery Table Limitations
For **MTA** and **Conversions** schemas in BigQuery, nested columns—`url_parameters`, `utm_parameters`, and `additional_attributes`—won’t auto-expand when new parameters appear.
Contact Rockerbox if you need newly added parameters reflected in those fields.
# Getting Started
Source: https://data-foundation.rockerbox.com/warehousing/quickstart
## 1. Contact your Rockerbox representative to enable warehouse integration as a paid add-on feature.
## 2. Complete platform-specific setup steps:
Connect your Snowflake account
Set up project and dataset permissions
Configure IAM roles and external schema
> 🔑 If your preferred platform lacks a native Rockerbox integration, then Rockerbox can provision a [Snowflake Reader Account](https://docs.snowflake.com/en/user-guide/data-sharing-reader-create) to enable data egress to the platform of your choice.
4. Select data sets to sync.
5. Begin leveraging Rockerbox data in your warehouse.
# Connect to Amazon Redshift
Source: https://data-foundation.rockerbox.com/warehousing/redshift
Setup guide to share Rockerbox schemas with your Redshift instance.
## Pre-Requisites
* An AWS account
* Your Redshift region
* A user to configure IAM roles
* A user with Redshift admin access
***
## Step 1: Create an IAM Role
1. Open the [Warehousing setup page](https://app.rockerbox.com/v3/data/exports/data_warehouse) Rockerbox and choose **Redshift** → **Connect to Redshift**.
2. Go into **IAM service** in the AWS Console.
3. Select Roles from the **Access Management** sidebar on the left.
4. Click **Create Role** from the top right portion of the page.
5. Keep the trusted entity service as the default **AWS Service**. Towards the bottom of the page, select **Redshift** from the dropdown list of **“Use Cases for Other AWS Services”** section. Then select **Redshift - Customizable** from the options.
6. You’ll be directed to the **Permissions** page. Don’t add any permissions here, and click **Next**.
7. Name this role **“RockerboxRedshiftRole”** , and use **“Assumes Rockerbox Role to Access Rockerbox Shared Tables”** for the description field.
8. Find the **“RockerboxRedshiftRole”** role you just created in the **AWS Roles** page and click into it.
9. Copy the **ARN value** and return to the **Redshift Setup page in the Rockerbox UI** where you’ll need to enter this **ARN value**.
***
## Step 2: Add an Inline Policy to the IAM Role
1. Find the **“RockerboxRedshiftRole”** role you previously created in the **AWS Roles** page and click into it.
2. Click on **Add Permissions** and select **Create Inline Policy**.
3. The default selection on the next screen is **“Visual Editor”**. Select **“JSON”** instead.
4. Copy the **Inline Policy** displayed on your **Redshift Setup UI** and paste it into the **JSON** field and then click **“Review Policy”**.
5. Name this policy **“RockerboxAccess”** and then click **“Create Policy”**.
***
## Step 3: Associate IAM Role to Redshift cluster and run an External Schema Command.
1. Go into **Amazon Redshift** service in the **AWS Console**.
2. Select your **Redshift cluster** from the **Provisioned Clusters Dashboard**.
3. Go to the properties tab inside your Redshift cluster.
4. Go to the **Cluster Permissions** section and click on **Manage IAM Roles** and then select **Associate IAM Roles**.
5. Select the **“RockerboxRedshiftRole”** that you previously created in the pop up window and then click on **Associate IAM Roles**.
6. Wait for the role to sync in the **Associated IAM Roles** list under **Cluster Permissions** section.
7. Go to **query editor v2**.
8. Copy the **External Schema Statement** displayed on your **Redshift Setup UI**, paste it into the query editor, and click **Run**.
Data should now be available in the Rockerbox schema!
***
## Step 4: Sync Rockerbox Data Sets
* Choose which datasets to share:
* **Platform Data**: Select the ad platforms you actively use.
* **Rockerbox Data**: Select **Conversion** and any **Log Level MTA** datasets for each conversion event that you need.
* Click **Sync this dataset** when ready.
📦 `aggregate_mta` and `taxonomy_lookup` tables are automatically created in the data share.
### ⚠️ Backfill Notes
* **Platform Performance Schemas**: No data is automatically backfilled. This can be backfilled on a limited basis upon request to `support@rockerbox.com`.
* **Rockerbox First Party Data Schemas**: For each conversion dataset, Rockerbox will backfill based on the conversion event “First Reporting Date” in Rockerbox\
💡 If no date is set, Rockerbox backfills one day of data. This process can take up to **24 hours**.
***
## Step 5: Query your new data tables
### Sample queries: `aggregate_mta` table
Test setup
```sql theme={null}
SELECT * FROM `..aggregate_mta` LIMIT 1;
```
Report even weight attributed conversions and spend by conversion event + tier\_1 (most broad reporting taxonomy dimension)
```sql theme={null}
SELECT
conversion_event_name,
tier_1,
SUM(even) AS conversions_even,
SUM(included_spend) AS spend
FROM `..aggregate_mta`
WHERE
date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
AND date < CURRENT_DATE();
```
# Aggregate MTA
Source: https://data-foundation.rockerbox.com/warehousing/schema-aggregate-mta
## New Product Update
Aggregate MTA is replacing the Buckets Breakdown schema. Rockerbox will reach out to you directly when you are required to migrate to this new schema.
***
## Description
* The **Aggregate MTA** schema offers granular marketing performance reporting including spend, attributed conversions, and revenue for **all conversion events** tracked in Rockerbox.
* This dataset is aggregated for each date down to the lowest level of granularity that ad performance is tracked in Rockerbox (e.g., ad group level for Google Ads, ad level for Meta).
***
## Table Creation
This table is automatically created upon connecting Rockerbox with your supported warehouse provider.
***
## Partition Keys
`aggregate_mta` is an external table. There are two partition keys relevant for data access:
* `conversion_event_id`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Usage Notes
* Aggregate (SUM) attributed conversions and revenue and spend across relevant dimension columns (e.g., `date`, `tier_1`, `tier_2`, etc.) to compute KPIs such as CPA and ROAS.
* To only extract spend apply `WHERE included_spend > 0`
### Sample SQL (compatible with Snowflake, BigQuery, and Redshift)
```sql theme={null}
SELECT
date,
tier_1,
tier_2,
SUM(even) AS even,
SUM(revenue_even) AS revenue_even,
SUM(included_spend) AS spend
FROM aggregate_mta
GROUP BY date, tier_1, tier_2;
```
***
## Primary Key
There is no logical primary key in this table.
Each row represents metrics (spend OR conversions/revenue) aggregated by the following dimensions:
* `date`
* `platform_join_key`
* `tier_1`
* `tier_2`
* `tier_3`
* `tier_4`
* `tier_5`
Measures (spend, attributed\_conversions, attributed\_revenue) should be aggregated across this full set (or subset) of dimensions to avoid double counting.
***
## Field Reference
| Name | Description | Type |
| :------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------- |
| report | Report name | str |
| version | Schema version | str |
| advertiser | Rockerbox Account ID. Note: this static column is only visible in Snowflake integrations | str |
| currency\_code | The reporting currency for both revenue and spend | str |
| date | Date when the conversion event happened and when the ad spend was incurred | date |
| even | Conversions attributed via even weight attribution methodology | float |
| first\_touch | Conversions attributed via first touch attribution methodology | int |
| fx\_rate\_to\_usd | The exchange rate from the local currency to USD for the date. If currency\_code is USD, this value will be 1. | float |
| conversion\_event\_id | Unique ID of the conversion event being tracked in Rockerbox | int |
| conversion\_event\_name | Name of the conversion event being tracked in Rockerbox (e.g., Purchase) | str |
| last\_touch | Conversions attributed via last touch attribution methodology | int |
| normalized | Conversions attributed via multi-touch attribution model | float |
| ntf\_even | Conversions for new customers attributed via even weight attribution methodology | float |
| ntf\_first\_touch | Conversions for new customers attributed via first touch attribution methodology | int |
| ntf\_last\_touch | Conversions for new customers attributed via last touch attribution methodology | int |
| ntf\_normalized | Conversions for new customers attributed via multi-touch attribution model | float |
| ntf\_revenue\_even | Conversion revenue for new customers attributed via even weight attribution methodology | float |
| ntf\_revenue\_first\_touch | Conversion revenue for new customers attributed via first touch attribution methodology | float |
| ntf\_revenue\_last\_touch | Conversion revenue for new customers attributed via last touch attribution methodology | float |
| ntf\_revenue\_normalized | Conversion revenue for new customers attributed via multi-touch attribution model | float |
| platform | The name of the ad platform (e.g., Facebook). This is only populated for platforms with spend tracking in Rockerbox. | str |
| platform\_join\_key | The unique ID used to record spend for an advertising platform. This is typically the AD ID, or a composite identifier if the campaign types within a platform support different reporting granularities. This is only populated for platforms with spend tracking in Rockerbox. | str |
| rb\_sync\_id | The unique ID of a particular instance of a dataset; this is internal to Rockerbox | int |
| revenue\_even | Conversion revenue attributed via even weight attribution methodology | float |
| revenue\_first\_touch | Conversion revenue attributed via first touch attribution methodology | float |
| revenue\_last\_touch | Conversion revenue attributed via last touch attribution methodology | float |
| revenue\_normalized | Conversion revenue attributed via multi-touch attribution model | float |
| included\_spend | Total spend for a given line item | float |
| tier\_1 | Marketing channel categorization level 1 (most broad), as defined in your mapping rules / reporting taxonomy. For example, a link where referrer url = Google and utm\_campaign = cpc may be mapped as tier\_1 = Paid Search and tier\_2 = Google | str |
| tier\_2 | Marketing channel categorization level 2 | str |
| tier\_3 | Marketing channel categorization level 3 | str |
| tier\_4 | Marketing channel categorization level 4 | str |
| tier\_5 | Marketing channel categorization level 5 | str |
| updated\_at | Updated timestamp. Note: Rockerbox processes and publishes datasets on a conversion\_event\_id + date basis; therefore, all records data from the full date are replaced and the updated\_at timestamp will be identical for all rows for a given conversion\_event\_id and date | timestamp |
# Clickstream
Source: https://data-foundation.rockerbox.com/warehousing/schema-clickstream
## Description
* The Clickstream dataset is a user and event-level dataset that reports on every Page View and Conversion event tracked via the Rockerbox pixel on your site.
* These events are attributed back to click-based marketing touchpoints, with the Rockerbox tier structure and spend keys applied for standardization with other Rockerbox conversion datasets.
***
## Table Creation
* This table is automatically created upon activation of the clickstream feature.
* Once the Clickstream dataset has been enabled by Rockerbox, the table will appear in your data warehouse as `ON_SITE_EVENTS_ALL_PAGES`.
***
## Partition Keys
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
While data warehouses do not enforce primary key constraints, the `event_id` functions as the logical primary key for the table.
***
## Field Reference
| # | Name | Description | Type |
| -- | -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | action | Name of the raw pixel event. This includes events for all conversion segments configured in Rockerbox + a page view. | str |
| 2 | advertiser | Rockerbox Account ID | str |
| 3 | base\_id | Primary User ID | str |
| 4 | date | Date when the action occurred | date |
| 5 | engaged\_session | Binary 0 or 1 indicating a session lasting > 10 seconds (`session_max - session_min > 10`). | int |
| 6 | event\_id | A unique identifier for each action. Can be used as the primary key. | str |
| 7 | hash\_ip\_events | Hashed IP address of user for a particular action | str |
| 8 | identifier | Advertiser-specific identifier | str |
| 9 | marketing\_type | Type of marketing touchpoint. This will always be `onsite` as this dataset reflects click-based marketing events only. | str |
| 10 | onsite\_count | The total number of actions seen against a given user within a given session. | int |
| 11 | original\_url | URL of the page landing page | str |
| 12 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | int |
| 13 | report | The name of the report | str |
| 14 | request\_referrer | Page Referrer (the previous site where the user came from) | str |
| 15 | session\_id | Identifier for a session, indicated by a timestamp. A unique user session will be a combination of `session_id\|uid`, or can be identified using `session_start`. | str |
| 16 | session\_max | Timestamp of the last time a user was seen on site during a session | timestamp |
| 17 | session\_min | Timestamp of the first time a user was seen on site during a session | timestamp |
| 18 | session\_start | Binary field indicating the first event of a session. Can be used for session / visitor analysis by filtering for `session_start = 1`. | int |
| 19 | spend\_key | The ID used to pull spend from an advertising platform. This is typically the Ad ID, but may differ based on your account setup. | str |
| 20 | tier\_1 | Aligns to the 5-tiered categorization structure available in the Rockerbox UI. `tier_1` = most broad categorization (level 1). | str |
| 21 | tier\_2 | Aligns to the 5-tiered categorization structure available in the Rockerbox UI. `tier_2` = level 2 categorization (more granular than level 1). | str |
| 22 | tier\_3 | Aligns to the 5-tiered categorization structure available in the Rockerbox UI. `tier_3` = level 3 categorization. | str |
| 23 | tier\_4 | Aligns to the 5-tiered categorization structure available in the Rockerbox UI. `tier_4` = level 4 categorization. | str |
| 24 | tier\_5 | Aligns to the 5-tiered categorization structure available in the Rockerbox UI. `tier_5` = level 5 categorization. | str |
| 25 | timestamp\_action | Timestamp of when the action occurred | timestamp |
| 26 | timestamp\_event | Timestamp of when the marketing touchpoint occurred. This will only appear on the first action within a session, as the only action that will have a marketing touchpoint. The `timestamp_event` and `timestamp_action` will match. | timestamp |
| 27 | transform\_table\_id | ID associated with the Rockerbox table used to apply mappings and spend. *Closed beta feature only.* | int |
| 28 | uid | Rockerbox User ID cookie | str |
| 29 | updated\_at | Time the cache record was updated most recently | timestamp |
| 30 | user\_agent | Web identifier that includes characteristics like browser, device, operating system, and application | str |
| 31 | utm\_campaign | `utm_campaign` value parsed from the landing page URL, if present | str |
| 32 | utm\_content | `utm_content` value parsed from the landing page URL, if present | str |
| 33 | utm\_id | `utm_id` value parsed from the landing page URL, if present | str |
| 34 | utm\_medium | `utm_medium` value parsed from the landing page URL, if present | str |
| 35 | utm\_source | `utm_source` value parsed from the landing page URL, if present | str |
| 36 | utm\_term | `utm_term` value parsed from the landing page URL, if present | str |
***
## Clickstream FAQ
### How does Rockerbox handle a session that spans two dates (UTC)
Rockerbox will break a session (generating a new session\_id and date) if a user's session is active across two days. This may be common if users are active around UTC midnight.
### How does Rockerbox define a session? How can I make sure a session is unique when I query against the dataset?
* The session\_id used by Rockerbox is a timestamp of the first event in a session, vs the session\_id cached in your browser. This allows Rockerbox to maintain the same session\_id when a user opens a new tab.
* Because session\_id is defined as a timestamp, it's not unique per user. To identify all unique user sessions, join session\_id|uid or filter for session\_start = 1 in your query.
* A session "expires" after 30 minutes of inactivity. If the user is seen as active again, the session\_id will be reset.
* A session is NOT re-set if new source information is provide (for example, a UTM on an internal page)
### How do Rockerbox sessions compared to other sessions sources?
* Rockerbox's source data is pixel based events, with sessions logic layered on top of source data to group disparate actions into connected sessions. Source pixel data may differ from session source data from other providers like Shopify, GA4, Amplitude, etc.
* Rockerbox's session definiton (described above) may differ from other source session definitions and cannot be customized to match the definitions of other data providers.
### How does Rockerbox handle bot traffic
* Today, the only filtering performed on top of your raw site data is to remove any uids seen > 200x in the same day. Additional bot filtering is not applied by default, knowing that brands often prefer a custom approach to this type of filtering.
### Why are some fields for a given row in the dataset blank?
* Not all actions will have associated marketing context. Typically, only the first action in a session will pass along click-based marketing context in the URL. In your Rockerbox conversion data, the marketing context for each conversion event is carried over via this first action with marketing context. In this dataset, only the events that carry marketing context will have relevant fields populated to avoid any duplication.
### Can I see non-click marketing context?
* Non-click marketing context like view-based data from Linear TV, OTT, Display, and Social as well as other marketing context like promo code attribution or direct mail matching is not currently available in this dataset.
### How can I identify a repeat visitor?
* When a visitor returns to site, they may or make not have the same Rockerbox cookie ID (uid). To string together a user path when the uid is NOT the same, Rockerbox applies an identity resolution process to our conversion datasets. This is not currently available for this dataset.
* Users with > 200 events / day are excluded from this dataset under the assumption that these are admin users or server-side cookie IDs.
### What is an engaged session?
* An engaged session is defined by a session where the differences of the session\_max and session\_min timestamps are > 10 seconds.
* By logic, this means that a session with only 1 action cannot be an engaged session, since the session\_min and session\_max timestamps will be the same
* The engaged\_session flag carries through all events within a given session (eg sessions with 4 events that last > 10 seconds will have an engaged\_session flag on each row). To compare engaged session starts to overall session start, filter for session\_start = 1 in your queries.
### Why does the Clickstream channel attribution differ from GA4?
* Rockerbox applies custom channel rules per advertiser using custom logic and parameters beyond UTMs, which can lead to variances in attribution categorization of a session
* Most advertisers have "Last Non-Direct Click Attribution" applied in GA4, meaning Direct attribution may be overwritten by the last non-Direct marketing channel seen against the same user.
### How can I join the clickstream dataset to the Clickstream Event Paramters dataset?
* Each unique event\_id in the Clickstream dataset will have multiple rows in the Event Parameter dataset, reflecting the individual parameters passed on the pixel. To retrieve specific query\_param\_name and value details, most advertisers will
* Join on the event\_id and date
* Filter by a specific query\_param\_name
### What is each query\_param\_name?
* The query\_param\_name and value fields are parsed directly from your on-site pixels with no further modifications applied. While certain parameters are required to be passed for Rockerbox implementation, in many cases additional parameters are also provided. Questions about what values are passed and what each means will likely need to be investigated by your team as the experts on your implementation and data layer, vs by the Rockerbox team.
* If you don't see a certain query\_param\_name, check the name of the field in your implementation (ex in GTM). Otherwise, check if the query\_param\_name is passed anywhere on your pixel.
# Clickstream Event Parameters
Source: https://data-foundation.rockerbox.com/warehousing/schema-clickstream-events
## Description
* Auxiliary table containing additional parameters captured on each event tracked by the Rockerbox on-site pixel.
* Includes ecommerce context (e.g., product, revenue, order\_id) and marketing parameters parsed from landing page URLs (e.g., gclid, UTM values), passed through exactly as received from the pixel without additional processing.
***
## Table Creation
* This table is automatically created upon activation of the clickstream feature.
* Once the Clickstream dataset has been enabled by Rockerbox, the table will appear in your data warehouse as `ON_SITE_EVENTS_ALL_PAGES`.
***
## Partition Keys
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
While data warehouses do not enforce primary key constraints, the combination of `event_id` and `query_param_name` functions as the logical primary key for the table.
***
## Field Reference
| # | Name | Description | Type |
| -- | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | event\_id | A unique identifier for each action. Each event\_id from the Clickstream dataset will have multiple corresponding records in the Event Parameters table. | str |
| 2 | timestamp\_action | Timestamp of when the action occurred | timestamp |
| 3 | action | Name of the raw pixel event. This includes events for all conversion segments configured in Rockerbox + a page view. | str |
| 4 | category | Categorization of query\_param\_name based on common variables required for Rockerbox implementation. Possible Values: conversion, marketing, tracking | str |
| 5 | query\_param\_name | The name of the parameter passed on the Rockerbox pixel (ex product, utm\_medium). Each event\_id will have multiple rows with query\_param\_names and values | str |
| 6 | value | The specific value passed on the query\_param\_name. For example, the query\_param\_name = order\_id will have values = each specific order\_id. | str |
| 7 | advertiser | Rockerbox Account ID | str |
| 8 | date | Date when the action occurred | date |
| 9 | identifier | Advertiser-specific identifier | str |
| 10 | report | The name of the report | str |
| 11 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | int |
| 12 | updated\_at | Time the cache record was updated most recently | timestamp |
***
## Event categorization
Rockerbox categorizes query parameters based on common variables required for your Rockerbox implementation. This looks for an exact match of the query\_param\_name passed on your event to the below. Further customization of categories is not supported.
* **conversion:** action, email, external\_id, hash\_email, hash\_phone\_number, order\_id, phone\_number, referrer, revenue, script\_version, sessionid, source, uid, url
* **marketing:** contains 'utm', contains 'clid', query string parameter used for Rockerbox's standard integrations
* **tracking:** all other
# Conversion Event Metadata
Source: https://data-foundation.rockerbox.com/warehousing/schema-conversion-event-metadata
## Description
* Contains metadata for all conversion events tracked in Rockerbox.
* Provides unique identifiers and human-readable names for conversion events.
* Reference table for filtering attribution data by specific conversion events.
* Includes event lifecycle flags (active/deleted status) and the first reporting date in Rockerbox for each conversion event.
***
## Schema Structure
* **`conversion_event_metadata`**: Single lookup table containing conversion event definitions and metadata.
***
## Usage Notes
* **Filtering Attribution Data**: Use `conversion_event_id` to filter `log_mta` and other attribution tables to specific conversion types (e.g., Purchase, Add to Cart).
* **Event Discovery**: Query this table to find available conversion events and their IDs before filtering attribution data.
* **Active Events**: Filter by `active = true` to see currently tracked conversion events.
* **Query Pattern**: Join with attribution tables using `conversion_event_id` to add human-readable event names to your results.
***
## Table Creation
This table is automatically created upon connecting Rockerbox with your supported warehouse provider.
***
## Logical Primary Key
* `conversion_event_id` — Unique identifier for each conversion event type
***
## Field Reference
### Table: `conversion_event_metadata`
Metadata for all conversion events tracked in Rockerbox.
| Name | Description | Type |
| ----------------------- | ---------------------------------------------------------------------------------------- | ---- |
| conversion\_event\_id | Unique identifier for the conversion event type | str |
| conversion\_event\_name | Human-readable name of the conversion event (e.g., "Purchase - Pixel", "Add to Cart") | str |
| first\_reporting\_date | Date when this conversion event first started reporting data in Rockerbox | date |
| active | `true` if the conversion event is currently being tracked, `false` if tracking is paused | bool |
| deleted | `true` if the conversion event has been deleted, `false` otherwise | bool |
# Log Conversions
Source: https://data-foundation.rockerbox.com/warehousing/schema-log-conversion
## Description
* The Conversion Data schema contains one row for each instance of a conversion event tracked in Rockerbox.
* It includes detailed information about the conversion event including user identifiers, timestamps, order metadata, geographic context, and any other contextual information passed to Rockerbox via conversion event tracking or batch files (flat files).
***
## Schema Structure
* Conversion data is split into two tables with a 1:1 relationship:
* **`log_conversions`**: Core conversion event data including geographic, device, and revenue information
* **`log_conversions_user_identifiers`**: User identifier and PII data (emails, phone numbers, IP addresses, etc.)
* Both tables report data for ALL conversion events tracked in Rockerbox (e.g., Purchase, Add to Cart).
***
## Usage Notes
* **Privacy & Compliance**: User identifiers and PII are isolated in `log_conversions_user_identifiers` to enable stricter access controls and comply with privacy regulations.
* **Query Performance**: When PII is not needed for analysis, query only `log_conversions` to reduce data scanned and improve performance
* **Geographic Analysis**: Use `ip_*` fields for geolocation-based analysis depending on conversion IP address; customer-provided fields (`city`, `state`, `zip`) will depend on the user address information provided on the conversion event
* **Revenue Reporting**: Use `revenue_usd` for standardized reporting; `original_revenue` and `original_currency` preserve the raw conversion data
* **User Matching**: Multiple hash formats (email, phone, IP, address) support identity resolution and audience building while preserving privacy
* **Complete Conversion Record**: Join both tables when you need the full context of who converted and the conversion details
***
## Table Creation
These tables are automatically created upon connecting Rockerbox with your supported warehouse provider.
***
## Table Relationship
The two tables have a **1:1 relationship** based on the `log_conversion_id` field:
* Each record in `log_conversions` has exactly one corresponding record in `log_conversions_user_identifiers`
* Join the tables using: `log_conversion_id`
* This separation allows for flexible data governance and access control of personally identifiable information (PII)
**Example Join:**
```sql theme={null}
SELECT
c.*,
u.order_id,
u.base_id,
u.uid,
u.email,
u.phone_number
FROM log_conversions c
LEFT JOIN log_conversions_user_identifiers u
ON c.log_conversion_id = u.log_conversion_id
AND c.conversion_event_id = u.conversion_event_id --Include partition keys for optimal query performance
AND c.date = u.date --Include partition keys for optimal query performance
WHERE c.timestamp_conv >= '2026-01-01'
```
***
## Partition Keys
These tables are implemented as external tables. Always leverage partition keys when querying these tables to improve query performance:
**Log Conversions**:
* `log_conversions.conversion_event_id`
* `log_conversions.date`
**Log Conversions User Identifiers**:
* `log_conversions_user_identifiers.conversion_event_id`
* `log_conversions_user_identifiers.date`
**Note**: The `conversion_event_id` in these tables references `conversion_event_metadata.conversion_event_id`. Use `conversion_event_metadata` to find valid IDs before filtering.
***
## Logical Primary Key
### `log_conversions`
* `log_conversion_id` — Unique identifier for each conversion event record
### `log_conversions_user_identifiers`
* `log_conversion_id` — Unique identifier matching the corresponding record in `log_conversions`
***
## Field Reference
### Table: `log_conversions`
Core conversion event data including geographic, device, and revenue information.
| Name | Description | Type |
| ----------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| conversion\_event\_id | Partition Key - unique identifier of the conversion event tracked in Rockerbox | str |
| date | Partition Key - date that the conversion occured | str |
| log\_conversion\_id | Unique identifier for this conversion event record | str |
| action | Name of the conversion event (e.g., `purchase`, `action=Viewed Product`). This is often overloaded with additional context for segmentation (e.g., `purchase..`) | str |
| original\_currency | Original currency code of the conversion revenue before conversion to USD | str |
| pseudonymized\_user\_id | Pseudonymized user identifier | |
| city | City provided by the customer during conversion | str |
| country | Country provided by the customer during conversion | str |
| country\_code | Country code provided by the customer during conversion | str |
| dma\_id | Designated Market Area (DMA) ID provided by the customer | str |
| ip\_city | City derived from IP address geolocation at time of conversion | str |
| ip\_country | Country derived from IP address geolocation at time of conversion | str |
| ip\_dma\_id | DMA ID derived from IP address geolocation at time of conversion | str |
| ip\_state | State derived from IP address geolocation at time of conversion | str |
| ip\_zip\_code | Zip code derived from IP address geolocation at time of conversion | str |
| province\_code | Province code provided by the customer during conversion | str |
| state | State provided by the customer during conversion | str |
| zip | Zip code provided by the customer during conversion | str |
| device | Device type used for the conversion | str |
| order\_source | The channel through which the conversion was record, as applicable (e.g., web, app, retail, call center) | str |
| timestamp\_conv | Timestamp of the conversion event (ISO 8601 UTC) | timestamp |
| updated\_at | Timestamp when this record was last updated | timestamp |
| revenue\_usd | Conversion revenue in USD (after currency conversion if applicable) | decimal |
| original\_revenue | Conversion revenue in original currency before conversion to USD (if applicable) | decimal |
| new\_to\_file | `1` if new customer (first-time conversion seen), else `0` | str |
***
### Table: `log_conversions_user_identifiers`
User identifier and personally identifiable information (PII).
| Name | Description | Type |
| ------------------- | ------------------------------------------------------------------------------------- | --------- |
| log\_conversion\_id | Unique identifier matching the corresponding record in `log_conversions` | str |
| order\_id | Unique identifier for the purchase/order as tracked in Shopify or customer POS system | str |
| base\_id | Primary user identifier (e.g., customer ID) | str |
| external\_id | External identifier for the user from third-party systems | str |
| uid | Rockerbox user ID cookie | str |
| email\_raw | Raw email address as provided (original format) | str |
| email | Normalized email address | str |
| email\_hash | Hashed email address (SHA256) | str |
| phone\_number\_raw | Raw phone number as provided (original format) | str |
| phone\_number | Normalized phone number | str |
| phone\_number\_hash | Hashed phone number (SHA256) | str |
| address\_hash | Hashed physical address (SHA256) | str |
| ip\_raw | Raw IP address as captured (original format) | str |
| ip | Normalized IP address | str |
| ip\_hash | Hashed IP address (SHA256) | str |
| user\_agent | User agent string from the browser/device at time of conversion | str |
| updated\_at | Timestamp when this record was last updated | timestamp |
# Log MTA
Source: https://data-foundation.rockerbox.com/warehousing/schema-log-mta
## Description
* Captures every marketing touchpoint on each individual user path to conversion.
* Synthesizes several types of data:
* User context on the conversion event as well as conversion timestamp.
* User, device, and referrer context for each marketing event.
* UTM parameter tracking and custom URL parameters for each marketing event
* Marketing ad object context for each marketing event.
* Attribution credit assigned to each marketing event per four attribution methodologies: (1) first touch (2) last touch (3) even weight (4) Rockerbox custom multi-touch attribution model.
* Excludes deterministic viewthrough touchpoints from Pinterest and Meta partnership integrations due to data sharing agreement restrictions.
***
## Schema Structure
* Log Level MTA data is split into two tables with a 1:1 relationship:
* **`log_mta_partner_permissioned_viewthrough`**: Core marketing touchpoint and attribution data
* **`log_mta_user_identifiers`**: User identifier and device information (PII-adjacent data)
* Both tables report data for ALL conversion events tracked in Rockerbox (e.g., Purchase, Add to Cart).
***
## Usage Notes
* **Attribution Analysis**: All attribution metrics (first touch, last touch, even, normalized) are available in `log_mta_partner_permissioned_viewthrough`.
* **Privacy & Compliance**: User identifiers are isolated in `log_mta_user_identifiers` to enable stricter access controls.
* **Query Performance**: When user event identifiers are not needed, query only `log_mta_partner_permissioned_viewthrough` to reduce data scanned.
* **Filtering by Conversion Type**: Join with `log_conversions_*` tables enrich attribution data with additional conversion and user context.
***
## Table Creation
These tables are automatically created upon connecting Rockerbox with your supported warehouse provider.
***
## Table Relationship
The two tables have a **1:1 relationship** based on the `log_mta_id` field:
* Each record in `log_mta_partner_permissioned_viewthrough` has exactly one corresponding record in `log_mta_user_identifiers`
* Join the tables using: `log_mta_id`
This separation allows for flexible data governance and access control of user identifiers
**Example Join:**
```sql theme={null}
SELECT
m.*,
u.uid_event,
u.hash_ip_event,
u.user_agent_event,
u.base_id
FROM log_mta_partner_permissioned_viewthrough m
LEFT JOIN log_mta_user_identifiers u
ON m.log_mta_id = u.log_mta_id
AND m.conversion_event_id = u.conversion_event_id --Include partition keys for optimal query performance
AND m.date = u.date --Include partition keys for optimal query performance
WHERE m.date >= '2026-01-01'
AND m.conversion_event_id = '12345'
```
***
## Partition Keys
These are external tables. Always leverage partition keys when querying to improve query performance:
**Log MTA**:
* `log_mta_partner_permissioned_viewthrough.conversion_event_id`
* `log_mta_partner_permissioned_viewthrough.date`
**Log MTA User Identifiers**:
* `log_mta_user_identifiers.conversion_event_id`
* `log_mta_user_identifiers.date`
**Note**: The `conversion_event_id` in these tables references `conversion_event_metadata.conversion_event_id`. Use `conversion_event_metadata` to find valid IDs before filtering.
***
## Logical Primary Key
### `log_mta_partner_permissioned_viewthrough`
* `log_mta_id` — Unique identifier for each marketing touchpoint / event record
### `log_mta_user_identifiers`
* `log_mta_id` — Unique identifier matching the corresponding record in `log_mta`
***
## Related Tables
* **`log_conversions_*`**: Reference table containing metadata for the instance of the given conversion event
* TBD
* TBD
* **`conversion_event_metadata`**: Reference table containing metadata for all conversion events tracked in Rockerbox. Use this table to:
* Discover available conversion event IDs and their human-readable names to enabling filtering on specific conversions
* Identify which conversion events are currently active
\[!IMPORTANT] Always filter `log_mta_partner_permissioned_viewthrough` and `log_mta_user_identifiers` on `conversion_event_id` to leverage partition keys.
***
## Field Reference
### Table: `log_mta`
Core marketing touchpoint and attribution data.
| Name | Description | Type |
| -------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| conversion\_event\_id | Partition Key - unique identifier of the conversion event tracked in Rockerbox | str |
| date | Partition Key - date that the conversion occured | str |
| log\_mta\_id | Unique identifier for the marketing touchpoint record attributed to the conversion | str |
| log\_conversion\_id | Unique identifier for each instance of the conversion - used to join against log\_conversions schemas | str |
| conversion\_id | Secondary unique identifier for each instance of the conversion - should not be used as a join key | str |
| action | Name of the conversion action tracked in Rockerbox. This is often overloaded with additional dimensions for segmentation (e.g., `purchase..`) | str |
| event\_type | Marketing event type (e.g., `onsite`, `tiktok_view`, `facebook_click`) that indicates whether the interaction was a click or impression. | str |
| touchpoint\_id | Unique identifier for the marketing touchpoint | str |
| sequence\_number | Order of the touchpoint in the user journey (1 = earliest) | int |
| new\_to\_file | `1` if new customer (first-time conversion seen), else `0` | int |
| pseudonymized\_user\_id | Pseudonymized user identifier for privacy | str |
| request\_referrer | Referrer of the page where the user came from | str |
| original\_url | URL of the marketing touchpoint | str |
| utm\_campaign | UTM campaign parameter from the URL | str |
| utm\_content | UTM content parameter from the URL | str |
| utm\_medium | UTM medium parameter from the URL | str |
| utm\_source | UTM source parameter from the URL | str |
| utm\_term | UTM term parameter from the URL | str |
| utm\_id | UTM ID parameter from the URL | str |
| utm\_source\_platform | UTM source platform parameter from the URL | str |
| utm\_creative\_format | UTM creative format parameter from the URL | str |
| utm\_marketing\_tactic | UTM marketing tactic parameter from the URL | str |
| mapping\_rule\_name | Name of the categorization rule applied to this touchpoint | str |
| platform\_join\_key | Platform-specific identifier for joining with spend data | str |
| platform | Marketing platform name (e.g., `tiktok_v3`, `facebook_v2`) | str |
| tier\_1 | Marketing channel categorization level 1 (most broad), as defined in your mapping rules / reporting taxonomy. | str |
| tier\_2 | Marketing channel categorization level 2 | str |
| tier\_3 | Marketing channel categorization level 3 | str |
| tier\_4 | Marketing channel categorization level 4 | str |
| tier\_5 | Marketing channel categorization level 5 | str |
| tier\_one | Raw ad object identifiers captured on event used to build reporting taxonomy | str |
| tier\_two | Raw ad object identifiers captured on event used to build reporting taxonomy | str |
| tier\_three | Raw ad object identifiers captured on event used to build reporting taxonomy | str |
| tier\_four | Raw ad object identifiers captured on event used to build reporting taxonomy | str |
| tier\_five | Raw ad object identifiers captured on event used to build reporting taxonomy | str |
| timestamp\_conv | Timestamp of when the conversion occurred (ISO 8601 UTC) | timestamp |
| timestamp\_events | Timestamp of the marketing event (ISO 8601 UTC) | timestamp |
| updated\_at | Timestamp when the record was last updated. All records for same `date` + `conversion_event_id` partition share the same `updated_at` | timestamp |
| onsite\_count | Total number of times the user appeared on your site | int |
| total\_events | Number of marketing touchpoints before conversion | int |
| even | Equal fractional credit across touchpoints | float |
| first\_touch | `1` if first touchpoint, else `0` | int |
| last\_touch | `1` if last touchpoint, else `0` | int |
| normalized | Fractional attribution assigned by multi-touch model | float |
| total\_revenue\_usd | Total revenue in USD associated with the conversion | float |
| revenue\_even\_usd | Revenue attributed under even weight distribution (USD) | float |
| revenue\_first\_touch\_usd | Full revenue if it's the first touch, else `0` (USD) | float |
| revenue\_last\_touch\_usd | Full revenue if it's the last touch, else `0` (USD) | float |
| revenue\_normalized\_usd | Revenue assigned by multi-touch attribution model (USD) | float |
***
### Table: `log_mta_user_identifiers`
User identifier and device information (PII-adjacent data).
| Name | Description | Type |
| --------------------- | ------------------------------------------------------------------------------ | ---- |
| conversion\_event\_id | Partition Key - unique identifier of the conversion event tracked in Rockerbox | str |
| date | Partition Key - date that the conversion occured | str |
| log\_mta\_id | Unique identifier matching the corresponding record in `log_mta` | str |
| uid\_event | Rockerbox user ID cookie from the event | str |
| hash\_ip\_event | Hashed IP address of the user for that event | str |
| user\_agent\_event | User agent ID or identifier from the marketing event | int |
| base\_id | Primary user identifier (e.g., customer ID) | str |
***
## Event Type Reference
The `event_type` column in `log_mta` indicates the nature of each tracked touchpoint:
| Event Type | Definition |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| onsite | A click drove a user to site where a marketing touchpoint was tracked |
| creative | A display impression (view-based touchpoint) |
| address\_clean\_hash | A Direct Mail touchpoint from mailer address matchback |
| postlog | A linear TV touchpoint from postlog spike analysis |
| mail | A direct mail touchpoint via address matchback |
| facebook\_click | In-app Facebook click that leads to conversion across devices |
| tiktok\_click | In-app TikTok click that leads to conversion across devices |
| adwords\_click | In-app YouTube click that leads to conversion across devices |
| facebook\_view | Facebook view-based touchpoint (synthetically modeled) |
| ott\_device\_via\_batch | View-based touchpoint that leverages an IP address matchback (includes Reddit viewthrough facilitated via Reddit data sharing partnership) |
| ott\_mobile\_app\_via\_batch | View-based touchpoint that leverages an IP address matchback (includes Reddit viewthrough facilitated via Reddit data sharing partnership) |
| ott\_web\_pixels | View-based touchpoint that leverages an IP address matchback (includes Reddit viewthrough facilitated via Reddit data sharing partnership) |
| ott\_web\_via\_batch | View-based touchpoint that leverages an IP address matchback (includes Reddit viewthrough facilitated via Reddit data sharing partnership) |
| external\_id | Touchpoint via custom matching of external file to Rockerbox data |
| viewthrough\_events\_reddit | Reddit view-based touchpoint via deterministic matchback facilitated via a user-level data sharing partnership |
| viewthrough\_events\_snapchat | Snapchat view-based touchpoint via deterministic matchback facilitated via a user-level data sharing partnership |
| viewthrough\_events\_tiktok | TikTok view-based touchpoint via deterministic matchback facilitated via a user-level data sharing partnership |
| tiktok\_view | TikTok view-based touchpoint (extrapolation of deterministic matchback to account for low user tracking opt-in rate) |
| adwords\_view | View-based touchpoint for YouTube/Demand Gen campaigns (synthetically modeled) |
| conv\_only | A direct (unattributed) touchpoint |
| survey | A touchpoint inserted into user path to conversion based on a post-purchase survey |
# Platform - Bing
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-bing
## Description
The **Platform - Facebook** dataset contains delivery, spend, and conversion metrics at an hourly, ad level from Microsoft Advertising (Bing Ads).
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `utc_hour`
* `ad_id`
***
## Field Reference
| Order | Field | Description | Type |
| ----- | --------------------- | --------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox account ID. | str |
| 2 | type | Dataset type (e.g., `platform_data`). | str |
| 3 | platform | Name of the advertising platform (e.g., Bing). | str |
| 4 | report | Dataset name (e.g., `platform_performance_bing`). | str |
| 5 | identifier | Unique identifier of the account in the advertising platform. | str |
| 6 | date | Date the performance metrics occurred. | date |
| 7 | utc\_hour | UTC hour the performance metrics occurred. | int |
| 8 | tier\_1 | Marketing channel categorization level 1. | str |
| 9 | tier\_2 | Marketing channel categorization level 2. | str |
| 10 | tier\_3 | Marketing channel categorization level 3. | str |
| 11 | tier\_4 | Marketing channel categorization level 4. | str |
| 12 | tier\_5 | Marketing channel categorization level 5. | str |
| 13 | mta\_tiers\_join\_key | Identifier used to pull spend from an advertising platform. Typically `ad_id`, but may differ based on account setup. | str |
| 14 | campaign\_name | Campaign name. | str |
| 15 | campaign\_id | Microsoft Advertising–assigned unique identifier of a campaign. | str |
| 16 | ad\_group\_name | Ad group name. | str |
| 17 | ad\_group\_id | Microsoft Advertising–assigned unique identifier of an ad group. | str |
| 18 | ad\_title | Ad title. | str |
| 19 | ad\_id | Microsoft Advertising–assigned unique identifier of an ad. | str |
| 20 | spend | Estimated total spend in the ad account’s local currency. | float |
| 21 | currency\_code | ISO currency code of the ad account (e.g., `USD`, `EUR`). | str |
| 22 | spend\_usd | Estimated total spend in USD. | float |
| 23 | clicks | Number of clicks on an ad. | int |
| 24 | impressions | Number of times an ad was displayed on search results pages. | int |
| 25 | conversions | Conversion metrics object (includes qualified, all, and view-through conversion counts across conversion goals). | dict |
| 26 | rb\_sync\_id | Identifier used by Rockerbox to sync the dataset to your warehouse. | str |
| 27 | updated\_at | Timestamp of the most recent row update. | timestamp |
***
## Nested Fields
The following fields are nested JSON objects keyed by Facebook conversion event name:
* `view_1d`
* `click_1d`
* `click_7d`
* `view_1d_value`
* `click_1d_value`
* `click_7d_value`
* `view_1d_value_usd`
* `click_1d_value_usd`
* `click_7d_value_usd`
### Example Stored Object
```json theme={null}
{
"purchase": 12,
"add_to_cart": 41
}
```
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d:"purchase"::number as purchase_click_1d,
click_1d_value:"purchase"::float as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
f.key as conversion_event,
f.value::number as conversions_click_1d
from .. t,
lateral flatten(input => t.click_1d) f;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d['purchase']::int as purchase_click_1d,
click_1d_value['purchase']::decimal(18,4) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
select
t.date,
t.ad_id,
kv.key as conversion_event,
kv.value::int as conversions_click_1d
from .. t,
t.click_1d as kv;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(click_1d, '$.purchase') as int64) as purchase_click_1d,
cast(json_value(click_1d_value, '$.purchase') as float64) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
k as conversion_event,
cast(json_value(t.click_1d, concat('$.', k)) as int64) as conversions_click_1d
from .. t,
unnest(json_keys(t.click_1d)) as k;
```
# Platform - Facebook
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-facebook
## Description
The **Platform - Facebook** dataset contains Facebook ad platform performance metrics and conversion reporting at the hourly, ad-level granularity.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `utc_hour`
* `ad_id`
***
## Field Reference
| Order | Name | Description | Type |
| ----- | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox Account ID | str |
| 2 | type | Report type (e.g., `platform_data`, `attribution`) | str |
| 3 | platform | Name of platform (e.g., `Facebook`) | str |
| 4 | report | The name of the report (only visible in Snowflake integrations) | str |
| 5 | identifier | Ad platform account identifier | str |
| 6 | date | Date when the platform metrics occurred | date |
| 7 | utc\_hour | UTC hour when the platform metrics occurred | int |
| 8 | tier\_1 | Five-level categorization tiers aligned to UI taxonomy (most broad) | str |
| 9 | tier\_2 | Five-level categorization tiers aligned to UI taxonomy | str |
| 10 | tier\_3 | Five-level categorization tiers aligned to UI taxonomy | str |
| 11 | tier\_4 | Five-level categorization tiers aligned to UI taxonomy | str |
| 12 | tier\_5 | Five-level categorization tiers aligned to UI taxonomy (most granular) | str |
| 13 | mta\_tiers\_join\_key | Platform spend identifier (usually Ad ID or composite key) | str |
| 14 | campaign\_name | The name of the ad campaign. A campaign contains ad sets and ads | str |
| 15 | campaign\_id | The unique ID of the ad campaign | str |
| 16 | adset\_name | The name of the ad set | str |
| 17 | adset\_id | The unique ID of the ad set | str |
| 18 | ad\_name | The name of the ad | str |
| 19 | ad\_id | The unique ID of the ad | str |
| 20 | spend | The estimated total amount spent in the ad platform account’s local currency | float |
| 21 | currency\_code | Reporting currency for revenue and spend | str |
| 22 | spend\_usd | The estimated total amount spent in USD | float |
| 23 | clicks | The number of clicks on the ad | int |
| 24 | impressions | The number of times the ads were shown on screen | int |
| 25 | inline\_link\_clicks | The number of clicks on links to select destinations or experiences, on or off Facebook-owned properties (fixed 1-day-click attribution window) | int |
| 26 | outbound\_clicks | The number of clicks on links that take users off Facebook-owned properties | int |
| 27 | view\_1d | The total number of view-through conversions within a 1-day lookback window | dict |
| 28 | click\_1d | The total number of click-through conversions within a 1-day lookback window | dict |
| 29 | click\_7d | The total number of click-through conversions within a 7-day lookback window | dict |
| 30 | view\_1d\_value | The total value of view-through conversions (local currency) within a 1-day lookback window | dict |
| 31 | click\_1d\_value | The total value of click-through conversions (local currency) within a 1-day lookback window | dict |
| 32 | click\_7d\_value | The total value of click-through conversions (local currency) within a 7-day lookback window | dict |
| 33 | view\_1d\_value\_usd | The total value of view-through conversions converted to USD within a 1-day lookback window | dict |
| 34 | click\_1d\_value\_usd | The total value of click-through conversions converted to USD within a 1-day lookback window | dict |
| 35 | click\_7d\_value\_usd | The total value of click-through conversions converted to USD within a 7-day lookback window | dict |
| 36 | rb\_sync\_id | Rockerbox internal sync identifier | str |
| 37 | updated\_at | Timestamp when the record was last updated | timestamp |
***
## Nested Fields
The following fields are nested JSON objects keyed by Facebook conversion event name:
* `view_1d`
* `click_1d`
* `click_7d`
* `view_1d_value`
* `click_1d_value`
* `click_7d_value`
* `view_1d_value_usd`
* `click_1d_value_usd`
* `click_7d_value_usd`
### Example Stored Object
```json theme={null}
{
"purchase": 12,
"add_to_cart": 41
}
```
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d:"purchase"::number as purchase_click_1d,
click_1d_value:"purchase"::float as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
f.key as conversion_event,
f.value::number as conversions_click_1d
from .. t,
lateral flatten(input => t.click_1d) f;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d['purchase']::int as purchase_click_1d,
click_1d_value['purchase']::decimal(18,4) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
select
t.date,
t.ad_id,
kv.key as conversion_event,
kv.value::int as conversions_click_1d
from .. t,
t.click_1d as kv;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(click_1d, '$.purchase') as int64) as purchase_click_1d,
cast(json_value(click_1d_value, '$.purchase') as float64) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
k as conversion_event,
cast(json_value(t.click_1d, concat('$.', k)) as int64) as conversions_click_1d
from .. t,
unnest(json_keys(t.click_1d)) as k;
```
# Platform - Google
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-google
## Description
The **Platform – Google** dataset contains aggregated performance metrics from Google Ads (formerly AdWords), including spend, impressions, clicks, and conversion data.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `utc_hour`
* `campaign_id`
* `ad_group_id`
* `ad_network_type`
***
## Field Reference
| Order | Name | Description |
| ----- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 | advertiser | The name of the Rockerbox account associated with the data export. |
| 2 | type | The dataset type. For this table, the value will be `platform_data`. |
| 3 | platform | The advertising platform name. For this dataset, the value is `Adwords` (Google Ads). |
| 4 | report | The name of the dataset. For this table, the value is `platform_performance_ad`. |
| 5 | identifier | The unique identifier of the advertiser account in Google Ads. |
| 6 | date | The calendar date associated with the reported performance metrics. |
| 7 | utc\_hour | The UTC hour during which the activity occurred. |
| 8 | tier\_1 | Marketing channel categorization level 1, as defined in your Rockerbox configuration. |
| 9 | tier\_2 | Marketing channel categorization level 2, as defined in your Rockerbox configuration. |
| 10 | tier\_3 | Marketing channel categorization level 3, as defined in your Rockerbox configuration. |
| 11 | tier\_4 | Marketing channel categorization level 4, as defined in your Rockerbox configuration. |
| 12 | tier\_5 | Marketing channel categorization level 5, as defined in your Rockerbox configuration. |
| 13 | mta\_tiers\_join\_key | The unique identifier used to join spend data from Google Ads to Rockerbox MTA datasets. This is typically the `ad_id`, but may vary depending on your account setup. |
| 14 | ad\_network\_type | The Google Ads network where the ad was served (e.g., `SEARCH`, `SEARCH_PARTNERS`, `MIXED`, `YOUTUBE_WATCH`). |
| 15 | advertising\_channel\_type | The primary advertising channel type for the campaign (e.g., `SEARCH`, `DISPLAY`, `SHOPPING`). |
| 16 | campaign\_name | The name of the campaign in Google Ads. A campaign may contain one or more ad groups. |
| 17 | campaign\_id | The unique identifier of the campaign in Google Ads. |
| 18 | ad\_group\_type | The type of ad group (e.g., `SEARCH_STANDARD`, `DISPLAY_STANDARD`). |
| 19 | ad\_group\_name | The name of the ad group. An ad group contains one or more ads. |
| 20 | ad\_group\_id | The unique identifier of the ad group in Google Ads. |
| 21 | spend | The total estimated spend in the account’s local currency for the given date and dimension breakdown. |
| 22 | currency\_code | The local currency of the Google Ads account (e.g., `USD`, `EUR`). |
| 23 | spend\_usd | The total estimated spend converted to USD. |
| 24 | clicks | The number of clicks recorded on the ads. |
| 25 | impressions | The number of times ads were served across Google properties or partner networks. |
| 26 | cost\_micros | Ad spend in micro units of the account’s local currency. For example, \$1.23 is represented as 1,230,000 (1.23 × 1,000,000). |
| 27 | all\_conversions\_by\_conversion\_date | The total number of conversions attributed based on the actual conversion date. |
| 28 | all\_conversions | The total number of conversions attributed based on the date of the associated advertising interaction (impression or click). |
| 29 | view\_through\_conversions | The number of conversions attributed to impressions without a click interaction. |
| 30 | all\_conversions\_value\_by\_conversion\_date | The total value of conversions attributed based on the actual conversion date. |
| 31 | all\_conversions\_value | The total value of conversions attributed based on the date of the associated advertising interaction (impression or click). |
| 32 | view\_through\_lookback\_window\_days | The maximum number of days between an impression and a conversion for the conversion to be attributed without an interaction. |
| 33 | click\_through\_lookback\_window\_days | The maximum number of days between a click and a conversion for the conversion to be attributed. |
| 34 | rb\_sync\_id | Internal identifier used by Rockerbox to sync this dataset to your data warehouse. |
| 35 | updated\_at | Timestamp indicating when the record was last updated in Rockerbox. |
***
## Nested Fields
The following fields are nested JSON objects keyed by Facebook conversion event name:
* `all_conversions_by_conversion_date`
* `all_conversions`
* `view_through_conversions`
* `all_conversions_value_by_conversion_date`
* `all_conversions_value`
* `view_through_lookback_window_days`
* `click_through_lookback_window_days`
### Example Stored Object
```json theme={null}
{
"purchase": 12,
"add_to_cart": 41
}
```
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
all_conversions_by_conversion_date:"purchase"::number as purchase,
all_conversions_value_by_conversion_date:"purchase"::float as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
f.key as conversion_event,
f.value::number as all_conversions
from .. t,
lateral flatten(input => t.all_conversions_by_conversion_date) f;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
all_conversions_by_conversion_date['purchase']::int as purchase,
all_conversions_value_by_conversion_date['purchase']::decimal(18,4) as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
select
t.date,
t.ad_id,
kv.key as conversion_event,
kv.value::int as conversions
from .. t,
t.all_conversions_by_conversion_date as kv;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(all_conversions_by_conversion_date, '$.purchase') as int64) as purchase,
cast(json_value(all_conversions_value_by_conversion_date, '$.purchase') as float64) as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
k as conversion_event,
cast(json_value(t.all_conversions_by_conversion_date, concat('$.', k)) as int64) as conversions
from .. t,
unnest(json_keys(t.all_conversions_by_conversion_date)) as k;
```
# Platform - LinkedIn
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-linkedin
## Description
The **Platform - LinkedIn** dataset contains delivery, engagement, spend, and conversion metrics by date and creative from LinkedIn Ads.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `creative_id`
***
## Field Reference
| Order | Field | Description | Type |
| ----- | ------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox account ID. | str |
| 2 | type | Dataset type (e.g., `platform_data`). | str |
| 3 | platform | Name of the advertising platform (`LinkedIn`). | str |
| 4 | report | Dataset name (`platform_performance_linkedin`). | str |
| 5 | identifier | Unique identifier of the account in the advertising platform. | str |
| 6 | date | Date the performance metrics occurred. | date |
| 7 | tier\_1 | Marketing channel categorization level 1. | str |
| 8 | tier\_2 | Marketing channel categorization level 2. | str |
| 9 | tier\_3 | Marketing channel categorization level 3. | str |
| 10 | tier\_4 | Marketing channel categorization level 4. | str |
| 11 | tier\_5 | Marketing channel categorization level 5. | str |
| 12 | mta\_tiers\_join\_key | Identifier used to pull spend from an advertising platform. This is typically the `ad_id`, but may differ based on your account setup. | str |
| 13 | campaign\_group\_name | Name of the campaign group. Campaign groups provide a way to manage status, budget, and performance across multiple campaigns. | str |
| 14 | campaign\_group\_id | Unique identifier of the campaign group. | str |
| 15 | campaign\_name | Name of the campaign. Campaigns define the ad set and budget (daily/total). | str |
| 16 | campaign\_id | Unique identifier of the campaign. | str |
| 17 | campaign\_type | Type of campaign (synonymous with ad placement). Possible values include `TEXT_AD`, `SPONSORED_UPDATES`, `SPONSORED_INMAILS`, `DYNAMIC_ADS`, `SPONSORED_CONTENT`. | str |
| 18 | creative\_id | Unique identifier of the creative (ad). | str |
| 19 | creative\_type | Type of ad creative (e.g., Text Ads, Sponsored Content, Message Ads, Conversation Ads, Sponsored Video, Carousel Ad, Dynamic Ads). | str |
| 20 | spend | Estimated total spend in the ad account’s local currency. | float |
| 21 | currency\_code | ISO currency code of the ad account (e.g., `EUR`, `USD`). | str |
| 22 | spend\_usd | Estimated total spend in USD. | float |
| 23 | clicks | Count of chargeable clicks. | int |
| 24 | impressions | Count of impressions for Sponsored Content and Sponsored Messaging. | int |
| 25 | company\_page\_clicks | Count of clicks to view the company page. | int |
| 26 | landing\_page\_clicks | Count of clicks directing users to the creative landing page. | int |
| 27 | follows | Count of follows (Sponsored Content and Follower Ads only). | int |
| 28 | likes | Count of likes (Sponsored Content only). | int |
| 29 | comments | Count of comments (Sponsored Content only). | int |
| 30 | video\_completions | Count of video ads viewed to 97–100% completion. | int |
| 31 | total\_engagements | Count of all user interactions with the ad unit. | int |
| 32 | post\_click\_attribution\_window\_size | Number of days after a click that conversions are attributed to the ad. | int |
| 33 | view\_through\_attribution\_window\_size | Number of days after a view that conversions are attributed to the ad. | int |
| 34 | conversion\_value | Value of conversions in the ad account’s local currency, based on advertiser-defined rules. | float |
| 35 | external\_website\_conversions | Sum of `external_website_post_click_conversions` + `external_website_post_view_conversions`. | int |
| 36 | external\_website\_post\_view\_conversions | Count of conversion actions taken after a user views the ad. | int |
| 37 | external\_website\_post\_click\_conversions | Count of conversion actions taken after a user clicks the ad. | int |
| 38 | rb\_sync\_id | Identifier used by Rockerbox to sync the dataset to your warehouse. | str |
| 39 | updated\_at | Timestamp of the most recent row update. | timestamp |
# Platform - Pinterest
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-pinterest
## Description
The **Platform - Pinterest** dataset contains performance metrics and conversion reporting at the daily, pin-level granularity.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `ad_id`
* `pin_promotion_id`
***
## Field Reference
| Order | Name | Description | Type |
| ----: | ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox Account ID | str |
| 2 | type | The dataset type (e.g., platform\_data) | str |
| 3 | platform | The name of the ad platform (e.g., Adwords) | str |
| 4 | report | The name of the dataset (e.g., platform\_performance\_adwords) | str |
| 5 | identifier | The unique identifier of the account in the advertising platform | str |
| 6 | date | The date that the action occured | date |
| 7 | tier\_1 | Marketing channel categorization level 1 | str |
| 8 | tier\_2 | Marketing channel categorization level 2 | str |
| 9 | tier\_3 | Marketing channel categorization level 3 | str |
| 10 | tier\_4 | Marketing channel categorization level 4 | str |
| 11 | tier\_5 | Marketing channel categorization level 5 | str |
| 12 | mta\_tiers\_join\_key | The unique identifier used to pull spend from an advertisting platform. This is typically the ad\_id, but may differ based on your account setup | str |
| 13 | campaign\_name | The name of the advertising campaign | str |
| 14 | campaign\_id | The unique identifier associated with a given advertising campaign | str |
| 15 | campaign\_status | The status of a given campaign – active, paused, or archived | str |
| 16 | ad\_group\_name | The name of the ad group | str |
| 17 | ad\_group\_id | The unique identifier associated with a given ad group | str |
| 18 | ad\_group\_status | The status of given ad group – running, paused, not started, completed, advertiser disabled, or archived | str |
| 19 | pin\_promotion\_ad\_group\_type | | str |
| 20 | pin\_promotion\_ad\_group\_type\_2 | | str |
| 21 | pin\_promotion\_name | The name of the promoted pin | str |
| 22 | pin\_promotion\_id | The unique identifier of the promoted pin (i.e., ad) | str |
| 23 | pin\_promotion\_status | The status of a pin promotion – active, other, or paused. | str |
| 24 | pin\_id | The unique identifier of the pin | str |
| 25 | ad\_id | The unique identifier associated with a given ad | str |
| 26 | spend | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in the ad platform account’s local currency | foat |
| 27 | currency\_code | The local currency of your account in the ad platform (e.g., EUR, USD) | str |
| 28 | spend\_usd | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in USD. | foat |
| 29 | clicks | The total number of clicks on your Pin or ad to content on Pinterest or off of Pinterest (e.g., the advertisers website) | int |
| 30 | clickthrough\_1\_gross | Pin clicks for first order ad events (e.g., clicks on a promoted pin) | int |
| 31 | clickthrough\_2 | Pin clicks for downstream or earned events (e.g., clicks on a saved instance of a promoted pin). Advertisers are not billed for earned activity. | int |
| 32 | impressions | The number of times your Pins or ads were on screen. | int |
| 33 | impression\_1\_gross | Impressions for first order ad events (e.g., promoted pins) | int |
| 34 | impression\_2 | Impressions for downstream or earned events (e.g., impressions of a saved instance of a promoted pin). | int |
| 35 | repin\_1 | The number of times the pin was saved to another user’s board. This metric captures repins of first order ad events (e.g., repins of promoted pins). | int |
| 36 | repin\_2 | The number of times the pin was saved to another user’s board. This metric captures repins of downstream or earned events (e.g., repins of saved instances of promoted pins). | int |
| 37 | outbound\_click\_1 | An outbound click is a click on a pin that lead the user to a destination off of Pinterest (i.e., the advertiser’s websiter). This metric aggregates all outbound clicks for first order ad events (e.g., outbound clicks of promoted pins). | int |
| 38 | outbound\_click\_2 | An outbound click is a click on a pin that lead the user to a destination off of Pinterest (i.e., the advertiser’s websiter). This metric aggregates all outbound clicks for downstream or earned events (e.g., outbound clicks of saved instances of promoted pins). | int |
| 39 | engagement\_1 | Engagements track any interactions with your Pins – this includes saves, Pin clicks, outbound clicks, carousel card swipes, secondary creative (collections) clicks and Idea Pin forward/backward swipes. This metric captures all engagement for first order ad events (e.g., promoted pins). | int |
| 40 | engagement\_2 | Engagements track any interactions with your Pins – this includes saves, Pin clicks, outbound clicks, carousel card swipes, secondary creative (collections) clicks and Idea Pin forward/backward swipes. This metric captures all engagement for secondary or earned events (i.e., saved instances of promoted pins). | int |
| 41 | video\_mrc\_views\_2 | The number of times your video ad played continuously for 2 seconds while at least 50% in view after being saved to another person’s board. Note that metrics ending in \_2 refer to actions taking on an organic (nonad) Pin. | int |
| 42 | video\_avg\_watchtime\_in\_second\_2 | The average time people watched your video. This includes people who rewatch your video in the same day. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | foat |
| 43 | video\_p0\_combined\_2 | The number of times the non-ad Pin version of your video played while at least 50% in view, but did not reach 25% played. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 44 | video\_p25\_combined\_2 | The number of times the non-ad version of your Pin played at least a quarter of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 45 | video\_p50\_combined\_2 | The number of times the non-ad version of your Pin played at least half the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 46 | video\_p75\_combined\_2 | The number of times the non-ad version of your Pin played at least three quarters of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 47 | video\_p95\_combined\_2 | The number of times the non-ad version of your Pin played at least 95% of the content while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 48 | video\_p100\_complete\_2 | The number of times the non-ad Pin version of your video played completely while at least 50% in view. Note that metrics ending in \_2 refer to actions taking on an organic (non-ad) Pin. | int |
| 49 | total\_conversions | The total number of conversions across all conversion objectives | int |
| 50 | total\_conversions\_quantity | The total order quantity across all conversion objectives | int |
| 51 | 1d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 52 | 1d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting. | dict |
| 53 | 1d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 54 | 7d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 55 | 7d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 56 | 7d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 57 | 14d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 58 | 14d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 59 | 14d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 60 | 30d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 61 | 30d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 62 | 30d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 63 | 60d\_view | The count of conversions within the specified lookback window where a user viewed a Pin prior to converting | dict |
| 64 | 60d\_click | The count of conversions within the specified lookback window where a user clicked a Pin prior to converting | dict |
| 65 | 60d\_engagement | The count of conversions within the specified lookback window where a user interacted with an ad (saves, close-ups, etc.) prior to converting | dict |
| 66 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | str |
| 67 | updated\_at | | timestamp |
***
## Nested Fields
The following fields are structured as dictionaries (`dict` type):
* `1d_view`
* `1d_click`
* `1d_engagement`
* `7d_view`
* `7d_click`
* `7d_engagement`
* `14d_view`
* `14d_click`
* `14d_engagement`
* `30d_view`
* `30d_click`
* `30d_engagement`
* `60d_view`
* `60d_click`
* `60d_engagement`
Each dictionary contains conversion event names as keys and the associated conversion count as values.
### Example Stored Object
```json theme={null}
{
"purchase": 25,
"add_to_cart": 40,
"signup": 12
}
```
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
1d_click:"purchase"::number as purchase
from ..;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
1d_click['purchase']::int as purchase
from ..;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(1d_click, '$.purchase') as int64) as purchase
from `..`;
```
# Platform - Snapchat
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-snapchat
## Description
The **Platform - Snapchat** dataset contains delivery, spend, engagement, video, and conversion performance metrics from Snapchat Ads. Data is available at the ad-level by date and hour.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `utc_hour`
* `ad_id`
***
## Field Reference
| Order | Field | Description | Type |
| ----- | ------------------------------------- | --------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox account ID. | str |
| 2 | type | Dataset type (e.g., `platform_data`). | str |
| 3 | platform | Advertising platform name (`Snapchat`). | str |
| 4 | report | Dataset name (`platform_performance_snapchat`). | str |
| 5 | identifier | Unique identifier of the Snapchat ad account. | str |
| 6 | date | Date the performance metrics occurred. | date |
| 7 | utc\_hour | UTC hour the performance metrics occurred. | int |
| 8 | tier\_1 | Marketing channel categorization level 1. | str |
| 9 | tier\_2 | Marketing channel categorization level 2. | str |
| 10 | tier\_3 | Marketing channel categorization level 3. | str |
| 11 | tier\_4 | Marketing channel categorization level 4. | str |
| 12 | tier\_5 | Marketing channel categorization level 5. | str |
| 13 | mta\_tiers\_join\_key | Identifier used to join platform spend to MTA datasets. Typically `ad_id`. | str |
| 14 | campaign\_name | Campaign name in Snapchat. | str |
| 15 | campaign\_id | Unique campaign ID in Snapchat. | str |
| 16 | ad\_squad\_name | Ad squad name. | str |
| 17 | ad\_squad\_id | Unique ad squad ID. | str |
| 18 | ad\_name | Ad name. | str |
| 19 | ad\_id | Unique ad ID. | str |
| 20 | ad\_type | Snap ad format (e.g., `SNAP_AD`, `LONGFORM_VIDEO`). | str |
| 21 | spend | Estimated total spend in the account’s local currency. | float |
| 22 | currency\_code | ISO currency code of the ad account (e.g., `USD`, `EUR`). | str |
| 23 | spend\_usd | Estimated total spend in USD. | float |
| 24 | swipes | Swipe-up count. | int |
| 25 | impressions | Total impressions. | int |
| 26 | earned\_impressions | Impressions generated after being shared via Chat or Stories. | int |
| 27 | paid\_impressions | Impressions served via paid delivery. | int |
| 28 | total\_impressions | Combined paid and earned impressions. | int |
| 29 | screen\_time\_millis | Total time spent viewing Top Snap ads (milliseconds). | float |
| 30 | avg\_screen\_time\_millis | Average Top Snap view time (milliseconds). | float |
| 31 | quartile\_1 | Video views to 25%. | int |
| 32 | quartile\_2 | Video views to 50%. | int |
| 33 | quartile\_3 | Video views to 75%. | int |
| 34 | view\_completion | Video views to completion. | int |
| 35 | video\_views | Impressions meeting qualifying video view criteria (≥2 seconds consecutive watch time or swipe-up). | int |
| 36 | video\_views\_15s | Impressions meeting ≥15 seconds watched (or 97% completion if shorter) or swipe-up. | int |
| 37 | video\_views\_time\_based | Impressions meeting ≥2 seconds consecutive watch time (excluding swipe-ups). | int |
| 38 | saves | Number of times a lens/filter was saved to Memories. | int |
| 39 | shares | Number of times a lens/filter was shared via Chat or Stories. | int |
| 40 | attachment\_impressions | Impression count from attachments. | int |
| 41 | attachment\_quartile\_1 | Long-form video views to 25%. | int |
| 42 | attachment\_quartile\_2 | Long-form video views to 50%. | int |
| 43 | attachment\_quartile\_3 | Long-form video views to 75%. | int |
| 44 | attachment\_view\_completion | Long-form video views to completion. | int |
| 45 | attachment\_avg\_view\_time\_millis | Average attachment view time (milliseconds). | float |
| 46 | attachment\_total\_view\_time\_millis | Total attachment view time (milliseconds). | int |
| 47 | view\_1\_day | View-through conversions within a 1-day lookback window. | dict |
| 48 | view\_7\_day | View-through conversions within a 7-day lookback window. | dict |
| 49 | swipe\_1\_day | Click-through (swipe) conversions within a 1-day lookback window. | dict |
| 50 | swipe\_7\_day | Click-through (swipe) conversions within a 7-day lookback window. | dict |
| 51 | swipe\_28\_day | Click-through (swipe) conversions within a 28-day lookback window. | dict |
| 52 | view\_1\_day\_value | Total value of 1-day view-through conversions (local currency). | dict |
| 53 | view\_7\_day\_value | Total value of 7-day view-through conversions (local currency). | dict |
| 54 | swipe\_1\_day\_value | Total value of 1-day click-through conversions (local currency). | dict |
| 55 | swipe\_7\_day\_value | Total value of 7-day click-through conversions (local currency). | dict |
| 56 | swipe\_28\_day\_value | Total value of 28-day click-through conversions (local currency). | dict |
| 57 | view\_1\_day\_value\_usd | USD value of 1-day view-through conversions. | dict |
| 58 | view\_7\_day\_value\_usd | USD value of 7-day view-through conversions. | dict |
| 59 | swipe\_1\_day\_value\_usd | USD value of 1-day click-through conversions. | dict |
| 60 | swipe\_7\_day\_value\_usd | USD value of 7-day click-through conversions. | dict |
| 61 | swipe\_28\_day\_value\_usd | USD value of 28-day click-through conversions. | dict |
| 62 | rb\_sync\_id | Rockerbox-generated identifier used to sync the dataset to your warehouse. | str |
| 63 | updated\_at | Timestamp of the most recent row update. | timestamp |
***
## Nested Fields
The following fields are nested JSON objects keyed by Snapchat conversion event name:
`view_1_day`
`view_7_day`
`swipe_1_day`
`swipe_7_day`
`swipe_28_day`
`view_1_day_value`
`view_7_day_value`
`swipe_1_day_value`
`swipe_7_day_value`
`swipe_28_day_value`
`view_1_day_value_usd`
`view_7_day_value_usd`
`swipe_1_day_value_usd`
`swipe_7_day_value_usd`
`swipe_28_day_value_usd`
### Example Stored Object
```json theme={null}
{
"purchase": 12,
"add_to_cart": 41
}
```
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
view_1_day:"purchase"::number as purchase,
view_1_day_value:"purchase"::float as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
f.key as conversion_event,
f.value::number as conversions_view_1_day
from .. t,
lateral flatten(input => t.view_1_day) f;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
view_1_day['purchase']::int as purchase,
view_1_day_value['purchase']::decimal(18,4) as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
select
t.date,
t.ad_id,
kv.key as conversion_event,
kv.value::int as conversions
from .. t,
t.view_1_day as kv;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(view_1_day, '$.purchase') as int64) as purchase,
cast(json_value(view_1_day_value, '$.purchase') as float64) as purchase_value
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
k as conversion_event,
cast(json_value(t.view_1_day, concat('$.', k)) as int64) as conversions
from .. t,
unnest(json_keys(t.view_1_day)) as k;
```
# Platform - TikTok
Source: https://data-foundation.rockerbox.com/warehousing/schema-platform-tiktok
## Description
The **Platform - TikTok** dataset contains TikTok ad platform performance metrics at the hourly, ad-level granularity.
***
## Partition Keys
* `identifier`
* `date`
💡 **Note:** Leverage partition keys when querying the table to improve query efficiency.
***
## Logical Primary Key
These fields uniquely identify a record. While data warehouses do not enforce primary key constraints, this combination functions as the logical primary key for the table.
* `identifier`
* `date`
* `utc_hour`
* `ad_id`
***
## Field Reference
| Order | Name | Description | Type |
| ----- | ------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- |
| 1 | advertiser | Rockerbox Account ID | str |
| 2 | type | The dataset type (e.g., `platform_data`) | str |
| 3 | platform | The name of the ad platform (e.g., Adwords) | str |
| 4 | report | The name of the dataset (e.g., `platform_performance_adwords`) | str |
| 5 | identifier | The unique identifier of the account in the advertising platform | str |
| 6 | date | The date that the action occured | date |
| 7 | utc\_hour | The UTC hour that the action occured | int |
| 8 | tier\_1 | Marketing channel categorization level 1 | str |
| 9 | tier\_2 | Marketing channel categorization level 2 | str |
| 10 | tier\_3 | Marketing channel categorization level 3 | str |
| 11 | tier\_4 | Marketing channel categorization level 4 | str |
| 12 | tier\_5 | Marketing channel categorization level 5 | str |
| 13 | mta\_tiers\_join\_key | The unique identifier used to pull spend from an advertisting platform. This is typically the ad\_id, but may differ based on your account setup | str |
| 14 | campaign\_name | The name of the campaign. A campaign is made up of a series of ad groups, each with its own unique targeting settings, optimization goals, budgets, and ads | str |
| 15 | campaign\_id | The unique identifier of the campaign | str |
| 16 | adgroup\_name | The name of the ad group. An ad group relates to one campaign and contains a set of similar ads | str |
| 17 | adgroup\_id | The unique identifier of the ad group | str |
| 18 | ad\_name | The name of the ad | str |
| 19 | ad\_id | The unique identifier of the ad | str |
| 20 | spend | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in the ad platform account’s local currency | float |
| 21 | currency\_code | The local currency of your account in the ad platform (e.g., EUR, USD) | str |
| 22 | spend\_usd | The estimated total amount of money you’ve spent on your campaign, ad set or ad during its schedule in USD. | float |
| 23 | clicks | Clicks recorded to CTA button, ad caption, nickname, profile picture, and swipe-left | int |
| 24 | impressions | The number of times your ads were on screen | int |
| 25 | follows | The number of new followers that were gained within 1 day of a user seeing a paid ad | int |
| 26 | likes | The number of likes the video creative received within 1 day of a user seeing a paid ad | int |
| 27 | comments | The number of comments your video creative received within 1 day of a user seeing a paid ad | int |
| 28 | reach | The number of unique users who saw your ads at least once. This metric is estimated | int |
| 29 | frequency | The average number of times each person saw your ad | float |
| 30 | shares | The number of times your video creative was shared within 1 day of a user seeing a paid ad | int |
| 31 | profile\_visits | The number of profile visits the paid ad drove during the campaign | int |
| 32 | secondary\_goal\_result | The number of times your ad achieved an outcome, based on the secondary goal you selected. | int |
| 33 | video\_play\_actions | The number of times your video starts to play. Replays will not be counted | int |
| 34 | average\_video\_play | The average time your video was played per single video view, including any time spent replaying the video | float |
| 35 | average\_video\_play\_per\_user | The average amount of time your video ads played per person. Including anytime spent replaying the video | float |
| 36 | video\_watched\_2s | Number of times your video was played for at least 2 seconds. Replays will not be counted | int |
| 37 | video\_watched\_6s | Number of times your video was played for at least 6 seconds. Replays will not be counted | int |
| 38 | video\_views\_p25 | The number of times your video was played at 25% of its length. Replays will not be counted | int |
| 39 | video\_views\_p50 | The number of times your video was played at 50% of its length. Replays will not be counted | int |
| 40 | video\_views\_p75 | The number of times your video was played at 75% of its length. Replays will not be counted | int |
| 41 | video\_views\_p100 | The number of times your video was played at 100% of its length. Replays will not be counted | int |
| 42 | clicks\_on\_music\_disc | The number of clicks recorded to Music Disc icon and Music title | int |
| 43 | conversions | The number of times your ad achieved an outcome based on the objective and settings you selected | dict |
| 44 | rb\_sync\_id | Identifier used by Rockerbox to sync dataset to your warehouse | str |
| 45 | updated\_at | | timestamp |
***
## Nested Fields
The following fields are nested JSON objects keyed by Facebook conversion event name:
* `conversions`
### Example Stored Object
```json theme={null}
{
"conversion": 5,
"real_time_conversion": 3,
"real_time_result": 13,
"result": 20
}
```
### Nested Field Definitions
* **conversion** — attributed conversion events based on the ad group’s configured attribution window (finalized reporting metric).
* **real\_time\_conversion** — near real-time conversion events reported before the full attribution window has matured; primarily used for in-flight pacing, rapid performance checks, and short-term bid/budget adjustments.
* **result** — Attributed events tied specifically to the campaign’s selected optimization objective (the primary KPI metric).
* **real\_time\_result** — near real-time version of the objective-based result metric; used by marketers to monitor live optimization performance and make same-day creative or budget decisions.
### Snowflake - Querying Nested Fields
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d:"purchase"::number as purchase_click_1d,
click_1d_value:"purchase"::float as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
f.key as conversion_event,
f.value::number as conversions_click_1d
from .. t,
lateral flatten(input => t.click_1d) f;
```
### Redshift - Querying Nested Fields (SUPER type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
click_1d['purchase']::int as purchase_click_1d,
click_1d_value['purchase']::decimal(18,4) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
select
t.date,
t.ad_id,
kv.key as conversion_event,
kv.value::int as conversions_click_1d
from .. t,
t.click_1d as kv;
```
### BigQuery - Querying Nested Fields (JSON type)
#### Extract a Single Event
```sql theme={null}
select
date,
ad_id,
cast(json_value(click_1d, '$.purchase') as int64) as purchase_click_1d,
cast(json_value(click_1d_value, '$.purchase') as float64) as purchase_value_click_1d
from ..;
```
#### Flatten All Events Into Rows
```sql theme={null}
select
t.date,
t.ad_id,
k as conversion_event,
cast(json_value(t.click_1d, concat('$.', k)) as int64) as conversions_click_1d
from .. t,
unnest(json_keys(t.click_1d)) as k;
```
# Taxonomy Lookup
Source: https://data-foundation.rockerbox.com/warehousing/schema-taxonomy-lookup
## Description
* The Taxonomy Lookup schema provides your standardized Rockerbox reporting taxonomy for all channels with spend tracking.
* It is aggregated to the lowest reporting level per platform/vendor (e.g., ad group for Google Ads, ad for Meta).
* The table always shows the latest mapping rules, with updates automatically synced.
⚠️ **Note:** The taxonomy for non-paid media and organic channels is **not included** in this table.
***
## Table Creation
The `taxonomy_lookup` table is automatically created upon connecting Rockerbox with your supported warehouse provider.
***
## Logical Primary Key
* `join_key`
***
## Usage Notes
* Join this table against these schemas to apply taxonomy updates to historical data:
* Aggregate MTA
* Log Level MTA
* Platform Performance
* The `tier_1` through `tier_5` taxonomy columns have inconsistent capitalization in comparison to legacy schemas in certain instances.
* Apply `LOWER()` to the `tier_1` to `tier_5` columns when joining `taxonomy_lookup` with ANY of those schemas to ensure consistency in aggregate computations when grouping by `tier_x` dimensions in your taxonomy.
> The Aggregate MTA, Log Level MTA, and Platform Performance schemas already include `tier_1` through `tier_5`, but only with the mapping rules that were in place when the data was created. Joining against `taxonomy_lookup` applies the most up-to-date mappings.
***
## Table Join Guidance
* Use a **left join** because `taxonomy_lookup` only contains mappings for channels with spend tracking.
* Lowercase and `tier_1` through `tier_5` to ensure consistency in taxonomy capitalization across historical data
* Lowercase `platform_join_key` on `aggregate_mta` before joining onto the `taxonomy_lookup` table.
***
### Example: Aggregate MTA
```sql theme={null}
SELECT
COALESCE(t.tier_1, a.tier_1, "No mapping") AS tier_1,
COALESCE(t.tier_2, a.tier_2) AS tier_2,
COALESCE(t.tier_3, a.tier_3) AS tier_3,
COALESCE(t.tier_4, a.tier_4) AS tier_4,
COALESCE(t.tier_5, a.tier_5) AS tier_5
FROM aggregate_mta a
LEFT JOIN taxonomy_lookup t
ON t.join_key = lower(a.platform_join_key)
```
### Example: Log Level MTA
```sql theme={null}
SELECT
LOWER(COALESCE(t.tier_1, m.tier_1, "No mapping")) AS tier_1,
LOWER(COALESCE(t.tier_2, m.tier_2)) AS tier_2,
LOWER(COALESCE(t.tier_3, m.tier_3)) AS tier_3,
LOWER(COALESCE(t.tier_4, m.tier_4)) AS tier_4,
LOWER(COALESCE(t.tier_5, m.tier_5)) AS tier_5
FROM m
LEFT JOIN taxonomy_lookup t
ON t.join_key = m.spend_key
```
### Example: Platform Performance
```sql theme={null}
SELECT
LOWER(COALESCE(t.tier_1, m.tier_1, "No mapping")) AS tier_1,
LOWER(COALESCE(t.tier_2, m.tier_2)) AS tier_2,
LOWER(COALESCE(t.tier_3, m.tier_3)) AS tier_3,
LOWER(COALESCE(t.tier_4, m.tier_4)) AS tier_4,
LOWER(COALESCE(t.tier_5, m.tier_5)) AS tier_5
FROM m
LEFT JOIN taxonomy_lookup t
ON t.join_key = m.mta_tiers_join_key
```
***
## Field Reference
| Name | Description | Type |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---- |
| report | Report name | str |
| version | Schema version | str |
| advertiser | Rockerbox Account ID Note: this static column is only visible in Snowflake integrations | str |
| parent\_platform | The name of the ad platform (e.g., Facebook) | str |
| platform | The name of the ad platform overloaded with context of the version of the Rockerbox integration with a particular ad platform (e.g., facebook\_v2) | str |
| join\_key | The unique ID used to record spend for an advertising platform. This is typically the AD ID, or a composite identifier if the campaigns types within a platform support different reporting granularities. | str |
| tier\_1 | Marketing channel categorization level 1 (most broad), as defined in your mapping rules / reporting taxonomy. For example, a link where referrer url = Google and utm\_campaign = cpc may be mapped as tier\_1 = Paid Search and tier\_2 = Google | str |
| tier\_2 | Marketing channel categorization level 2 | str |
| tier\_3 | Marketing channel categorization level 3 | str |
| tier\_4 | Marketing channel categorization level 4 | str |
| tier\_5 | Marketing channel categorization level 5 (most granular) | str |
# Schema Overview
Source: https://data-foundation.rockerbox.com/warehousing/schemas
Learn about the datasets available for data warehouse integration
## Available Datasets
The datasets available for data warehouse integration are designed to provide a comprehensive view of your marketing data. Not only do they provide an up to date view of your marketing data, but they also provide a standardized set of schemas that can be used as a your marketing data foundation.
* **First-Party Event Data:** User-generated events and outcomes built on Rockerbox first party tracking.
* **Attribution Data:** Modeled credit and attributed revenue.
* **Platform Data:** Ad platform-reported spend, conversions, and performance metrics.
* **Normalization & Metadata:** Lookup, mapping, and enrichment tables for standardized reporting across sources.
## Schema Listing
| Type | Schema | Description | |
| ------------------------ | -------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | - |
| First-Party Events | Log Conversions | Event-level dataset with one row per conversion event occurrence, including geographic, device, and revenue information and excluding PII. | |
| First-Party Events | Log Conversions User Identifiers | Event-level dataset with one row per conversion event occurrence, including only user identifiers and any PII tracked on each conversion. | |
| First-Party Events | Clickstream | Event-level dataset with one row per Page View and Conversion event tracked onsite, attributed to click-based marketing touchpoints with standardized tier structure and spend keys applied. | |
| First-Party Events | Clickstream Event Parameters | Log-level dataset containing key-value event parameters associated with each Clickstream event, supporting detailed behavioral analysis and custom reporting. | |
| Attribution | Log MTA | Event-level dataset with one row per marketing touchpoint in each user path to conversion, excluding all PII. | |
| Attribution | Log MTA User Identifiers | Event-level dataset with one row per marketing touchpoint in each user path to conversion, including only user data associated with each marketing touchpoint. | |
| Attribution | Aggregate MTA | Aggregate dataset providing attributed conversions, revenue, and spend across all channels, structured as a pivot of log-level MTA and unioned with aggregate spend data | |
| Platform | Platform - `` | Platform-reported spend, conversions, and performance metrics. Supported Platforms: Google, Meta, Bing, TikTok, Snapchat, Pinterest, LinkedIn. | |
| Normalization & Metadata | Taxonomy Lookup | Latest reporting taxonomy for all channels with spend tracking. Used to apply mapping updates over historical data. | |
| Normalization & Metadata | Conversion Event Metadata | Metadata of all conversion events tracked in Rockerbox.. | |
***
## Entity Relationship Diagram
**Note**:
This diagram captures join keys and important fields to illustrate relationships between entities. Complete field references are documented per schema.
```mermaid theme={null}
erDiagram
%% FIRST-PARTY EVENTS: Conversions
LOG_CONVERSIONS {
string log_conversion_id
timestamp timestamp_conv
date date
string action
string currency_code
}
LOG_CONVERSIONS_USER_IDENTIFIERS {
string conversion_event_id
string date
string log_conversion_id
string external_id
string order_id
string uid
}
%% ATTRIBUTION: Multi-Touch Attribution
LOG_MTA {
string conversion_event_id
date date
string log_mta_id
string log_conversion_id
string platform_join_key
int sequence_number
timestamp timestamp_events
timestamp timestamp_conv
string event_type
string platform
string tier_1
string tier_2
string tier_3
string tier_4
string tier_5
}
LOG_MTA_USER_IDENTIFIERS {
string conversion_event_id
date date
string log_mta_id
string uid_event
string hash_ip_event
string user_agent_event
string base_id
}
AGGREGATE_MTA {
string conversion_event_id
string platform_join_key
date date
string platform
string tier_1
string tier_2
string tier_3
string tier_4
string tier_5
float included_spend
float normalized
}
%% FIRST-PARTY EVENTS: Clickstream
CLICKSTREAM {
string event_id
string session_id
string uid
string original_url
string spend_key
string tier_1
string tier_2
string tier_3
string tier_4
string tier_5
}
CLICKSTREAM_EVENT_PARAMETERS {
string event_id
string query_param_name
string value
}
%% REFERENCE DATA
CONVERSION_EVENT_METADATA {
string conversion_event_id
string conversion_event_name
date first_reporting_date
bool active
}
TAXONOMY_LOOKUP {
string join_key
string parent_platform
string platform
string tier_1
string tier_2
string tier_3
string tier_4
string tier_5
}
%% RELATIONSHIPS: Conversions
LOG_CONVERSIONS ||--|| LOG_CONVERSIONS_USER_IDENTIFIERS : "date, conversion_event_id, log_conversion_id"
%% RELATIONSHIPS: Attribution
LOG_CONVERSIONS ||--|{ LOG_MTA : "date, conversion_event_id, log_conversion_id"
LOG_CONVERSIONS_USER_IDENTIFIERS ||--|{ LOG_MTA : "date, conversion_event_id, log_conversion_id"
LOG_MTA ||--|{ LOG_MTA_USER_IDENTIFIERS : "date, conversion_event_id, log_mta_id"
%% RELATIONSHIPS: Clickstream
CLICKSTREAM ||--o{ CLICKSTREAM_EVENT_PARAMETERS : "date, event_id"
%% RELATIONSHIPS: Metadata joins
CONVERSION_EVENT_METADATA ||--o{ LOG_CONVERSIONS : "conversion_event_id"
CONVERSION_EVENT_METADATA ||--o{ LOG_CONVERSIONS_USER_IDENTIFIERS : "conversion_event_id"
CONVERSION_EVENT_METADATA ||--o{ LOG_MTA : "conversion_event_id"
CONVERSION_EVENT_METADATA ||--o{ LOG_MTA_USER_IDENTIFIERS : "conversion_event_id"
CONVERSION_EVENT_METADATA ||--o{ AGGREGATE_MTA : "conversion_event_id"
%% RELATIONSHIPS: Taxonomy joins
TAXONOMY_LOOKUP |o--o{ LOG_MTA : "platform_join_key -> join_key"
TAXONOMY_LOOKUP |o--o{ AGGREGATE_MTA : "platform_join_key -> join_key"
TAXONOMY_LOOKUP |o--o{ CLICKSTREAM : "spend_key -> join_key"
```
# Connect to Snowflake
Source: https://data-foundation.rockerbox.com/warehousing/snowflake
Setup guide to share Rockerbox schemas with your Snowflake account.
## Pre-Requisites
* A Snowflake account
* A user with `ACCOUNTADMIN` or admin access
> Rockerbox uses [Snowflake's Secure Data Sharing](https://docs.snowflake.com/en/user-guide/data-sharing-intro) to share Rockerbox datasets with your Snowflake account.
***
## Step 1: Select Snowflake in the Rockerbox UI
1. Navigate to the [Warehousing setup page](https://app.rockerbox.com/v3/data/exports/data_warehouse) in Rockerbox.
2. Select **Snowflake** as your destination.
3. Click **Connect to Snowflake**.
***
## Step 2: Gather Snowflake account context
1. Login to your Snowflake account.
2. Open the Query Editor, and run the following SQL:
```sql theme={null}
SELECT CURRENT_REGION(), CURRENT_ACCOUNT();
```
***
## Step 3: Enter Snowflake account context in Rockerbox
1. **Cloud Platform**\
Select your platform from the dropdown (e.g. `aws`, `gcp`, `azure`)
> 💡 This is the **prefix** of the value from `CURRENT_REGION()`\
> Example:\
> If the result is `aws_us_east_1`, then Cloud Platform = `aws`
2. **Cloud Region**\
Select your region (e.g. `us_east_1`)
> 💡 This is the **suffix** of the value from `CURRENT_REGION()`\
> Example:\
> If the result is `aws_us_east_1`, then Cloud Region = `us_east_1`
3. **Account Locator**\
Paste the value returned by `CURRENT_ACCOUNT()`
4. Click **✅ Setup Share**
***
## Step 4: Create the Rockerbox Database
* Execute the query provided by Rockerbox in the Snowflake query editor to create the database.
* ⚠️ Note: Requires the `ACCOUNTADMIN` role.
* 💡 You may rename the `rockerbox` database if it already exists — but do not modify the rest of the query.
***
## Step 5: Sync Rockerbox Data Sets
* Choose which datasets to share:
* **Platform Data**: Select the ad platforms you actively use.
* **Rockerbox Data**: Select **Conversion** and any **Log Level MTA** datasets for each conversion event that you need.
* Click **Sync this dataset** when ready.
📦 `aggregate_mta` and `taxonomy_lookup` tables are automatically created in the data share.
### ⚠️ Backfill Notes
* **Platform Performance Schemas**: No data is automatically backfilled. This can be backfilled on a limited basis upon request to `support@rockerbox.com`.
* **Rockerbox First Party Data Schemas**: For each conversion dataset, Rockerbox will backfill based on the conversion event “First Reporting Date” in Rockerbox\
💡 If no date is set, Rockerbox backfills one day of data. This process can take up to **24 hours**.
***
## Step 6: Query your new data tables
### Sample queries: `aggregate_mta` table
Test setup
```sql theme={null}
SELECT * FROM .public.aggregate_mta LIMIT 1;
```
Report even weight attributed conversions and spend by conversion event + tier\_1 (most broad reporting taxonomy dimension)
```sql theme={null}
SELECT
conversion_event_name,
tier_1,
SUM(even) as conversions_even,
SUM(included_spend) as spend
FROM .public.aggregate_mta
WHERE
date >= DATEADD(day, -7, CURRENT_DATE)
AND date < current_date;
```
# Snowflake Reader Account
Source: https://data-foundation.rockerbox.com/warehousing/snowflake-reader
Setup your reader account for egress.
## Overview
With a Snowflake Reader Account, you can access Rockerbox datasets and egress the data to the platform of your choice.
* You do not need to be a Snowflake customer to have access to a reader account.
* You will not pay for any storage or compute for this account; these are billed to Rockerbox as the provider of the account.
> 📊 Rockerbox implements monthly credit quota on the account, and will work with you to ensure that you can run the egress out of Snowflake within the monthly credit quota.
## Pre-Requisites
* An account with a data warehouse provider where you will store the data egressed from the Snowflake Reader Account.
***
## Step 1: Access your reader account
* Rockerbox support will provide you with your: (1) username (2) password (3) account URL
* Login to your Snowflake reader account
***
## Step 2: Sync Rockerbox Data Sets
* Choose which datasets to share and create the share tables in the Rockerbox UI:
* **Platform Data**: Select the ad platforms you actively use.
* **Rockerbox Data**: Select **Conversion** and any **Log Level MTA** datasets for each conversion event that you need.
* Click **Sync this dataset** when ready.
📦 `aggregate_mta` and `taxonomy_lookup` tables are automatically created in the data share.
### ⚠️ Backfill Notes
* **Platform Performance Schemas**: No data is automatically backfilled. This can be backfilled on a limited basis upon request to [support@rockerbox.com](mailto:support@rockerbox.com).
* **Rockerbox First Party Data Schemas**: For each conversion dataset, Rockerbox will backfill based on the conversion event “First Reporting Date” in Rockerbox.\
💡 If no date is set, Rockerbox backfills one day of data. This process can take up to **24 hours**.
***
## Step 3: Query your new data tables
Open a new SQL worksheet to access the query editor and confirm that you can run a query against a share table.
Test setup
```sql theme={null}
SELECT FROM .public.aggregate_mta LIMIT 1;
```
***
## Step 4: Build egress pipeline
**Objective:** Pull Rockerbox data out of a Snowflake reader account and load it to an external destination.
**General Notes**
* Run this script for each table that needs to be egressed.
* Rockerbox recommends processing updates 3 times per day for the most recent 2 days.
* For longer lookback windows, you can ingest updates on a rolling basis depending on your integrations. Work with Rockerbox Support to determine the right update frequency and configure the lookback appropriately.
#### Step 1: Create python connector
```python theme={null}
# https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect
con = snowflake.connector.connect(
user='READER_ACCOUNT_USER',
password='READER_ACCOUNT_PASSWORD',
account='READER_ACCOUNT_ACCOUNT_IDENTIFIER'
)
```
#### Step 2: Create a list of dates to process for a given table
```python theme={null}
# Run query to identify files in the Snowflake external table that were updated in the last 24 hours
# Note: TABLE_NAME is dependent on the name of the table you defined in the Rockerbox UI
# Note: Adjust the lookback interval depending on how frequently you want to extract the data
DATABASE_NAME = "ROCKERBOX"
SCHEMA_NAME = "PUBLIC"
TABLE_NAME = "PLACEHOLDER"
with conn.cursor() as cur:
MANIFEST_QUERY = f"""
-- Replace this with SQL query from below depending on table schema
SELECT * FROM
"""
# Results is a list of tuples that contains fields:
# - advertiser, report, identifier, date, registered_on
# - iterate through dates to load into external destination
results = cur.execute(MANIFEST_QUERY).fetchall()
```
```python theme={null}
SELECT
SPLIT_PART(SPLIT_PART(file_name, '/', 2), '=', 2) as advertiser,
SPLIT_PART(SPLIT_PART(file_name, '/', 5), '=', 2) as report,
SPLIT_PART(SPLIT_PART(file_name, '/', 6), '=', 2) as identifier,
SPLIT_PART(
REGEXP_SUBSTR(
file_name,
'date=[0-9]{4}-[0-9]{2}-[0-9]{2}',
1,
1
),
'=',
2
) AS date,
registered_on
FROM
TABLE(
{DATABASE_NAME}.information_schema.external_table_files(
TABLE_NAME => '{DATABASE_NAME}.{SCHEMA_NAME}.{TABLE_NAME}'
)
)
WHERE
last_modified >= CURRENT_TIMESTAMP - interval '24 hour'
```
```python theme={null}
SELECT
file_name,
SPLIT_PART(SPLIT_PART(file_name, '/', 3), '=', 2) as advertiser,
SPLIT_PART(SPLIT_PART(file_name, '/', 2), '=', 2) as report,
SPLIT_PART(SPLIT_PART(file_name, '/', 7), '=', 2) as conversion_event_id,
SPLIT_PART(
REGEXP_SUBSTR(
file_name,
'date=[0-9]{4}-[0-9]{2}-[0-9]{2}',
1,
1
),
'=',
2
) AS date,
registered_on
FROM
TABLE(
{DATABASE_NAME}.information_schema.external_table_files(
TABLE_NAME => '{DATABASE_NAME}.{SCHEMA_NAME}.aggregate_mta'
)
)
WHERE
last_modified >= CURRENT_TIMESTAMP - interval '24 hour';
```
#### Step 3: Load the data to your destination
```python theme={null}
## INSERT CODE TO LOAD TO EXTERNAL DESTINATION HERE
with conn.cursor() as cur:
for result in results:
date = result[3] # position of "date" in the output tuple
results = cur.execute("""
SELECT * FROM {DATABASE_NAME}.{SCHEMA_NAME}.{TABLE_NAME}
WHERE date = '{date}'
""").fetchall()
# Write "results" output to external destination
```
# Creating Your Data Foundation
Source: https://data-foundation.rockerbox.com/why-rockerbox
Leveraging Rockerbox as your marketing data foundation
A data foundation is a critical component for any marketing organization. It provides a single source of
truth for all marketing data.
By taking a data-first approach, Rockerbox operates as an analysis agnostic layer to marketing measurement, that
can power all types of analysis range from simple spend consolidation up through complex attribution models.
#### Unbiased Source of Truth
By leading with a data foundation, Rockerbox can serve as an unbiased source of truth:
* Eliminate conflicting data from different platforms
* Standardized reporting across all marketing channels
* De-duplicated conversion tracking
#### Operational Efficiency
Additionally, Rockerbox immediately provides operational efficiency by eliminating the need for manual data aggregation or spreadsheets.
* Automated data collection and normalization
* No manual data aggregation or spreadsheets
* Real-time data access and reporting
#### Complete Marketing View
Finally, Rockerbox provides a complete marketing view:
* Track both online and offline channels
* Connect upper and lower funnel activities
* Understand cross-channel customer journeys