Building Marketing Data Warehouse
on Google Cloud Platform (GCP)

When you understand how customers interact with your brand, you drive lifetime value (LTV) and enable deeper marketing insights. The marketer's role is evolving from traditional campaign execution to relevant, real-time engagement. Where data capture and retroactive performance analysis drove the old paradigm, today's marketer uses data-backed customer insights, performance-led strategy, and proactive, thoughtful targeting.

Use case

The fictional company in this example is an online cosmetics retailer, with you as the chief marketing officer. You want to get key insights while minimizing the amount of technical engagement with DevOps teams. You have limited IT resources, but you do have the help of a data scientist. Your primary challenge is to optimize the marketing budget by tracking the return on investment (ROI) of ad spending, but

  • Data is scattered across Google Analytics 360, Customer Relationship Management (CRM), and Google DoubleClick Campaign Manager (DCM) products, among other sources.
  • Customer and sales data is stored in a CRM system
  • Some data is not in a queryable format.
  • No common tool exists to analyze data and share results with the rest of the organization.

Architecture

The following architecture diagram illustrates the process for moving from ingesting data from various sources to making re-marketing decisions.


  • In this diagram, some datasets are lighter in color to indicate that they are not part of the specific use cases described in this article, even though you could address them in the same way. For example, this article shows you how to run DoubleClick for Publishers (DFP) or YouTube queries on DCM data, but you could do the same for data exported to BigQuery.
  • The diagram includes a section labeled More advanced. When you have data consolidated in a central location, a data scientist can help you use the data to do more advanced work, such as machine learning.

Functional requirements

This section explains the technology options based on the following functional requirements..

  • Collecting and storing data
  • Transforming data
  • Analyzing data
  • Visualizing data
  • Activating data

Transform & Analyze

You can use BigQuery to do batch transformation from one table to another or by using a View. But for more advanced transformations, you might prefer a visual tool that can run terabytes of data through a complex processing pipeline with minimal programming requirements.After you save your cleaned data centrally, you can begin analyzing it for insights. Having the data available in BigQuery offers several advantages:

  • You can run queries on data bigger than, for example, what a DoubleClick reporting API or UI can handle.
  • You have access to finer-grained data that is not always available in UI or reporting APIs.
  • You can process and join data from multiple sources by using a common key.

Activate

With raw data in a common location, accessible through both code and dashboard and in a platform that can manipulate data, many marketing decisions become possible—for example:

  • Descriptive analytics on how frequency affects conversion per user per campaign- Having this information helps when you build remarketing campaigns to adapt frequency on a specific list of users. BigQuery's access to raw DCM data makes this information possible.
  • Diagnostic analytics to understand the impact of a campaign and website behavior on your sales- To activate these analytics, you use SQL statements to create joins of IDs over big data..
  • Predictive analytics on LTV for specific users- By predicting the value of specific groups of users, you can run marketing campaigns to increase sales. An example would be the blue-dot graph in the previous diagram, where you might discover that a group of users with limited brand engagement has a high potential of buying if the users are more engaged. You gain this insight through joining data and using machine learning to build customer segments and predict an LTV amount.
  • Prescriptive analytics on product sentiment- By analyzing the evolution of text comments and ratings, you can help prevent inaccurate targeting by predicting how a certain group of users will receive a product that has certain characteristics. You might do this task by using sentiment analysis and customer segmentation, for example.