Analytics glossary
Try it · 30 seconds · from Atterna’s practice
An AI assistant
“A typical paid Meridian order is worth €61.”
Paid orders average €60.92; the median is €43.96. Is the AI right?
Preparing the practice…
Short definitions of the metrics and SQL terms analysts use every day — GMV, AOV, conversion, retention, join fan-out, sample ratio mismatch — each with the mistake that most often breaks it, the query on real practice data and what to ask when an AI gets it wrong.
Money and margin
What the platform sells, what it keeps and what each order costs.
- GMVThe total value of goods sold through a marketplace over a period, before discounts, refunds and fees. It measures the size of trade on the platform, not the platform’s income.Trap: Calling GMV “revenue”. The platform earns only its commission.
- Net revenueWhat customers actually paid after discounts, minus refunds. Which deductions are included differs between companies, so always state them.Trap: Forgetting pending refunds, or mixing gross and net in one comparison.
- Take rate · commission rateThe share of GMV a marketplace keeps as its own revenue: platform revenue divided by GMV.Trap: Averaging sellers’ commission rates instead of dividing total commission by total GMV.
- AOVRevenue divided by the number of orders in the same period and with the same filters.Trap: An average of money is pulled up by a few large orders. Report the median next to it.
- Refund rateRefunded orders (or refunded value) divided by paid orders (or paid value) in the same period.Trap: Reporting it too early: refunds arrive days or weeks after the order.
- ARPURevenue in a period divided by the number of users in the same period. Which users count (all, active, paying) must be stated.Trap: Dividing by paying users and calling it ARPU; that is ARPPU, and it is always higher.
- LTV · CLVThe revenue or margin a customer is expected to bring over the whole relationship. It is an estimate built from retention and spend, not an observed number.Trap: Using revenue instead of margin, or a lifetime longer than the data you have, which makes almost any acquisition look profitable.
- CACMarketing and sales spend in a period divided by the new customers acquired in the same period.Trap: Counting customers who would have come anyway, or leaving out part of the spend, so CAC looks lower than it is.
- Unit economicsRevenue and costs per unit — per order, per customer or per delivery — to see whether each unit makes or loses money before fixed costs.Trap: Leaving out costs that grow with volume, such as delivery, payment fees or discounts.
- Contribution marginRevenue minus the costs that vary with each sale (discounts, delivery, payment fees). It shows what a sale contributes to fixed costs and profit.Trap: Celebrating GMV growth from a promotion without checking contribution: sales can rise while the platform keeps less.
People and rates
Who is counted, out of whom, and who comes back.
- Conversion rateThe share of a starting group that completes an action: numerator over denominator, both defined for the same people and period.Trap: An unstated denominator. The same word can mean per visit, per visitor or per registered user.
- DenominatorThe “out of what” in any rate or share. Choosing it is part of the definition of the metric, not a technical detail.Trap: Comparing two rates with different denominators, or changing the denominator between periods.
- Active usersAccounts that performed a chosen action within a window: a day, a week or a month. The action and the window are choices, and both must be stated.Trap: Counting test accounts, staff or bots; mixing “logged in” and “bought”.
- Metric definitionA precise statement of what is counted: measure, population, condition, period and exclusions. Two people with the same definition and data get the same number.Trap: Starting with the query. Write the definition first: “[measure] of [population], [condition], [period], excluding [exclusions]”.
- Retention · repeat purchase rateThe share of a group that comes back or buys again after a first event, measured over a stated window.Trap: Measuring retention on a window that is still open, so recent customers look worse than they are.
- CohortA group of users who share a starting event in the same period, for example everyone whose first purchase was in September. Cohort analysis follows each group over time.Trap: Mixing cohorts of different ages in one average, which makes a stable product look like it is declining.
- ChurnThe share of customers who stop using or paying in a period. It is the complement of retention for the same group and window.Trap: Dividing by the customer count at the end of the period instead of the start.
- FunnelThe sequence of steps users take towards a goal — visit, product page, basket, checkout, payment — with the share that reaches each step.Trap: Mixing people and sessions between steps, or comparing steps measured over different periods.
SQL
What a row means and what joins, groups and filters do to it.
- GrainWhat one row of a table represents: one order, one order line, one customer per day. Knowing the grain tells you what a join and an aggregate will do.Trap: Summing a column after joining to a table with a finer grain.
- Join fan-out · row multiplicationWhen a join to a one-to-many table repeats each row once per match, so aggregates of the “one” side are counted several times.Trap: SUM after the join: no error, just a total that is too high.
- COUNT(DISTINCT …)Counts unique values instead of rows. Use it to count people or orders when a row can repeat.Trap: Using SUM(DISTINCT …) for money — two different orders can have the same amount.
- Primary keyA column (or set of columns) whose value identifies exactly one row of a table and never repeats, such as order_id in an orders table.Trap: Assuming a column is unique because it looks like an id. Check it: COUNT(*) against COUNT(DISTINCT id).
- Foreign keyA column that points to the primary key of another table, such as buyer_id in orders pointing to buyers. It is how tables connect in a join.Trap: Joining on a column that repeats on both sides, which multiplies rows.
- NULLThe value is unknown or missing. It is not zero and not an empty string, and it is not even equal to itself: NULL = NULL is not true.Trap: Filtering with = NULL (use IS NULL), or letting AVG silently skip NULLs so the average covers fewer rows than you think.
- GROUP BYCollapses rows that share the same values into one row per group, so aggregates such as COUNT, SUM or AVG are computed for each group.Trap: Grouping by a column with more detail than intended (a timestamp instead of a date), which returns one row per event instead of per day.
- WHERE vs HAVINGWHERE filters rows before they are grouped; HAVING filters groups after aggregation, so it can use COUNT, SUM and other aggregates.Trap: Putting a row condition in HAVING (an error for a column that isn’t grouped, wasted work for one that is), or an aggregate in WHERE (always an error).
- INNER JOINReturns only the rows that have a match in both tables. Rows without a partner disappear from the result.Trap: Losing rows silently: customers with no orders vanish, so a count of customers after the join is too low.
- LEFT JOINKeeps every row of the left table and adds matching columns from the right table, with NULL where there is no match.Trap: A WHERE condition such as o.status = 'paid' rejects unmatched NULL rows. Put the paid-status condition in ON when buyers without a paid order must remain; WHERE o.order_id IS NULL then selects those unmatched buyers.
- Window function · OVER (…)A calculation over a set of rows related to the current row — a running total, a rank, the previous value — that keeps every row instead of collapsing them like GROUP BY.Trap: Forgetting PARTITION BY, so the running total or rank covers the whole table instead of each customer or month.
- CTE · common table expressionA named subquery defined with WITH at the top of a query, so a long calculation can be written and checked in readable steps.Trap: Treating each step as correct because the final query runs. Check the row count after every step.
Experiments and statistics
Averages, splits, significance and what a comparison can prove.
- MedianThe middle value when all values are sorted: half are below, half above. For skewed data such as order values or session length it describes the “typical” case better than the average.Trap: Averaging medians of groups — the median of the whole is not the average of the parts.
- A/B testAn experiment that randomly splits users into groups that see different versions, then compares a pre-registered metric between them.Trap: Stopping as soon as the difference looks significant, or checking many metrics and reporting the one that moved.
- Sample ratio mismatchWhen observed group sizes are statistically unusual under an experiment’s planned split, commonly checked with chi-square against a pre-set alert threshold. It is a warning to investigate assignment and logging before interpreting the metric, not proof of a bug.Trap: Reading the result anyway.
- Simpson’s paradoxA trend that appears in every subgroup but reverses when the groups are combined, because the groups differ in size or mix.Trap: Comparing overall rates between segments with a different mix, such as new and returning customers.
- Statistical significance · p-valueA p-value is the probability of seeing a difference at least as large as the observed one if there were no real effect. “Significant” means it is below a threshold chosen before the test, often 0.05.Trap: Reading p = 0.03 as “97% sure the variant works”, treating significance as size or importance, or peeking daily and stopping on the first significant day.
- Confidence intervalA range of values for the true effect that the data are consistent with. At 95% confidence, the method that builds it captures the true value in 95 of 100 repeated experiments. A wide interval means the test cannot tell you much yet.Trap: Reporting only the point estimate (“+6%”) when the interval runs from −2% to +14%.
- Sample size · statistical powerHow many users an experiment needs to detect an effect of a given size with a given probability, called power (usually 80%). It is decided before the test, from the smallest effect worth detecting: the minimum detectable effect (MDE).Trap: Running until “it looks significant” instead of until the planned sample size is reached.
- Selection biasWhen the groups you compare differ before any treatment, because of how people got into them, so the difference in outcomes is not the effect you are measuring.Trap: Comparing people who chose something (used a code, opened an email) with people who did not.
- Correlation vs causationTwo numbers moving together (correlation) does not mean one causes the other. A third factor, the direction of cause or plain chance can explain it.Trap: Writing “driven by the promo” in a summary when the data only shows the two happened at the same time.
- OutlierA value far from the rest, such as one huge order. It may be real, a data error or a test account, and it can move an average a lot.Trap: Deleting outliers without a rule decided in advance, or keeping test accounts that inflate totals.
Time and trends
Periods that are not finished yet and patterns the calendar makes.
- Incomplete periodA day, week or month in a report for which not all data exists yet, usually the latest one.Trap: Reading it as a collapse.
- SeasonalityRegular patterns tied to the calendar — weekdays, paydays, holidays, sales seasons — that move metrics regardless of what the team did.Trap: Comparing a week with a holiday to one without, or this December with last month instead of last December.
Examples use Meridian, Atterna’s fictional practice marketplace.