Choosing a time grain is choosing how much of the underlying signal a model or report is allowed to see, and that choice should be made deliberately, once, up front — not discovered after the fact because a platform's export happened to round every timestamp to the day. This page lays out the grain ladder this KB works with and the considerations that should drive a choice at any given rung.
The grain ladder
- instant
- A single epoch millisecond value. Exact, but not directly comparable across weeks or aggregable without a coarser bucket.
- hour_of_week_slot
- 0-167, slot 0 = Mon 00:00 UTC. The canonical unit — see The 168 axis. Repeats weekly; pair with an ISO week for uniqueness.
- broadcast_day
- A declared local cutover hour (commonly 06:00) defines when one day ends and the next begins; can be 23, 24, or 25 UTC hours across a DST transition.
- daypart
- A named local band (morning, primetime) mapped to a UTC slot-set per zone and week; the reporting grain of media planning, not a modeling primitive.
- iso_week (cell x week)
- The coarsest grain in this ladder: one row per (cell, week). The standard MMM grain — enough weeks of history for a stable regression, coarse enough to avoid daily noise.
Coarser is not automatically safer, and finer is not automatically better: each rung trades away a specific kind of resolution to gain a specific kind of stability, and the right choice depends on what the downstream consumer is actually going to do with the number.
Choosing a grain
Statistical power. A marketing-mix model regressing weekly spend against weekly outcome needs enough independent weekly observations to fit a stable coefficient — commonly 104+ weeks (two years) to resolve seasonality separately from a media effect. Daily or hourly grain for the same regression multiplies the row count but does not multiply independent information at the same rate, because adjacent hours within a day are highly autocorrelated; the extra rows buy resolution on within-week shape, not more independent evidence for the weekly coefficient. A day-part causal test (does a 6pm-9pm flight lift traffic versus a 9pm-midnight flight) needs the opposite: fine enough grain (hour-of-week slot) that the two windows are actually distinguishable in the data, because collapsing both into a single "evening" daypart erases the very contrast the test is designed to detect.
Privacy. Individual-level event timestamps at instant grain are higher-risk for re-identification than the same events aggregated to a slot or a week, because a rare instant (a single visit at 3:14:07am) can be a fingerprint in a way that "visited during slot 3" is not. Aggregating to a coarser grain before an inventory leaves controlled infrastructure is a defensible privacy control — but it must be declared, not discovered downstream by an analyst wondering why every hour looks identical within a day.
Platform reporting grain. Ad platforms, POS systems, and BI tools each report at their own native grain, and that grain is frequently coarser than the canonical slot even when the underlying event stream is finer — a POS system might expose "daily transaction count" with no hourly breakdown available at all. The platform's native grain caps the finest grain any analysis built on that feed can honestly claim, regardless of what grain the modeling question would prefer.
Decision guide
- Weekly MMM regression across many markets
- iso_week (cell x week) — coarse, stable, matches spend cadence
- Day-part causal lift test (single market)
- hour_of_week_slot — fine enough to isolate the tested window
- Cross-platform media plan reporting
- daypart, converted per zone/week to hour_of_week_slot for measurement
- Broadcast/linear inventory and program scheduling
- broadcast_day, with a declared cutover hour
- Any export leaving controlled infrastructure
- no finer than privacy policy allows; declare the aggregation applied
If a caller requests hour-of-week slot delivery and the system can only produce day or week grain, that is a rejection or a renegotiation, not a quiet substitution. A report labeled "hourly" that is actually daily under the hood is not a rounding error — it silently answers a different question than the one asked, and no downstream consumer can detect the substitution without re-deriving the grain from first principles.
Truncation is a one-way door
Timestamp rounding and truncation (timestamp-rounding-truncation) is the
data-quality version of the same problem: a timestamp stored with only
day-level precision — common in older warehouses or privacy-truncated
exports — cannot be placed in a slot at all, because the hour-of-week
information was discarded before the record ever reached this pipeline.
There is no recovery step for this; the claimed grain of any downstream
analysis must be capped at the coarsest grain any input column actually
supports, and a system that reports slot-level granularity built on top
of day-truncated inputs is fabricating precision it does not have. When
in doubt, treat the finest grain any single input column supports as a
hard ceiling on the finest grain the whole pipeline may claim, and state
that ceiling explicitly in any report — see
Measurement semantics for how this
compounds with event-vs-ingestion-vs-report time ambiguity, and
The 168 axis for the canonical unit this ladder is
built around.