Marketing Data Centralization With Google BigQuery

Are you a website owner or marketer looking to make data driven decisions about your site's visitors’ marketing tactics and conversions? Ever felt like your digital marketing tools weren't telling you the full picture when it comes to your digital data? Do you find it difficult to attribute conversions to specific channels? Do the expensive marketing attribution software's just not cut it for your business needs?

You’ve come to the right place! In this excerpt from our data analytics team, we will discuss how to solve these questions with a customizable and scalable solution through Google Analytics 4 and Google BigQuery.

Why Google Analytics 4 (GA4)?

Most website owners and marketers are familiar with web tracking software such as Google Analytics. In fact, they likely use it every day. The problem we’ve found with Google Analytics is that the data is great, but acting on the data can be tough. For example, users are limited to 5 or so columns when exporting reports and they are often sampled reports and not the full thing.

For this reason, we recommend that businesses upgrade their Google Analytics Universal analytics properties to the new and improved version of Google Analytics which is Google Analytics 4.

We did an in-depth review of GA4 and the benefits, but for this use case, the 3 main reasons you should care about Google Analytics 4 is as follows:

Google BigQuery Linking

GA4 provides a native integration with Google BigQuery where businesses can begin their low-cost digital data warehousing adventures. We recommend starting small with GA4 and other data sources

• GA4 provides a native integration with Google BigQuery where businesses can begin their low-cost digital data warehousing adventures. We recommend starting small with GA4 and other data sources
• For example, our website averages over 1 thousand sessions a month and we don’t pay any monthly fees.
• Another website we manage see over 2 million sessions a month and they only incur around $30 worth of monthly data processing and storage fees

Better User Profiling Abilities

• As privacy laws and issues continue to change the analytics landscape, Google took aim at solving the cookie problem with GA4 with better device to user tracking abilities.
• A “user_pseudo_id” is assigned to every user session that Google calculates with browser and device information and exposes to us, to leverage in reporting.

Limited Sampling

• GA4 has a full set of reports that don’t sample data (in other words they show you every data point).
• This is especially true when using the Google Analytics 4 for BigQuery link.

Marketing Data Centralization With Google Analytics 4 And Google BigQuery

Now that we’re on GA4 we can literally click a single button and Google will start piping our data from the GA4 interface into data tables in Google BigQuery. If you're not familiar, Google BigQuery is Google's cloud-based database that marketers and businesses can use as their data centralization engine. As we mentioned before, we will need a credit card to set up a google cloud instance for this to work. Trust us, it’s worth it!

Now that we’re linked, Google will automatically send GA4 data to Google BigQuery daily. Every metric and dimension in GA4 are now available it the extremely wide, but well-structured Google BigQuery “events_” table.

I Have Google Analytics 4 For BigQuery Setup, Now What?

Well, now that we have Google Analytics data at our fingertips in Google’s cloud-based SQL warehouse called Google BigQuery, the world is really our oyster. We can write queries to view Google Analytics data in any which way that’s important for measuring our business.

Imagine you’re a B2B business with lead generation forms, if I were you, I’d create a Google BigQuery table with:

• Every user that has contacted us
• When they contacted
• The date we first saw them
• What pages they've seen (upsell opportunity?)
• Which channels did they come from (who gets the credit?)
• Geo location

If you’re an ecommerce business, focused on transactions, if I were you, I’d create a Google BigQuery table with:

• Every user that has placed an order with us
• How many days since the users last order (loyalty opportunity?)
• The user's favorite product ordered (remarketing opportunity?)
• The last time the user has been to the site

Then, as a business, how can we leverage this data to drive impact? We can study our users and customers and make product/service enhancements based on our findings. We can segment our users based on their behavior and pass a list to marketing to take action. The opportunities are endless now that we have the data.

These are 2 ideas for 2 different types of companies, but hopefully you get the idea. Now that we have the data, we can essentially do...whatever we want with it. We are no longer bound by the limitations of analyzing data within the GA interface and excel.

Google BigQuery For Marketing

Okay, so Google Analytics data is great, but what else can we do with Google BigQuery in marketing? Why not start by identifying our top marketing data source and piping that data into Google BigQuery as well.

For example, maybe email marketing is your go to channel that brings users to your site. And oh, you sue Mailchimp as your service provider. Let’s set up a data pipeline that feeds Mailchimp data to Google BigQuery on a daily basis, and then build email marketing performance (Mailchimp) and site behavior (GA4) specific table.

This blending of data sources helps us to identify greater insights into our data and in marketing. For example, with just Google Analytics data, I could tell you that email sessions are up or down. But when we blend the two data sources, I can now see exactly why: more send, higher read to click engagement with the welcome series email, etc.

Once we feel good about our marketing data foundation with our top channel, let’s not stop there.... let’s build out an entire digital data analytics ecosystem with Google BigQuery in the center of it all.

Marketing Data in Google BigQuery

Now what? Well now for the fun part... We can do several things like:

Reporting

• Now that we have our data in one place, we can build meaningful reports of our entire digital ecosystem

Advanced Analytics Segmentation

• Now that we have a base of historical data building in Google BigQuery, we can perform deep learning and analysis on that data
• For example, we can analyze the actions that our users typically take before converting. Then we can build a list of those users who have taken the “pre action” but still haven’t converted. Then we can push them through the on-site funnel via messaging, personalization, and/or marketing tactics.

Predictive Models

• With a plethora of historical data collecting, we can build predictive models to predict things like what type of user is most likely to convert.
• With this information, we can cross sell and upsell active and idle users