How to Calculate 90-Day Shopper LTV by Intelligems Test Group

Learn how to calculate the 90-day lifetime value (LTV) of customers in your Intelligems A/B test using only Shopify and Google Sheets.

Overview:

Most A/B tests only measure immediate conversions, but the real winner might be the variant that attracts customers who spend more over time. A 10% discount might win on first-purchase conversion rate, but a free shipping offer could attract customers with higher repeat purchase rates. This guide shows you how to uncover which test variant drives more valuable long-term customers.

Prerequisites:

To use this guide successfully, users must have:

• Concluded their test at least 90 days ago.

• Tagged their orders by Test Groups. [Instructions]

Step 1: Export Orders from Shopify

1. Log into your Shopify Admin

2. Click "Orders" in the left sidebar

3. Click "Export" button (top right)

4. Select:

   • Export: "Orders by Date"

   • Start Date: Start date of your test

   • End Date: Today

   • Export as: "CSV for Excel, Numbers, or other spreadsheet programs"

5. Click "Export orders"

6. Download the CSV from your email (arrives in 1-5 minutes)

Step 2: Import into Google Sheets

1. Go to: https://sheets.google.com

2. Click: "Blank" spreadsheet

3. Click: File → Import

4. Click: "Upload" tab

5. Drag your CSV file into the window

6. Settings:

   • Import location: "Replace spreadsheet"

   • Separator type: "Detect automatically"

7. Click: "Import data"

Step 3: Keep Only the Columns You Need

Delete all columns EXCEPT Email, Total, Created at, and Tags.

Also, Rename "Sheet1" to: All Orders

Step 4: Clean Your Date Column

1. Click on cell E1

2. Type: Clean Date

3. Click on cell E2

4. Paste this formula:

   =DATEVALUE(LEFT(C2,10))

1. Press Enter

2. Click on cell E2 again

3. Drag the formula down to all rows with data (click the small blue square at the bottom-right corner of E2 and drag down to your last row)

4. Click on the column E header to select the entire column

5. Click: Format → Number → Date

6. The numbers should now display as dates like 2/9/2026 or Feb 9, 2026

Step 5: Add Test Group Column

1. Click on cell F1

2. Type: Test Group

3. Click on cell F2

4. Take the below formula & update it with your order tags. Then paste into F2:

   =IF(REGEXMATCH(D2,"INSERT CONTROL ORDER TAG"),"Control",IF(REGEXMATCH(D2,"INSERT VARIANT ORDER TAG"),"Variant","Other"))

1. Press Enter

2. Click on cell F2 again

3. Drag the formula down to all rows with data

Step 6: Filter to Only Intelligems Orders

1. Click cell A1

2. Click: Data → Create a filter

3. Click the filter icon (▼) in column F (Test Group)

4. Uncheck "Other"

5. Make sure only "Control" and "Variant" are checked

6. Click OK

Now you're seeing only the orders where customers FIRST saw your test.

Step 7: Create Customer LTV Sheet

1. Select all visible data (Click cell A1, then Ctrl+A or Cmd+A)

2. Copy (Ctrl+C or Cmd+C)

3. Click the "+" button at the bottom to add a new sheet

4. Rename the new sheet to: Customer LTV (double-click the tab name)

5. Click cell A1 in the new "Customer LTV" sheet

6. Paste (Ctrl+V or Cmd+V)

Step 8: Remove Duplicate Customers

1. Click on the column A header (Email) to select the entire column

2. Click: Data → Data cleanup → Remove duplicates

3. Check "Data has header row"

4. Click "Remove duplicates"

You should now have a unique list of customer emails with their data.

Step 9: Add Column Headers

In the Customer LTV sheet:

1. In cell G1, type: First Order Date

2. In cell H1, type: 90-Day Revenue

Step 10: Find Each Customer's First Order Date

1. Click on cell G2 in "Customer LTV" sheet

2. Paste this formula:

=MINIFS('All Orders'!E:E,'All Orders'!A:A,A2,'All Orders'!F:F,"<>Other")

1. Press Enter

2. Click on cell G2 again

3. Drag this formula down to all rows that have an email address

4. Click on the column G header to select the entire column

5. Click: Format → Number → Date

6. The numbers should now display as dates like 2/9/2026 or Feb 9, 2026

This finds the earliest order date for each customer from your test.

Step 11: Calculate 90-Day Revenue Per Customer

1. Click on cell H2 in "Customer LTV" sheet

2. Paste this formula:

=SUMIFS('All Orders'!B:B,'All Orders'!A:A,A2,'All Orders'!E:E,">="&G2,'All Orders'!E:E,"<="&G2+90)

1. Press Enter

2. Click on cell H2 again

3. Drag this formula down to all rows with emails

This calculates the total revenue for each customer within 90 days of their first order.

Step 12: Create Summary Table

In the "Customer LTV" sheet, create your summary starting at cell K1:

1. In cell K1, type: Test Group

2. In cell L1, type: Average 90-Day LTV

3. In cell M1, type: Customer Count

4. In cell N1, type: Total Revenue

5. In cell K2, type: Control

6. In cell K3, type: Variant

Step 13: Calculate Summary Values

1. In cell L2, paste:

=AVERAGEIF(F:F,"Control",H:H)

1. In cell L3, paste:

=AVERAGEIF(F:F,"Variant",H:H)

1. In cell M2, paste:

=COUNTIF(F:F,"Control")

1. In cell M3, paste:

=COUNTIF(F:F,"Variant")

1. In cell N2, paste:

=SUMIF(F:F,"Control",H:H)

1. In cell N3, paste:

=SUMIF(F:F,"Variant",H:H)

Step 14: Format Your Results

1. Select column L

2. Click: Format → Number → Currency

3. Select column N

4. Click: Format → Number → Currency

FINAL RESULT

Your summary table should now show:

Test Group	Average 90-Day LTV	Customer Count	Total Revenue
Control	$XXX.XX	XXX	$X,XXX.XX
Variant	$XXX.XX	XXX	$X,XXX.XX

WHAT THESE NUMBERS MEAN

• Average 90-Day LTV: The average total amount each customer spent within 90 days of their first order, including ALL repeat purchases

• Customer Count: How many unique customers were in each test group

• Total Revenue: The total revenue from all customers in each group (within their first 90 days)