# Making Data Sing: A Framework for Asking Better Questions of Data ## Why this exists Handed a new dataset, most students do the same thing: group by a column, sum a number, sort, done. "The most common name is James." True, but nobody leans forward. Interesting analysis isn't about knowing more functions. It's about knowing **what kinds of questions a dataset can answer**. And that is largely determined by **what types of columns it has, and which columns occur together.** The framework has four steps: 1. **Type the columns.** What kind of thing is each column? 2. **Look up question families.** Each type, and each pair of types, invites certain questions. 3. **Apply moves.** Small transformations that turn a flat answer into an interesting one. 4. **Look for compound shapes (advanced).** When certain columns occur together, whole new kinds of analysis open up, such as process mining, cohorts, networks and more. A final section, **From seed phrase to full question**, shows how to turn a rough idea ("names over time") into a question you can actually answer. --- ## Step 1: Type the columns | Type | Examples | What it invites | |---|---|---| | **Entity / identifier** | name, product, team, student | Top-N, long tail, and the text of the identifier itself as data | | **Category** | sex, region, department | Comparison, share, overlap and crossover | | **Time** | year, date, term | Trend, peak, first/last appearance, change | | **Place** | state, country, zip code | Regional difference, spread, distinctiveness | | **Measure** | count, price, duration | Totals, distribution, concentration | Some columns can play more than one role. A year is time, but it's also an ordering. A state is a place, but it's also a category, and it nests inside a region. Noticing the double roles is where Step 4 begins. --- ## Step 2: Question families by type ### One type on its own - **Entity:** Which are biggest? How long is the tail? How many distinct entities are there? - **Measure:** Is the distribution skewed? What's typical versus extreme? - **Category:** How is the total split across categories? ### Pairs of types | Pair | Question families | |---|---| | **Entity × Time** | When did it peak? When did it first appear or disappear? Who rose or fell fastest? What came back? Fad or classic? | | **Entity × Category** | Which entities appear in more than one category? Which switched from one to another? | | **Entity × Place** | Where is it unusually popular compared with everywhere else? Where did it start? | | **Category × Time** | Did the gap between groups grow or shrink? | | **Place × Time** | Are places becoming more alike or more different? Did something spread geographically? | | **Measure × anything** | How concentrated is the total? Is concentration rising or falling? | --- ## Step 3: Moves that make data sing Moves are small transformations. Almost every interesting question uses at least one. | # | Move | What it does | Why it matters | |---|---|---|---| | 1 | **Count → share** | Divide by the total for that group or period | Totals change over time; shares are comparable | | 2 | **Count → rank** | Replace a value with its position | Ranks compare across eras and scales | | 3 | **Value → change** | Subtract one period from another | Finds the biggest movers, not just the biggest values | | 4 | **Global → over-index** | Compare a group's share with the overall share | Finds what's *distinctive*, not just what's common | | 5 | **Top-N within each group** | Nest the question inside a category, place or time | "Top name in each state" beats "top name overall" | | 6 | **Peak / first / last** | Find *when* the extreme happened | Turns a number into a story with a timeline | | 7 | **Concentration** | What fraction of the whole do the top few account for? | Reveals diversity, inequality, long tails | | 8 | **The identifier is data** | Derive features from the text: length, first letter, last letter | Patterns hide inside the names themselves | | 9 | **Look for reversals and outliers** | Find where the pattern breaks | The exception is usually the story | **The difference in practice:** - Group-by answer: *"Michael is the most common boys' name."* - With moves: *"Michael held the #1 spot for decades, yet today's #1 boys' name covers a much smaller share of all babies. Parents are spreading out."* (rank, share, concentration) --- ## Step 4 (Advanced): Compound shapes, or when columns occur together This is where data analysis gets exciting. Everything above treats rows as **independent**. You filter, group and aggregate. But when certain columns appear **together**, rows start to relate to one another. A set of rows becomes a *journey*, a *basket*, a *network* or a *hierarchy*. Each of these shapes unlocks a whole toolkit that a plain group-by can't reach. There are three ways columns combine: 1. **Order within an entity.** Add time or sequence, and each entity becomes a story: process mining, cohorts, survival, intervals, rank trajectories, before/after. 2. **Pair rows that share a key.** Two entities connected through a common group give co-occurrence, networks, similarity and flows. 3. **Nest one category inside another.** This gives hierarchies, drill-down, decomposition and Simpson's paradox. ### The three questions to ask of every dataset > **1. Can I order rows within an entity?** → sequences, cohorts, survival, intervals > **2. Can I pair rows that share a key?** → co-occurrence, networks, similarity > **3. Does one category nest inside another?** → hierarchy and decomposition Each "yes" opens a new toolkit. The technical step is usually small: a **window function**, a **self-join**, or a **join to a parent table**. These aren't exotic topics. They're the natural next questions once you see the shape. --- ### Shape A: Event sequences and process mining ⭐ **Signature columns:** case ID (entity) + activity (category) + timestamp (time). Optional: resource (who did it), cost, outcome. **Why it's exciting:** each case becomes a *path*. You stop asking "how many approvals?" and start asking "what route do cases actually take, and where do they get stuck?" Many datasets are secretly event logs that nobody treats that way: clickstreams, support tickets, course enrollments, medical visits, git commits, order fulfillment. **Question families** (following van der Aalst's process mining tasks): - **Discovery:** What paths do cases actually take? What are the most common variants? - **Conformance:** Do cases follow the intended process? Where do they skip steps, repeat them, or go out of order? - **Performance:** Where does time go? Which transitions are bottlenecks? - **Rework and loops:** What gets repeated, and does repetition predict a bad outcome? - **Handoffs:** Who passes work to whom? The resource column becomes a social network. **New moves:** - **Order within case:** number the events (first step, last step, step N). - **Pairs → transitions:** count "A directly followed by B." This directly-follows graph is the heart of process discovery. - **Gaps between events:** time between consecutive steps, not just total duration. - **Sequence → variant:** concatenate each case's activities into one string, then group by it. Suddenly an ordinary group-by answers "what are the top 5 paths?" - **Outcome by path:** which paths end in success, which in failure? **Technical move:** window functions (`ROW_NUMBER`, `LAG`/`LEAD`) partitioned by case, ordered by time. **Example:** student course-taking (student = case, course = activity, term = time). What paths do students actually take through the major, compared with the catalog's recommended sequence? Which course is the bottleneck that delays graduation? --- ### Shape B: Cohorts **Signature columns:** entity + time of first appearance + later activity over time. **Why it's exciting:** instead of mixing everyone together, you group entities by *when they started* and watch each group age. It's the backbone of retention and churn analysis, and it reveals generational differences that totals hide. **Questions:** Do people who joined in 2020 behave differently from people who joined in 2023? Does each new cohort retain better or worse? **Technical move:** compute each entity's first-appearance period (a `MIN` per entity), then group by cohort × periods-since-start. --- ### Shape C: Survival / time-to-event **Signature columns:** entity + start time + end time (or event), possibly still ongoing. **Why it's exciting:** it answers "how long until…?" and "what makes things last?" The key subtlety, and a great teaching moment, is **censoring**: some cases haven't ended yet, so a naive average duration is wrong. **Questions:** How long do customers stay? What fraction survive past year 2? Are things lasting longer or shorter than they used to? **Technical move:** compute duration per entity, flag censored cases, then plot the share still "alive" at each duration. --- ### Shape D: Intervals and concurrency **Signature columns:** entity + start time + end time. **Why it's exciting:** rows become spans, so you can ask about **overlap**: how many are active at once, what's the peak load, where are the gaps? It's the basis of utilization, capacity and session analysis. **Questions:** What's the maximum number of hospital beds occupied at once? When are rooms idle? How long are typical sessions? **Technical move:** explode each interval into start (+1) and end (−1) events, then take a running sum. --- ### Shape E: Rank trajectories **Signature columns:** entity + rank (or a measure you can rank) + time. **Why it's exciting:** ranks turn a table into a *leaderboard over time*. You can measure churn, stability, climbs and falls, and the result is comparable across eras with different totals. **Questions:** How much does the top 10 turn over each year? Who climbed the most places in a decade? Is the leaderboard more volatile now than before? **Technical move:** `RANK()` within each period, then `LAG` across periods per entity. --- ### Shape F: Before/after an event **Signature columns:** entity + time + a dated external event (often joined in from outside). **Why it's exciting:** this is the doorway from description to **causal thinking**. Did something change *because* of the event? Compare against entities that didn't experience it (difference-in-differences), or against the pre-event trend (interrupted time series). **Questions:** What happened to X after the policy, the product launch, the news story? Did the effect last or fade? **Technical move:** join a table of events with dates; compare windows before and after; compare with a control group. --- ### Shape G: Co-occurrence / market baskets **Signature columns:** group ID + item (many items per group). **Why it's exciting:** you learn what *goes together*: products in an order, songs on a playlist, courses in a student's schedule. It leads to association rules ("people who take X also take Y") and recommendation. **Questions:** Which pairs appear together far more than chance would predict? (Use *lift*, not raw counts.) **Technical move:** self-join the table on the group ID to create item pairs, then count. --- ### Shape H: Networks **Signature columns:** entity + entity (+ weight). Typical columns are from/to, sender/recipient, citer/cited, or a network *derived* from co-occurrence or similarity. **Why it's exciting:** the data becomes a graph, so you can ask about **structure**: who's central, what communities form, who bridges groups, how far apart things are. **Questions:** Who is the hub? Which clusters exist? Who connects otherwise separate groups? **Technical move:** build an edge list (often via self-join), then use a graph tool for centrality and communities. --- ### Shape I: Similarity / bipartite profiles **Signature columns:** entity A + entity B + value (user + item + rating; state + name + count). **Why it's exciting:** each entity becomes a **vector**, a fingerprint of its tastes or behavior. Now you can ask "what is this most like?", find lookalikes, cluster entities and recommend. **Questions:** Which entity is most similar to X? What natural clusters exist? **Technical move:** pivot to a matrix (entity × item), normalize to shares, compute a similarity measure (cosine or correlation). --- ### Shape J: Origin–destination flows **Signature columns:** place + place + count (migration, trade, commuting). **Why it's exciting:** flows have *direction*, so you can find imbalances. Who sends more than they receive? What are the hubs and corridors? **Questions:** What's each place's net flow? Which corridors are lopsided? **Technical move:** join the table to itself with origin and destination swapped, then subtract. --- ### Shape K: Hierarchies **Signature columns:** a category that nests inside another (state → region, course → department, product → category). **Why it's exciting:** you can **drill down** (the total moved, but which parts drove it?) and **roll up**. It's also where students meet **Simpson's paradox**: a trend at the top level can reverse inside every subgroup. **Questions:** Is this change everywhere, or driven by one subgroup? Which part contributed most to the total change? **Technical move:** join to a parent lookup table; compute each child's contribution to the parent's change. --- ### Summary table of compound shapes | Shape | Signature columns | Unlocks | Core technical step | |---|---|---|---| | A. Event sequences ⭐ | case + activity + time | Process discovery, conformance, bottlenecks | Window functions by case | | B. Cohorts | entity + start time + activity | Retention, generational comparison | First-appearance per entity | | C. Survival | entity + start + end | Time-to-event, durability | Duration + censoring | | D. Intervals | entity + start + end | Concurrency, utilization, gaps | Running sum of +1/−1 events | | E. Rank trajectories | entity + rank + time | Churn, climbers, volatility | Rank per period + lag | | F. Before/after | entity + time + dated event | Impact, causal reasoning | Join events, compare windows | | G. Co-occurrence | group + item | What goes together, lift | Self-join on group | | H. Networks | entity + entity | Centrality, communities, bridges | Edge list | | I. Similarity | entity + entity + value | Lookalikes, clusters, recommendations | Pivot + similarity measure | | J. Flows | place + place + count | Net flows, hubs, imbalances | Swap-and-subtract self-join | | K. Hierarchy | nested categories | Drill-down, decomposition, Simpson's paradox | Join to parent | --- ## From seed phrase to full question Good questions usually start as short, vague **seed phrases**: - "names over time" - "1950s cohort vs 1980s cohort" - "boy names that became girl names" A seed phrase is a great start, but you can't compute it yet. A **full question** spells out five things: | Part | Ask yourself | Example | |---|---|---| | **Subject** | What entity or group? | Girls' names that entered the top 100 | | **Measure** | What number, and as count, share or rank? | Years spent in the top 100 | | **Move or shape** | Which move (Step 3) or compound shape (Step 4)? | Cohort + survival | | **Comparison** | Compared to what? | 1950s cohort vs 1980s cohort | | **What counts as an answer** | What result would surprise you? | If the 1980s names faded twice as fast | **Seed:** "1950s cohort vs 1980s cohort" **Full question:** *"For girls' names that first entered the top 100 in the 1950s versus the 1980s, how many years did each name stay in the top 100, and do names from the later cohort fade faster?"* A useful test: **could two different students compute the same answer from your full question?** If not, it's still a seed. --- ## Quick checklist for any new dataset 1. ☐ Type every column (entity, category, time, place, measure). 2. ☐ List the pairs of types and pick two question families from Step 2. 3. ☐ Apply at least two moves from Step 3. 4. ☐ Ask the three compound-shape questions: order? pair? nest? 5. ☐ Write three seed phrases and turn each into a full question. 6. ☐ Before computing, write down what you *expect*. The gap between expectation and result is where insight lives. --- ## Lineage and further reading - Ben Shneiderman (1996), *The Eyes Have It: A Task by Data Type Taxonomy for Information Visualizations* - Robert Amar, James Eagan & John Stasko (2005), *Low-Level Components of Analytic Activity in Information Visualization* - Tamara Munzner, *Visualization Analysis and Design* (the What–Why–How framework) - Jeff Leek & Roger Peng (2015), *What is the question?*, Science - Hadley Wickham et al., *R for Data Science*, chapter on exploratory data analysis - Wil van der Aalst, *Process Mining: Data Science in Action* - Yan Holtz, *From Data to Viz* (data types to chart types)