Christian Soujon

A Cookbook for Marketing Analytics on GCP

Weather-Transaction Correlation

in BigQuery

Weather can have a significant impact on user behavior. Many businesses think about the impact a hot summer day can have on their conversion rates. But overlapping effects from seasonality and weather require us to provide exact, session-based weather data. One approach is to ext ...

Cumulated And Rolling Calculations

When metrics fluctuate a lot, or a single row should take the bigger picture into account, cumulated and rolling calculations can be beneficial to get a clear view. The absolute classics are the rolling aggregation and the year-to-date cumulation, but you might come up with many ...

Google Analytics Timings

in BigQuery

Google Analytics' standard reporting features for custom timing or duration is minimal. In SQL on raw data, however, this is pretty easy and flexible. In this post, we take a look at how to work more flexibly with the session date, calculate exact timestamps of hits, and analyze ...

Custom Dimensions And Metrics

in BigQuery

Having a strong concept for custom dimensions is what differentiates an okay Google Analytics setup from a good one. With GA360, we can specify up to 200 custom dimensions and 200 custom metrics. With all that space, we should generally overuse them instead of being too scarce wi ...

Funnels and Flows

in BigQuery

Let's take a look at two approaches for creating a funnel or flow analysis: The typical window navigation query and a simple subquery example. Both approaches only create funnels within one user session how to get around that we review in the end. Window navigation A straightforw ...