The Data Analyst
Field Manual

A distribution describes possible values and how frequently they occur. Start with sample size, missingness and a plot. The mean uses every value but is sensitive to extremes; the median is the 50th percentile and is robust to skew; the mode is the most frequent value. Range uses only extremes. Variance averages squared deviations from the mean; standard deviation returns to the original unit. Interquartile range spans the middle 50%.

Skewness describes asymmetry; kurtosis relates to tail weight. Do not label a distribution “normal” because the histogram looks vaguely bell-shaped. Report robust summaries for skewed quantities such as income and waiting time.

Exercise: Create two datasets with the same mean and different spread. Then add one extreme value and compare changes in mean, median, standard deviation and IQR.

Probability ranges from 0 to 1. For mutually exclusive events, add probabilities; for independent events, multiply. Conditional probability asks about A given B: P(A|B). Independence means learning B does not change the probability of A. In real data, events described as independent often are not.

A test with high sensitivity can still produce many false alarms when the condition is rare. Always inspect the base rate. Expected value combines possible outcomes with their probabilities; expected utility also accounts for costs and consequences.

Exercise: A fraud rule catches 90% of fraud and falsely flags 5% of legitimate transactions. If fraud prevalence is 1%, calculate the probability that a flagged transaction is actually fraudulent using a table of 10,000 cases.

A sample statistic varies from sample to sample. Its sampling distribution describes that variation. The standard error of a mean is approximately s/√n; it shrinks with the square root of sample size, so four times as many observations roughly halves the standard error. A confidence interval combines estimate and uncertainty.

A 95% confidence procedure captures the true parameter in 95% of repeated samples under its assumptions. It does not mean there is a 95% probability that a fixed parameter lies inside this particular interval. Practical issues—bias, clustering, weighting and non-response—can dominate the textbook margin of error.

Exercise: Draw 100 repeated samples from the same simulated population, calculate 95% intervals and count coverage. Repeat with a biased sampling mechanism.

A null hypothesis defines a reference model. The p-value is the probability—assuming that model and its assumptions—of observing a result at least as incompatible with the null as yours. It is not the probability the null is true, and p > .05 does not prove no effect.

Choose tests by design and variable type: t-tests compare means under assumptions; paired tests respect matched observations; chi-square tests examine categorical association; ANOVA compares multiple means; non-parametric alternatives use ranks or permutations. Report the observed difference, confidence interval and effect size alongside the p-value. Correct or control for multiple testing when searching many outcomes.

Exercise: Compare delivery time between two regions. State hypotheses, examine distributions, select a test, calculate effect size, report an interval and write a two-sentence decision conclusion.

Pearson correlation measures linear association; Spearman correlation measures monotonic rank association. Both can be distorted by outliers, restricted range, aggregation and confounding. Always inspect a scatterplot. Correlation can arise because X affects Y, Y affects X, a third factor affects both, or selection creates the pattern.

Linear regression models an expected outcome as an intercept plus coefficients times predictors. A coefficient is the expected change in Y for a one-unit increase in X while included predictors are held constant. Check linearity, residual patterns, unequal variance, influential points, dependence and multicollinearity. For binary outcomes, logistic regression models log-odds; exponentiated coefficients are odds ratios, not risk ratios.

Outcome = β₀ + β₁×DeliveryDays + β₂×CustomerTenure + error

Exercise: Fit a simple regression, inspect residuals, add a confounder and explain why the coefficient changes. Write a prediction statement and a separate causal statement you are not entitled to make.

Learn variables, numbers, strings, Booleans, None, lists, tuples, dictionaries and sets. Use comparisons and Boolean operators in if statements; iterate with for; generate sequences with comprehensions; package repeated logic in functions. Exceptions should explain failure, not silently hide it.

def margin_rate(revenue: float, cost: float) -> float | None:
  """Return gross-margin rate, or None when revenue is zero."""
  if revenue == 0:
      return None
  return (revenue - cost) / revenue

rates = [margin_rate(r, c) for r, c in zip(revenues, costs)]

Use a project-specific virtual environment and record dependencies. Put constants and configuration near the top; use descriptive names; write small functions; never repeat secret credentials in notebooks.

Exercise: Write functions to normalise island names, validate dates and calculate margin. Add examples for normal, boundary and invalid inputs.

NumPy arrays store homogeneous values and enable vectorised computation. A pandas Series is a labelled one-dimensional array; a DataFrame is labelled columns sharing an index. Select columns with brackets, rows with .loc by label and .iloc by position. Boolean masks filter rows. Avoid chained assignment; make intent explicit.

import pandas as pd

df = pd.read_csv('data-analyst-field-manual-practice.csv',
               parse_dates=['order_date'])
df.info()
df.describe(include='all').T
completed = df.loc[df['status'].eq('Completed')].copy()
completed['margin'] = completed['revenue'] - completed['cost']

