3 min read

Part 2 - Advanced Time Series Forecasting Using GA4 and BigQuery ML for Ecommerce Websites: A Practitioner's Guide

Part 2 - Advanced Time Series Forecasting Using GA4 and BigQuery ML for Ecommerce Websites: A Practitioner's Guide
Photo by Stephen Phillips - Hostreviews.co.uk / Unsplash

As mentioned in the previous blog, this post is part two of our series on advanced time series forecasting using GA4, with this part focusing on the practical implementation.

Exploring the Rich Dataset of GA4

In one of my previous articles, I have gone through the details of GA4 data structure. This blog is more focused on practical usage of GA4 data. It tracks individual user interactions with your website, providing a treasure trove of detailed data. Here's a representation of GA4 data:

Since its nested, we will need to preprocess it to use as an input to our model.

Preprocessing GA4 Data in BigQuery

We will have to flatten the data before applying any forecasting models. Let's start with preprocessing the GA4 data. First, we'll create a new table to hold the flattened data. Here's the SQL command:

It seems that the dataset contains events from a single day (June 18, 2023), So we will use wildcard * to select from all of dates with  Let's modify the preprocessing step to use a wildcard:
Now that we have the right data set here - We will use BQML (Vertex AI) to create and execute machine learning models using SQL queries. With BQML's integration with Vertex AI, we can leverage powerful time series algorithms with relative ease. In our scenario, let's use the ARIMA_PLUS model in BQML. 

Here’s a high level snapshot of various steps that BQML automates for us in the background. 

Let’s call the model within the same BigQuery SQL engine - I mean, this is amazing!
Let’s go to the Model and explore

Now that the model has been successfully trained, we can use it to forecast future event counts. The following SQL query uses the ML.FORECAST function to predict the total event count for the next 7 days:

One thing that is SUPER COOL about BigQuery is that it offers you one-click integration with Sheets, Looker and now Google Colab
Obviously, I used it and created this interesting chart 
Based on this I better buckle up and write some good stuff so that my 26th July events are not as low as 11!!!

Combining a BQML ARIMA_PLUS forecasting model with GA4 data provides a number of significant advantages for e-commerce businesses. First, it allows businesses to harness the power of time series forecasting to gain deep insights into user engagement metrics. By accurately predicting daily active users, session lengths, and user engagement trends, businesses can proactively tailor their strategies, align with changing consumer demands, improve user experiences, and drive long-term growth.

In addition, the combination of GA4 data and the BQML ARIMA_PLUS model gives e-commerce websites unparalleled potential to optimize their performance across key metrics, such as sales, conversion rates, cart abandonment rates, and other critical factors. By providing detailed forecasts, businesses can make informed decisions about sales strategies, inventory management, and resource allocation. Additionally, the ability to forecast marketing performance, which includes a comprehensive evaluation of marketing channels, campaigns, and ad groups, enables businesses to optimize their marketing spend, fine-tune their strategies, and maximize impact in an ever-changing landscape.

Please let me know if you liked this topic and want me to write on other use-cases leveraging GA4 data for forecasting.

If you have any questions or want to have any discussion on this topic, please reach out to me at LinkedIn.