Pre-IPL Metrics Spec

Requirement spec for all metrics to be tracked before IPL. Each metric is tagged:

• [LIVE] — Already instrumented, just needs dashboard/query
• [PARTIAL] — Event exists but missing fields or aggregation
• [NEW] — Needs new instrumentation

Data sources:

• MP = Mixpanel (frontend events, user-level analytics, funnels, cohorts)
• TS = TimescaleDB (backend events, time-series, Grafana dashboards)
• PG = Postgres (transactional data, point-in-time queries)

---

1. User Activity

#	Metric	Definition	Source	Status
1.1	DAU / WAU / MAU	Distinct user_id with any session_event per day/week/month	TS session_events	[LIVE] — daily_active_users aggregate exists. Add WAU/MAU rollups.
1.2	DTU / WTU / MTU	Distinct user_id with bet_placed event per day/week/month	TS bet_events	[LIVE] — "Active Bettors" stat exists. Add weekly/monthly.
1.3	D1, D7, W1, W2, W4, W8 Retention	% of users returning on day 1, 7, week 1, 2, 4, 8 after signup	MP cohort analysis	[PARTIAL] — user_signup + user_login events exist. Build MP retention report. No code change needed.
1.4	Desktop vs Mobile split	User-agent breakdown per session	MP (auto-captured)	[LIVE] — Mixpanel captures $device, $os, $browser automatically. Build report.
1.5	Desktop → Mobile PWA conversion	Users who first logged in on desktop, then on mobile PWA	MP	[PARTIAL] — Need to track platform property (web/pwa/mobile) on auth_success. Currently not distinguishing PWA from web.
1.6	Login method split	% by login provider (Google, email, wallet)	MP login_provider_selected + TS session_events.chain_type	[LIVE] — Both events exist. Build report.

Implementation needed:

• 1.1: Add weekly_active_users and monthly_active_users continuous aggregates in TimescaleDB
• 1.5: Add platform: 'web' | 'pwa' | 'mobile' property to auth_success Mixpanel event (detect via window.matchMedia('(display-mode: standalone)'))

---

2. Betting Volume & Behavior

#	Metric	Definition	Source	Status
2.1	Daily / Weekly / Monthly volume	Total stake placed per period	TS bet_stats_hourly	[LIVE] — Aggregate exists.
2.2	Bets per user (avg, p90, p95)	Distribution of bet count per user per day	TS bet_events	[LIVE] — Data exists. Add percentile query: percentile_cont(0.9) WITHIN GROUP (ORDER BY bet_count)
2.3	P&L per user (avg, p90, p95)	Distribution of net P&L per user per day	TS bet_events (settled)	[LIVE] — profit_loss field exists. Add percentile query.
2.4	Win/Loss → subsequent activity	After a win: does user bet more/less/higher stakes? Same for loss.	TS bet_events	[NEW] — Needs analytical query: window function over user's bet timeline, compare stake/frequency in 1h/24h after win vs loss. No new instrumentation — pure query work.
2.5	Sport / Match / Market split	% of bets by sport, by fixture, by market type	TS bet_events	[LIVE] — sport_id, fixture_id, market_name all captured.
2.6	Betfair vs Bifrost odds split	% of bets routed to each exchange	TS bet_events.routing_venue + exchange_events.exchange	[LIVE] — "Bets by Routing Venue" chart exists. Add Bifrost-specific filter.
2.7	Fancy market betting	% of bets on fancy markets (9.x Bifrost markets)	TS bet_events	[PARTIAL] — Need is_fancy boolean on bet_placed event. Currently inferred from market name.
2.8	Live vs Pre-match split	% of bets placed during live vs before match start	TS bet_events	[NEW] — Add `event_phase: 'prematch'
2.9	Market vs Limit orders	Bets at displayed odds vs changed odds (Betfair only)	TS bet_events	[NEW] — Add `order_type: 'market'

Implementation needed:

• 2.7: Add is_fancy field to bet_placed tsEvent metadata
• 2.8: Add event_phase field to bet_placed tsEvent. Derive from fixture status + match context at bet time.
• 2.9: Add order_type field. If user changed odds in betslip → limit, else market. Frontend sends this flag with order.

---

3. Onboarding & Conversion Funnels

#	Metric	Definition	Source	Status
3.1	Onboarding funnel	Landing → Sign up click → Auth success → First fixture view → First bet	MP	[LIVE] — All events exist: sign_in_clicked → auth_success → fixture_viewed → bet_placed. Build MP funnel.
3.2	Bet placement funnel	Fixture view → Market click → Bet added to slip → Bet placed → Bet accepted	MP + TS	[LIVE] — fixture_viewed → market_category_viewed → bet_added → bet_placed → bet_placement_success. Build MP funnel.
3.3	Event click-through	Which events get the most clicks from sports page	MP fixture_viewed	[LIVE] — fixture_viewed has entry_point. Group by fixture.
3.4	Betslip abandonment	Bets added but never placed (session-level)	MP	[PARTIAL] — Have bet_added and bet_placed. Calculate abandonment as sessions with bet_added but no bet_placed. Pure MP analysis.

