Glossary · 161 terms

Data analytics glossary

The words analysts, managers and interviewers use, each in one plain sentence. Where a level uses the idea, the link takes you there to try it on real data.

A

A/B test
A controlled experiment that randomly splits users between two versions and compares one metric.
Statistics · Check the split before the resultStatistics · Conversion per arm and the lift
Absolute reference
A cell reference locked with $ ($R$1) so it does not move when the formula is copied.
Excel · Prices with tax, one rate for every row Excel cheat sheet
Aggregate function
A function that turns many rows into one value: COUNT, SUM, AVG, MIN, MAX.
SQL · Total units and revenue SQL cheat sheet
Alias
A temporary name for a column or table in a query, written with AS (revenue AS rev, orders AS o).
SQL · Compute margin per product
Anti-join
A join that keeps the rows with no match on the other side, such as customers who never ordered.
SQL · Customers who never orderedPython · Customers who never ordered SQL cheat sheet
AOV (average order value)
Revenue divided by the number of orders over the same period.
SQL · Customer lifetime valueCasework · Holiday season review in SQL SQL cheat sheet
Attribution
Deciding which marketing channel gets credit for a conversion (last click, first click or shared).
Casework · Cost per conversion in SQL

B

Boolean mask
A Series of True/False values used to select rows in pandas: df[df["price"] > 50].
Python · Only the marketplace orders pandas cheat sheet
Box plot
A chart of the median, quartiles and outliers of a variable; good for comparing distributions across groups.
Bucketing (binning, banding)
Grouping a numeric value into ranges (under $50, $50–199, $200+) to count or compare them.
SQL · Products by price bandPython · Tag orders by size pandas cheat sheet

C

CALCULATE
The DAX function that evaluates an expression with filters added, replaced or removed; the base of most advanced measures.
Calculated column
A DAX column computed row by row at refresh and stored in the model; good for grouping and slicing, not for totals.
Cardinality
The type of a relationship between tables: one-to-one, one-to-many or many-to-many.
SQL · Lines per order without double counting
CASE expression
SQL's if/else: returns the value of the first WHEN that is true, else the ELSE value.
SQL · Products by price band SQL cheat sheet
CAST and CONVERT
Change a value's data type, for example text to number; CONVERT is SQL Server's variant with style options.
SQL cheat sheet
Churn
Customers who stop buying or cancel in a period; churn rate is that number over customers at the start.
Casework · Customer retention in SQL
CLV (customer lifetime value, LTV)
The total revenue (or margin) a customer brings over their whole relationship with the business.
SQL · Customer lifetime value
COALESCE
Returns the first argument that is not NULL; the usual way to replace NULL with a default.
SQL · Shipping and merchandise revenue per channel SQL cheat sheet
Cohort
A group of customers who share a starting point, usually the month of their first order.
SQL · Boss: cohort retentionCasework · Customer retention in SQL
Cohort analysis
Tracking each cohort separately over time, so new and old customers are not mixed in one rate.
SQL · Boss: cohort retentionCasework · Customer retention in pandas
Conditional aggregation
An aggregate over only some rows of each group, written with CASE inside SUM/AVG or a FILTER clause.
SQL · Cancellation rate by channel SQL cheat sheet
Conditional formatting
Formatting that changes with a cell's value (colour scales, highlighted duplicates, icons) to make patterns visible.
Confidence interval
A range built from the sample that would contain the true value in, say, 95% of repeated samples.
Statistics · How sure is the mobile rate?Statistics · Is the lift real?
Constraint
A rule a database enforces on a column or table: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK.
Conversion rate
The share of visits or users that complete a goal, such as sessions that end in an order.
Python · Boss: who converts on the website?Casework · Cost per conversion in SQL SQL cheat sheet
Correlated subquery
A subquery that refers to the outer row, so it runs once per row (EXISTS checks are the common case).
SQL · Customers who returned something
Correlation
How strongly two variables move together, from −1 to 1. It does not show that one causes the other.
Excel · Price vs units sold on a scatter chartExcel · Does traffic move with sales? CORREL
Cost per conversion (CPA, cost per acquisition)
Marketing spend divided by the conversions it produced in the same period.
Casework · Cost per conversion in SQLCasework · Cost per conversion in pandas
CTE (common table expression, WITH clause)
A named step inside a query (WITH name AS (...)) that later steps can select from like a table.
SQL · Customer lifetime value SQL cheat sheet

D