Inspect shape, dtypes, head/tail, memory, missingness and unique values immediately after loading. Indexes align automatically; that power can also create unexpected missing values when labels differ.

Exercise: Load the practice dataset, write a five-line profile and prove whether order_id is unique.

Use string accessors, datetime accessors, astype, replace, map, where, assign and pipe to create readable transformations. Diagnose missingness before dropna or fillna. Remove duplicates only after defining the key and which record should survive. Treat outliers as investigation targets—not automatic deletions.

quality = (df.isna().mean().mul(100)
           .rename('missing_pct').to_frame()
           .join(df.nunique(dropna=False).rename('distinct')))

df = (df.assign(region=lambda x: x.region.str.strip().str.title(),
              order_month=lambda x: x.order_date.dt.to_period('M'))
      .drop_duplicates(subset=['order_id','product_id'], keep='last'))

Exercise: Write a clean_sales() function that returns cleaned data plus a dictionary of row counts affected by each rule. Confirm idempotence: cleaning an already-clean output should not change it again.

groupby splits rows into groups, applies aggregations or transformations and combines results. Named aggregation keeps outputs clear. pivot_table reshapes summaries; melt converts wide columns into tidy rows. merge implements database-style joins; use validate and indicator to expose cardinality and unmatched keys.

monthly = (df.groupby(['order_month','region'], dropna=False)
.agg(orders=('order_id','nunique'),
     revenue=('revenue','sum'), cost=('cost','sum'))
.assign(margin=lambda x: x.revenue-x.cost)
.reset_index())

checked = df.merge(products, on='product_id', how='left',
                 validate='many_to_one', indicator=True)

Exercise: Reproduce your SQL region-month result in pandas. Assert that totals and distinct order counts match exactly.

Use notebooks for exploration and explanation, but restart and run all cells before delivery. Hidden state makes a notebook lie: a later cell may depend on an old variable. Move stable ingestion, cleaning and metric logic into .py modules. Use main() entry points and configuration rather than manual edits.

Assertions are executable assumptions: unique keys, allowed categories, date bounds, non-negative quantities, reconciliation totals. Unit tests check functions with small known inputs. Data tests check datasets. Logging records what ran, when, on which inputs and with what row counts.

assert clean['order_id'].notna().all()
assert clean[['order_id','product_id']].duplicated().sum() == 0
assert clean['revenue'].ge(0).all()
assert set(clean['status']).issubset({'Completed','Cancelled','Returned'})

Exercise: Refactor one notebook into load, clean, analyse, validate and export functions. Add at least six assertions and a README command to reproduce the output.

For magnitude comparison use sorted bars or dots. For change through ordered time use lines. For distribution use histogram, density, box or strip plots. For relationships use scatterplots. For composition use stacked bars or small multiples. For flow use a funnel only when stages are genuinely sequential; otherwise use bars. Maps are for spatial patterns, not decoration.

Exercise: Take one dataset and answer four different questions with four chart forms. Write the question above each chart and remove anything that does not help answer it.

Position on a shared scale is easier to compare than angle, area or colour saturation. Bars should normally begin at zero because their length encodes magnitude. Lines may use a non-zero axis when the purpose is change, but label it clearly. Equal visual area must represent equal quantity. Avoid 3D perspective, unexplained dual axes and truncated scales that manufacture drama.

Use colour for grouping, emphasis or ordered magnitude—not as wallpaper. Choose accessible contrast and palettes distinguishable under common colour-vision deficiencies. Direct labels beat legend hunting. Keep source, period, unit and definitions near the chart.

Exercise: Redesign a misleading chart three ways: honest axis, direct labels and an annotation that states the substantive change rather than merely the percentage.

Use Matplotlib/Seaborn in Python or ggplot2 in R to make distributions, faceted comparisons and residual diagnostics reproducibly. Start with raw observations where feasible, then overlay summaries. Small multiples often reveal subgroup patterns hidden by averages.

import seaborn as sns
import matplotlib.pyplot as plt

sns.scatterplot(data=df, x='delivery_days', y='revenue',
              hue='region', alpha=.55)
plt.title('Revenue does not rise uniformly with delivery time')
plt.xlabel('Delivery time (days)')
plt.ylabel('Revenue (MVR)')
sns.despine()
plt.tight_layout()

Exercise: Create a univariate plot for every key field, a relationship matrix for numeric measures and faceted trends by region. Write three observations and three questions—not conclusions—from EDA.

A useful dashboard has an audience, decision, cadence and action. Put the most important status and exception in the first viewing area. Use a hierarchy: headline KPIs, trend/context, drivers and row-level detail. Filters should answer real questions; too many create a control panel nobody understands.

Every KPI needs target or comparison. Show freshness, coverage and definitions. Make defaults meaningful. Preserve filter context when navigating. Test on the actual screen and with real users performing tasks—not only asking whether it “looks nice.”

Exercise: Sketch a dashboard on paper using only boxes and labels. Give a colleague three tasks. Change the layout based on where they hesitate.

