Setting up a Near Real-Time Data Warehouse for Marketing Analytics

The business impact of your marketing data stack is highly dependent on your marketing data warehouse. It’s the engine or the brain of the stack that powers all the insights, decisions, and actions and the first step in your journey of maturing marketing analytics, driving higher ROI, and setting up a data stack. 

We’ve seen a drastic improvement in the accuracy of our models, and we’ve started seeing its impact on the conversions of our ad campaigns.

Miles Davis, Data & Analytics Manager, Harmoney.

With a warehouse in place, you will have marketing data from all channels and tools easily accessible in a single place for you to run your analytics magic on top of it.

A data warehouse is a central repository that helps consolidate large amounts of data from multiple sources. It is highly optimized to store as well as query large amounts of data thus making it a powerful data storage for analytics. With its analytical capabilities, not only marketing teams but entire organizations can derive valuable business insights from their data to improve their decision-making process. 

With a data warehouse (Single Source of Truth) at your disposal, all marketers will have access to the same standardized data allowing them to execute faster, more efficient initiatives. With all the data in one place, you can find answers to any questions by marketers and generate more granular insights about any problem. Sounds ideal, doesn’t it? You can even leverage the machine learning capabilities of your warehouse to perform predictive analysis on your marketing campaigns and user behavior. This will not only optimize your campaigns but ease the planning and forecast process for the entire marketing team.

This article will help you take your first steps in setting up your marketing data warehouse to centralize your marketing data—starting right from assessing various data warehouse options to the process involved in setting it up. Let’s dive in!

Table of Contents

• How to Choose the Best Data Warehouse for your Marketing Analytics Infrastructure?
• Popular Data Warehouse Options in the Market
  • Google BigQuery
  • Amazon Redshift
  • Snowflake
  • Databricks
• Setting up your Data Warehouse for Marketing Analytics
  • Planning the Technical Setup
  • Getting Your Data to the Marketing Data Warehouse
  • Using the Data
• Things to Remember While Setting up Your Marketing Data Warehouse
• Final Thoughts

How to Choose the Best Data Warehouse for your Marketing Analytics Infrastructure?

Here are a few factors that you should consider before deciding on a marketing data warehouse solution.

• Data cost estimations: A data warehouse involves different types of costs such as deployment costs, data management costs, opportunity costs, procurement costs, maintenance costs, etc. To ensure that a data warehouse system offers value to your organization, the overall costs during its lifespan must be assessed and offset against the expected value derived from such a solution. For marketing teams, costs can be assessed based on the number of marketing tools to be integrated, whether they are SaaS or in-house tools or just simple Google Sheets. The amount of marketing data to be loaded and the frequency of loading also comes into play. Similarly, the value can be measured in terms of minimizing data redundancy, decreasing time to insights, and helping create a single source of truth for all marketing data.
• Maintenance: Before deciding on a data warehouse solution, you need to assess who will manage and track the addition and removal of metrics and accessibility, as well as if this will be done in-house or in collaboration with a technology partner. In-house can refer to someone with little to no technical expertise, such as an analyst or a technical team member.
• Accessibility and Speed: You should select a data warehouse solution based on the required frequency of loading data, the relative impact of latency on your operations, and how important speed is for you. For example, data specific to campaign performance reports may need to be updated every few minutes, while for analyzing user behavior and the impact of marketing activities using funnel reports, you can set the frequency to twice daily. If you need critical data updated in near-real-time, a solution with minimal downtime would be preferable to you.
• Performance Capabilities: You need to select your data warehouse to optimize your computing capacity according to the volume of data and the processing of queries. This will depend on the number, frequency, and sophistication of your queries and the number of concurrent users. All these depend on the type of reports you need and the frequency at which you need them, e.g., web analytics reports, social media reports, attribution modeling, etc. Marketing teams need to evaluate by determining how much data they will process, how long they can wait for updated data, and how much money they are willing to spend. In addition, if you wish to find a solution that can meet your growing data processing needs in future, then the ability of a data warehouse to scale to match your activities will be important for you.
• Vendor: While considering data warehousing solutions, you might also want to consider whether you need an elaborate ecosystem or can work in a siloed environment. You could also think of other data sources and tools you require and if these are natively supported by your marketing data warehouse. For instance, if your organization uses Google enterprise suite, then your organization could go for BigQuery as it would easily support a Google-based ecosystem.
• Security Compliances: When selecting a marketing data warehouse solution, do compare your organization’s compliance requirements against the standards enforced by cloud data warehouse service providers. For example, HIPAA compliance laws strictly govern security standards for protecting the security of patient data and certain healthcare information. Any organization in the healthcare sector would need to ensure its data warehouse complies with HIPAA regulations. Similarly, several rules and regulations govern how sensitive data should be handled for different industries.
• Data Science Capabilities: All data warehouses support standard SQL queries for filtering and extracting data for your different marketing requirements such as preparing campaign reports, funnel analysis reports, web analytics reports, etc.; however, support for data science varies substantially. Some data warehouses, such as Databricks, provide machine learning and advanced analytics capabilities, but others leave data science to the customer. Even when these features are present, their usability might vary substantially. Marketers can utilize these capabilities to leverage predictive analytics to predict marketing trends and foresee customer engagement. If your marketing requirements are simpler, you may not require this.

