How to Count New vs Returning Customers in Google Sheets
Who bought for the first time this month, who came back, and how much of the revenue the regulars bring. Two helper columns and three formulas.
The short answer: give every order line its customer's first order date with =MINIFS($A$2:$A, $C$2:$C, C2), label it New or Returning, then count distinct customers of each kind per month. In the sample data, September 2026 had 2 new and 63 returning customers.
The sample data
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Order ID | Customer | Region | Channel | Product | Category | Qty | Unit price | Revenue | Cost |
| 2 | 2025-01-01 | SO-10001 | Tin Roof Café | Northeast | Sales rep | Sip Lids (case of 1,000) | Supplies | 6 | 54 | 324 | 198 |
| 3 | 2025-01-01 | SO-10001 | Tin Roof Café | Northeast | Sales rep | Espresso Roast 5 lb | Coffee | 10 | 68 | 680 | 370 |
| 4 | 2025-01-01 | SO-10002 | Foxglove Café | Northeast | Online store | Sip Lids (case of 1,000) | Supplies | 1 | 54 | 54 | 33 |
| 5 | 2025-01-01 | SO-10002 | Foxglove Café | Northeast | Online store | Cold Brew Concentrate 1 gal | Coffee | 8 | 42 | 336 | 152 |
| 6 | 2025-01-01 | SO-10003 | Ember Café | Midwest | Online store | Sip Lids (case of 1,000) | Supplies | 4 | 54 | 216 | 132 |
| … | 8,115 more rows | ||||||||||
Sample data: a tab named Orders with 8,120 order lines from January 2025 to October 2026, one row per order line. Revenue is Qty × Unit price; Cost is the line's cost of goods (Qty × unit cost). Download orders.csv and import it (File → Import) to follow along. The business is fictional.
The classic way: a first-order column, then COUNTUNIQUEIFS
Step 1, on the Orders tab. Type First order in L1 and, in L2 copied down, the earliest date this customer appears:
=MINIFS($A$2:$A, $C$2:$C, C2)Type Type in M1 and, in M2 copied down, compare the order's month with the first order's month:
=IF(TEXT(A2,"yyyy-mm")=TEXT(L2,"yyyy-mm"), "New", "Returning")A customer is new in the month of their first order, even if they order twice that month, and returning in every month after.
Step 2, on a summary tab. Months in column A from =DATE(2026,5,1) down with =EDATE(A2,1). Then in row 2, copied down:
=COUNTUNIQUEIFS(Orders!$C:$C, Orders!$M:$M, "New", Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))
=COUNTUNIQUEIFS(Orders!$C:$C, Orders!$M:$M, "Returning", Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))
=SUMIFS(Orders!$J:$J, Orders!$M:$M, "Returning", Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1)) / SUMIFS(Orders!$J:$J, Orders!$A:$A, ">="&$A2, Orders!$A:$A, "<"&EDATE($A2,1))B counts new customers, C returning customers, D the share of the month's revenue from returning ones (format it as a percent).
Example on sample data| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | New customers | Returning customers | Revenue from returning |
| 2 | May 2026 | 1 | 61 | 99.7% |
| 3 | Jun 2026 | 1 | 55 | 99.8% |
| 4 | Jul 2026 | 3 | 59 | 97.9% |
| 5 | Aug 2026 | 1 | 66 | 95.4% |
| 6 | Sep 2026 | 2 | 63 | 97.6% |
| 7 | Oct 2026 | 0 | 64 | 100.0% |
Computed from the sample CSV above. Cells formatted as currency and percent.
Big sheet? MINIFS on every row gets slow past a few thousand rows. One formula in L2 does the whole column: =ARRAYFORMULA(IF(C2:C="", , VLOOKUP(C2:C, SORT({C2:C, A2:A}, 2, TRUE), 2, FALSE))). It sorts customer and date by date, so VLOOKUP's first match is each customer's earliest order.
Gotchas
- "New" only means new to this data. Everyone in the first month of your export looks new. The sample starts in January 2025, so the counts from mid-2026 on are reliable; give yourself at least a year of history before the months you report.
- Names are not IDs. "Blue Door Bistro" and "Blue Door Bistro LLC" are two customers to a formula. Use a customer ID or email if you have one.
- Counts do not add across months. A returning customer is counted in every month they buy. Six months of returning counts is not the number of loyal customers.
The one-cell way: =QUIRIZ
With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload orders.csv as a dataset, or connect your Shopify store. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:
=QUIRIZ("new vs returning customers by month from May to October 2026, where a new customer is one whose first order is in that month, and the share of revenue from returning customers", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | New customers | Returning customers | Revenue from returning |
| 2 | May 2026 | 1 | 61 | 99.7% |
| 3 | Jun 2026 | 1 | 55 | 99.8% |
| 4 | Jul 2026 | 3 | 59 | 97.9% |
| 5 | Aug 2026 | 1 | 66 | 95.4% |
| 6 | Sep 2026 | 2 | 63 | 97.6% |
| 7 | Oct 2026 | 0 | 64 | 100.0% |
Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.
The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.
Ask the question, get the table
Quiriz answers business questions in a Google Sheets cell, from your imported files, scheduled Google Sheets and connected tools. Free to start.
Install for Google Sheets →Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.