Build a Shopify Customer Cohort Retention Table from an Orders CSV

How to export order history from Shopify for cohort retention

1. Open your Shopify Admin and click Orders.
2. Click Export, select All orders (so each buyer's true first order is included in the file), and choose 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 cohort heatmap.

Shopify CSV columns used

• Name: unique order number (#1001), used to collapse multiple line items into one order header.
• Email: unique customer key across repeat orders.
• Created at: order creation timestamp.
• Subtotal and Refunded Amount: used to calculate net revenue per order.
• Discount Code and Lineitem sku: optional cohort slice dimensions.

Tableau LOD formulas and SQL for Shopify cohorts

You can test each formula below in the formula playground before building the view.

1. Order-Level Deduplication (FIXED LOD)

Because orders_export.csv repeats Email and Created at on every line item in a multi-item basket, first propagate order net sales at the order Name level:

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

2. Customer Acquisition Month (FIXED LOD)

// [First Order Month]
{ FIXED [Email] : MIN(DATETRUNC('month', [Created at])) }

3. Months Since First Order & Cumulative LTV per Cohort Buyer

Ecommerce cohorts differ from SaaS subscription cohorts: a customer might buy in Month 0, skip Months 1 and 2, and buy again in Month 3. Tracking Cumulative Net Revenue per Cohort Buyer alongside Active Buyer % gives the clearest picture of payback:

// [Cohort Age Months]
DATEDIFF('month', [First Order Month], DATETRUNC('month', [Created at]))

In the xmetric app, put [First Order Month] on Rows, [Cohort Age Months] on Columns, and the retention measure on Color to get the heatmap.

Equivalent SQL

WITH orders_clean AS (
  SELECT
    "Name" AS order_name,
    LOWER(TRIM("Email")) AS customer_email,
    DATE_TRUNC('month', MIN(CAST("Created at" AS TIMESTAMP))) AS order_month,
    MAX(CAST(COALESCE(NULLIF("Subtotal", ''), '0') AS DOUBLE))
      - MAX(CAST(COALESCE(NULLIF("Refunded Amount", ''), '0') AS DOUBLE)) AS net_sales,
    MAX("Discount Code") AS first_discount_code
  FROM shopify_orders
  WHERE "Email" IS NOT NULL AND TRIM("Email") <> ''
  GROUP BY 1, 2
),
cohorts AS (
  SELECT
    customer_email,
    MIN(order_month) AS cohort_month
  FROM orders_clean
  GROUP BY 1
),
cohort_base AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT customer_email) AS cohort_buyers,
    SUM(net_sales) AS m0_net_sales
  FROM orders_clean o
  JOIN cohorts c USING (customer_email)
  WHERE o.order_month = c.cohort_month
  GROUP BY 1
)
SELECT
  c.cohort_month,
  DATE_DIFF('month', c.cohort_month, o.order_month) AS months_since_first_order,
  COUNT(DISTINCT o.customer_email) AS returning_buyers,
  ROUND(100.0 * COUNT(DISTINCT o.customer_email) / b.cohort_buyers, 1) AS buyer_retention_pct,
  ROUND(SUM(o.net_sales) / b.cohort_buyers, 2) AS net_sales_per_initial_buyer
FROM orders_clean o
JOIN cohorts c USING (customer_email)
JOIN cohort_base b ON c.cohort_month = b.cohort_month
GROUP BY 1, 2, b.cohort_buyers
ORDER BY 1, 2;

Traps when building Shopify cohorts from CSV exports

• Exporting only the last 6 months of orders: If you export orders from July to December, any customer who placed their true 1st order in January and a 2nd order in August will falsely appear as a "new August cohort buyer". Export All orders (or at least 24 months of history) so first-order dates are accurate.
• Black Friday / Cyber Monday (November) cohort distortion: November cohorts often have the highest Month 0 volume and the lowest Month 1 to Month 6 retention because gift buyers and deep-discount shoppers churn at higher rates. In the xmetric app, drag a [First Order Discount Flag] calculated field onto Filters to separate promo hunters from full-price buyers.

Slice Shopify cohorts with AI agents

Every filter, shelf, and LOD expression in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your AI assistant will be able to compare Shopify cohorts by first-order product or discount code. Live connections to Postgres and BigQuery are also coming.