How to build an RFM analysis in Google Sheets

TL;DR

You can build a working RFM analysis in Google Sheets using nothing but your raw order export and a handful of built-in formulas. The whole process takes two to three hours the first time and under an hour once you have a reusable template. You need transaction history with at least three columns: a customer identifier, an order date, and a revenue figure per order.

What You Need Before You Start

• Google account (free, any tier) with access to Google Sheets
• Order export file in CSV or XLSX format with these columns at minimum: customer email or ID, order date, and order revenue per transaction
• At least 90 days of transaction history — 12 months gives you much more reliable recency scoring
• A unique customer identifier — customer email works fine if you have no dedicated customer ID column
• Dataset under 50,000 rows — Sheets handles this well. Above that, formula lag becomes a real problem.
• Optional: Klaviyo or your email platform for re-importing customer segments after scoring
• Optional: basic familiarity with COUNTIFS and SUMIFS — this guide explains both, but prior exposure speeds things up

Step 1: Export and Clean Your Order Data

Start with a clean, flat file. Every row must represent one order, not one product line. If your platform exports one row per item, you need to aggregate to one row per order before doing anything else.

Export from Shopify, WooCommerce, BigCommerce, or whichever platform you run. Import the CSV into a new Google Sheet. Name that first tab raw_orders.

Your columns should follow this structure:

order_id	customer_email	order_date	revenue
1001	jane@example.com	2025-03-12	84.00
1002	bob@example.com	2025-04-01	120.50

Delete test orders, exclude fully refunded orders unless you want to track them separately, and remove any rows with blank customer emails. Then select your order_date column and format it as a Date via Format > Number > Date so your formulas recognize it correctly.

You should now see a clean, flat table with no blank rows, consistent date formatting, and one row per order.

Step 2: Build the Customer Summary Table

Create a second tab called rfm_base. This tab aggregates order-level rows into one row per customer and feeds every formula in the analysis.

In cell A1, type the header customer_email. In A2, pull a unique list of customers from the raw data:

=UNIQUE(raw_orders!B2:B)

This fills column A with one unique email per row automatically. Then add these headers in B1, C1, and D1:

• last_order_date
• order_count
• total_revenue

You should now see a column of unique customer emails in A, with three empty columns waiting for aggregation formulas.

Step 3: Calculate Recency

Recency measures how many days have passed since a customer’s most recent purchase. Fewer days means better recency.

In B2, enter:

=MAXIFS(raw_orders!C:C, raw_orders!B:B, A2)

This returns the most recent order date for the customer in A2. Fill this formula down the full customer list.

In a helper cell, say F1, store today’s reference date:

=TODAY()

Add a column E with the header days_since_order. In E2, enter:

=$F$1-B2

Format column B as Date and column E as Number. Fill both formulas down. You should now see each customer’s most recent order date in column B and the number of days since that order in column E. The $F$1 anchor keeps the reference date fixed as you fill down.

Step 4: Calculate Frequency

Frequency counts how many orders each customer has placed. More orders equals higher frequency.

In C2, enter:

=COUNTIFS(raw_orders!B:B, A2)

This counts every order row in raw_orders where the customer email matches A2. Fill it down the full customer list.

A customer with one order gets a 1. A repeat buyer who ordered eight times gets an 8. There are no complex arguments here. You should now see a whole number in column C for every customer, with no blanks anywhere in the column.

Step 5: Calculate Monetary Value

Monetary value is each customer’s total spend across all orders in your dataset.

In D2, enter:

=SUMIFS(raw_orders!D:D, raw_orders!B:B, A2)

Fill this down the full list. Format column D as currency via Format > Number > Currency.

Cross-check three or four customers manually against your platform’s customer reports to confirm the totals match. Discrepancies usually mean refunded orders or multi-currency transactions are mixed into your export.

You should now see total lifetime spend per customer in column D, formatted as currency, for the entire list.

Step 6: Score Each RFM Dimension on a 1-to-5 Scale

Now you convert raw numbers into scores from 1 (worst) to 5 (best). PERCENTRANK places each customer on a 0-to-1 scale relative to the full group, and you map that onto five buckets.