Implementation needed:

• 3.1–3.4: No new instrumentation. Build Mixpanel funnels and reports.

---

4. Watchlist & Alerts

#	Metric	Definition	Source	Status
4.1	Users with ≥1 watchlisted match	Count of distinct users with active watchlist items	PG watchlist_items	[LIVE] — Query existing table.
4.2	Betting entry point split	% of bets originating from watchlist vs homepage vs match page	MP fixture_viewed.entry_point → bet_placed	[PARTIAL] — fixture_viewed has entry_point. Need to carry entry_point through to bet_placed event as betting_source.
4.3	Alert type split	% of alerts by type (price_movement, score, wicket, etc.)	PG watchlist_alerts	[LIVE] — Query alert configs.
4.4	Alert medium split	% by delivery channel (push, email, telegram)	PG watchlist_alerts	[LIVE] — deliverTo field exists on alerts.
4.5	Alert → Bet conversion	User receives alert → places bet on same fixture within 30 min	TS + PG	[NEW] — Join alert trigger timestamp with bet_events for same user+fixture within 30min window. Needs: add alert_triggered tsEvent when notification fires.

Implementation needed:

• 4.2: Propagate entry_point from fixture_viewed to bet_placed in Mixpanel (set as super property on fixture view, read on bet place)
• 4.5: Add alert_triggered tsEvent in notificationService.ts with user_id, fixture_id, alert_type, medium

---

5. AI Features

#	Metric	Definition	Source	Status
5.1	Chat questions asked	Total messages sent, unique users, messages/user	MP ai_chat_message_sent + ai_command_sent	[LIVE] — Both events exist with message_length.
5.2	Chat response quality	User satisfaction signal (thumbs up/down, follow-up rate)	MP	[NEW] — Add thumbs up/down UI + ai_response_rated event with `rating: 'positive'
5.3	Fixture analysis usage	How many users trigger AI analysis per fixture	MP ai_analysis_triggered	[LIVE] — Event exists with fixture context.
5.4	AI → Bet conversion	User views AI analysis → places bet on same fixture	MP ai_analysis_loaded → bet_placement_success	[PARTIAL] — bet_placement_success has influenced_by_ai flag. Verify it's being set correctly.
5.5	Chatbot response latency	p50, p90, p95 of response time	MP ai_chat_response_received.latency_ms	[LIVE] — Latency captured. Build percentile report.
5.6	Fixture analysis latency	p50, p90, p95 of analysis generation time	MP ai_analysis_loaded.latency_ms	[LIVE] — Latency captured. Build percentile report.
5.7	Top questions / topics	Cluster of most common chat queries	MP ai_chat_message_sent	[PARTIAL] — Messages tracked but no categorization. Can export and analyze offline. Or add simple intent classification server-side.

Implementation needed:

• 5.2: Add thumbs up/down to AI chat UI + ai_response_rated Mixpanel event
• 5.4: Audit influenced_by_ai flag — ensure it's set when user viewed AI analysis before betting

---

6. Backend / Technical Metrics

#	Metric	Definition	Source	Status
6.1	Login success / failure rate	% of auth attempts that succeed vs fail	TS session_events	[PARTIAL] — user_login logged on success. Need login_failed tsEvent on auth failure.
6.2	Betfair API metrics	Latency (p50/p95/p99), error rate, timeout rate	TS exchange_events	[LIVE] — exchange_fill has latency_ms. Add error/timeout events.
6.3	Bifrost API metrics	Same as Betfair	TS exchange_events	[PARTIAL] — Bifrost events exist but need latency_ms on all interactions (bet placement, settlement, market data).
6.4	Order placement success rate	By provider (Betfair Match Odds, Bookmaker, Fancy), filterable by sport/match	TS bet_events	[LIVE] — bet_placed, bet_accepted, bet_declined exist with routing_venue, sport_id. Acceptance rate chart exists. Add sport/match filter to Grafana.
6.5	Order placement latency	Time from user click to confirmed placement (p50/p90/p95)	MP bet_placement_success.latency_ms + TS	[LIVE] — Frontend latency in Mixpanel. Add backend-side latency to bet_placed tsEvent.
6.6	Order cancellation latency	Time from cancel request to confirmed cancel (Betfair only)	TS	[NEW] — Add cancellation_latency_ms to bet_cancelled tsEvent. Measure in orderSyncJob.ts.
6.7	Odds update latency	Time from exchange price change to frontend display	TS	[NEW] — Needs instrumentation: timestamp when price received from Betfair/Bifrost WebSocket vs when pushed to client WebSocket. Add odds_update_latency tsEvent.
6.8	Ball running latency	Time from ball bowled (Roanuz) to betslip lock / market suspension	TS	[NEW] — Add ball_event_latency tsEvent in ballByBallService.ts. Measure: Roanuz event timestamp vs market suspension timestamp.
6.9	Frontend validation exclusions	Bets blocked by frontend min/max limit checks (never reach backend)	MP	[NEW] — Add bet_validation_failed Mixpanel event with `reason: 'below_min'

