In a few months Universal Analytics will stop working. If you have already implemented your GA4 (Google Analytics 4) property then it’s a good start. But what about your data models ?
It could be challenging to start from scratch. That’s why we would like to propose one simple starting solution that in our opinion could fit many use cases and analysis for you and your team. This implementation does not answer every use case but it should definitely be one of the Google Analytics datamarts in your possession. Prior to any implementation, you should always do a workshop with the different business stakeholders to understand their needs; this will drive in the end your optimal Google Analytics models.
Nota bene: in this article we simplify the model a lot to explain easily how to build it. Contact us if you want to know more about the full dbt code.
Methodology
GA4 has an “event” granularity, with every “event” linked to a page. Our approach is to have a session / pages granularity like the example below:
With this structure you will be able to add as many dimensions and measures you want per page (like channel, device, number of add_to_cart clicks, number of transactions and revenue). All of those dimensions and measures are linked to a specific page that’s why it seems relevant to have this type of granularity.
Implementation Steps
Let’s take an example, imagine you want to analyse basic metrics like number of sessions, number of users, and one click button event that we are going to call “book a meeting”. The resulting table we would like to have is this one:
The first step is to flatten the google analytics raw table.
Flat table
Let’s do it with fewer fields to simplify our example, but of course you can add as many fields as you want.
To have this session / page granularity we need to identify the ranking of every page during a session and regroup events per page. Let’s do it this way:
Ok now we join this table to the flat_ga4:
It will give us the table below:
This flat table serves as a foundational starting point for any desired granularity, whether it be at the session level, daily level, or focused on a particular event.
Session / Pages table
To create our final table that will be used by our BI tool, we need to group by session and row_pages. Remember, this row_pages field allows us to identify the ranking of every page during a session and regroup events per page.
That’s it! Now we have our final table. You can modify the code and add as many events and measures as you want. For our example we built some visualisations in Looker like the ones below.
Conclusion
Why this granularity? The “sessions / events” has some drawbacks because depending on your traffic and number of events you could end up with a huge table, and high storage / request cost. In the meantime, with the session / page granularity, a detailed table is present and we are still able to analyse what appends on a page for a specific session, and understand the journey of our users.
Thank you
For this example we simplified the way we build the different models. If you want to have the full code, go on our website and book a meeting with us 😉 We will be glad to discuss how we can implement the best Google Analytics data modelling to feed your business needs.
If you enjoyed reading this article, stay tuned as we regularly publish articles on Looker and how to get the most value of this tool. Follow Astrafy on LinkedIn to be notified for the next article ;).
If you are looking for support with your Looker implementation, advice on Data Stack or Google Cloud solutions, feel free to reach out to us at sales@astrafy.io.