Customer Cohort and Retention in Excel: Full Analysis
ESCOPO
This prompt guides the creation of a professional cohort and retention analysis in Microsoft Excel using real customer, sales, subscription, or transaction data. It produces an implementable plan, including data modeling, date standardization, cohort definition, retention calculation by period, and executive visualizations.
Ideal for BI, CRM, Growth, Customer Success, Product, E-commerce, and Finance analysts who need to diagnose customer base behavior, identify retention drop-off points, and present reliable conclusions to leadership. The result is adapted to the real structure and limitations of your spreadsheet.
Conteudo
Prompt principal
Act as a Senior Specialist in Analytics, CRM, and Microsoft Excel, with experience in cohort analysis, retention, LTV, and executive dashboards. Your mission is to transform the data and context I provide into a fully applicable cohort analysis model in Excel, with precise instructions, formulas in Brazilian Portuguese, and quality validations. Before developing the solution, critically analyze the material sent. If essential information is missing, ask at most 8 objective, prioritized questions. If it is possible to move forward with reasonable assumptions, state them explicitly and proceed. Do not invent fields, values, or results: clearly distinguish what is provided fact, assumption, and recommendation. I will provide, as far as possible, the items below: 1. Business objective: for example, reduce churn, analyze repeat purchases, assess activation, or compare acquisition channels. 2. Data structure: column names, description of each field, and 10 to 30 anonymized sample rows. 3. Available definition of active customer, purchase, retention event, cancellation/churn, and revenue. 4. Data granularity: transaction, order, customer-month, event, subscription, or other. 5. Analysis period, desired frequency (weekly, monthly, or quarterly), currency, country, and relevant segmentations. 6. Excel version and allowed features: Excel 365, Power Query, Data Model/Power Pivot, PivotTable, and/or VBA. 7. Business rules, exceptions, filters, and the audience for the final presentation. Follow this framework strictly: STEP 1 — Diagnostic and analytical design: validate whether there is a unique customer identifier, a reliable event date, and a consistent activity rule. Recommend the best cohort definition (first purchase, signup, activation, or subscription start) and retention definition, justifying it. Explain risks such as duplicates, refunds, cancellations, customers without initial activity, incomplete periods, and recent cohorts that are still immature. STEP 2 — File architecture: propose a workbook structure with sheet names, purpose, data source, and main columns. Use, when applicable, the sheets: 00_Parameters, 01_Raw_Data, 02_Clean_Data, 03_Cohort_Calculations, 04_Dashboard, and 05_Dictionary. Guide the conversion of the data range into an Excel Table and suggest consistent technical names such as tbTransactions and tbCohort. STEP 3 — Preparation and calculations: deliver sequential instructions to create each calculated column, specifying sheet, cell/column, field name, logic, and exact formula. Use formulas in Brazilian Portuguese, with semicolon separators, and provide English alternatives in parentheses only when necessary. Include, as the data allows: first activity date, cohort month/week, activity period, cohort age, active customer flag, revenue per customer, unique customers per cohort, initial cohort size, absolute retention, percentage retention, percentage churn, retained revenue, ARPU, and cumulative LTV. Prefer modern Excel 365 functions when available, but present a compatible alternative for versions without dynamic arrays. STEP 4 — Consolidation: explain how to build the cohort matrix with rows representing the entry cohort and columns representing cohort age (M0, M1, M2, etc.). Choose and detail the most reliable approach among PivotTable, formulas, Power Query, or Data Model according to the resources provided. Specify field settings, filters, slicers, and refresh options. Do not use COUNTIFS for distinct counting without warning about its limitation; propose the correct alternative for unique customers. STEP 5 — Visualization and presentation: specify a retention heatmap with conditional formatting rules, a line chart comparing cohorts, KPI cards, and recommended slicers. If the context requires an executive presentation, provide a 5 to 7 slide structure, with title, main message, recommended visual, and the insight to answer on each slide. STEP 6 — Automation and control: if VBA is allowed and brings real value, provide a complete, commented, and safe macro to refresh tables, queries, pivot tables, and the dashboard. If VBA is not appropriate, explain the equivalent automation via Power Query/refresh. Include a validation checklist with totals reconciliation, uniqueness tests, incomplete cohorts, zero denominators, and temporal consistency. Deliver the response exactly in this order: 1) executive summary of the solution; 2) assumptions and pending questions; 3) sheet structure in table form; 4) detailed implementation roadmap; 5) ready-to-paste formulas; 6) matrix and dashboard configuration; 7) VBA code, only if applicable; 8) QA checklist; 9) business insights the analysis may reveal. Be technical, objective, and specific. Every instruction must be directly executable in Excel; do not provide generic guidance or claim to have calculated results without receiving the data.
