Analytics prompts: pin cohort windows and time zones

Last updated: 7 Oct 202611 min read

tutorial

A cohort is defined by a membership event and a time boundary. Prompting for a retention or conversion comparison must identify that event, the time zone used for bucketing, the observation horizon, data cutoff, and how incomplete cohorts are marked. A calendar-day label and a rolling 24-hour window answer different questions. Never compare a mature cohort with a recent cohort whose users have not had equal time to complete. The model can draft a cohort table schema, but the timestamp conversion and eligibility calculation belong in tested code.

Operational case

Meridian's report closes at midnight in the reporting time zone. A signup started at 23:50 may complete shortly after midnight while still falling within its personal 24-hour window. If the analyst buckets starts by local day but cuts completions at the same midnight, the final cohort is incomplete. The release table marks that cohort provisional until all users have had the full 24 hours plus the documented late-arrival allowance.

Membership: first eligible signup start
Bucket: reporting-zone calendar date
Outcome: complete within personal 24-hour window
Cutoff: end of outcome window plus late-arrival allowance
Recent cohort: provisional, not compared as mature

Performance and review cost

Bucketing N events after timestamp normalization is O(N); joining each user's outcome is O(N) expected time with an indexed identity map. Waiting for a mature window delays publication but avoids a false decline. Store UTC instants and the reporting-zone rule so a daylight-saving transition or boundary event can be replayed consistently.

Common Mistakes

Do not compare an immature cohort with a fully observed cohort.
Do not confuse local calendar days with rolling 24-hour windows.
Do not erase the reporting time zone from the chart caption.

Connected lessons

prompt engineering

product analytics