xmetricRecipes

Recipe Tableau and SQL

How many of this month's customers are new, and how many came back?

Find each customer's first order date with { FIXED [customer_id] : MIN([order_date]) }, then compare every order's month to it: same month is new, a later month is returning. In this plant shop, new customers outnumber returning ones in every month of 2025, yet repeat orders from the one customer in four who came back make 53% of the year's revenue.

Updated

The data

Fern & Fig online plant shop, orders in 2025

Synthetic dataorders.csv

A small online plant shop that grew its ad spend through 2025. Most buyers order one starter plant and vanish; about one in four comes back for pots, soil and bigger plants.

order_idorder_datecustomer_idrevenue
FF-500032025-01-01C-104639.78
FF-500042025-01-01C-104744.17
FF-500062025-01-02C-102842.99
FF-500082025-01-02C-106327.29

Step 1

Find each customer's first order

One date per customer, computed over their whole history. Counting customers by the month of that date gives new customers per month: the shop's ads bring a few more every month.

Tableau

{ FIXED [customer_id] : MIN([order_date]) }

SQL

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_order
  FROM input
  GROUP BY customer_id
)
SELECT
  date_trunc('month', first_order) AS month,
  COUNT(*) AS new_customers
FROM firsts
GROUP BY 1
ORDER BY 1

Rows

MonthNew customers
Jan 202568
Feb 202580
Mar 202583
Apr 202584
May 202591
Jun 202588
Jul 202593
Aug 2025103
Sep 2025109
Oct 2025119
Nov 2025115
Dec 2025125

Step 2

Tag every month's customers new or returning

[First order] is the FIXED field from step 1. Put customer_type on Color and COUNTD([customer_id]) on Rows. Each customer is counted once per month, so the two parts add up to that month's customers. New customers are the bigger part in every month.

Tableau

IF DATETRUNC('month', [order_date]) = DATETRUNC('month', [First order]) THEN "New"
ELSE "Returning"
END

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  month,
  CASE
    WHEN month = first_month THEN 'New'
    ELSE 'Returning'
    END AS customer_type,
  COUNT(DISTINCT customer_id) AS customers
FROM t
GROUP BY 1, 2
ORDER BY 1, 2

Rows

MonthCustomer typeCustomers
Jan 2025New68
Feb 2025New80
Feb 2025Returning6
Mar 2025New83
Mar 2025Returning17
Apr 2025New84
Apr 2025Returning20
May 2025New91
May 2025Returning52
Jun 2025New88
Jun 2025Returning42
Jul 2025New93
Jul 2025Returning64
Aug 2025New103
Aug 2025Returning82
Sep 2025New109
Sep 2025Returning82
Oct 2025New119
Oct 2025Returning97
Nov 2025New115
Nov 2025Returning105
Dec 2025New125
Dec 2025Returning115

New customers win every month. Do they win the revenue?

No. Count heads and new customers are the bigger group in all twelve months: in December, 125 new against 115 returning. Count money and the picture flips. The 296 customers who came back, one in four, made 53% of the year's revenue through their repeat orders, and 65% of December's.

Step 3

Now split the revenue the same way

Same split, different measure, opposite picture. A first order is one small starter plant; a returning customer buys pots, soil and bigger plants. From July on, the smaller group brings in more money every month.

Tableau

SUM([revenue])
// Color: the New / Returning field from step 2

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  month,
  CASE
    WHEN month = first_month THEN 'New'
    ELSE 'Returning'
    END AS customer_type,
  ROUND(SUM(revenue), 2) AS revenue
FROM t
GROUP BY 1, 2
ORDER BY 1, 2

Rows

MonthCustomer typeRevenue
Jan 2025New$2,172
Feb 2025New$2,545
Feb 2025Returning$372
Mar 2025New$2,644
Mar 2025Returning$1,046
Apr 2025New$2,607
Apr 2025Returning$1,160
May 2025New$2,991
May 2025Returning$3,110
Jun 2025New$3,054
Jun 2025Returning$2,455
Jul 2025New$2,963
Jul 2025Returning$3,909
Aug 2025New$3,263
Aug 2025Returning$4,548
Sep 2025New$3,601
Sep 2025Returning$5,133
Oct 2025New$3,732
Oct 2025Returning$6,203
Nov 2025New$3,725
Nov 2025Returning$6,432
Dec 2025New$3,972
Dec 2025Returning$7,368

Step 4

Compare the share of heads with the share of money

Two lines tell the story. Returning customers stay under half of the heads all year, but their share of revenue crosses the 50% line in May and stays above it from July on.

Tableau

COUNTD(IF [Customer type] = "Returning" THEN [customer_id] END) / COUNTD([customer_id])
// and SUM(IF [Customer type] = "Returning" THEN [revenue] END) / SUM([revenue])

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  month,
  COUNT(DISTINCT customer_id) FILTER (WHERE month > first_month)
    * 1.0
    / COUNT(DISTINCT customer_id) AS returning_share_of_customers,
  COALESCE(SUM(revenue) FILTER (WHERE month > first_month), 0)
    / SUM(revenue) AS returning_share_of_revenue
FROM t
GROUP BY 1
ORDER BY 1

Rows

MonthReturning share of customersRevenue
Jan 20250%0%
Feb 20257%12.7%
Mar 202517%28.4%
Apr 202519.2%30.8%
May 202536.4%51%
Jun 202532.3%44.6%
Jul 202540.8%56.9%
Aug 202544.3%58.2%
Sep 202542.9%58.8%
Oct 202544.9%62.4%
Nov 202547.7%63.3%
Dec 202547.9%65%