A story is not decorative narration. It is a sequence that helps an audience understand why a finding matters. Establish the baseline and objective, reveal the change or gap, diagnose the evidence, quantify uncertainty, present options and recommend an action. Use annotations to point at evidence, not to repeat the title.

Lead with the conclusion for decision-makers: “Late delivery explains most of the retention gap; prioritise two high-volume routes.” Then show evidence and caveats. Separate observation (“return rate rose”) from interpretation (“likely connected to packaging change”) and recommendation (“pilot reinforced packaging”).

Exercise: Turn one dashboard into a five-slide story: decision, evidence, driver, option, recommendation. Each slide gets one sentence headline.

A fact table stores events or snapshots at a declared grain and numeric measures. Dimension tables describe who, what, where and when. In a star schema, one-to-many relationships flow from unique dimensions to facts. Surrogate keys stabilise changing business keys. A dedicated Date dimension enables consistent time intelligence.

A semantic model adds reusable measures, relationships, hierarchies, formats and business names. It is where “revenue” becomes one governed definition instead of fifteen dashboard formulas. Hide technical keys, organise measures and document definitions.

Exercise: Convert a wide sales file into FactSales, DimDate, DimProduct, DimCustomer and DimLocation. Declare each grain and relationship.

Use Power Query for ingestion and shaping, the Model view for relationships, DAX measures for business calculations and report pages for interaction. Keep transformations upstream where practical. Disable automatic date tables in governed models and use a marked Date table.

DAX has row context and filter context. CALCULATE evaluates an expression under modified filter context. DIVIDE handles zero denominators. Measures should be composable: base measures such as Revenue and Cost feed Margin and Margin %. Validate totals at every layer.

Orders := DISTINCTCOUNT(FactSales[OrderID])
Revenue := SUM(FactSales[Revenue])
Revenue YTD := TOTALYTD([Revenue], DimDate[Date])
Revenue YoY % := DIVIDE([Revenue]-[Revenue PY],[Revenue PY])

Exercise: Build a three-page Power BI report: Executive Overview, Drivers and Detail. Add a definition tooltip and freshness indicator.

Tableau builds views by placing dimensions and measures on Rows, Columns and the Marks card. Understand discrete versus continuous pills, aggregation, filters, parameters and table calculations. Relationships preserve logical table context; physical joins combine rows and can alter grain.

Level-of-detail expressions compute at explicit grains: {FIXED [Customer ID] : MIN([Order Date])} finds first order regardless of view detail. Use them when the business question’s grain differs from the visualisation’s grain, and document filter-order implications.

Exercise: Rebuild the same executive dashboard in Tableau. Compare defaults, interaction and calculation semantics with Power BI.

Define refresh frequency from decision latency, not habit. Monitor failures, source freshness, row counts and schema changes. Incremental refresh reduces work for large time-partitioned facts. Row-level security restricts data based on the viewer; test roles explicitly and remember that export permissions can bypass intended viewing patterns.

Governance includes owner, steward, certified source, metric dictionary, change log, access review, retention, support path and retirement criteria. Usage numbers alone do not prove value; interview users about decisions made and time saved.

Exercise: Write an operational runbook covering refresh, validation, failure alert, access request, metric change and rollback.

Begin every project with a one-page contract. Name the decision, the decision-maker, the deadline, the unit of analysis, the comparison, the success measure and the constraints. “Analyse sales” is not a question. “Which three product categories should the commercial director prioritise next quarter to improve gross margin without increasing returns?” is.

Exercise: Rewrite “Why are customers leaving?” as three answerable questions: descriptive, diagnostic and predictive. For each, state the exact decision that the answer would inform.

Descriptive asks what happened: revenue fell 8%. Diagnostic asks why: the fall is concentrated in returning customers after delivery times increased. Predictive estimates what may happen: customers with two late orders have a 34% churn probability. Prescriptive compares actions: prioritising recovery calls for high-value customers yields the largest expected retained margin.

The stages are not a ladder you must always climb. A reliable descriptive answer can be more valuable than a fragile model. Prediction does not prove causation, and a prescription requires costs, constraints and values—not merely an algorithm.

Exercise: For hospital waiting time, school attendance and resort energy use, write one question of each type. Circle the questions that require causal evidence.

A metric is a quantity; a KPI is a metric selected because it represents progress toward an objective. Good metrics have a clear definition, owner, frequency, source, grain and direction. Every rate needs a numerator, denominator and eligibility rule. “Conversion” is meaningless until you define who entered the funnel, what counts as conversion and how long they had.

Pair a goal metric with guardrails. If the goal is faster case closure, guardrails might include re-open rate, complaint rate and due-process compliance. This prevents local optimisation: improving a number while damaging the system.

Exercise: Create a metric dictionary for five measures. Include name, purpose, formula, exclusions, source, refresh frequency, owner and known limitations.

