Work like a mid-level data analyst: define KPIs, forecast, run and analyse A/B tests, model analytics tables and turn stakeholder requests into recommendations.

Data Analytics in Practice

## Why it matters

"How are we doing?" is the question behind most analytics work, and the answer depends entirely on how you measure. Two analysts asked for "conversion rate" will often produce two different numbers: one divides orders by sessions, the other buyers by visitors; one includes refunds, the other does not. Then a meeting is spent arguing about whose number is right instead of what to do. Mid-level analysts are expected to prevent this by writing **precise metric definitions** and making them the single source of truth.

## Metrics, KPIs and goals

- A **metric** is any quantity you measure: page views, orders, average delivery time.
- A **KPI (key performance indicator)** is a metric the organisation has chosen to track progress toward a goal. There should be few of them.
- A **goal or target** is the value you want the KPI to reach: "raise monthly purchase conversion from 18% to 22% by March."

Frameworks help pick KPIs. A common one is a **North Star metric** (the single number that best captures value delivered to customers, such as weekly active teams for a collaboration tool) supported by **input metrics** the team can influence directly (sign-up completion, activation rate, invitations sent). Another is the **HEART** framework from Google (Happiness, Engagement, Adoption, Retention, Task success) for product experience. You do not need a framework to start, but you do need to know why each KPI exists.

## What a good KPI definition contains

Write a short specification for every KPI:

| Part | Example |
|---|---|
| **Name** | Monthly purchase conversion |
| **Question it answers** | What share of people who visit in a month buy something? |
| **Formula** | distinct users with ≥1 completed order in month ÷ distinct users with ≥1 session in month |
| **Inclusions and exclusions** | Exclude refunded and test orders; exclude internal staff accounts |
| **Grain and time** | Per calendar month, UTC dates |
| **Unit and rounding** | Percentage, 1 decimal place |
| **Source tables** | `sessions`, `orders` |
| **Owner** | Growth analytics team |
| **Direction** | Higher is better |

The formula must be specific enough that two people writing SQL independently get the **same** number. Most disagreements hide in the exclusions, the counting unit (rows vs distinct people) and the time zone.

## Worked example: from words to SQL

The definition above becomes this query:

```sql
WITH visitors AS (
  SELECT substr(session_date, 1, 7) AS month,
         COUNT(DISTINCT user_id)   AS visitors
  FROM sessions
  GROUP BY 1
),
buyers AS (
  SELECT substr(order_date, 1, 7) AS month,
         COUNT(DISTINCT user_id)  AS buyers
  FROM orders
  WHERE status = 'completed'          -- exclusions written down in the spec
  GROUP BY 1
)
SELECT v.month, v.visitors, COALESCE(b.buyers, 0) AS buyers,
       ROUND(100.0 * COALESCE(b.buyers, 0) / v.visitors, 1) AS conversion_pct
FROM visitors v
LEFT JOIN buyers b ON b.month = v.month
ORDER BY v.month;
```

Points to notice:

- `COUNT(DISTINCT user_id)` counts people, not sessions or orders.
- The `LEFT JOIN` keeps months with visitors but no buyers (conversion 0%, not missing).
- `100.0 *` forces decimal division; in many SQL dialects integer ÷ integer truncates to 0.
- Each CTE maps to one part of the written definition, which makes review easy.

## Ratio metrics need care

Most KPIs are ratios, and ratios have traps:

- **Numerator and denominator must match.** If the denominator excludes staff accounts, the numerator must too.
- **Aggregate before dividing.** Quarterly conversion is total quarterly buyers ÷ total quarterly visitors, not the average of three monthly percentages.
- **Watch small denominators.** A region with 4 visitors and 2 buyers has 50% conversion that means very little. Show counts alongside rates.

## Good KPIs are actionable and hard to game

- **Actionable:** a team can influence it. "Total registered users ever" only goes up and changes no decisions.
- **Balanced:** pair a KPI with a guardrail. If support is measured on tickets closed per hour, also track reopen rate, or people will close tickets without solving them.
- **Timely:** available often enough to act on.

## Common mistakes

- Counting rows when the definition says people.
- Forgetting refunds, test data or bots.
- Averaging percentages across groups.
- Changing a definition silently. Version it and annotate the dashboard when it changes.

## Quick recap

- A KPI is a metric tied to a goal; keep the list short.
- Write a full spec: formula, exclusions, grain, unit, source, owner.
- Translate the spec directly into SQL, one CTE per part.
- Aggregate before dividing, show counts with rates, and add guardrails.

## Try it

In the **Do** tab, fix a first-draft query so it matches a written KPI definition exactly.


Turn a vague goal into a key performance indicator with an exact, testable formula.

Defining KPIs

What are KPIs and Metrics? | Data Fundamental for Beginners

