# How to Build a Product Analytics with Lovable - Tool: How to Build with Lovable - Difficulty: Intermediate - Compatibility: Lovable Pro or higher - Last updated: April 2026 ## TL;DR Build a product analytics platform in Lovable that tracks user events and sessions with a JavaScript snippet delivered via Edge Function, uses Supabase materialized views for fast aggregations, and renders trend, funnel, and retention charts with Recharts — all without a third-party analytics vendor. ## Before you start - Lovable Pro account (Edge Functions are required) - Supabase project with URL, anon key, and service role key ready - VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY in Cloud tab → Secrets - SUPABASE_SERVICE_ROLE_KEY in Supabase Secrets for the Edge Function - Access to modify the HTML of the application you want to track (to add the script tag) ## Step-by-step guide ### 1. Scaffold the events and sessions schema Create the tracking tables with appropriate partitioning strategy, indexes for time-range queries, and the initial materialized views. The schema is optimized for append-only writes and range queries. ``` Create a product analytics app with Supabase. Set up these tables: - projects: id, owner_id, name, tracking_key (uuid, unique, auto-generated), created_at - 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 - 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 Create indexes: - events(project_id, event_name, created_at DESC) - events(project_id, user_id, created_at DESC) WHERE user_id IS NOT NULL - sessions(project_id, started_at DESC) Create a materialized view daily_active_users: SELECT 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) Create a materialized view top_events: SELECT 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 Enable RLS: project owners can only read data for their own projects. Seed with 1000 sample events across 100 sessions for testing. ``` > Pro tip: For production scale, consider partitioning the events table by month using Postgres range partitioning. Ask Lovable to add this after the initial build is working — it is easier to add once you understand the query patterns. **Expected result:** All tables and indexes are created. Both materialized views are populated with the seed data. Querying SELECT * FROM daily_active_users returns rows grouped by day. ### 2. Build the event ingest Edge Function and tracking snippet Create a Supabase Edge Function that accepts event payloads from tracked applications. A second endpoint serves the JavaScript snippet that tracked apps include via a script tag. ``` Create two Supabase Edge Functions: 1. supabase/functions/track/index.ts — event ingestion: - Accept POST with JSON body: { tracking_key, session_id, anonymous_id, user_id, event_name, page_url, properties, client_timestamp } - Validate tracking_key exists in projects table (use service role client) - Insert into events table - If session_id does not exist in sessions table, create a new session row - Return { ok: true } with 201 status - Accept CORS from any origin (tracked apps may be on any domain) - No authentication required — tracking is public-facing 2. supabase/functions/snippet/index.ts — JavaScript snippet delivery: - Accept GET requests with ?key=[tracking_key] query parameter - Return Content-Type: application/javascript - Return a minified JavaScript snippet that: a. Generates or retrieves a UUID from localStorage as anonymous_id b. Creates a session_id (UUID) stored in sessionStorage c. Exposes window.analytics.track(eventName, properties) function d. The track() function POSTs to the ingest function URL with all required fields e. Auto-tracks page views on load and on popstate/hashchange f. Includes the tracking_key from the snippet URL ``` > Pro tip: Keep the JavaScript snippet under 2KB minified. Any bloat in the snippet loads on every page of the tracked application. Avoid importing any libraries in the snippet — use only vanilla browser APIs. **Expected result:** Adding a script tag to any webpage fires a page view event to the ingest function. You can verify events are arriving by watching the events table in the Supabase Table Editor. ### 3. Build the trend chart for DAU and event volume Create the main trend chart reading from the daily_active_users materialized view. Show a dual-axis chart with DAU as a line and event volume as bars for the selected date range. ``` import { useQuery } from '@tanstack/react-query' import { supabase } from '@/integrations/supabase/client' import { ComposedChart, Line, Bar, XAxis, YAxis, Tooltip, Legend, ResponsiveContainer } from 'recharts' import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card' import { format, subDays } from 'date-fns' type DauRow = { day: string; dau: number; event_count: number } type Props = { projectId: string; days?: number } export function TrendChart({ projectId, days = 30 }: Props) { const since = format(subDays(new Date(), days), 'yyyy-MM-dd') const { data = [], isLoading } = useQuery({ queryKey: ['dau', projectId, days], queryFn: async () => { const { data, error } = await supabase .from('daily_active_users') .select('day, dau, event_count') .eq('project_id', projectId) .gte('day', since) .order('day', { ascending: true }) if (error) throw error return data.map((r) => ({ ...r, day: format(new Date(r.day), 'MMM d') })) }, staleTime: 10 * 60_000, }) if (isLoading) return
return ( Active Users & Events ) } ``` > Pro tip: Use a ComposedChart with two YAxis (one for DAU, one for event volume) so the two metrics do not compete for the same scale — daily active users might be in the hundreds while event count is in the thousands. **Expected result:** The trend chart shows 30 days of DAU as a purple line overlaid on event count bars. The dual Y-axis prevents either metric from being compressed by the other's scale. ### 4. Build the funnel analysis chart Create a configurable funnel where users define up to 5 event steps. A Postgres function computes how many users completed each step, and Recharts FunnelChart renders the conversion rates. ``` Build a FunnelAnalysis component at src/components/analytics/FunnelAnalysis.tsx. Requirements: - Create a Postgres function get_funnel_stats(p_project_id uuid, p_steps text[], p_days integer) that: - Takes an ordered array of event_name steps - For each step, counts distinct anonymous_ids who fired that event within p_days days - A user must have completed all prior steps to count in the current step (ordered funnel) - Returns rows: { step_name, step_index, user_count, conversion_rate } - Build the UI: - A shadcn/ui Card with a 'Configure Funnel' section at the top - Up to 5 Input fields for step event names (with + and - buttons to add/remove steps) - A 30d/60d/90d period Select - A Calculate Button that calls the Postgres function via supabase.rpc() - Below the config, show Recharts FunnelChart with step names and conversion rates as LabelList - Each funnel layer uses a different shade of indigo - Below the chart, show a summary table: step, users, conv. from previous, conv. from top ``` > Pro tip: Pre-populate the funnel steps with your most common conversion flow (e.g. page_view → signup → first_event → upgraded) so new users immediately see a meaningful chart rather than an empty configuration. **Expected result:** Entering event names in the funnel steps and clicking Calculate renders a funnel chart. The summary table shows the conversion rate at each step and the overall end-to-end conversion rate. ### 5. Build the cohort retention table Create a retention table that groups users by their signup week and shows what percentage returned in each subsequent week up to 8 weeks out. ``` Build a RetentionTable component at src/components/analytics/RetentionTable.tsx. Requirements: - Create a Postgres function get_retention(p_project_id uuid, p_weeks integer default 8) that: - Groups users by the week they first appeared (week 0 = signup cohort) - For each cohort week, counts users who fired any event in week 1, 2, 3... up to p_weeks - Returns rows: { cohort_week (date), cohort_size, week_1_pct, week_2_pct, ..., week_8_pct } - Build the UI: - Render an HTML table using shadcn/ui Table, TableHeader, TableBody, TableRow, TableCell - Header row: Cohort Week, Users, Week 1, Week 2, ... Week 8 - 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}) - The cohort_week column shows the week start date formatted as 'MMM d, yyyy' - Show a Skeleton while loading - Add a tooltip on each percentage cell showing the absolute user count ``` **Expected result:** A color-coded retention table appears showing each signup cohort as a row. Darker indigo cells indicate higher retention. Hovering shows the absolute user count. ## Complete code example File: `src/components/analytics/TrendChart.tsx` ```typescript import { useQuery } from '@tanstack/react-query' import { supabase } from '@/integrations/supabase/client' import { ComposedChart, Line, Bar, XAxis, YAxis, Tooltip, Legend, ResponsiveContainer, CartesianGrid } from 'recharts' import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card' import { Select, SelectTrigger, SelectValue, SelectContent, SelectItem } from '@/components/ui/select' import { Skeleton } from '@/components/ui/skeleton' import { useState } from 'react' import { format, subDays } from 'date-fns' type DauRow = { day: string; dau: number; event_count: number } export function TrendChart({ projectId }: { projectId: string }) { const [days, setDays] = useState(30) const { data = [], isLoading } = useQuery({ queryKey: ['dau', projectId, days], queryFn: async () => { const since = format(subDays(new Date(), days), 'yyyy-MM-dd') const { data, error } = await supabase .from('daily_active_users') .select('day, dau, event_count') .eq('project_id', projectId) .gte('day', since) .order('day', { ascending: true }) if (error) throw error return (data ?? []).map((r) => ({ ...r, day: format(new Date(r.day), 'MMM d') })) }, staleTime: 10 * 60_000, }) return ( Active Users & Events {isLoading ? : ( )} ) } ``` ## Common mistakes - **Querying raw events tables directly from the dashboard for aggregations** — The events table grows to millions of rows quickly. Running COUNT DISTINCT or GROUP BY on it without materialized views causes slow queries that degrade as data accumulates. Fix: Always read aggregated data from materialized views or Postgres functions. Query raw events only for debugging individual sessions. - **Putting the SUPABASE_SERVICE_ROLE_KEY in the tracking snippet JavaScript** — The snippet runs in the browser. Anyone who views the page source can read the service role key and make admin-level Supabase API calls. Fix: The ingest Edge Function uses the service role key server-side via Deno.env. The snippet only needs the public tracking_key — never a Supabase key. - **Refreshing materialized views on every dashboard load** — REFRESH MATERIALIZED VIEW locks the view during refresh. Calling it on every page load blocks dashboard queries and creates database lock contention. Fix: Refresh views on a schedule using Supabase pg_cron (every hour or every 15 minutes) rather than on demand from the dashboard. - **Not handling anonymous users before login** — Most users fire events before they sign up or log in. If you only track authenticated user_ids, you miss the top of the funnel and cannot compute correct conversion rates. Fix: Always generate and store an anonymous_id in localStorage on first visit. Link the anonymous_id to a user_id after login using an identity merge approach. ## Best practices - Use materialized views for all aggregations — never query the raw events table from the analytics dashboard. - Schedule materialized view refreshes with pg_cron rather than triggering them from the client. - Keep the tracking JavaScript snippet under 2KB — it loads on every page of the tracked application and should never affect page performance. - Store event properties as JSONB to allow flexible event schemas — different event types can have different property structures without schema migrations. - Add indexes on (project_id, event_name, created_at) and (project_id, anonymous_id, created_at) to support the most common analytics queries. - Always track both anonymous_id and user_id — anonymous tracking captures the full funnel including pre-signup behavior. - Enable RLS on events and sessions scoped by project_id so each project owner only sees their own tracking data. - Use CONCURRENTLY when refreshing large materialized views to avoid blocking read queries during refresh. ## Frequently asked questions ### How do I add the tracking snippet to my application? Deploy the snippet Edge Function to Supabase, then add a script tag to your HTML: a script tag pointing to your Supabase snippet Edge Function URL with your tracking key. After that, call window.analytics.track('button_clicked', { label: 'Sign Up' }) anywhere in your JavaScript. ### Does this track users across different browsers or devices? The tracking snippet uses localStorage for the anonymous_id, which is device and browser specific. Users are treated as separate anonymous identities on different devices unless they log in — at which point the user_id links their events across sessions. ### How often do the materialized views refresh? By default, materialized views do not refresh automatically. Set up a pg_cron job in Supabase to refresh them on a schedule. Go to Database → Extensions → Enable pg_cron, then add a job: SELECT cron.schedule('refresh-analytics', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_active_users'). ### Can I track events from a mobile app as well? Yes. The ingest Edge Function accepts standard HTTP POST requests from any client. Build a thin wrapper in your mobile app (React Native, Swift, etc.) that sends the same JSON payload structure. The anonymous_id should be the device ID or a UUID stored in device storage. ### How do I handle GDPR and user data deletion requests? Add a DELETE endpoint to your Edge Function that accepts a user_id or anonymous_id and removes all associated events and sessions rows. Log the deletion request in an audit table. For anonymization instead of deletion, update user_id to NULL and replace anonymous_id with a hash. ### The funnel is showing 100% at every step. Why? The funnel function checks if a user fired each event within the time window, but does not enforce ordering by default. If all your seed data users fired all events (because they were seeded together), every step shows 100%. Add ordering logic to the function: a user must have fired step N at any time before step N+1. ### Can I track server-side events as well as browser events? Yes. Call the ingest Edge Function from your server with the same JSON payload. Include the user_id (which you know server-side) and generate a session_id that represents the server-side operation. Mark these events with a source: 'server' property so you can distinguish them in queries. ### Can RapidDev help me customize the tracking for my specific application? Yes. RapidDev can help you design an event taxonomy, set up the materialized view refresh schedule, and build custom funnel and retention analyses tailored to your product's conversion flows. --- Source: https://www.rapidevelopers.com/how-to-build-lovable/product-analytics © RapidDev — https://www.rapidevelopers.com/how-to-build-lovable/product-analytics