The grain is the meaning of one row. It might be one order, one order line, one customer-day or one island-month. Most catastrophic analysis errors are grain errors: joining a customer table to an order-line table and counting customers without deduplicating; averaging daily averages instead of weighting by observations; mixing snapshots with events.

Write the grain above every table. Identify the primary key that makes a row unique. Before a join, predict the expected relationship—one-to-one, one-to-many or many-to-many—and the expected row count.

-- Grain audit
SELECT COUNT(*) AS rows,
     COUNT(DISTINCT order_id) AS orders,
     COUNT(DISTINCT customer_id) AS customers
FROM order_lines;

Exercise: Given orders, order_lines, customers and products, state each grain and safe keys. Explain why SUM(orders.total) after joining to lines may inflate revenue.

Recognise numeric, categorical, ordinal, Boolean, text, date/time, geospatial and identifier fields. A phone number may contain digits but is not numeric. Satisfaction levels are ordered but the gap from “poor” to “fair” is not guaranteed to equal the gap from “good” to “excellent.” Dates carry calendars, time zones and business rules.

Tabular data has rows and columns; relational data separates entities into linked tables; semi-structured JSON nests keys and arrays; unstructured documents require extraction before conventional analysis. CSV does not store data types or formulas. Excel workbooks do. Parquet stores typed, compressed columns and is efficient for analytical workloads.

Exercise: Classify every field in the practice dataset. Mark identifiers, measures, dimensions and timestamps. Write one invalid operation for each type.

Data may come from operational databases, forms, sensors, surveys, spreadsheets, APIs, logs, public statistics or manual observation. For each field, document who created it, at what moment, for what operational purpose, under which validation rules, and which transformations occurred before you received it.

Lineage is the path from source to output. A defensible lineage record contains source location, extraction time, query or filter, raw-file checksum or immutable copy, transformation steps, output version and owner. Never overwrite raw data. Use folders such as 01_raw, 02_interim, 03_processed, 04_outputs.

Exercise: Draw lineage for a monthly dashboard fed by an online form and a finance database. Identify where duplicates, late records and definition changes could enter.

Quality has multiple dimensions: completeness (required values exist), validity (values obey rules), uniqueness (entities are not duplicated), consistency (definitions agree), timeliness (data arrives when useful), accuracy (values reflect reality) and integrity (relationships remain valid).

Profile row counts, distinct counts, missingness, min/max, distributions, date ranges, duplicate keys and referential integrity before changing anything. Missingness can mean “not applicable,” “not asked,” “unknown,” “refused,” “system failure” or genuine zero. Never replace all blanks with zero.

Quality report per column:
type | rows | distinct | missing % | invalid %
minimum | maximum | example values | rule violated

Exercise: Build a quality report. Create a quarantine table for rejected rows and a cleaning log with issue, rule, affected_rows, action and impact.

A sample is useful only relative to a target population. Probability sampling gives known selection probabilities; convenience and voluntary-response samples often overrepresent accessible or motivated people. Watch for coverage bias, non-response bias, survivorship bias, measurement error, selection on the outcome and historical processes encoded in administrative data.

More rows reduce random sampling error but do not remove systematic bias. Ten million app users cannot represent people without the app. Report who is missing, compare the sample with known population margins, use weights when justified and avoid universal language when the data covers a subgroup.

Exercise: Critique an online poll about public satisfaction. Define the target population, sampling frame, likely exclusions and a better design.

Use data for a legitimate, stated purpose. Minimise collection, restrict access, retain only as long as necessary and aggregate outputs where individual detail is not needed. Direct identifiers name a person; quasi-identifiers such as island, age and occupation can identify someone when combined. Pseudonymisation reduces exposure but is not anonymity.

Before analysis ask: could this harm a person or group? Is consent or lawful authority present? Are protected or sensitive attributes necessary? Could a proxy recreate them? Who can challenge an error? Suppress small cells, avoid publishing exact locations, document model limitations and never place confidential records into unapproved AI tools.

Exercise: Write a data-protection impact note for a staff-performance dashboard. Remove unnecessary fields and define role-based access.

Use separate sheets for README, Raw, Lookup, Work, Checks and Output. Convert ranges to Tables (Ctrl+T) so formulas and references expand safely. Keep one header row, one variable per column and one observation per row. Never merge cells inside data. Store dates as dates, not decorative text.

Use consistent colours sparingly: inputs, formulas, links and warnings. Freeze panes, apply validation, name important cells and include a visible “last refreshed” timestamp. Build control totals: row count, revenue sum, earliest/latest date, unmatched keys and duplicate count.

Exercise: Import the practice CSV, create the six-sheet architecture and make a Checks sheet that turns red if revenue or row count changes unexpectedly.

Master relative (A2), absolute ($A$2) and mixed (A$2, $A2) references. Core aggregation functions are SUM, AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK, SUMIFS, COUNTIFS and AVERAGEIFS. Use IF, IFS, AND, OR, IFERROR for explicit business logic.

