In a world where consumers have many choices with customer loyalty getting harder. The exposure of ads, multiple visits to site, a complex customer journey are some of the areas brands need to better understand with buying motivations to influence purchase behaviour. It requires collecting and analysing millions of data points and this can only be solved with a marketing data warehouse.
The marketing data warehouse has become the key martech that provides a solid and consistent data foundation for measurement requirements as the rest of the martech ecosystem plays musical chairs.
The data requirements that brands’ have will align with the maturity and the business questions being asked can be delivered via a data warehouse. The maturity and evolution of brands in not wanting to trust the data solely from platforms i.e., Facebook, Google Analytics etc which have great use cases for using the data within platforms itself but when the need is to merge data sets the only solution is a data warehouse.
Depending on the maturity of the brand the marketing data warehouse will likely go through an evolution as requirements will change more so if you are building a data warehouse at a start-up v scale-up v mature brand. As a start-up it’s becomes about extracting and loading the data but once the brand moves to scale up / mature transforming the data becomes critical in the process to get the right structured data.
The Plan
Building a marketing data warehouse should be part of a wider data strategy where a measurement framework is developed which focuses on the KPI’s that need to be tracked and the data that needs to be collected. It ensures that streams of data are not collected for the sake of it and analysed with no context at all which becomes a never ending cycle. It safeguards the data collected is actionable and it ultimately allows a shift in performance against the key KPI’s.
The Why
Understanding the WHY is fundamental in the success of building a marketing data warehouse.
Business intelligence: Depending how marketing is viewed within the wider business in some form, marketing must prove the value its delivering. This will come in the form of what are the business questions that are being asked to the marketing team that cannot be answered with the current state of play.
Access to raw data: Being able to prove the business value can only be done by accessing raw data this is not only data from Facebook or Google Analytics it’s also CRM data / internal data, any dataset that helps prove the business value. Where most data will be viewed within the platform, each platform will have raw data that is available via API or have a connector that can be piped into a data warehouse.
Single source of truth: With a complex martech ecosystem that is providing duplicate data making it hard to trust. The data warehouse provides a single source of truth with the right data.
Data quality: In control of how the data is designed (via an ETL process, Extract, Transform and Load) to provide better data quality making the data more useable and easier to understand.
Flexibility: Having access to the raw data provides huge amount of flexibility in how to shape the data for different visualisation / analysis / modelling projects.
Efficiency: Saving time by not investing into manual data collection and preparation. Where the time can be invested in analysis and optimisations that can move the needle by investing into a process that provides the right data.
The Big Picture
The purpose of building a marketing data warehouse is to move away from platform led data and get better data to drive the marketing analytics and help deliver against the business goals. The business questions will help understand:
- KPI’s: KPI performance that are mapped out in the measurement framework.
- What kind of analytics will be needed:
- Channel and Campaign insights
- Attribution
- Econometrics
- Experimentation – Incrementality / AB testing
- Cohort Analysis
- Customer Lifetime Value
- Recency, Frequency, Monetary (RFM)
This will help understand the raw data structure that is required and how it needs to be merged with other data sources (i.e., GA data merged with CRM) to deliver against the marketing analytics requirements.
What the marketing data warehouse allows is to start building a base of high level of quality and long data, the ability to look at the data over an extended period that can be trusted. Building the data pipelines from the various data sources and structuring the data is central to the success.
Case Study
The data requirements for building a marketing data warehouse are exhaustive. For a mature omnichannel brand were looking to evolve their measurement with Econometrics and Incrementality one of the first steps was to build out a data warehouse to help the process. With data collection all in one place providing a consistency of data it would also help with understanding channel and campaign performance.
The data collection requirements were split into 3 buckets:
- Advertising data
- Business data
- External variable data
In future the number of buckets may increase as data evolves but for the requirements these buckets made sense.
Understanding what data is needed then it’s the long hurdle in collecting the right data and how to collect the right data. It was decided that Google Big Query would be the would be data warehouse, so data was piped into using Funnel, Google Sheets and Python. It was also decided to back-fill data from 2017 which would allow re-structuring of data i.e. for campaigns and channels so they align to the new structure going forward.
Which translated into a roadmap, with the 5 phases taking place over 5 months:
Phase 1: Aligning the strategy and framework to data requirements and sources
Phase 2: Map out the schema for all data sources with sample data set from the different sources
Phase 3: Agree on new naming convention that can be used going forward and backfilled for past data
Phase 4: Manually manipulating past Google Analytics, channel, and campaign data to align with new naming convention
Phase 5: Setting up Big Query + pipelines from Funnel, Google Sheets and Python
Phase 6: Building out dashboards and visualisations
Selecting the right data warehouse needs to be aligned to the long term vision as it can positively or negatively impact the growth with the fast moving need for accurate data and insights. Remembering that the data warehouse is the cornerstone of the marketing ecosystem, and its evolution will go from measurement to driving the composable CDP and delivering the business data strategy.
