Before I bought a product analytics tool, I assumed the value was in the data collection — that Mixpanel and Amplitude were doing something clever to capture events I couldn't capture myself. They aren't, usually. If your app writes to a relational database, you already have users, events or actions, subscriptions, and timestamps on all of them. That's the entire raw material product analytics runs on. The tool isn't collecting anything you don't already have; it's querying and visualizing data that's sitting in your own tables.
I ran internal analytics off Postgres for close to two years before we bought Mixpanel, and by the time we bought it, the SQL queries weren't the limiting factor. The limiting factor was that our PM wanted to build her own funnels without asking an engineer, and no SQL query fixes that. But the actual analysis — funnels, retention, feature adoption, activation — we'd been doing in psql the whole time, and it worked fine.
This post is the five queries that covered 90% of what we needed, plus an honest account of where SQL stops being the right tool.
What product analytics tools actually give you
Strip away the UI and a product analytics platform is doing four things:
- Ingesting events — either auto-captured (clicks, page views) or explicitly tracked (
track('signed_up')) — into an event table, usually columnar, optimized for fast aggregation over billions of rows. - Running aggregate queries against that event table — funnels are conversion-rate queries, retention is a cohort self-join, feature adoption is a distinct-user count with a filter.
- Rendering the results as charts, with a UI that lets non-technical people build those queries by clicking instead of writing SQL.
- Layering on things that are genuinely hard to build yourself — real-time streaming, session replay, auto-capture, experimentation/feature-flag integration.
Items 1 and 2 are exactly what your production database already contains, assuming you're tracking anything at all — signups, logins, feature usage, subscription events. Item 3 is a real value-add if your team includes people who don't write SQL. Item 4 is where the platforms earn their keep, and I'll come back to it.
If your team is technical and your event volume is in the thousands-to-low-millions per month rather than billions, a well-indexed Postgres table and five queries will get you further than you'd expect before you need any of that.
The data model these queries assume
Nothing exotic — this is close to what most apps already have:
-- users: one row per signup
-- id, email, created_at
-- events: one row per tracked action
-- id, user_id, event_name, created_at, metadata (jsonb)
-- subscriptions: one row per paid plan a user is/was on
-- id, user_id, plan, status, started_at, canceled_atIf you're logging application events into a table like this already — even loosely — you can run all five queries below with minor renaming. If you're not logging events at all yet, that's the actual prerequisite for product analytics, whether you buy a platform or not. Mixpanel doesn't remove the need to instrument your app; it just gives the instrumented events somewhere to land.
Fastero
Connect your database. Ask questions. Get dashboards.
Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.
Try free →Five core product analytics queries
1. Funnel analysis: signup → activation → paid
The core funnel question is "what fraction of people who did step 1 went on to do step 2, then step 3." In SQL that's a series of EXISTS checks against the same base cohort:
WITH signups AS (
SELECT id AS user_id, created_at AS signup_at
FROM users
WHERE created_at >= '2026-06-01'
AND created_at < '2026-07-01'
),
activated AS (
SELECT DISTINCT e.user_id
FROM events e
JOIN signups s ON s.user_id = e.user_id
WHERE e.event_name = 'activated_user'
AND e.created_at BETWEEN s.signup_at AND s.signup_at + interval '14 days'
),
paid AS (
SELECT DISTINCT sub.user_id
FROM subscriptions sub
JOIN signups s ON s.user_id = sub.user_id
WHERE sub.status = 'active'
AND sub.started_at BETWEEN s.signup_at AND s.signup_at + interval '30 days'
)
SELECT
(SELECT count(*) FROM signups) AS step_1_signups,
(SELECT count(*) FROM activated) AS step_2_activated,
(SELECT count(*) FROM paid) AS step_3_paid,
round(100.0 * (SELECT count(*) FROM activated) / NULLIF((SELECT count(*) FROM signups), 0), 1) AS pct_activated,
round(100.0 * (SELECT count(*) FROM paid) / NULLIF((SELECT count(*) FROM activated), 0), 1) AS pct_activated_to_paid;The pattern generalizes to as many steps as you need — each step is a CTE that filters the previous cohort down by a time-windowed EXISTS/JOIN. Add a step, add a percentage column. It's more typing than clicking through a funnel builder, but it's also completely transparent about what "activated" means, which matters more than it sounds like it should — funnel tools have a way of quietly redefining your step boundaries when you're not looking.
2. Retention cohort: the real matrix
This is the one people assume requires a specialized tool. It doesn't — it's date_trunc, a generate_series to get complete week buckets (including weeks with zero activity, which matters for the matrix to be honest), and a LEFT JOIN:
WITH cohorts AS (
SELECT id AS user_id, date_trunc('month', created_at) AS cohort_month
FROM users
WHERE created_at >= '2026-01-01'
),
activity AS (
SELECT DISTINCT user_id, date_trunc('week', created_at) AS active_week
FROM events
WHERE event_name IN ('query_or_report_created', 'dashboard_created_from_file', 'connection_activated')
),
cohort_weeks AS (
SELECT
c.cohort_month,
c.user_id,
a.active_week,
-- weeks elapsed between cohort start and this activity
floor(extract(epoch FROM (a.active_week - c.cohort_month)) / (7 * 86400))::int AS week_number
FROM cohorts c
LEFT JOIN activity a ON a.user_id = c.user_id AND a.active_week >= c.cohort_month
)
SELECT
cohort_month,
count(DISTINCT user_id) FILTER (WHERE week_number = 0) AS week_0,
count(DISTINCT user_id) FILTER (WHERE week_number = 1) AS week_1,
count(DISTINCT user_id) FILTER (WHERE week_number = 2) AS week_2,
count(DISTINCT user_id) FILTER (WHERE week_number = 3) AS week_3,
count(DISTINCT user_id) FILTER (WHERE week_number = 4) AS week_4,
round(100.0 * count(DISTINCT user_id) FILTER (WHERE week_number = 1) /
NULLIF(count(DISTINCT user_id) FILTER (WHERE week_number = 0), 0), 1) AS week_1_retention_pct
FROM cohort_weeks
GROUP BY cohort_month
ORDER BY cohort_month;Two things make this query correct rather than just plausible-looking. First, the LEFT JOIN — an INNER JOIN here silently drops users who churned entirely, which inflates every retention percentage. Second, week_number is computed relative to each user's own cohort start, not a fixed calendar week, or your cohorts won't line up into a proper triangle matrix. If you want the classic cohort-retention heatmap visual, this result set pivots directly into one — Excel, a BI tool, or a five-line pandas script all take it from here.
3. Feature adoption: who's actually using feature X
"What percent of active users touched feature X in the last 30 days" is a straightforward ratio of two distinct-user counts:
WITH active_users AS (
SELECT DISTINCT user_id
FROM events
WHERE created_at >= now() - interval '30 days'
),
feature_users AS (
SELECT DISTINCT user_id
FROM events
WHERE event_name = 'streamlit_app_created'
AND created_at >= now() - interval '30 days'
)
SELECT
(SELECT count(*) FROM active_users) AS active_users_30d,
(SELECT count(*) FROM feature_users) AS feature_users_30d,
round(100.0 * (SELECT count(*) FROM feature_users) /
NULLIF((SELECT count(*) FROM active_users), 0), 1) AS adoption_pct
FROM (SELECT 1) AS dummy;Run this per feature and UNION ALL the results to get an adoption leaderboard across your whole event vocabulary in one query — genuinely useful for spotting which shipped features nobody found.
4. Activation rate: time-boxed "aha moment"
Different from the funnel query above in one important way: this measures the rate at which a specific milestone happens within a specific window, independent of any other funnel step:
WITH signups AS (
SELECT id AS user_id, created_at AS signup_at
FROM users
WHERE created_at >= now() - interval '90 days'
),
activation_events AS (
SELECT s.user_id, min(e.created_at) AS activated_at
FROM signups s
JOIN events e ON e.user_id = s.user_id
WHERE e.event_name IN (
'dashboard_created_from_file', 'connection_activated',
'query_or_report_created', 'streamlit_app_created',
'workflow_draft_created', 'activated_user'
)
AND e.created_at BETWEEN s.signup_at AND s.signup_at + interval '7 days'
GROUP BY s.user_id
)
SELECT
count(DISTINCT s.user_id) AS total_signups,
count(DISTINCT a.user_id) AS activated_within_7d,
round(100.0 * count(DISTINCT a.user_id) / NULLIF(count(DISTINCT s.user_id), 0), 1) AS activation_rate_pct,
round(avg(extract(epoch FROM (a.activated_at - s.signup_at)) / 3600), 1) AS avg_hours_to_activate
FROM signups s
LEFT JOIN activation_events a ON a.user_id = s.user_id;The min(e.created_at) inside the CTE matters — you want the first qualifying event, not a count of all of them, or you'll conflate "activated once" with "activated repeatedly" and overstate the rate for power users who happen to be in the window.
5. Power users: action frequency distribution
Not everyone with an account is equally engaged, and averages hide that. NTILE buckets users into quartiles by activity so you can see the shape of engagement, not just the mean:
WITH user_activity AS (
SELECT user_id, count(*) AS event_count
FROM events
WHERE created_at >= now() - interval '30 days'
GROUP BY user_id
),
buckets AS (
SELECT
user_id,
event_count,
ntile(4) OVER (ORDER BY event_count) AS quartile
FROM user_activity
)
SELECT
quartile,
count(*) AS users,
min(event_count) AS min_events,
max(event_count) AS max_events,
round(avg(event_count), 1) AS avg_events
FROM buckets
GROUP BY quartile
ORDER BY quartile DESC;Quartile 4 is your power-user segment — the group worth interviewing, worth building for next, and usually a strong predictor of who renews. In practice, a small top quartile driving a wildly disproportionate share of total events is close to universal; the number worth watching is whether that quartile's floor (min_events) is trending up or down over time.
Where SQL breaks down
I want to be straight about this because it's the part vendor comparison content usually skips: these five queries cover a lot of ground, but there's a real point past which hand-written SQL stops being the efficient answer.
Real-time event streams. Every query above runs against your production or replica database, which means it's as fresh as your last read, not live. If you need to watch events arrive within seconds — debugging a live incident, watching a launch in real time — a purpose-built event pipeline (Kafka into a columnar store, or a platform that does this for you) is doing something your OLTP database genuinely isn't built for.
Session replay. There's no SQL query that reconstructs what a user's screen looked like when they got stuck. That requires capturing DOM mutations or screen recordings client-side, which is a different instrumentation problem entirely, and it's one of the clearest cases where Mixpanel/FullStory-style tools are doing something you can't approximate with a database query.
Auto-capture. Tools like Mixpanel and Amplitude can retroactively analyze clicks and page views you never explicitly tracked, because their SDK captures broadly by default. With hand-rolled event logging, you only have data for what you remembered to instrument. If your engineering team is inconsistent about adding track() calls, auto-capture closes gaps SQL can't — there's simply no data to query if the event was never written.
Experimentation. A/B test assignment, statistical significance testing, and automatic winner detection are a genuinely different piece of engineering than aggregate reporting. You can build this on top of Postgres — assignment table, exposure events, a t-test in a notebook — but at that point you're re-building a chunk of what a platform ships out of the box, and it's worth being honest with yourself about whether that's a good use of engineering time.
Non-technical stakeholders self-serving. This is the one that actually got us to buy Mixpanel. Our SQL queries were accurate and fast, but every new funnel cut meant a Slack message to an engineer. A PM who can drag-and-drop a funnel builder and get an answer in ninety seconds is a real productivity difference, not a nice-to-have — it changes how often the question even gets asked.
The middle ground: SQL + scheduling + a dashboard
The gap between "run a query in psql when someone asks" and "pay $50k/year for a full platform" is wider than the vendor comparison pages make it look. The queries above answer the analysis question. What they don't do on their own is show up automatically, on a schedule, somewhere a non-SQL person can see them without pinging you.
That's the part worth solving separately from "do we need Mixpanel." Save the five queries above as saved reports, schedule them to refresh weekly, and share the resulting numbers as a lightweight dashboard your team can check without a database connection — that's the piece Fastero is built for: taking exactly this kind of SQL and turning it into something scheduled and shareable, without asking you to first stand up an event-ingestion pipeline you don't need yet.
When to actually buy Mixpanel (or Amplitude)
Buy the platform when a few of these are true, not just one:
- You're tracking 100+ distinct events and the mental overhead of remembering what each one means, in SQL, without a UI, has become its own tax.
- Non-technical PMs or growth people need to build their own funnels without going through an engineer — this is the single strongest reason, and the one hardest to route around with SQL.
- You need session replay to debug UX problems you can't diagnose from aggregate numbers alone.
- You're running a real experimentation program — multiple concurrent A/B tests with proper statistical rigor, not a one-off comparison.
- Your team has grown past ~30 people and "ask an engineer for a query" no longer scales as your default answer path.
If none of those are true yet, you're very likely over-buying. Write the five queries, schedule them, and revisit the Mixpanel question when the friction is real rather than hypothetical.
Try Fastero free — connect your product database and ask questions in plain English — get the analytics you need without adopting another platform. No credit card required.