How to Build Product analytics with V0

1// Paste this prompt into V0's AI chat:
2// Build a product analytics platform. Create a Supabase schema with:
3// 1. projects: id (uuid PK), owner_id (uuid FK), name (text), api_key (uuid default gen_random_uuid() unique), created_at (timestamptz)
4// 2. events: id (uuid PK), project_id (uuid FK), user_id (text), session_id (text), event_name (text), properties (jsonb), page_url (text), referrer (text), device_info (jsonb), created_at (timestamptz)
5// Add index on events (project_id, event_name, created_at)
6// 3. funnels: id (uuid PK), project_id (uuid FK), name (text), steps (jsonb), created_at (timestamptz)
7// 4. dashboards: id (uuid PK), project_id (uuid FK), name (text), widgets (jsonb), created_at (timestamptz)
8// Add RLS: project owners can read their own data.
9// Generate SQL migration and TypeScript types.

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3
4export const runtime = 'edge'
5
6const supabase = createClient(
7  process.env.SUPABASE_URL!,
8  process.env.SUPABASE_SERVICE_ROLE_KEY!
9)
10
11export async function POST(req: NextRequest) {
12  const apiKey = req.headers.get('x-api-key')
13  if (!apiKey) {
14    return NextResponse.json({ error: 'Missing API key' }, { status: 401 })
15  }
16
17  const { data: project } = await supabase
18    .from('projects')
19    .select('id')
20    .eq('api_key', apiKey)
21    .single()
22
23  if (!project) {
24    return NextResponse.json({ error: 'Invalid API key' }, { status: 401 })
25  }
26
27  const { events } = await req.json()
28  if (!Array.isArray(events) || events.length === 0) {
29    return NextResponse.json({ error: 'Events array required' }, { status: 400 })
30  }
31
32  const rows = events.map((e: Record<string, unknown>) => ({
33    project_id: project.id,
34    user_id: e.user_id as string,
35    session_id: e.session_id as string,
36    event_name: e.event_name as string,
37    properties: e.properties ?? {},
38    page_url: e.page_url as string,
39    referrer: e.referrer as string,
40    device_info: e.device_info ?? {},
41  }))
42
43  const { error } = await supabase.from('events').insert(rows)
44  if (error) {
45    return NextResponse.json({ error: error.message }, { status: 500 })
46  }
47
48  return NextResponse.json({ ingested: rows.length })
49}

1// Paste this prompt into V0's AI chat:
2// Build a product analytics dashboard at app/dashboard/page.tsx.
3// Requirements:
4// - Fetch event counts from Supabase for the selected project
5// - Show 4 metric Cards at the top: Active Users, Total Events, Sessions, Events/User
6// - Each Card shows the current value and percentage change from previous period
7// - Add shadcn/ui Tabs for time range selection: 24h, 7d, 30d, 90d
8// - Below metrics, show a Recharts LineChart of events over time (x-axis: date, y-axis: event count)
9// - Add a Table below the chart showing top 10 event names with count and percentage
10// - Use HoverCard on metric Cards to show tooltip with additional context
11// - Use Select for filtering by event_name
12// - Server Components for data fetching
13// - Use Skeleton components while data loads

1// Paste this prompt into V0's AI chat:
2// Build a funnel analysis page at app/dashboard/funnels/page.tsx.
3// Requirements:
4// - Show existing funnels in a list with name and step count
5// - "Create Funnel" Button opens a Dialog with:
6//   - Input for funnel name
7//   - Dynamic step builder: each step has a Select for event_name (populated from distinct event names)
8//   - Add Step and Remove Step buttons
9//   - Save via Server Action createFunnel()
10// - When a funnel is selected, show visualization:
11//   - Recharts BarChart (horizontal) with each step as a bar
12//   - Bar width represents percentage of users who reached that step
13//   - Labels show: step name, user count, conversion rate from previous step
14//   - Use Badge for conversion rate (green >50%, yellow 20-50%, red <20%)
15// - Funnel data is computed by querying events table:
16//   - Step 1: count distinct user_ids with event_name = step1
17//   - Step 2: count distinct user_ids from step 1 set who also have event_name = step2 (after step1 timestamp)
18//   - Continue for each step
19// - Server Components for data fetching, client component for interactive chart