Dashboard
One screen of the key metrics and charts for a question or a team, built to be checked at a glance.
Data analysis process
The usual cycle: ask the question, collect the data, clean it, analyse it, share the findings, act on them.
Data cleaning
Fixing errors and inconsistencies (spaces, casing, duplicates, wrong types, impossible values) before analysis.
Python · Boss: the clean 2025 reportSQL · Customers by state, cleaned up pandas cheat sheet
Data consolidation
Combining data from several sheets, files or sources into one summary, for example twelve monthly files into a yearly table.
Data storytelling
Presenting findings as a short argument: the question, the answer with its number, the evidence, and what to do next.
Data type
What kind of value a column holds (integer, decimal, text, date, boolean), which decides what you can do with it.
Python · Which weekday sells most?
Data validation
An Excel rule on what a cell accepts (a list, a number range, dates, text length), shown as a drop-down or an error alert.
Data warehouse
A central database that collects cleaned data from many systems for reporting and analysis.
DataFrame
pandas' table: labelled columns of possibly different types, sharing one row index.
Python · Your first DataFrame pandas cheat sheet
Date table (calendar table, DimDate)
A table with one row per day and columns such as year, month and weekday, related to the facts for time analysis.
DATE_TRUNC
Rounds a date or timestamp down to the start of its month, week or year; the standard way to group by period.
SQL · Monthly revenue for 2025 SQL cheat sheet
DAX (Data Analysis Expressions)
The formula language of Power BI and Power Pivot, used for measures, calculated columns and calculated tables.
DDL (data definition language)
SQL that defines structure: CREATE, ALTER and DROP for tables, views and constraints.
Deduplication
Finding records that describe the same thing and keeping one, usually after cleaning the key.
SQL · Find duplicate customer recordsPython · Find duplicate customers SQL cheat sheet
Dictionary (dict)
A Python collection of key → value pairs, used for lookups and counting.
Python · Update the stock sheetPython · Count orders by channel
Dimension table
A table that describes the things in the facts (products, customers, dates) and is used to filter and group them.
SQL · Revenue by category
DISTINCT
Removes repeated rows from a result; COUNT(DISTINCT x) counts different values.
SQL · List the category tree
Distribution
How often each value or range of values occurs in a variable; a histogram draws it.
Python · Count orders by status
DML (data manipulation language)
SQL that changes rows: INSERT, UPDATE and DELETE (SELECT is often counted here too).
dtype
The data type of a pandas column, such as int64, float64, object (text), bool or datetime64.
Python · How big is the table?Python · Which weekday sells most?

E

EDA (exploratory data analysis)
A first pass over new data with counts, summaries and charts to learn its shape, gaps and oddities before answering anything.
Python · Boss: the first-look report pandas cheat sheet
ETL (extract, transform, load)
Pulling data out of source systems, cleaning and reshaping it, and loading it where analysts query it.
Excel Table (structured table, Ctrl+T)
A range turned into a named table that grows with new rows, carries formulas down and allows references by column name.
Excel cheat sheet
Execution order (logical query order)
The order SQL evaluates a query in: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
SQL · Why does this query fail?
EXISTS
A condition that is true when a subquery returns at least one row; NOT EXISTS is the NULL-safe anti-join.
SQL · Customers who returned something

F

f-string
A Python string with values filled in from variables: f"Total: {total:.2f}".
Python · Format a shelf label
Fact table
The table of events with numbers to add up (order lines, payments), plus keys to the dimensions.
SQL · Revenue by category
Fan-out
Rows multiplying after a one-to-many join, which inflates sums of columns from the "one" side.
SQL · Lines per order without double countingPython · Why did shipping revenue double? SQL cheat sheet
Fill down
Copying a formula into the cells below, with relative references shifting one row each time.
Excel · Prices with tax, one rate for every row Excel cheat sheet
Filter context
In DAX, the set of filters applied to a calculation by the visual, slicers and CALCULATE.
Foreign key
A column that points at the primary key of another table, such as orders.customer_id.
SQL · Revenue by category
Funnel
The steps users go through towards a goal (visit, cart, checkout, order) and how many drop out at each.
Python · Boss: who converts on the website?

G

Granularity (grain)
What one row of a table represents: one order, one order line, one customer per day.
SQL · Lines per order without double countingPython · Why did shipping revenue double?
Gross margin
Revenue minus the cost of the goods sold, often shown as a percentage of revenue.
SQL · Compute margin per product SQL cheat sheet
GROUP BY
Splits rows into groups that share values, so aggregates return one row per group.
SQL · Orders by channel and status SQL cheat sheet
groupby
pandas' split-apply-combine: split rows by keys, aggregate each group, combine the results.
Python · Average shipping fee per channelPython · A category summary table pandas cheat sheet

H

