One of Google Analytics 360’s nicest features is the ability to export its raw dataset to Google BigQuery, giving you access to run your own queries on the data set without the constraints of the user interface. However, that data set does not include various dimensions and metrics that are derivative of the collected data. Often you need to align the data you’re acquiring from Google, so sometimes it’s necessary to recreate these data points in the queries you run in BigQuery.
Over on the Napkyn blog, I wrote up a detailed post on how to recreate Google Analytics channel groupings in BigQuery. The result? An 80-line Standard SQL query that generates all the default groupings that Google offers according to their definitions (which can, of course, be customized to get custom groupings).
That has some use in terms of tinkering with those definitions, but it’s also a valuable learning tool as it can help you understand not only how those channel groupings are defined in the first place, but also the structure of the BigQuery schema that is exported from Google Analytics.
After Google last week announced that they’re offering up a sample of the Google Analytics BigQuery data set it’s a good time for analysts to brush up their SQL skills and get to understand that data!