Pick the dashboard and write down who it appears to be for and what it seems to be about.

Talk to at least one real or likely user for 10 to 15 minutes (or, for a public dashboard, write down three decisions a realistic user would make). Note the decisions and any thresholds that trigger action.

Build an audit table with one row per tile: who uses it, which decision it supports, whether it has a comparison, and keep / change / remove.

Check two metric definitions on the dashboard. Are they stated? Would two analysts compute them the same way? Write a one-line definition for each.

Sketch the redesigned dashboard on one screen: status KPIs with comparisons on top, explanatory breakdowns in the middle, an action list or exception table at the bottom.

Identify any decision better served by a scheduled alert than a dashboard, and write the alert rule (metric, threshold, who receives it).

Write a short changelog entry explaining what you changed and why, as you would publish it to users.

## Why it matters

Most organisations have more dashboards than anyone reads. Usage logs in BI tools often show that a large share of reports are opened by nobody except their creator after the first month. At the same time, people still email analysts for "a quick number". The problem is rarely the charts. It is that the dashboard was built around the data that was available instead of the decisions people make.

At mid-level, you will often be asked to **fix** an existing dashboard rather than build a new one. That means auditing it, talking to users and rebuilding with less.

## Step 1: Find out how it is used

Start with evidence, not opinions:

- **Usage data.** Most BI tools (Power BI, Tableau, Looker, Metabase) show views per report and per user. Who opens it, how often, and which page?
- **Interviews.** Talk to three to five users for 15 minutes each. Ask: "When did you last open this? What were you trying to decide? What did you do next? What did you look at elsewhere?"
- **Workarounds.** Ask whether people export to Excel. An export usually means the dashboard lacks a view they need.

You are listening for **decisions and triggers**: "If refunds go above 3% in a region I call that region's manager." That sentence is a dashboard requirement.

## Step 2: Audit every tile

For each chart or number on the current dashboard, ask:

1. Which decision does this support? (If none, it is a candidate for removal.)
2. Does it have a comparison (target, last period)?
3. Is the metric defined, and does the definition match other reports?
4. Is the chart type right for the question (Module 1 of Data Visualization & Storytelling)?
5. Is it trusted? Have users noticed numbers that looked wrong?

A simple audit table keeps this honest:

| Tile | Used by | Decision | Keep? | Change |
|---|---|---|---|---|
| Total revenue (all time) | nobody | none | remove | replace with month vs target |
| Revenue by region pie | regional leads | where to focus | keep | sorted bars, highlight below target |
| 14 product sparklines | nobody | none | remove | top 5 movers table |
| Refund rate | ops | call region manager if > 3% | keep | add threshold line and alert |

## Step 3: Redesign around decisions

- **Lead with status:** a top row of the three to five KPIs with comparisons, coloured only when they need attention.
- **Then the "why":** breakdowns that explain movement (by region, channel, product), sorted.
- **Then the "what to do":** an exception list or table that names specific items to act on (regions above the refund threshold, accounts at risk).
- **Make alerts do the watching.** If a decision is triggered by a threshold, a scheduled alert (email or chat message when refunds exceed 3%) may be better than a dashboard someone must remember to check. Most BI tools support data-driven alerts or subscriptions.

## Step 4: Ship, then measure

Release the new version alongside the old one, tell users what changed, and check usage again after a few weeks. Retire the old version on a fixed date. Keep a changelog on the dashboard itself ("v2, 2026-10: refund rate now excludes test orders").

## Worked example

An e-commerce operations dashboard has 22 tiles over three scrolling pages. Interviews show the operations manager makes two weekly decisions: which warehouse needs more staff, and whether to escalate late deliveries with a carrier. The rebuilt dashboard has six tiles on one screen: on-time delivery % vs target, orders per warehouse vs capacity (sorted bars), late deliveries by carrier this week vs last, and a table of the 10 oldest undelivered orders. Two scheduled alerts cover the thresholds. Fourteen tiles move to an "analysis" page for the analytics team.

## Common mistakes

- Redesigning visuals without talking to users.
- Keeping every tile because "someone might need it". Move rarely used views to a secondary page instead.
- Metric definitions that differ from the finance or product reports. Reconcile them first.
- No owner. Every dashboard needs a named owner and a review date.

## Quick recap

- Measure usage and interview users about decisions and triggers.
- Audit each tile: decision, comparison, definition, chart type, trust.
- Rebuild: status, then why, then what to do; use alerts for thresholds.
- Release, measure adoption, retire the old version, keep a changelog.

## Try it

The **Do** tab walks you through auditing and redesigning a real dashboard.


Audit an existing dashboard and rebuild it around the decisions its users actually make.

Dashboards people use

Data Dashboard Design Best Practices