=SUMIFS(Sales[Revenue],Sales[Region],$B2,Sales[Month],C$1)
=XLOOKUP([@Product_ID],Products[Product_ID],Products[Category],"Missing")
=FILTER(Sales[[Order_ID]:[Revenue]],Sales[Region]=H2,"No rows")
=LET(net,[@Revenue]-[@Cost],IF(net<0,"Loss",net))

Prefer XLOOKUP or INDEX/MATCH over legacy VLOOKUP when columns may move. Use UNIQUE, SORT, FILTER, SEQUENCE and LET in modern Excel. Text tools include TRIM, CLEAN, SUBSTITUTE, TEXTBEFORE, TEXTAFTER, LEFT, RIGHT and TEXTSPLIT.

Exercise: Build a reusable summary returning revenue, cost, margin, orders and average order value for any selected region and month.

Dates are serial values displayed with formats. Use DATE, YEAR, MONTH, DAY, EOMONTH, EDATE, WEEKDAY, NETWORKDAYS and DATEDIF where appropriate. Define reporting calendars explicitly: calendar month, fiscal period, ISO week or rolling 30 days are not interchangeable.

Clean text with TRIM for repeated spaces, CLEAN for non-printing characters, SUBSTITUTE for known patterns and case functions for presentation. Do not destroy the raw column; create a cleaned column and a flag showing what changed.

Exercise: Standardise three spellings of each island, parse mixed date strings, flag impossible future dates and calculate service days.

A PivotTable groups dimensions into Rows/Columns and aggregates measures in Values. Check whether Excel chose Sum or Count. Group dates carefully, show values as percentage of total or difference from previous period, add slicers and refresh after source changes. Distinct Count requires the Data Model.

Choose charts by question: bars for comparison, lines for time, scatterplots for relationships, histograms for distributions, box plots for spread, stacked bars for composition when totals matter. Avoid 3D, dual axes without strong justification and pie charts with many categories.

Exercise: Create a one-page performance view with four KPIs, monthly trend, region comparison and product mix. Add one sentence stating the decision supported.

Power Query is the repeatable ingestion and shaping layer: connect, set types, filter, split, replace, merge, append, group, pivot/unpivot and load. Every applied step becomes part of a refreshable recipe. Power Pivot stores multiple related tables in a Data Model. Prefer a star schema: fact table at a declared grain surrounded by dimensions such as Date, Customer and Product.

Total Revenue := SUM(Sales[Revenue])
Total Cost := SUM(Sales[Cost])
Gross Margin := [Total Revenue] - [Total Cost]
Margin % := DIVIDE([Gross Margin], [Total Revenue])
Prior Month Revenue := CALCULATE([Total Revenue], DATEADD('Date'[Date],-1,MONTH))

Measures respond to filter context; calculated columns compute row by row at refresh. Use measures for aggregations. Validate relationships and avoid uncontrolled bidirectional filtering.

Exercise: Load monthly files from a folder, append them, relate Sales to Date and Product dimensions, then build five measures and reconcile them to the raw totals.

Tables represent entities or events. A primary key uniquely identifies a row; a foreign key points to another table. SQL is declarative: describe the result, and the database plans how to obtain it. Logical query order is approximately FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT—which explains why a SELECT alias is often unavailable in WHERE.

SELECT order_id, order_date, region,
     revenue - cost AS gross_margin
FROM sales
WHERE order_date >= DATE '2026-01-01'
AND status = 'Completed'
ORDER BY gross_margin DESC
LIMIT 20;

Use IS NULL, not = NULL. Boolean logic needs parentheses when mixing AND and OR. Never use SELECT * in production outputs: declare the contract.

Exercise: Write ten queries using comparison, IN, BETWEEN, LIKE, null checks, calculated fields, sorting and limiting. Predict each row set first.

Aggregate functions collapse many rows: COUNT, SUM, AVG, MIN, MAX. Group by every selected non-aggregated field. WHERE filters rows before aggregation; HAVING filters groups afterward. Count deliberately: COUNT(*) counts rows, COUNT(column) excludes nulls, COUNT(DISTINCT key) counts unique non-null keys.

SELECT region,
     COUNT(DISTINCT order_id) AS orders,
     SUM(revenue) AS revenue,
     SUM(revenue-cost) AS margin,
     AVG(revenue) AS avg_line_revenue,
     SUM(CASE WHEN returned THEN 1 ELSE 0 END)::decimal
       / NULLIF(COUNT(*),0) AS return_rate
FROM sales
GROUP BY region
HAVING SUM(revenue) > 100000;

Exercise: Produce a region-month KPI table. Reconcile totals with an ungrouped query and explain why average line revenue differs from average order value.

INNER JOIN keeps matches; LEFT JOIN keeps every left row; FULL OUTER JOIN exposes unmatched rows from both; a self-join relates rows in one table. Begin by testing key uniqueness and relationship cardinality. After the join, compare row counts, distinct keys and control totals.

