Shopify RFM Analysis: Segment Customers With ChatGPT From an Export

Export your orders, collapse them to one row per customer (days since last order, number of orders, total spent), and ask ChatGPT to score each on a 1 to 5 scale and sort customers into five segments: champions, loyal, new, at risk and lapsed. Then write one email per segment, to people who agreed to marketing.

RFM stands for recency, frequency and monetary value: how recently someone bought, how often, and how much they spent. It is old, simple and effective for a store with a few hundred to a few thousand buyers. One snag trips most people up straight away: Shopify's customer export has no last-order date, so recency has to come from the orders export. The rest of this page is the walk-through, a worked store, and the checks that keep a pleasant-looking chart honest.

Follow me on Instagram@sagnikteaches

Why the customer export alone will not do

When you export customers in Shopify, the columns include first and last name, email, whether they accept email marketing, address fields, phone, SMS and WhatsApp marketing consent, Total Spent, Total Orders, a note, a tax-exempt flag and tags. Shopify says you cannot edit Total Spent and Total Orders because they reflect what the customer spent and ordered from your online store, so do not assume in-person POS sales are counted in them. That gives you frequency and monetary value, but not recency.

Connect on LinkedInSagnik Bhattacharya

The orders export has what you need: an order date, an email or customer identifier, the order total and the financial status. Collapse that to one row per customer and you can calculate all three measures from a single source, which is better than stitching two files together. An export of more than 50 orders, or one filtered by date, arrives by email rather than downloading. Customer CSV files are capped at 15 MB; the orders export page gives no size limit, but a very large store will find a year at a time easier to handle.

Subscribe on YouTube@sagnikteaches

Here is the shape of the file you want ChatGPT to receive:

Strip the personal data first

RFM does not need to know who anyone is. It needs dates and amounts. Before anything is uploaded, replace each email with a customer number in your spreadsheet and keep the key (number to email) in a separate file that never leaves your computer. Drop names, phone numbers and addresses from the upload copy. When the analysis comes back with a segment per customer number, you match the segment labels back to your key and send from your email tool.

That is the minimal-data route, and it costs ten minutes. It also means a mistake in handling the upload copy exposes a list of numbers, dates and amounts, not a list of people. Check your plan's data settings for how uploaded files are used, because business plans and personal plans differ, and delete the working chat when you finish.

A small manufacturer's customer list, worked through

Take a small manufacturer that sells hand-forged cookware straight from its Shopify store. It has 1,840 customers who have bought at least once, an average order of about $55 (care kits and utensils outnumber the pans), and a founder who has never looked at the list as anything but a pile. All figures below are illustrative.

Step one: build the one-row-per-customer table

Export all orders for the past 24 months. In a spreadsheet, filter to paid orders and remove test orders and anything wholesale. Then, with a pivot table or a formula, produce one row per customer with the date of the most recent order, the count of orders and the sum of the order totals less refunds. If you would rather have ChatGPT do this, upload the orders file (with the email replaced by an ID) and ask it to build the table, then ask it to show the first ten rows so you can check them.

Step two: score each measure

The classic method ranks customers into quintiles on each measure. For a store where most customers bought only once, quintiles break down, because the bottom 60% all have a frequency of one and cannot be split. Use fixed bands for recency and frequency, and quintiles for spend:

Tweak the recency bands to your buying cycle. A cookware store that people replace every few years can stretch them. A store selling coffee or skincare, where refills run monthly, should tighten them. If your product is consumable, the refill timing guide shows how to set the bands from the gap between orders.

Step three: ask ChatGPT to score and segment

I am attaching customers.csv with one row per customer: customer_id,
last_order_date, orders, spend, accepts_marketing. Today is 2026-11-05.

1. Add days_since_last_order.
2. Score R, F and M from 1 to 5 using these bands: [paste the table].
   Use spend quintiles for M and tell me the dollar cut-offs you found.
3. Assign each customer to ONE segment, in this order of priority:
   - Champions: R 4-5 and F 4-5
   - Loyal: F 3-5 and R 3-5 (anyone not already a champion)
   - New: orders = 1 and R 4-5
   - At risk: orders 2 or more and R 1-2
   - Lapsed: everyone else with R 1-2
   Anyone left over goes into "Other" and I want to see how many there are.
4. Give me a table: segment, customers, share of customers, total spend,
   share of spend, average spend, average days since last order.
5. Save the full customer-level table as a CSV I can download.
Do not invent data. If a row is odd, list it instead of fixing it.

The ordering rule matters, and so does reading the output as a rule-follower, not a judge: the assistant applies your bands, and you decide whether the bands are right. A customer can satisfy two definitions, such as "New" and "Champions" when a spender buys once for a large amount. Giving the assistant a priority order produces the same answer every time you rerun it.