Popular Data Warehouse Options in the Market

After considering these factors, you can now decide which data warehouse is appropriate for your marketing analytics infrastructure. Below are several data warehouse options available in the market.

1) Google BigQuery

If you don’t require a high frequency of data updates but rather want to process complex analytics queries, then BigQuery is a great choice. BigQuery has an in-built cache; therefore, it performs really well in scenarios where the data does not frequently change, such as data about one-time marketing campaigns running in Linkedin Ads, Facebook Ads, etc. However, BigQuery is not suitable for basic aggregation or filtering. In addition, if your datasets are fairly small, BigQuery will be inefficient, as a single query might take several seconds.

The platform makes use of a capacitor, which uses AI to assess data storage continually, and data is co-located by column. This makes BigQuery efficient at scanning individual columns over an entire dataset. Thus, you can stream your real-time data to BigQuery tables and update or delete existing values effectively.

BigQuery’s pricing approaches are based on computational power and resource allocation, i.e., the pay-as-you-go approach. You can set up and run new instances without paying for all the servers. 

2) Amazon Redshift

If you want to use a data warehouse primarily for repetitive, automated ingestion and loading tasks like daily/weekly/monthly reports, then Redshift is the go-to warehouse for you. Funnel analysis reports, web analytics reports, and campaign analytics reports fall into this category.  Redshift is highly scalable – it can dynamically allocate processing and memory resources to accommodate rising demands without slowing down. More significantly, the flexible price structure enables you to pay just for what you need.

Its rapid query execution on terabyte-scale data, on the other hand, makes it an ideal choice for business intelligence use cases. 

Redshift’s performance is stable and predictable as long as the cluster is appropriately resourced. As a result, it is a common choice for data-driven applications that may use data for reporting or performing computations.

Redshift, being part of the AWS ecosystem, offers easy integration capabilities with the AWS suite of applications. So, if your marketing sources belong to the AWS architecture, then integration becomes a cakewalk with Redshift. However, you can also use third-party data pipeline solutions like Hevo Data to move your data from several other sources into Redshift.

3) Snowflake

If your data storage requirements fluctuate, then Snowflake with its concurrency scaling feature is your go-to warehouse solution. It allows you to spin up and down computing clusters of any size for any user or virtually any number of workloads, without interfering with other jobs. While this is helpful for marketing teams who require instantaneous query results and can’t compromise with speed, it can increase your overall costs if data is not managed correctly. 

Since Snowflake has automated maintenance features, it is suitable for marketing teams that don’t want to depend on engineering teams for setup, maintenance, and support of in-house servers. Moreover, teams can also pay for storage and computation separately based on their requirements. For example, if a marketing team runs one-time campaigns, then storage hasn’t much of a requirement for expansion. 

Snowflake works seamlessly with multiple data integration patterns such as batch (e.g., fixed schedule), near real-time (e.g., event-based), and real-time (e.g., streaming). Thus marketing teams that need to leverage real-time customer engagement data, near-real-time web analytics data from Google Analytics, Adobe Analytics, etc., and large historical volumes of data in their marketing analytics infrastructure can definitely choose Snowflake over others.

4) Databricks

With Databricks, you can perform advanced analytics and build machine-learning models on top of your data. Not only that, you step up your data analytics capabilities because of the native integration of Databricks with a suite of ML-powered predictive analytics tools—Apache Spark, Python, Scala, ML Flow, Keras, scikit-learn, and many more. By implementing predictive analytics on historical data, you can foresee the outcome of a marketing campaign running through Google Ads, Facebooks Ads, etc. Broadly speaking, it will help predict marketing trends and scenarios. 

Additionally, Databricks also supports autoscaling—the clusters are resized automatically based on workload demand. This means that a marketing analyst won’t have to wait for query results even during peak hours.

There are several other data warehouse options available in the market, such as Firebolt. You can even use PostgreSQL as your marketing data warehouse.

You have now assessed all the options and finally selected a data warehouse appropriate for your use case. What’s next? 

Setting up your Data Warehouse for Marketing Analytics

Here are the steps to take in order to get your marketing data warehouse up and running.

Planning the Technical Setup

You can ask yourself a few analytical questions to predict the capacity of your data warehouse and set it up at ease. Some of these questions are:

• How many data users will be in the warehouse?
• At what rate will the data users grow?
• What is the volume of data that the data warehouse would support? 
• At what rate will the data grow?
• How much transactional data will be stored in the data warehouse?
• At what rate will the transactional data grow?
• What is the proper level of granularity for data in the data warehouse?
• Can you change the level of granularity if needed?
• How much historical data will be stored in the data warehouse?
• At what rate will the historical data grow?
• Can you decide if you want to add more history than anticipated?