Add three column headers: r_score, f_score, m_score in columns F, G, and H.

For Recency (fewer days = better, so the score is inverted):

=6 - MAX(1, ROUNDUP(PERCENTRANK($E$2:$E$1000, E2) * 5, 0))

For Frequency (higher count = better):

=MAX(1, ROUNDUP(PERCENTRANK($C$2:$C$1000, C2) * 5, 0))

For Monetary (higher spend = better):

=MAX(1, ROUNDUP(PERCENTRANK($D$2:$D$1000, D2) * 5, 0))

Adjust the range ($E$2:$E$1000 etc.) to match your actual row count. The MAX(1,…) wrapper prevents edge cases where PERCENTRANK returns exactly 0, which would otherwise produce a score of 0.

You should now see scores from 1 to 5 in columns F, G, and H, with 5 representing your top-performing customers on each dimension.

Step 7: Combine Scores into an RFM String and Total

Add a column I with the header rfm_score. Concatenate the three scores into a three-character string:

=F2&G2&H2

A champion customer gets “555”. A churned one-time buyer gets “111”. This string makes segment lookups fast and readable.

Also add a column J called rfm_total for numeric sorting and filtering:

=F2+G2+H2

Totals range from 3 (worst) to 15 (best). You should now see every customer tagged with a score string like “541” or “213” alongside a numeric total you can sort by to quickly find your highest-value customers.

Step 8: Assign Customer Segments

This is where numbers become action. Add a column K with the header segment. Use an IFS formula to label each customer based on their R and F scores. Monetary value matters for prioritization but the most actionable segments come from recency and frequency combined.

Here F2 is your r_score column and G2 is your f_score column:

=IFS(
  AND(F2>=4, G2>=4), "Champions",
  AND(F2>=4, G2>=2), "Loyal Customer",
  AND(F2>=4, G2<2), "Potential Loyalist",
  AND(F2<=2, G2>=3), "At Risk",
  AND(F2=1, G2=1), "Lost",
  F2<=2, "Hibernating",
  TRUE, "Needs Attention"
)

Fill this down the full list. Adjust the thresholds based on your business context. A shop with 500 total customers needs different cutoffs than one with 50,000.

For a deeper look at how these segment definitions map to campaign strategies, see the customer segmentation guide on this site.

You should now see every customer assigned to a named segment, with no customer left unclassified thanks to the TRUE catch-all at the end.

Step 9: Build a Segment Summary Pivot Table

Create a third tab called rfm_summary. This is the tab you will actually use to make decisions.

Insert a pivot table via Insert > Pivot table, set the data source to rfm_base, and configure it as follows:

• Rows: segment
• Values: COUNTA of customer_email, AVERAGE of rfm_total, SUM of total_revenue

This gives you a one-glance view of how many customers sit in each bucket and how much revenue each segment generates. Sort by SUM of total_revenue descending to see which segments are worth the most attention immediately.

For a full walkthrough of pivot table mechanics in Sheets, see the Google Sheets pivot tables guide.

You should now see a clean summary table showing segment sizes and revenue contribution side by side, refreshable any time you update the raw_orders tab.

Step 10: Apply Conditional Formatting and Export Segments

Add a color scale to the rfm_summary tab to make high-value segments jump out visually. Select the total revenue column, go to Format > Conditional formatting, choose Color scale, and set green for the maximum value and red for the minimum.

To export a specific segment for a campaign, go to rfm_base, click the filter icon on the segment column, select only the segment you want, then copy all visible rows and paste them into a new sheet. Export as CSV and import into your email platform.

If you use Klaviyo, upload your Champions segment for a loyalty or early-access campaign and your At Risk segment for a win-back sequence. Those two groups alone usually represent the highest ROI opportunities in any email calendar.

You should now have a fully segmented, conditionally formatted customer list you can export, import, and act on without ever leaving the Google Sheets environment.

Common Mistakes To Avoid

