--- id: casos/kpis-historicos area: BI usage: common effort: medium grain: KPI x period (closed day or month) x load batch --- # Historical KPIs in a living spreadsheet A snapshot of each KPI per period, stored when the period closes and never rewritten, in a spreadsheet that updates itself and keeps the trend the source system forgets. ## Executive summary **For:** Companies that track KPIs month over month and today rely on screenshots, copied spreadsheets or a system that only shows the current value. **What it solves** - A closed month's number does not change after it is presented. - Sources that only keep the last few weeks stop erasing the past. - Automatic and manual KPIs live in a single series, with an owner and a definition. **Why it matters:** Many sources only show today's state or a short window: the CRM rewrites the stage, the social media API starts a few months back, the customer service tool keeps a few weeks. Recomputing history on every run rewrites the past, and August's meeting number changes in September. Without the snapshot stored on the day, there is no reliable trend. **Business questions it answers** - How have revenue, active customers and churn evolved over the last 12 months? - Is the August number we presented the same one in the spreadsheet today? - Which KPIs have been below target for three months in a row? - How many followers did we have at the end of each month last year? - Who owns each KPI and when was the last entry? **What you need** - The source metrics in gold tables or connected sources - A KPI catalog: name, definition, unit, owner and frequency - The destination spreadsheet or the trend dashboard **What you get** - An append-only KPI values table, with load batch and data status - A read view with the last valid value for each KPI and period - Frozen closed months, with corrections recorded as adjustments - The living spreadsheet with the series, updated after every load ## Detailed ### The problem The KPI spreadsheet is filled in by hand or recomputed from scratch on every run. When the CRM changes a deal's stage or a classification rule is adjusted, past months change too. When the API only returns the last few weeks, the history disappears. And the transformation that was meant to keep the snapshots recreates the whole table on every run. In the end, nobody trusts the series and every meeting starts by checking numbers. ### Business rules 1. **The snapshot is stored at close, not recomputed.** For each KPI and period, store the value when the period closes (previous closed day, closed month) in an append-only table. Recomputing the whole series on every run rewrites the past: in a real case, a qualification rule adjusted mid-month started applying to old sign-ups and a month that had already been presented shrank on its own. (Avoid: Recomputing every month from the current state of the sources.) 2. **SQL transformations do not keep history.** A SQL transformation on Nekt recreates the table on every run and would erase earlier snapshots. To accumulate, use a notebook writing in append or merge mode, or a query that reads its own output table and replaces only the current period. Refuse to write when the result is empty: an empty write wipes the series. (Avoid: A SQL snapshot that overwrites the whole table.) 3. **Short-window sources need an accumulator.** Analytics, product charts, social media and customer service tools often return only a moving window of days or weeks. Accumulate in your own table, one record per day or per id, never deleting, with first and last extraction dates. A rolling window does not repopulate the past: what was not stored is gone. That is why the accumulator starts today. (Avoid: Trusting the API to return history when you need it.) 4. **KPI catalog with a stable id.** Each KPI has a kpi_id that never changes, a name, definition, unit, value type, owner, area and frequency. An entry for a KPI outside the catalog is rejected. Changing the definition of a KPI that already has history requires an explicit decision and a series review, or a new KPI. (Avoid: Spreadsheet columns renamed every quarter.) 5. **Each load is a batch, and the last valid one wins.** Each run writes a batch with an identifier, a sequence number, who filled it in (routine or person) and when. A batch is all or nothing: one invalid row rejects the whole batch. Reading uses, per KPI and period, the value from the batch with the highest sequence. Rerunning the same batch writes nothing new. (Avoid: Deleting and rewriting the month's value when someone corrects it.) 6. **A closed month freezes in two rounds.** At close, store a provisional version and allow one correction, which becomes final. Later corrections come in as adjustments, with the reference period recorded, without rewriting the final number. Reopening a frozen month is a manual action, with a reason and an owner. (Avoid: Leaving the month open forever.) 7. **Data status on every value.** Store whether the value is ok, partial or missing. A day with no source load does not become zero: it stays missing, and a gap raises an alert. The current month comes in as partial and is not compared with a closed month. (Avoid: Storing zero when the source did not load.) 8. **Stock, flow and rates are stored differently.** Stock KPIs (active customers, followers, balance, MRR) are the value on the last day of the period. Flow KPIs (sales, leads, cancellations) are the sum over the period. Rates are stored with numerator and denominator so quarter and year can be recomputed. Currency-converted values keep the rate used for the month and freeze with it. (Avoid: Summing each month's active customers to get the year.) 9. **The spreadsheet only reads.** The series reaches the spreadsheet through reverse ETL, in automatic tabs that only the routine writes. Manual tabs (comments, targets) stay separate and are never overwritten. A manual KPI entry goes through the entries table, validated against the catalog, not typed into the automatic tab. (Avoid: The team editing numbers in the same tab the routine overwrites.) ### What each row of the series stores One row per KPI, period and batch. Reads take the last valid batch. - **Stable catalog identifier** → KPI (`kpi_id`) - **Closed reference day or month** → Period (`data`) - **Value, plus numerator and denominator for rates** → Value (`valor`) - **ok, partial or missing** → Data status (`status_dado`) - **Load identifier and sequence** → Batch (`batch_id, carga_seq`) - **Routine or person, and when** → Origin (`preenchido_por, preenchido_em`) - **Provisional, final or adjustment** → Close (`versao`) ### Definitions | Metric | Definition | |---|---| | KPI value | value of the KPI for a kpi_id and a period, written by a batch; reads use the valid batch with the highest sequence | | Stock KPI | value on the last day of the period: active customers, followers, balance, MRR | | Flow KPI | sum of events in the period: sales, leads, cancellations | | Rate | period numerator / period denominator, stored separately; quarter and year recomputed from the sums | | Closed period | previous day in local time or a finished calendar month; the current period is partial | | Frozen month | month with a final version stored; it only changes through a recorded adjustment | | Series coverage | periods with status ok / periods since the KPI started | ### Pitfalls - **The month shrank after the meeting.** A qualification rule recomputed on every run started applying to old sign-ups, and a month's number dropped after it was presented. How to avoid: Stamp the verdict on the day of the event and never rewrite it. - **One write erased weeks.** A run overwrote the snapshot table and about three weeks had to be rebuilt. How to avoid: Append mode and a guard against empty writes. - **The API only returned weeks.** The customer service tool only delivered the last few weeks of tickets, and the previous month had less than half of the real tickets in the table. How to avoid: An accumulator per id from the first load. - **Social media with no past.** A social network API only covered recent months; years of daily series existed only in a spreadsheet exported earlier. How to avoid: Load the history once as a frozen source and join it with the new series at a hard date cutoff. - **A full reload that erased months.** A full event reload failed halfway and erased months from the source table. How to avoid: Take a frozen copy before any full reload. - **A zero that was a missing load.** A business day with no records for any account showed up as zero in the series; it was a source load failure. How to avoid: Missing status, not zero, and a gap alert. ### Acceptance test - [ ] Running the routine again for a month with a final version changes no value in that month. - [ ] For each KPI and period there is exactly one value in the read view, and it comes from the batch with the highest sequence. - [ ] A closed month's value in the spreadsheet equals the one in the values table and the one presented at close. - [ ] A batch with a kpi_id outside the catalog is rejected entirely and none of its rows appear in the series. - [ ] The series for a short-window source has one record per day since the first load, with no gaps other than those flagged as missing. ### Context document ```text Historical KPIs - The official series is in ; read it through the view , which returns the last valid batch per KPI and period. - Each KPI has a kpi_id, definition, unit, owner and frequency in the catalog . Use the catalog definition. - Months with a final version do not change. Later corrections appear as adjustments, with the reference month. - The current month is partial: do not compare it with a closed month. - Stock KPIs (active customers, followers, balance, MRR) = value on the last day of the period; never sum months. - Flow KPIs = sum over the period. Rates: recompute quarter and year from numerator and denominator. - Missing status is not zero: say there is no data for that period. - Never recompute a closed period from the source tables to answer; use the series. Client exceptions: - : KPI changed definition on ; before that the series uses . - : history for before comes from . - : reporting exchange rate = monthly average from , frozen at close. ``` ### Variations - **Manual entry:** Leaders enter KPIs that have no automatic source through a routine that validates against the catalog. - **Target vs actual:** The target comes in as another value for the same kpi_id, with the plan version frozen. - **Weekly entity snapshot:** For fields the CRM does not version, a weekly snapshot of the deal alongside the KPI series.