Implementation needed:

• 6.1: Add login_failed tsEvent in auth route catch block
• 6.3: Add latency_ms to all Bifrost exchange events
• 6.6: Measure cancel round-trip time, add to bet_cancelled tsEvent
• 6.7: Add odds_update_latency tsEvent — timestamp diff between exchange receipt and WS push
• 6.8: Add ball_event_latency tsEvent — Roanuz event time vs market suspension time
• 6.9: Add bet_validation_failed Mixpanel event in BetSlipFooter when validation fails

---

7. Data Provider Metrics (Roanuz / AI)

#	Metric	Definition	Source	Status
7.1	Live feed uptime	% of time Roanuz WebSocket is connected during live matches	TS	[PARTIAL] — Reconnect events logged. Add data_feed_status tsEvent with connected/disconnected + uptime calc.
7.2	Ball-by-ball latency	Time from ball bowled to data received	TS	[NEW] — Compare ball_timestamp from Roanuz payload vs received_at. Add ball_data_latency tsEvent.
7.3	Scorecard update latency	Score, CRR, RRR, partnership update lag	TS	[NEW] — Similar to 7.2. Add scorecard_update_latency tsEvent in RoanuzDataAdapter.
7.4	Batsman / Bowler update latency	Time for player stat changes to propagate	TS	[NEW] — Same mechanism. Batch with 7.3 as data_update_latency tsEvent with type field.
7.5	Watchlist alert success rate	Alerts triggered vs alerts that should have triggered	TS + PG	[NEW] — Add alert_evaluation tsEvent: `{ result: 'triggered'
7.6	Alert delivery latency	Time from condition met to notification delivered	TS	[NEW] — Add alert_latency_ms to alert_triggered tsEvent (from 4.5). Measure: condition evaluation time → notification send time.
7.7	AI chatbot response latency (backend)	Server-side LLM response time	TS	[NEW] — Add ai_response tsEvent in llm_service.py with latency_ms, model, token_count.

Implementation needed:

• 7.1: Add data_feed_status tsEvent in RoanuzWebSocketManager on connect/disconnect
• 7.2–7.4: Add data_update_latency tsEvent in RoanuzDataAdapter with type discriminator. Requires Roanuz payload to include source timestamp (check if available).
• 7.5: Add alert_evaluation tsEvent in condition evaluator
• 7.6: Measure end-to-end alert latency in notification service
• 7.7: Add ai_response tsEvent in Python LLM service

---

Priority Matrix

P0 — Must have before IPL (core business visibility)

Metric	Work needed
DAU/WAU/MAU (1.1)	Add WAU/MAU aggregates — query only
DTU/WTU/MTU (1.2)	Same — query only
Retention D1/D7/W1-W8 (1.3)	Mixpanel report setup
Betting volume daily/weekly (2.1)	Already live
Sport/Match/Market split (2.5)	Already live — add Grafana filters
Live vs Pre-match split (2.8)	Add event_phase — ~2h backend
Betfair vs Bifrost split (2.6)	Already live
Onboarding funnel (3.1)	Mixpanel funnel setup
Bet placement funnel (3.2)	Mixpanel funnel setup
Order placement success rate (6.4)	Already live — add sport filter to Grafana
Order placement latency (6.5)	Already live in Mixpanel
Login success/failure (6.1)	Add login_failed — ~30min backend
Betfair/Bifrost API metrics (6.2, 6.3)	Enrich existing events — ~2h backend

P1 — High value, build during IPL week 1

Metric	Work needed
Bets per user percentiles (2.2)	Query only
P&L per user percentiles (2.3)	Query only
Fancy market split (2.7)	Add is_fancy — ~1h backend
Market vs Limit orders (2.9)	Add order_type — ~2h frontend + backend
Alert → Bet conversion (4.5)	Add alert_triggered tsEvent — ~2h backend
AI → Bet conversion (5.4)	Audit influenced_by_ai — ~1h
Ball running latency (6.8)	Add tsEvent — ~2h backend
Odds update latency (6.7)	Add tsEvent — ~3h backend
Frontend validation blocks (6.9)	Add MP event — ~1h frontend
Betting entry point split (4.2)	Propagate entry_point — ~1h frontend

P2 — Nice to have, build post-IPL week 1

Metric	Work needed
Win/Loss → subsequent activity (2.4)	Analytical query — complex but no instrumentation
Desktop vs Mobile (1.4)	Mixpanel report — auto-captured
Desktop → PWA conversion (1.5)	Add platform property — ~1h frontend
Login method split (1.6)	Report only
AI response quality (5.2)	Add thumbs UI — ~3h frontend
Roanuz latency metrics (7.2–7.4)	Add tsEvents — ~3h backend
Alert success rate (7.5)	Add tsEvent — ~2h backend
Alert latency (7.6)	Add tsEvent — ~1h backend
AI backend latency (7.7)	Add tsEvent in Python service — ~1h
Order cancellation latency (6.6)	Add to tsEvent — ~1h backend
Betslip abandonment (3.4)	Mixpanel analysis
Watchlist users count (4.1)	PG query
Alert type/medium split (4.3, 4.4)	PG query

---

New TimescaleDB Schema Additions

-- Add to existing bet_events
ALTER TABLE bet_events ADD COLUMN IF NOT EXISTS event_phase TEXT;        -- prematch|live|first_innings|second_innings|death_overs
ALTER TABLE bet_events ADD COLUMN IF NOT EXISTS is_fancy BOOLEAN;
ALTER TABLE bet_events ADD COLUMN IF NOT EXISTS order_type TEXT;         -- market|limit
ALTER TABLE bet_events ADD COLUMN IF NOT EXISTS cancellation_latency_ms INTEGER;

-- New: latency_events (umbrella table for all latency tracking)
CREATE TABLE IF NOT EXISTS latency_events (
  time           TIMESTAMPTZ NOT NULL,
  event_type     TEXT NOT NULL,          -- odds_update|ball_event|scorecard_update|alert_delivery|ai_response
  source         TEXT,                   -- betfair|bifrost|roanuz|notification|ai
  fixture_id     TEXT,
  latency_ms     INTEGER NOT NULL,
  metadata       JSONB DEFAULT '{}'
);
SELECT create_hypertable('latency_events', 'time', if_not_exists => TRUE);

-- New: alert_events
CREATE TABLE IF NOT EXISTS alert_events (
  time           TIMESTAMPTZ NOT NULL,
  event_type     TEXT NOT NULL,          -- alert_triggered|alert_suppressed|alert_evaluated
  user_id        TEXT,
  fixture_id     TEXT,
  alert_type     TEXT,                   -- price_movement|score|wicket|...
  medium         TEXT,                   -- push|email|telegram
  latency_ms     INTEGER,
  converted_to_bet BOOLEAN DEFAULT FALSE,
  metadata       JSONB DEFAULT '{}'
);
SELECT create_hypertable('alert_events', 'time', if_not_exists => TRUE);

New Mixpanel Events

Event	Properties	Where to add
bet_validation_failed	reason, stake, min_limit, max_limit, fixture_id	BetSlipFooter.tsx
ai_response_rated	`rating: positive	negative, conversation_id, message_id`

Mixpanel Properties to Add to Existing Events

Event	New property	Value
auth_success	platform	web / pwa / mobile
bet_placed	betting_source	Carried from fixture_viewed.entry_point
bet_placed	event_phase	prematch / live / first_innings etc.
bet_placed	order_type	market / limit

---

Grafana Dashboard Additions

Dashboard: "IPL Command Center" (New)

Single-pane view for IPL:

Row 1 — Live pulse:

• DAU (today), DTU (today), bets in last 1h, stake in last 1h

Row 2 — Volume:

• Bets over time (5min buckets, stacked by sport)
• Stake over time
• Live vs Pre-match split (pie)

Row 3 — Exchange health:

• Betfair placement latency (p50/p95 line chart)
• Bifrost placement latency (p50/p95)
• Acceptance rate (Betfair vs Bifrost)
• Odds update latency (p95)

Row 4 — Data feed:

• Ball-by-ball latency (p95)
• Roanuz connection status (up/down)
• Alert delivery latency (p95)

Row 5 — Errors:

• Login failures (count)
• Bet rejections (count by reason)
• Exchange disconnects (count)

---

Estimated Total Effort

Category	Effort
P0 backend instrumentation	~5h
P0 Mixpanel/Grafana setup (no code)	~4h
P1 backend instrumentation	~10h
P1 frontend instrumentation	~3h
P1 Grafana dashboards	~3h
P2 (all)	~15h
Total P0	~9h
Total P0 + P1	~25h