12 Dashboard design tips for better data visualization

## Why it matters

Planning needs numbers about the future: how many support agents to schedule next quarter, how much stock to order, what revenue target is realistic. Analysts are often asked for "a forecast" with little time. You do not need advanced machine learning for most of these requests. A simple, transparent model that captures **trend** and **seasonality**, plus an honest error range, beats a black box nobody can explain.

## The building blocks of a time series

Most business series can be described as:

- **Trend:** the long-run direction (sales growing about 5 units per quarter).
- **Seasonality:** a pattern that repeats with a fixed period (every Q4 is higher because of holidays; Mondays are busier than Sundays).
- **Noise (residual):** what is left: random variation and one-off events.

**Additive** decomposition assumes `value = trend + seasonal + noise`, where the seasonal effect is a fixed number of units. **Multiplicative** decomposition assumes `value = trend × seasonal × noise`, where the effect is a percentage (Q4 is 15% above trend). Use multiplicative when the seasonal swings grow as the series grows.

## Start with baselines

Before building anything, compute simple baselines. Any model you present must beat them:

- **Naive:** next value = last value.
- **Seasonal naive:** next value = value one season ago (next Q1 = this Q1).
- **Average:** next value = historical mean.

Seasonal naive is surprisingly hard to beat for strongly seasonal data.

## Worked example: trend plus seasonal index

Quarterly sales for three years:

```text
Year 1: 212 189 177 231
Year 2: 235 207 199 254
Year 3: 254 231 218 276
```

**1. Fit a linear trend.** With time index t = 0..11, least squares gives about `trend(t) = 194.6 + 5.27 t`. In Python:

```python
import statistics
slope, intercept = statistics.linear_regression(range(12), sales)
```

**2. Compute residuals** `y[t] - trend(t)`, then average them by quarter. Q4 residuals are large and positive, Q3 negative. Centre the four averages so they sum to zero; these are the **seasonal indexes**, roughly `+18, -12, -28, +22` for Q1 to Q4. (You will compute the exact values in the activity.)

**3. Forecast** each future quarter as trend plus its seasonal index. For t = 12 (next Q1): `194.6 + 5.27 × 12 + 18.0 ≈ 276`.

**4. Check accuracy on held-out data.** Fit on years 1 and 2 only, forecast year 3, and compare with what actually happened using the **mean absolute error (MAE)**: the average of |actual − forecast|. Also report MAPE (mean absolute percentage error) if stakeholders think in percentages. Compare with the seasonal naive baseline's MAE. This is called a **backtest**.

## Doing it with libraries

On your own machine you would usually use a library:

```python
import pandas as pd
from statsmodels.tsa.seasonal import seasonal_decompose
from statsmodels.tsa.holtwinters import ExponentialSmoothing

s = pd.Series(sales, index=pd.period_range("2024Q1", periods=12, freq="Q"))
parts = seasonal_decompose(s, model="additive", period=4)   # trend, seasonal, resid
model = ExponentialSmoothing(s, trend="add", seasonal="add", seasonal_periods=4).fit()
print(model.forecast(4))
```

Holt-Winters exponential smoothing weights recent data more heavily, which helps when the trend changes. Other common tools are ARIMA models and Meta's Prophet library. Spreadsheet users have `FORECAST.ETS` in Excel. All of them build on the same trend-and-season ideas.

## Communicate the uncertainty

A single forecast number will be wrong. Give a range. A simple, honest approach: use the backtest errors. If past quarterly errors were within about ±12 units, say "next Q1: about 276, likely between 264 and 288." Libraries can produce formal prediction intervals.

Also state the assumptions: "assumes no major price change and that the current growth continues."

## Common mistakes

- **No baseline comparison.** A complex model that loses to seasonal naive is not useful.
- **Testing on the training data.** Always hold out the most recent periods.
- **Too little history.** You need at least two full seasonal cycles to estimate seasonality, preferably more.
- **Ignoring known events.** Promotions, price changes and outages distort the history; note or adjust them.
- **Extrapolating a straight line forever.** Trends bend. Keep horizons short and refresh forecasts regularly.

## Quick recap

- Series = trend + seasonality + noise (additive or multiplicative).
- Beat naive and seasonal-naive baselines.
- Trend line + centred seasonal indexes gives a transparent forecast.
- Backtest with MAE on held-out periods, and always give a range.

## Try it

In the **Do** tab, fit a trend, compute seasonal indexes and forecast four quarters using only Python's standard library.


Forecast a business metric from its trend and seasonality, and check the forecast against held-out data.

Simple forecasting

What is Time Series Decomposition

What is Time Series Analysis?

Define KPIs with exact formulas, rebuild dashboards around decisions, and make a simple trend-and-seasonality forecast.