Reporting Schema
Below you can find the main schema and corresponding definitions for the two main Rockerbox reports:
Available Report Types
Available Columns
Primary Key Columns
Column Name | Definition |
conversion_hash_id | Identifier per unique conversion (this is a hash of the conversion_key value) |
event_id | Identifier per unique marketing touch point |
base_id | internal identifier or an external identifier depending on segment setup (i.e. email, Segment anonymous ID, etc.) |
Conversion Info Columns
Column Name | Definition |
action | Name of the conversion event |
uid | Rockerbox unique User ID |
new_to_file_key | Used to identify a unique user (this is typically your provided User ID) |
Email address of the user | |
conversion_key | An identifier used to denote a unique conversion event (this is typically your provided Order ID) |
hash_ip_conv | Hashed IP address of user when they converted |
revenue | Revenue associated with a given conversion |
date | Date when the conversion occurred (in UTC) |
timestamp_conv | Timestamp of when the conversion occurred (ISO 8601 format in UTC) |
conversion_count | Number of conversions seen for that particular user |
conversion_max | Timestamp of the most recent conversion seen by Rockerbox for that user |
conversion_min | Timestamp of the first conversion seen by Rockerbox for the user |
new_to_file | At the time of conversion, if Rockerbox has seen that user make a Purchase before, then new_to_file = 0. Otherwise, they are considered a new customer and new_to_file = 1. |
Marketing Touchpoint Info Columns
Column Name | Definition |
hash_ip_events | Hashed IP address of user for a particular marketing touchpoint |
timestamp_events | Timestamp of when the marketing touchpoint occurred (ISO 8601 format in UTC) |
onsite_count | Total number of interactions (page views) on your website |
total_events | Total number of marketing touchpoints leading up to the conversion |
sequence_number | The order of when this marketing touchpoint occurred (1 = first, 2 = second, etc.) |
original_url | URL of the page where the marketing touchpoint occurred |
request_referrer | Page Referrer (the previous site where the user came from) |
tier_1 | Marketing channel categorization level 1 (most broad). For example, a link where referrer url = Google and utm_campaign = cpc may be mapped as |
tier_2 | Marketing channel categorization level 2 |
tier_3 | Marketing channel categorization level 3 |
tier_4 | Marketing channel categorization level 4 |
tier_5 | Marketing channel categorization level 5 (most specific) |
weight | Likelihood of the user to convert, given that they interacted with this marketing touchpoint (based on model) |
total_weight | Sum of weights of all marketing touchpoints that led to the conversion |
normalized | Calculated by dividing weight by total_weight (this ensures that the sum of normalized weights of marketing touchpoints leading to a conversion will equal 1) |
revenue_normalized | Using the normalized weight, the portion of conversion revenue that is attributed to this marketing touchpoint |
first_touch | Will be 1 if this marketing touchpoint is the first interaction. Otherwise will be 0. |
revenue_first_touch | If the marketing touchpoint is the first interaction, it will get full revenue credit. Otherwise will be 0. |
last_touch | Will be 1 if this marketing touchpoint is the last interaction. Otherwise will be 0 |
revenue_last_touch | If the marketing touchpoint is the last interaction, it will get full revenue credit. Otherwise will be 0. |
even | Fractional credit a marketing touchpoint receives if each touchpoint gets equal weight |
revenue_even | Using the even weight, the portion of conversion revenue that is attributed to this marketing touchpoint |
matches | List of categorization rules that a touchpoint has matched with. The last rule in the list is applied. |
onsite_count | Number of marketing events that Rockerbox sees for that user |
onsite_max | Timestamp of the most recent event for the user |
onsite_min | Timestamp of the first event for the user |
tier_1_lower | The lowercase letters version of tier_1 that Rockerbox uses for mapping purpose |
tier_one tier_two tier_three tier_four tier_five | Values passed to Rockerbox on click URLs and impression tracking pixels. If |
Since the MTA reports are log-level and detail each marketing and conversion event combination, conversion-level information will be duplicated. If you’re ingesting this data, you need to account for that. For example, the revenue column is repeated for each marketing event. If you simply sum the revenue column for a given day, you will overcount.
Here’s a sample representation of different revenue columns and how they would report out values for a single conversion.
Marketing Channel | revenue | revenue_first_touch | revenue_last_touch | revenue_even | revenue_normalized |
Paid Search | 100 | 100 | 0 | 25 | 37 |
100 | 0 | 0 | 25 | 22 | |
Paid Social | 100 | 0 | 0 | 25 | 31 |
Display | 100 | 0 | 100 | 25 | 10 |