How to Build a Product Analytics with Replit

1// Build a product analytics system with Express and PostgreSQL using Drizzle ORM.
2//
3// Tables:
4// 1. events: id serial primary key, user_id text not null, event_name text not null,
5//    properties jsonb (arbitrary key-value pairs), session_id text,
6//    timestamp timestamp default now()
7// 2. event_definitions: id serial, name text unique not null, category text,
8//    description text, is_active boolean default true
9// 3. funnels: id serial, name text not null, steps jsonb not null (ordered array of event_name strings),
10//    owner_id text not null, time_window_hours integer default 72, created_at timestamp default now()
11// 4. segments: id serial, name text not null,
12//    filter_config jsonb not null (e.g. {event_name: 'signup', property: 'plan', value: 'pro'}),
13//    owner_id text not null
14// 5. daily_aggregates: id serial, event_name text not null, date date not null,
15//    count integer not null, unique_users integer not null, unique(event_name, date)
16//
17// Routes:
18// POST /api/events (ingest — API key auth via x-api-key header from process.env.ANALYTICS_API_KEY)
19// POST /api/events/batch (bulk ingest up to 100 events)
20// GET /api/analytics/overview (top events by count over ?days=30 date range)
21// GET /api/analytics/funnel/:id (calculate funnel conversion)
22// GET /api/analytics/retention (cohort retention grid ?weeks=8)
23// GET /api/analytics/events/:name/timeseries (count over time for a specific event)
24//
25// Admin routes (Replit Auth):
26// POST /api/funnels, GET /api/funnels
27//
28// Bind to 0.0.0.0:3000.

1const express = require('express');
2const { db } = require('../db');
3const { events } = require('../schema');
4const { withDbRetry } = require('../lib/retryDb');
5
6const router = express.Router();
7
8// API key middleware for event ingestion
9function requireApiKey(req, res, next) {
10  const key = req.headers['x-api-key'];
11  if (!key || key !== process.env.ANALYTICS_API_KEY) {
12    return res.status(401).json({ error: 'Invalid API key' });
13  }
14  next();
15}
16
17// Single event ingestion
18router.post('/api/events', requireApiKey, express.json(), async (req, res) => {
19  const { userId, eventName, properties, sessionId } = req.body;
20  if (!userId || !eventName) {
21    return res.status(400).json({ error: 'userId and eventName are required' });
22  }
23  // Return 200 immediately — don't await if you want sub-10ms responses
24  // For correctness, we await but keep the handler fast
25  await withDbRetry(() =>
26    db.insert(events).values({
27      userId: String(userId),
28      eventName: String(eventName),
29      properties: properties || null,
30      sessionId: sessionId ? String(sessionId) : null,
31    })
32  );
33  return res.json({ recorded: true });
34});
35
36// Batch ingestion — up to 100 events
37router.post('/api/events/batch', requireApiKey, express.json(), async (req, res) => {
38  const { events: batch } = req.body;
39  if (!Array.isArray(batch) || batch.length === 0) {
40    return res.status(400).json({ error: 'events array required' });
41  }
42  if (batch.length > 100) {
43    return res.status(400).json({ error: 'Max 100 events per batch' });
44  }
45  const rows = batch.map(e => ({
46    userId: String(e.userId),
47    eventName: String(e.eventName),
48    properties: e.properties || null,
49    sessionId: e.sessionId ? String(e.sessionId) : null,
50  }));
51  await withDbRetry(() => db.insert(events).values(rows));
52  return res.json({ recorded: rows.length });
53});
54
55module.exports = router;

