{"id":93829,"date":"2025-02-17T15:09:09","date_gmt":"2025-02-17T15:09:09","guid":{"rendered":"https:\/\/peraltafinancing.com\/analytics\/bigquerytips-query-guide-to-google-analytics-app-web\/"},"modified":"2025-02-17T15:09:09","modified_gmt":"2025-02-17T15:09:09","slug":"bigquerytips-query-guide-to-google-analytics-app-web","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=93829","title":{"rendered":"#BigQueryTips: Query Guide To Google Analytics: App + Web"},"content":{"rendered":"


\n<\/p>\n

\n

I\u2019ve thoroughly enjoyed writing short (and sometimes a bit longer) bite-sized tips for my #GTMTips<\/a> topic. With the advent of Google Analytics: App + Web<\/a> and particularly the opportunity to access raw data through BigQuery<\/a>, I thought it was a good time to get started on a new tip topic: #BigQueryTips<\/strong>.<\/p>\n

For Universal Analytics, getting access to the BigQuery export with Google Analytics 360 has been one of the major selling points for the expensive platform. The hybrid approach of getting access to raw<\/strong> data that has nevertheless been annotated with Google Analytics\u2019 sessionization schema and any integrations (e.g. Google Ads) the user might have enabled is a powerful thing indeed.<\/p>\n

With Google Analytics: App + Web<\/strong>, the platform is moving away from the data model that has existed since the days of Urchin, and is instead converging with Firebase Analytics<\/a>, to which we have already had access with native Android and iOS applications.<\/p>\n

\n

<\/p>\n

\"BigQuery<\/p>\n

<\/a><\/p>\n<\/div>\n

Fortunately, BigQuery export for App + Web properties<\/a> comes at no additional platform costs – you only pay for storage and queries just as you would if creating a BigQuery project by yourself in Google\u2019s cloud platform.<\/p>\n

So, with data flowing into BigQuery, I thought it time to start writing about how to build queries against this columnar data store. One of the reasons is because I want to challenge myself, but the other reason is that there just isn\u2019t that much information available online when it comes to using SQL with Firebase Analytics\u2019 BigQuery exports.<\/p>\n

\n

<\/p>\n

\"Pawel\"\/<\/p>\n

<\/a><\/p>\n<\/div>\n

To get things started with this new topic, I\u2019ve enlisted the help of my favorite SQL wizard in #measure<\/a>, Pawel Kapuscinski<\/strong><\/a>. He\u2019s never short of a solution when tricky BigQuery questions pop up in Measure Slack, so I thought it would be great to get him to contribute with some of his favorite tips for how to approach querying BigQuery data with SQL.<\/p>\n

Hopefully, #BigQueryTips<\/strong> will expand to more articles in the future. There certainly is a lot of ground to cover!<\/p>\n


\n X<\/span><\/p>\n

\n
\n The Simmer Newsletter<\/strong>\n <\/p>\n

\n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n

<\/span><\/p>\n

Getting started<\/h2>\n

First of all, you\u2019ll naturally need a Google Analytics: App + Web<\/strong> property. Here are some guides for getting started:<\/p>\n

\n

<\/p>\n

\"Link<\/p>\n

<\/a><\/p>\n<\/div>\n

Next, you need to enable the BigQuery export for the new property, and for that you should follow this guide<\/a>.<\/p>\n

\n

<\/p>\n

\"Mode<\/p>\n

<\/a><\/p>\n<\/div>\n

Once you have the export up-and-running, it\u2019s a good time to take a moment to learn and\/or get a refresher on how SQL works. For that, there is no other tutorial online that comes even close to the free, interactive SQL tutorial at Mode Analytics<\/a>.<\/p>\n

\n

<\/p>\n

\"Bigquery<\/p>\n

<\/a><\/p>\n<\/div>\n

And once you\u2019ve learned the difference between LEFT JOIN<\/code> and CROSS JOIN<\/code>, you can take a look at some of the sample data sets for iOS Firebase Analytics<\/a> and Android Firebase Analytics<\/a>. Play around with them, trying to figure out just how much querying a typical relational database differs from accessing data stored in columnar structure as BigQuery does.<\/p>\n

At this point, you should have your BigQuery table collecting daily data dumps from the App + Web<\/strong> tags firing on your site, so let\u2019s work with Pawel and introduce some pretty useful BigQuery SQL queries<\/strong> to get started on that data analysis path!<\/p>\n

Tip #1: CASE and GROUP BY<\/h2>\n

Our first tip covers two extremely useful SQL statements: CASE<\/code> and GROUP BY<\/code>. Use these to aggregate and group your data!<\/p>\n

CASE<\/h3>\n
\n

<\/p>\n

\"CASE<\/p>\n

<\/a><\/p>\n<\/div>\n

CASE<\/code> statements are similar to the if...else<\/code> statements used in other languages. The condition is introduced with the WHEN<\/code> keyword, and the first WHEN<\/code> condition that matches will have its THEN<\/code> value returned as the value for that column.<\/p>\n

You can use the ELSE<\/code> keyword at the end to specify a default value. If ELSE<\/code> is not defined and no conditions are met, the column gets the value null<\/code>.<\/p>\n

SOURCE TABLE<\/h4>\n\n\n\n\n\n\n
user<\/th>\nage<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\n15<\/td>\n<\/tr>\n
23456<\/td>\n54<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SQL<\/h4>\n
\n
SELECT<\/span> user<\/span>, age,\n  CASE<\/span>\n    WHEN<\/span> age >= 90<\/span> THEN<\/span> \"90+\"<\/span>\n    WHEN<\/span> age >= 50<\/span> THEN<\/span> \"50-89\"<\/span>\n    WHEN<\/span> age >= 20<\/span> THEN<\/span> \"20-49\"<\/span>\n    ELSE<\/span> \"0-19\"<\/span>\n  END<\/span> AS<\/span> age_bucket\nFROM<\/span> some_table<\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n
user<\/th>\nage<\/th>\nage_bucket<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\n15<\/td>\n0-19<\/td>\n<\/tr>\n
23456<\/td>\n54<\/td>\n50-89<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The CASE<\/code> statement is useful for quick transformations and for aggregating the data based on simple conditions.<\/p>\n
GROUP BY<\/h3>\n
\n
    <\/p>\n
    \"GROUP<\/p>\n
    <\/a><\/p>\n<\/div>\n
GROUP BY<\/code> is required every time you want to summarize<\/strong> your data. For example, when you do calculations with COUNT<\/code> (to return the number of instances) or SUM<\/code> (to return the sum of instances), you need to indicate a column to group these calculations by (unless you\u2019re only<\/em> retrieving the calculated column) . GROUP BY<\/code> is thus most often used with aggregate functions<\/a> such as COUNT<\/code>, MAX<\/code>, ANY_VALUE<\/code>, SUM<\/code>, and AVG<\/code>. It\u2019s also used with some string functions such as STRING_AGG<\/code> when aggregating multiple rows into a single string.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n
user<\/th>\nmobile_device_model<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\niPhone 5<\/td>\n<\/tr>\n
12345<\/td>\nNokia 3310<\/td>\n<\/tr>\n
23456<\/td>\niPhone 7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span> \n  user<\/span>, \n  COUNT<\/span>(mobile_device_model) AS<\/span> device_count\nFROM<\/span> table<\/span>\nGROUP<\/span> BY<\/span> 1<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n
user<\/th>\ndevice_count<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\n2<\/td>\n<\/tr>\n
23456<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In the query above, we assume that a single user can have more than one device associated with them in the table that is being queried. As we do a COUNT<\/code> of all the devices for a given user, we need to group the results by the user<\/code> column for the query to work.<\/p>\n
USE CASE: Device category distribution across users<\/h3>\n
Let\u2019s keep the theme of users and devices alive for a moment.<\/p>\n
Users<\/strong> and events<\/strong> are the key metrics for App + Web. This is fundamentally different to the session<\/strong>-centric approach of Google Analytics (even though there are reverberations of \u201csessions\u201d in App + Web, too). It\u2019s much closer to a true hit stream<\/em> model than before.<\/p>\n
However, event counts alone tell as nothing without us drilling into what kind of event happened<\/em>.<\/p>\n
In this first tip, we\u2019ll learn to calculate the number of users per device category<\/strong>. As the concept of \u201cUser\u201d is still bound to a unique browser client ID, if the same person visited the website on two different browser instances or devices, they would be counted as two users.<\/p>\n
This is what the query looks like:<\/p>\n
\n
SELECT<\/span>\n  CASE<\/span>\n    WHEN<\/span> device.category = \"desktop\"<\/span> THEN<\/span> \"desktop\"<\/span>\n    WHEN<\/span> device.category = \"tablet\"<\/span> AND<\/span> app_info.id IS<\/span> NULL<\/span> THEN<\/span> \"tablet-web\"<\/span>\n    WHEN<\/span> device.category = \"mobile\"<\/span> AND<\/span> app_info.id IS<\/span> NULL<\/span> THEN<\/span> \"mobile-web\"<\/span>\n    WHEN<\/span> device.category = \"tablet\"<\/span> AND<\/span> app_info.id IS<\/span> NOT<\/span> NULL<\/span> THEN<\/span> \"tablet-app\"<\/span>\n    WHEN<\/span> device.category = \"mobile\"<\/span> AND<\/span> app_info.id IS<\/span> NOT<\/span> NULL<\/span> THEN<\/span> \"mobile-app\"<\/span>\n  END<\/span> AS<\/span> device,\n  COUNT<\/span>(DISTINCT<\/span> user_pseudo_id) AS<\/span> users\nFROM<\/span>\n  `dataset.analytics_accountId.events_2*`\nGROUP<\/span> BY<\/span>\n  1<\/span><\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"Group<\/p>\n
    <\/a><\/p>\n<\/div>\n
The query itself is simple, but it makes use of the two statements covered in this chapter effectively. CASE<\/code> is used to segment \u201cmobile\u201d and \u201ctablet\u201d users further into web and app groups (something you\u2019ll find useful once you start collecting data from both websites and mobile apps), and GROUP BY<\/code> displays the count of unique device IDs per device category.<\/p>\n
Tip #2: DISTINCT and HAVING<\/h2>\n
The next two keywords we\u2019ll cover are HAVING<\/code> and DISTINCT<\/code>. The first is great for filtering results based on aggregated values. The latter is used to deduplicate results to avoid calculating the same result multiple times.<\/p>\n
DISTINCT<\/h3>\n
\n
    <\/p>\n
    \"DISTINCT\"\/<\/p>\n
    <\/a><\/p>\n<\/div>\n
The DISTINCT<\/code> keyword is used to deduplicate results.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n
user<\/th>\ndevice_category<\/th>\nsession_id<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\ndesktop<\/td>\nabc123<\/td>\n<\/tr>\n
12345<\/td>\ndesktop<\/td>\ndef234<\/td>\n<\/tr>\n
12345<\/td>\ntablet<\/td>\nefg345<\/td>\n<\/tr>\n
23456<\/td>\nmobile<\/td>\nfgh456<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  user<\/span>,\n  COUNT<\/span>(DISTINCT<\/span> device_category) AS<\/span> device_category_count\nFROM<\/span>\n  table<\/span>\nGROUP<\/span> BY<\/span>\n  1<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n
user<\/th>\ndevice_category_count<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\n2<\/td>\n<\/tr>\n
23456<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
For example, if the user had three sessions with device categories desktop<\/code>, desktop<\/code>, and tablet<\/code>, then a query for COUNT(DISTINCT device.category)<\/code> would return 2<\/code>, as there are just two instances of distinct<\/em> device categories.<\/p>\n
HAVING<\/h3>\n
\n
    <\/p>\n
    \"HAVING\"\/<\/p>\n
    <\/a><\/p>\n<\/div>\n
The HAVING<\/code> clause can be used at the end of the query, after all calculations have been done, to filter the results. All the rows selected for the query are still processed, even if the HAVING<\/code> statement strips out some of them from the result table.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n
user<\/th>\ndevice_category<\/th>\nsession_id<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\ndesktop<\/td>\nabc123<\/td>\n<\/tr>\n
12345<\/td>\ndesktop<\/td>\ndef234<\/td>\n<\/tr>\n
12345<\/td>\ntablet<\/td>\nefg345<\/td>\n<\/tr>\n
23456<\/td>\nmobile<\/td>\nfgh456<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  user<\/span>,\n  COUNT<\/span>(DISTINCT<\/span> device_category) AS<\/span> device_category_count\nFROM<\/span>\n  table<\/span>\nGROUP<\/span> BY<\/span> \n  1<\/span>\nHAVING<\/span>\n  device_category_count > 1<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n
user<\/th>\ndevice_category_count<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\n2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
It\u2019s similar to WHERE<\/code> (see next chapter), but unlike WHERE<\/code> which is used to filter the actual records that are processed by the query, HAVING<\/code> is used to filter on aggregated values. In the query above, device_category_count<\/code> is an aggregated count of all the distinct device categories found in the data set.<\/p>\n
USE CASE: Distinct device categories per user<\/h3>\n
Since the very name, App + Web<\/strong>, implies cross-device measurement, exploring some ways of grouping and filtering data based on users with more than one device in use seems fruitful.<\/p>\n
The query is similar to the one in the previous chapter, but this time instead of grouping by device category, we\u2019re grouping by user and counting the number of unique device categories each user has visited the site with. We\u2019re filtering the data to only include users with more than one device category in the data set.<\/p>\n
This is an exploratory query. You can then extend it to actually detect different models instead of just using device category. Device category is a fickle dimension to use, as in the example dataset used for this article, many times a device labelled as \u201cApple iPhone\u201d was actually counted as a Desktop device.<\/p>\n
See this article by Craig Sullivan<\/a> to understand how messed up device attribution in web analytics actually is.<\/p>\n
\n
SELECT<\/span>\n  user_pseudo_id,\n  COUNT<\/span>(DISTINCT<\/span> device.category) AS<\/span> used_devices_count,\n  STRING_AGG(DISTINCT<\/span> device.category) AS<\/span> distinct_devices,\n  STRING_AGG(device.category) AS<\/span> devices\nFROM<\/span>\n  `dataset.analytics_accountId.events_2*`\nGROUP<\/span> BY<\/span>\n  1<\/span>\nHAVING<\/span>\n  used_devices_count > 1<\/span><\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"cross-device<\/p>\n
    <\/a><\/p>\n<\/div>\n
As a bonus, you can see how STRING_AGG<\/code> can be used to concatenate multiple values into a single column. This is useful for identifying patterns that emerge across multiple rows of data!<\/p>\n
Tip #3: WHERE<\/h2>\n
WHERE<\/h3>\n
\n
    <\/p>\n
    \"WHERE\"\/<\/p>\n
    <\/a><\/p>\n<\/div>\n
If you want to filter the records against which you\u2019ll run your query, WHERE<\/code> is your best friend. As it filters the records that are processed, it\u2019s also a great way to reduce the (performance and monetary) cost of your queries.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n
user<\/th>\nsession_id<\/th>\nlanding_page_path<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nabc123<\/td>\n\/home\/<\/td>\n<\/tr>\n
12345<\/td>\nbcd234<\/td>\n\/purchase\/<\/td>\n<\/tr>\n
23456<\/td>\ncde345<\/td>\n\/home\/<\/td>\n<\/tr>\n
34567<\/td>\ndef456<\/td>\n\/contact-us\/<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  *\nFROM<\/span>\n  table<\/span>\nWHERE<\/span>\n  user<\/span> = '12345'<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n
user<\/th>\nsession_id<\/th>\nlanding_page_path<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nabc123<\/td>\n\/home\/<\/td>\n<\/tr>\n
12345<\/td>\nbcd234<\/td>\n\/purchase\/<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The WHERE<\/code> clause is used to filter the rows against which the rest of the query is made. It is introduced directly after the FROM<\/code> statement, and it reads as \u201creturn all the rows in the FROM<\/code> table that match the condition of the WHERE<\/code> clause\u201d.<\/p>\n
Do note that WHERE<\/code> can\u2019t be used with aggregate values<\/strong>. So if you\u2019ve done any calculations with the rows returned from the table, you need to use HAVING<\/code> instead.<\/p>\n
Due to this, WHERE<\/code> is less expensive in terms of query processing than HAVING<\/code>.<\/p>\n
USE CASE: Engaged users<\/h3>\n
As mentioned before, App + Web<\/strong> is event-driven. With Firebase Analytics, Google introduced a number of automatically collected<\/a>, pre-defined events to help users gather useful data from their apps and websites without having to flex a single muscle.<\/p>\n
One such event is user_engagement<\/strong>. This is fired when the user has engaged with the website or app for a specified period of time.<\/p>\n
Since this is readily available as a custom event, we can create a simple query that uses the WHERE<\/code> clause to return only those records where the user was engaged.<\/p>\n
The key to WHERE<\/code> is that it\u2019s executed after the FROM<\/code> clause (which specifies the table to be queried). If a row doesn\u2019t match the condition in WHERE<\/code>, it won\u2019t be used to match the rest of the query against.<\/p>\n
\n
SELECT<\/span>\n  event_date,\n  COUNT<\/span>(DISTINCT<\/span> user_pseudo_id) AS<\/span> engaged_users\nFROM<\/span>\n  `dataset.analytics_accountId.events_20190922`\nWHERE<\/span>\n  event_name = \"user_engagement\"<\/span>\nGROUP<\/span> BY<\/span>\n  1<\/span><\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"Engaged<\/p>\n
    <\/a><\/p>\n<\/div>\n
See how we\u2019re including data from just one date? We\u2019re also only including DISTINCT<\/code> users, so if a user was engaged more than once during the day we\u2019re only counting them once.<\/p>\n
Tip #4: SUBQUERIES and ANALYTIC functions<\/h2>\n
A subquery<\/strong> in SQL means a query within a query. They can emerge in multiple different places, such as within SELECT<\/code> clauses, FROM<\/code> clauses, and WHERE<\/code> clauses.<\/p>\n
Analytic<\/strong> functions let you do calculations that cover other<\/em> rows than just the one that is being processed. It\u2019s similar to aggregations such as SUM<\/code> and AVG<\/code> except it doesn\u2019t result in rows getting grouped into a single result. That\u2019s why analytic functions are perfectly suited for things like running totals\/averages<\/em> or, in analytics contexts, determining session boundaries by looking at first and last timestamps, for example.<\/p>\n
SUBQUERY<\/h3>\n
\n
    <\/p>\n
    \"SUBQUERY\"\/<\/p>\n
    <\/a><\/p>\n<\/div>\n
The point of a subquery is to run calculations on table data, and return the result of those calculations to the enclosing query. This lets you logically organize your queries, and it makes it possible to do calculations on calculations, which would not be possible in a single query.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n
user<\/th>\nevent_name<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nsession_start<\/td>\n<\/tr>\n
23456<\/td>\nuser_engagement<\/td>\n<\/tr>\n
34567<\/td>\nuser_engagement<\/td>\n<\/tr>\n
45678<\/td>\nlink_click<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  COUNT<\/span>(DISTINCT<\/span> user<\/span>) AS<\/span> all_users,\n  (\n    SELECT<\/span>\n      COUNT<\/span>(DISTINCT<\/span> user<\/span>)\n    FROM<\/span>\n      table<\/span>\n    WHERE<\/span>\n      event_name = 'user_engagement'<\/span>\n  ) AS<\/span> engaged_users\nFROM<\/span>\n  table<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n
all_users<\/th>\nengaged_users<\/th>\n<\/tr>\n<\/thead>\n
4<\/td>\n2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In this example, the subquery is within the SELECT<\/code> statement, meaning the subquery result is bundled into a single column of the main query.<\/p>\n
Here, the engaged_users<\/code> column retrieves the count of all distinct user IDs from the table, where these users had an event named user_engagement<\/code> collected at any time.<\/p>\n
The main query then combines this with a count of all distinct user IDs without any restrictions, and thus you get both counts in the same table.<\/p>\n
You couldn\u2019t have achieved with just the main query, since the WHERE<\/code> statement applies to all SELECT<\/code> columns in the table. That\u2019s why we needed the subquery – we had to use a WHERE<\/code> statement that only applied to the engaged_users<\/code> column.<\/p>\n
ANALYTIC FUNCTIONS<\/h3>\n
\n
    <\/p>\n
    \"Analytic<\/p>\n
    <\/a><\/p>\n<\/div>\n
Analytic functions can be really difficult to understand, since with SQL you\u2019re used to going over the table row-by-row, and comparing the query against each row one at a time.<\/p>\n
With an analytic function, you stretch this logic a bit. The query still goes over the source table row-by-row, but this time you can reference other rows when doing calculations.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n\n
user<\/th>\nevent_name<\/th>\nevent_timestamp<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nclick<\/td>\n1001<\/td>\n<\/tr>\n
12345<\/td>\nclick<\/td>\n1002<\/td>\n<\/tr>\n
23456<\/td>\nsession_start<\/td>\n1012<\/td>\n<\/tr>\n
23456<\/td>\nuser_engagement<\/td>\n1009<\/td>\n<\/tr>\n
34567<\/td>\nclick<\/td>\n1000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  event_name,\n  user<\/span>,\n  event_timestamp\nFROM<\/span> (\n  SELECT<\/span>\n    user<\/span>,\n    event_name,\n    event_timestamp,\n    RANK() OVER (PARTITION BY<\/span> event_name ORDER<\/span> BY<\/span> event_timestamp) AS<\/span> rank\n  FROM<\/span>\n    table<\/span>\n) \nWHERE<\/span>\n  rank = 1<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n\n
event_name<\/th>\nuser<\/th>\nevent_timestamp<\/th>\n<\/tr>\n<\/thead>\n
click<\/td>\n34567<\/td>\n1000<\/td>\n<\/tr>\n
user_engagement<\/td>\n23456<\/td>\n1009<\/td>\n<\/tr>\n
session_start<\/td>\n23456<\/td>\n1012<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In this query, we take each event and see which user sent the first<\/em> such event in the table.<\/p>\n
For each row in the table, the RANK() OVER (PARTITION BY event_name ORDER BY event_timestamp) AS rank<\/code> is run. The partition<\/em> is basically a \u201creference\u201d table with all the event names matching the current row\u2019s event name, and their respective timestamps.<\/p>\n
These timestamps are then ordered in ascending order (within the partition). The RANK() OVER<\/code> part of the function returns the current event name\u2019s rank<\/em> in this table.<\/p>\n
To walk through an example, let\u2019s say the query engine encounters the first row of the table. The RANK() OVER (PARTITION BY event_name ORDER BY event_timestamp)<\/code> creates the reference table that looks like this for the first row:<\/p>\n\n\n\n\n\n\n\n
user<\/th>\nevent_name<\/th>\nevent_timestamp<\/th>\nrank<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nclick<\/td>\n1000<\/td>\n1<\/td>\n<\/tr>\n
12345<\/td>\nclick<\/td>\n1001<\/td>\n2<\/td>\n<\/tr>\n
12345<\/td>\nclick<\/td>\n1002<\/td>\n3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The query then checks how the current row matches against this partition, and returns 2<\/code> as the first row in the table was rank 2<\/code> of its partition.<\/p>\n
This partition is ephemeral – it\u2019s only<\/strong> used to calculate the result of the analytic function.<\/p>\n
For the purposes of this exercise, this analytic function is furthermore done in a subquery, so that the main query can filter the result using a WHERE<\/code> for just those items that had rank 1<\/code> (first timestamp of any given event).<\/p>\n
I recommend checking out the first paragraphs in this<\/a> document – it explains how the partitioning works.<\/p>\n
USE CASE: First interaction per event<\/h3>\n
Let\u2019s extend the example from above into the App + Web dataset.<\/p>\n
We\u2019ll create a list of client IDs together with the event name, the timestamp, and the timestamp converted to a readable (date) format.<\/p>\n
\n
SELECT<\/span>\n  user_pseudo_id,\n  event_name,\n  event_timestamp,\n  DATETIME(TIMESTAMP_MICROS(event_timestamp), \"Europe\/Helsinki\"<\/span>) AS<\/span> date<\/span>\nFROM<\/span> (\n  SELECT<\/span>\n    user_pseudo_id,\n    event_name,\n    event_timestamp,\n    RANK() OVER (PARTITION BY<\/span> event_name ORDER<\/span> BY<\/span> event_timestamp) AS<\/span> rank\n  FROM<\/span>\n    `dataset.analytics_accountId.events_20190922` )\nWHERE<\/span>\n  rank = 1<\/span>\nORDER<\/span> BY<\/span>\n  event_timestamp<\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"First<\/p>\n
    <\/a><\/p>\n<\/div>\n
The logic is exactly the same as in the introduction to analytic functions above. The only difference is how we use the DATETIME<\/code> and TIMESTAMP_MICROS<\/code> to turn the UNIX timestamp (stored in BigQuery) into a readable date format.<\/p>\n
Don\u2019t worry – analytic functions in particular are a tough concept to understand. Play around with the different analytic functions to get an idea of how they work with the source idea.<\/p>\n
There are quite a few articles and videos online that explain the concept further, so I recommend checking out the web for more information. We will also return to analytic functions many, many times in future #BigQueryTips posts.<\/p>\n
Tip #5: UNNEST and CROSS JOIN<\/h2>\n
The dataset exported by App + Web does not end up in a relational database where we could use the JOIN<\/code> key to quickly pull in extra information about pages, sessions, and users.<\/p>\n
Instead, BigQuery arranges data in nested and repeated fields – that\u2019s how it can have a single row represent all the hits of a session (as in the Google Analytics dataset).<\/p>\n
The problem with this approach is that it\u2019s not too intuitive to access these nested values.<\/p>\n
Enter UNNEST<\/code>, particularly when coupled with CROSS JOIN<\/code>.<\/p>\n
UNNEST<\/code> means that the nested structure is actually expanded so that each item within can be joined with the rest of the columns in the row. This results in the single row becoming multiple rows, where each row corresponds to one value in the nested structure. This column-to-rows is achieved with a CROSS JOIN<\/code>, where every item in the unnested structure is joined with each column in the rest of the table.<\/p>\n
UNNEST and CROSS JOIN<\/h3>\n
\n
    <\/p>\n
    \"UNNEST<\/p>\n
    <\/a><\/p>\n<\/div>\n
The two keywords go intrinsically together, so we\u2019ll treat them as such.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n
timestamp<\/th>\nevent<\/th>\nparams.key<\/th>\nparams.value<\/th>\n<\/tr>\n<\/thead>\n
1000<\/td>\nclick<\/td>\ntime
type
device<\/td>\n		10
right-click
iPhone<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
SELECT<\/span>\n  timestamp<\/span>,\n  event,\n  event_params.key<\/span> AS<\/span> event_params_key\nFROM<\/span>\n  table<\/span> AS<\/span> t\n  CROSS<\/span> JOIN<\/span> UNNEST<\/span> (t.params) AS<\/span> event_params<\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n\n
timestamp<\/th>\nevent<\/th>\nevent_params_key<\/th>\n<\/tr>\n<\/thead>\n
1000<\/td>\nclick<\/td>\ntime<\/td>\n<\/tr>\n
1000<\/td>\nclick<\/td>\ntype<\/td>\n<\/tr>\n
1000<\/td>\nclick<\/td>\ndevice<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
See what happened? The nested structure within params<\/code> was unnested<\/em> so that each item was treated as a separate row in its own column. Then, this unnested structure was cross-joined<\/strong> with the table. A CROSS JOIN<\/code> combines every row from table<\/code> with every row from the unnested structure.<\/p>\n
This is how you end up with a structure that you can then use for calculations in your main query.<\/p>\n
It\u2019s a bit complicated since you always need to do the UNNEST<\/code> – CROSS JOIN<\/code> exercise, but once you\u2019ve done it a few times it should become second nature.<\/p>\n
In fact, there\u2019s a shorthand for writing the CROSS JOIN<\/code> that might make things easier to read (or not). You can replace the CROSS JOIN<\/code> statement with a comma. This is what the example query would look like when thus modified.<\/p>\n
\n
SELECT<\/span>\n  timestamp<\/span>,\n  event,\n  event_params.key<\/span> AS<\/span> event_params_key\nFROM<\/span>\n  table<\/span> AS<\/span> t,\n  UNNEST<\/span> (t.params) AS<\/span> event_params<\/code><\/pre>\n<\/div>\n
USE CASE: Pageviews and Unique Pageviews per user<\/h3>\n
Let\u2019s look at a query for getting the count of pageviews and unique pageviews per user.<\/p>\n
\n
SELECT<\/span>\n  event_date,\n  event_params.value.string_value as<\/span> page_url,\n  COUNT<\/span>(*) AS<\/span> pageviews,\n  COUNT<\/span>(DISTINCT<\/span> user_pseudo_id) AS<\/span> unique_pageviews\nFROM<\/span>\n  `dataset.analytics_accountId.events_20190922` AS<\/span> t,\n  UNNEST<\/span>(t.event_params) AS<\/span> event_params\nWHERE<\/span>\n  event_params.key<\/span> = \"page_location\"<\/span>\n  AND<\/span> event_name = \"page_view\"<\/span>\nGROUP<\/span> BY<\/span>\n  1<\/span>,\n  2<\/span>\nORDER<\/span> BY<\/span>\n  3<\/span> DESC<\/span><\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"Page<\/p>\n
    <\/a><\/p>\n<\/div>\n
In the FROM<\/code> statement we define the table as usual, and we give it an alias t<\/code> (good practice, as it helps keep the queries lean).<\/p>\n
The next step is to UNNEST<\/code> one of those nested fields, and then CROSS JOIN<\/code> it with the rest of our table. The WHERE<\/code> clause is used to make sure only rows that have the page_location<\/code> key and the page_view<\/code> event type are included in the final table.<\/p>\n
Finally, we GROUP BY<\/code>  the event date and page URL, and then we sort everything by the count of pageviews in descending order.<\/p>\n
Conceptually, the UNNEST<\/code> and CROSS JOIN<\/code> create a new table that has a crazy number of rows, as every row in the original table is multiplied by the number of rows in the event_params<\/code> nested structure. The WHERE<\/code> clause is thus your friend in making sure you only analyze the data that answers the question you have.<\/p>\n
Tip #6: WITH\u2026AS and LEFT JOIN<\/h2>\n
The final keywords we\u2019ll go through are WITH...AS<\/code> and LEFT JOIN<\/code>. The first lets you create a type of subquery that you can reference in your other queries as a separate table. The second lets you join two datasets together while preserving rows that do not match against the join condition.<\/p>\n
WITH\u2026AS<\/h3>\n
\n
    <\/p>\n
    \"WITH...AS\"\/<\/p>\n
    <\/a><\/p>\n<\/div>\n
WITH...AS<\/code> is very similar conceptually to a subquery. It allows you to build a table expression that can then be used by your queries. The main difference to a subquery is that the table gets an alias, and you can then use this alias wherever you want to refer to the table\u2019s data. A subquery would need to be repeated in all places where you want to reference it, so WITH<\/code> makes custom queries more accessible.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n\n\n
user<\/th>\nevent_name<\/th>\nevent_timestamp<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nclick<\/td>\n1000<\/td>\n<\/tr>\n
12345<\/td>\nuser_engagement<\/td>\n1005<\/td>\n<\/tr>\n
12345<\/td>\nclick<\/td>\n1006<\/td>\n<\/tr>\n
23456<\/td>\nsession_start<\/td>\n1002<\/td>\n<\/tr>\n
34567<\/td>\nsession_start<\/td>\n1000<\/td>\n<\/tr>\n
34567<\/td>\nclick<\/td>\n1002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
WITH<\/span> users_who_clicked AS<\/span> (\n  SELECT<\/span>\n    DISTINCT<\/span> user<\/span>\n  FROM<\/span>\n    table<\/span>\n  WHERE<\/span> event_name = 'click'<\/span>\n)\nSELECT<\/span>\n  *\nFROM<\/span>\n  users_who_clicked<\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n
It\u2019s not the most mind-blowing of examples, but it should illustrate how to use table aliases. After the WITH...AS<\/code> clause, you can then reference the table in e.g. FROM<\/code> statements and in joins.<\/p>\n
See the next example where we make this more interesting.<\/p>\n
LEFT JOIN<\/h3>\n
\n
    <\/p>\n
    \"LEFT<\/p>\n
    <\/a><\/p>\n<\/div>\n
A LEFT JOIN<\/code> is one of the most useful data joins because it allows you to account for unmatched rows as well.<\/p>\n
A LEFT JOIN<\/code> takes all<\/strong> rows in the first table (\u201cleft\u201d table), and the rows that match<\/strong> a specific join criterion in the second table (\u201cright\u201d table). For all the rows that did not have a match, the first table is populated with a null<\/em> value. Rows in the second<\/em> table that do not have a match in the first table are discarded.<\/p>\n
SOURCE TABLE<\/h4>\n\n\n\n\n\n\n\n\n\n\n
user<\/th>\nevent_name<\/th>\nevent_timestamp<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\nclick<\/td>\n1000<\/td>\n<\/tr>\n
12345<\/td>\nuser_engagement<\/td>\n1005<\/td>\n<\/tr>\n
12345<\/td>\nclick<\/td>\n1006<\/td>\n<\/tr>\n
23456<\/td>\nsession_start<\/td>\n1002<\/td>\n<\/tr>\n
34567<\/td>\nsession_start<\/td>\n1000<\/td>\n<\/tr>\n
34567<\/td>\nclick<\/td>\n1002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL<\/h4>\n
\n
WITH<\/span> users_who_clicked AS<\/span> (\n  SELECT<\/span>\n    DISTINCT<\/span> user<\/span>\n  FROM<\/span>\n    table<\/span>\n  WHERE<\/span> event_name = 'click'<\/span>\n)\nSELECT<\/span>\n  t1.user<\/span>,\n  CASE<\/span>\n    WHEN<\/span> t2.user<\/span> IS<\/span> NOT<\/span> NULL<\/span> THEN<\/span> \"true\"<\/span>\n    ELSE<\/span> \"false\"<\/span>\n  END<\/span> AS<\/span> user_did_click\nFROM<\/span>\n  table<\/span> as<\/span> t1\nLEFT<\/span> JOIN<\/span>\n  users_who_clicked as<\/span> t2\nON<\/span> \n  t1.user<\/span> = t2.user<\/span>\nGROUP<\/span> BY<\/span>\n  1<\/span>, 2<\/span><\/code><\/pre>\n<\/div>\n
QUERY RESULT<\/h4>\n\n\n\n\n\n\n\n
user<\/th>\nuser_did_click<\/th>\n<\/tr>\n<\/thead>\n
12345<\/td>\ntrue<\/td>\n<\/tr>\n
23456<\/td>\nfalse<\/td>\n<\/tr>\n
34567<\/td>\ntrue<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
With the LEFT JOIN<\/code>, we take all the user IDs in the main table, and then we take all the rows in the users_who_clicked<\/code> table (that we created in the WITH...AS<\/code> tutorial above). For all the rows where the user ID from the main table has a match in the users_who_clicked<\/code> table, we populate a new column named user_did_click<\/code> with the value true<\/strong>.<\/p>\n
For all the user IDs that do not have a match in the users_who_clicked<\/code> table, the value false<\/strong> is used instead.<\/p>\n
USE CASE: Simple segmenting<\/h4>\n
Let\u2019s put a bunch of things we\u2019ve now learned together, and replicate some simple segmenting functionality from Google Analytics in BigQuery.<\/p>\n
\n
WITH<\/span>\n  engaged_users_table AS<\/span> (\n  SELECT<\/span>\n    DISTINCT<\/span> event_date,\n    user_pseudo_id\n  FROM<\/span>\n    `dataset.analytics_accountId.events_20190922`\n  WHERE<\/span>\n    EVENT_NAME = \"user_engagement\"<\/span>),\n  pageviews_table AS<\/span> (\n  SELECT<\/span>\n    DISTINCT<\/span> event_date,\n    event_params.value.string_value AS<\/span> page_url,\n    user_pseudo_id\n  FROM<\/span>\n    `simoahava-com.analytics_206575074.events_20190922` AS<\/span> t,\n    UNNEST<\/span>(t.event_params) AS<\/span> event_params\n  WHERE<\/span>\n    event_params.key<\/span> = \"page_location\"<\/span>\n    AND<\/span> event_name = \"page_view\"<\/span>)\nSELECT<\/span>\n  t1.event_date,\n  page_url,\n  COUNTIF(t2.user_pseudo_id IS<\/span> NOT<\/span> NULL<\/span>) AS<\/span> engaged_users_visited,\n  COUNTIF(t2.user_pseudo_id IS<\/span> NULL<\/span>) AS<\/span> not_engaged_users_visited\nFROM<\/span>\n  pageviews_table AS<\/span> t1\nLEFT<\/span> JOIN<\/span>\n  engaged_users_table AS<\/span> t2\nON<\/span>\n  t1.user_pseudo_id = t2.user_pseudo_id\nGROUP<\/span> BY<\/span>\n  1<\/span>,\n  2<\/span>\nORDER<\/span> BY<\/span>\n  3<\/span> DESC<\/span><\/code><\/pre>\n<\/div>\n
\n
    <\/p>\n
    \"With<\/p>\n
    <\/a><\/p>\n<\/div>\n
Let\u2019s chop this down to pieces again.<\/p>\n
In the query above, we have two<\/strong> table aliases created. The first one named engaged_users_table<\/code> should be familiar – it\u2019s the query we built in tip #3<\/a>.<\/p>\n
The second one named pageviews_table<\/code> should ring a bell as well. We built it in tip #5<\/a>.<\/p>\n
We create these as table aliases so that we can use them in the subsequent join.<\/p>\n
Now, let\u2019s look at the rest of the query:<\/p>\n
\n
SELECT<\/span>\n  t1.event_date,\n  page_url,\n  COUNTIF(t2.user_pseudo_id IS<\/span> NOT<\/span> NULL<\/span>) AS<\/span> engaged_users_visited,\n  COUNTIF(t2.user_pseudo_id IS<\/span> NULL<\/span>) AS<\/span> not_engaged_users_visited\nFROM<\/span>\n  pageviews_table AS<\/span> t1\nLEFT<\/span> JOIN<\/span>\n  engaged_users_table AS<\/span> t2\nON<\/span>\n  t1.user_pseudo_id = t2.user_pseudo_id\nGROUP<\/span> BY<\/span>\n  1<\/span>,\n  2<\/span>\nORDER<\/span> BY<\/span>\n  3<\/span> DESC<\/span><\/code><\/pre>\n<\/div>\n
Focus on the part after the FROM<\/code> clause. Here, we take the two tables, and we LEFT JOIN<\/code> them. The pageviews_table<\/code> is the \u201cleft\u201d table, and engaged_users<\/code> is the \u201cright\u201d table.<\/p>\n
LEFT JOIN<\/code> takes all the rows<\/strong> in the \u201cleft\u201d table and the matching rows<\/strong> from the \u201cright\u201d table. The matching criterion is established in the ON<\/code> clause. Thus, a match is made if the user_pseudo_id<\/code> is the same between the two tables.<\/p>\n
To illustrate, this is what a simple LEFT JOIN<\/code> without any calculations or aggregations would look like:<\/p>\n
\n
    <\/p>\n
    \"LEFT<\/p>\n
    <\/a><\/p>\n<\/div>\n
Here you can see that on rows 1<\/strong>, 9<\/strong>, and 10<\/strong> there was no match for the user_pseudo_id<\/code> in the engaged_users<\/code> table. This means that the user who dispatched this pageview was not considered \u201cengaged\u201d in the date range selected for analysis.<\/p>\n
If you\u2019re wondering what COUNTIF<\/code> does, then check out these two statements detail:<\/p>\n
\n
COUNTIF(\n  t2.user_pseudo_id IS<\/span> NOT<\/span> NULL<\/span>\n) AS<\/span> engaged_users_visited,\n\nCOUNTIF(\n  t2.user_pseudo_id IS<\/span> NULL<\/span>\n) AS<\/span> not_engaged_users_visited<\/code><\/pre>\n<\/div>\n
The first one increments the engaged_users_visited<\/code> counter by one for all the rows where the user visited the page AND had a row in the engaged_users<\/code> table.<\/p>\n
The second one increments the not_engaged_users_visited<\/code> counter by one for all the rows where the user visited the page and DID NOT have a row in the engaged_users<\/code> table.<\/p>\n
We can do this because the LEFT JOIN<\/code> leaves the t2.*<\/code> columns null<\/code> in case the user ID from the pageviews_table<\/code> was not found in the engaged_users_table<\/code>.<\/p>\n
SUMMARY<\/h2>\n
Phew!<\/p>\n
That wasn\u2019t a quick foray into BigQuery after all.<\/p>\n
However, if you already possess a rudimentary understanding of SQL (and if you don\u2019t, just take a look at the Mode Analytics<\/a> tutorial), then the most complex things you need to wrap your head around are the ways in which BigQuery leverages its unique columnar format.<\/p>\n
It\u2019s quite different to a relational database, where joins are the most powerful tool in your kit for running analyses against different data structure.<\/p>\n
In BigQuery, you need to understand the nested structures and how to UNNEST<\/code> them<\/a>. This, for many, is a difficult concept to grasp, but once you consider how UNNEST<\/code> and CROSS JOIN<\/code> simply populate the table as if it had been a flat structure all along, it should help you build the queries you want.<\/p>\n
Analytic functions<\/a> are arguably the coolest thing since Arnold Schwarzenegger played Mr. Freeze. They let you do aggregations and calculations without having to build a complicated mess of subqueries across all the rows you want to process. We\u2019ve only explored a very simple use case here, but in subsequent articles we\u2019ll definitely cover these in more detail.<\/p>\n
Finally, huge thanks to Pawel for providing the SQL examples and walkthroughs. Simo contributed some enhancements to the queries, but his contribution is mainly editorial. Thus, direct all criticism and suggestions for improvement to him, and him alone.<\/p>\n
If you\u2019re an analyst working with Google Analytics and thinking about what you should do to improve your skill set, we recommend learning SQL and then going to town on either any one of the copious BigQuery sample datasets out there, or on your very own App + Web export<\/a>.<\/p>\n
Let us know in the comments if questions arose from this guide!<\/p>\n<\/p><\/div>\n