Mixpanel

Promptfree, for everyone, and the only version there is

Copy the prompt for Claude CodeCopy the prompt for CodexCopy the prompt for Cursor

Copy

Build me product analytics that replace Mixpanel — and know why this is ALMOST.

The reports are conceptually simple. What you are paying for is **funnels, retention and cohorts that recompute in seconds over hundreds of millions of events**. That is a columnar store and careful query design, not a table in a relational database, and it is the honest ceiling on a homemade version. At a few million events a month this is comfortable; at a few hundred million it is a different project.

STACK
- Node 20+ with Fastify
- SQLite through better-sqlite3 to begin with, and DuckDB or ClickHouse when the volume demands it. Design the schema so that move is a copy rather than a rewrite
- A tracking script under 6KB gzipped, plus server-side ingestion
- Server-rendered dashboard, charts as inline SVG
- Caddy in front

THE DATA MODEL
- events: id, name, distinct_id, anonymous_id, session_id, properties_json, path, country, device, browser, os, bot, at — one wide table, narrow columns for what you filter on, the rest in a map
- users: id, distinct_id, first_seen_at, last_seen_at, properties_json
- groups: id, kind, key, properties_json — because business products are used by an account, and most questions are really about the account rather than the person
- identities: anonymous_id, distinct_id, at — the stitch that makes events before signup belong to the person afterwards
- definitions for funnels, cohorts and saved reports. Never stored results — everything computes at query time or it goes stale

IDENTITY STITCHING
- An anonymous identifier per browser until the person is identified, then a stitch row binding the two
- After stitching, earlier events belong to the user, and every report must honour that retroactively — which is why events keep both identifiers rather than being rewritten
- Property values are recorded on the event as well as on the user, so 'which plan were they on when they did this' is answerable. That distinction between event properties and user properties trips up every homemade version

THE THREE REPORTS THAT MATTER
- **Funnels**: ordered steps with a conversion window. Strict or loose ordering, chosen explicitly and stated, because the two give very different numbers. Break down by any property, and show where people went instead of the next step — usually the most useful thing on the screen
- **Retention**: of the people who first did X in a period, how many did Y in each following period. Say which definition is in use, because there are several and they disagree
- **Cohorts**: a saved definition of people who did something with certain properties, computed at query time and usable as a filter everywhere else

MAKING IT FAST
- Partition by day and order by the columns you filter on. Those two choices decide whether a month of data answers in a second or a minute
- Keep property cardinality under control: a property with millions of distinct values as a real column will destroy the index. Keep those in the map
- A funnel is a self-join or a window function over one user's ordered events. Write it once, carefully, and test it against a known answer
- Sampling as a deliberate, labelled option for exploratory queries — never silently

BOTS AND HONESTY
- Flag, never drop, and exclude through a view
- The script executing is the strongest humanity signal; record it
- Every metric has its definition written beside it. These numbers get shown to people who act on them, and a figure you cannot explain in a sentence is one that will be doubted

INSTRUMENTATION
- Named events with properties, defined in one place in your code, with a schema. Autocapture is a starting point; named events are what makes analysis possible
- Server-side ingestion as well as browser, because the important events — a payment, a renewal — happen on the server
- Never capture the contents of an input, and never send personal data as a property without deciding to

OPERATIONS
- .env: DATABASE_PATH, BASE_URL, HASH_SALT, GEOIP_DB_PATH, SESSION_SECRET
- Migrations on boot, each once; nightly backup off the machine
- Health endpoint

WHAT MATTERS MOST
Identity stitching and the funnel query. Get the anonymous-to-known join right before any report exists — every funnel and every cohort is wrong without it — and write down the volume at which you would move to a columnar store.