Entity Aggregates from Events

## The shape problem Classical models expect a rectangular table: one row per thing you score, one column per feature, one label per row. Event data does not arrive that way. A clickstream, an order table, an authorisation log or a SaaS audit trail is long and thin — each row is one thing that happened at one instant, roughly `(entity_id, timestamp, event_type, value)`. A busy account contributes four thousand rows; a quiet one contributes three. You cannot train "will this account churn next month?" on that table, because the unit of the question (the account) is not the unit of the row (the event). **Entity aggregation** is the reshape that fixes it: every event belonging to one entity collapses into a single row of summary numbers, and that row gets one label. ## Three decisions before any arithmetic **1. The entity grain.** What is one row? An account, a user inside an account, a payment card, a store-item pair. Pick the grain the prediction is made at and the grain the label is defined at — they must match. If the business acts on accounts but events carry user ids, you aggregate users up to the account. **2. The reference date.** Every aggregate is computed *as of* a moment. For training rows this is the snapshot date the label is measured after; at scoring time it is the moment you score. Naming it explicitly is what makes the feature reproducible: `days since last login` is meaningless until you say "since when". **3. The window.** How far back the aggregate reaches: last 7, 30 or 90 days, or all history. Use a half-open interval — include events with `window_start <= t < reference_date` — so the row is built only from what was knowable at that instant, and so an event exactly on the boundary is counted once rather than twice across adjacent windows. ## The aggregate families Once the grain, reference date and window are fixed, the aggregates themselves are a small vocabulary applied to every numeric field and every event type: - **Volume** — `count` of events, `sum` of amounts. "How much did this entity do?" - **Typical value** — `mean` or `median` amount per event. "How big is a typical action?" Note this is volume divided by count, so it carries different information than either. - **Extremes** — `max` (and sometimes `min`). A single unusually large transaction is often the signal, and a mean hides it. - **Breadth** — `distinct count`: how many merchant categories a cardholder touched in the last 90 days, how many features a SaaS account used. Breadth and volume are genuinely different: one account may fire a thousand events against a single feature, another two hundred across twelve. - **Timing** — `recency = reference_date - last_event_date` and `tenure = reference_date - first_event_date`. Recency is usually the strongest single column in a churn or reactivation model, because disengagement shows up as silence long before it shows up as a change in volume. Tenure separates "quiet because new" from "quiet because leaving". - **Composition** — share-of-total: this title's spend divided by the entity's total spend that month. **RFM** is simply three of these chosen together for a shopper: Recency (days since last order), Frequency (order count in the window) and Monetary (total spend in the window). It is a good default starting set precisely because it covers timing, volume and value with three columns. The vocabulary multiplies out: aggregate x field x event type x window. A 30-day window over a SaaS event log easily yields session count, distinct features touched, sum of seconds in app, max session length and days since last login — five columns from one raw log. ## Two construction traps **Losing the silent entities.** If you build the feature table by grouping the event log, any entity with no events in the window is not in the result at all. Those are frequently the most interesting rows. Start from the entity roster and left-join the aggregates, so a silent account appears with a count of zero. **Mixing grains.** A common bug is labelling each *event* with the entity's outcome and training on that — a heavy user then contributes thousands of correlated rows and dominates the loss, while a quiet user contributes almost nothing. One entity, one row, one label. ## What good looks like A clean aggregate table is documented by its three parameters — grain, reference date, window — and every column name states which aggregate over which window it is, for example `orders_count_30d` or `days_since_last_order`. Anyone reading a row can then say exactly what was known, about whom, and when.