SELECT s.order_id, s.revenue, p.category
FROM sales s
LEFT JOIN products p ON s.product_id = p.product_id;

-- Unmatched keys
SELECT s.product_id, COUNT(*)
FROM sales s LEFT JOIN products p USING (product_id)
WHERE p.product_id IS NULL
GROUP BY s.product_id;

UNION stacks compatible results and removes duplicates; UNION ALL preserves them and is usually faster. INTERSECT keeps shared rows; EXCEPT keeps rows in the first result not the second.

Exercise: Intentionally create a many-to-many join, diagnose the inflation, then repair it by aggregating or deduplicating to the required grain.

Common Table Expressions (WITH) name intermediate results and make complex logic readable. Window functions calculate across related rows without collapsing them. The OVER clause defines partitions and ordering. Learn ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running sums and moving averages.

WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month,
       region, SUM(revenue) AS revenue
FROM sales GROUP BY 1,2
)
SELECT month, region, revenue,
     LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prior,
     revenue - LAG(revenue) OVER
       (PARTITION BY region ORDER BY month) AS change,
     RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS rank
FROM monthly;

Exercise: Calculate customer first purchase, days between orders, rolling three-month revenue and top three products per region.

Time analysis needs an event timestamp, consistent timezone and defined period. Use date truncation for calendar periods and interval arithmetic for windows. A cohort groups entities by a starting event—often first purchase—and tracks subsequent activity. A funnel counts eligible entities completing ordered stages within a defined window.

Prevent denominator drift. Every funnel stage should be based on the same eligible cohort, unless you explicitly report stage-to-stage conversion. Deduplicate repeated events and decide whether order matters.

Exercise: Create monthly acquisition cohorts and a retention matrix for months 0–5. Then build visit → signup → purchase conversion with both overall and stage-to-stage rates.

Causal questions compare an observed outcome with the counterfactual outcome that would have occurred under another action. Because one unit cannot be observed in both states at the same time, design creates a credible comparison. Random assignment balances known and unknown confounders on average. Define unit of randomisation, eligibility, treatment, primary outcome, guardrails, sample size and analysis plan before looking at results.

Analyse by intention-to-treat when appropriate: compare groups as assigned. Check sample-ratio mismatch, attrition, treatment exposure and novelty effects. Do not stop an experiment whenever significance appears. For observational work, use domain knowledge and causal diagrams; techniques such as matching, regression adjustment, interrupted time series and difference-in-differences rely on strong assumptions.

Exercise: Design an A/B test for a reminder message. Specify hypotheses, primary metric, guardrails, minimum detectable effect, duration, exclusions and decision rule.

A time series is ordered and usually autocorrelated. Decompose level, trend, seasonality, cycles and irregular variation. Establish naïve baselines: last value, seasonal last value and historical average. More complex models must beat them on future-like validation.

Never randomly split time. Train on the past and validate on later periods using rolling-origin evaluation. Metrics include MAE, RMSE and scale-free alternatives; MAPE fails around zero. Add intervals, not just point forecasts. Structural breaks, promotions, policy changes and capacity constraints can invalidate patterns.

# leakage-safe idea
train = data.loc[data.date < '2026-01-01']
test  = data.loc[data.date >= '2026-01-01']
seasonal_naive = test.value.shift(12)  # conceptual monthly baseline

Exercise: Forecast six months of regional demand using seasonal naïve and a trend model. Backtest both, graph errors and write circumstances under which neither should be trusted.

Segmentation groups observations for understanding or action. Begin with interpretable rule-based segments such as recency-frequency-monetary scores. K-means assigns scaled numeric points to the nearest centroid and minimises within-cluster squared distance. It assumes roughly spherical clusters and requires k; results depend on scaling and initialisation.

Use silhouette scores and stability checks as diagnostics, not automatic truth. Profile clusters on variables not used to create them. A cluster is useful only if it is distinct, stable, understandable, reachable and connected to a decision. Do not turn algorithmic groupings into essential claims about people.

Exercise: Standardise three behaviour variables, compare k=2 through k=6, name segments from evidence and propose an action with an ethical guardrail for each.

Supervised learning maps features to a known target. Regression predicts quantities; classification predicts classes or probabilities. Establish a baseline, split before preprocessing and fit transformations only on training data. Cross-validation estimates variation, but folds must respect time, groups and repeated entities.

Know the common models: linear/logistic regression provides a strong interpretable baseline; decision trees learn rule splits; random forests average trees; gradient boosting builds sequential corrections; k-nearest neighbours predicts from nearby scaled points; naïve Bayes combines feature evidence under conditional-independence assumptions. Choose metrics from the decision: MAE/RMSE for numeric error; precision, recall, F1, ROC-AUC, PR-AUC, log loss and calibration for classification. Accuracy is weak under imbalance.

from sklearn.pipeline import make_pipeline
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LogisticRegression

model = make_pipeline(SimpleImputer(), StandardScaler(),
                    LogisticRegression(max_iter=1000))

