Debugging And Reconciling Metrics

Why this matters

As an Analytics Engineer, you are the bridge between raw data and trustworthy business metrics. When two dashboards show different revenue, or a product team says DAU looks off, you need to quickly pinpoint the cause and reconcile the numbers. This work builds stakeholder trust and prevents expensive decisions based on bad data.

• Real task: explain why Finance sees $5.2M and Marketing sees $5.0M for the same period.
• Real task: fix a DAU definition mismatch after a timezone change.
• Real task: ensure a conversion rate uses the correct denominator (visitors vs sessions).

Concept explained simply

A metric is a precise recipe: aggregation of rows at a defined grain, with filters, time boundaries, and joins. Two metrics that sound the same can differ if any ingredient differs.

Mental model

Think of a metric as: Metric = Aggregation(Function over Rows) at Grain with Filters in Time Window using Joins and Null Rules. Reconciliation means checking each knob until both sides match or you document the true definition.

Debugging workflow (fast and repeatable)

1. Confirm the exact question: Which metric name, definition, and period?
2. Freeze the window: Agree on a single date range and timezone.
3. Check freshness: Is one dataset more recent? Compare loaded_at and run times.
4. Align the grain: Aggregate upstream tables to the same grain before joining.
5. Compare inputs step-by-step: Start from counts of base rows, then add joins, then filters, then the final aggregation.
6. Diff the queries: Place both definitions side-by-side; change one knob at a time until numbers converge.
7. Document the true definition: Record the final SQL, grain, filters, and caveats.

Worked examples

Example 1: Revenue inflated by a one-to-many join

Symptom: Your revenue is higher than Finance's report.

Root cause: Summing order_items without excluding refunded orders.

-- Finance logic (net revenue): excludes refunded orders
SELECT SUM(oi.quantity * oi.price) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

-- Your logic (inflated): no status filter
SELECT SUM(oi.quantity * oi.price) AS gross_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;

SELECT
  SUM(CASE WHEN o.status = 'completed' THEN oi.quantity * oi.price ELSE 0 END) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;

Example 2: DAU mismatch due to timezone

Symptom: DAU differs by day between Product and BI.

Root cause: Product uses UTC; BI uses UTC+02.

-- Convert UTC to reporting timezone (example uses +02 for simplicity)
SELECT
  CAST(event_time_utc + INTERVAL '2 hour' AS DATE) AS local_date,
  COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY 1
ORDER BY 1;

Example 3: Conversion rate denominator mismatch

Symptom: Marketing conversion rate is 4.2% while Product shows 2.8%.

Root cause: One uses sessions as denominator, the other uses unique visitors.

-- Version A: per session
SELECT SUM(CASE WHEN purchased THEN 1 ELSE 0 END)::decimal / COUNT(*) AS conv_rate
FROM sessions;

-- Version B: per visitor (user_id)
WITH visitor_flags AS (
  SELECT user_id,
         MAX(CASE WHEN purchased THEN 1 ELSE 0 END) AS purchased_flag
  FROM sessions
  GROUP BY user_id
)
SELECT AVG(purchased_flag::decimal) AS conv_rate
FROM visitor_flags;

Reconciliation checklist (self-check)

• Window: exact start/end and timezone aligned.
• Grain: all joins happen after pre-aggregation to the final grain.
• Filters: explicitly listed; no hidden defaults.
• Nulls: COUNT(col) vs COUNT(*) implications considered.
• Dedup: windowed row_number() to keep one row per natural key.
• Freshness: loaded_at and source extract time confirmed.
• Math: integer vs decimal division fixed.

Exercises

These mirror the interactive tasks below. Try them before opening the solutions.

Exercise 1: Net vs gross revenue

Sample data:

orders
--------
order_id | user_id | status     | created_at
1        | 101     | completed  | 2025-01-05
2        | 101     | refunded   | 2025-01-06
3        | 102     | completed  | 2025-01-06
4        | 103     | completed  | 2025-01-06

order_items
-----------
order_id | sku | quantity | price
1        | A   | 1        | 50
1        | B   | 2        | 20
2        | A   | 1        | 50
3        | C   | 3        | 10
4        | B   | 1        | 20

Task: Write SQL to return gross_revenue, net_revenue (excluding refunded orders), and diff for all rows above.

Exercise 2: DAU with timezone

Sample data (UTC):

events
------
user_id | event_name | event_time_utc
101     | login      | 2025-01-05 22:30:00
101     | purchase   | 2025-01-05 23:10:00
102     | login      | 2025-01-06 00:20:00
103     | login      | 2025-01-06 01:05:00
101     | login      | 2025-01-06 23:20:00

Assume reporting timezone is UTC+02. Task: Return local_date and dau (distinct user_id) ordered by date.

Common mistakes

• Joining detail tables without pre-aggregating, causing inflated sums.
• Mixing COUNT(*) and COUNT(col) unintentionally.
• Forgetting to convert to the reporting timezone.
• Integer division when you need decimals.
• Assuming freshness: comparing a live dashboard to yesterday’s static export.

Practical projects

• Build a metric spec doc for three core KPIs (DAU, revenue, conversion rate) including SQL, grain, filters, timezone, and caveats.
• Create a reconciliation notebook that computes metrics stepwise and highlights diffs at each step.
• Implement deduplication strategies for user events and orders with row_number() and test cases.

Mini challenge

Your active_subscribers count is higher in Dashboard A than B. A uses a subscription table joined to payments on user_id. B uses only the subscription table. In one sentence each, list two plausible causes and the SQL change you would test first.

Next steps

• Turn your reconciliation checklist into a saved SQL template.
• Add tests that assert metric equality across two definitions within a tolerance for the last N days.
• Document final, approved definitions and share them with stakeholders.

Who this is for

• Analytics Engineers and BI Developers who own metric definitions.
• Data Analysts validating KPI dashboards.

Prerequisites

• Comfort with SQL joins, GROUP BY, window functions.
• Basic understanding of timezones and date arithmetic.

Learning path

1. Review metrics fundamentals (grain, filters, aggregation).
2. Practice reconciling revenue and user metrics with small datasets.
3. Automate checks and document canonical definitions.

Quick Test is available to everyone. If you log in, your progress will be saved.