GA4 Data in BigQuery: Count, Sum, Average, Min and Max

Analysts often grapple with vast datasets, and their ability to distill meaningful insights hinges on the tools at their disposal. One such tool that stands out in the SQL arsenal is the aggregate function. In this blog post, we will explore these functions.

Before I explain the syntax of these SQL aggregate functions, let us look at some examples you can use.

1. Counting Events: To count the number of occurrences of a specific event, you can use the COUNT function. For example, to count the number of purchases or orders:

2. Summing Event Values: If your events have numeric values associated with them, you can use the SUM function to calculate the total. For example, to calculate the total revenue from purchase events:

3. Calculating Average: To find the average of a numeric event parameter, you can use the AVG function. For instance, to find the average e-commerce revenue.

4. Finding Minimum and Maximum: To find the minimum and maximum values for a numeric event parameter, you can use the MIN and MAX functions. For example, to find the minimum and maximum eCommerce revenue from purchase events.

Even though I provided examples of GA4 data, you can use these functions on any other data sources/tables.

Now let’s take a look at SQL queries for the above examples.

Counting Events

To count events or any other records, you will use a function called COUNT as follows:

SELECT COUNT(*)
FROM your_project_id.your_dataset_id.events
WHERE event_name = 'purchase';

Explanation

First, you use the COUNT(*) to instruct SQL to count the records.
Next, you specify the name of the table in the FROM statement.
Finally, you provide the filtering conditions to narrow down the results to specific events (or any other conditions you need). In the above example, we are narrowing down the count for all the purchase events.

Summing Event Values

SELECT SUM(ecommerce.purchase_revenue)
FROM your_project_id.your_dataset_id.events 
WHERE event_name = 'purchase';

Explanation

First, you use the SUM() function, with the column name you want to sum, to instruct SQL to sum the values.
Next, you specify the name of the table in the FROM statement.
Finally, you provide the filtering conditions to narrow down the results to specific events (or any other conditions you need).

Calculating Average

SELECT AVG(ecommerce.purchase_revenue)
FROM your_project_id.your_dataset_id.events 
WHERE event_name = 'purchase';

Explanation

First, you use the AVG() function, with the column name you want to find the average for, to instruct SQL to average the values.
Next, you specify the name of the table in the FROM statement.
Finally, you provide the filtering conditions to narrow down the results to specific events (or any other conditions you need).

Finding Minimum Value

SELECT MIN(ecommerce.purchase_revenue)
FROM your_project_id.your_dataset_id.events 
WHERE event_name = 'purchase';

 Explanation

First, you use the MIN() function, with the column name you want to find the minimum value for.
Next, you specify the name of the table in the FROM statement.
Finally, you provide the filtering conditions to narrow down the results to specific events (or any other conditions you need).

Finding Maximum Value

SELECT MIN(ecommerce.purchase_revenue) AS min_revenue, MAX(ecommerce.purchase_revenue) FROM your_project_id.your_dataset_id.events 
WHERE event_name = 'purchase';

Explanation

First, you use the MAX() function, with the column name you want to find the maximum value for.
Next, you specify the name of the table in the FROM statement.
Finally, you provide the filtering conditions to narrow down the results to specific events (or any other conditions you need).

Advanced Sections

Subscribe to Premium to read the rest.