Inspect subgroup errors, calibration, threshold trade-offs and drift. Feature importance is not causality. Deep neural networks, CNNs, RNNs, reinforcement learning, TensorFlow and PyTorch belong to specialist paths; learn them only when unstructured data, sequential decisions or scale makes them appropriate.

Exercise: Build a churn classifier with a dummy baseline and logistic regression. Use a pipeline, cross-validation, confusion matrix and threshold chosen from intervention cost.

R is a first-class analytical language. Use vectors, lists, data frames and functions; the tidyverse provides readr for import, dplyr for manipulation, tidyr for reshaping and ggplot2 for visualisation. The pipe expresses sequences. R Markdown or Quarto combines code, output and narrative.

library(tidyverse)
sales <- read_csv("data-analyst-field-manual-practice.csv") |>
mutate(margin = revenue - cost,
       month = lubridate::floor_date(order_date, "month")) |>
group_by(month, region) |>
summarise(revenue = sum(revenue), .groups = "drop")

Do not learn Python and R simultaneously from zero. Become productive in one, then translate a completed project into the other. Concepts—grain, types, joins, missingness, models and validation—transfer.

Exercise: Reproduce one pandas groupby, one SQL join and one Seaborn chart using dplyr and ggplot2.

An API exposes structured requests and responses. Read documentation for endpoint, method, authentication, parameters, pagination, rate limits, status codes and schema. Store the raw response and request metadata. Handle retries with backoff, timeouts and partial failure. Flatten JSON deliberately: preserve parent keys when exploding arrays.

import requests

rows, page = [], 1
while True:
  response = requests.get(API_URL, params={'page': page}, timeout=30)
  response.raise_for_status()
  payload = response.json()
  rows.extend(payload['results'])
  if not payload.get('next'): break
  page += 1

For web scraping, prefer published downloads and APIs. Check terms, robots guidance, copyright, privacy and load. Identify yourself where appropriate, rate-limit requests and never bypass access controls. Pages change; write selectors defensively and validate record counts.

Exercise: Retrieve a paginated public API, save raw JSON with timestamp, normalise it into a table and produce a schema/quality report.

Git records changes to text files. A repository contains commits; branches isolate work; pull requests support review. Commit logical changes with messages that explain intent. Never commit passwords, tokens, personal data, giant exports or generated caches. Use .gitignore and environment variables.

git status
git switch -c analysis/customer-retention
git add src/ README.md
git commit -m "Add cohort retention calculation and checks"
git diff main...HEAD

Version SQL, Python/R, Power Query definitions where possible, metric documentation and small synthetic samples. For binary BI files, use disciplined naming and release notes because line-by-line merges are limited.

Exercise: Create a repository with README, data dictionary, src, notebooks, tests, outputs and .gitignore. Make five focused commits and review the diff.

Operational systems optimise transactions; analytical warehouses optimise scans, joins and history. ETL transforms before loading; ELT loads raw data then transforms inside the analytical platform. A robust pipeline is idempotent, observable and restartable. It records run time, input versions, row counts, rejected records and outcome.

Facts can be transactional, periodic snapshots or accumulating snapshots. Slowly changing dimensions handle attribute history: Type 1 overwrites; Type 2 creates dated versions. Choose based on whether historical reports must preserve what was known then.

Exercise: Design daily ingestion from an order system to a warehouse. Include incremental key, late-arriving updates, duplicate protection, quality tests and failure recovery.

Big data is not “a large Excel file.” Relevant challenges include volume, velocity, variety and distributed failure. Columnar formats such as Parquet reduce analytical I/O. Partitioning prunes data; bad partitions create tiny files or skew. Parallel processing divides work, but shuffles and joins are expensive.

Hadoop popularised distributed storage and MapReduce; Spark provides distributed DataFrame and SQL processing, including in-memory execution. Cloud object storage separates storage from compute. Modern warehouses and lakehouses offer elastic SQL. MPI is important in high-performance computing but uncommon in everyday analyst work.

Learn concepts before platforms: storage, compute, partitions, orchestration, identity, encryption, cost and observability. Most analysts need competent SQL and data modelling long before custom Spark code.

Exercise: Explain when a 50-million-row dataset should stay in a warehouse versus move to Spark. Include latency, transformations, concurrency, skills and cost.

Ask what decision is pending, who owns it, what options exist, what evidence would change the choice and what deadline matters. Learn the operational process from the people doing it. Repeat definitions back. Expose disagreements early: two departments may use “active customer” differently.

Manage scope using a question tree: primary question, drivers, drill-downs and excluded questions. Agree on a minimum useful output before adding attractive extras. Send a written recap with definitions and open assumptions.

Exercise: Conduct a 20-minute mock discovery meeting. Produce a decision statement, metric definitions, stakeholder map, risks and acceptance criteria.

