konnosDashboard
marketing-events.ts117 lines · main
1import { newId } from '@briven/shared';
2import { and, gte, sql } from 'drizzle-orm';
3
4import { getDb } from '../db/client.js';
5import {
6 marketingEvents,
7 marketingEventTypes,
8 type MarketingEventType,
9} from '../db/schema.js';
10import { log } from '../lib/logger.js';
11
12/**
13 * /migrate funnel tracking. Two event types:
14 * - migrate_view: a marketing page rendered for a given source
15 * - migrate_lead_submitted: the public lead form POST succeeded
16 *
17 * Writes never block the request that triggered them — failures are
18 * logged and swallowed (a missing analytics row is preferable to a
19 * 500 on the surface that wanted to track).
20 */
21
22const ALLOWED_SOURCES = new Set([
23 'convex',
24 'supabase',
25 'firebase',
26 'mongodb',
27 'drizzle',
28 'prisma',
29 'postgres',
30 'hasura',
31 'nextauth',
32 'other',
33 'hub',
34]);
35
36interface TrackInput {
37 eventType: string;
38 source: string;
39 ipHash?: string | null;
40 userAgent?: string | null;
41}
42
43export async function trackMarketingEvent(input: TrackInput): Promise<void> {
44 if (!(marketingEventTypes as readonly string[]).includes(input.eventType)) return;
45 if (!ALLOWED_SOURCES.has(input.source)) return;
46 try {
47 const db = getDb();
48 await db.insert(marketingEvents).values({
49 id: newId('me'),
50 eventType: input.eventType as MarketingEventType,
51 source: input.source,
52 ipHash: input.ipHash ?? null,
53 userAgent: input.userAgent?.slice(0, 500) ?? null,
54 });
55 } catch (err) {
56 log.error('marketing_event_write_failed', {
57 eventType: input.eventType,
58 source: input.source,
59 error: err instanceof Error ? err.message : String(err),
60 });
61 }
62}
63
64interface FunnelRow {
65 source: string;
66 views: number;
67 leads: number;
68 /** views → leads, as a 0–1 ratio. Null when views is 0. */
69 conversion: number | null;
70}
71
72/**
73 * Per-source funnel rollup. Single GROUP BY query so we stay cheap
74 * even at high event volume. `since` defaults to 30 days ago.
75 */
76export async function getMarketingFunnel(opts: { sinceDays?: number } = {}): Promise<{
77 rows: FunnelRow[];
78 totals: FunnelRow;
79 sinceDays: number;
80}> {
81 const db = getDb();
82 const sinceDays = opts.sinceDays ?? 30;
83 const since = new Date(Date.now() - sinceDays * 24 * 60 * 60 * 1000);
84
85 const rows = await db
86 .select({
87 source: marketingEvents.source,
88 views: sql<number>`count(*) filter (where ${marketingEvents.eventType} = 'migrate_view')`,
89 leads: sql<number>`count(*) filter (where ${marketingEvents.eventType} = 'migrate_lead_submitted')`,
90 })
91 .from(marketingEvents)
92 .where(and(gte(marketingEvents.createdAt, since)))
93 .groupBy(marketingEvents.source);
94
95 const parsed: FunnelRow[] = rows.map((r) => {
96 const views = Number(r.views);
97 const leads = Number(r.leads);
98 return {
99 source: r.source,
100 views,
101 leads,
102 conversion: views > 0 ? leads / views : null,
103 };
104 });
105 parsed.sort((a, b) => b.views - a.views);
106
107 const totalViews = parsed.reduce((sum, r) => sum + r.views, 0);
108 const totalLeads = parsed.reduce((sum, r) => sum + r.leads, 0);
109 const totals: FunnelRow = {
110 source: 'all',
111 views: totalViews,
112 leads: totalLeads,
113 conversion: totalViews > 0 ? totalLeads / totalViews : null,
114 };
115
116 return { rows: parsed, totals, sinceDays };
117}