How to design a marketing data warehouse

6-MINUTE-READ By Riku Mikkonen

Marketing data warehouses are slowly creeping into the hands of data-driven marketers. Maybe you are already planning on using one? Before going out and setting one up, it’s a good idea to look at the big picture and design an approach that works the best for your business. Because the design step is crucial, we go through the five steps that are integral for a successful marketing data warehouse design.

To get started with your marketing data warehouse design, try out Supermetrics for BigQuery with a 14-day free trial.

1. Gather the business requirements

The first step is to determine why you need a data warehouse. The reason can be found inside the business requirements of your marketing department.  What are the key goals marketing is trying to achieve? The overall goals help you understand what kind of data you need and where to focus your analytics on. If you are just following a single channel on a quarterly basis, basic spreadsheets are more than enough to suit you. If you are doing multi-channel reporting with plenty of historical data for comparison, then data warehousing could be the solution for you.

Setting goals help you with the later steps. Knowing what to solve helps you understand which data to collect and analyze. Just collecting random data and leaving it in the data warehouse does not give you a good return on investment. Data warehouses are not just storage units, they are built to support your analytics.

2. Identify data sources

Data Warehouses store data, but where do we get the data? Designing an architecture requires you to understand where your data comes from. For many marketers, these can be advertising networks or social media platforms.

As you are selecting your data sources, you need to know what your key channels are. How do these channels serve my goal? With the business requirements on hand, you can easily identify which sources help you answer the questions you need answered. Usually getting answers requires data from numerous sources. When Inseev did multi-channel reporting, they combined all of their ad networks with Google Analytics data to measure return on ad spend.

One of the key value propositions of a data warehouse is the possibility to blend data from several sources. Therefore, any data source deemed important can be connected to a data warehouse. Oftentimes marketers want to do multi-channel reporting, so this requires multiple sources to be effective.

3. Select the data warehousing platform

Having identified data sources, you need to understand where the data will be stored. Where as you already had information on what data sources you had, selecting the destination requires more concrete information gathering and processing.

When selecting a data warehousing platform, you would first need to identify if the platform is a fully-managed solution or an in-house built solution. An in-house built solution works well for very large enterprises that have many different sources that have specific needs for the format, availability, and data privacy. 

The in-house data warehouses are custom built to business needs of larger enterprises. The data security practices are exactly in line with any organizational policy in effect. On the flip-side, hardware configuration and data schema creation require a lot of time and other resources. 

Another option for an in-house solution is to use a public cloud. Data structures are created from the outset. Users of public cloud solutions also do not need to worry about configuring the back-end as all of that is done automatically. 

If a need for new storage or more processing power arises, cloud platforms can be easily scaled up. Identifying the cloud option also requires you to select which data warehouse platform is already in use. If you are already using a cloud, chances are your cloud provider has a data warehouse solution available. If you don’t have a cloud platform in place, many such platforms offer free trials for testing.

4. Evaluate data transfers

Now that you’ve identified the sources and set the destination, the next step is to design how the data will be transferred. Building a data pipeline on your own seems like the most efficient solution, but it does have caveats. Knowing the API structures of your sources and destinations are the first building blocks required to start building your pipelines. 

More technically knowledgeable users are capable of building these links, but it still takes a bit of time to get up and running. If there are any changes on the API side, you need to fix them yourself in order for the data transfers to work without any extra interruptions. Troubleshooting is also on you. If data transfers fail due to pipeline configuration, it is on you to fix it.

An alternative to building your own is a managed solution. Managed solutions are in the form of ready made connectors, that connect to source and destination APIs. They then transfer the data for you without any extra tinkering. 

Automated connectors, such as the ones Supermetrics offers, are simple and easy to take into use. Whenever there is an API change, the connectors are updated to reflect that. Any connector downtime is addressed by the provider, freeing you from hours of troubleshooting.

5. Select a reporting tool

With your new data warehouse designed, you’re ready to start solving your business questions. Of course, all that data needs to be put into action and not left just lying dormant in your data warehouse. 

The most effective way to do this is to connect reporting tools to the data warehouse. For example, connecting Google Data Studio to BigQuery is simple and easy. Reporting tools help you create dashboards and reports of your performance based on data. These tools are constantly connected to your data warehouse and keep your reports up-to-date with the latest data.

The selection of a reporting layer depends largely on user preference. If there is an existing tool inside the organization, that will be the one connected to the data warehouse. Most of the commonly used business intelligence tools are connectable so there is no need to introduce new tools to the stack. If you however do not have a tool already in use, the wide variety of different reporting tools available can offer you the reporting your organization needs.

In summary

Designing your marketing data warehouse is not a difficult task. It only requires you to determine what kind of business question you want to determine and what kind of data you need for answering it. With the questions in mind, identify what kind of sources you use, where you will store that data and how you transfer the data. Finally, decide on what analytics tool you will use for analyzing the data. 

Ready to put your design to action? Test Supermetrics for BigQuery for 14 days with a free trial.