title: User Usage Analytics status: active last-updated: 2026-06-11 authors: [@amalkrsihna] audience: [product, ops, analytics] abstract: > SQL + KQL queries to measure user activity, signups, trade volume, retention, and conversion against current prod data. No third-party analytics tool — built from PG + LAW only. Each query is read-only and safe to run in prod. companion: [incident-debugging, prod-snapshot-2026-06-11]

User Usage Analytics¶

Companion: incident-debugging, prod-snapshot-2026-06-11.

Every query in this doc is read-only. Run during business hours; complex aggregates may briefly raise PG CPU. None mutate data.

0. Why this document exists¶

There is no Mixpanel / Amplitude / Google Analytics wired up today. Product + ops still need to answer: - How many users signed up this week? - DAU / WAU / MAU? - How many trades per match? - Which players are most-traded? - Funnel: signup → first trade → repeat trader. - Churn signals.

Everything below is built from data already in PG + LAW. Run them in psql $PROD_DATABASE_URL for SQL or the Log Analytics workspace blade for KQL.

1.1 Signups per day, last 14 days¶

SELECT date_trunc('day', created_at) AS day, COUNT(*) AS signups
FROM users
WHERE created_at > NOW() - INTERVAL '14 days'
GROUP BY 1
ORDER BY 1 DESC;

WITH first_trade AS (
  SELECT user_id, MIN(opened_at_ms) AS first_open_ms
  FROM cfd_positions
  GROUP BY user_id
)
SELECT
  COUNT(DISTINCT u.id) AS signups,
  COUNT(DISTINCT f.user_id) AS traded_at_least_once,
  ROUND(100.0 * COUNT(DISTINCT f.user_id) / NULLIF(COUNT(DISTINCT u.id), 0), 2) AS pct_converted
FROM users u
LEFT JOIN first_trade f ON f.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '14 days';

WITH ft AS (
  SELECT user_id, MIN(opened_at_ms) AS first_open_ms FROM cfd_positions GROUP BY user_id
)
SELECT
  date_trunc('week', u.created_at) AS signup_week,
  COUNT(*) AS users_in_cohort,
  ROUND(AVG(EXTRACT(epoch FROM (to_timestamp(ft.first_open_ms/1000) - u.created_at))/60), 1) AS avg_minutes_to_first_trade,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(epoch FROM (to_timestamp(ft.first_open_ms/1000) - u.created_at))/60) AS median_minutes
FROM users u
JOIN ft ON ft.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1 DESC;

1.4 Signup source (if `referral_code` is populated)¶

SELECT
  CASE
    WHEN referral_code IS NULL OR referral_code = '' THEN 'organic'
    ELSE 'referred'
  END AS source,
  COUNT(*) AS signups
FROM users
WHERE created_at > NOW() - INTERVAL '14 days'
GROUP BY 1;

2. Activity metrics — DAU, WAU, MAU¶

2.1 DAU (last 14 days)¶

SELECT date_trunc('day', to_timestamp(opened_at_ms/1000)) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM cfd_positions
WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '14 days') * 1000
GROUP BY 1
ORDER BY 1 DESC;

2.2 WAU (last 8 weeks)¶

SELECT date_trunc('week', to_timestamp(opened_at_ms/1000)) AS week,
       COUNT(DISTINCT user_id) AS wau
FROM cfd_positions
WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '8 weeks') * 1000
GROUP BY 1
ORDER BY 1 DESC;

2.3 MAU (last 6 months)¶

SELECT date_trunc('month', to_timestamp(opened_at_ms/1000)) AS month,
       COUNT(DISTINCT user_id) AS mau
FROM cfd_positions
WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '6 months') * 1000
GROUP BY 1
ORDER BY 1 DESC;

2.4 Stickiness ratio (DAU / MAU)¶

WITH dau AS (
  SELECT COUNT(DISTINCT user_id) AS n
  FROM cfd_positions
  WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '1 day') * 1000
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS n
  FROM cfd_positions
  WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '30 days') * 1000
)
SELECT dau.n AS today_dau, mau.n AS mau, ROUND(100.0 * dau.n / NULLIF(mau.n, 0), 2) AS stickiness_pct
FROM dau, mau;

Industry benchmark for "good" trading game stickiness: 20–30 %. WC26 month should spike to 40 %+.

