Marketing data warehouse solutions allow you to provide them with timely, targeted and personalized advertising experiences while respecting the privacy of your users. This post is for data engineers, data scientists, or IT members who support marketing analytics.

Implementing a marketing data warehouse helps you meet your following business needs:

• Extensive insights: If you use multiple software services (SaaS) platforms, you can use this architecture to combine marketing and advertising data in BigQuery. If you're a business stakeholder, you can get real-time insights into marketing and business performance.

• Marketing innovation: If you are a data scientist or data engineer, you can create machine learning (ML) models for business needs such as customer segmentation, customer lifetime value, product recommendations, and purchase estimates. You can enable these models across multiple platforms, such as email marketing or ad targeting.

• Customer Experience: A marketing data warehouse gives you a higher visibility into customer preference, so you can improve your customers' experience through the right personalization. Gaining this insight allows you to personalize your customers' interaction points, such as first-party apps, websites, online ads, and email marketing.

Architecture

The diagram below shows a typical marketing analytics reference architecture that uses multiple data analytics and ML products.

‍

The diagram shows the following stages that you can configure in a marketing data warehouse workflow:

• Data collection
• Data processing
• Machine learning
• Insights and activation

What Are the Stages of Marketing Data Warehouse?

This section describes the stages and required technology components in a marketing data warehouse solution.

Data Collection

The first stage of building a marketing data warehouse is to consolidate your data in one central location. You can collect data from the following data sources:

• Google and SaaS platforms: You can import data sources such as Google Analytics, Google Ads and Google Marketing Platform into the Google Cloud marketing data store in BigQuery. SaaS connectors are available through Google Cloud and our partners to get data from sources like Salesforce.

• Public CloudEnvironments: You can use BigQuery VeriTransfer Service to retrieve data from other public clouds. For example, to move data from Amazon S3 to BigQuery, you can automatically schedule and manage repetitive loading jobs. You can also use BigQuery Omni, a flexible, multi-cloud analytics solution that lets you analyze data from Google Cloud and Amazon Web Services.

• APIs and on-premise first-party data: You can get data from sources such as customer relationship management (CRM) or point-of-sale (POS) systems. Usually, you do this data collection offline using the bq command line tool, BigQuery API, or Google Cloud console. You can upload data locally or from Cloud Storage. For large datasets, we recommend using Cloud Storage to optimize your bandwidth usage, network speeds, and product integration. You can set Cloud Function triggers to upload activity-based data to BigQuery. For example, set triggers based on new data availability.

Most of the above aggregation approaches use bulk uploads. If you want to import any streaming data set to BigQuery, you can use BigQuery's streaming capabilities. For flow analytics use cases, see flow analytics solutions.

Data Processing

After collecting the data, you can process the data as needed. This step is applied when you need to process the data before running the queries. Data processing involves cleaning and reformatting to ensure consistency across large datasets. You can use data processing products within Google Cloud.

Depending on who your users are, you can choose the appropriate Google Cloud product. For example, consider the following types of users and recommended products:

• Developers who create data lines can use the Cloud Data Fusion data integration product. Cloud Data Fusion has a UI that allows you to deploy your ELT and ETL data pipelines without code.

• Data engineering teams that support marketing analytics can use DataFlow. Dataflow allows you to scale and analyze both bulk and stream data sources.

• Data analysts can use Dataprep by Trifacta, which allows you to export data for analysis in BigQuery.

Machine Learning 

After your system collects and processes the data, you can use the Google AI Platform product options for the following use cases:

• Per user - descriptive analytics on the conversion of frequency per campaign: This information helps you to tailor the targeting frequency of your remarketing campaigns based on a specific list of users. BigQuery provides access to raw Campaign Manager 360 data, making this information available.

• Predictive analytics on lifetime value for specific users: When you estimate the value of specific user groups, you can run marketing campaigns to drive sales. For example, you might discover that a user group with limited brand engagement has a high purchase potential if users engage more. You get this insight by combining data and using ML to build customer segments and estimate a lifetime amount of value.

• Suggestive analytics on product sentiment: To avoid mistargeting, you can analyze the change of text comments and ratings. This analysis allows you to predict how a product with certain characteristics may satisfy a certain group of users. For example, you can use emotion analysis and customer segmentation to predict emotion.

With the consolidated marketing data in BigQuery, you can choose an AI Platform product that suits your needs. You can choose one of the following products based on your organization's ML maturity and skill set:

• If your organization does not work with ML at all, With AutoML, you can create and deploy customer ML models. For example, you can use AutoML Tables to create regression and classification models, such as customer churn probability and customer lifetime value.

• If your organization has SQL skillsBigQueryML allows you to use SQL structures such as model building, evaluation, and estimation, for example, mass segmentation models. You can train, deploy, and execute ML workflows on many supported models without migrating data from BigQuery.

• If you have a team of data scientists in your organizationYou can create and deploy scale-optimized models using Vertex AI. For an example of how to use Vertex AI to solve customer lifetime value, see “Predicting Customer Lifetime Value” with “AI Platform”.

Insights and Activation

You can use Google Cloud options to get insights from combined advertising and marketing data. You can then bring the data back (for example, segmented segments) to platforms such as Google Analytics and email marketing. Google Cloud offers multiple ways to take action on your data based on your needs.

For example, you can get your parsed segments back to your preferred channels, Google Analytics or Salesforce.

Looker for Google Marketing Platform

You can review and share insights through Looker, which is also a business intelligence (BI) platform. You can use Looker to merge multiple datasets, track cross-channel customer behavior, and segment customers by features.

You can use Looker to create the following interiors:

• Return on investment (ROI) analysis: Understand the revenue spent and generated by campaigns.

• Warning: Set custom rules to receive email alerts when tactics or ads go wrong.

• Cross-channel behavior analysis: Identify trends in customer behavior between and across your marketing channels.

• A/B test: Analyze how your variations may influence key user behavior based on statistically significant results.

• Acquisition channels: Track where new leads and customers are coming from.

• Cohort analysis: Segment your data and analyze how different segments behave over time.

Blocks and actions in Looker form the basis of robust and shareable analytics for Google Marketing Platform advertising and web data. These customizable blocks and actions offer interactive data discovery, new data slices with lightweight ML predictions, and activation paths back to the Google Marketing Platform. Activation paths allow you to effectively deliver your first-party data to the target audience.

The diagram below shows how Google products can work with Looker.

‍

The diagram shows that Looker can generate real-time reporting from any data in Google Analytics 4, Google Analytics360, Campaign Manager 360, and BigQuery.

You can enable first-party data from Looker on the Google Marketing Platform via Actions for Ads (via Customer Match) and Data Import for Analytics.

Custom Integrations

You can also use Google Cloud to create custom integrations to push data back to your preferred platforms. For example, you can run programmed queries to create audience lists with your Analytics data, and then push the data back with API calls. For example, you can use Cloud Functions to trigger a data push when a new segment is ready in Cloud Storage.

‍