Google Analytics is a great tool that allows you to understand the user behaviour of those visiting your website. Whereas, Shopify is fantastic at providing detailed coverage of performance from an eCommerce perspective. Both are great at what they do, but there comes a point where the data from Shopify and Google Analytics (GA) in isolation fails to provide the deeper insights you might want. Whilst each has the ability to provide powerful information, there is a much greater level of insight that can be gained by combining the data.
In this particular example, we will cover how we achieved this for our friends over at Pooch & Mutt, a UK-based company that produces natural and healthy dog food. We wanted to enhance insights by taking the data collected in Google Analytics and using it in conjunction with eCommerce data from Shopify, as well as the subscription data from Recharge and joining this with legacy subscription data from Bold. Using this variety of data sources, we could build a richer, more complete picture.
This blog will seek to provide an overview of how we aggregate this data, and then outline the thought processes behind combining it to extract and visualise these data-driven insights. Specifically, we will explain how we went about answering the following three questions;
Before we get into answering these questions, let’s first cover how we collected all the data from their respective platforms into a centralised location and built a table we could use to collate this data.
We chose to use BigQuery (BQ) to centralise the data because of its versatility and capability to handle the combination of datasets. The flowchart below highlights the general connections between data and the way in which this was achieved:
Stitch is an affordable tool that allows you to extract and load data into pre-built data warehouses.
We easily connected to Shopify and Recharge through Stitch, where we simply selected the tables we wanted. In this case, the most important tables are ‘customers’ and ‘subscriptions’ from Recharge, and ‘orders’ from Shopify.
This information is then synced with BQ every 6 hours through Stitch’s automated scheduling process.
For historical subscriber data, we took a final export and uploaded this to BQ via Google Sheets.
Whilst Stitch can also be used for this, we opted to use RStudio to get this data into BQ. If you’re interested in learning more about how you can do this, you can read a blog by our very own R Studio expert Danny Smith on how to use GA API with R.
Once we had all the required data in BQ, we could begin combining it. The steps we took to do this are as follows:
We then scheduled this query to run once a day and used the result as the master table for additional queries. Doing this meant we didn’t have to run the full query multiple times, as it’s a static table that only updates when scheduled, rather than every time a request is made through the Looker Studio Dashboard.
Now that we’ve covered the methodology and outlined the steps taken to get all the data we need, we can move on to discussing how we used this data to answer our questions.
These are just some surface-level examples of the views we generated using these reports. With the data, there are endless possibilities – other examples from this project include looking at customer lifetime value from various perspectives and assessing the use of discount codes and their impact across the lifetime behaviour of customers. With these insights, they can be used to supplement business decisions. For example, knowing that it takes an average of 3 orders before becoming a subscriber, you can look to share offers and incentives that encourage customers to become subscribers after 2 purchases. The results of this can then be measured at a later time.
This case study demonstrates the potential for incredibly valuable data-driven insights that can be gained through the combination of multiple data sources, from GA to Shopify and beyond. Once this infrastructure is created, it can be maintained dynamically and used to report on numerous different aspects of the business.
If the questions we answered in this blog are similar to those that you might be interested in for your own business, you can get in touch with us by filling out our contact form.