Analytics Architecture

Analytics Architecture

Self-Hosted Analytics (No Cookie Consent Needed)

Use Umami or Plausible — lightweight, privacy-focused, no cookies.

• Track: page views, referrers, countries, devices, custom events
• No GDPR cookie banner needed (no cookies = no consent required)
• Deploy on same VPS or separate instance

Event Tracking Schema

CREATE TABLE analytics_events (
  id uuid DEFAULT gen_random_uuid(),
  user_id uuid,
  event_type text NOT NULL,
  properties jsonb DEFAULT '{}',
  created_at timestamptz DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE analytics_events_2026_03 PARTITION OF analytics_events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Index for common queries
CREATE INDEX idx_events_type_date ON analytics_events (event_type, created_at);
CREATE INDEX idx_events_user ON analytics_events (user_id, created_at);

Key Metrics Per Product

Arena

OUTBOUND

MathMind

Funnel Analysis

-- Arena conversion funnel
WITH funnel AS (
  SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'page_view' AND properties->>'page' = '/') as visited,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'signup') as signed_up,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'agent_connected') as connected,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'challenge_entered') as entered,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'challenge_completed') as completed
  FROM analytics_events
  WHERE created_at > now() - interval '30 days'
)
SELECT
  visited,
  signed_up, ROUND(signed_up::numeric / visited * 100, 1) as signup_rate,
  connected, ROUND(connected::numeric / signed_up * 100, 1) as connect_rate,
  entered, ROUND(entered::numeric / connected * 100, 1) as entry_rate,
  completed, ROUND(completed::numeric / entered * 100, 1) as completion_rate
FROM funnel;

Cohort Analysis

-- Week-over-week retention by signup cohort
WITH cohorts AS (
  SELECT user_id, date_trunc('week', created_at) as cohort_week
  FROM auth.users
),
activity AS (
  SELECT user_id, date_trunc('week', created_at) as active_week
  FROM analytics_events WHERE event_type = 'challenge_entered'
)
SELECT
  c.cohort_week,
  COUNT(DISTINCT c.user_id) as cohort_size,
  COUNT(DISTINCT a1.user_id) FILTER (WHERE a1.active_week = c.cohort_week + interval '1 week') as week_1,
  COUNT(DISTINCT a1.user_id) FILTER (WHERE a1.active_week = c.cohort_week + interval '4 weeks') as week_4
FROM cohorts c
LEFT JOIN activity a1 ON c.user_id = a1.user_id
GROUP BY c.cohort_week ORDER BY c.cohort_week;

Sources

• Umami analytics documentation
• PostHog product analytics patterns
• Amplitude cohort analysis methodology

Changelog

• 2026-03-21: Initial skill — analytics architecture