1const express = require('express');
2const { db } = require('../db');
3const { funnels } = require('../schema');
4const { eq } = require('drizzle-orm');
5
6const router = express.Router();
7
8router.get('/api/analytics/funnel/:id', async (req, res) => {
9  const [funnel] = await db.select().from(funnels)
10    .where(eq(funnels.id, parseInt(req.params.id))).limit(1);
11  if (!funnel) return res.status(404).json({ error: 'Funnel not found' });
12
13  const steps = funnel.steps; // e.g. ['page_view', 'signup_start', 'signup_complete', 'purchase']
14  const windowHours = funnel.timeWindowHours || 72;
15
16  if (steps.length < 2) return res.status(400).json({ error: 'Funnel needs at least 2 steps' });
17
18  // Build a CTE that tracks each user through the funnel sequentially
19  // For each step N, find users who did step N after step N-1 within the time window
20  const cteLines = steps.map((step, i) => {
21    if (i === 0) {
22      return `step_0 AS (
23        SELECT user_id, MIN(timestamp) AS step_time
24        FROM events WHERE event_name = $${i + 1}
25        GROUP BY user_id
26      )`;
27    }
28    return `step_${i} AS (
29      SELECT e.user_id, MIN(e.timestamp) AS step_time
30      FROM events e
31      JOIN step_${i - 1} prev ON e.user_id = prev.user_id
32      WHERE e.event_name = $${i + 1}
33        AND e.timestamp > prev.step_time
34        AND e.timestamp <= prev.step_time + interval '${windowHours} hours'
35      GROUP BY e.user_id
36    )`;
37  });
38
39  const countSelects = steps.map((_, i) =>
40    `(SELECT COUNT(DISTINCT user_id) FROM step_${i}) AS step_${i}_count`
41  ).join(',\n    ');
42
43  const sql = `WITH ${cteLines.join(',\n')}\nSELECT ${countSelects}`;
44
45  const result = await db.execute({ sql, params: steps });
46  const row = result.rows[0];
47
48  const counts = steps.map((step, i) => ({
49    step: i + 1,
50    event: step,
51    users: parseInt(row[`step_${i}_count`]),
52    conversionFromPrev: i === 0 ? 100 :
53      row[`step_${i - 1}_count`] > 0
54        ? Math.round(row[`step_${i}_count`] / row[`step_${i - 1}_count`] * 100)
55        : 0,
56  }));
57
58  return res.json({ funnelId: funnel.id, name: funnel.name, steps: counts });
59});
60
61module.exports = router;

1// server/jobs/aggregateEvents.js — run as a Scheduled Deployment every hour
2const { db } = require('../db');
3const { events, dailyAggregates } = require('../schema');
4const { sql } = require('drizzle-orm');
5
6async function runAggregation() {
7  console.log('[aggregate] Starting daily aggregation job...');
8
9  // Upsert daily counts for events from the last 48 hours
10  // (overlap ensures we catch any late-arriving events from yesterday)
11  await db.execute(sql`
12    INSERT INTO daily_aggregates (event_name, date, count, unique_users)
13    SELECT
14      event_name,
15      DATE(timestamp) AS date,
16      COUNT(*) AS count,
17      COUNT(DISTINCT user_id) AS unique_users
18    FROM events
19    WHERE timestamp >= NOW() - INTERVAL '48 hours'
20    GROUP BY event_name, DATE(timestamp)
21    ON CONFLICT (event_name, date)
22    DO UPDATE SET
23      count = EXCLUDED.count,
24      unique_users = EXCLUDED.unique_users
25  `);
26
27  // Clean up raw events older than 90 days to stay within PostgreSQL 10GB limit
28  const deleted = await db.execute(sql`
29    DELETE FROM events WHERE timestamp < NOW() - INTERVAL '90 days'
30  `);
31
32  console.log(`[aggregate] Done. Deleted ${deleted.rowCount} old events.`);
33}
34
35runAggregation()
36  .then(() => process.exit(0))
37  .catch(err => { console.error('[aggregate] Error:', err); process.exit(1); });

1// Ask Agent to build the React analytics dashboard with this prompt:
2// Build a React analytics dashboard with four views:
3//
4// 1. Overview Page:
5//    - Date range selector (last 7/30/90 days)
6//    - Fetch GET /api/analytics/overview?days=30
7//    - Bar chart using recharts BarChart showing top 10 events by count
8//    - Each bar shows event name and total count
9//    - Click on a bar navigates to that event's detail page
10//
11// 2. Funnel Page:
12//    - Dropdown to select from GET /api/funnels list
13//    - Fetch GET /api/analytics/funnel/:id on selection
14//    - Horizontal funnel visualization: series of bars narrowing left to right
15//    - Each bar shows step name, user count, and conversion % from previous step
16//    - Color gradient from green (high conversion) to red (drop-off)
17//
18// 3. Retention Page:
19//    - Fetch GET /api/analytics/retention?weeks=8
20//    - Cohort grid: rows = cohort week, columns = weeks since first event
21//    - Cell value = % of cohort users who returned that week
22//    - Color cells from white (0%) to deep blue (100%)
23//
24// 4. Event Detail Page (/events/:name):
25//    - Line chart showing daily count for this event over the past 30 days
26//    - Properties breakdown: top 10 values for each property key found in this event's jsonb
27//
28// Use React Router for navigation. Add a nav bar with links to all four views.