Get some insights.

Case studies

This post is also available in: German

Stage 17 copy

ROI attribution for non-traceable marketing channels

Initial situation

In a lot of cases, the impact of certain marketing channels is not directly traceable. For example in the case of TV advertising, we can not directly observe how a campaign influenced certain potential customers in their buying behaviour. In the case of TV, it is possible to measure the so-called direct impact of a spot, by looking on the website traffic of a product and flag users which visit it in a certain time window after the spot. This method became popular in the last years but has certain drawbacks. The main problem of it is, that only a small amount of the potential customers who watched the spot are visiting the website within the next minutes after the spot; nevertheless, all people who watched the spot are potentially influenced by it. As a result, most KPIs like for example CPO (cost per order) or CPV (cost per visit) look much higher than they are in reality – which leads to wrong conclusions. In most cases, it will look like as if TV is not a marketing channel in which it is worth to invest in – a demonstrable error.

Our task

Our task was to develop a model to attribute the correct impact of TV advertising (or other non-traceable marketing channels) only on the basis of data which is available in almost all cases: a time series of marketing activity in the channel (for example net costs spent per week over time) and a time series measuring an impact (for example orders of the product per week over time). This is a difficult task and initially, it was not clear if this is possible, since especially TV advertising has not a short but a long-term effect. Additionally, there are a lot of overlaying effects, like for example seasonalities and activity of other marketing channels.

Our solution

It turned out, that apart from measuring the direct impact of a spot, it is also possible to estimate the impact of a campaign with an indirect methodology. The main advantage of this method is, that we are able to measure the whole impact of the campaign and not only the impact on a small subset of people visiting the website directly after the spot, which distorts the true KPI’s which are usually much better. Also, it gives us the possibility to build a prediction model which we can use to plan future campaigns better.

For measuring the indirect effect, we trained a prediction model which explains the time series of a KPI (e.g. orders per week over time) as an echo of our marketing activity in the channel (e.g. measured as net costs per week for TV advertising) plus the impact of other marketing channels, which we not necessarily need to know (but can improve our performance).

From this, we can derive an explanation for how the marketing channel affected our orders and can decompose our orders time series into a time series of orders attributable to our marketing channel and a time series of orders attributable to other effects. By this, we can give a plausible an accurate estimation of the true impact of the marketing channel. If we have enough data, the impact can be also measured over a long time range.

The methodology we use is related to Temporal Canonical Correlation Analysis, but uses an additional Bayesian model approach specifically developed for our problem to incorporate previous knowledge and make the solutions more stable. We already applied it successfully to different kind of marketing channels and products.

The result

It turns out, that TV advertising is indeed a marketing channel which has a strong impact and for the first time, it was possible to measure the correct and long-term effect of TV advertising.

“We need a BI!”

Initial situation

A medium sized eCommerce shop reached a stage in which it professionalized its processes, team structures and was ready for scaling and international expansion. The founders realized, that the current BI (Business intelligence) solution (SAAS) was not sufficient for serving the needs of a strongly growing business and decided to establish an in-house BI team and a DWH (data warehouse).


Our tasks were:

  1. Identification of the companies needs and requirements to an in-house BI system.
  2. Determining the data sources and the technical infrastructure.
  3. Implementation of a DWH and development of the companies data model.
  4. Benchmark and setup the most crucial reports.
  5. Interviewing and teaching the new BI team.

Approach for the technical implementation

The first and one of the most important steps was to fully understand our clients business model and its major processes. Therefore we arranged interviews with every team head from every department and collected information about the internal processes and current challenges with respect to data, analysis and reporting needs. The second step was to identify all important operating systems which produced data. We had to determine the main data source (in this case it was a common shop system – which served as the single source of truth) and all contributing data sources like a web tracking tool (GA and Webtrekk), operating marketing tools, logistics and cost reporting tools.

For the technical implementation, we decided to use the Microsoft BI solution with the SQL-Server 2012 and SSIS. We divided the setup into 3 major processes:

  1. Source-to-Stage: This is the major process which collects the data from all the sources relevant for a DWH. Using SSIS (SQL Server Integration Services), we have built up the ETL and organized a daily delta load for each data source. The advantage of this methodology is that no additional coding was necessary. This saved IT ressources and enabled to teach “non-techies” to manage the ETL process.
  2. Operational DB: In this step the data consolidation and data cleaning is executed. For this, it is important to understand the data structure from each source, since those might be completely different. In order to verify the data quality, sample tests had been conducted in cooperation with the team leads.
  3. DWH DB with Star Schema: The DWH DB is a further consolidation of the operational DB and is restructured into a Star-Schema. Following our customers needs, we have implemented individual extra tables, which contained all relevant sales and marketing data in a convenient format.


After achieving all 5 goals (as mentioned at top), our client not only had a new and fully operating BI system, but also a new BI team of 2 developers who had been trained and could manage the DWH independently without further external support. The training contained the full-stack knowledge about BI: ETL, DWH architecrute and SQL.

Dependencies within marketing channels

Initial situation

A medium sized and well established eCommerce shop from Germany professionalized its marketing processes and aimed to automate its target CPA calculations (maximum allowed cost per acquisition) for all performance marketing channels. This client used Webtrekk as its main online marketing tracking tool and had already built up a data warehouse. Before deciding to work with 7Plans Consulting, this client was used to calculate manual reports in Excel, which was slow, inflexible and time-consuming.


Using the data from Webtrekk and the DWH (data warehouse), our task was to setup an automated online marketing controlling unit, which determines CPA targets for the main performance marketing channels. Moreover, the CEO assigned every team lead the task not only to optimize campaigns based on the new CPA targets, but also to understand the implications of each marketing channel, its customer cohort behavior and CLV (customer livetime value). In order to establish the marketing controlling unit, a client specific logic needed to be defined.


As a first step, it was necessary to understand the functioning of each marketing channel and its role in the overall marketing strategy. Furthermore, we needed to understand that some of the marketing channels were overlapping and some channels were amplified by other channels. Therefore a “simple” click attribution model was out of question. Instead, we have analyzed the dependencies of marketing channels with statistical models (e.g. increase in spending in channel A drives channel B) and figured out a way, how to attribute marketing channels to every single “click”. For instance, a visitor coming from SEM Nonbrand could have been assigned to 63% SEM-Nonbrand and 27% Display.

Another aspect was the calculation of the CLV (customer lifetime value), which was the major input parameter for the target CPA. Considering the marketing strategy, budget situation and the cohort behavior, we decided that customers should be profitable in average after a certain amount of time. This amount of time (measured in days) was different depending on which season the customer was acquired. For instance: a customer acquired in April or May, should be profitable after 250 days latest; a customer acquired in October should be profitable after 50 days. The reason for this differentiation is the strong seasonailty of our clients business model and its pricing strategy over the year, which makes the differentiation absolutely essential.

The final step was the technical implementation. With internal support, we managed to integrate this new model directly into the DWH and established an automatic weekly reporting containing the latest cohort analysis for each marketing channel, a CLV projection by number of days and a suggested target CPA. After the implementation, we teached internal BI- and marketing analysts to use and to modify this model.


Using the new target CPA methodology, our customer significantly improved the overall ROI of its marketing and achieved a better balance of the budget distribution over the channels. Moreover, the overall awareness for data-driven optimization increased and was partially transferred to other areas, such as on-site optimization.