Aggregations For KPI Tables

Why this matters

As a BI Developer, you turn raw events and transactions into KPI tables that power dashboards, alerts, and executive decisions. Typical tasks include:

• Producing daily/weekly revenue, orders, and average order value (AOV).
• Computing conversion rates (sessions → orders), activation rates, and churn.
• Building rolling windows (7-day revenue), MTD/YTD metrics, and cohort summaries.
• Creating reliable, idempotent aggregations that refresh quickly and don’t double count.

Concept explained simply

A KPI table is a compact, query-ready summary of key metrics at a chosen grain (for example, one row per date), with consistent definitions and filters. It’s faster and safer for dashboards than calculating metrics from raw data every time.

Mental model

• Grain: Decide the row unit (per day, per week, per product, per channel, etc.).
• Dimensions: What you group by (date, country, channel).
• Measures: How you summarize (SUM, COUNT DISTINCT, AVG, ratios, windowed sums).
• Filters & logic: What you include/exclude (refunds, test orders, canceled states).
• Idempotent & incremental: The same SQL gives the same result, and can refresh only what changed.

Data used in examples

Assume these simplified tables:

orders(
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  order_status TEXT,        -- e.g., 'paid','canceled','refunded'
  order_amount NUMERIC(12,2),
  currency TEXT
)

sessions(
  session_id BIGINT,
  user_id BIGINT,
  started_at TIMESTAMP
)

signups(
  user_id BIGINT,
  signup_at TIMESTAMP
)

refunds(
  refund_id BIGINT,
  order_id BIGINT,
  refunded_at DATE,
  refund_amount NUMERIC(12,2)
)

Tip: If your warehouse has a date/calendar table, join it to guarantee complete date coverage (including days with zero activity).

Worked examples

1) Daily revenue KPI (exclude canceled)

WITH base AS (
  SELECT
    order_date AS dt,
    CASE WHEN order_status = 'paid' THEN order_amount ELSE 0 END AS paid_amount
  FROM orders
  WHERE order_date >= DATE '2023-01-01'
)
SELECT
  dt,
  SUM(paid_amount) AS revenue
FROM base
GROUP BY dt
ORDER BY dt;

Notes:

• Filter early (by date). Keep logic inside CASE for consistent inclusion/exclusion.
• One row per day: the grain is daily.

2) Daily conversion rate (sessions → orders)

Aggregate separately, then join on date. This avoids double counting and keeps denominators correct.

WITH daily_sessions AS (
  SELECT
    CAST(started_at AS DATE) AS dt,
    COUNT(DISTINCT session_id) AS sessions
  FROM sessions
  GROUP BY 1
),

daily_orders AS (
  SELECT
    order_date AS dt,
    COUNT(DISTINCT order_id) AS orders
  FROM orders
  WHERE order_status = 'paid'
  GROUP BY 1
)
SELECT
  COALESCE(s.dt, o.dt) AS dt,
  COALESCE(s.sessions, 0) AS sessions,
  COALESCE(o.orders, 0) AS orders,
  CASE WHEN COALESCE(s.sessions, 0) = 0 THEN 0.0
       ELSE COALESCE(o.orders, 0)::DECIMAL / s.sessions END AS conversion_rate
FROM daily_sessions s
FULL OUTER JOIN daily_orders o USING (dt)
ORDER BY dt;

Notes:

• Guard against divide-by-zero with CASE.
• Use FULL OUTER JOIN to keep dates that appear in one side only.

3) Rolling 7-day revenue and MTD revenue

Use window functions for rolling sums, and date truncation for MTD.

WITH daily_rev AS (
  SELECT
    order_date AS dt,
    SUM(CASE WHEN order_status = 'paid' THEN order_amount ELSE 0 END) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  dt,
  revenue,
  SUM(revenue) OVER (
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rev_7d,
  SUM(revenue) OVER (
    PARTITION BY DATE_TRUNC('month', dt)
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rev_mtd
FROM daily_rev
ORDER BY dt;

Notes:

• ROWS BETWEEN 6 PRECEDING gives a 7-day window including today.
• PARTITION BY month segments the running total per calendar month.

How to design a KPI table (quick steps)

1. Pick the grain (daily, weekly, monthly, or entity + time).
2. Define metric rules (what counts as revenue, what excludes test/canceled).
3. Aggregate from each source separately (facts), then join.
4. Calculate ratios at the final grain; guard denominators.
5. Ensure idempotency and consider incremental refresh by date.

Exercises

Everyone can attempt these. Log in to save your progress.

Exercise 1 — Daily Active Customers and AOV

Create a daily KPI table with:

• active_customers: COUNT DISTINCT of customer_id with paid orders
• orders_paid: COUNT DISTINCT paid orders
• revenue: SUM order_amount for paid orders
• aov: revenue / NULLIF(orders_paid, 0)

Expected output sample (shape):

dt         active_customers  orders_paid  revenue   aov
2023-01-01 45                60           7250.00   120.83
2023-01-02 39                52           6100.00   117.31

See the exercise panel below for hints and solution.

Exercise 2 — Weekly Net Revenue with Refunds

Produce a weekly KPI table at week_start (DATE_TRUNC('week', ...)) with:

• gross_revenue: SUM of paid order_amount
• refunds_amount: SUM of refund_amount
• net_revenue: gross_revenue - refunds_amount

Expected output sample (shape):

week_start  gross_revenue  refunds_amount  net_revenue
2023-01-02  41250.00       1250.00         40000.00
2023-01-09  38810.00       810.00          38000.00

Common mistakes and self-checks

• Double counting after joins: Aggregate each fact table first, then join by the chosen grain and dimensions.
• Wrong denominator in rates: Ensure numerator and denominator share the same grain and filters.
• Divide-by-zero: Use NULLIF or CASE to avoid errors and misleading infinities.
• Ignoring time zone: Decide on a canonical time zone for date truncation and document it.
• Counting canceled/refunded as revenue: Encode business rules with CASE when aggregating.
• SUM of averages fallacy: Always recompute ratios at the final grain, don’t sum pre-averaged values.

-- Control total: raw paid revenue vs. KPI revenue
SELECT SUM(order_amount) FROM orders WHERE order_status='paid';

-- Or compare a single day range explicitly
SELECT SUM(order_amount) FROM orders WHERE order_status='paid' AND order_date=DATE '2023-01-02';

Who this is for

• BI Developers and Analysts building dashboard-ready datasets.
• Analytics Engineers standardizing metric definitions.

Prerequisites

• Comfort with SELECT, GROUP BY, CASE, and JOINs.
• Basic window functions (ROWs/RANGE, PARTITION BY) familiarity is helpful.

Learning path

1. Master GROUP BY and CASE for conditional aggregations.
2. Practice separate aggregation and joining for ratios.
3. Add rolling windows (7D/28D) and MTD/YTD using window functions.
4. Harden definitions: time zone choice, filters, and idempotency.

Practical projects

• Build a daily commerce KPI table (orders, revenue, AOV, unique customers).
• Create a marketing KPI table (sessions, signups, conversion rate by channel).
• Publish a weekly financial KPI table (gross, refunds, net revenue) with rolling sums.

Next steps

• Take the quick test below to check understanding.
• Apply the patterns to your own dataset and validate against source-of-truth reports.

Mini challenge

Add a 7-day rolling conversion rate to your daily KPI table. Hint: compute orders and sessions per day first; then apply a 7-day rolling sum on both and divide at the end. Guard against zero.