Customer Segmentation with AI (Excel + Microsoft Copilot + ChatGPT)

Group customers by value and behavior so you market to the right people — with Microsoft Copilot and ChatGPT inside every step. This tutorial uses the RFM method in Excel, with both AIs helping you score, segment, and interpret customers.

Table of Contents

• What Is Customer Segmentation
• The RFM Framework
• Scoring Customers in Excel
• Segmenting with AI
• Acting on Segments with AI

What Is Customer Segmentation

Customer segmentation splits your list into groups that behave alike — big spenders, at-risk buyers, first-timers. Instead of one generic email to all, you send the right message to each group.

Why it is useful: A "we miss you" offer to a lapsed VIP recovers more than the same blast to casual browsers.

Use AI from the start — ChatGPT to design the approach:

"I have customer purchase data: last purchase date, order count, total spent. Recommend a segmentation method and the exact Excel columns and scores I should build."

ChatGPT recommends RFM and outlines the Recency/Frequency/Monetary score columns — your blueprint before touching Excel.

The RFM Framework

RFM segments with no statistics needed. Three scores per customer:

• R (Recency): How recently did they buy? Recent = higher score.
• F (Frequency): How often? More = higher score.
• M (Monetary): How much spent? More = higher score.

Score each 1 (worst) to 5 (best), then add for a total. Simple and powerful.

Scoring Customers in Excel

Sample data:

Recency score (lower days = better). Use RANK:

Recency Score:  =6 - RANK(E2, $E$2:$E$4, 1)

(RANK order 1 ranks smallest first; subtracting from 6 flips it so recent = 5.)

Frequency & Monetary scores:

Frequency Score: =RANK(F2, $F$2:$F$4)
Monetary Score:  =RANK(G2, $G$2:$G$4)

Total RFM:

RFM Total: =H2+I2+J2

Simple explanation: With only 3 customers the RANK scale tops at 3 per dimension, so the max RFM total here is 11. Customer A scores 11/11 (would reach 15/15 in a larger base) — a Champion. Customer B (60 days idle, 2 orders, $300) scores just 5 — a Lost, lapsed low-value buyer worth a nudge, not a VIP offer.

Use AI — Microsoft Copilot to build the scores:

"Using the Last Purchase, Orders, and Total Spent columns, add R, F, M scores from 1–5 and a total RFM column."

Copilot writes the RANK formulas and the total, so you skip the manual setup.

Common Mistake: Scoring on raw values without RANK across the group. "$2400" means nothing until compared to others — ranking creates that comparison.

Segmenting with AI

Once scores exist, bucket customers into named groups.

Use AI — Microsoft Copilot to label segments:

"Group these customers by RFM total into Champions (12-15), Loyal (9-11), At-Risk (6-8), Lost (3-5), and count each group."

Copilot adds a segment label column and a summary count — campaign-ready buckets instantly.

Use AI — ChatGPT to sanity-check the brackets:

"I'm using RFM brackets Champions 12-15, Loyal 9-11, At-Risk 6-8, Lost 3-5. Are these reasonable, and when should I adjust them?"

ChatGPT confirms the standard split and notes to widen brackets for small lists — guidance that prevents mislabeling.

Best Practice: Define segment brackets in your prompt so Copilot's groups match your rules, not its guesses.

Acting on Segments with AI

Segments only pay off when turned into campaigns.

Use AI — Microsoft Copilot to summarize the segments:

"Summarize my four customer segments with their counts and average spend in one short paragraph for a marketing brief."

Copilot produces the brief text straight from the labeled table.

Use AI — ChatGPT to name and act on segments:

"Four segments: Champions (12-15), Loyal (9-11), At-Risk (6-8), Lost (3-5). For each suggest one email tactic and one offer type."

ChatGPT may reply: Champions → early-access launches + loyalty perks; At-Risk → 'we miss you' discount + restock alert; Lost → win-back coupon with expiry.

Note: Keep customer names/PII out of ChatGPT. Share segment sizes and scores, not identities.