Overview of the main Google BigQuery features — practice writing requests for marketing analysis

The more information a business accumulates, the more acute the question of where to store it. If you don’t have the ability or desire to maintain your own servers, Google BigQuery (GBQ) can help. BigQuery provides fast, cost-effective, and scalable storage for working with big data, and it allows you to write queries using SQL-like syntax as well as standard and user-defined functions.

In this article, we look at the main functions of BigQuery and show their possibilities using specific examples. You’ll learn how to write basic queries and test them on demo data.

Structured Query Language (SQL) allows you to retrieve data from, add data to, and modify data in large arrays. Google BigQuery supports two SQL dialects: Standard SQL and the outdated Legacy SQL.

Which dialect to choose depends on your preferences, but Google recommends using Standard SQL for these benefits:

• Flexibility and functionality for nested and repeating fields
• Support for the DML and DDL languages, allowing you to change data in tables as well as manage tables and views in GBQ
• Faster processing of large amounts of data compared to Legacy SQL
• Support for all future BigQuery updates

You can learn more about the dialect differences in the BigQuery documentation.

By default, Google BigQuery queries run on Legacy SQL.

You can switch to Standard SQL in several ways:

1. In the BigQuery interface, in the query editing window, select Show Options and remove the checkmark next to Use Legacy SQL:

2. Before querying, add the line #standardSQL and start your query with a new line:

So you can practice and run queries with us, we’ve prepared a table with demo data. Fill out the form below and we’ll email it to you.

To get started, download your demo data table and upload it to your Google BigQuery project. The easiest way to do this is with the OWOX BI BigQuery Reports add-on.

1. Open Google Sheets and install the OWOX BI BigQuery Reports add-on.
2. Open the table you downloaded that contains demo data and select OWOX BI BigQuery Reports –> Upload Data to BigQuery:

3. In the window that opens, choose your Google BigQuery project, a data set, and think up a name for the table in which the loaded data will be stored.
4. Specify a format for the loaded data (as shown in the screenshot):

If you don’t have a project in Google BigQuery, create one. To do this, you’ll need an active billing account in the Google Cloud Platform. Don’t let it scare you that you need to link a bank card: you won’t be charged anything without your knowledge. In addition, when you register, you’ll receive $300 for 12 months that you can spend on data storage and processing.

Before talking about Google BigQuery features, let’s remember what basic queries look like in both the Legacy SQL and Standard SQL dialects:

When building queries, you’ll most frequently use aggregate , date, string, and window functions. Let’s take a closer look at each of these groups of functions.

Aggregate functions provide summary values for an entire table. For example, you can use them to calculate the average check size or total revenue per month, or you can use them to select the segment of users who made the maximum number of purchases.

These are the most popular aggregate functions:

For a list of all aggregate functions, see the Legacy SQL and Standard SQL documentation.

Let’s look at the demo data to see how these functions work. We can calculate the average revenue for transactions, purchases for the highest and lowest amounts, total revenue, total transactions, and the number of unique transactions (to check if purchases were duplicated). To do this, we’ll write a query in which we specify the name of our Google BigQuery project, the dataset, and the table.

#legacy SQL

#standard SQL

As a result, we’ll get the following:

You can check the results of these calculations in the original table with demo data using standard Google Sheets functions (SUM, AVG, and others) or using pivot tables.

As you can see from the screenshot above, the number of transactions and unique transactions is different. This suggests there are two transactions in our table with the same transactionId:

If you’re interested in unique transactions, use a function that counts unique strings. Alternatively, you can group data using the GROUP BY function to get rid of duplicates before applying the aggregate function.

These functions allow you to process dates: change their format, select the necessary field (day, month, or year), or shift the date by a certain interval.

They may be useful when:

• converting dates and times from different sources to a single format to set up advanced analytics
• creating automatically updated reports or trigger mailings (for example, when you need data for the last two hours, week, or month)
• creating cohort reports in which it’s necessary to obtain data for a period of days, weeks, or months

These are the most commonly used date functions:

For a list of all date functions, see the Legacy SQL and Standard SQL documentation.

Let’s take a look at our demo data to see how each of these functions works. For example, we’ll get the current date, turn the date from the original table into the format % YYYY -% MM-% DD, take it away, and add one day to it. Then we’ll calculate the difference between the current date and the date from the source table and break the current date into separate year, month, and day fields. To do this, you can copy the sample queries below and replace the project name, dataset, and data table with your own.

#legacy SQL

#standard SQL

After running the query, you’ll receive this report:

String functions allow you to generate a string, select and replace substrings, and calculate the length of a string and the index sequence of the substring in the original string. For example, with string functions, you can:

• filter a report with UTM tags that are passed to the page URL
• bring data into a single format if the source and campaign names are written in different registers
• replace incorrect data in a report (for example, if the campaign name is misprinted)

These are the most popular functions for working with strings:

You can learn more about all string functions in the Legacy SQL and Standard SQL documentation.

Let’s look at demo data to see how to use the described functions. Suppose we have three separate columns that contain day, month, and year values:

Working with a date in this format isn’t very convenient, so we can combine the values into one column. To do this, use the SQL queries below and remember to substitute the name of your project, dataset, and table in Google BigQuery.

#legacy SQL

#standard SQL

After running the query, we receive the date in one column:

Often, when you download a page on a website, the URL records the values ​of the variables the user has chosen. This can be a payment or delivery method, transaction number, index of the physical store in which the buyer wants to pick up the item, etc. Using a SQL query, you can select these parameters from the page address. Consider two examples of how and why you might do this.

Example 1. Suppose we want to know the number of purchases in which users pick up goods from physical stores. To do this, we need to calculate the number of transactions sent from pages in the URL that contain a substring shop_id (an index for a physical store). We can do this with the following queries:

#legacy SQL

#standard SQL

From the resulting table, we see that 5502 transactions (check = true) were sent from pages containing shop_id:

Example 2. You’ve assigned a delivery_id to each delivery method, and you specify the value of this parameter in the page URL. To find out which delivery method the user has chosen, you need to select the delivery_id in a separate column.

We can use the following queries for this:

#legacy SQL

#standard SQL

As a result, we get a table like this in Google BigQuery:

These functions are similar to the aggregate functions we discussed above. The main difference is that window functions don’t carry out calculations on the entire set of data selected using the query but only on part of that data — a subset or window.

Using window functions, you can aggregate data in a group section without using the JOIN function to combine multiple queries. For example, you can calculate the average revenue per ad campaign or the number of transactions per device. By adding another field to the report, you can easily find out, for example, the share of revenue from an ad campaign on Black Friday or the share of transactions made from a mobile application.

Together with each function in the query, you must spell out the OVER expression that defines the window boundaries. OVER contains three components that you can work with:

• PARTITION BY — Defines the characteristic by which you divide the original data into subsets, such as clientId or DayTime
• ORDER BY — Defines the order of rows in a subset, such as hour DESC
• WINDOW FRAME — Allows you to process rows within a subset of a specific feature (for example, only the five rows before the current row)

In this table, we’ve collected the most frequently used window functions:

You can see a list of all aggregate analytic functions and navigation functions in the documentation for Legacy SQL and Standard SQL.

Example 1. Let’s say we want to analyze the activity of customers during working and non-working hours. To do this, we need to divide transactions into two groups and calculate the metrics of interest:

• Group 1 — Purchases during working hours from 9:00 to 18:00
• Group 2 — Purchases after hours from 00:00 to 9:00 and from 18:00 to 23:59

In addition to working and non-working hours, another variable for forming a window is clientId. That is, for each user, we’ll have two windows:

Let’s use demo data to calculate the average, maximum, minimum, and total revenue, total number of transactions, and number of unique transactions per user during working and non-working hours. The requests below will help us do this.

#legacy SQL

#standard SQL

Let’s see what happens as a result using the example of the user with clientId 102041117.1428132012. In the original table for this user, we have the following data:

By running the query, we receive a report that contains the average, minimum, maximum, and total revenue from this user as well as the user’s total number of transactions. As you can see in the screenshot below, both transactions were made by the user during working hours:

Example 2. Now for a more complicated task:

• Put sequence numbers for all transactions in the window depending on the time of their execution. Recall that we define the window by user and working/non-working time slots.
• Report the revenue of the next/previous transaction (relative to the current) within the window.
• Display the revenue of the first and last transactions in the window.

To do this, we’ll use the following queries:

#legacy SQL

#standard SQL

We can check the results of the calculations using the example of a user we already know: clientId 102041117.1428132012:

From the screenshot above, we can see that:

• the first transaction was at 15:00 and the second transaction was at 16:00
• after the transaction at 15:00, there was a transaction at 16:00 with revenue of 25066 (column lead_revenue)
• before the transaction at 16:00, there was a transaction at 15:00 with revenue of 3699 (column lag_revenue)
• the first transaction within the window was at 15:00, and the revenue for this transaction was 3699 (column first_revenue_by_hour)
• the query processes the data line by line, so for the transaction in question, the last transaction in the window will be itself and the values in the columns last_revenue_by_hour and revenue will be the same

Helpful articles about Google BigQuery:

• Top 6 BigQuery Visualization Tools
• How to Upload Data to Google BigQuery
• How to Upload Raw Data from Google Ads to Google BigQuery
• Google BigQuery ↔ Google Sheets Connector
• Automate Reports in Google Sheets Using Data from Google BigQuery
• Automate reports in Google Data Studio Based on Data from Google BigQuery

If you want to collect unsampled data from your website in Google BigQuery but don’t know where to start, book a demo. We’ll tell you about all the possibilities you get with BigQuery and OWOX BI.

In this article, we’ve looked at the most popular groups of functions: aggregate, date, string, and window. However, Google BigQuery has many more useful functions, including:

• casting functions that allow you to convert data to a specific format
• table wildcard functions that allow you to access multiple tables in a dataset
• regular expression functions that allow you to describe the model of a search query and not its exact value

We’ll definitely write about these functions on our blog. In the meantime, you can try out all the functions described in this article using our demo data.