Patrik Segersven | Monday June 24, 2019 | Analytics

How to Create the Ultimate Marketing BI Dashboard

Are you searching for the Holy Grail of marketing dashboards that can bring you the most meaningful business KPIs in real time, which is also displayed in a useful and actionable manner? A dashboard that not only tells you your historical customer lifetime value (CLV) or how it develops over time, but also predicts the future? A dashboard where you can look at customer acquisition cost (CAC) and CLV side by side, so you can scale marketing accordingly? A dashboard that has your back and leads the way when making business-critical, long and short term marketing decisions?

If so, we’re happy to announce that your search is over and you are moving from marketing analytics to a concept we'd like to call Marketing Business Intelligence (BI).

In our experience most ecommerce companies accept and live with large data discrepancies. They allocate and optimize their investments based on short term data, and often even on incorrect data. It’s very common for companies to focus on current and past performance rather than looking at the future performance. Lastly, companies put hours of work every week into manual data entry and mapping, currency conversion, and managing the data input, which brings no additional value to the business. Quite the opposite, in fact.

In this post we are focusing on, but not limited to, ecommerce businesses that are using Google Analytics (GA) as their primary analytics platform. We will guide you through how you can leverage your marketing analytics with the right technology to remove all the hassle mentioned above to create the ultimate Marketing BI Dashboard, the Holy Grail of marketing dashboards.

What is the ultimate Marketing BI dashboard?

Firstly, it’s not a report. A dashboard is supposed to bring you actionable insights, otherwise it’s not performing its primary function. The ultimate marketing BI dashboard is something that goes beyond the basic marketing metrics and data, and does it automatically and in real time.

The ultimate Marketing BI dashboard should:

  • Be actionable and relevant, not a report.
  • Have accurate data over customer-, advertising-, transactional- and behavioral dimensions.
  • Focus on predictive future performance as well as past performance.
  • Give insights on both unique customers and retention, as well as transactions and users in GA.
  • Be clear to all users. You don’t have to be an expert to understand what’s being displayed.
  • Tell you how much each and every customer is worth to you, and how that value develops over time.
  • Have accurate channel groupings that are based on intent, rather than the source or medium of acquired traffic.
  • Be fully automated with no manual data input.

Why do you need this?

Most ecommerce companies don’t have this in place today, but this is really not optional, unless your ambition is to sell something once, and once only over the lifetime for a customer. The answer is simple: you need this in order to make the right marketing investment decisions and to save time from manual data input, by automating that function.

Why traditional marketing profitability measurement sucks

In our experience the KPIs that are most commonly used when allocating performance marketing budgets are Return on Ad Spend (ROAS), Cost of Sales (COS%) or similar profitability measures, but this is really short term optimization. You need to be able to say what your customer acquisition cost (CAC) and expected customer lifetime value (CLV) is for a specific period of time, especially if you’re expanding your business. You need the data to justify the short term negative cash flow that you are potentially experiencing, unless you want to simply invest and hope for the best.

It’s also important to look at CAC and CLV side by side because the ratio (CLV / CAC) between these two KPIs are as important as the KPIs themselves. This ratio tells you how many times you will get your money back for every customer you gain.

As long as your CAC is lower than or equal to your CLV, ratio 1:1, you are good to go (in theory), assuming you have enough money in the bank to fill the gap between short term and long term revenue. If your ratio is too high, say above 5, you might be missing out on business and you should consider investing more in marketing or new customer acquisition. Many online sources* say that for ecommerce a ratio of 3 is healthy, meaning that you would pay 1 Euro to get 3 Euros in return during the lifetime of the customer.

*Klipfolio, Corporate Finance Institute

Use the right data for the right purpose

Since you want to blend together customer-, advertising-, transactional- and behavioural data, it’s important that you pull the data from sources that you know are correct and sources that are not vulnerable to tracking issues.

Why shouldn’t we use the transactional data from Google Analytics, for example? Because the discrepancy between GA revenue and actual revenue varies from everything between 5-15% for a variety of reasons. In the worst cases you might be making the wrong decisions because you have the wrong data.

Another common reason is that you lose ecommerce tracking for a period of time and you are unable to backfill that data in GA. If we instead use the CMS, or Magento in this case, as the source to pull the transactional data, we know it is always going to be correct and we can be confident that we are making decisions based on correct data.

So, use the source that is the most stable and the data that is the most correct for all of your KPIs.

For comparison we’ve summarised the key data point differences between a Marketing performance dashboard and a Marketing BI dashboard.

Marketing BI dashboard

Marketing performance dashboard

Revenue Actual revenue Revenue from GA*
Transactions Actual number of transactions Transactions from GA*
Unique customers Actual number of unique customers User ID from GA**
Retention rate YES NO
Avg orders / Customer YES NO

*For a variety of reasons there is always a discrepancy between GA sales and revenue compared to the actual sales and revenue. In our experience the discrepancy is on average between ~5-15% lower in GA. This means that all profitability metrics such as COS%, ROAS and CR% will be incorrect if you use GA revenue or transactions in your calculations.

**User IDs cannot really tell you how many unique customers have purchased something from your website, but it’s the closest you can get with GA data.

The Tech

We are using three solutions to build, map, merge, calculate and visualise the data from numerous data sources.

    • Instead of collecting data from every advertising platform one by one, does this automatically for us. This is also the software we’ve used to merge the advertising data with the behavioral and transactional data in GA. With over 400 advertising connectors and a wealth of other features, this software removes all manual labor from the equation.
  • BigQuery
    • BigQuery is used to pull in data from Funnel, GA and Magento (an ecommerce platform). This is where we do all the matching of transactions, predict lifetime value with machine learning calculations, and merge the different data sources together to make it available and workable in Google Data Studio (GDS).
  • Google Data Studio
    • We use GDS for data visualisation because of the flexibility and simplicity of the software, and the fact that you don’t have to know a coding language to create custom metrics, blend data sources etc. Last but not least, it's also free to use. A large part of the relative KPIs are also calculated in GDS.

Data Sources

The data sources may vary depending on the business. In this case we’ve pulled data from the following three sources: GA, Magento, and various advertising platforms (via Funnel). Due to the flexibility of the tech being utilised, this setup is really not limited to any data sources.

  • Google Analytics
    • For behavioral data
  • Magento
    • For transactional and customer data
  • Advertising platforms (Google Ads, Facebook, Affiliate, Bing, DV360)
    • For cost data


We hope this post has helped you to understand how and why you should leverage your marketing analytics and technology to start working with Marketing BI. If you want to pick our brains on this topic please drop me an email

In our next post we will look closer at what models as well as how to calculate CLV, CAC, retention and so forth. We will also go through what’s critical to bear in mind when you set these KPI:s. Lastly we will break these KPI:s to more important segments such as demographics, device and channels, that’s when you make BI actionable!

Click here if you want to download this post in PDF format


Post a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.