Analytics

SELECT SpanName, uniqExact(TraceId) as sessions FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanName IN ('rage_click','dead_click','excessive_scroll','quick_back') AND Timestamp >= now() - INTERVAL 7 DAY GROUP BY SpanName

SELECT SpanName, uniqExact(TraceId) as sessions FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND Timestamp >= now() - INTERVAL 7 DAY AND SpanName NOT LIKE 'page.%' AND SpanName NOT IN ('dead_click','rage_click','quick_back','excessive_scroll') GROUP BY SpanName ORDER BY sessions DESC LIMIT 10

SELECT level, count() as cnt FROM ( SELECT TraceId, windowFunnel(2592000)(Timestamp, SpanName = 'order.created', SpanName = 'inspection.scheduled', SpanName = 'report.submitted', SpanName = 'review.completed', SpanName = 'order.closed' ) as level FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND Timestamp >= now() - INTERVAL 30 DAY GROUP BY TraceId ) GROUP BY level ORDER BY level

WITH funnel AS ( SELECT TraceId, windowFunnel(2592000)(Timestamp, SpanName = 'order.created', SpanName = 'order.closed' ) >= 2 AS converted FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND Timestamp >= now() - INTERVAL 30 DAY GROUP BY TraceId ), all_events AS ( SELECT TraceId, SpanName FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND Timestamp >= now() - INTERVAL 30 DAY ) SELECT e.SpanName as event_name, countIf(f.converted) as completed, countIf(NOT f.converted) as dropped, round((countIf(f.converted) / greatest(sum(toUInt32(f.converted)), 1)) / greatest(countIf(NOT f.converted) / greatest(sum(toUInt32(NOT f.converted)), 1), 0.001), 1) as likelihood_ratio FROM all_events e JOIN funnel f ON e.TraceId = f.TraceId GROUP BY e.SpanName HAVING completed + dropped > 5 ORDER BY likelihood_ratio DESC

WITH cohorts AS ( SELECT SpanAttributes['operator.id'] as operator, toStartOfWeek(min(Timestamp)) as cohort_week FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 60 DAY GROUP BY operator ), activity AS ( SELECT SpanAttributes['operator.id'] as operator, toStartOfWeek(Timestamp) as activity_week FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 60 DAY GROUP BY operator, activity_week ) SELECT c.cohort_week, count(DISTINCT c.operator) as cohort_size, dateDiff('week', c.cohort_week, a.activity_week) as weeks_since, count(DISTINCT a.operator) as active_count, round(count(DISTINCT a.operator) / count(DISTINCT c.operator) * 100, 0) as retention_pct FROM cohorts c LEFT JOIN activity a ON c.operator = a.operator AND a.activity_week >= c.cohort_week GROUP BY c.cohort_week, weeks_since ORDER BY c.cohort_week, weeks_since

WITH actors AS ( SELECT DISTINCT SpanAttributes['operator.id'] as operator FROM otel_traces WHERE SpanName = '{selected_event}' AND Timestamp >= now() - INTERVAL 60 DAY ), weekly AS ( SELECT SpanAttributes['operator.id'] as operator, toStartOfWeek(Timestamp) as week FROM otel_traces WHERE SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 60 DAY GROUP BY operator, week ), first_seen AS ( SELECT operator, min(week) as cohort_week FROM weekly GROUP BY operator ) SELECT dateDiff('week', f.cohort_week, w.week) as weeks_since, countIf(a.operator IS NOT NULL) as cohort_active, countIf(a.operator IS NULL) as control_active, count() as total FROM weekly w JOIN first_seen f ON w.operator = f.operator LEFT JOIN actors a ON w.operator = a.operator WHERE w.week >= f.cohort_week GROUP BY weeks_since ORDER BY weeks_since

SELECT toDate(Timestamp) as day, uniqExact(ResourceAttributes['rum.sessionId']) as sessions FROM hyperdx_sessions WHERE Timestamp >= now() - INTERVAL 30 DAY GROUP BY day ORDER BY day

WITH weekly AS ( SELECT SpanAttributes['operator.id'] as operator, toStartOfWeek(Timestamp) as week FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 60 DAY GROUP BY operator, week ), first_seen AS ( SELECT operator, min(week) as first_week FROM weekly GROUP BY operator ), classified AS ( SELECT w.week, w.operator, CASE WHEN f.first_week = w.week THEN 'new' WHEN prev.operator IS NOT NULL THEN 'returning' ELSE 'resurrecting' END as lifecycle FROM weekly w JOIN first_seen f ON w.operator = f.operator LEFT JOIN weekly prev ON w.operator = prev.operator AND prev.week = w.week - INTERVAL 1 WEEK ) SELECT week, lifecycle, count(DISTINCT operator) as cnt FROM classified GROUP BY week, lifecycle ORDER BY week, lifecycle

SELECT active_days, count() as operator_count FROM ( SELECT SpanAttributes['operator.id'] as operator, uniqExact(toDate(Timestamp)) as active_days FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 30 DAY GROUP BY operator ) GROUP BY active_days ORDER BY active_days

SELECT SpanAttributes['operator.id'] as operator, SpanName as feature, count() as usage FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND SpanAttributes['operator.id'] != '' AND Timestamp >= now() - INTERVAL 30 DAY GROUP BY operator, feature ORDER BY operator, usage DESC

SELECT StatusMessage, count() as cnt FROM otel_traces WHERE ServiceName = 'clickstack-portal' AND StatusCode = 'ERROR' AND Timestamp >= now() - INTERVAL 7 DAY GROUP BY StatusMessage ORDER BY cnt DESC LIMIT 10