Using Google Analytics Parallel Tracking and a custom data pipeline for Shopify, we managed to get all necessary data in BigQuery for more advanced analysis and reporting.
About the client
Our client is a successful e-commerce business, headquartered in the UK.
Their focus is on food supplements, nutritional products, and diet plans.
Depending on a season, their monthly online revenue is 1-1.5 million GBP (1.3 – 2 million USD). This comes from around 14k orders every month.
Due to NDAs, we are not allowed to disclose the name of the client. Everything else in this case study remains unchanged.
The challenge
When the company in the focus of this case study first contacted us, their main concerns were about properly tracking customer lifetime value (LTV) and churn. Being in the business of food supplements where, naturally, a big chunk of revenue is coming through subscriptions (automatically recurring orders), keeping track of these metrics is extremely important.
Tech stack
The main tech tool stack for this client was a fairly standard one for an e-commerce business of this size. Shopify Plus for website and order processing, ReCharge for handling subscriptions, Google Analytics for general reporting, and ShipStation for managing shipments.
***
While Shopify can provide you with reports for both LTV and churn (via ReCharge analytics), it is rather limited when it comes to attributing those numbers to your marketing and other efforts like CRO or A/B testing.
The client pointed out three main areas where they need a more detailed look into LTV and churn-related metrics:
- Ad Spend (mostly Google Ads and Facebook)
- E-mail campaigns
- CRO and A/B testing
The solution
After having several meetings with various teams from the client’s side, we had a quite good understanding of what kind of reports they’re trying to build and what kind of data it requires.
In order to accommodate all of their needs, we had to involve data from the following sources:
- Shopify
- ReCharge
- Google Analytics
- Google Ads
- Facebook Ads
- ShipStation
- Google Optimize
As our solution involved data from more than three sources, it was quite obvious that setting up a data warehouse is required. The client decided to go with our default recommendation for data warehouse software — BigQuery.
Getting data in BigQuery
As LTV and churn had always been such essential metrics for the client, they had already tried solving this in-house. Their existing tool stack consisted of Stitch, Supermetrics, MySQL database and Google Sheets. While this could’ve worked in theory, the reality was a really slow system that constantly failed and produced inaccurate numbers.
Our solution involved using Parallel Tracking technology for Google Analytics and a custom data pipeline for the rest of the sources. This enabled us to modify the ETL for maximum efficiency while meeting all the data requirements.
Building the reports
Since our client was already familiar with the rest of Google’s Marketing Suite and it works really well with BigQuery, using Data Studio for building the reports was an easy choice. Besides being a free (!) tool, by leveraging the BigQuery BI Engine, Data Studio can provide you with the best performance as well!
After understanding the reports that would be used most frequently, we built a set of 2 main dashboards:
- LTV per source/medium/country/product/campaign/age
- Churn per source/medium/country/product/campaign/age
These dashboards provide the client with a good overview of their key metrics and how they correlate with some of the more common dimensions/attributes. This is a good stepping stone for a deeper analysis and digging for insights.
Luckily, the client’s marketing team already had a few people that were comfortable working with SQL, Jupyter Notebooks etc. This meant that by pointing them in the right direction and providing a few guidelines, they had everything needed for digging deeper into data stored in BigQuery.
Besides dashboards and enabling ad-hoc analysis, a few teams also wished for a periodic automated report in their inbox. This was also something we were happy to set up using some custom software on Google Cloud.
Conclusion
While this project started as a rather standard one for us, it turned out to include several custom parts that made it fun for us and enabled maximum effectiveness for the client.
What better way to take it all together than a few words from the team we worked with on the client’s side.
We turned to Reflective Data with a rather concrete goal in mind — to fix our LTV and churn tracking and reporting. Being in the business of food supplements, a large portion of our revenues come from recurring orders. This makes keeping track of those metrics extremely important for us. Not only was Reflective Data able to set up a proper tracking system, they helped us figure out the dashboards, email reports and helped our analysts on SQL analysis! Couldn’t be happier about this!
Nothing’s better than such warm words from a happy client!
If you, too, need help with your analytics & data setup, get in touch with us for a free consultation session. I’m sure we can find a solution that suits both of us.
2 thoughts on “Case Study: Sending Data From Shopify and Google Analytics to BigQuery for More Advanced Analysis”