learn-people-analytics-with-phoebe / Deep-dive session 1 of 10
Learn People Analytics with Phoebe · Deep-dive session 1 of 10

The people data model

Every people question is a question about one of three tables: who the people are, what positions exist, and what happened when. Most HR systems can produce the first as an export, the second as an org chart, and the third not at all, which is why the export you are handed is a snapshot with a handful of events stapled to it. Tonight builds the vocabulary and the first numbers: a constructed 1,071-row table that looks like a real export, the headcount on a date, the joiners and leavers between two dates, the identity that ties them together, and the three bases that turn 210 leavers into 20.5, 22.3 or 24.4 percent. By the end you will know from the shape of a table which questions it can answer, and you will have written the six decisions that make two analysts' numbers agree.

🟡 Core HR analysts, people data teams, anyone who owns the export Python, pandas 45 min
0-3 · Welcome 3-18 · Three tables, one snapshot 18-40 · Build-along: the bridge in pandas 40-45 · Q&A
Part 0

Where we start

The running data is Apex, a constructed group of four entities whose staff table was generated with a seeded script to look like what an HR system exports: one row per person, a join date, a last day if they left, a department, a free-text reason. Nobody in it is real and every page says so. What is real is the shape, because it is the shape of nearly every people dataset an analyst is handed, and the shape decides more than the numbers do.

🎫 Clear all three quiz questions to stamp your passport Live = presented in the 45 minutes Self-study = the full depth, after class ★ Build-along = run the code yourself

★ What you walk out with tonight. The three-table model and the test for which one you have been given, a headcount function that takes a date, the joiners and leavers between two dates with the identity asserted in code, and the three bases printed side by side so that the choice is visible.

Part 1 · covers the three tables

Employee, position, event

5 min live

Three tables answer every people question between them. The employee table has one row per person and says who they are. The position table has one row per seat and says what the organisation has decided it needs. The event table has one row per thing that happened to a person on a date: joined, moved, promoted, went on leave, left. Attrition, tenure, time-in-grade and every flow live in the third table, and the third table is the one you almost never get.

THREE TABLES ANSWER EVERYTHING BETWEEN THEM EMPLOYEE · one row per person id · name · department · grade join date · last day (if any) the export you are usually given last day = one event stapled on POSITION · one row per seat position id · grade · department filled by · open since the org chart, as a table vacancies and spans live here EVENT · one row per happening person id · date · type joined · moved · promoted · left the table you almost never get every flow is a count of these rows headcount on a date joiners, leavers, moves A snapshot of the first table with "last day" filled in can rebuild SOME events: the last one per person. It cannot rebuild the ones before.
🔍 Click to zoom - the staple on the employee table is the last day column: one event per person, and only the final one
LiveWhich table have you been given, and the test that tells you3 min▶

The test is one question: can a person appear more than once? If every person is exactly one row, you have an employee snapshot, and every flow will have to be reconstructed from date columns. If a person can appear many times with a date and a type on each row, you have an event log, and flows are counts. Apex is the first kind: 1,071 rows, 1,071 people, a join date on every row and a last day on 210 of them.

A snapshot with a last day column can rebuild one event per person: the join, and the exit if there was one. It cannot rebuild a transfer three years ago, a promotion, a period of leave, or the exit of anybody who left before the snapshot was taken and was therefore never in it. Apex marks transfers with a flag and a "pre-transfer years" column, which is the snapshot's way of stapling a second event on, and it is a sign the table was built by somebody who knew what was missing.

The consequence for every later session: attrition, tenure and survival are event questions asked of a snapshot, and the honest analysis says which events the snapshot can see and which it cannot.

Self-studyThe id that is the person, and the one that is the seat2 min▶

Apex's Staff ID is one per person. Some systems issue a new id on every rehire or every transfer between legal entities, and then a person is two rows, a transfer looks like an exit and a hire, and the identity breaks by exactly the number of transfers. Check the cardinality before anything else: the number of distinct ids against the number of rows, and both against what the payroll says. The retention course found the same trap in a customer table, where an order-side key made every customer a one-time buyer; the people version makes every transferee a leaver.

Real world

The event log that existed and was never exported. Most HR systems record every change to a person's record with a timestamp, in an audit table nobody has been asked for. An analyst who asks for "the employee export" gets the snapshot. An analyst who asks for "the change history for the join date, department, grade and status fields" gets an event log, from the same system, for the same people. The second request is the one that makes deep-dive sessions 2 and 8 possible. Constructed setting; the mechanism is common.

