I’m building an internal KPI portal for executives, and they keep asking for weekly summaries plus “why did this change?” explanations. What’s the best setup?

The best setup is usually a two-layer KPI system:

1. A reliable metrics layer that computes weekly KPI values consistently
2. An explanation layer that automatically identifies drivers, anomalies, and likely reasons for change

That gives executives:

• What changed?
• How much changed?
• Why did it change?
• What should we look at next?

Recommended architecture

1) Central metrics definitions

Create a governed metrics layer so every KPI is computed the same way everywhere.

Include for each KPI:

• Definition
• Formula
• Grain
• Filters/exclusions
• Owner
• Source tables
• Refresh cadence
• “Certified” status

This prevents arguments like “my dashboard says revenue is up, yours says down.”

2) Weekly summary generator

For each KPI, generate a short weekly narrative:

• Current week value
• Prior week value
• WoW change
• 4-week trend
• Year-over-year if relevant
• Status vs target/threshold

Example:

Weekly active users increased 8% WoW to 124k, driven primarily by stronger mobile traffic and higher returning-user engagement. Web signups were flat.

3) Driver analysis / decomposition

When a KPI changes, automatically break it down by common dimensions:

• Region
• Product line
• Channel
• Customer segment
• Device
• Sales rep / team
• Cohort / tenure
• Campaign

Use a contribution analysis approach:

• Which segments account for most of the change?
• Did one segment swing unusually?
• Is the change broad-based or concentrated?

This is usually the most useful answer to “why did this change?”

4) Exception detection

Don’t explain every metric every week. Focus on:

• Large absolute changes
• Large percentage changes
• Values outside expected ranges
• Changes that exceed confidence bands or seasonality expectations

This keeps the portal from overwhelming executives.

5) Narrative generation

Use a templated summary with optional AI-generated wording, but ground it in computed facts.

Good pattern:

• Rules/SQL/Python compute the facts
• LLM turns facts into readable prose
• The model is not allowed to invent reasons not supported by data

Example guardrails:

• Only mention drivers present in the data
• Include numeric evidence
• If uncertainty is high, say “likely” or “needs investigation”
• Always link to underlying charts

---

Best-practice design for the “why” question

Executives usually want one of four types of explanations:

A) Mix shift

Example:

• Overall sales up because high-value enterprise deals increased, even though total deal count was flat

B) Volume change

Example:

• Revenue increased because order count grew 12%

C) Rate / conversion change

Example:

• Conversions fell because checkout conversion dropped from 4.2% to 3.7%

D) Outlier event

Example:

• A large enterprise renewal closed this week; without it, revenue would have been flat

Your system should try to classify the change into one of these buckets.

---

What I’d implement first

MVP

• Certified KPI definitions
• Weekly snapshots
• WoW delta calculations
• Simple driver breakdown by 3–5 key dimensions
• Rule-based anomaly detection
• Auto-generated weekly executive summary

Then add

• Seasonality-aware expected ranges
• Root-cause drill-downs
• Cohort analysis
• Alerting for material changes
• LLM-generated summaries with citation links

---

Suggested data flow

1. Ingest data
2. Build clean fact tables
3. Compute KPI layer
4. Store weekly snapshots
5. Run change detection
6. Run driver decomposition
7. Generate summary text
8. Publish to portal / email / Slack

---

Example executive view

For each KPI card:

• Current week value
• WoW %
• Status indicator
• Sparkline
• “Why changed?” expandable section

“Why changed?” section:

• Top 3 drivers
• Dimensional breakout
• Exception notes
• Link to drill-down dashboard

---

Tech stack options

If you want low-friction analytics

• dbt for metrics transformations
• Warehouse: Snowflake/BigQuery/Redshift
• BI: Tableau/Power BI/Looker
• Python job for anomaly detection and narrative generation

If you want a more semantic/metrics-driven setup

• Metric store / semantic layer: dbt metrics, Cube, LookML, MetricFlow
• Orchestrator: Airflow / Dagster / Prefect
• Narrative service: small API that assembles facts into summaries

---

Key caution

Avoid letting an LLM be the source of truth for explanations.

Best practice is:

• SQL/Python computes the facts
• LLM only writes the explanation from those facts

That keeps the portal accurate and auditable.

---

Bottom line

For executives, the best setup is:

• A governed metrics layer
• Weekly automated summaries
• Driver-based change explanation
• Anomaly detection
• LLM-written narrative grounded in computed data

If you want, I can sketch:

1. a reference architecture diagram,
2. a data model, or
3. a sample prompt/template for the weekly “why did this change?” summary.