Mixpanel export (Insights CSV / raw Export API)
The report CSV downloaded from a Mixpanel Insights board and the raw /export API dump, mistaken for equivalent event tables, they use different counting, different dedup, and a timestamp convention that quietly offsets the whole dataset.
The shape, how the file is really laid out
Insights CSV: a header, one row per segment, an "$overall" / totals row, and
"undefined" for null segments; the number is whatever the report computed
(Total events vs Unique users, on the report's own counting method). Raw Export
API: newline-delimited JSON (JSONL), one event per line, shaped as
{"event": "...", "properties": {"time": <unix seconds>, "distinct_id": "...",
"$insert_id": "...", ...}}. Mixpanel-reserved properties are prefixed "$"
($city, $browser) and "mp_"; time is Unix epoch SECONDS, and in legacy projects
it is stored offset to the project's timezone rather than UTC.
The traps
The time trap is the headline: in legacy projects the epoch-seconds "time" is written in project-local time, so interpreting it as UTC shifts every event by the project's offset and moves events across day boundaries; newer projects store UTC, you must know which.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Mixpanel Docs, "Raw Event Export API" (JSONL, epoch-seconds time) · Mixpanel Docs, "$insert_id and event deduplication" · Mixpanel Docs, "Timezones / project time and the time property" · Mixpanel Docs, "Export data from Insights"