Overview

This query returns a ranked list of customers based on total revenue within a defined date range. It includes order count, revenue, and date range of their activity in the period — useful for identifying high-value customers and segmenting by activity recency or acquisition status.

SQL Query

select
  customer_id,
  customer_email,
  sum(order_revenue) as order_revenue,
  count(1) as orders,
  min(event_date) as first_order_date_in_period,
  max(event_date) as last_order_date_in_period,
  max(is_new_customer) as is_new_customer_in_period
from
  orders_table o
where
  event_date BETWEEN '2024-01-01' AND '2024-12-31'
group by
  grouping sets ((customer_id, customer_email), ())
order by 
  order_revenue desc
limit 200

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• customer_id
• customer_email
• event_date
• is_new_customer

Measures

• order_revenue

Derived

• orders

Customization Options

This query summarizes customer-level revenue and order data within a specific calendar year. You can customize it in the following ways:

Change the cohort window

• Adjust the event_date filter to report on a different calendar year or rolling period.

Filter by new vs. returning customers

• Add HAVING is_new_customer_in_period = 1 to focus only on customers acquired during the selected period.
• Use HAVING is_new_customer_in_period = 0 to view only returning customers who had purchased before the period.

Add segment filters

• Include filters such as platform = 'Shopify' or region = 'US' to analyze customer behavior by channel or geography.