How to build customer segments in Google Sheets

by

TL;DR

You can build a working RFM customer segmentation model directly in Google Sheets without any paid tools or coding knowledge. The whole setup takes about 90 minutes if your transaction data is already clean and exported. You need at minimum three columns: customer ID, purchase date, and order value.

What You Need Before You Start

  • A Google account with access to Google Sheets (free, no paid plan required)
  • A transaction history export with at least: customer ID or email, purchase date, and order amount per row
  • At least 50 rows of real customer data for the segments to carry any meaning
  • Basic familiarity with Google Sheets formulas like SUMIF, COUNTIF, and IF
  • Optional: a CRM export in CSV format from HubSpot, Mailchimp, Shopify, or WooCommerce
  • Optional: the Coefficient add-on for Google Sheets (free tier available) if you want live data pulls from your CRM instead of manual exports

Your data does not need to be perfect going in. Duplicate rows and a few missing values are manageable. What matters is that each row represents one transaction, not one customer.

Step 1: Set Up Your Raw Data Sheet

Create a new Google Sheets file and name it “Customer Segments 2026”. Rename the default Sheet1 tab to “Raw Data”.

Paste or import your transaction export here. Structure your columns in this order so the formulas later work without adjustment:

A B C D
customer_id customer_email order_date order_value

If your export has extra columns, leave them. Just confirm those four exist and note which letters they fall in. Format column C as a date: select the column, go to Format > Number > Date. Format column D as currency: Format > Number > Currency.

Do not merge any cells in this sheet. Merged cells silently break ARRAYFORMULA calculations and you will spend an hour debugging what is actually a formatting issue.

You should now see a clean table with a header row in row 1 and one transaction per row below it.

Step 2: Build Your Customer Summary Sheet

Click the “+” icon at the bottom to add a new sheet. Name it “Customer Summary”. This sheet holds exactly one row per unique customer.

In cell A1, type “customer_id”. In B1, type “customer_email”. Then in A2, enter:

=SORT(UNIQUE('Raw Data'!A2:B5000))

Adjust the range to match your actual data size. If your Raw Data sheet has 500 rows, use A2:B500. If it has 10,000 rows, use A2:B10000.

This formula pulls a deduplicated list of every customer automatically. Any new rows you paste into Raw Data later will flow through to this sheet on the next calculation.

You should now see one row per customer with their ID and email populated. If you see blank rows at the bottom, that is normal. The UNIQUE function pads the output range.

Step 3: Calculate Recency (Days Since Last Purchase)

Recency tells you how recently a customer bought. A buyer from last week is far more likely to buy again than one who went quiet 18 months ago.

In Customer Summary, add a header “last_purchase_date” in column C and “recency_days” in column D.

In C2:

=ARRAYFORMULA(IF(A2:A="","",MAXIFS('Raw Data'!C:C,'Raw Data'!A:A,A2:A)))

In D2:

=ARRAYFORMULA(IF(C2:C="","",TODAY()-C2:C))

Format column C as Date and column D as Number with 0 decimal places. The MAXIFS function finds the most recent purchase date for each customer across every matching row in Raw Data.

You should now see whole numbers in column D. A value of 14 means that customer last bought 14 days ago. A value of 400 means they have been quiet for over a year.

Step 4: Count Purchase Frequency Per Customer

Frequency counts how many times a customer has ordered. A one-time buyer and a ten-time buyer are completely different relationships, even if they spent the same total amount.

Add header “frequency” in column E of Customer Summary. In E2:

=ARRAYFORMULA(IF(A2:A="","",COUNTIF('Raw Data'!A:A,A2:A)))

This counts the number of rows in Raw Data that match each customer ID. Verify one or two customers by hand: filter Raw Data by their ID and count the rows. The number should match column E exactly.

You should now see frequency counts across every populated row in the column.

Step 5: Calculate Total Monetary Value Per Customer

Monetary value is total lifetime spend. Add header “total_spend” in column F. In F2:

