One idea
Cohorts and funnels both start with user-level milestones. First find each user’s key dates or steps, then count users from that clean table.
Do not count raw event rows first. One active student can create many events and make your product look better than it is.
Cohort meaning
A cohort is a group of users who started in the same period. For example, users who signed up in July, or users whose first order was in week 32.
A retention cohort asks: after they started, how many came back in a later period? This is useful for apps, clubs, SaaS tools, marketplaces, and campus products.
Cohort query shape
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done
Monthly retention
This example uses an events table with user_id, event_name, and event_time. It groups users by signup month, then counts who was active in later months.
Change event names to match your sample data. If your database is SQLite, use strftime. If you use PostgreSQL, use date_trunc.
-- PostgreSQL
WITH signups AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(event_time)) AS cohort_month
FROM events
WHERE event_name = 'signup'
GROUP BY user_id
),
activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_time) AS activity_month
FROM events
WHERE event_name IN ('login', 'view_product', 'place_order')
),
cohort_activity AS (
SELECT
s.cohort_month,
a.activity_month,
(
EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month)
) - (
EXTRACT(YEAR FROM s.cohort_month) * 12 + EXTRACT(MONTH FROM s.cohort_month)
) AS month_number,
s.user_id
FROM signups s
JOIN activity a ON a.user_id = s.user_id
WHERE a.activity_month >= s.cohort_month
)
SELECT
cohort_month,
month_number,
COUNT(DISTINCT user_id) AS retained_users
FROM cohort_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;Funnel meaning
A funnel measures how many users complete steps in order. Example: signup, view product, add to cart, place order.
For a hackathon analytics project, a funnel is stronger when it answers a real question. For example: where do users drop before paying by UPI?
Ordered funnel
This query counts users who reached each step after the previous step. It prevents a user from being counted for checkout if checkout happened before signup.
Use MIN to keep the first time a user did each step.
WITH user_steps AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS signup_time,
MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS view_time,
MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS cart_time,
MIN(CASE WHEN event_name = 'place_order' THEN event_time END) AS order_time
FROM events
GROUP BY user_id
),
valid_steps AS (
SELECT
user_id,
signup_time,
CASE WHEN view_time >= signup_time THEN view_time END AS view_time,
CASE WHEN cart_time >= view_time AND view_time >= signup_time THEN cart_time END AS cart_time,
CASE WHEN order_time >= cart_time AND cart_time >= view_time AND view_time >= signup_time THEN order_time END AS order_time
FROM user_steps
)
SELECT 'signup' AS step, COUNT(*) AS users FROM valid_steps WHERE signup_time IS NOT NULL
UNION ALL
SELECT 'view_product', COUNT(*) FROM valid_steps WHERE view_time IS NOT NULL
UNION ALL
SELECT 'add_to_cart', COUNT(*) FROM valid_steps WHERE cart_time IS NOT NULL
UNION ALL
SELECT 'place_order', COUNT(*) FROM valid_steps WHERE order_time IS NOT NULL;Common mistake
Do not calculate funnel steps with total event counts. If one user views 20 products, that is still one user reaching the view step.
Also avoid mixing time windows. A weekly funnel should use events from the same defined window, or you should clearly say it is lifetime conversion.
Do this now
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done