August 29, 2024

Unified ROAS? your guide to overcoming data silos in e-commerce analytics

Modern e-commerce brands rely on an ever-increasing number of apps and online services to promote and sell their products. Online advertising platforms, influencer partnerships, and web traffic tracking are all mission-critical technologies producing mission-critical data. But, interrogating this data means logging in to each platform and viewing that data in isolation.

‍

Are your e-commerce insights in multiple places?

‍

Here’s a common e-commerce analytics scenario:

‍

Data on your latest TikTok campaign can be found in the TikTok UI
Data on your Shopify sales lives in the Shopify UI
Web analytics are in Google Analytics

‍

There are three separate datasets here, maintained by three separate companies. With new advertising platforms and customer tracking technologies added to the stack every month, this data siloing is causing real issues for e-commerce companies.

‍

How many CSVs are you juggling?

‍

Are you spending innumerable hours downloading CSVs from different sources and crunching numbers in Google Sheets or Excel? Are you trying to calculate ROAS (Return on Ad Spend) with source data from Meta Ads, Google Ads, and TikTok Ads? What about LinkedIn Ads and Reddit Ads? If this is you, then you're not alone; it's a universal problem spanning a global industry.

‍

A multi-million dollar e-commerce brand recently approached me with exactly this problem.

‍

They wanted a single data model containing data from TikTok, Meta Ads, Microsoft Ads, Google Ads, Impact, Google Analytics, and Shopify in one place. This would allow them to track their customer journey from acquisition to order and finally give their board ROAS figures they can trust.

‍

One tool, all sources — ELT simplified

‍

To achieve this, we would need to connect to each platform’s API and load the data to a cloud data warehouse where it could be modeled and visualized. After trying several, they selected Snowflake.

‍

Historically, building and maintaining these API connections would have been a huge task requiring a team of developers. Using Precog, however, meant that each connection could be set up in just a few minutes, and we were able to load all the data we needed to Snowflake in a streamlined and time-effective manner.

‍

With the complexities of interacting with multiple APIs ameliorated, we were able to focus on building a unified data model.

‍

A unified advertising data model is closer than you think

‍

First, we set up Google E-commerce Tracking in Shopify. This meant that each time an order was made on Shopify, the order number was recorded in Google Analytics as a Transaction ID. Because these Transaction IDs are sent by customer browsers, we found that we had around 90% coverage. There are Shopify plug-ins that allow Transaction IDs to be sent from the Shopify app instead for improved accuracy.

‍

Using the Google Analytics 4 Precog connector, we created a lookup table that mapped campaign IDs and channel groupings to these Transaction IDs. This allowed us to associate rows in our Shopify Orders table with the advertising source to which Google Analytics attributed them.

‍

One limitation is that we were bound to use the Google Analytics 4 attribution model. This was acceptable since we used the same attribution model across all advertising sources.

‍

Conversely, we could join detailed advertising datasets from Precog’s connectors for TikTok, Meta, Impact, Klayvio, and Microsoft Ads. This join depended mainly on the quality of the UTM parameters ascribed to each campaign.

‍

End-to-end customer journey tracking

‍

By ensuring that we used clear and unique Campaign IDs and UTM trackers, we achieved very robust relationships between our advertising data sources, Google Analytics, and, in turn, Shopify. With these relationships in place, we achieved our goal of end-to-end customer journey tracking. An added benefit of this data model was that it made it easy to implement a Power BI-based data distribution strategy, which proved more effective than previous systems that relied on considerable manual work.

‍

Savings? Time is money

‍

Scheduling data loads from all the connected APIs saved our client hours of time and effort. No more manually downloading spreadsheets. It also gave them greatly improved clarity on advertising returns across the whole business by leveraging a robust data model and modern visualization tools.

‍

This approach will benefit e-commerce brands and online sellers looking to better understand their Return on Ad Spend (ROAS). To learn more about how to implement this in your business, contact us at Precog.

‍

Ad Connectors

‍

See all connectors