Part 2 · covers snapshot vs event log

A snapshot is a photograph, an event log is a film

5 min live

Apex's table holds everyone employed on 1 January 2026 and the 210 people who left between then and 17 June. Nobody who left in 2025 is in it. Ask it "how many people left in 2024" and it will answer zero, confidently, because the people who did are not there to be counted. That is not a bug in the table; it is what a snapshot is.

THE SNAPSHOT what Apex's export is THE EVENT LOG what would answer every flow question 1 JAN 2026 faint: left before the photograph, absent dark: 1,071 rows in the table dots: the 210 leavers, all Jan to Jun 2026 leavers in 2024 or 2025: 0, by construction 2012-02-16 · S10001 · joined · Engineering 2015-06-01 · S10001 · moved · Apex Logistics 2018-03-12 · S10233 · left · resigned 2019-05-15 · S10001 · promoted · grade 4 2023-11-15 · S10003 · joined · R&D 2026-02-09 · S10420 · left · compensation ... one row per happening, every year leavers in 2024: a count, not a guess Same people, two tables. The snapshot answers "who is here now"; the film answers "what happened". Constructed rows for the illustration.
🔍 Click to zoom - the snapshot's zero leavers in 2024 is not a finding about 2024; it is a property of the photograph
LiveWhat a snapshot can and cannot answer, in one table3 min▶

Can answer: headcount on the snapshot date; headcount on any earlier date for people still present at the snapshot (a biased count, because the earlier leavers are absent); joiners in any period, if the join date is kept for everyone; leavers in the window after the snapshot, if the export includes them, which Apex's does for six months.

Cannot answer: leavers before the snapshot; the true tenure distribution of everyone who ever joined; how many people left in their first year, historically; any flow before the photograph. Ask any of these of a snapshot and it will return a number, and the number will describe the survivors.

The rule for the rest of the track: every flow computed from Apex is a flow inside the window January to June 2026, the only window the table can see. Foundations session 1 taught the same lesson with a camera drawing; here it becomes a filter in the code, and the filter is written at the top.

Self-studyBuilding an event log from snapshots you already have2 min▶

If the system will not give you the change history, monthly snapshots will. Keep every month's export with the date in the filename, and the difference between two consecutive snapshots is the event log for that month: ids present in the second and not the first joined, ids present in the first and not the second left, ids whose department changed moved. It is coarse, one event per person per month at most, and it is the single most valuable habit a people data team can adopt, because it turns the photograph problem into a film from the day you start.

Part 3 · covers the identity and the three bases

The identity, and the base under the rate

5 min live

With a snapshot that includes six months of leavers, the bridge for that window can be computed exactly: 1,022 people on 1 January, 49 joined, 210 left, 861 on 1 July. The identity holds by construction, and asserting it in code is the cheapest test a people pipeline can have. Then the same 210 over three bases: 20.5, 22.3 and 24.4 percent, printed side by side so the choice is a choice.

TWO DATE COLUMNS, FOUR NUMBERS, ONE IDENTITY, THREE RATES Date Join Last Day NaT for 861 rows start, 1 Jan 1,022 joiners + 49 leavers - 210 end, 1 Jul 861 assert start + in - out == end 210 / 1,022 start 20.5% 210 / 941.5 average 22.3% 210 / 861 end 24.4% Two columns make all four numbers, and the four numbers make three rates. Print all three; choose one; say which. Constructed Apex data.
🔍 Click to zoom - the tick is an assert statement, and it is the one line of this pipeline most worth keeping
LiveHeadcount as a function of a date, and why that shape matters3 min▶

Headcount is not a column; it is a function: the number of rows whose join date is on or before the date and whose last day is either missing or after it. Written that way once, it gives a consistent stock for any date, and the bridge becomes three calls: headcount at the start, headcount at the end, and the two flows as date-range counts. Written any other way, as a column somebody filled in, a total on a slide, or a figure from a different system, it drifts from the flows and the identity breaks.

Two details decide whether the identity holds. The boundary: a person whose last day is exactly the end date is in the end stock or not, and a joiner on the start date is in the start stock or not; pick one convention and use it in both the stock and the flow. The window: only events inside the window count, so a person who left after the end date is still in the end headcount. Get both right and the assert passes; get either wrong and it fails by exactly the number of boundary cases, which is a useful number to see.