Half-open date range
A filter with >= start and < next start, which catches every timestamp in the period with no overlap.
SQL · Orders from March 2024 SQL cheat sheet
HAVING
Filters groups after aggregation (HAVING COUNT(*) > 5); WHERE filters rows before.
SQL · Our most loyal customers SQL cheat sheet
Heatmap
A grid coloured by value, often used for a correlation matrix or for counts by weekday and hour.
Histogram
A chart of how many values fall into each range of a numeric variable; shows the shape of a distribution.
HLOOKUP
The sideways twin of VLOOKUP: searches the first row of a range and returns a value from a row below it.
Excel cheat sheet
Hypothesis test
A procedure that checks whether data is surprising enough to reject a "no effect" assumption.

I

Import and DirectQuery
Power BI storage modes: Import keeps a refreshed copy in the model (fast); DirectQuery asks the source on every view (fresh, slower).
Index
The row labels of a pandas Series or DataFrame; groupby keys become the index unless you reset it.
Python · Your first DataFramePython · Rows by position, columns by name
INDEX/MATCH
A two-function lookup: MATCH finds the row position of a key, INDEX returns the value at that position.
Excel · INDEX/MATCH, right to leftExcel · What a lookup returns for a missing key Excel cheat sheet
Inner join
Keeps only rows that have a match in both tables.
SQL · Revenue by categoryPython · Add product names to order lines SQL cheat sheet
IQR (interquartile range)
The range between the 25th and 75th percentiles; 1.5 × IQR beyond them is a common outlier rule.
Excel · Outliers in a quantity columnPython · Read the price boxes
Iterator (SUMX, AVERAGEX)
A DAX function that evaluates an expression for every row of a table and then aggregates, such as SUMX(Sales, [Qty] * [Price]).

K

KPI (key performance indicator)
One of the few metrics a team agrees to judge its performance by, with a clear definition and target.
SQL · Boss: performance snapshot

L

LAG and LEAD
Window functions that read the previous (LAG) or next (LEAD) row's value, used for period-over-period change.
SQL · Month-over-month growth SQL cheat sheet
Lambda
A small unnamed Python function written inline, often as a sort key: key=lambda p: p["price"].
Python · Sort products with a key
Left join
Keeps every row of the left table; columns from the right are NULL where nothing matches.
SQL · Customers who never orderedPython · Customers who never ordered SQL cheat sheet
List comprehension
Python's one-line loop that builds a list: [p for p in prices if p > 50].
Python · Filter products in one line
loc and iloc
pandas selectors: loc by labels and boolean masks, iloc by integer positions.
Python · Rows by position, columns by name pandas cheat sheet

M

matplotlib
The core Python plotting library (plt.plot, plt.bar, plt.hist); pandas .plot() is built on it.
Mean
The sum of the values divided by how many there are; pulled up or down by extreme values.
SQL · Total units and revenue
Measure
A DAX calculation evaluated at query time for the current filters, such as Total Revenue; the right home for KPIs.
Median
The middle value when the values are sorted; half are above, half below, outliers barely move it.
Excel · Summarise the lighting rangeCasework · Frame the headcount and pay question Excel cheat sheet
merge
pandas' join: combines two DataFrames on key columns, with how="inner", "left", "right" or "outer".
Python · Add product names to order lines pandas cheat sheet
Merge and Append (Power Query)
Merge joins two queries on a key, like a SQL join; Append stacks queries with the same columns, like UNION ALL.
Python · Add product names to order lines
Mixed reference
A reference with only the row or only the column locked (A$1, $A1), used to fill one formula across a grid.
Excel · Prices with tax, one rate for every row Excel cheat sheet
Mode
The most frequent value in a set; the natural "average" for categories.
MoM (month over month)
Change from one month to the previous: (this month − last month) ÷ last month.
SQL · Month-over-month growth SQL cheat sheet
Moving average (rolling average)
The average of the last N periods at each point, which smooths day-to-day noise.
SQL · 7-day moving average of orders SQL cheat sheet

N

Named aggregation
groupby(...).agg(new_name=("column", "function")): several aggregates with clear output names in one step.
Python · A category summary table pandas cheat sheet
NaN
Not a Number: pandas' marker for a missing value in numeric and text columns; most aggregates skip it.
Python · Where do customers come from?Python · What does mean() do with missing costs? pandas cheat sheet
Normalisation (text)
Making equal values look equal before comparing: trimming spaces, fixing case, unifying formats.
Python · Fix the state codesSQL · Customers by state, cleaned up SQL cheat sheet
North star metric
The single metric a company treats as the best sign of long-term value for customers.
SQL · Boss: performance snapshot
NTILE
A window function that splits ordered rows into N roughly equal buckets, such as quartiles of customers by spend.
NULL
A missing or unknown value. Any comparison with NULL is unknown, so test it with IS NULL.
SQL · The NULL comparison trapSQL · How many customers came from referrals? SQL cheat sheet
Null hypothesis
The default assumption a test tries to reject, usually "there is no difference".
NULLIF
Returns NULL when its two arguments are equal; NULLIF(x, 0) avoids division by zero.
Casework · Cost per conversion in SQL SQL cheat sheet

