KPI Reporting System

Structured metric definition for every KPI in the reporting system documented before any data work begins: the metric name, what business question it answers, the exact formula (expressed as SQL or as a plain-language specification that the data engineer translates to SQL), the data source and field mapping, how edge cases are handled (refunds included or excluded from revenue, trials included or excluded from active customer count, cancelled-then-resumed subscriptions counted as churned), the time dimension (is weekly revenue recognised at booking date, invoice date, or payment receipt date), and the team that owns the definition and can approve changes. Metric reconciliation workshop for contested metrics: when sales calls it bookings and finance calls it revenue and the two numbers differ, we facilitate a structured reconciliation that maps the formula each team currently uses, identifies the specific inputs that diverge (recognition timing, refund treatment, currency conversion rate), documents both the existing calculations and the agreed canonical definition, and gets sign-off from both department heads before the data layer is built. The canonical definitions are stored in a metric dictionary (dbt metrics YAML file, or a Notion/Confluence page with a structured schema) that is version-controlled and linked from every report so any recipient can find the definition for any number they are looking at without asking the data team. Change management: any change to a metric definition follows a structured process -- proposed change documented, affected reports listed, prior-period restatement requirement assessed, stakeholders notified, change approved before it is deployed to the reporting pipeline so it never appears in a report unexpectedly.

Data layer built on dbt (data build tool) models running in BigQuery, Snowflake, Redshift, or PostgreSQL -- depending on your data warehouse environment. Source data extracted from operational systems on a configured schedule (nightly for weekly/monthly reporting, hourly for daily operational reporting) via Fivetran, Airbyte, or custom Python ETL pipelines; raw data landed in a staging schema, then transformed through a series of dbt models following a medallion architecture: staging (source system fidelity, minimal transformation), intermediate (cross-system joins, standardised field names and data types), and mart (final metric calculations exposing the agreed definitions to the reporting layer). Each metric is implemented in its own dbt model with a clear SQL implementation, model-level documentation in the YAML schema file, data quality tests (not_null, unique, accepted_values, and custom SQL assertions for business-rule validation), and a source freshness check that alerts if the upstream source data hasn't refreshed within the expected window. Metric versioning: when a definition changes, the prior version is preserved as a deprecated model (prefixed with the version number) so historical reports can be regenerated consistently against the definition that was in effect when the original report was produced; the restatement decision (restate prior periods or present old and new side-by-side in a bridging report) is made by the metric owner and documented. Data quality gate: Great Expectations or dbt tests run before the report generation step; if critical quality checks fail (revenue source table has a 40% row count drop compared to yesterday, suggesting an ETL failure), the report generation job does not run and an alert fires to the data team with the failing test name and the threshold that was violated.

Report layout presenting each metric in a consistent comparison structure across every metric pack in the organisation: current period value, prior period value, same period last year value, and budget target (where budget data is available) in adjacent columns; absolute variance (current minus prior) and percentage variance columns calculated automatically; traffic light RAG status (green for within 5% of target, amber for 5--15% below target, red for more than 15% below target -- thresholds configurable per metric to account for different acceptable tolerances). Reports rendered as HTML emails using a responsive template (tested across Gmail, Outlook 2019, Apple Mail) with inline CSS for Outlook compatibility, and as PDF via headless Chrome/Puppeteer for archival and formal distribution. Period definition: weekly reports cover Mon--Sun calendar week; monthly reports cover the calendar month; financial year reports align to your fiscal year start month (not necessarily January 1); periods are labelled unambiguously in the report (Week 32, 2025: 5 Aug -- 11 Aug) to prevent any confusion about which period is being reported. Budget versus actual comparison requires budget data to be loaded into the data warehouse (from Excel/Google Sheets via Fivetran Google Sheets connector or manual upload to a budgets table) with budget figures at the same granularity as the actuals (monthly revenue budget by product line matching the actuals breakdowns). Commentary section: a structured commentary block per report section where the report owner can annotate significant variances with one-click narrative templates ("Revenue is 8% below target due to...") -- commentary entered via a simple web form and injected into the generated report before distribution.

