Here’s a breakdown of GA4 User Metrics and their corresponding BigQuery fields:

1-day repeat purchasers, 2–7-day repeat purchasers, 31–90-day repeat purchasers, 8–30-day repeat purchasers

There are no direct BigQuery fields for these metrics; they must be calculated by analyzing purchase event timestamps for each user.

Here is a general approach to calculating these metrics:

Step 1: Identify Purchase Events

First, identify the purchase events in your dataset. This involves filtering your events table for events that represent a purchase. The event name for purchases in GA4 is typically ‘purchase'.

Step 2: Calculate Repeat Purchasers

For each of the specified time intervals, calculate the number of users who made repeat purchases within those intervals. This involves comparing the timestamps of purchase events for each user. Here’s an example approach for calculating 1-day repeat purchasers:

WITH Purchases AS (
  SELECT
    user_pseudo_id,
    event_date,
    MIN(TIMESTAMP_MICROS(event_timestamp)) AS first_purchase_timestamp
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'
  GROUP BY
    user_pseudo_id, event_date
),

RepeatPurchases AS (
  SELECT
    a.user_pseudo_id,
    a.event_date AS first_purchase_date,
    b.event_date AS repeat_purchase_date
  FROM
    Purchases a
  JOIN
    Purchases b
  ON
    a.user_pseudo_id = b.user_pseudo_id
    AND DATE_DIFF(PARSE_DATE('%Y%m%d', b.event_date), PARSE_DATE('%Y%m%d', a.event_date), DAY) = 1
)

SELECT
  COUNT(DISTINCT user_pseudo_id) AS one_day_repeat_purchasers
FROM
  RepeatPurchases

Explanation:

• Purchases CTE: This Common Table Expression (CTE) identifies the first purchase event for each user on each day.
• RepeatPurchases CTE: This CTE finds pairs of purchase events for the same user where the second purchase occurs exactly 1 day after the first purchase.
• The final SELECT statement counts the unique users who made repeat purchases exactly 1 day apart.

Adjustments for other time intervals:

• For 2–7-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 2 and 7 days.
• For 8–30-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 8 and 30 days.
• For 31–90-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 31 and 90 days.

Note:

• Replace your_project.your_dataset.events_* with your actual dataset path.
• Adjust 'start_date' and 'end_date' to your specific analysis period.
• Ensure that the event name for purchases ('purchase') matches how purchases are tracked in your GA4 setup.

30-day paid active users, 7-day paid active users, 90-day paid active users

These metrics count users who made purchases within the last 30, 7, and 90 days, respectively. Calculating these metrics involves filtering purchase events within the specified time frames and counting unique users.

Here is a general approach to calculating each of these metrics:

1. Identify Purchase Events: Filter your dataset for events that represent a purchase. In GA4, the event name for purchases is typically ‘purchase'.
2. Define Time Frames: For each metric, define the time frame relative to the current date (or another reference date) — last 7 days, last 30 days, and last 90 days.
3. Count Unique Users: For each time frame, count the unique user_pseudo_id or user_id (if available) associated with purchase events.

Example SQL Query – 7-Day Paid Active Users

SELECT
  COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_7_days
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'purchase'
  AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Example SQL Query – 30-Day Paid Active Users

SELECT
  COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_30_days
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'purchase'
  AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

Example SQL Query – 90-Day Paid Active Users

SELECT
  COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_90_days
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'purchase'
  AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)

Notes:

• These queries use CURRENT_TIMESTAMP() to define the current date and time, and TIMESTAMP_SUB to calculate the time frames for the last 7, 30, and 90 days.
• If you’re tracking user_id and prefer to use it for identifying unique users, replace user_pseudo_id with user_id in the queries.

My Step-By-Step Blueprint For Querying GA4 data in BigQuery without understanding a single line of SQL.

Query GA4 BigQuery Data via ChatGPT (73 Pages)

Download the FREE ebook

Average daily purchasers, Max daily purchasers, Min daily purchasers

These metrics require aggregating purchase events by day and then calculating average, maximum, and minimum values. 

Here is an example SQL query that demonstrates how to calculate these metrics:

WITH DailyPurchases AS (
  SELECT
    FORMAT_DATE('%Y-%m-%d', TIMESTAMP_MICROS(event_timestamp)) AS purchase_date,
    COUNT(DISTINCT user_pseudo_id) AS daily_purchasers
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'
  GROUP BY
    purchase_date
)

