Calculate Shopify Repeat Purchase Rate and Customer LTV from CSV

How to export your Orders CSV from Shopify Admin

1. In your Shopify Admin, go to Orders.
2. Click Export, choose All orders (or filter by date), and select CSV for Excel, Numbers, or other spreadsheet programs.
3. Download orders_export.csv. Drop it above for a first chart, or import it into the xmetric app to build the repeat-purchase view.

Shopify Orders CSV columns used

• Name: the unique order identifier (such as #1001), required to deduplicate multi-item orders.
• Email: customer identifier across orders.
• Created at: order timestamp.
• Financial Status: filter to paid and partially_refunded to exclude canceled or voided orders.
• Subtotal, Total, and Refunded Amount: order-level revenue fields.

Solving Shopify's multi-line-item CSV trap with FIXED LODs

In a raw Shopify orders_export.csv, an order with 3 products creates 3 rows with the same Name (#1001). Shopify prints order-level Subtotal and Total on the first row of the order and leaves them blank on subsequent line-item rows, while repeating Name, Email, and Created at on all 3 rows.

If you run COUNT([Email]) in a spreadsheet, a single 3-item order looks like 3 separate orders, inflating your repeat purchase rate. Using COUNTD([Name]) and { FIXED [Name] } solves this. Test both LODs below in the formula playground.

1. Deduplicated Order Net Revenue (FIXED LOD)

// [Order Net Revenue]
{ FIXED [Name] : MAX([Subtotal]) - MAX(IFNULL([Refunded Amount], 0)) }

2. Customer Lifetime Order Count (FIXED LOD)

// [Customer Total Orders]
{ FIXED [Email] : COUNTD(
    IF [Financial Status] = "paid" OR [Financial Status] = "partially_refunded"
    THEN [Name]
    END
) }

3. Repeat Buyer Rate and Days from 1st to 2nd Order (SQL equivalent)

The equivalent SQL for the customer summary:

WITH orders_deduped AS (
  SELECT
    "Name" AS order_name,
    LOWER(TRIM("Email")) AS customer_email,
    MIN(CAST("Created at" AS TIMESTAMP)) AS order_at,
    MAX(CAST(COALESCE(NULLIF("Subtotal", ''), '0') AS DOUBLE))
      - MAX(CAST(COALESCE(NULLIF("Refunded Amount", ''), '0') AS DOUBLE)) AS net_order_value
  FROM shopify_orders
  WHERE "Financial Status" IN ('paid', 'partially_refunded')
    AND "Email" IS NOT NULL
    AND TRIM("Email") <> ''
  GROUP BY 1, 2
),
ranked_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_email ORDER BY order_at, order_name) AS order_seq,
    MIN(order_at) OVER (PARTITION BY customer_email) AS first_order_at
  FROM orders_deduped
),
customer_summary AS (
  SELECT
    customer_email,
    COUNT(*) AS total_orders,
    SUM(net_order_value) AS lifetime_net_revenue,
    SUM(CASE WHEN DATE_DIFF('day', first_order_at, order_at) <= 90 THEN net_order_value ELSE 0 END) AS ltv_90d,
    MAX(CASE WHEN order_seq = 2 THEN DATE_DIFF('day', first_order_at, order_at) END) AS days_to_second_order
  FROM ranked_orders
  GROUP BY 1
)
SELECT
  COUNT(*) AS unique_customers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE total_orders >= 2) / NULLIF(COUNT(*), 0), 1) AS repeat_customer_rate_pct,
  ROUND(AVG(lifetime_net_revenue), 2) AS avg_realized_ltv,
  ROUND(AVG(ltv_90d), 2) AS avg_90d_ltv,
  ROUND(MEDIAN(days_to_second_order), 0) AS median_days_to_2nd_order
FROM customer_summary;

When to time your replenishment email flow

Look at median_days_to_2nd_order (the median days between a customer's 1st and 2nd order). If 50% of your repeat buyers place their second order by Day 38, trigger your win-back or replenishment campaign at Day 30 to Day 35 rather than guessing a 60-day window.

Automate Shopify LTV checks with AI agents

Every action in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to query a Shopify orders_export.csv and segment LTV by first-order SKU or discount code. Live Postgres and BigQuery connections are also coming.