12-MINUTE READ By Riku Mikkonen
With marketing data increasing drastically in volume, many marketers are looking to build a marketing data warehouse. This guide helps marketers learn how to get started with marketing data warehouses and how to benefit from using one.
This guide is written for data-driven marketers looking to take control of their data. It serves as an introduction to the topic and works as a refresher to those already familiar with data warehouses.
Navigate this article:
- What is a marketing data warehouse
- Marketing data warehouse design
- Benefits of using a marketing data warehouse
- Data warehousing providers
- Setting up a marketing data warehouse in BigQuery
- Use cases for marketing data warehouses
- Data sources for marketing data warehouses
- Using marketing data warehouse for reporting
Want to save this for later reading? Get your complementary e-book copy from here.
1. What is a marketing data warehouse?
Marketing data warehouses are unified destinations for storing and analyzing marketing data. Instead of using a limited dataset for analysis, marketing data warehouses contain data from multiple sources across a vast time period. The data granularity is much higher as the storage space capabilities are much larger when compared to a traditional database.
Marketing Data Warehouses feature a large amount of storage. As data warehouses are built for serving analytics purposes, they come with computing capabilities to support analyzing large datasets.
In the world of marketing and martech, data volumes are increasing in size. This is caused by analytics becoming more specific and focused on capturing detailed information.
Marketers are focused on gathering more detailed information about user behavior. Detailed metrics increase the detail of data. Therefore the need for better hardware capabilities have increased.
Ownership of data is not fully in the hands of the marketers, as data retention policies vary between platforms. Varying data retention policies can cause historical data to be purged, meaning a loss of valuable data.
Data warehousing used in marketing can help users to seize the ownership of their historical data. The storage capabilities of a marketing data warehouse allow for a larger amount of data to be stored. Analyzing data takes only a few moments. The computing capabilities provided by marketing data warehouses allow analysis on larger datasets within minutes.
Basic concepts of data warehousing
The word “data warehouse” might bring to mind a warehouse filled with data containers. After all, traditional warehouses are made to store items for later consumption and to serve those items whenever somebody needs them.
Data warehouses work with the same principle, but they do not exist in a physical location.
Data warehouses are used to store data for later use and serving users with the data whenever they need it. Data stored in a data warehouse is commonly high in volume and granularity.
Data warehouses are used for data storage, but they also serve another function. They are custom-built to handle analytics for a larger dataset. Calculating large datasets requires more hardware capacity than found on a workstation.
Data warehousing is thus split into two major elements: “Storage” and “Compute”
- “Storage” refers to the capability to store and retain data in the data warehouse. A large amount of storage is needed to store the vast volume data at a high granularity.
- “Compute” is the data processing part of data warehousing. Analyzing and processing a large dataset requires a lot of power, so designing a data warehouse requires efficient hardware.
With the increased availability of cloud-based systems, data warehouses have become more available. Prospective data warehousing practitioners do not need to provision the hardware or acquire data centers. Getting started with a cloud-based data warehouse only requires a few clicks.
Cloud-based platforms offer a serverless approach for the end-user, removing the need for any backend maintenance. With the elasticity offered by the cloud platforms, users only pay for the computing power they need. Queries performed by the user are always utilizing the proper amount of hardware. The system scales accordingly to the needs of the query.
How data warehousing differs from databases
Databases and data warehouses are systems that are created for storing data. However, their key points of focus differ from each other as they are built for different use cases.
Databases are designed to store structured data for query purposes. The data stored in databases usually represent only one source. Data can be supplemented and updated with a Database management software such as Oracle, MySQL, or Microsoft SQL Server.
Databases are great for storing a smaller subset of data. However, they are limited by the amount of storage and computing capability.
Databases work well when performing queries in order to retrieve data. They are not, however, suitable for complex analytical calculations as they run on general server hardware.
Data warehouses are built to work on a larger scale with a larger amount of storage and computing power available. Data warehouses are designed to contain data gathered from various sources.
The data loaded into a data warehouse is not updated as a traditional database. Instead, the data contained within is preserved in its original form. This is done so that the data can be compared to other similar datasets for analytics purposes. The stored data can be used for analytics, using the more developed computing capabilities of a marketing data warehouse.
2. Marketing data warehouse design
Designing a marketing data warehouse requires the user to identify the data sources, destinations and analytics tools needed. This can sound difficult, but is actually quite simple to achieve.
First step when designing a marketing data warehouse is to identify where to extract data from. Which are the channels we need to analyze and how much data we need for our analysis purposes? What are the multi-channel reporting benefits we can get from analyzing these sources together?
After the sources are defined, the next step is to select a data warehousing provider. Major cloud providers, such as Azure, Google Cloud, and AWS, all have data warehousing solutions. Snowflake operates using the aforementioned platforms, but provides their own approach to data warehousing.
Each provider has their own approach to data warehousing. When selecting a provider, you should consider which provider suits the use case and existing marketing stack the best.
With data sources and destinations selected, the final step is to choose the analysis and visualization layer. Data warehouses can be connected to a variety of different reporting and dashboarding tools. These include solutions such as Qlik, Looker, Google Data Studio, and PowerBI.
3. Benefits of using a marketing data warehouse
Having a marketing data warehouse offers a variety of different benefits. Some major benefits that using a marketing data warehouse include
- Full Data Ownership: All your marketing data is in your storage. Instead of depending on data retention policies, the marketing data is under your control
- Unified storage for marketing data: Take data from multiple sources and put them together in a single unified storage. Stored data can be easily mixed for further analysis.
- Nearly unlimited storage: marketing data warehouses allow you to store a large amount of data. Instead of being limited by your data sizes, you can store data in the amount you wish to.
- More power for analysis: Data Warehouses are built on powerful hardware. With the power provided by a data warehouse, users can perform more complex queries. These queries will only take a few seconds with the increased performance.
- Connectability to different sources: Marketing data warehouses can be connected to all major marketing software. Using a data pipeline tool, marketers can move their marketing data to their data warehouse. Likewise, marketers can use the data stored in their marketing data warehouses for reporting. Data warehouses can connect directly to reporting and dashboarding tools such as Qlik and Looker. Reports created can utilize the datasets directly and update automatically as new data is loaded.
- Low maintenance: Marketing Data Warehouses are readily available in the cloud. Users do not need to worry about the maintenance work. This is due to the server provisioning and backend maintenance being handled by the cloud provider. With the elasticity of the cloud, resources are scaled automatically.
- Cost sensitive: Cloud data warehouses make data warehousing cost-flexible. Marketers do not need to acquire large amounts of hardware and pay for their maintenance. Modern data warehouses charge only by usage.
Data warehousing versus spreadsheets
Spreadsheets are a common destination for analyzing and storing data. The usability of spreadsheets has made them a tool-of-choice for many data analysts. Spreadsheets are great for smaller datasets, but quickly become cumbersome as the size of data grows in volume.
Huge spreadsheets require a lot of storage space and can be resource-intensive to use on a daily basis. Queries performed on spreadsheets can be inefficient. The limited hardware capabilities of a desktop computer can cause the calculations to be longer than usual.
For larger datasets, data warehousing can provide a better solution. Data Warehouses offer storage for a larger volume of data. Users are not limited to the row limits of spreadsheets or the storage space of their computers. Performing queries on the data stored within is faster thanks to the hardware capabilities offered.
Reporting can be done on tools such as Looker, Qlik or Google Data Studio. Reporting in these tools is swift and easy. The calculations and queries are performed inside the data warehousing platform. As the hardware capabilities are much higher, users are not hindered by their personal device’s performance.
Benefits of scale when using data warehousing
Marketing data warehouses are built for continuous use. After setting up a data warehouse, you also have to set up automatic data transfers. These data transfers keep introducing new data to your marketing data warehouse. With a larger dataset comes better opportunities for analytics.
Storage capabilities of a data warehouse enable you to store a larger amount of data. Blending old data with new data helps you to analyze your performance even further. Instead of limiting your data schemas to just select fields, you can use the full storage capability. Data schemas created for data warehouses help you to extract higher granularity data.
With the larger data set available, you can do deeper queries on your datasets. Analyzing historical data across a wider time spectrum gives a deeper insight into your organization’s performance. Blending data with numerous sources helps to get a deeper insight into overall performance. Data warehouses offer computing capabilities that can be used to analyze larger datasets.
As more data is loaded into a marketing data warehouse, the larger the benefits will become. The high volume of data brings forth more accurate and comparable results.
Want to finish reading this later? Secure your complementary e-book copy by clicking here.
4. Data warehousing providers
With the advent of cloud technologies, providers in the data warehousing space have increased. Major cloud providers have capitalized on having an existing structure by creating their own cloud data warehouse solutions. In addition to the existing players, new solution providers have emerged using the cloud infrastructure to provide a data warehousing experience.
- Amazon Redshift: Amazon’s high capacity data warehouse running on their AWS platform. Connectable to a variety of different reporting tools such as IBM Cognos, SiSense, Tableau, and Yellowfin.
- Azure Synapse Analytics: Microsoft’s Data Warehousing platform that offers petabyte-scale data warehousing and Big Data -suited analytics. Microsoft promises a full ecosystem, using Machine Learning and PowerBI natively inside the data warehouse system. Heavily focused on creating the most secure data warehousing solution on the market.
- Google BigQuery: Google’s cloud-based data warehousing solution. Offering elasticity and simplicity, BigQuery brings data warehousing to the masses. No maintenance or optimization work required, as Google has automated this.
- Snowflake: Snowflake offers scalable and intuitive data warehousing solutions to the masses. With easy configuration, Snowflake removes the need for extra maintenance and lets users focus on leveraging data.
5. Setting up a marketing data warehouse in BigQuery
One of the more prevalent data warehousing solutions is Google BigQuery. Google BigQuery runs on the Google Cloud platform, giving marketers access to Google’s cloud computing and storage capabilities. BigQuery is a fully managed solution, meaning that the users don’t need to do back-end maintenance.
BigQuery lowers the bar of entry for data warehousing. Any marketer with a Google Cloud account to create a marketing data warehouse within minutes. With Supermetrics’ native connectors, BigQuery users can setup transfers without writing a single line of code.
To get started, read our guide for setting up a marketing data warehouse in BigQuery
6. Use cases for marketing data warehouses
Applications for Marketing Data Warehouses come in various forms and varieties. Using a marketing data warehouse can help you to utilize data in a more diverse way.
Here are a few use cases that you can apply with a marketing data warehouse:
- Blend data from different sources for cross-channel reporting. Katté Digital Agency centralizes their marketing data into a BigQuery data warehouse. Data is gathered from Google Ads, LinkedIn Ads etc. and transformed into a queryable form. Resulting tables are then used in spreadsheet tools for reporting client’s overall advertising performance.
- Unify historical data under one platform. VanMoof centralizes their historical data to perform analysis on their overall performance. To visualize their findings, VanMoof connected Google Data Studio to their Data Warehouse. Using historical data as a basis, VanMoof analyzes their campaign performance.
- Store higher granularity data for more accurate reporting. EPL Digital uses their marketing data warehouse to store marketing data with multiple dimensions. This data helps EPL to analyze detailed dimensions such as geographical location.
- Combine data sources for an eagle-eye view of performance. Inseev Interactive combines all of their client data to produce an eagle-eye view of their overall performance. Inseev blended data from all of their clients’ data sources to produce a Tableau dashboard helping them follow their overall performance.
- Calculate ROIs of your marketing campaigns. Siavak uses Google BigQuery to provide post-click analysis reports. By combining performance data from advertising campaigns with Google Analytics results, Siavak analyzes how their campaigns result in conversions.
7. Data sources for marketing data warehouses
Marketing data warehouses are built for storing data from various different sources. With purpose-built connectors, data can be extracted from various different sources to data warehousing platforms.
Connecting data to a marketing data warehouse can be achieved using APIs offered. An alternative to building data pipelines is to use a data pipeline tool. Data pipeline tools are built for easy data transfer between data sources and destinations. Major data pipeline tools include:
All of these tools connect to key data sources. Key data sources are all of the tools marketers use and contain data for analysis. These tools include:
- Advertising platforms: Pulling data such as conversions, clicks, CPC, demographic data and keyword performance. Common platforms include Facebook Ads, Google Ads, Adroll, and Yandex.Direct
- Social Media platforms: Gather post engagements, hashtag performance and user demographics. Common sources include Instagram, Twitter, LinkedIn, and Facebook.
- Payment Processors: Collect conversion related data from payment processors. Common sources include Paypal and Stripe.
- Web Analytics providers: Gather information from session times, traffic sources and conversions. Includes tools such as Adobe Analytics and Google Analytics
- CRMs: Gather lead and opportunity data. Includes platforms such as Salesforce, Microsoft CRM, and SAP.
8. Using marketing data warehouses for reporting
Storing data in a data warehouse is only the first step. Full benefits from having a marketing data warehouse are gained when connecting to a reporting tool.
Reporting helps you communicate the situation of your business. Calculating KPIs and creating dashboards are part of reporting. Historically reporting was done using spreadsheets and powerpoint presentations.
However, with the introduction of reporting software, the quality and accessibility of reporting has become higher. Users can easily create dashboards featuring metrics in a form of presentation they wish. All calculations can be performed automatically and modified to suit user needs.
Commonly used tools for reporting include:
- Google Data Studio
Linking reporting tools such as Tableau to a marketing data warehouse gives access to the larger data. All of the data contained within can be used to provide data for reports and dashboards. Highly detailed data stored in a data warehouse provides more insights in the reporting with more metrics and dimensions to analyze.
Performing queries and calculations on the dataset is fast. Producing reports takes only a few minutes as the reporting tools utilize the data warehouse’s power. Data is being automatically retrieved, basing the reports on the most recent data available.
Want a PDF version of this post? Click here to secure your copy.
Supermetrics’ mission is to help marketers better report, monitor, and analyze their data by connecting the marketing platforms to wherever they want to use the data. Supermetrics offers native connectors to all major marketing tools. In 2019, Supermetrics introduced Supermetrics for BigQuery, bringing marketing data warehousing to the masses.
If you are interested in getting started with building a marketing data warehouse, book a demo. If you would rather a hands-on approach, try Supermetrics for BigQuery with a 14-day free trial.