O

OLAP
A database or model built for analysis: big scans and aggregates over history, often a star schema in a warehouse.
OLTP
A database that runs day-to-day operations with many small reads and writes, such as the shop's order system.
One-to-many relationship
One row on one side matches many on the other: one order has many order lines.
SQL · Lines per order without double counting
Outlier
A value far from the rest. It may be an error to fix or a real rare case to keep and explain.
SQL · Boss: data quality sweep

P

p-value
Assuming no real effect, the probability of a result at least as extreme as the one observed.
Statistics · What p = 0.03 actually saysStatistics · Test the test with an A/A split
PARTITION BY
Splits rows into groups for a window function without collapsing them, like GROUP BY that keeps every row.
SQL · Top 3 products in every category SQL cheat sheet
Percentile
The value below which a given share of observations fall; the 90th percentile beats 90% of values.
Python · Read the price boxesPython · Which orders are unusually large?
Pivot table
A summary with one variable down the rows, another across the columns, and an aggregate in each cell.
Casework · Holiday season review in pandas pandas cheat sheet
Power BI
Microsoft's reporting tool: Power Query to load data, a data model with DAX measures, and interactive reports published to the Power BI Service.
Power BI Service
The online side of Power BI where reports are published, shared in workspaces, refreshed on a schedule and secured.
Power Query
The tool in Excel and Power BI that connects to sources and records cleaning steps (filter, split, merge, append) to repeat on every refresh.
Pre-aggregation
Summarising a table to the grain you need before joining it, to avoid fan-out.
SQL · Shipping and merchandise revenue per channel SQL cheat sheet
Primary key
The column (or columns) that uniquely identifies each row of a table, such as order_id.
SQL · Look at the product catalog

R

RANK and DENSE_RANK
Window functions that number rows with ties sharing a number; RANK skips after a tie (1, 2, 2, 4), DENSE_RANK does not.
SQL · ROW_NUMBER vs RANK vs DENSE_RANK SQL cheat sheet
Relative reference
A cell reference (A2) that shifts with the formula when it is copied or filled.
Excel · Line totals with cell references Excel cheat sheet
Repeat purchase rate
The share of customers who order more than once, usually within a fixed window.
SQL · Boss: first orders and repeat purchasesCasework · Customer retention in SQL
Retention rate
The share of a cohort still active (ordering, subscribed) after a given time.
Casework · Customer retention in SQLSQL · Boss: cohort retention
Return rate
Returned items (or orders) divided by items (or orders) sold in the same period.
Python · Boss: where do returns come from?SQL · Boss: category scorecard SQL cheat sheet
Revenue
Money from sales. Say which: gross or net of discounts, refunds and shipping.
SQL · Total units and revenuePython · Compute line revenue
Right-censoring
When recent customers have not had the full time window yet, so their retention looks lower than it will be.
Casework · Customer retention in SQLCasework · Customer retention in pandas
Row context
In DAX, "the current row" while a calculated column or an iterator such as SUMX walks a table.
ROW_NUMBER
A window function that gives each row a unique number in the window order, used for top-N per group and de-duplication.
SQL · Top 3 products in every category SQL cheat sheet
Row-level security (RLS)
Rules that filter which rows each user can see in the same report, such as one region per manager.
Running total (cumulative sum)
A sum that adds each row to everything before it, such as year-to-date revenue.
SQL · Year-to-date revenue SQL cheat sheet

S

