Revenue reattribution using media platforms, GA, and ERP data

Category: Ecommerce | Solution: Data source treatment | Type: Recurring | Client: Caran d’Ache
Tags: #Performance #Business #KPI adjustment

Revenue is one of the most important KPIs that every company measures. It is derived from different departments, calculated in various ways, and analyzed by distinct business units with various dimensions.

One of the biggest challenges in analyzing revenues in the web industry is attribution: to properly decide where to invest in advertising on media platforms, traffic managers need to understand where the current revenue comes from. However, this is quite difficult because each platform attributes sales differently, web analytics tools like Google Analytics provide a partial and often subjective picture, and even the total absolute numbers don’t always match what appears in the ERP back office.

In this article, we introduce how Datama helped one of our clients, Caran d’Ache, resolve such challenges by reattributing revenue from various incomplete data sources in BigQuery and then monitoring performance on a daily basis using Datama Detect.

”Thanks to Datama, we have been able to reconcile disparate data sources and reattribute the unallocated traffic from Google Analytics. This has given us a clearer and more reliable view of our revenue distribution by acquisition channel and allows us to better understand the performance of our campaigns in real-time.”

India Valla Mothes

Head of Ecommerce, Caran d’Ache

Context

Our partner, Caran d’Ache, is a Swiss manufacturing company of art materials and writing instruments. It operates an e-commerce platform selling products like pens, pencils, gouache paints, and ink cartridges. Over the course of 100 years, Caran d’Ache instruments have become internationally renowned for the drawing and writing experience they offer through the elegance of their lines, the quality of their materials, the purity of their pigments and the technical brilliance of their design. The company runs numerous stores and collaborates with dealers globally. It also distributes directly online with its own ecommerce store.

To maximize revenue, it invests in various social platforms. Consequently, the company’s total revenue is recorded by media channels. Additionally, Google Analytics (GA) serves as their data collector and manager, also providing total revenue figures. Lastly, the company’s ERP platform, SAP, records total revenue.

Pain point: Inconsistent revenue with missing sub-totals by expected channels

The issue arises from inconsistencies in revenue figures from the media platforms, Google Analytics, and SAP:

The revenue figures reported by platforms for attributed revenue are very different from those attributed by Google Analytics (GA)
GA assigns part of the revenue to “direct” traffic, which doesn’t truly exist, but due to third-party cookie restrictions, ad blockers, it’s impossible to determine where this “direct” traffic actually comes from.
The revenue in SAP doesn’t match those in GA—even less than the total revenue reported across all platforms.The sub-summed values differ across sources, too.
SAP provides a total revenue figure, regarded as the most reliable by the business team, but it does not break down revenue by channels

So, the objective is to use SAP’s total revenue as the baseline and accurately parse it by different channels.

Solution

The issue was efficiently addressed by Datama team, making use of the data architecture of the partner. The steps taken are outlined below:

1. Data collection and validation

The first step involved collecting the most reliable media revenue data from available sources.

Using python code and cloud functions, we created a universal mail connector to pull data from different media channels, Google Analytics and SAP platform into BigQuery without being dependent on costly tools such as Supermetrics or Funnel.io and requiring low maintenance as we don’t depend on those platform APIs, but on email exports.

2. Revenue reattribution

Within Bigquery environment, we reattributed the revenue as following:

For revenue reported by paid platform (Meta, DV360, Pinterest, Google Ads, and Criteo), we retained the values directly from the media platforms, as these figures are more direct than those from Google Analytics and trusted by the agencies.
For “free” traffic (Organic and Mail channels), we relied on Google Analytics data, as the more reliable source of truth
The Direct channel was artificially set to zero to reflect the absence of “free” traffic.
Using those figures, we then ensure that the total sum matches the SAP numbers by pro-rating them at a country level

3. Monitoring and alerting

After the reattribution, the new total revenue matched the SAP figure and was correctly segmented by media channels. This provided the company with a reliable and actionable revenue breakdown, and corrected ROAS.

To ensure efficient monitoring, we connected the results from BigQuery with the Datama platform and set up the Datama Anomaly Detect alerting, which helps monitor the dynamic availability of corrected revenue and the evolution of performance drivers. This solution sends alerts in case of issues such as missing or delayed revenue, or significant fluctuations in performance drivers. Below is an example of monitoring the variation of total revenue on a daily basis with the help of Datama Anomaly Detect solution. If the revenue or any acquisition KPI (CPC, CVR, ROAS…) is not matching expectations, an alert by email is sent out for human check & intervention.

Conclusion

By leveraging BigQuery, a systematic approach to reattribution, and Datama solution, the latter successfully addressed the discrepancies in the company’s revenue data, helping our partner:

Reduce the cost by implementing a method to automatically pull media platform data into BigQuery, at almost no cost to our partner—compared to the ~€1k+ per month it would cost to achieve the same result using Supermetrics or Funnel.io.
Get the accurate performance table in the same place for all platforms (Click/Cost/Impression) for each media channel (per campaign and country) available for any reporting tool

Get ROAS per acquisition channel that includes the reattribution of direct traffic and matches the actual revenue in the ERP, while remaining directionally aligned with the figures reported in the media platforms.
Automatically monitor the data synchronization anomaly and reactively explain the performance variation gap if any using Datama solutions.

This case demonstrates the importance of a robust data integration and reattribution strategy in today’s multi-channel business environment. It also underscores the value of partnering with a professional data team to tackle complex challenges. If your organization faces data-related issues, Datama team is here to help you navigate them effectively and achieve actionable insights.