=ARRAYFORMULA(IF(A2:A="","",SUMIF('Raw Data'!A:A,A2:A,'Raw Data'!D:D)))

Format column F as currency. Cross-check one customer: find all their rows in Raw Data, add up their order_value figures manually, and compare the result to what column F shows.

You should now see a lifetime spend figure per customer. This is the number you will use to separate high-value customers from low-value ones in the next step.

Step 6: Score Each RFM Metric from 1 to 3

Now you convert raw numbers into comparable scores. A score of 3 is best, 1 is worst. Add three headers: “r_score” in column G, “f_score” in H, “m_score” in I.

For recency, lower days is better. In G2:

=ARRAYFORMULA(IF(A2:A="","",IF(D2:D<=30,3,IF(D2:D<=90,2,1))))

This gives a 3 to anyone who bought in the last 30 days, a 2 for 31 to 90 days, and a 1 for anything older. Adjust the thresholds to your own sales cycle. A monthly SaaS product might use 7 and 30 days. A furniture store might use 180 and 365 days.

For frequency in H2:

=ARRAYFORMULA(IF(A2:A="","",IF(E2:E>=5,3,IF(E2:E>=2,2,1))))

For monetary value in I2:

=ARRAYFORMULA(IF(A2:A="","",IF(F2:F>=500,3,IF(F2:F>=100,2,1))))

Adjust the dollar thresholds to your average order value. If typical orders are $15, a $500 threshold will make nearly everyone score a 1.

You should now see columns G, H, and I filled with 1, 2, or 3 for every customer row.

Step 7: Build Your Segment Label with IFS

This is where the numbers become actionable names. Add header “segment” in column J. In J2:

=ARRAYFORMULA(IF(A2:A="","",IFS(
  G2:G=3,IF(H2:H>=2,"Champions","Promising"),
  G2:G=2,IF(H2:H>=2,"Loyal","At Risk"),
  G2:G=1,IF(H2:H>=2,"Needs Attention","Lost")
)))

This creates six segments:

  • Champions: bought recently and buys often
  • Promising: recent buyer but low purchase count
  • Loyal: less recent but consistent history
  • At Risk: fading recency with some frequency
  • Needs Attention: not recent, occasional past buyer
  • Lost: not recent and bought rarely

You can rename these labels to anything. The formulas do not care what the text says. Rename “Champions” to “VIPs” if that makes more sense to you.

You should now see a label in column J for every customer row.

Step 8: Summarize Segments with COUNTIFS

Add a new sheet called “Segment Summary”. In A1 through A6, type each of your six segment names. Add header “customer_count” in B1 and “total_revenue” in C1.

In B2:

=COUNTIF('Customer Summary'!J:J,A2)

In C2:

=SUMIF('Customer Summary'!J:J,A2,'Customer Summary'!F:F)

Copy both formulas down to row 7. You now have a high-level view of how many customers sit in each segment and how much revenue each group represents.

You should see non-zero counts across most rows. If one segment shows zero, go back and loosen the IFS thresholds in Step 7.

Step 9: Visualize the Breakdown with a Bar Chart

Select the range A1:B7 in Segment Summary. Go to Insert > Chart. Google Sheets will default to a bar chart. Under Chart Editor on the right, confirm the Chart type is “Bar chart” with column A as the X-axis label and column B as the data series.

Click Customize > Series and change the bar color. Default blues all look identical on a screen share. A darker green for Champions and a muted red for Lost makes the chart readable at a glance.

Duplicate the chart and swap the data range to A1:A7 and C1:C7 to show revenue by segment alongside the count chart.

You should now see two charts that give you an instant read on where your customers cluster. See how to build pivot tables in Google Sheets for more ways to slice this same data without extra formulas.