3. Trade volume + behaviour¶

3.1 Trades per fixture¶

SELECT m.id AS fixture_id, m.home_team, m.away_team, m.starting_at,
       COUNT(p.id) AS trades_opened,
       SUM(p.lot_size) AS total_lots
FROM matches m
LEFT JOIN cfd_positions p ON p.instrument_id IN (
  SELECT id FROM instruments WHERE club IN (m.home_team, m.away_team)
)
  AND p.opened_at_ms BETWEEN extract(epoch from m.starting_at) * 1000
                          AND extract(epoch from m.starting_at + INTERVAL '2 hours') * 1000
WHERE m.starting_at > NOW() - INTERVAL '7 days'
GROUP BY 1, 2, 3, 4
ORDER BY trades_opened DESC;

3.2 Most-traded players (last 24 h)¶

SELECT i.name, i.club, i.position, COUNT(p.id) AS trades, SUM(p.lot_size) AS total_lots
FROM cfd_positions p
JOIN instruments i ON i.id = p.instrument_id
WHERE p.opened_at_ms > extract(epoch from NOW() - INTERVAL '24 hours') * 1000
GROUP BY i.id, i.name, i.club, i.position
ORDER BY trades DESC
LIMIT 20;

3.3 Average trade duration¶

SELECT
  COUNT(*) AS closed_trades,
  ROUND(AVG((closed_at_ms - opened_at_ms) / 1000.0 / 60), 1) AS avg_minutes_held,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (closed_at_ms - opened_at_ms) / 1000.0 / 60) AS median_minutes_held,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY (closed_at_ms - opened_at_ms) / 1000.0 / 60) AS p95_minutes_held
FROM cfd_positions
WHERE closed_at_ms IS NOT NULL
  AND opened_at_ms > extract(epoch from NOW() - INTERVAL '7 days') * 1000;

3.4 P&L distribution¶

SELECT
  COUNT(*) FILTER (WHERE realized_pnl > 0) AS winners,
  COUNT(*) FILTER (WHERE realized_pnl < 0) AS losers,
  COUNT(*) FILTER (WHERE realized_pnl = 0) AS even,
  ROUND(AVG(realized_pnl), 2) AS avg_pnl,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY realized_pnl) AS median_pnl
FROM cfd_positions
WHERE closed_at_ms IS NOT NULL
  AND opened_at_ms > extract(epoch from NOW() - INTERVAL '7 days') * 1000;

3.5 Long vs short split¶

SELECT direction, COUNT(*) AS trades, SUM(lot_size) AS total_lots
FROM cfd_positions
WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '24 hours') * 1000
GROUP BY direction;

3.6 Close-reason mix¶

SELECT close_reason, COUNT(*) AS trades, ROUND(AVG(realized_pnl), 2) AS avg_pnl
FROM cfd_positions
WHERE closed_at_ms IS NOT NULL
  AND opened_at_ms > extract(epoch from NOW() - INTERVAL '7 days') * 1000
GROUP BY 1
ORDER BY 2 DESC;

user / stop_loss / take_profit / washout / auto_exit_ft — distribution shows how often users close themselves vs the system closes them. A spike in washout is a leading indicator of bad margin management.

4. Retention cohorts¶

4.1 Weekly cohort retention (D1, D7, D14, D28)¶

WITH cohorts AS (
  SELECT id AS user_id, date_trunc('week', created_at) AS cohort_week
  FROM users
  WHERE created_at > NOW() - INTERVAL '8 weeks'
),
activity AS (
  SELECT DISTINCT user_id, date_trunc('day', to_timestamp(opened_at_ms/1000)) AS active_day
  FROM cfd_positions
)
SELECT
  c.cohort_week,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_week + INTERVAL '1 day' THEN c.user_id END) AS d1_active,
  COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_week + INTERVAL '7 days' THEN c.user_id END) AS d7_active,
  COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_week + INTERVAL '14 days' THEN c.user_id END) AS d14_active,
  COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_week + INTERVAL '28 days' THEN c.user_id END) AS d28_active
FROM cohorts c
LEFT JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week DESC;

4.2 Users who signed up but never traded (last 7 days)¶

