Chapter 9 · 8 levels
Analytics patterns
The queries behind every business review: running totals that reset each year, month-over-month and year-over-year growth, top N per group with ties, funnels, activation and retention grids.
StartMost analyst requests are variations on a handful of patterns: “how are we tracking this year?”, “are we growing?”, “what sells best in each category?”, “where do visitors drop off?”, “do new customers come back?”. Each pattern has a standard query shape and, more importantly, a standard way to get the definition wrong.
This chapter builds on window functions and CTEs. You will write running totals that reset each year, month-over-month and year-over-year growth side by side, top N per group with deliberate tie handling, a conversion funnel, an activation rate by signup quarter and a cohort retention grid. The boss is a monthly business review that combines them.
Levels
- Year-to-date that resets every January
from Laura Bennett, Head of Finance
running total, partition by, sum over, year to date, cte
- Month-over-month vs year-over-year
from Robert Kim, Chief Operating Officer
yoy growth, mom growth, lag, seasonality, cte
- Top 3 per category, ties included
from Jessica Turner, Merchandising Manager
top n per group, rank, ties, qualify, cte
- A conversion funnel by device
from Daniel Foster, Mobile Product Manager
funnel, conversion rate, conditional aggregation, filter clause
- Activation rate by signup quarter
from Jordan Reyes, Head of Marketing
activation, cohort, left join, date difference, censoring
- A cohort retention grid
from Priya Shah, Head of Data
cohort retention, datediff, month offset, cte, window functions
- The partial-month trap
from Megan Collins, Head of Sales
period over period, month to date, seasonality, yoy growth
- Boss: the annual business review
from Catherine Hale, Chief Executive Officer
yoy growth, share of total, top n per group, row number, repeat revenue, cte