Overview

This query calculates the repeat purchase rate for each month by dividing the number of returning customers (not marked as new) by the total number of customers within that month. It's useful for tracking customer retention and identifying trends in buyer loyalty over time.

SQL Query

SELECT
  formatDateTime(event_date, '%Y-%m') AS month,
  uniqIf(customer_id, not is_new_customer) / uniq(customer_id) AS repeat_purchase_rate
FROM
  orders_table o
WHERE
  event_date BETWEEN @start_date AND @end_date
GROUP BY
  grouping sets ((month), ())

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• event_date
• customer_id
• is_new_customer

Customization Options

This query calculates the monthly repeat purchase rate by dividing the number of returning customers by the total number of customers. You can customize it in the following ways:

Change repeat logic

• Replace not is_new_customer with an explicit count of orders if you'd rather define "repeat" as 2+ orders in a given timeframe.

Use full month names

• Change '%Y-%m' to '%B %Y' in formatDateTime(...) to show full month names like “July 2024”.

Add customer or platform filters

• Add conditions like AND platform = 'Shopify' or AND region = 'US' to segment repeat behavior by platform or geography.