Free Reporting Dashboard for Holiday Campaign Insights

The holiday season is upon us, and according to eMarketer, it’s expected to be the first-ever to hit the trillion-dollar mark for retail sales. Are you prepared to glean actionable insights from your eCommerce data that you can use to improve your digital campaigns during this busy time of year? Do you know:

• Which of your products sell best together?
• How your product pairings vary by region?
• What audiences have the highest likelihood to purchase?

We can help. To help spread some holiday cheer — and give you insights that can boost your sales — we created a Data Studio dashboard based on BigQuery analysis. It’s currently helping 25 of our clients drive insights, and now we’re offering it to you for free, too. These insights will allow you to build more informed audience segments, including those with a higher propensity to buy and retarget them to boost your conversion rate. Like many of our clients, you may be surprised by how much particular regions play a role in top product pairings. 

What It Looks Like

Here are some samples of what the dashboard might look like when implemented:

 

 

How to Get Started with the Dashboard

In order to get started, you (or your clients) need to have Google Analytics 360, Enhanced Ecommerce and BigQuery integration. If you check those three boxes, you’re ready to begin. We’re happy to set up the dashboard for you and share insights – free of charge. Simply contact us and we’ll put the process in motion. But if you’d rather do it yourself, here’s how::

1. Customize and run the query below against your Google Analytics dataset
   1. You must have the permissions: Data Editor & Job User
2. Save the results as a new table
3. Connect the new table to this Data Studio report for visualization
4. Customize the Data Studio report with your branding and preferences

Query for Step One

WITH product_data as
      (
      SELECT
        date,
        geoNetwork.country,
        geoNetwork.city,
        geoNetwork.region,
        hits.transaction.transactionId as transaction,
        product.v2ProductName as name
      FROM
        `cohinc-146020.132851443.ga_sessions_201812*`,
        unnest(hits) as hits,
        unnest(hits.product) as product
      WHERE
        _TABLE_SUFFIX BETWEEN “01” AND “25”
      AND
        hits.eCommerceAction.action_type=’6′
      GROUP BY
        1, 2, 3, 4, 5, 6
      )
    , pair as
      (
      SELECT
        product1.date, product1.name as Product1, product2.name as Product2,
        product1.country, product1.region, product1.city,
        count(distinct product1.transaction) OVER(partition by product1.name) as timesproduct1bought
      FROM
        product_data product1
      FULL OUTER JOIN
        product_data product2
      ON product1.transaction = product2.transaction AND product1.date=product2.date AND product1.name < product2.name
      )
    , aggregate1 as (
      SELECT
          Product1,
          Product2,
          country,
          region,
          city,
          count(*) OVER(PARTITION BY region, Product1, Product2) as timesBoughtTogether,
          max(timesproduct1bought) as timesproduct1bought
      FROM
          pair
      GROUP BY 1,2,3,4,5
      HAVING Product1!=Product2
      ORDER BY timesBoughtTogether DESC
    )
SELECT
*
, FIRST_VALUE(Product1) OVER(PARTITION BY region ORDER BY timesBoughtTogether DESC) as TopPairPerRegion_Product1
, FIRST_VALUE(Product2) OVER(PARTITION BY region ORDER BY timesBoughtTogether DESC) as TopPairPerRegion_Product2
FROM
aggregate1
ORDER BY region ASC, timesBoughtTogether DESC

 

Please contact us with any questions.

Here’s to a prosperous holiday season!