A commercial director wants account-management effort to reflect how customers buy. In this lesson, you will turn invoice-line history into a customer segmentation that another consultant can inspect, reproduce and challenge. You will ask ChatGPT to do the calculations and prepare an Excel handoff, then use Excel to check the result.
The useful skill is defining and defending the analysis. A customer’s recency, invoice frequency and recorded monetary amount describe observed purchases. Your labels and follow-up ideas remain business judgments. Neither the data nor a high RFM score establishes customer profit, lifetime value or the benefit of a campaign.
What you will learn
- Keep complete customer histories and invoices while selecting an analytical window.
- Distinguish transaction lines from orders and retain signed returns or credits.
- Define recency, frequency and monetary value at a fixed cutoff.
- Use explicit percentile boundaries without splitting identical values.
- Build interpretable segments, compare a second rule set and trace the differences to individual customers.
- Check an Excel handoff against preserved source records and a full reconciliation.
For sequential study, Lesson 2 provides the cleaning and reconciliation concepts. For standalone use, read the method card before Stage 1; all data and policy inputs needed here are supplied. No programming is required. The blueprint’s 50-minute allowance is a planning estimate, not a measured completion time. You may stop between stages and resume the same conversation.
Set up the lesson
1. Download and unzip L09_Learner_Practice_Files.zip. Alternatively, open the complete publication package and use only its 01_start and 02_practice folders for the first four stages.
2. Make a local folder for your own outputs. Keep the supplied files unchanged. Do not open and resave the frozen CSV in Excel before upload.
3. Start a new ChatGPT conversation with file analysis available. Record the model shown and whether the chat is regular or Temporary. Capabilities and upload limits vary by account; the lesson does not depend on a particular model name.
4. Attach the seven Stage 1 files listed below. If your interface limits a single batch, attach them over several messages, asking ChatGPT to wait until all seven are present. Do not paste a preview as a substitute for the file.
5. Paste Prompt 1. Review its control table before proceeding. Use one prompt at a time, saving the files produced after each stage.
Do not upload the complete publication ZIP, the original source workbook, the instructor full-history pack or the reference answers at the start. The instructor sources include later December 2011 records. The learner analysis must use only information available before 1 December 2011.
| Starting file | Purpose |
|---|---|
| L09_L10_retail_asof_2011_12_01.csv | Exact 63,734-row source history; every observation is retained. |
| L09_Selected_Customer_Register.csv | The selected 500 customer IDs and pre-cutoff history controls. |
| L09_Merchandise_Classification_v1.csv | One versioned, exact-case merchandise decision per observed code. |
| L09_RFM_and_Eligibility_Policy_v1.json | Eligibility, unknown handling, RFM definitions and scoring rules. |
| L09_Data_Dictionary_and_Method_Card.pdf | Standalone definitions and field dictionary. |
| L09_Source_and_Reuse_Notice.txt | Attribution, transformations and source/reuse limits. |
| L09_Input_Manifest.json | Expected hashes, file sizes and upload inventory. |
Stage 1 Verify the source and scope
The source contains observations both inside and before the RFM year. The older rows are useful history and will remain in the ledger. The timestamp boundary already selects the correct source sheet for the overlapping period. You should validate that choice rather than deduplicate again.
New uploads: L09_L10_retail_asof_2011_12_01.csv, L09_Selected_Customer_Register.csv, L09_Merchandise_Classification_v1.csv, L09_RFM_and_Eligibility_Policy_v1.json, L09_Data_Dictionary_and_Method_Card.pdf, L09_Source_and_Reuse_Notice.txt, L09_Input_Manifest.json.
Copy this prompt unchanged for your first attempt.
Act as my data-analysis partner. I am an Excel-comfortable consultant with no programming experience. We are segmenting customers for proportionate account-management follow-up as of 1 December 2011. Use the attached Lesson 9 files and execute calculations; do not invent results. First list the seven attachments, their roles and which files you can actually read. Read the policy and method card before analysis. The manifest inventories six payload files and deliberately omits its own hash; still inventory all seven attachments. The PDF contains the necessary dictionary; its companion CSV is optional reference material.
Read the retail CSV with all eleven original fields as strings, including blanks. Preserve every value, exact StockCode case, row order and source_sheet/source_row/source_record_id. Compute its SHA256 from the uploaded bytes and compare with 599542edb2c2e5f4202af87ecc4395dcc21ede0556ee7860303fc42c28ba08dc. Verify 63,734 data rows, 11 fields, 8,465,618 bytes and 500 normalized customer IDs, with no source-record-ID duplicates. Check exact membership against the supplied selected-customer register and one-to-one exact StockCode mapping. Do not trim or uppercase a source field.
Verify the already-applied canonical sheet partition and no timestamp on or after 2011-12-01. Report the 2,390 December 2009 rows, the 32,082 rows in [2010-12-01, 2011-12-01), date limits, countries and all missing/invalid fields you observe. These are controls to test, not assumed findings. Do not filter the source again or remove duplicate candidates. Explain which checks use the uploaded CSV and which parent-source/customer-selection claims can only be read from the supplied provenance; do not claim to have rechecked the full original workbook if it is not attached. Do not access or request post-cutoff outcomes.
Return a concise inventory, control table, classification coverage summary and any blockers. If a source hash, customer-membership or mapping-cardinality control fails, stop before RFM and show the discrepancy. Save My_L09_Input_Audit.txt. Do not segment customers yet.
Checkpoint 1: continue only when the exact source hash, shape, customer membership, cutoff and lookup-cardinality checks pass. Distinguish direct inspection of the uploaded CSV from the supplied provenance controls for the much larger parent workbook. A mismatch is a reason to investigate, not an instruction to delete rows until a count fits.
Stage 2 Create an eligibility trail
Cleaning for a customer analysis means adding a documented view. The source remains intact. A complete invoice can contain both eligible merchandise and excluded administrative lines. One qualifying positive merchandise line can make that invoice a purchase; the other lines still belong in the evidence. Negative rows contribute to eligible net monetary value when the policy permits them, while their signed amounts remain visible separately.
No new uploads. Continue in the same conversation.
Copy this prompt unchanged for your first attempt.
Use the verified source from Stage 1 and the supplied L09-RFM-v1.0 policy. Execute the eligibility analysis, keeping all 63,734 observations and all eleven original field strings unchanged. Add derived fields separately. Parse Quantity and Price as finite decimal values and calculate their product without line rounding. Leave unknown amounts unknown. Use recorded timestamps without a timezone conversion.
Create a complete row-level ledger with exact source lineage, a file-hash-bound row ID, window flag, exact classification/version, mutually exclusive disposition and independent diagnostic flags. Apply the policy's disposition precedence. The reference ledger has 41 fields; you may add a classification-version column. Verify the required evidence and original fields rather than forcing an identical total column count. Identify positive merchandise purchase rows, signed negative merchandise return/credit candidates, nonmerchandise, unresolved codes, nonpositive price, zero quantity, cancellation-sign conflicts and unknown values. A cancellation or negative quantity is not automatically an error. Treat positive C-prefixed merchandise lines as sign conflicts under the stated policy. Preserve every duplicate candidate occurrence; report groups, all members and excess occurrences separately and do not describe candidates as proven duplicates.
Reconcile source rows and known signed amounts to exhaustive disposition categories. Keep unknown counts beside known subtotals. Reconcile the window and before-window populations without summing overlapping flags. Report positive amount, signed negative amount, absolute negative amount and net for eligible window merchandise. Keep unresolved and excluded amounts visible. A whole invoice stays in the ledger even when only some lines qualify.
Build an invoice audit keyed by normalized customer ID plus exact source Invoice. Include every invoice and its full line count, eligible purchase-line count, last qualifying purchase timestamp, positive and signed negative eligible amounts, window status and excluded-line counts. Frequency will count distinct eligible invoices, never lines. Report any customer/invoice or mapping conflicts and how they affect eligibility. Save My_L09_Row_Disposition_Ledger.csv, My_L09_Invoice_Audit.csv and My_L09_Reconciliation.csv. Give a plain-language explanation of the policy and a few actual traceable exceptions. Do not calculate segments yet.
Checkpoint 2: the mutually exclusive row dispositions must sum to all source rows. The dispositions for in-window rows must sum to 32,082 rows. Diagnostic flags such as cancellation, negative quantity and duplicate candidate can overlap; their counts are not an additive row reconciliation. Unknown amounts must have their own counts even when the observed count is zero. Keep the returned CSVs, including all excluded and unresolved rows.
Stage 3 Build the customer measures and segments
RFM has three different units. Recency is days, frequency is distinct invoices and monetary value is pounds sterling. A negative credit does not erase the fact that a qualifying positive invoice occurred. Likewise, a customer can have a known zero net amount and still have a purchase history.
We use calendar dates for recency: choose the latest qualifying timestamp inside the trailing year, take its date, then subtract it from 1 December 2011. We do not floor an elapsed time interval. A customer with no qualifying purchase in this year receives a separate label; an older purchase is not substituted into the score.
No new uploads. Continue in the same conversation.
Copy this prompt unchanged for your first attempt.
Continue with the checked ledger and invoice audit. Keep every one of the 500 selected customers in the customer table, including those with no qualifying purchase. Use only [2010-12-01, 2011-12-01) for RFM. Recency is the calendar-date difference between 2011-12-01 and the latest qualifying purchase date in this window; ignore time of day after selecting the latest purchase. Leave recency blank if no qualifying purchase. Frequency is the distinct qualifying exact invoice count. Monetary is signed eligible window merchandise net amount, with positive and signed negative components and return/credit rows separately visible. Show a known subtotal and leave full monetary blank when the policy's unknown-review condition applies. Review incomplete data takes priority over No qualifying purchase. Do not use an older purchase to fill window recency.
Use the same complete customer population with frequency greater than zero for all R, F and M quartiles. Calculate Q25, Q50 and Q75 using PERCENTILE.INC/linear interpolation: for sorted n values, h=(n-1)*p, interpolate between zero-based positions floor(h) and the next position. Use right-closed intervals x<=Q25; Q25<x<=Q50; Q50<x<=Q75; x>Q75. Recency scores are 4,3,2,1 in ascending intervals. Frequency and monetary scores are 1,2,3,4. Never split equal values by customer ID or row order. Report cutoffs, equality counts, four band counts, distinct cutoffs and occupied bands; explicitly report any empty bands.
Apply the policy's ordered mapping: incomplete review first, then no qualifying purchase; otherwise Core repeat if R>=3 AND F>=3 AND M>=3; otherwise Recent developing if R>=3; otherwise Re-engage established if F>=3 OR M>=3; otherwise Occasional older. Demonstrate all 64 possible complete score combinations map exactly once. Create a customer CSV, segment summary, quantile/tie tables and a small action matrix with proportionate follow-up suggestions. Distinguish recorded observations, calculated measures and teaching judgments. Reconcile 500 customers, invoices, positive/negative/net amounts and unknown coverage back to Stage 2. Use a small sorted sample of actual customer observations to demonstrate the interpolation convention.
Save My_L09_Customer_RFM_and_Segments.csv, My_L09_Segment_Summary.csv, My_L09_Quantile_Cutoffs.csv, My_L09_Score_Bands.csv and My_L09_Action_Matrix.csv. Show at least five actual customer/invoice traces, including a no-purchase case if present and a return/credit case. Do not claim profitability, lifetime value, churn, response or causal targeting benefit.
Checkpoint 3: all 500 customers must remain in the customer file. Check the common scoring population and the three quartile tables. Equal values must receive equal scores, even when the resulting groups have unequal sizes. The rule order matters: once a customer matches a rule, later rules do not reassign it. Read the action matrix as proposed investigation, not proof of a profitable or effective intervention.
Stage 4 Practice with a second rule set
Before opening the instructor notes, predict which kinds of customers might move when “recent” means a larger fixed number of days or “frequent” requires more orders. Write your prediction in one paragraph. Then use the fixed practice inputs; do not tune them after seeing the result.
New uploads: L09_Practice_Thresholds_v1.json, L09_RFM_Segment_Mapping_v1.csv.
Copy this prompt unchanged for your first attempt.
Run the Lesson 9 practice on exactly the same 500 customers and the same scored subset, source rows, eligibility, RFM values and segment priority. Read the two new uploads. The business thresholds were fixed before the results: ascending recency boundaries 30,90,180 days; frequency boundaries 1,3,6 distinct invoices; monetary boundaries GBP250,GBP1000,GBP2500. All intervals are right-closed and score directions stay unchanged. Do not optimize these thresholds or use future outcomes. Verify that the supplied complete-score mapping contains 64 unique combinations and matches the ordered rules; handle incomplete/no-purchase cases separately.
Calculate business-threshold scores and segments alongside quantile scores and segments for every customer. Produce a customer-level transition table with old/new segment, changed flag, both score triples, RFM measures, positive/negative/net amounts and old/new proposed action. Produce a complete transition matrix including zero cells; segment counts and signed monetary coverage under both schemes; changed and unchanged customer counts for all 500 and for the scored subset; and the full changed-customer ID list. Reconcile all totals and show that only the rules, not the population or monetary amount, changed.
Explain at least three actual customer changes, which boundaries explain them and how the suggested account-management follow-up differs. Distinguish a rule-sensitive label from evidence that one method is more effective. Save My_L09_Practice_Customer_Transitions.csv, My_L09_Practice_Transition_Matrix.csv, My_L09_Practice_Segment_Summary.csv and My_L09_Practice_Answers.txt. Do not inspect the instructor answers until you finish your own calculation.
Your practice deliverable is a short comparison supported by the complete customer transition table. State both rule definitions, show customer and monetary coverage under each, list the customers who change, and explain at least three individual changes. A satisfactory answer also identifies the assumptions that have not changed. The instructor copy contains the independent answer.
Stage 5 Prepare an Excel and client handoff
Keep the client view compact. A segment table and a few well-labeled charts should explain the groups, their coverage and their sensitivity to the chosen rules. Preserve the full evidence in accompanying CSVs so a reviewer can reproduce the totals without navigating a very wide transaction worksheet.
No new uploads. Continue in the same conversation.
Copy this prompt unchanged for your first attempt.
Build a simple Excel workbook from the verified prepared customer aggregates. Keep the full transaction and invoice evidence in the CSVs. Include a segment summary and action matrix, a customer table containing all 500 customers, an explicit threshold/definition section, both rule sets and a transition matrix, and reconciliation checks. Use live formulas for recency where a last-purchase date exists, net amount with unknown guards, quartiles, scores, segment assignment, counts, monetary totals and transitions. If any cannot be live, disclose the limitation instead of implying the workbook recalculates it. Use familiar formulas and visible intermediate columns. State which inputs are prepared summaries and that editing the workbook does not reread the CSV ledger. Show missing or invalid threshold/input cases explicitly. Blank or nonnumeric business thresholds and boundaries that are not strictly increasing must trigger a visible review status rather than a normal segment. Repeated quantile cutoffs are allowed ties and must remain visible, not be treated as an invalid business-threshold input. Retain blank unknowns and meaningful zeroes. Keep identifiers as text.
Create publication-ready PNG exhibits with the exact underlying CSV values: a segment customer-count and signed-net profile, R/F/M score-band occupancy showing ties, and the quantile-to-business-threshold customer-transition matrix. Label GBP, the cutoff/window, populations, sample sizes, right-closed boundaries and any display restriction. Do not use a pie chart for signed net amounts. Do not round data before calculations or silently omit customers.
Write a concise client interpretation connecting observed segment sizes and recorded monetary coverage to proportionate investigations, explaining unresolved classification, rule sensitivity and what the data cannot establish. Separate suggestions from measured results. Supply My_L09_Customer_Segmentation_Workbook.xlsx, three PNGs, underlying chart CSVs and My_L09_Client_Interpretation.docx. Reopen or inspect exported artifacts where tools permit, scan formulas and check every chart value against the tables. Report actual calculation/artifact tests separately from native Excel and human usability testing. Do not claim any test you could not run.
Checkpoint 5: open each generated file. Inspect labels, units, signs, sample sizes, zero categories and formula cells. The workbook must say where prepared inputs stop and live formulas begin. A source edit, changed cutoff or revised merchandise decision requires rebuilding the prepared tables. Editing only a displayed threshold is a different, limited sensitivity exercise.
Stage 6 Compare with the released references
Complete your own analysis first. Then take the five reference CSVs listed below from 03_reference/data in the complete package and attach them. If you have only the learner ZIP, download the complete package for this final comparison. Keep your own files so differences remain reviewable.
New uploads: L09_Customer_RFM_and_Segments.csv, L09_Quantile_Cutoffs_and_Ties.csv, L09_Score_Distribution.csv, L09_Customer_Segment_Transitions.csv, L09_Count_and_Amount_Reconciliation.csv.
Copy this prompt unchanged for your first attempt.
I have completed my own analysis and have now attached five released reference CSVs. Preserve my outputs and compare them against these references by stable source/customer keys and exact definitions, not row order. Match every customer measure, score, segment and practice transition; all quantile and tie controls; and all count and signed-amount reconciliations. Compare exact decimal amounts where possible and explain any float display tolerance separately. Do not overwrite a mismatch merely to match the answer.
For each difference, identify the customer/source records, the two values, the policy interpretation causing it and the correction if justified. Recheck affected outputs after any correction. Confirm that the source hash and all eleven source fields remain unchanged, all 500 customers remain visible, no post-cutoff rows entered the analysis and duplicate candidates were retained.
Give exact Microsoft Excel verification instructions using my workbook's actual tab names and cell addresses. Include a distinct-invoice check, last-purchase/recency check, positive plus signed negative equals net, PERCENTILE.INC calculation, tied-value check, changed-customer count and a reversible practice-threshold edit/restore check. Explain how to import ledger evidence with identifiers/source text preserved and numeric helper fields separate. Do not claim to have opened Excel unless you actually did. Save My_L09_Verification_Report.txt and clearly separate completed checks, unresolved discrepancies and tests left for me to perform.
Verify the reference workbook in Excel
These addresses refer to the released L09_Customer_Segmentation_Workbook.xlsx, not to a workbook that ChatGPT happens to lay out differently. Use Prompt 6 to get equivalent addresses for your own workbook. Save a separate test copy. The four sheets are Segments, Thresholds, Customers and Checks.
1. In Excel, choose Formulas, Calculation Options, Automatic, then Calculate Now. Record your platform/version and the result. Production checks did not run native Microsoft Excel; this is your application test.
2. On Customers, rows 8 to 507 hold 500 customers and row 508 is a total. Column A is a text identifier. Never include the total row in a percentile. C508 is the sum of distinct qualifying customer-invoices; it must be 1,534. This is not the 31,441 qualifying positive line occurrences.
3. D508 is £588,654.17 of eligible positive amounts and E508 is -£85,724.21 of eligible negative amounts. G508 is their signed sum, £502,929.96. H508 is the complete amount because F508, the unknown-review count, is zero. T508, U508 and V508 reconcile eligible rows, raw window rows and full-history rows.
4. Customer 12346 is row 8. Check B8 = 18 January 2011, C8 = 1, D8 = £77,183.60, E8 = -£77,183.60, H8 = £0 and I8 = 317 days. Find invoices 541431 and C541433 in the invoice register. The two amounts offset, but frequency remains one. Do not infer a physical return or cash refund without further evidence.
5. On Thresholds, C8:E10 contains live PERCENTILE.INC formulas for R, F and M. The corresponding customer inputs are X8:X507, Y8:Y507 and Z8:Z507, containing 381 numbers apiece and blank special-status rows. As an independent Excel check, enter =PERCENTILE.INC(Customers!Y8:Y507,0.25) in an empty cell of your test copy. The first frequency cutoff is 1 invoice. H9 reports 131 customers exactly at that cutoff. B35:E35 reports frequency band sizes 131, 68, 108 and 74; this inequality is intentional.
6. On Segments, B8:B13 shows the six quantile segment counts and F8:F13 the six business-threshold counts. B21 is 109 changed customers; B22 is 391 unchanged. The complete transition matrix B33:H39 includes the workbook’s zero-population threshold-error guard and sums to 500 in I40. These are customer counts, not transactions or money.
7. On Checks, F6:F56 should show PASS on the unchanged release. Each row has an expected control and tolerance. A deliberate threshold change may make a frozen-release comparison fail even when your alternative calculation is valid; restore the release inputs before comparing again.
For a reversible guard test, set Customers!F8 to 1 in the test copy. Known net G8 remains zero, full net H8 and scores become blank, and both segment columns show Review incomplete data. Restore F8 to zero. On the no-purchase customer at row 13, the same edit must give incomplete review priority over no purchase. To test a missing threshold, blank Thresholds!B16, observe the explicit warning, then restore 30. Do not save the test copy over the release.
To inspect CSV evidence in Excel, use Data, From Text/CSV, then Transform Data. In Applied Steps, remove any automatic Changed Type step first so an earlier conversion cannot alter the source strings. Set all eleven original source columns, customer IDs, invoices and StockCode to Text before loading. Keep those columns intact. Convert only the separate derived measure/count columns needed for a check. For a selected customer, filter the invoice register to counts_as_qualifying_invoice = 1 and count its invoice rows; the register already has one row per customer-invoice. Cross-check the count against Customers column C. In the ledger, sum recorded_amount_gbp only where the matching customer has monetary_eligible = 1; compare positive and negative components separately. Use the original strings and source-record IDs for tracing, not an Excel auto-converted copy of the raw CSV.
Explain the result to a client
A defensible client statement gives the population, window, rule set, count and recorded amount, then proposes an investigation proportionate to that evidence. It also explains rule sensitivity and classification exclusions. Avoid calling a segment “high profit,” “loyal” or “churned” unless additional evidence supports those claims.
In a client project, replace the teaching classifications and thresholds with explicit, reviewable business definitions. Preserve a comparable baseline before changing them. Validate customer and invoice identifiers, source completeness, known versus unknown values and timing afresh; this public dataset does not validate a client’s systems. Add cost-to-serve, margin and service context before interpreting economic value, and test an intervention before claiming its benefit.
Submission checklist
- Source and policy inputs remain unchanged; the full row ledger, invoice register and 500-customer table reconcile with unknowns visible.
- Quartiles, ties, special statuses and all 64 score combinations are explicit.
- Practice includes both rules, all customer transitions, monetary coverage and three traced examples.
- Workbook, chart values, exhibits and client note agree; the verification report records actual checks, fixes and unperformed tests.
Source: Chen, D. (2012), Online Retail II, UCI, CC BY 4.0. The source notice identifies adaptations. Official guidance: ChatGPT data analysis and Excel PERCENTILE.INC.