• Using product-level rows instead of order-level rows. A customer with five items in one order will look like a five-order buyer if you skip aggregation. Always consolidate to one row per order before building the formula layer.
• Ignoring refunds. A customer with three orders and two full refunds looks like a repeat buyer in raw COUNTIFS logic. Exclude fully refunded orders from your export, or subtract refund amounts from total_revenue manually.
• Hardcoding the analysis date. If you type a specific date instead of =TODAY() in your reference cell, your recency scores go stale the moment you close the file. Use =TODAY() and pin it with $F$1 in your formulas.
• Treating segment thresholds as permanent rules. A strict “R must equal 5 for Champions” rule breaks down when a cluster of customers all purchased within the last week. Review and adjust thresholds every quarter, especially after major promotions.
• Sending discounts to Champions. Champions do not need a coupon. They need exclusivity and recognition. Sending discount codes to your highest-value customers trains them to wait for sales instead of buying at full price.
• Rebuilding the sheet from scratch every month. Once your formulas are in place, you only need to refresh the data in raw_orders. Keep formulas in separate tabs so a paste operation never overwrites your logic.

When To Level Up

Google Sheets handles RFM well for datasets under 50,000 rows and teams of one or two people. Past that point, a few things break down in predictable ways.

Formula recalculation slows noticeably when COUNTIFS and SUMIFS run over large ranges. Multiple collaborators editing the same file creates conflict and accidental overwrites. There is no version history unless you manually duplicate the sheet before each monthly refresh. And if you want to combine RFM scores with behavioral data from your website, Sheets has no practical path for that.

When you hit those limits, the natural next step is a BI tool connected directly to your database or ecommerce platform. Looker Studio connects to BigQuery or Postgres and runs the same aggregation logic with no formula lag. Python with pandas handles the full RFM calculation on millions of rows in seconds. Dedicated platforms like Putler or Glew run RFM out of the box with automatic syncing, no setup required.

Browse the data analysis tools category to compare what makes sense at your current scale. The RFM tools comparison breaks down paid platforms that automate exactly what you just built manually.

Frequently Asked Questions

How often should I refresh my RFM analysis?
For most ecommerce businesses, monthly is the right cadence. If you run frequent flash sales or your customer base is very active, bi-weekly works better. Refreshing too often introduces noise because customers naturally shift between segments with each new purchase cycle.

What is the minimum amount of data needed to make RFM useful?
You need enough customers to make the 1-to-5 scoring meaningful. Fewer than 200 customers produces very coarse segments where the scores cluster together. You also need at least six months of history so recency scores reflect genuine buying patterns rather than the spike from a single campaign.

Should I include one-time buyers in the analysis?
Yes, include them. One-time buyers usually make up the largest segment in any ecommerce business. Knowing how many you have and how recently they bought helps you design specific activation campaigns for that group rather than treating them as noise.

What if I sell B2B and have very few high-value accounts?
RFM still works, but adjust your scoring emphasis. With B2B, monetary value usually dominates because purchase frequency is inherently low due to longer sales cycles. Consider running a simpler two-dimension analysis focused on Recency and Monetary if Frequency data is too sparse to score meaningfully.

Can I use this template for a subscription business?
With modifications, yes. For subscriptions, recency loses meaning because customers are billed automatically whether they engage or not. Focus instead on engagement signals like login activity or feature usage. A pure order-based RFM model will overrate churned subscribers who are still on an active billing cycle.

Bottom Line

Building RFM in Google Sheets costs nothing except your time. You pull your order export, aggregate to one row per customer, calculate recency with MAXIFS, frequency with COUNTIFS, and monetary value with SUMIFS, then score each dimension from 1 to 5 using PERCENTRANK. A concatenated score string and a segment IFS formula turn those numbers into named customer groups you can filter and export in minutes. The summary pivot table shows you at a glance which customers are driving your revenue and which ones are drifting toward churn. It is not the most scalable solution, and it has real limits at high row counts, but it is actionable, free, and reusable month after month once built. When you are ready to automate this or push it to a larger dataset, browse the data analysis tools category for the platforms that fit your next stage of growth.