Historical KPIs in a living spreadsheet

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.

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.

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.

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.

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.

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.

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.

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.

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.