Self-studySix decisions, written at the top of the script2 min▶

Which id is the person. Which rows are in the stock (payroll employees; contractors; people on leave). Which date is the join (offer, start, first payroll). Which date is the exit (notice, last day, last payroll). How transfers are treated on this bridge. Which base the rate uses. Six lines of comment above the code, and when a colleague's bridge differs from yours the diff between the two comment blocks is the answer. The same discipline the retention and RFM courses use for a customer table, applied to people, where the disagreements are more emotional and the fix is the same.

Build-along

The bridge in pandas, on 1,071 constructed rows

★ 22 min · everyone builds

Seven steps on materials/apex-staff.csv, the constructed Apex table that ships with these pages. Every printed number is what the code produces on that table; on your own export the numbers will differ and the decisions will not.

SEVEN STATIONS 1 · load 1,071 rows 1,071 ids 2 · decisions six comments at the top 3 · headcount(d) 1,022 on 1 Jan 861 on 1 Jul 4 · the flows 49 joined 210 left 5 · assert 1,022 + 49 - 210 == 861 6 · three bases 20.5 · 22.3 24.4 percent 7 · split 4 entities 8 departments Station 3 is the whole trick: headcount is a function of a date, not a column. Station 5 is the test. Station 6 is where the choice becomes visible. Save the bridge with both dates in the filename. Session 3 compares it with a benchmark and session 8 plans from it.
🔍 Click to zoom - the moss station is a single assert, and it catches more people-data errors than any dashboard review

Load, and check that the id is the person. Rows and distinct ids should agree; if they do not, somebody is two rows.

★ Step 1 · loadimport pandas as pd d = pd.read_csv("materials/apex-staff.csv", parse_dates=["Date Join", "Last Day"]) print(len(d), d["Staff ID"].nunique()) # 1071 1071 print(d["Company"].value_counts().to_dict()) # {'Apex Manufacturing': 460, 'Apex Retail': 250, 'Apex Logistics': 231, 'Apex Digital': 130} print(d["Last Day"].notna().sum()) # 210 rows have a last day

The six decisions, as comments. Written before any number, so that a colleague's different number is a diff and not an argument.

★ Step 2 · decisions# person id: Staff ID, one per person (checked: 1071 == 1071) # in the stock: every row (the table is payroll employees; no contractor flag exists) # join date: Date Join # exit date: Last Day; NaT means still employed at the export # transfers: is_transfer marks moves between Apex entities; NOT exits on the group bridge # base: average of start and end headcount, stated on every rate # window: the export is a snapshot on 2026-01-01 plus leavers to 2026-06-17; # NO flow before 2026-01-01 can be computed from it

Headcount as a function of a date. On the payroll on that date: joined on or before it, and not yet left (no last day, or a last day after it).

★ Step 3 · headcount(date)def headcount(df, date): date = pd.Timestamp(date) joined = df["Date Join"] <= date still = df["Last Day"].isna() | (df["Last Day"] >= date) return int((joined & still).sum()) START, END = "2026-01-01", "2026-07-01" print(headcount(d, START), headcount(d, END)) # 1022 861

The two flows, inside the window. Joiners with a join date on or after the start; leavers with a last day inside the window. Nothing before the start can be seen, and the code says so.

★ Step 4 · flowsjoiners = int((d["Date Join"] >= START).sum()) leavers = int(((d["Last Day"] >= START) & (d["Last Day"] < END)).sum()) print(joiners, leavers) # 49 210 print(d["Last Day"].min().date(), d["Last Day"].max().date()) # 2026-01-01 2026-06-17 -> every exit the table can see is inside the window

Assert the identity. One line. If it fails, the difference is the number of boundary cases or double counts, and that is the next thing to look at.

★ Step 5 · the identityassert headcount(d, START) + joiners - leavers == headcount(d, END), "bridge does not reconcile" print("bridge reconciles:", headcount(d, START), "+", joiners, "-", leavers, "=", headcount(d, END)) # bridge reconciles: 1022 + 49 - 210 = 861

Three bases, printed together. The choice is the average base, and the other two are printed beside it so the choice is visible. Annualised by doubling, with the assumption in the label.

