Summary Tables

Why this matters

Summary tables turn raw rows into a clear picture. As a Data Analyst, you will use them to size problems, spot anomalies, and guide stakeholders quickly. Typical tasks include:

• Profiling a new dataset (counts, missing rates, distributions).
• Measuring performance by segment (e.g., signups by channel, revenue by region).
• Monitoring week-over-week changes and flagging spikes or drops.
• Preparing inputs for dashboards and deeper modeling.

Concept explained simply

A summary table groups rows and calculates metrics (counts, sums, averages, percentages). It can be flat (grouped table) or pivoted (values spread across columns).

Mental model

Think of each row as a small item and your summary table as shelves in a store:

• Group By = which shelf you place items on (e.g., by country).
• Aggregations = what you read on the shelf label (count, total, average).
• Pivot = turning some labels into columns (e.g., one column per device type).

Worked examples

SQL sketch:
SELECT DATE_TRUNC('month', order_date) AS month,
       device,
       COUNT(*) AS orders,
       ROUND(AVG(order_value), 2) AS aov
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

Steps:
1) Build ticket_count = COUNT(*) by team, severity.
2) Row % = ticket_count / SUM(ticket_count) OVER (PARTITION BY team).
3) Col % = ticket_count / SUM(ticket_count) OVER (PARTITION BY severity).

Tenure bucket rules: 0-1m, 1-3m, 3-6m, 6-12m, 12m+.
For each bucket: churn_rate = churned_customers / total_customers.

How to build a summary table (step-by-step)

1. Define the question: What decision should this table inform?
2. Pick grouping keys: Time, segment, product lines, etc.
3. Select metrics: Count, distinct count, sums, averages, medians, percentages.
4. Handle data quality: Treat missing values and outliers; decide on filters.
5. Construct table: Group, aggregate, optionally pivot.
6. Validate: Totals match? Percentages sum to ~100%? Sample raw rows.
7. Annotate: Clear metric names and time context (e.g., last 30 days).

Quick recipes

SELECT channel,
       COUNT(*) AS signups,
       COUNT(DISTINCT user_id) AS unique_users,
       ROUND(AVG(session_duration), 1) AS avg_secs
FROM sessions
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY channel
ORDER BY signups DESC;

import pandas as pd
agg = (df
  .query("event_date >= @cutoff")
  .groupby("channel")
  .agg(signups=("user_id", "count"),
       unique_users=("user_id", pd.Series.nunique),
       avg_secs=("session_duration", "mean"))
  .reset_index())

Quality checklist

• Totals reconcile with raw counts.
• Percentages sum to ~100% (allow for rounding).
• Distinct counts used where appropriate (e.g., users vs. events).
• Clear time window (e.g., last 7 days vs. all-time).
• Outliers and missing values handled intentionally.
• Column naming is unambiguous (e.g., aov_usd_30d).

Common mistakes and how to self-check

• Using mean on skewed data: Prefer median or trimmed mean for heavy tails. Self-check: Compare mean vs. median; if far apart, report both.
• Mixing events and users: Use COUNT distinct for users. Self-check: sanity-check ratios (events per user).
• Wrong denominator in percentages: Define row% vs. col% upfront. Self-check: Sum across the intended direction and verify ~100%.
• Hidden filters: Document all filters. Self-check: Re-run without filters and compare deltas.
• Over-pivoting: Too many columns hurt readability. Self-check: Keep 2–6 columns per pivot dimension, else regroup or bin.

Who this is for

• Early-career analysts who want fast, reliable EDA.
• Professionals switching to analytics from ops, marketing, or finance.
• Anyone preparing data for dashboards or experiments.

Prerequisites

• Basic comfort with spreadsheets or SQL.
• Knowing data types (numeric vs. categorical) and nulls.
• Basic understanding of averages and percentages.

Learning path

1. Practice basic grouped tables (count, distinct, sum).
2. Add central tendency and dispersion (median, IQR).
3. Introduce percentages (row, column, overall).
4. Pivot and bin for comparisons.
5. Automate with your main tool (SQL views, Pivot caches, pandas scripts).

Exercises

These mirror the exercises below. Try them before opening the solutions.

order_id,order_value,country
1,120,US
2,80,US
3, ,CA
4,50,CA
5,200,US
6,110,MX
7, ,US
8,75,CA

week_start,channel,signups
2025-01-06,Organic,140
2025-01-06,Paid,220
2025-01-06,Referral,40
2025-01-13,Organic,160
2025-01-13,Paid,180
2025-01-13,Referral,60

• Check that your totals reconcile with raw data.
• Confirm your missing_value_rate = 1 - (non_null / total) per group.
• Ensure row% sums to 100% across channels; col% sums to 100% down weeks.

Practical projects

• Acquisition funnel table: visits, signups, activations by channel with conversion rates.
• Revenue table: monthly revenue by product line with MoM deltas and median order value.
• Quality table: defects by plant and shift with Pareto 80/20 highlighting.

Mini challenge

Pick a dataset you use weekly. Build one flat summary and one pivot that answers a question your team keeps asking. Add a quality checklist and one insight in one sentence. Share with a teammate for feedback.

Next steps

• Automate your summary as a saved query, pivot template, or script.
• Add clear definitions and time windows to make it re-usable.
• Move to the Quick Test below to check your understanding.

Note: The quick test is available to everyone; log in to save your progress.