How to Build a Product Analytics with Lovable

1Create a product analytics app with Supabase. Set up these tables:
2
3- projects: id, owner_id, name, tracking_key (uuid, unique, auto-generated), created_at
4
5- sessions: id, project_id, anonymous_id (text, browser fingerprint or random UUID), user_id (uuid nullable, set after login), started_at (timestamptz), ended_at (timestamptz nullable), duration_seconds (integer nullable), page_count (integer default 1), device_type ('desktop'|'tablet'|'mobile'), browser (text), referrer (text nullable), landing_page (text), created_at
6
7- events: id, project_id, session_id, anonymous_id (text), user_id (uuid nullable), event_name (text), page_url (text), properties (jsonb default '{}'), client_timestamp (timestamptz), created_at
8
9Create indexes:
10- events(project_id, event_name, created_at DESC)
11- events(project_id, user_id, created_at DESC) WHERE user_id IS NOT NULL
12- sessions(project_id, started_at DESC)
13
14Create a materialized view daily_active_users:
15SELECT project_id, DATE(created_at) as day, COUNT(DISTINCT anonymous_id) as dau, COUNT(*) as event_count FROM events GROUP BY project_id, DATE(created_at)
16
17Create a materialized view top_events:
18SELECT project_id, event_name, COUNT(*) as event_count, COUNT(DISTINCT anonymous_id) as unique_users FROM events WHERE created_at > now() - interval '30 days' GROUP BY project_id, event_name ORDER BY event_count DESC
19
20Enable RLS: project owners can only read data for their own projects.
21Seed with 1000 sample events across 100 sessions for testing.

1Create two Supabase Edge Functions:
2
31. supabase/functions/track/index.ts — event ingestion:
4- Accept POST with JSON body: { tracking_key, session_id, anonymous_id, user_id, event_name, page_url, properties, client_timestamp }
5- Validate tracking_key exists in projects table (use service role client)
6- Insert into events table
7- If session_id does not exist in sessions table, create a new session row
8- Return { ok: true } with 201 status
9- Accept CORS from any origin (tracked apps may be on any domain)
10- No authentication required — tracking is public-facing
11
122. supabase/functions/snippet/index.ts — JavaScript snippet delivery:
13- Accept GET requests with ?key=[tracking_key] query parameter
14- Return Content-Type: application/javascript
15- Return a minified JavaScript snippet that:
16  a. Generates or retrieves a UUID from localStorage as anonymous_id
17  b. Creates a session_id (UUID) stored in sessionStorage
18  c. Exposes window.analytics.track(eventName, properties) function
19  d. The track() function POSTs to the ingest function URL with all required fields
20  e. Auto-tracks page views on load and on popstate/hashchange
21  f. Includes the tracking_key from the snippet URL

1import { useQuery } from '@tanstack/react-query'
2import { supabase } from '@/integrations/supabase/client'
3import { ComposedChart, Line, Bar, XAxis, YAxis, Tooltip, Legend, ResponsiveContainer } from 'recharts'
4import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card'
5import { format, subDays } from 'date-fns'
6
7type DauRow = { day: string; dau: number; event_count: number }
8
9type Props = { projectId: string; days?: number }
10
11export function TrendChart({ projectId, days = 30 }: Props) {
12  const since = format(subDays(new Date(), days), 'yyyy-MM-dd')
13
14  const { data = [], isLoading } = useQuery<DauRow[]>({
15    queryKey: ['dau', projectId, days],
16    queryFn: async () => {
17      const { data, error } = await supabase
18        .from('daily_active_users')
19        .select('day, dau, event_count')
20        .eq('project_id', projectId)
21        .gte('day', since)
22        .order('day', { ascending: true })
23      if (error) throw error
24      return data.map((r) => ({ ...r, day: format(new Date(r.day), 'MMM d') }))
25    },
26    staleTime: 10 * 60_000,
27  })
28
29  if (isLoading) return <div className="h-64 animate-pulse rounded-lg bg-muted" />
30
31  return (
32    <Card>
33      <CardHeader><CardTitle>Active Users & Events</CardTitle></CardHeader>
34      <CardContent>
35        <ResponsiveContainer width="100%" height={280}>
36          <ComposedChart data={data}>
37            <XAxis dataKey="day" tick={{ fontSize: 11 }} />
38            <YAxis yAxisId="left" />
39            <YAxis yAxisId="right" orientation="right" />
40            <Tooltip />
41            <Legend />
42            <Bar yAxisId="right" dataKey="event_count" name="Events" fill="#e0e7ff" />
43            <Line yAxisId="left" dataKey="dau" name="DAU" stroke="#6366f1" strokeWidth={2} dot={false} />
44          </ComposedChart>
45        </ResponsiveContainer>
46      </CardContent>
47    </Card>
48  )
49}

1Build a FunnelAnalysis component at src/components/analytics/FunnelAnalysis.tsx.
2
3Requirements:
4- Create a Postgres function get_funnel_stats(p_project_id uuid, p_steps text[], p_days integer) that:
5  - Takes an ordered array of event_name steps
6  - For each step, counts distinct anonymous_ids who fired that event within p_days days
7  - A user must have completed all prior steps to count in the current step (ordered funnel)
8  - Returns rows: { step_name, step_index, user_count, conversion_rate }
9- Build the UI:
10  - A shadcn/ui Card with a 'Configure Funnel' section at the top
11  - Up to 5 Input fields for step event names (with + and - buttons to add/remove steps)
12  - A 30d/60d/90d period Select
13  - A Calculate Button that calls the Postgres function via supabase.rpc()
14  - Below the config, show Recharts FunnelChart with step names and conversion rates as LabelList
15  - Each funnel layer uses a different shade of indigo
16  - Below the chart, show a summary table: step, users, conv. from previous, conv. from top

1Build a RetentionTable component at src/components/analytics/RetentionTable.tsx.
2
3Requirements:
4- Create a Postgres function get_retention(p_project_id uuid, p_weeks integer default 8) that:
5  - Groups users by the week they first appeared (week 0 = signup cohort)
6  - For each cohort week, counts users who fired any event in week 1, 2, 3... up to p_weeks
7  - Returns rows: { cohort_week (date), cohort_size, week_1_pct, week_2_pct, ..., week_8_pct }
8- Build the UI:
9  - Render an HTML table using shadcn/ui Table, TableHeader, TableBody, TableRow, TableCell
10  - Header row: Cohort Week, Users, Week 1, Week 2, ... Week 8
11  - Each data cell shows the percentage and is colored with a background from white (0%) to indigo (100%) using inline style: background: rgba(99,102,241, {pct/100})
12  - The cohort_week column shows the week start date formatted as 'MMM d, yyyy'
13  - Show a Skeleton while loading
14  - Add a tooltip on each percentage cell showing the absolute user count