A dashboard that only shows customer counts tells this shop to keep buying ads. The revenue split says the real engine is the second order, and that the ads are worth what they lead to, not what the first basket pays.

How do I filter dates without making old customers look new?

Find the first order over all of a customer's history, then filter. Move the slider to choose the first month shown.

112

Step 5

Filter the months without breaking the split

The first-order date is found over the whole year, then the months are filtered. Tableau's FIXED does this for you because it runs before dimension filters; in SQL, the window sits in the CTE and the WHERE comes after it.

Tableau

{ FIXED [customer_id] : MIN([order_date]) }
// Filters: MONTH([order_date]) >= {knob.from_month}, not added to context

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  month,
  CASE
    WHEN month = first_month THEN 'New'
    ELSE 'Returning'
    END AS customer_type,
  COUNT(DISTINCT customer_id) AS customers
FROM t
WHERE month(order_date) >= 7
GROUP BY 1, 2
ORDER BY 1, 2

Rows

MonthCustomer typeCustomers
Jul 2025New93
Jul 2025Returning64
Aug 2025New103
Aug 2025Returning82
Sep 2025New109
Sep 2025Returning82
Oct 2025New119
Oct 2025Returning97
Nov 2025New115
Nov 2025Returning105
Dec 2025New125
Dec 2025Returning115

Common mistake

Finding the first order after the date filter

If the first order is computed on filtered rows, every customer's first order in the range looks like their first order ever. Filter to July onwards and July shows 157 new customers instead of the real 93: every returning customer is relabelled new. Compute the first date over all rows, then filter.

WrongStarts with 2025-07-01 00:00:00, 2025-08-01 00:00:00, 2025-09-01 00:00:00…
Point at a bar to read it.
0501001502025-07-01 00:00:002025-08-01 00:00:002025-09-01 00:00:002025-10-01 00:00:002025-11-01 00:00:002025-12-01 00:00:00

Tableau

MIN([order_date])
// with customer_id in the view, or a FIXED expression whose date filter was added to context

SQL

WITH f AS (
  SELECT *
  FROM input
  WHERE month(order_date) >= 7
), t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM f
)
SELECT
  month,
  COUNT(DISTINCT customer_id) FILTER (WHERE month = first_month) AS new_customers
FROM t
GROUP BY 1
ORDER BY 1
RightStarts with 2025-07-01 00:00:00, 2025-08-01 00:00:00, 2025-09-01 00:00:00…
Point at a bar to read it.
0501002025-07-01 00:00:002025-08-01 00:00:002025-09-01 00:00:002025-10-01 00:00:002025-11-01 00:00:002025-12-01 00:00:00

Tableau

{ FIXED [customer_id] : MIN([order_date]) }
// date filter stays an ordinary filter

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  month,
  COUNT(DISTINCT customer_id) FILTER (WHERE month = first_month) AS new_customers
FROM t
WHERE month(order_date) >= 7
GROUP BY 1
ORDER BY 1

How many returning customers did we have this year?

Count distinct customers over the year, not the sum of the monthly column.

Common mistake

Adding up the monthly returning counts

A loyal customer comes back in several months and is counted in each. Summing the monthly column gives 682 'returning customers' for 2025; only 296 different customers ever came back. Count distinct customers over the year instead.

Wrong
Point at a bar to read it.
682Returning customers

Tableau

WINDOW_SUM(COUNTD(IF [Customer type] = "Returning" THEN [customer_id] END))
// across the months

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
), m AS (
  SELECT
    month,
    COUNT(DISTINCT customer_id) FILTER (WHERE month > first_month) AS returning_count
  FROM t
  GROUP BY month
)
SELECT SUM(returning_count) AS returning_customers
FROM m
Right
Point at a bar to read it.
296Returning customers

Tableau

COUNTD(IF [Customer type] = "Returning" THEN [customer_id] END)
// with month off the view

SQL

WITH t AS (
  SELECT
    *,
    date_trunc('month', order_date) AS month,
    date_trunc('month', MIN(order_date) OVER (PARTITION BY customer_id)) AS first_month
  FROM input
)
SELECT
  COUNT(DISTINCT customer_id) FILTER (WHERE month > first_month) AS returning_customers
FROM t

Run it on your own orders

Any orders table with a customer id, an order date and an amount works. Drop your CSV and pick the three columns; the steps above recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, orders.csv, shows the shape.

Questions

Should a second order in the same month count as returning?

Pick one rule and keep it. This page works by month: a customer is new for their whole first month, so each customer lands in exactly one bucket per month. Here 299 customers ordered at least twice, but 3 of them only within their first month, so they never show as returning.

Why does my new customer count jump when I filter dates?

The first-order date was computed after the filter, so the first order in the filtered range looks like a first order ever. Use a FIXED expression (it runs before ordinary dimension filters) and don't add the date filter to context.

How do I get the repeat purchase rate?

Customers with two or more orders divided by all customers: COUNTD(IF { FIXED [customer_id] : COUNTD([order_id]) } >= 2 THEN [customer_id] END) / COUNTD([customer_id]).

My export only starts in January. Is that a problem?

Yes. Everyone's first order in the file looks like their first order ever, so January is all new. Export the full history, or join a customer created-date column and use it instead of MIN([order_date]).

Sources