- GA4 BigQuery For Marketing Analytics
- Posts
- GA4 Data in BigQuery: Count, Sum, Average, Min and Max
GA4 Data in BigQuery: Count, Sum, Average, Min and Max
Learn Aggregating Functions in SQL
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.
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: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: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.Finding Minimum and Maximum: To find the minimum and maximum values for a numeric event parameter, you can use the
MIN
andMAX
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).
Reply