Log Conversions - Rockerbox Data Foundation Docs

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:

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.<geo>.<web_or_app>`)	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

​Description

​Schema Structure

​Usage Notes

​Table Creation

​Table Relationship

​Partition Keys

​Logical Primary Key

​log_conversions

​log_conversions_user_identifiers

​Field Reference

​Table: log_conversions

​Table: log_conversions_user_identifiers