Blog

November 2023 – Google Analytics 4 and BigQuery with Scott Zakrajsek

Over fifteen years ago, Scott Zakrajsek was one of the founding organizers of Columbus Web Analytics Wednesday (he’s the guy in the green shirt right in the middle of this picture taken in May, 2008; three people in this picture, as well as the photographer, were present at our November 2023 event, and none of us could remember the name of the restaurant; the passage of time does wonders to one’s memories):

Several role changes, co-founding a company, spending several years in Boston before returning to central Ohio, getting married and having a couple of kids, and we finally convinced him it was time to re-take the stage at one of our meetups!

The topic: Google Analytics 4 and BigQuery. That’s a Big(Query) topic to cover briefly, but these two platforms are increasingly intertwined, as it has been evident for a while that Google has decided that the road to flexibility and robustness in accessing and analyzing GA4 data is a path that passes directly through BigQuery.

Scott provided a brief recap of how the fundamental data model in GA4 differs from Universal Analytics. He then made the case for why the ease with which that data can be piped into Google BigQuery (he outlined the steps for turning on that integration, including highlighting the key choices to be made when doing that) enables both deeper analysis as well as easier integration of website and mobile app behavioral data with data from other sources.

Once the data is in BigQuery, though, it has to be made accessible, both to analysts and to business users. For the former, that means SQL, and it means going beyond simply SELECT, FROM, WHERE, ORDER BY, and GROUP BY to also be comfortable with UNNEST, subqueries, and CTEs. He demonstrated how generative AI—Bard, as one option (which led to a brief discussion of Duet AI and Copilot as other options)—could be used to get an initial pass at functional SQL, although some tweaking is generally required. That led to a discussion of the difference between SQL-for-exploration-and-one-time-analysis vs. SQL-to-be-productionalized.

To wrap the session, Scott conducted a live demo, including pushing the results of a query into Looker Studio.

The presentation was followed by a great discussion that demonstrated the value of in-person meetups—attendees included both several individuals who are elbows-deep in GA4 with BigQuery as well as a number of BigCurious individuals who were able to tap into the experience of Scott and the attendees to get a much better since of what is involved in bringing the two platforms together.

More details? Check out the slides:

And, hey, the same guy who took that picture at the top of this post with 2008 digital photography tech has upgraded his gear a few times since then, so there are pictures from the event, too:

 

Bonus: in the intro, Bryan brought up the User Journey – Vol. 1 rock opera that long-time WAW co-organizer Jason Packer was instrumental in producing!