Use a decision brief: one-sentence answer, why it matters, three evidence points, uncertainty/limitations and recommended action with owner and timing. Put methods in an appendix unless they determine interpretation. Quantify magnitude and baseline: “rose 20%” is incomplete without 5 to 6 versus 500 to 600.

Use calibrated language. “The data shows” for direct observation; “is associated with” for non-causal relationships; “we estimate” for models; “the experiment indicates” for randomised effects; “we do not know” when evidence is absent.

Exercise: Write a 150-word executive brief from a project. Delete every sentence that does not change understanding or action.

Begin with the decision and headline. Structure: context, question, answer, evidence, alternative explanations, recommendation and next step. One slide should perform one job. Put the conclusion in the title. Rehearse transitions and the 30-second, 3-minute and 15-minute versions.

When challenged, identify whether the disagreement concerns data, definition, method, interpretation or values. Do not defend a weak result for pride. State what would change your conclusion. Keep a backup appendix for definitions, sample construction and robustness checks.

Exercise: Record a five-minute presentation. Watch without sound for visual clarity, then listen without video for reasoning. Revise both.

Separate code review, data review, statistical review and communication review. A strong checklist asks: Is the question answerable? Is grain correct? Are exclusions justified? Do joins preserve totals? Are denominators stable? Are methods appropriate? Is uncertainty reported? Do charts match numbers? Can outputs be reproduced?

Use adversarial checks: independently calculate one KPI, inspect random records, test edge cases, reverse a filter, compare with an external benchmark and ask what result would appear if a key assumption were wrong.

Exercise: Exchange projects. Reviewer logs issues by severity and evidence; author responds with fix, accepted limitation or reasoned disagreement.

A README states purpose, decision, structure, prerequisites, exact run steps and outputs. A data dictionary defines each field, type, unit, allowed values, missing meaning and source. A methodology note records population, time period, transformations, tests, assumptions and limitations. A runbook explains refresh, failure and ownership.

Document why, not merely what the code visibly does. Link definitions to the code implementing them. Record changes in a changelog. Archive obsolete outputs clearly so users do not mistake them for current truth.

Exercise: Give your repository to someone with no verbal explanation. Record every question they must ask; answer those questions in the documentation.

Three excellent projects beat twelve shallow tutorials. Each case study should show business question, stakeholder, data source, grain, quality problems, method choices, validation, result, uncertainty, recommendation and reflection. Include code/query/workbook, data dictionary, polished output and a README that reproduces it.

Use public, synthetic or properly anonymised data. Never publish employer data. Show evolution: initial hypothesis, failed approach and revision demonstrate judgment. Write for a hiring manager who has six minutes, then offer technical depth for a reviewer.

Exercise: Audit every project with a rubric: question 15, data integrity 20, method 20, reproducibility 15, communication 20, ethics 10. Do not publish below 75/100.

1. Data quality audit: profile, rule table, cleaning log and before/after report.
2. Excel operations dashboard: Power Query, Data Model, DAX and executive page.
3. SQL business investigation: joins, cohorts, funnel and window functions.
4. Exploratory report: distributions, anomalies, subgroup patterns and questions.
5. Statistical comparison: estimate, interval, effect size and decision memo.
6. Public-service dashboard: accessible BI with definitions and guardrails.
7. Experiment design: pre-analysis plan plus simulated analysis.
8. Forecast: baselines, rolling backtest, intervals and operational limits.
9. Segmentation/model: leakage-safe evaluation, subgroup errors and action.
10. Capstone: complete product from stakeholder brief to maintained output.

Maldives capstone ideas: ferry reliability, resort energy demand, local-app adoption, food-price tracking, public-service waiting times, island waste collection, fisheries price chains or training-programme evaluation. Use only data you are authorised to use.

SQL interviews test filtering, aggregation, joins, nulls, dates, CTEs and windows. Clarify grain and expected output before typing. Think aloud, use small examples and test edge cases. Analytics cases test question framing, metrics, segmentation, experiment design and communication—not only calculation.

Prepare stories using Situation–Task–Action–Result–Reflection for ambiguous requests, bad data, disagreement, error caught, deadline trade-off and impact. Be able to explain every portfolio line: why this method, what could be wrong, how validated and what action followed.

Exercise: Complete a 45-minute mock: 20 minutes SQL, 15 minutes case, 10 minutes project explanation. Review recording against clarity, correctness and structure.

Prioritise requests by decision value, urgency, effort, risk and reusability. Negotiate deliverables instead of quietly missing deadlines. Share early prototypes. Keep a decision log and reusable query/metric library. Measure impact after delivery: adoption, time saved, error reduced or decision changed.

Continue learning through a loop: identify a work problem, learn the smallest necessary concept, apply it, seek review, document the pattern and teach it. Certifications may structure learning, but they do not replace projects. AI can draft formulas, queries and code; you remain responsible for data permission, definitions, tests, validation and communication.

Final exercise: Write a 90-day professional plan with one technical skill, one business domain, one communication habit, one portfolio release and one person from whom you will seek review.