The client decision
A retailer wants to know whether customers first observed in different months return at different rates. You will build a cohort view that supports further retention investigation while accounting for unequal follow-up. A cohort is a group of customers with the same first-observed purchase month. The work ends with a transparent Excel workbook and evidence-linked questions for the client.
You need Excel familiarity, not programming experience. ChatGPT will execute the calculations and produce evidence files; you will specify the rules, inspect the evidence and check the result in Excel. The blueprint allocates 55 minutes. That is an unvalidated planning estimate, not a measured completion time. Reading, uploads, calculations and checking can take longer; work in stages.
What you will learn
- Define a purchase event from complete invoices while preserving the original source records.
- Assign first-observed cohorts from the full history and distinguish them from verified acquisition.
- Count distinct active customers, use the original cohort denominator and keep unobservable months blank.
- Compare Month 1 activity with a precisely defined 90-day repeat measure, separating changes in definition from changes in denominator.
- Trace a result to actual customers and invoices, and communicate what the sample can and cannot establish.
Use the guide in either order
For sequential study, this lesson builds on Lesson 9’s source-preservation and classification work. Start a fresh ChatGPT conversation; the files contain the necessary context. Do not carry forward Lesson 9’s trailing-year RFM filter. Cohort assignment needs every available pre-cutoff purchase, including December 2009.
For a client-project reference, use the readable cohort policy and standalone method card first, then follow the six prompts. The same workflow applies to another dataset only after confirming its identifiers, event rules, observation window, permission to use the data and customer population. The frozen lesson thresholds are teaching choices, not client targets.
Read a cohort cell correctly
M0 is the calendar month of the first observed qualifying purchase. M1 is the next calendar month and M3 is three calendar months later. A customer can buy several times in one month but contributes at most one active customer to that cell. A zero means no qualifying activity was observed during a complete month. A blank with Unobservable status means the month lies beyond the evidence window. An inactive month can be followed by activity; it does not establish churn.
All rates use the original assigned cohort size. M0 is baseline activity and is not evidence of repeat purchasing. The December 2009 source-start group is displayed separately because earlier history is unavailable. Every other cohort is also first observed, rather than proven acquisition.
Prepare the files and conversation
Extract the complete ZIP to a local folder. The smaller Learner Files ZIP contains only the starting material and practice policy. Start with 00_read_first and 01_start. Keep 03_reference closed until you have attempted the analysis. 05_instructor_only contains the parent archive and later observations; do not upload it to your learner conversation.
Open a fresh ChatGPT conversation with file analysis available. Upload the seven individual files below from 01_start; do not upload the whole publication ZIP. Wait for each attachment to finish. If your interface limits attachments, attach them in batches in the same conversation before sending Prompt 1. Confirm all seven are present by filename. Feature availability varies by account; a text-only reply without executed calculations does not complete this lesson.
| Starting file | Purpose |
|---|---|
| L09_L10_retail_asof_2011_12_01.csv | Exact frozen observations and lineage |
| L09_Selected_Customer_Register.csv | Fixed 500-customer membership |
| L09_Merchandise_Classification_v1.csv | Unchanged exact-code teaching lookup |
| L10_Qualifying_Purchase_and_Cohort_Policy_v1.json | Frozen eligibility, event, cohort and comparison rules |
| L10_Data_Dictionary_and_Method_Card.pdf | Standalone definitions and field dictionary |
| L10_Source_and_Reuse_Notice.txt | Attribution, provenance, precision and limitations |
| L10_Input_Manifest.json | Hashes of the six payloads above |
The raw CSV must have 63,734 rows, 11 fields and 500 normalized customer IDs. Its SHA256 is 599542edb2c2e5f4202af87ecc4395dcc21ede0556ee7860303fc42c28ba08dc. The manifest intentionally omits its own hash. The optional L10_Field_Dictionary.csv repeats the PDF’s definitions in a filterable format; it is not an extra required Stage 1 upload.
Use L10_Exact_Prompts.txt to copy each prompt without Word line-wrap problems. Continue the six stages in the same new conversation. Save each response and downloaded output into your own working folder before continuing. If a tool reports an error, keep its text, ask for diagnosis and require the affected checks to be rerun. Do not accept a substituted source or deleted records merely to match expected controls.
Source observations, analytical results and teaching conventions have different roles. Original invoice fields are observations. Cohort assignments and rates are calculated results. Merchandise labels, the 20-member comparison minimum and 90-day interval are teaching conventions. No retailer or human approval is implied.
Stage 1 Verify the frozen inputs
Use the seven starting attachments. Copy this prompt unchanged.
Act as my data-analysis partner. I am a management consultant comfortable with Excel and with no programming experience. We want to compare repeat purchasing across first-observed customer cohorts as of 1 December 2011. Read the attached policy and method card, execute calculations, and distinguish checked facts from supplied provenance. Do not invent results or request future outcomes.
Inventory all seven attachments and verify that you can read them. The manifest hashes six payload files and deliberately omits its own hash. The method PDF contains the necessary field definitions; its companion CSV is optional reference material. Read all eleven original retail fields as strings, preserving blanks, exact StockCode case, row order and source_sheet/source_row/source_record_id. Compute SHA256 from the uploaded bytes and compare with 599542edb2c2e5f4202af87ecc4395dcc21ede0556ee7860303fc42c28ba08dc. Test 63,734 rows, 11 fields, 8,465,618 bytes, 500 normalized customer IDs and unique source-record IDs. Check exact customer membership against the register and unique exact-code coverage against the classification.
Verify the already-applied canonical sheet partition: Year 2009-2010 before 2010-12-01, Year 2010-2011 from that date. Confirm no timestamp on or after 2011-12-01. Check the 2,390 December 2009 rows and full pre-cutoff date coverage. Do not apply Lesson 9's trailing-year RFM filter. Preserve all source rows and duplicate candidates. Report missing/invalid fields, countries and any classification conflicts. Explain which checks are possible from the attached files and which full-parent selection/history assertions remain supplied provenance. Do not claim to have rechecked the 5,244-ID parent selection pool without the parent workbook.
Explain why first observed purchase is not verified acquisition, and why the fixed selection rule makes this a retrospective selected sample, particularly for December 2009. Return an inventory/control table and any blocker. Stop before analysis if the source hash, membership or lookup-cardinality controls fail. Save My_L10_Input_Audit.txt. Do not calculate cohorts yet.
Checkpoint before proceeding
Look for an executed hash/shape/membership audit, not just a restatement of this guide. The source hash, 500-ID register, exact-code lookup cardinality and cutoff controls must pass. Read any exceptions. The learner files can validate their own contents; they cannot independently prove the full 5,244-ID parent pool. That stronger parent check belongs to the supplied production provenance.
The canonical sheet partition has already been applied. Applying another overlap deletion or restricting the raw rows to December 2010 onward would change the intended history. Save the input audit before Stage 2.
Stage 2 Create purchase and invoice evidence
No new upload. Continue in the same conversation with this prompt.
Use the verified full pre-cutoff history and the frozen L10-cohort-v1.0 policy. Create an all-row disposition ledger preserving all 63,734 observations and all eleven original strings. Add derived customer key, file-hash-bound source-row lineage, parsed timestamps and finite Decimal quantities/prices separately. Recorded amount is Quantity times Price without line rounding; unknown amounts remain unknown.
Apply the policy's exact precedence for time scope, country, missing invoice key, unknown numeric amounts, classification and sign eligibility. A qualifying purchase is an in-scope United Kingdom identified-merchandise row with known positive quantity and price and a non-C-prefixed invoice. Keep cancellations, signed negative merchandise, nonmerchandise, unresolved M/TEST002 and all other exclusions visible. Report unknown counts beside known subtotals. Keep every duplicate-candidate occurrence, reporting groups, members and excess occurrences separately; candidates are not proven duplicates. Reconcile mutually exclusive row dispositions and signed amounts without adding overlapping diagnostic flags.
Build one complete invoice-audit row per normalized customer ID plus exact source Invoice. Keep every original line in its invoice group. A qualifying invoice has at least one qualifying positive line, and its event timestamp is the earliest qualifying positive line timestamp. Retain first/last qualifying timestamps and flag disagreements or cross-month groups. Do not split or merge invoice keys to simplify time handling. This event convention is specific to Lesson 10 and differs from Lesson 9's last-purchase recency calculation.
Report the conservative customer unknown-review flag defined in the policy. Unresolved merchandise codes remain outside the identified-merchandise purchase scope with visible counts; they do not become invented zero amounts. Save My_L10_Row_Disposition_Ledger.csv, My_L10_Invoice_Register.csv and My_L10_Count_and_Amount_Reconciliation.csv. Explain actual traceable exceptions and reconcile invoice membership to every source row. Do not assign final cohorts until these checks pass.
Checkpoint before proceeding
The ledger must contain every original row and eleven unchanged source fields. Ask for the mutually exclusive disposition total and the amount reconciliation, with unknown counts beside known subtotals. Negative rows, unresolved codes and duplicate candidates remain in the evidence. A complete invoice can include both qualifying and excluded lines; only its positive qualifying lines establish the purchase event.
Inspect the earliest/last qualifying time for invoices with disagreements. This lesson treats one complete customer/invoice group as one event; different line timestamps do not create extra purchases. Save both ledger and invoice register. If the source or a grouping rule changes, rerun the affected preparation rather than editing a final cohort percentage.
Stage 3 Assign cohorts and build the activity matrix
No new upload. Use the full checked history.
Use the checked ledger and complete invoice events. Keep every one of the 500 selected customers. Apply the policy's customer-status priority: Review incomplete purchase evidence, then No qualifying purchase, otherwise Assigned. Preserve any known candidate first event separately when review is required. For each Assigned customer, find the earliest qualifying invoice event anywhere in the full available history before 2011-12-01. If different invoices tie at that timestamp, use the lexicographically smallest exact Invoice as the index identifier and report the tie. The cohort is the recorded event's calendar month, without timezone conversion.
Show December 2009 as a separate Source-start cohort. Show every January 2010-November 2011 cohort, including any zero-size group. Produce a complete customer-by-age grid for all 500 customers and ages 0-23, and a complete 24-cohort by 24-age cell table. For an Assigned customer, elapsed month is the calendar-month difference from the cohort. Active means at least one qualifying invoice event in that calendar month; multiple invoices count the customer once. Activity may resume after a zero month.
For each fully observable, nonempty cohort cell, show the distinct-active-customer numerator, original-cohort-size denominator and their ratio. Observed zero activity must be numeric zero. Calendar months after November 2011 must have explicit Unobservable status and blank numerator/denominator/rate. Zero-member cohorts have a separate status and blank rate. Unassigned/review customers keep explicit status and blank outcomes. Month 0 is baseline activity, not repeat-purchase evidence. Reconcile every customer to one cohort or an explained exception and every numerator to customer-level activity.
Use the frozen minimum of 20 cohort members and comparison age M3 before interpretation. Compare only January 2010-August 2011 cohorts with at least 20 members and fully observed M3; show December 2009 separately. Retain smaller groups with their denominators. If you show pooled rates, sum eligible numerators and denominators rather than average percentages. State which cohorts contribute at each age. Save My_L10_Customer_Cohorts.csv, My_L10_Customer_Month_Activity.csv, My_L10_Cohort_Cells.csv and My_L10_Cohort_Summary.csv. Trace several actual customers to their earliest events and month cells. Explain sampling, classification and first-observed-history limits without calling nonpurchase churn.
Checkpoint before proceeding
Require all 500 customers, including any explained exception. Check that each assigned customer has one first-observed month and that the first event can be traced to an actual invoice and source row. The complete grid has 12,000 customer-age rows and 576 cohort-age cells. These are grid-size controls; they do not mean all cells are observable.
Inspect one observed zero and one unobservable blank. The latter cannot be used in a comparison at that age. The predefined M3 comparison retains only January 2010–August 2011 cohorts with at least 20 members and observed M3; every smaller cohort remains visible. December 2009 is separate. Do not interpret percentages as evidence of causation or a prospective acquisition-retention curve.
Stage 4 Complete the practice
Upload L10_90_Day_Practice_Policy_v1.json from 02_practice. Do not open the instructor answer notes yet.
Run the practice on the same 500 customers and the same qualifying invoice events. Read the new fixed practice policy. Use the same index event t0 and exact index invoice as the cohort analysis. Month 1 activity means a qualifying event in [first day of the next calendar month, first day of the following month). Month 1 has complete follow-up when its upper bound is no later than the exclusive cutoff.
Define a 90-day repeat as a different exact invoice with event timestamp strictly later than t0 and no later than t0 plus 90 recorded days: (t0, t0+90 days]. Do not count another line of the index invoice or another invoice at exactly t0 as a later repeat. Retain and disclose such ties. Use recorded naive timestamps and no invented timezone conversion. Require t0+90 days to be strictly before 2011-12-01 for complete 90-day exposure, because the endpoint is inclusive and the source cutoff is exclusive.
For fully exposed Assigned customers, code repeat as 1 or observed no repeat as 0. Leave censored, incomplete and unassigned final outcomes blank with distinct statuses. Even if a censored customer already has an observed repeat, keep that customer outside the complete-exposure rate; show the partial-window event only as provisional evidence. Do not obtain the instructor's future rows.
Report each metric on its own fully observed denominator, then compare them on the intersection with both windows complete. Produce the all-500 customer table, cohort-level denominator/censoring table, full common-population 2x2 transition table including zero cells, and exact changed-customer IDs. For the common population, a changed flag means Month 1 activity differs from 90-day repeat; elsewhere leave it blank with a reason. Reconcile all populations and rates. Explain actual same-month or later-month repeats that affect this comparison rather than assuming the windows mean the same thing.
Use the policy's deterministic five-customer trace selection and preserve full source/invoice timelines for those actual customers. Save My_L10_Customer_Practice.csv, My_L10_Practice_Summary.csv, My_L10_Practice_Transitions.csv, My_L10_Practice_Changed_Customers.csv and My_L10_Practice_Answers.txt. Explain which findings are sensitive to definitions and exposure. Do not tune the 90 days, minimum volume or cohort rules after seeing the result, and do not read the instructor answers before completing your calculation.
Write your interpretation before opening the answer
Explain why a customer can have no Month 1 activity and still repeat within 90 days. Explain why a censored customer with an already observed repeat remains outside this complete-exposure rate. Identify one actual changed customer, one actual no-repeat customer with full exposure, and one censored customer. Show the exact dates and invoice identifiers rather than an invented example.
The first definition has a calendar-month window; the second starts at an exact timestamp. Their own eligible populations can differ. Only the common complete population supports a direct customer-by-customer classification comparison. A change of classification does not establish a change in underlying behavior.
Stage 5 Prepare the Excel and client handoff
No new upload. Ask for the artifacts after the underlying calculations pass.
Create a simple Excel cohort workbook from the verified prepared customer and cohort-count tables. Keep the full transaction, invoice and customer-month evidence in accompanying CSVs. Include the cohort activity view with sizes and source-start/small-cohort flags, practice rates and denominators, all 500 customers, and reconciliation checks. Use live formulas where appropriate for cohort sizes, rates, observation/exposure bounds, 90-day outcomes, practice summaries and comparisons. State every prepared-data boundary: editing the workbook does not reread transactions or rebuild invoice events. A cutoff or eligibility change requires new preparation; do not imply a refreshed source. Identify any calculation that cannot be live.
Keep unknown, unobservable, censored, no-cohort-members and observed zero distinct. Show input problems explicitly instead of using IFERROR to turn them into zero. Keep customer and invoice identifiers as text and dates as sortable dates. Use original-cohort denominators for cohort activity and the stated complete-exposure denominators for practice. Retain every cohort and avoid unweighted average percentages.
Produce publication-ready PNGs and exact underlying CSV values for a cohort heatmap, cohort sizes, an observation-window explanation using actual customer dates, and the Month 1 versus 90-day comparison. Label sample, cutoff, elapsed months, numerator/denominator, small-cohort rule and blanks. Any future part of an observation-window diagram is a derived unobserved boundary, never a future transaction. Use a common scale for comparable rates and display December 2009 separately. Do not rank incomplete or small cohorts as winners or losers.
Write a concise client note naming evidence-linked follow-up investigations and the limits on acquisition, retention, churn and causal claims. Supply My_L10_Cohort_Workbook.xlsx, four PNGs, chart-value CSVs and My_L10_Client_Interpretation.docx. Reopen/inspect exported artifacts where available, verify every chart value, and record actual calculation, artifact, native-application and human-usability checks separately. Do not claim tests that were not run.
Inspect the handoff
Open each exported file. Check that the workbook names the prepared-data boundary and preserves blanks. Verify the heatmap’s source-start label, cohort sizes, age axis, fixed color scale and explanation of future cells. A rounded chart label must agree with its exact underlying count and denominator.
The client note should propose further investigation, identify the relevant cohort or customer evidence and acknowledge selection, merchandise scope and incomplete follow-up. A zero cell is not proof of churn, and an association between cohort and activity is not proof that a retention intervention would work. Record which artifact and calculation checks ChatGPT actually executed.
Stage 6 Compare with the released references
Save your own outputs first. Now upload these six CSVs from 03_reference/data:
- L10_Customer_Cohorts_and_Practice.csv
- L10_Cohort_Activity_Table.csv
- L10_Cohort_Summary.csv
- L10_Practice_Metric_Comparison.csv
- L10_Practice_Exposure_Reconciliation.csv
- L10_Row_and_Amount_Reconciliation.csv
Copy this prompt unchanged.
I have finished my own analysis and now attach six released reference CSVs. Preserve my outputs before comparing. Match customers by normalized ID, invoices by customer/exact Invoice and cells by cohort month/elapsed month, not row order. Compare every assignment, first event, activity numerator/denominator/rate and observability state; all practice bounds, eligibility, outcomes and changed-customer definitions; and the count/signed-amount reconciliations. Check integer counts and original strings exactly, and explain any separately declared floating-point tolerance for ratios.
For any difference, show the actual source/customer/invoice records, both values and the rule interpretation causing it. Correct only a justified error and retest affected results. Confirm the original source hash and eleven fields are unchanged, all 500 customers remain visible, the December 2009 history was used, and no post-cutoff observation or future count influenced the analysis. Do not simply replace a mismatch with the reference answer.
Give precise Excel verification instructions using my workbook's actual sheet names and cell addresses. Include a cohort-size check, a distinct-active-customer cell check, an observed zero versus unobservable blank, a Month 0 baseline check, one customer index and repeat timeline, exact 90-day boundary/exposure checks, both practice denominators, and a reversible missing-input edit/restore test. Explain how to import CSV evidence using Transform Data, removing any automatic Changed Type step before setting original source fields and identifiers to Text. Keep numeric helper columns separate. State any native Excel test you could not execute. Save My_L10_Verification_Report.txt with completed checks, repairs, unresolved differences and tests left for me.
Resolve differences by rule and source
The reference customer table combines cohort and practice columns, while your own files may separate them. Compare by customer ID and cohort-month/age keys. A different label can be equivalent only if its eligibility, blank/zero behavior and numerical meaning agree. A changed denominator or event timestamp is a substantive difference. Document the cause and affected retest; do not overwrite your answer solely because a reference differs.
Verify the reference workbook in Excel
Use a copy of L10_Cohort_Workbook.xlsx from 03_reference. Open L10_Excel_Verification_Instructions.txt for the full procedure and exact record examples. Native Excel steps were not run in the production environment; your own opening and recalculation is a separate usability check.
1. Open the extracted workbook, select automatic calculation and recalculate. On Excel for Windows, Ctrl+Alt+F9 requests a full calculation. Record any repair warning before proceeding. The four tabs are Cohorts, Practice, Customers and Checks.
2. On Checks, inspect F6:F60. All 55 controls should say PASS. On Customers, rows 8:507 retain all 500 customers; =COUNTA(Customers!A8:A507) returns 500. The cohort sizes sum to 499 because one customer has no qualifying identified-merchandise purchase.
3. On Cohorts, January 2010 is row 11. B11 is 28, prepared M3 count G43 is 10, and G11 is 10/28, displayed as 35.7%. M3 corresponds to April 2010. Filter the customer-month evidence by cohort_month 2010-01, month_age 3 and active_customer 1; there must be ten distinct IDs.
4. Compare Cohorts E22, an observed zero for December 2010 M1, with E33, the unobservable M1 of November 2011. D33 is M0 baseline 100%. =COUNT(Cohorts!D10:AA33) is 300. COUNTA is unsuitable because formulas returning empty text are counted.
5. On Practice, compare the four ratios in A9:F13. Read both numerator and denominator. The two Month 1 percentages both display 21.4% but use different populations. The common-population transition table is B24:C25; D26 totals 439.
6. Inspect customer 14802 on Customers row 193. Its cohort and outcomes are blank with No qualifying purchase. The six original rows use unresolved M/Manual. This status is conditional on the merchandise policy; it is not proof that the person never purchased from the retailer.
7. Trace a date calculation and repeat invoice using the five-customer CSVs. Check index timestamp + 90, the strict cutoff comparison and the first strictly later exact invoice. Another invoice at exactly the index time does not qualify as a later repeat under this convention.
8. In a copy only, clear Cohorts E42. The row should flag missing/future count review and the affected rate should blank. Restore 29 and confirm the controls. Numeric zero is a different valid observed input. This reversible demonstration changes the workbook input, not the source history.
To inspect original CSV evidence, choose Data > From Text/CSV > Transform Data. Remove an automatic Changed Type step before setting original fields and identifiers to Text. Add numeric helpers separately. Do not double-click and resave the frozen source CSV, which can change formatting or precision. Use the supplied ledger when you need both original text and calculated fields.
Practice answer checkpoints
Read this section after completing your own practice. The instructor notes contain the complete answer, all cohort sizes, the five timelines, exceptions and calculation evidence.
| Measure | Numerator | Denominator | Rate |
|---|---|---|---|
| Month 1 own complete population | 102 | 477 | 21.3836% |
| 90 day own complete population | 213 | 439 | 48.5194% |
| Month 1 common complete population | 94 | 439 | 21.4123% |
| 90 day common complete population | 213 | 439 | 48.5194% |
The common population has 119 customers with Month 1 = 0 and 90-day repeat = 1, 94 with both = 1, 226 with both = 0 and zero with Month 1 = 1 and 90-day repeat = 0. The other 61 selected customers are outside the paired comparison. Sixty have incomplete 90-day follow-up and one has no qualifying purchase. Nineteen of the censored customers already have an observed repeat; the chosen complete-exposure rate still excludes them.
Customer 14492 first qualifies on invoice 503181 at 30 March 2010 14:23. April has no qualifying event. The first later invoice, 510786 on 3 June 2010 15:50, is before the inclusive 28 June 2010 14:23 endpoint. Month 1 is 0 and 90-day repeat is 1. Customer 15721 first qualifies on 28 November 2011 and cannot receive either complete follow-up outcome by the cutoff.
Finish with an evidence based recommendation
Name the specific cohorts or measurement questions to investigate, show their denominators and explain what additional client evidence would resolve the uncertainty. Do not describe first observed as acquisition, no purchase as contractual churn, or a longer measurement window as an improvement caused by targeting. The fixed sample also conditions selection on a purchase during January 2010–November 2011, especially affecting the source-start cohort.
Save your conversation, files and completed L10_Usability_Test_Log.txt. Record elapsed time as your own observation, the Excel platform/version, any changed prompt, error, repair and affected retest. A successful calculation does not by itself prove a fresh-chat or human usability test.
Source and further reading
Source: Chen, D. (2012), Online Retail II, UCI Machine Learning Repository, CC BY 4.0. Selection, serialization, classifications and analysis are disclosed adaptations. The source notice records exact hashes, reconstructed-recipe provenance and precision differences.
Use OpenAI data analysis guidance for current upload capabilities and Microsoft Power Query data types for CSV import behavior. Checked 24 October 2026. The next lesson is Lesson 11, Identify products purchased together; it is not included here.