Each department receives a metric pack containing the KPIs that reflect their function and accountability -- not a subset of the same generic dashboard, but a purposefully curated set of metrics that the department head is accountable for managing. Finance pack: ARR/MRR by product line, cash collected vs invoiced, debtors ageing (30/60/90/90+ days), operating cost by category vs budget, gross margin by product line, and runway (months at current burn). Sales pack: pipeline value by stage, new pipeline created in period, pipeline-to-revenue conversion rate, average deal size by product, sales cycle length by deal size tier, quota attainment per rep, and win/loss rate by competitor. Operations pack: on-time delivery rate (OTIF), order fulfilment cycle time, stock-out incidents, ticket resolution time by SLA tier, and first-contact resolution rate. Marketing pack: MQL volume by channel, MQL-to-SQL conversion rate, cost per MQL by channel, campaign ROI by spend category, and website conversion rate by landing page. Customer success pack: NRR, GRR, NPS, churn rate by cohort, expansion revenue from upsell/cross-sell, and accounts at risk score. Leadership summary pack: the top two metrics from each department pack in a one-page view alongside the company-level OKRs; if the leadership pack says revenue is on track and the finance pack says collections are behind, the discrepancy is visible because both packs are generated from the same data layer with the same metric definition -- not from different exports using different filters.

Report generation and distribution fully automated on a configured schedule with monitoring that catches failures before recipients notice. Generation pipeline: AWS EventBridge cron rule (or GCP Cloud Scheduler, Airflow DAG, or cron on a dedicated job server) triggers the report generation job at the configured time -- typically 06:00 local time on Monday morning for weekly reports, 06:00 on the first business day of each month for monthly reports, so reports are in recipients' inboxes before the working day starts. Generation steps: data quality gate (dbt test run, row count validation against previous period) -- if gate fails, generation stops and the on-call data team member is paged via PagerDuty or alerted via Slack with the failing check name; metric calculation from the mart layer; report HTML and PDF rendering; recipient list resolution (department heads for department packs, exec distribution list for leadership summary). Email delivery via SendGrid or Amazon SES with delivery event logging (delivered, opened, bounced); delivery confirmation stored per report per recipient so there is an audit record of every distribution. Failure alerting: any unhandled exception in the generation pipeline writes an error event to a monitoring table; a heartbeat check runs 30 minutes after the scheduled generation time and alerts if the delivery confirmation log is empty for an expected report -- this catches the case where the job ran without error but no emails were sent due to a delivery issue. Report archive: every generated report stored as a PDF in AWS S3 or Google Cloud Storage with a 7-year retention policy; accessible via a simple URL pattern so historical reports can be retrieved without re-running the generation pipeline. Report subscriber management: a self-service interface for adding and removing recipients from each distribution list without requiring a data engineer to edit a configuration file.

Each metric in the reporting system displayed with its rolling 12-month trend in a sparkline or mini-chart alongside the current period number -- the direction of travel conveys more information than any single-period value in isolation; a revenue figure of £450K is meaningless without knowing whether it was £380K three months ago (positive trend) or £520K three months ago (concerning decline). Moving average applied to volatile metrics (weekly transaction volume, daily active users, support ticket volume) with a configurable window (4-week or 13-week moving average) to smooth noise and reveal the underlying trend -- volatile metrics reported without smoothing generate questions about weekly fluctuations that aren't meaningful. Period-end forecast for month or quarter in progress: current run rate (actual performance in completed days of the period divided by days elapsed) extrapolated to period end with a simple linear projection; for revenue metrics where late-period dynamics differ from early-period (sales deals that close in the last week of the month), a seasonality-adjusted forecast using the historical intra-period distribution is applied instead. Anomaly detection: for each metric, a control band is calculated based on the rolling standard deviation over the past 12 months; values outside 2 standard deviations from the rolling mean are flagged as anomalies and highlighted in the report with an anomaly indicator; anomalies also trigger an out-of-cycle alert to the metric owner (separate from the scheduled report distribution) so they can investigate before the report is distributed. Year-over-year comparison: for businesses with strong seasonal patterns, year-over-year comparison is more meaningful than period-on-period -- a retail business comparing November to October is comparing Christmas peak to ordinary trading; YoY comparison is calculated in the data layer and displayed alongside the period-on-period comparison in the relevant metric packs.