Getting started with Rockerbox Data Warehousing

Updated by James Shields

Getting started with Rockerbox Data Warehousing can seem like a big task, here are your first few steps

Questions to Answer

  1. How do I get set up with Rockerbox Data Warehousing?
  2. How do I write my first query?
  3. What data is in the tables?
  4. How do I join all the tables together?
  5. How do I build a dashboard in Looker or Tableau?
  6. How do I perform advanced analysis?

How do I get set up with Rockerbox Data Warehousing?

  1. Check the Data Warehousing Sync page in the Rockerbox platform
    1. If it says "Contact Sales to get access to Export" then you need to speak to your Customer Success Manager to get Data Warehousing added to your contract and enabled.
    2. If it says Snowflake, Google BigQuery or Amazon Redshift Data Sync then you are ready
  2. Open your Data Warehouse terminal in either Snowflake, Google BigQuery or Amazon Redshift. These may all look different and you'll need to speak to your internal data team to get access
  3. If you're using a model database query Graphic User Interface (GUI), you should see the following:
    1. the name of the database
    2. a list of tables
    3. somewhere to write your query
    4. somewhere to see your results

With that confirmed, you're ready to write your first query.

How do I write my first query?

  1. Select and type the below
  2. SELECT TOP 10 * FROM DATABASE_NAME.TABLE_NAME
  3. Replace DATABASE_NAME with the name of your database, this is usually RB_your company name
  4. Replace TABLE_NAME with the name of one of your tables (pick ant from 3.b. above if you're unsure)
  5. Hit Run and wait for your data to populate

Great job, you've run your first query using Rockberbox data.

What data is in the tables?

  1. Rockerbox tables
    1. Buckets Breakdown
      This is a count of conversions, revenue and spend per day for marketing activity as per your 1 to 5 performance reporting tiers. Conversions and revenue is provided by first, last, event and MTA (normalized) attrbiution model and its available for all and just new customers (columns prefixed with ntf_).
    2. Log Level MTA
      This is a list of marketing touchpoints associated with your conversions. This table needs to be set up for each of your conversion events that Rockerbox is tracking (such as View Product, Add to Cart and Purchase). It has one entry per marketing touchpoint for each conversion and provides details for that touchpoint such as its 1 to 5 performance reporting tiers, its sequence number, the count of marketing touchpoints before it, its MTA conversion and revenue credit and other details like UTM params and spend key if associated. 
    3. Conversions
      This is a list of your conversions. This table needs to be set up for each of your conversion events that Rockerbox is tracking (such as View Product, Add to Cart and Purchase). It has one entry per conversion and provides details for each conversion such as date and, if a purchase event, will also contain order number as well as customer information like email, phone number and ID if applicable. All other purchase data is provided though the unstructured additional_attributes field. 
    Full table schemas are available at https://help.rockerbox.com/category/xt2q90i2i1-rockerbox-datasets
  2. Platform tables
    1. Platform-reported performance pulled and standardized for ease of use. Supported Platforms: Google, Bing, Facebook, TikTok, Snapchat, Pinterest, Linkedin
    Full table schemas are available at https://rockerbox.helpdocs.io/category/rmc4ims6n7-platform-performance

How do I join all the tables together?

  1. Log Level MTA and Conversions 
    SELECT * 
    FROM [Log Level MTA]LEFT JOIN [Conversions] 
    ON [Log Level MTA].conversion_key = [Conversions].conversion_key
    Join on conversion_key which is in both tables. There is a many to one relationship between Log Level MTA and Conversion. Think “each conversion can have multiple marketing touch points". Where a touchpoint has multiple conversions, the touchpoint is duplicated in Log Level MTA with a unique conversion and revenue credit for each associated conversion.
  2. Buckets Breakdown and Conversions
    SELECT *
    FROM [Buckets Breakdown]
    LEFT JOIN  [Conversions]
    ON [Buckets Breakdown].date = [Conversions].date
    AND [Buckets Breakdown].tier_1 = [Conversions].tier_1
    AND [Buckets Breakdown].tier_2 = [Conversions].tier_2
    Join on date and tier 1 to 5 which is in both tables to get conversions per day and reporting tier. There is a many to many relationship between the tables so will require grouping to aggregate
  3. Conversions and (Shopify) order tables 
    Join on conversion and Order ID in each table respectively. There is a one-to-one relationship Conversions and Shopify order table. Except for purchase events that Rockerbox doesn’t account for such as returns

How do I build a dashboard in Looker or Tableau?

To pull your Rockerbox data into a data visualization or reporting tool - like Looker or Tableau - follow the instructions

  1. Open your data visualization or reporting tool
  2. Connect to a new data source
  3. Select your data warehouse type (eg Snowflake, Redshift or BigQuery)
  4. Follow the instructions to enter your server name, access credentials and select the tables you want to connect
  5. Some data visualization or reporting tools - like Looker or Tableau - let you define table joins so you can perform analysis across tables. In other tools, you may have to build these as queries in your Data Warehouse terminal.
  6. Once the data is connected, you can build dashboards using all of your Rockerbox data

How do I perform advanced analysis?

Now you're ready to perform advanced analysis with your Rockerbox data

  1. Check out our Data Warehouse Use Case Resource Library
  2. Speak to your CSM to discuss a specific use case

How did we do?