What the summary looks like

Illustrative output for the 1,840 customers:

The five segments plus Other add up to 1,840 customers, and each share column adds to 100%. Read the table as questions. Five percent of customers produce nearly a fifth of the spend, so lose one champion and it takes nearly two loyal customers to replace the revenue. More than 40% of the list bought once and not in the past six months, so any email to everyone is mostly an email to people who have stopped listening. The 224 at-risk customers are the cheapest group to recover, because they have bought more than once and already know the product. And 216 people, 12% of the list, match none of the five rules, which the hand check below explains.

Eight customers scored by hand

Before trusting the assistant on 1,840 rows, score a few yourself and compare. Eight illustrative rows, with today as 5 November 2026:

Customer C0702 shows why the "Other" bucket exists. Two orders and 127 days sits in the middle, matching none of the five rules. Do not force them in. A mid-way group is normal; in this list it holds 216 people, mostly one-time buyers from three to six months ago and two-order customers who bought recently. Give it a light-touch email, such as a product tip, and revisit when they move. If the assistant puts C0702 in a named segment without telling you, your rules were not precise enough, so tighten the prompt.

One email for each segment

You do not need five elaborate campaigns. You need five short, different messages, each with one job. Only send to customers whose marketing flag is "yes".

• Champions (96): thank them and give early access, not a discount. A discount tells your best customers they were overpaying. Offer first look at the next batch.
• Loyal (210): suggest the next product that fits what they have bought. Pair it with a reason, such as a lid for the pan they own.
• New (308): a care guide and one useful tip in the first month. Ask for a review after delivery has had time to land.
• At risk (224): a plain "we have not seen you for a while" note that names something that has changed, and a small reason to come back.
• Lapsed (786): a final check-in with an easy unsubscribe, then rest the ones who do not open. A smaller, engaged list is more valuable than a large dead one.

Drafting the at-risk email

Write a short email (under 120 words) to customers who have bought twice or
more from our cookware store but not in the past 6 months. Tone: warm,
plain, no hype. Include: one sentence on what is new (a lighter 26 cm
skillet), a 10% code valid for 14 days, and a line saying they can reply
if something went wrong with a previous order. Give three subject lines
under 45 characters. Use [first name] for personalisation.

Illustrative reply: subject "Something new since your last order"; body: "Hi [first name], it has been a while since your last order. We have added a lighter 26 cm skillet, built the same way as the one you bought. Use WELCOME10 until the 19th for 10% off. If anything about your earlier order was not right, reply here and I will look into it. [your name]". What to fix: check the code actually exists in Shopify, that the end date is right, and that the sentence about the skillet is true of your range. AI will happily invent a feature.

A quick sum shows whether the email is worth the effort. If 224 people get it and 4% order (an assumption, not a benchmark), that is 9 orders. At the store's average order of about $55, less 10%, which is $49.50, the email brings in about $445. Not dramatic, but it cost one hour, and the list gets better each time you rerun it. For the tool that will send these on a schedule, the Klaviyo and Shopify Email comparison sets out the trade-offs.

Checks before you send anything

1. Counts: the segment counts add up to the number of customers in your table. If not, look at the "Other" group.
2. One customer by hand: pick someone you know and confirm their recency, frequency and spend against the order list. If any of the three is wrong, the join is wrong.
3. Duplicates: one person who checks out as a guest with two spellings of the email shows up as two customers, and looks less loyal than they are. Search for near-matches before scoring.
4. Refunds: a customer who spent $120 and was refunded $120 is not a champion. Spend should be net of refunds.
5. Consent: filter each send list on the marketing flag, and again inside your email tool before sending.

An eight-week sending calendar from one scoring run

A single scoring run is only useful if something follows. A light calendar for the example store looks like this, and none of it needs special software beyond your usual email tool:

1. Week 1: clean the data, build the customer table, score and segment. Check the top 20 spenders by eye.
2. Week 2: send the at-risk email (224 people, marketing consent only). Tag them "rfm-at-risk-nov" in your email tool so you can see who came back.
3. Week 3: send the new-customer care guide to anyone whose first order was in the past 30 days.
4. Week 4: early-access note to champions. Ask one question in it: what would they like us to make next.
5. Weeks 5 and 6: read replies, then send loyal customers a pairing suggestion.
6. Week 7: a final check-in to lapsed customers who opted in. Anyone who has not opened your last five emails moves to a rest list.
7. Week 8: export orders again, rescore, and compare the segment table with the first one.