Sample size
The number of observations in a sample; bigger samples give more precise estimates.
Excel · Average pages per visit, mobile onlyStatistics · Boss: how big should the sample be?
Scatter plot
A chart of one numeric variable against another, one dot per row; shows relationships and outliers.
Schema
The structure of a database: its tables, their columns and types, and how they relate.
SQL · Look at the product catalog
seaborn
A Python library on top of matplotlib for statistical charts in one line (histplot, boxplot, scatterplot, heatmap).
Seasonality
A pattern that repeats with the calendar, such as the November–December peak in home goods.
Casework · Holiday season review in SQL
Segmentation
Splitting customers or orders into groups (channel, segment, region) to compare them.
SQL · Boss: customer base profile
Self-join
Joining a table to itself, for example employees to their managers in the same table.
SQL · Who reports to whom SQL cheat sheet
Semi-join
Keeps the rows that have at least one match, without adding columns or duplicating rows (EXISTS, IN, isin).
SQL · Customers who returned something
Series
A single pandas column: values plus an index. df["col"] is a Series; df[["col"]] is a DataFrame.
Python · Series or DataFrame? pandas cheat sheet
Simpson's paradox
A trend that holds in every group but reverses when the groups are combined, because of different group sizes.
Casework · Check and build the web funnel in SQLCasework · Check and build the web funnel in pandas
Skew
A lopsided distribution with a long tail on one side; order values usually have a long right tail.
Excel · A typical order line with AVERAGE, MEDIAN and MODE.SNGLStatistics · Why the average order lies
Slicer
A clickable filter panel for a PivotTable, Excel Table or Power BI report.
SMART goal
A goal that is Specific, Measurable, Achievable, Relevant and Time-bound; the way to turn "more sales" into a question data can answer.
SSMS (SQL Server Management Studio)
Microsoft's desktop tool for writing queries against SQL Server and managing its databases.
Standard deviation
The typical distance of values from their mean: how spread out the data is.
Excel · Same average, different days with STDEV.SExcel · STDEV.S or STDEV.P?
Standard error
How much an estimate such as a mean would vary between samples: standard deviation ÷ √n.
Statistics · Boss: how big should the sample be?Statistics · How sure is the mobile rate?
Star schema
A data model with one fact table in the middle joined one-to-many to dimension tables around it.
Statistical significance
A result unlikely under the null hypothesis at a chosen threshold (often p < 0.05); not the same as important.
Structured reference
A formula reference to an Excel Table column by name, such as Orders[Quantity], instead of a range like F2:F41.
Subquery
A query inside another query, in WHERE, FROM or SELECT.
SQL · Products priced above average SQL cheat sheet
SUMIFS and COUNTIFS
Excel functions that sum or count only the rows meeting one or more conditions: Excel's GROUP BY.
Excel · Several conditions with SUMIFS and COUNTIFSExcel · Criteria strings, wildcards and a threshold cell Excel cheat sheet

T

T-SQL (Transact-SQL)
Microsoft SQL Server's dialect of SQL, with TOP, GETDATE, ISNULL and DATEADD where this site uses LIMIT, CURRENT_DATE, COALESCE and intervals.
SQL cheat sheet
Three-valued logic
SQL conditions can be true, false or unknown (when NULL is involved); WHERE keeps only true.
SQL · The NULL comparison trap
Time intelligence
DAX functions that compare periods (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD); they need a proper date table.
Casework · Holiday season review in SQL
Time series
Values recorded over time at a regular interval, such as orders per day.
SQL · Monthly revenue for 2025SQL · 7-day moving average of orders
Trend line (line of best fit)
A straight line fitted through a scatter of points to show the overall direction of the relationship.
Type I error (false positive)
Concluding there is an effect when there is none.
Type II error (false negative)
Missing an effect that is really there; power is the chance of avoiding it.
Statistics · Boss: plan the banner test

U

UNION and UNION ALL
Stack the rows of two queries with matching columns; UNION removes duplicate rows, UNION ALL keeps them all.

V

Vectorised operation
An operation applied to a whole column at once in compiled code, much faster than a Python loop over rows.
Python · Compute line revenue pandas cheat sheet
View
A saved query that can be selected from like a table; it stores the logic, not the data.
VLOOKUP
The older Excel lookup that searches the first column of a range; needs FALSE for an exact match.
Excel · List prices with VLOOKUP and IFERRORExcel · INDEX/MATCH, right to left Excel cheat sheet

W

Window frame
Which rows around the current one a window function uses, such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
SQL · 7-day moving average of orders SQL cheat sheet
Window function
A calculation across related rows that returns a value for every row (ranks, running totals, LAG) instead of collapsing them.
SQL · Top 3 products in every categorySQL · Year-to-date revenue SQL cheat sheet

X

XLOOKUP
Excel's modern lookup: finds a key in one range and returns the matching value from another, exact match by default.
Excel · Product names with XLOOKUPExcel · What a lookup returns for a missing key Excel cheat sheet

Y

YoY (year over year)
Change against the same period a year earlier, which removes most seasonality.
Casework · Holiday season review in SQL

Put the words to work

Interview prep

Interview questions

Questions from real analyst interviews, each with a model answer, the answer that loses points and a level to practise it.

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.