Make Google Analytics more useful with BigQuery

For GA360 clients, Google automatically sends Google Analytics data to BigQuery after a quick setup is done in Google Analytics and Console. Data is stored each day in a separate table coming from a listed Google Analytics view.

But how do you make your Google Analytics account more useful with BigQuery? The answer: get to know how to query Google Analytics data in BigQuery to answer basic business questions.

Here are 10 basic Google Analytics queries every marketer should know and how to use them.

1. Sum of total hits

Number of hits in a day

2. Count the number of sessions

Number of sessions grouped by medium

3. Concatenating visitor ID and visit ID

For a complete unique ID in BigQuery you should use fullVisitorId and VisitId. Underscore and a blank area both work between characters. For using CONCAT function you will have to convert visitID field from numeric to string.

4. Ordering by fields

To sort by a specific field use the ORDER BY clause and place it after FROM, WHERE or GROUP BY. If the order type is not specified as ‘ASC’ for ascending or ‘DESC’ for descending after the field name in the ORDER BY clause then the field will be sorted in ascending order by default.

5.  Landing page

To get to landing page data of the users in the WHERE clause set the hits.hitnumber = 1.

6. Number of sessions with transactions

Set the totals.transactions field to greater than 0. Totals.transactions fields passes the total number of ecommerce transaction number in a session. Therefore, totals.transactions field will be greater than 0 for all the valid transactions.

7. Hits type by session ID

If you want to understand how interactions or hit types are impacting the user behavior use the following query. Different types of hits are “PAGE,” “TRANSACTION,” “ITEM,” “EVENT,” “SOCIAL,” “APPVIEW,” “EXCEPTION.” You can add other fields to the select statement to better understand the user behavior.

8. Bounce rate by date and hour

Calculate the bounce rate percentage by date and hour. To round of the bounce rate, use the ROUND function.

9. Selecting tables based on date range

Google sends GA data to BigQuery in a daily date table format. Query multiple tables together using table decorators. In the example below we are querying data for a month by specifying the timestamp in the TABLE_DATE_RANGE function.

Remember to always format dates in YYYY-MM-DD format in BigQuery.

10. Flattening of repeated fields

To query multiple repeated fields, you can use the FLATTEN function.

By using these 10 queries you can answer day-to-day business questions and use BigQuery more effectively. To learn more about BigQuery and how it can help improve your brand’s analytics, contact us and speak to one of our BigQuery certified experts.