The comparison in week 8 is the payoff. If 40 of the 224 at-risk customers ordered, they have moved to Loyal or Champions (a third order lifts both their recency and frequency scores), and you can say what each email earned. If none moved, the offer or the timing was off, and you have a reason to change one thing, not everything.

Cutting the list another way: by product or by channel

RFM tells you who is valuable. It does not tell you what they like. Once the segments exist, add one more column to the customer table, such as the category of their first purchase, and ask the assistant to cross-tabulate. A manufacturer that sells both cookware and care kits might learn that champions almost all bought a skillet first, and that at-risk customers mostly started with a small accessory. That points to your acquisition: the accessory brings buyers who rarely return, so the advert for the skillet may be the better one to spend on. Run the same cut by discount code used on the first order, and you will often see that heavily discounted first buyers are over-represented in the lapsed group.

Keep the cross-tab small, two or three categories at a time. With under 2,000 customers, a table of twenty categories gives cells with three people in them, and you will read a pattern into noise. If the assistant offers a clustering model or a "predicted lifetime value", ask it to explain what data it used. For a small list, a plain table you can check by eye beats a model you cannot.

When RFM misleads a small store

A distributor selling workshop supplies through its Shopify store ran this on its list and found 40 champions. The "champions" turned out to be three trade buyers placing weekly orders through staff accounts, plus a dozen of the founder's own test orders from a launch. The segments looked fine; the data was not. After removing wholesale and test orders, the champion group shrank to 11, and the real picture was a business with a handful of loyal buyers and a long tail. Check the top 20 spenders by eye before reading anything into the segments. If a name looks like a company, a test or an internal address, take it out and rerun. (Illustrative.)

Two other limits are worth stating. RFM looks backwards: it describes behaviour, and says nothing about why. And it treats all spend as equal, so a customer who bought one expensive gift scores as a high spender even though they may never return. For products people buy once, such as furniture, frequency is a weak signal, and you may do better using recency and monetary value only.

Keep it running without making it a project

Once the first run works, store the prompt and the bands in a ChatGPT Project (or a note you paste each time) and rerun it every quarter. Record three numbers each time: the number of champions, the number at risk, and how many at-risk customers moved back to loyal or champions since last time. That last figure is the real test of whether your emails work, since it counts people, not opens.

If you want to go further, link segments to how you find new customers. The analytics tool comparison shows where to look for traffic and sales trends, and abandoned cart emails cover the buyers who never made it into your list at all. For a tidy export from messy source columns, the messy data tutorial is the one to read first.

Keeping the data small as the list grows

Three habits keep the privacy risk low. Keep the key file that maps numbers to emails somewhere separate from your uploads. Delete old customer-level files once the segments have been sent. And limit the columns to the five above, because every extra field (address, product names, notes) raises the stakes if a file is shared by mistake. If a colleague needs the result, share the segment totals, not the customer table.

Questions about RFM on a small store

Does Shopify's customer export include the last order date?

The customer CSV columns Shopify documents cover name, email, marketing consent, address, Total Spent, Total Orders, notes and tags. None is a last-order date, so build recency from the orders export, which carries a date on every order.

How many customers do I need for RFM to be worthwhile?

A few hundred is enough to be useful. Below about 300 customers, use fixed bands such as 30, 90 and 180 days instead of quintiles, and expect one or two segments to hold only a handful of people. Treat small segments as a list to read by eye.

Can I email everyone in a segment?

Only those who agreed to marketing. The export includes an Accepts Email Marketing column, so filter on it before you build any list. Customers who did not opt in can still get order-related messages, but not promotions.

Should I upload names and emails to ChatGPT?

You do not need to. Replace each email with a number before upload, run the scoring on the numbers, and match the segment labels back to emails in your own spreadsheet. The analysis needs dates and amounts, not identities.

Further reads

• Ecommerce Store Audit Checklist: Run It With ChatGPT or Claude — Check the pages new customers land on.
• Use AI to Sell Products Online: One First Job and a 30-Day Test — Pick one first selling job and test it.
• Reduce Subscription Box Churn: Cancel Reasons and Save Offers — Save offers for people about to leave.
• AI Product Recommendations on Shopify: Built-In vs Paid Apps — Show repeat buyers what to buy next.
• Track ChatGPT Traffic to Your Store and Find AI-Referred Orders — See which new customers arrive from AI answers.
• Shopify Profit Report With Claude: Add Ad Spend, COGS and Returns — Check which segments are actually profitable.
• Customer Loyalty Programmes for Retail Stores: Can AI Design One? — A small shop's loyalty programme drafted with AI: points, stamps and tiers costed line by line, plus the five corrections the draft needed.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Shopify Help Center (customer CSV export and import, column definitions and limits), checked November 2026.