You can answer these questions based on the number of marketing reports you plan to create, the number of stakeholders involved in querying and measuring the data, the rate of inflow of data from several marketing channels, the time period over which data is to be measured, etc.

The technical setup of your data warehouse begins with defining the computation resources and storage configurations. For Google BigQuery, you start by deciding on a pricing plan based on your use cases, such as flat-rate or on-demand. Once these details are determined, you can log in to your account and set up IAM (Identity and Access Management) roles and permissions for your tables. IAM ensures that the appropriate people in an organization have access to the tools they need to do their jobs.

Next, you will create a project and dataset in your data warehouse and then create a table within the dataset. In this table, you will provide the necessary credentials, such as the data source, the format and schema, and advanced options, such as encryption and write preferences.

It is worth noting that configuring a data warehouse may vary slightly depending on your specific platform. Some data warehouses may require you to define user permissions and storage and compute capacity, while others may have the ability to scale based on the data you ingest automatically. Regardless of the specific platform, the general process for setting up a data warehouse is similar.

Getting Your Data to the Marketing Data Warehouse

To set up an effective marketing data warehouse, start with identifying the data sources from which your data will be coming in. Some of the common data sources include:

• Advertising platforms like Facebook Ads, Google Ads, Amazon, and Simpli.fi
• Email marketing platforms like ConstantContact, Mailchimp and Sendinblue
• Social media platforms such as Twitter, Facebook, Instagram, and Pinterest
• Web analytics tools like Adobe Analytics and Google Analytics
• E-commerce platforms like PayPal, Amazon, Stripe, and Shopify
• CRM systems like HubSpot and Salesforce
• Customer data platforms like Segment and Exponea
• Databases like MySQL, MongoDB, and PostgreSQL

You have now identified the sources from where you want to pull data and to which marketing data warehouse, i.e., “from where” and “to where.” It’s time to decide the channel that will help your data movement—an ELT Tool.

A no-code ELT solution will help automate the movement of your data from your data sources to your marketing data warehouse. While there are a lot of ELT tools you can select from, with Hevo Data, you can set up integrations with 150+ data sources (including 40 free ones!). Its fault-tolerant architecture scales with zero data loss and zero latency.

Using the Data

Now that all your data resides in your marketing data warehouse, you can make it analysis-ready by performing post-load transformations on it. You can run complex in-built SQL transformations from the comfort of Hevo’s interface or opt for a transformation workflow like Dbt in your data warehouse. The choice is yours!

After applying transformations, you’ve got your data in the final analysis-ready form. It’s time to create dashboards and reports to assess the performance of your business’s marketing efforts and thus improve the ROI. You need to decide on a data visualization tool that best fits your use cases, company size, budget, and integration compatibility, i.e., it should be compatible with your marketing analytics infrastructure.

Below is a list of some of the commonly used data visualization tools. 

• Google Data Studio
• Power BI
• Tableau
• Qlikview
• NinjaCat
• Looker

There would be situations where marketing teams want to use the transformed data or the results of data modeling from the data warehouse in their preferred applications. These applications can be business applications like CRMs, marketing automation, and analytics software. For example, teams may prefer to view data on the key metric, Customer Lifetime Value (CLV), within Salesforce. This is exactly where a reverse ETL tool can help. With reverse ETL tools, you can push your data directly from your marketing data warehouse into the third-party systems leveraged by line-of-business (LOB) users. 

With all the above steps in place, you can now say that your data warehouse is set up for marketing analytics. 

Things to Remember While Setting up Your Marketing Data Warehouse

To set up your marketing data warehouse optimally, here are a few things that you should keep in mind:

• You can reduce the frequency at which non-critical data in your data warehouse is updated, deleted, or queried. This is about prioritizing the data which is critical for making business decisions, and that needs to be updated or deleted on a regular basis. By doing so, you’ll optimize the efficient and cost-effective usage of your marketing data warehouse.
• Every time it is not required to store granular level of data in the data warehouse. To tackle this, you can perform lighter transformations on your data before loading it into your data warehouse using tools with pre-load transformation capabilities, such as Hevo Data. In doing so, you will retain an intermediate level of detail for the data. This will not only save time while query processing but also help improve performance significantly. 
• You can compute pre-joins on your data to simplify your queries and make them way cheaper. Every time you want to run some queries after joining multiple tables, the processing time will increase. However, by computing pre-joins and saving them, you’ll save a lot of time involved in scanning, which will also cut down costs.
• Instead of performing multiple queries on a huge dataset, you can perform repeated queries on a smaller materialized view. This is because the queries don’t need to rescan all the tables repeatedly. Rather, it can just report the answer from the materialized views. This will not only improve the performance of your warehouse significantly but also cut down costs. 

Final Thoughts

As you can see, the marketing data warehouse plays a central role in the modern data stack. Are you now ready to take the next step and set up a near real-time marketing data warehouse to make the most of your marketing efforts? If yes, then signup for your preferred warehouse solution and set it up. Once it’s ready, then you can opt for a cloud ELT tool like Hevo with pre-built connectors to easily move data from all your marketing sources to the marketing data warehouse.