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::
- Customize and run the query below against your Google Analytics dataset
- You must have the permissions: Data Editor & Job User
- Save the results as a new table
- Connect the new table to this Data Studio report for visualization
- 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!