Comparing the same week (weekday for weekday) last year in Google Data Studio with Analytics 360 and BigQuery

Google Data Studio offers some powerful reporting features, but one piece we’re still waiting on is more control over comparisons — right now you can compare your data against the “previous period” or “previous year”, the latter of which gives you a same-date comparison year-over-year. However, a common requirement is being able to report on the same weekday year-over-year.

Over on the Napkyn blog, I wrote up my detailed (and consequently long) overview of how to accomplish day-of-week comparisons year-over-year with Data Studio and Google Analytics. The solution provides Google Analytics 360 customers with a way to accomplish this by using the GA integration with Google BigQuery.

Have a look, and let me know if you have any feedback in the comments!

Google Analytics default channel groupings in the BigQuery export

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!