{"id":116592,"date":"2025-03-07T00:35:59","date_gmt":"2025-03-07T00:35:59","guid":{"rendered":"https:\/\/peraltafinancing.com\/business\/marketing\/improve-your-ga4-reporting-with-bigquery-sessionized-tables\/"},"modified":"2025-03-07T00:35:59","modified_gmt":"2025-03-07T00:35:59","slug":"improve-your-ga4-reporting-with-bigquery-sessionized-tables","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=116592","title":{"rendered":"Improve Your GA4 Reporting with BigQuery Sessionized Tables"},"content":{"rendered":"


\n<\/p>\n

A few months ago, I wrote an article touting the benefits of using BigQuery custom SQL<\/a> inside Data Studio<\/a>\u2014Google\u2019s free visualization suite for analytics data\u2014and explained how this method could be used to produce eCommerce funnel reports.<\/p>\n

Shortly after that article was published, a few of my teammates mentioned that they\u2019d love to see how we could use Data Studio with customized Google Analytics 4 (GA4) data. I enjoy exploring interesting use cases for Data Studio and often find myself pushing the envelope as I look for ways to answer complex questions, so I was happy to start experimenting with the possibilities.<\/p>\n

Many businesses have heard and begun to act upon the news that \u201cUniversal Analytics will no longer process new data beginning July 1, 2023\u201d, so I anticipate attention will soon shift from implementing GA4 analytics to extracting insights from your new data.<\/p>\n

In my opinion, Google Analytics (GA4) reporting methodologies\u2014such as the built-in GA4 Reports and Explorations<\/a>\u2014are still in their infancy, with continued development and improvement plans hopefully in the works. Even though GA4 reporting solutions are available now through the administration pages, they will face limitations when it comes to highly customized reports that reach beyond traditional capabilities. In my book, BigQuery wins for having the most accurate data and the best range of options for event-level flexibility.<\/p>\n

Bridging the Gap: How to Make GA4\u2019s Event-Centric Data Work for You<\/h2>\n

\"Two<\/p>\n

It\u2019s easy to recognize that the mandated change to GA4 is a difficult one. As Google Analytics 4 data becomes available, it\u2019s logical for us to want to build reports similar to those used for previous analysis. And perhaps not surprisingly, our clients want to continue to view dashboards and metrics that they\u2019re familiar with after working with Universal Analytics data for so many years.<\/p>\n

Out of the gate, GA4 data is completely event-centric\u2014not session-centric. Universal Analytics did a lot of great aggregation for us, and even applied default user attribution rules; but simply connecting Data Studio to GA4 data and expecting similar reporting data is a lot to ask.<\/p>\n

As I attempted to construct such reports during my experimentation, I realized I wanted an already transformed table that mapped the data I needed using the raw event-structured GA4 data. It would be ideal to see a schema focused around sessions, as is prevalent in the majority of existing Universal Analytics reports. Unfortunately, GA4 data in this structure is currently not readily available via BigQuery.<\/p>\n

My \u201cAha\u201d Moment: Transforming GA4 Event Data into Session Data with Looker<\/h2>\n

\"Two<\/p>\n

A recent client project required me to convert a suite of analytics reports from utilizing Universal Analytics data to utilizing GA4 data within Google\u2019s Looker business intelligence platform<\/a>.<\/p>\n

One of Looker\u2019s strengths is strong data governance; and as I began the reporting conversion task, I discovered that Looker had built a transformational GA4 component. Since Looker is built on a series of LookML blocks, they\u2019ve already created a methodology to construct session-based intermediary tables that perform exactly the type of transformation I\u2019d been looking for.<\/p>\n

Inside Looker, a clever extraction of Google Analytics (GA4) event data is transitioned to a cached and sessionized internal table<\/a>. Using this internal table schema, Looker designers can draft and create \u201cLooks.\u201d These \u201cLooks\u201d then become elements in dashboards, which take advantage of the internally transformed data.<\/p>\n

Armed with a new methodology for transforming raw GA4 event data into session data, I determined the next course of action in my mission to power commonly requested reports would be to schedule a daily GA4 sessionized table creation process. This step relies on the Looker sessionized view, and establishes a new partitioned table in BigQuery on a daily basis.<\/p>\n

\"Scheduling<\/p>\n

Here are a few advantages to this approach:<\/strong><\/p>\n