SELECT u.id, u.email, u.created_at,
       EXTRACT(epoch FROM (NOW() - u.created_at))/3600 AS hours_since_signup
FROM users u
LEFT JOIN cfd_positions p ON p.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '7 days'
  AND p.user_id IS NULL
ORDER BY u.created_at DESC
LIMIT 100;

Trigger: drop email after 24 h with a tutorial nudge.

4.3 Power users (top 50 by trade count, last 30 days)¶

SELECT user_id, COUNT(*) AS trades, SUM(lot_size) AS total_lots,
       ROUND(SUM(COALESCE(realized_pnl, 0)), 2) AS total_pnl
FROM cfd_positions
WHERE opened_at_ms > extract(epoch from NOW() - INTERVAL '30 days') * 1000
GROUP BY user_id
ORDER BY trades DESC
LIMIT 50;

5. Leaderboard + ranks engagement¶

5.1 Distribution of leaderboard positions¶

-- This data lives in Redis as a ZSET; rebuild in PG from underlying state
SELECT
  COUNT(*) FILTER (WHERE w.balance > 11000) AS up,
  COUNT(*) FILTER (WHERE w.balance BETWEEN 9000 AND 11000) AS flat,
  COUNT(*) FILTER (WHERE w.balance < 9000) AS down
FROM wallets w
WHERE w.updated_at > NOW() - INTERVAL '1 day';

5.2 District / friends usage (if implemented)¶

Check districts table population and friendships (if exists) for usage rates.

6. WS / real-time engagement¶

6.1 Concurrent WS connections over time (from ws-server stdout)¶

ContainerAppConsoleLogs_CL
| where TimeGenerated > ago(7d)
| where ContainerAppName_s == 'ftl-prd-ws'
| where Log_s contains 'connections_total='
| extend conns = toint(extract(@'connections_total=(\d+)', 1, Log_s))
| summarize avg_conns = avg(conns), max_conns = max(conns) by bin(TimeGenerated, 1h)
| order by TimeGenerated desc

This requires the ws-server to emit a connections_total=N log line periodically. If absent, the /health endpoint reports current connections; scrape it on a cron.

6.2 Match-event engagement spikes¶

ContainerAppConsoleLogs_CL
| where TimeGenerated > ago(2h)
| where ContainerAppName_s == 'ftl-prd-api'
| where Log_s contains 'goal' or Log_s contains 'event_bump'
| summarize event_count = count() by bin(TimeGenerated, 1m)
| order by TimeGenerated desc

A goal at minute X should produce a spike at X+10 s (next poll), then ripple/decay over ~30 s with the no-decay sampler.

7. Frontend-side analytics (limited today)¶

Without page-level instrumentation in the React SPA, frontend analytics rely on:

Signal	Source	Limitation
Sessions	`POST /api/auth/google` count	Conflates login + token refresh
Page hits	None	Add path-level slog from a middleware to derive
Errors	Sentry / similar (not wired)	NONE captured today
Click depth	None	NONE captured

Recommendation: wire AppInsights JavaScript SDK from the React entry (src/main.tsx) so the existing ftl-prd-ai-cin workspace receives PageView + Exception telemetry. Coverage: ~3 hours of work.

8. Dashboard suggestions¶

Compose these queries into the following dashboards (Azure Workbook or any BI tool that can run PG + KQL):

Tournament Pulse — DAU, WAU, trades per fixture, concurrent WS connections, leaderboard activity.
Funnel — signups, % to first trade, time-to-first-trade, retention by cohort.
Health — CPU/memory per service, error rate, request p95, PG connection pool depth.
Cost — daily LAW GB, daily Container Apps cost, daily PG cost (mirrors §13 of cost-estimate-audit-2026-06-11).

9. Risk + privacy notes¶

All queries in §1–§5 reference user_id and email. If exporting to BI tools, redact emails (SELECT id, ... FROM users — drop email).
Position-level data is sensitive (financial). Limit who has direct PG read access in prod. Today: only the operator + on-call backend dev.
KQL queries on Log Analytics are billed as ingested data — they're free to run but the data they query is billed. Sampler (§14 of cost report) reduces this; live debug queries on Live Metrics are free.
Never run UPDATE / DELETE / TRUNCATE from a query you cut-pasted from this page. The doc is read-only. To mutate, write a proper migration through the standard release flow.