1// Paste this prompt into V0's AI chat:
2// Create a Supabase SQL migration for analytics materialized views:
3// 1. A materialized view daily_metrics AS:
4//    SELECT project_id, date_trunc('day', created_at) as date,
5//    event_name, count(*) as event_count,
6//    count(distinct user_id) as unique_users,
7//    count(distinct session_id) as sessions
8//    FROM events GROUP BY project_id, date, event_name
9// 2. A materialized view retention_cohorts AS:
10//    SELECT project_id,
11//    date_trunc('week', first_seen) as cohort_week,
12//    date_trunc('week', created_at) as activity_week,
13//    count(distinct user_id) as users
14//    FROM events JOIN (SELECT project_id, user_id, min(created_at) as first_seen FROM events GROUP BY project_id, user_id) first_events USING (project_id, user_id)
15//    GROUP BY project_id, cohort_week, activity_week
16// 3. A pg_cron job that refreshes both views every hour: SELECT cron.schedule('refresh-analytics', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_metrics; REFRESH MATERIALIZED VIEW CONCURRENTLY retention_cohorts;')
17// 4. Update the dashboard to query from daily_metrics instead of events table
18// Also create app/dashboard/retention/page.tsx showing a cohort heatmap using Recharts.

1import { createClient } from '@/lib/supabase/server'
2import { Card } from '@/components/ui/card'
3import { Badge } from '@/components/ui/badge'
4
5export default async function RetentionPage() {
6  const supabase = await createClient()
7
8  const { data: cohorts } = await supabase
9    .from('retention_cohorts')
10    .select('cohort_week, activity_week, users')
11    .order('cohort_week', { ascending: false })
12    .limit(500)
13
14  const cohortMap = new Map<string, Map<number, number>>()
15  const cohortSizes = new Map<string, number>()
16
17  cohorts?.forEach((row) => {
18    const key = row.cohort_week as string
19    const weekDiff = Math.round(
20      (new Date(row.activity_week as string).getTime() -
21        new Date(key).getTime()) /
22        (7 * 24 * 60 * 60 * 1000)
23    )
24    if (!cohortMap.has(key)) cohortMap.set(key, new Map())
25    cohortMap.get(key)!.set(weekDiff, row.users as number)
26    if (weekDiff === 0) cohortSizes.set(key, row.users as number)
27  })
28
29  return (
30    <div className="p-6">
31      <h1 className="text-2xl font-bold mb-6">Retention Cohorts</h1>
32      <Card className="p-4 overflow-x-auto">
33        <table className="w-full text-sm">
34          <thead>
35            <tr>
36              <th className="text-left p-2">Cohort</th>
37              <th className="p-2">Size</th>
38              {[...Array(8)].map((_, i) => (
39                <th key={i} className="p-2">W{i}</th>
40              ))}
41            </tr>
42          </thead>
43          <tbody>
44            {[...cohortMap.entries()].slice(0, 12).map(([week, data]) => {
45              const size = cohortSizes.get(week) ?? 1
46              return (
47                <tr key={week}>
48                  <td className="p-2 font-medium">{week.slice(0, 10)}</td>
49                  <td className="p-2 text-center">
50                    <Badge variant="outline">{size}</Badge>
51                  </td>
52                  {[...Array(8)].map((_, i) => {
53                    const users = data.get(i) ?? 0
54                    const pct = Math.round((users / size) * 100)
55                    const opacity = pct / 100
56                    return (
57                      <td
58                        key={i}
59                        className="p-2 text-center text-xs"
60                        style={{ backgroundColor: `rgba(59, 130, 246, ${opacity})`, color: pct > 50 ? 'white' : 'inherit' }}
61                      >
62                        {pct}%
63                      </td>
64                    )
65                  })}
66                </tr>
67              )
68            })}
69          </tbody>
70        </table>
71      </Card>
72    </div>
73  )
74}