SELECT
  AVG(daily_purchasers) AS average_daily_purchasers,
  MAX(daily_purchasers) AS max_daily_purchasers,
  MIN(daily_purchasers) AS min_daily_purchasers
FROM
  DailyPurchases;

Explanation:

• DailyPurchases CTE: This Common Table Expression (CTE) calculates the number of unique purchasers for each day. It groups the data by the date of the event and counts distinct user_pseudo_id for purchase events.
• Main SELECT Statement: This part of the query calculates the average, maximum, and minimum number of daily purchasers using the CTE results.

Notes:

• Replace your_project.your_dataset.events_* with the actual path to your GA4 BigQuery dataset.
• Adjust 'start_date' and 'end_date' to your specific analysis period.

Average engagement time, Average engagement time per session

Calculating these metrics involves aggregating engagement time (e.g., engagement_time_msec) across events or sessions and then averaging.

The ‘Average Engagement Time’ metric can be calculated by summing all engagement times and dividing by the number of sessions. Here’s how you can structure the query:

SELECT
  SUM(engagement_time_msec) / COUNT(DISTINCT session_id) AS average_engagement_time
FROM
  `your_project.your_dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'

‘Average Engagement Time Per Session’ metric calculates the average engagement time for each session and then averages those across all sessions:

WITH SessionEngagement AS (
  SELECT
    session_id,
    SUM(engagement_time_msec) AS total_engagement_time_per_session
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'
  GROUP BY
    session_id
)

SELECT
  AVG(total_engagement_time_per_session) AS average_engagement_time_per_session
FROM
  SessionEngagement

DAU/MAU, DAU/WAU, PMAU/DAU, PWAU/DAU, WAU/MAU

These metrics involve aggregating unique user counts over daily, weekly, and monthly periods and then computing the ratios of these aggregates.

Here’s how you can approach these calculations using GA4 data in BigQuery:

Step-1: Define active and paying users

Active Users: Users who have initiated at least one session within the specified time frame.

Paying Users: Users who have made at least one purchase within the specified time frame.

Step-2: Calculate unique user counts

For each time frame (daily, weekly, monthly), calculate the count of unique users. You can use the user_pseudo_id to identify unique users.

Example SQL Query – Daily Active Users (DAU)

SELECT
  FORMAT_DATE('%Y%m%d', DATE(TIMESTAMP_MICROS(event_timestamp))) AS day,
  COUNT(DISTINCT user_pseudo_id) AS dau
FROM
  `your_project.your_dataset.events_*`
GROUP BY
  day

Example SQL Query – Weekly Active Users (WAU)

To calculate WAU, you want to count unique users active within a week. You can define a week in various ways, but a common approach is to use ISO week numbers or simply group by the year and week number.

SELECT
  FORMAT_DATE('%G-W%V', DATE(TIMESTAMP_MICROS(event_timestamp))) AS week,
  COUNT(DISTINCT user_pseudo_id) AS wau
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
GROUP BY
  week

This query calculates the WAU for the last 7 days from the current timestamp. Adjust the WHERE clause as needed for your specific reporting requirements.

Example SQL Query – Monthly Active Users (MAU)

For MAU, count unique users active within a month. You can group by the year and month to get this metric.

SELECT
  FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_MICROS(event_timestamp))) AS month,
  COUNT(DISTINCT user_pseudo_id) AS mau
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP()
GROUP BY
  month

This query calculates the MAU for the last 30 days from the current timestamp. You might adjust the WHERE clause to align with calendar months or other specific monthly periods you are analyzing.

Example SQL Query – Paying Users

1. Identify and filter the events that represent purchases. In GA4, purchase events are typically named purchase.
2. Specify the time frames for which you want to count paying users. This could be daily, weekly, monthly, or any custom time period.
3. For each defined time frame, count the unique users who have made purchases. This involves using the COUNT(DISTINCT user_pseudo_id) function in SQL to ensure each user is counted only once per time frame.

SELECT
  FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_MICROS(event_timestamp))) AS month,
  COUNT(DISTINCT user_pseudo_id) AS paying_users
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'purchase'
  AND event_timestamp BETWEEN TIMESTAMP('2024-01-01') AND TIMESTAMP('2024-01-31')
GROUP BY
  month

This query provides the count of unique paying users for January 2024. Adjust the event_timestamp in the WHERE clause to match the specific time frames you are analyzing.

Step-3: Calculate ratios

To calculate the ratios of DAU (Daily Active Users), WAU (Weekly Active Users), MAU (Monthly Active Users), and their respective paying user counterparts (PDAU, PWAU, PMAU) once you have their counts, you can either join these counts from different queries or calculate them within the same query period.

Here’s how you can approach both methods using SQL in BigQuery:

Method 1: Calculating ratios within the same query

If you have a single query that can capture DAU, WAU, MAU, and their paying counterparts over the same period, you can calculate the ratios directly. Here’s an example:

WITH UserCounts AS (
  SELECT
    DATE(event_timestamp) AS date,
    COUNT(DISTINCT IF(event_name = 'session_start', user_pseudo_id, NULL)) AS DAU,
    COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PDAU,
    COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 7, user_pseudo_id, NULL)) AS WAU,
    COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 7 AND event_name = 'purchase', user_pseudo_id, NULL)) AS PWAU,
    COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 30, user_pseudo_id, NULL)) AS MAU,
    COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 30 AND event_name = 'purchase', user_pseudo_id, NULL)) AS PMAU
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    date
)

SELECT
  date,
  DAU,
  WAU,
  MAU,
  PDAU,
  PWAU,
  PMAU,
  DAU / MAU AS DAU_MAU_Ratio,
  DAU / WAU AS DAU_WAU_Ratio,
  PDAU / DAU AS PDAU_DAU_Ratio,
  PWAU / WAU AS PWAU_WAU_Ratio,
  PMAU / MAU AS PMAU_MAU_Ratio
FROM
  UserCounts
ORDER BY
  date DESC

Method 2: Joining counts from different queries

If you calculate DAU, WAU, MAU, and their paying counterparts in separate queries, you can join these results based on the date or another common identifier to calculate the ratios:

WITH Daily AS (
  SELECT
    DATE(event_timestamp) AS date,
    COUNT(DISTINCT user_pseudo_id) AS DAU,
    COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PDAU
  FROM
    `your_project.your_dataset.events_*`
  GROUP BY
    date
),
Weekly AS (
  SELECT
    DATE_TRUNC(DATE(event_timestamp), WEEK(MONDAY)) AS week,
    COUNT(DISTINCT user_pseudo_id) AS WAU,
    COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PWAU
  FROM
    `your_project.your_dataset.events_*`
  GROUP BY
    week
),
Monthly AS (
  SELECT
    DATE_TRUNC(DATE(event_timestamp), MONTH) AS month,
    COUNT(DISTINCT user_pseudo_id) AS MAU,
    COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PMAU
  FROM
    `your_project.your_dataset.events_*`
  GROUP BY
    month
)

SELECT
  d.date,
  d.DAU,
  w.WAU,
  m.MAU,
  d.PDAU,
  w.PWAU,
  m.PMAU,
  d.DAU / m.MAU AS DAU_MAU_Ratio,
  d.DAU / w.WAU AS DAU_WAU_Ratio,
  d.PDAU / d.DAU AS PDAU_DAU_Ratio,
  w.PWAU / w.WAU AS PWAU_WAU_Ratio,
  m.PMAU / m.MAU AS PMAU_MAU_Ratio
FROM
  Daily d
JOIN
  Weekly w ON d.date = w.week
JOIN
  Monthly m ON d.date = m.month
ORDER BY
  d.date DESC

My Step-By-Step Blueprint For Querying GA4 data in BigQuery without understanding a single line of SQL.

Query GA4 BigQuery Data via ChatGPT (73 Pages)

Download the FREE ebook

First time purchasers, First-time purchaser key event, First-time purchasers per new user:

1. To identify first-time purchasers, you need to filter your dataset for events that represent a purchase. 
2. After filtering purchase events, you need to determine which purchases are first-time purchases for each user by sorting purchases by date for each user and identifying the earliest purchase event.
3. Once you have identified first-time purchases, count these events to understand the volume of first-time purchasers within your selected time frame. 
4. Identifying key events for first-time purchasers (such as making a second purchase) involves tracking their subsequent actions after their first purchase.
5. To calculate the average number of first-time purchasers per new user, divide the total number of first-time purchasers by the total number of new users acquired in the same time frame. 

1. First Time Purchasers

To identify first-time purchasers, use a query that selects the earliest purchase date for each customer and filters those whose first purchase falls within a specific time frame:

SELECT
  user_id,
  MIN(purchase_date) AS first_purchase_date
FROM
  purchases
GROUP BY
  user_id
HAVING
  MIN(purchase_date) = 'YYYY-MM-DD'; -- Specify the date of interest

This query groups purchases by ‘user_id’, finds the minimum (earliest) purchase_date for each user, and filters to include only those whose first purchase date matches a specific date.

2. First-time Purchaser Key Event

The SQL query below uses a Common Table Expression (CTE) to identify each user’s first purchase date. It then joins this CTE with the original purchases table to filter for those first purchases that match a specified key event type.

WITH FirstPurchases AS (
  SELECT
    user_id,
    MIN(purchase_date) AS first_purchase_date
  FROM
    purchases
  GROUP BY
    user_id
)
SELECT
  p.user_id,
  p.purchase_date,
  p.event_type
FROM
  purchases p
JOIN
  FirstPurchases fp ON p.user_id = fp.user_id AND p.purchase_date = fp.first_purchase_date
WHERE
  p.event_type = 'KeyEventType'; -- Specify the key event type

3. First-time Purchasers Per New User

To calculate the ratio of first-time purchasers to new users (assuming new users are identified by a first_visit_date in a users table):

WITH FirstPurchases AS (
  SELECT
    user_id,
    MIN(purchase_date) AS first_purchase_date
  FROM
    purchases
  GROUP BY
    user_id
),
NewUsers AS (
  SELECT
    user_id,
    first_visit_date
  FROM
    users
  WHERE
    first_visit_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' -- Specify the date range of interest
)
SELECT
  COUNT(DISTINCT fp.user_id) * 1.0 / COUNT(DISTINCT nu.user_id) AS first_time_purchasers_per_new_user
FROM
  NewUsers nu
LEFT JOIN
  FirstPurchases fp ON nu.user_id = fp.user_id;

This query calculates the number of first-time purchasers within a specified date range of new users and divides it by the total number of new users in the same period to get the ratio of first-time purchasers per new user.

Returning users

The SQL query below counts the number of sessions per user and identifies those with more than one session, classifying them as returning users.

WITH SessionCounts AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT session_id) AS num_sessions
  FROM
    `your_project.your_dataset.events_*`
  GROUP BY
    user_pseudo_id
)

SELECT
  user_pseudo_id
FROM
  SessionCounts
WHERE
  num_sessions > 1

Note: The query does not specify a time frame, but you can add a WHERE clause in the CTE to analyze returning users within a specific period, such as WHERE event_date BETWEEN ‘YYYYMMDD’ AND ‘YYYYMMDD’.

User key event rate, User engagement

The ‘User Key Event Rate’ metric calculates the proportion of users who performed specific key events compared to the total user base within a given time frame.

SQL Query for User Key Event Rate

WITH KeyEvents AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT event_name) AS key_event_count
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    event_name IN ('purchase', 'add_to_cart', 'sign_up') -- Define key events here
  GROUP BY
    user_pseudo_id
),
TotalUsers AS (
  SELECT
    COUNT(DISTINCT user_pseudo_id) AS total_users
  FROM
    `your_project.your_dataset.events_*`
)

SELECT
  (SUM(key_event_count) / (SELECT total_users FROM TotalUsers)) * 100 AS user_key_event_rate
FROM
  KeyEvents

Explanation:

• KeyEvents CTE: This part of the query identifies users who performed any of the specified key events and counts these events for each user.
• TotalUsers CTE: Calculates the total number of unique users.
• Final SELECT: Calculates the percentage of key events performed by users relative to the total user base.

User Engagement metric measures the total engagement time users spend with your website or app, typically focusing on active engagement like page views or app interactions.

SQL Query for User Engagement

SELECT
  user_pseudo_id,
  SUM(engagement_time_msec) AS total_engagement_time
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'user_engagement' -- Assuming 'user_engagement' captures engagement time
GROUP BY
  user_pseudo_id

This query sums up the engagement_time_msec for the user_engagement event for each user, providing a total engagement time per user.