★ Step 6 · the rate with its basestart, end = headcount(d, START), headcount(d, END) bases = {"start": start, "average": (start + end) / 2, "end": end} for name, base in bases.items(): print(f"{name:8s} base {base:7.1f} H1 attrition {leavers / base * 100:5.1f}%") # start base 1022.0 H1 attrition 20.5% # average base 941.5 H1 attrition 22.3% # end base 861.0 H1 attrition 24.4% print(f"annualised (x2, assumes H2 repeats H1): {leavers / bases['average'] * 200:.1f}%") # 44.6%

Split by entity and department, start base. The base is printed under every rate, because the next session is about what a base of 75 does to a rate.

★ Step 7 · the splitdef bridge_rows(df, by): out = [] for key, g in df.groupby(by): s = headcount(g, START); l = int(((g["Last Day"] >= START) & (g["Last Day"] < END)).sum()) out.append((key, s, l, round(l / s * 100, 1))) return pd.DataFrame(out, columns=[by, "start", "leavers", "rate_on_start_base"]) print(bridge_rows(d, "Company").to_string(index=False)) # Company start leavers rate_on_start_base # Apex Digital 124 31 25.0 # Apex Logistics 221 52 23.5 # Apex Manufacturing 440 85 19.3 # Apex Retail 237 42 17.7 print(bridge_rows(d, "Department").sort_values("rate_on_start_base").to_string(index=False)) # R&D 94 / 7 / 7.4 ... Sales 108 / 54 / 50.0 bridge_rows(d, "Department").to_csv("bridge_2026-01-01_to_2026-07-01.csv", index=False)

Keep the assert. A people pipeline with the identity asserted in it fails loudly the day a system starts issuing new ids on transfer, a contractor feed is added to the export, or a last day is entered in the wrong year. Without it, the rate quietly moves and somebody presents the movement as a trend.

Homework

Before deep-dive session 2

30 min
  1. Run the seven steps on Apex, then on your own export. Write the six decisions as comments before any code below them.
  2. Move the window to 1 February to 1 June and re-run. Write which of the four bridge numbers changed and why the identity still holds.
  3. Answer "which table is this" for your export: can a person appear twice? What events can it rebuild, and what can it not?
  4. Bring the table. Session 2 fits a survival curve to it and shows how far the photograph problem bends the curve.
Check yourself

Three questions

Clear all three and deep-dive session 1 gets stamped in your passport.

1 · You are handed a table with one row per person, a join date, and a last day that is empty for most rows. Which table is it, and what follows?

The test is whether a person can appear more than once. One row per person is a photograph; the last day column is the final event only. Apex can see exits from 1 January to 17 June 2026 and nothing before.

2 · Why is headcount written as a function of a date rather than read from a column or a slide?

The boundary convention and the window convention live inside the function once, and both stock and flow use them. That is what lets one assert line catch double counts and boundary errors.

3 · The script prints 20.5, 22.3 and 24.4 percent for the same 210 leavers and then chooses one. Why print all three?

None of the three is wrong; an undeclared one is. The same habit applies to the annualised figure, which carries the words "assumes H2 repeats H1" in its label.

Sources covered

What this session teaches, and where it came from

✓The employee, position and event tables; snapshot vs event logStandard practice, no single source
✓The Apex bridge: 1,071 rows and ids; 1,022, 49, 210, 861; 20.5 / 22.3 / 24.4; annualised 44.6; entity rates 25.0 / 23.5 / 19.3 / 17.7; Sales 50.0 and R&D 7.4Computed from the constructed Apex table that ships with these pages; Apex and everyone in it are invented; annualising is labelled as an assumption
✓The identity as an assert; headcount as a function of a date; the six decisionsStandard practice; the same discipline the retention and RFM courses apply to a customer table
◐Rebuilding an event log from monthly snapshotsNamed in self-study; not built here
◐Survival and the photograph biasNamed; measured in session 2

Every fact on this page, and its verification tier, is recorded in the course's source map.

Deep-dive session 1 cheat sheet · pin this

Three tablesEmployee (who), position (seats), event (what happened when). Flows live in the third.
Which one is this?Can a person appear twice? No: a snapshot. Yes: an event log.
headcount(date)Joined on or before, not yet left. A function, never a column.
assert start + in - out == endApex: 1,022 + 49 - 210 = 861. Keep the line.
Three bases, printed20.5 start, 22.3 average, 24.4 end. Choose, say which.
The window is the snapshot'sApex sees exits from 1 Jan to 17 Jun 2026 and nothing earlier.