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