Common Mistakes To Avoid

  • Using one row per customer in Raw Data instead of one row per transaction. SUMIF and COUNTIF only work correctly when each row is a single order. Pre-aggregated data breaks frequency and monetary calculations silently.
  • Not using open-ended ranges in ARRAYFORMULA. Writing IF(A2,… instead of IF(A2:A,… means only one row gets calculated. Always extend ranges to A2:A when using ARRAYFORMULA.
  • Setting thresholds on day one and never revisiting them. Your recency and monetary cutoffs should reflect your actual business patterns. Check them every quarter, especially if your order volumes grow.
  • Mixing customer IDs and emails as your join key. If some Raw Data rows use an ID and others use an email, COUNTIF will miss matches and undercount frequency. Pick one key and standardize it before running any formulas.
  • Storing order_date as text instead of a real date value. If TODAY()-C2 returns an error, select column C and check Format > Number. If it shows “Plain text”, convert it to Date first.
  • Writing off the Lost segment completely. A customer who spent $3,000 two years ago and went silent is a win-back candidate. Sort the Lost segment by total_spend before you decide to ignore them.

When To Level Up

Google Sheets handles customer segmentation cleanly up to roughly 5,000 to 10,000 customers. Past that, ARRAYFORMULA recalculations slow down and the sheet can hang for 20 to 30 seconds after every edit. That friction compounds quickly when you are trying to work fast.

The other ceiling is automation. This build is fully manual. You export, paste, and wait for recalculation. If you want segments to refresh daily without opening a file, you need a tool that connects directly to your database or CRM pipeline.

When you hit either of those limits, the options that make sense next are Metabase for self-hosted BI (free tier, connects to most databases), or a proper customer data platform like Segment that handles the data pipeline for you. Python with pandas is also worth the investment if you are comfortable scripting. The same RFM logic runs on a million rows in under a second.

That said, starting here is the right move. Building it manually forces you to understand the data before you automate it. Browse the full Excel and Sheets tools guide to see what fits once you outgrow this setup.

Also check out how to clean messy CRM exports in Google Sheets before you run segmentation on a fresh export, and conditional formatting tricks for data dashboards to make the Segment Summary tab easier to scan at a glance.

Frequently Asked Questions

How many customers do I need before segmentation is useful?
You can run these formulas on 20 customers but the results will not be meaningful enough to make decisions from. Aim for at least 100 unique customers before you treat the segment labels as signals. below that, just read through your customer list directly.

Can I use this with Shopify or WooCommerce data?
Yes. Both platforms export orders as CSV with customer email, date, and order total. That maps directly to the Raw Data structure in Step 1. In Shopify, go to Orders > Export. In WooCommerce, go to WooCommerce > Orders and use the Export button at the top of the list.

What if a customer has multiple email addresses on file?
The UNIQUE formula will treat each email as a separate person, so you will get duplicated customer rows. Clean this in Raw Data first by picking one canonical identifier per customer. A numeric customer ID from your CRM is safer than an email field for this reason.

Do I need to redo all these steps when new orders come in?
No. You only need to paste new transaction rows into the Raw Data tab. All the ARRAYFORMULA columns in Customer Summary recalculate automatically. Set a weekly reminder to paste in fresh data, or use the Coefficient add-on to pull it live.

Can I add product category as a fourth segmentation dimension?
You can, but it complicates the IFS logic considerably. The cleanest approach is to filter Raw Data by category first, run a separate segmentation model for each category on its own sheet, and then VLOOKUP the results back into one master view. That is a more advanced build and deserves its own walkthrough.

Bottom Line

Building customer segments in Google Sheets is a genuine analytical workflow. You set up a Raw Data tab with one row per transaction, build a Customer Summary tab using SUMIF, COUNTIF, and MAXIFS to get one row per customer, score each customer on recency, frequency, and monetary value using IF formulas, and then assign a plain-language label with IFS. The whole model stays live as long as you keep pasting in fresh transaction data each week. you end up knowing exactly which customers are your best buyers, which ones are drifting, and which are worth a targeted win-back message. that knowledge changes who you email, who you offer discounts to, and where you put your energy. when the sheet gets slow or you want daily automation, move to a proper BI tool. until then, this approach does real work. head over to /category/excel-sheets-power-skills/ to see what the next step looks like.