Implement Churn risk scoring analytics provider

[phertyameen]

Description

Add a provider that computes churn risk scoring: flag users whose activity frequency has dropped sharply vs. their historical baseline. Accept a DateRangeDto and optional granularity query, and return a typed AnalyticsMetricResult. Query against pre-aggregated tables where one exists rather than scanning raw AnalyticsEvent rows.

Why this matters

Churn risk scoring is one of the core metrics product/growth needs surfaced on the analytics dashboard; without a dedicated provider it can't be exposed via an API endpoint.

Files to touch

• backend/src/analytics/providers/get-churn-risk.provider.ts
• backend/src/analytics/providers/get-churn-risk.provider.spec.ts

Acceptance Criteria

• Provider returns correct results against seeded test data
• Handles empty-range/no-data case without throwing
• Query is index-backed (no full table scan on AnalyticsEvent)
• Unit tests cover at least 3 scenarios (empty, single day, multi-day range)

Notes

This issue is part of the new analytics module effort (tracking, aggregation,
and reporting on player/game activity). Please keep new code inside
backend/src/analytics/ following the same module/controller/service/provider
layout used by existing modules (e.g. progress, streak, quests).

[storm-beyndtech]

Hi @phertyameen. I read through the analytics module before writing this, so apologies for the length.

The thing I keep running into: churn risk is a per-user metric, but the only pre-aggregated
table in the module is cohort-level. retention_cohorts stores cohortSize and
retainedDay1/7/30 with no userId (retention-cohort.entity.ts:20-39), so it can't
produce per-user flags no matter how it's queried. The fallback is raw analytics_events,
and that entity has no indexes at all — no @Index on userId, timestamp, or eventType.
Every other module in the repo indexes this shape (progress.entity.ts:15 has
['userId','attemptedAt'], which is almost exactly what churn needs). Analytics is the one
that doesn't.

So "query pre-aggregated tables where one exists" and "no full table scan on AnalyticsEvent"
can't both be satisfied with what's in the repo right now. Something new has to land: either
an index on analytics_events, or a per-user activity rollup analogous to retention_cohorts
but keyed by user. I'd want your call on which before I start, since the second one is a new
entity plus an aggregation job and that's well outside the two files listed.

A few other things I noticed while reading:

The taxonomy and the schema disagree. EVENT_TAXONOMY.md documents every event as
eventName + metadata, but the entity columns are eventType + payload
(analytics-event.entity.ts:9,18). The doc's own "Adding New Events" example at line 156
calls track({ eventName, userId, metadata }) — TypeORM's create() silently drops keys
that aren't columns, so following the doc writes a row with no event type and no payload.
This matters here more than usual: churn scoring is entirely a question of which events count
as activity, and the document that defines them doesn't match the columns.

The track endpoint can't write. entityId is non-nullable (analytics-event.entity.ts:14-15)
and TrackEventDto has no entityId field, while userId is @IsOptional() against another
non-nullable column. There's no migration for analytics_events in src/database/migrations/,
so the table comes from synchronize and those are real NOT NULLs. Related: TrackEventProvider
buffers in a module-local array and onModuleDestroy clears the interval without flushing
(track-event.provider.ts:46-48), so a deploy drops whatever is in the buffer. On failure it
re-queues and rethrows inside a setInterval callback (line 41), where nothing catches the
rejection. For churn specifically this is load-bearing — the loss is biased toward the most
recent events, which is the exact window that separates "this user went quiet" from "we lost
their writes." A user whose last few days got dropped scores as high risk.

Granularity. It's accepted and echoed but never groups anything — the retention provider
destructures it at line 28 and passes it straight into the response at line 55. Worth deciding
which one you want here, because it isn't cosmetic for churn: a weekly baseline and a daily
baseline flag genuinely different users.

Unbounded ranges. The validator caps ranges at a year, but only when both bounds are
present — if (start && !end) return true (date-range.validator.ts:13-15). Providers then
default a missing start to new Date(0) (get-retention-curve.provider.ts:33,
get-onboarding-funnel.provider.ts:23). Against an unindexed analytics_events that's a scan
of the entire table, which is the thing the third criterion is trying to prevent.

Return shape. AnalyticsMetricResult.data is typed RetentionDataPoint[]
(analytics-metric-result.dto.ts:31), so "returns a typed AnalyticsMetricResult" needs either
a generic parameter or a second data-point type. I'd rather match whichever you prefer than
pick one. Same question for the endpoint — GetRetentionCurveProvider is registered in the
module but not wired into the controller, so there's currently no retention route. Should churn
ship with its controller route or match that precedent?

On the scoring itself: the hard part isn't the drop calculation, it's that a naive
frequency-ratio threshold flags the wrong people. A user doing 40 puzzles a day who drops to 8
reads as an 80% collapse; a user doing one a day who drops to zero is the actual churner. The
discriminator is the drop relative to that user's own variance, not the raw ratio. New users
also have no baseline at all, so it needs a minimum-history gate or every signup flags on day
two. I'd return null for insufficient baseline rather than 0, matching how cohortSize === 0
already returns null instead of dividing (get-retention-curve.provider.ts:61-64) — otherwise
the dashboard reads "0% risk" for users you know nothing about.

I build on-chain forensics, and my current project profiles token deployers by their pre-launch
funding graphs — same shape of problem, scoring an entity against a behavioral baseline. I also
rebuilt a risk classifier from scratch after the first version scored 0% F1, which is where the
variance-not-ratio lesson came from the expensive way.

If useful, I can open a first PR with just the index migration and the provider skeleton
against the raw table, so you can sanity-check the data-source decision before I build the
scoring out.

[grantfox-oss]

🦊 GrantFox — @storm-beyndtech has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #521)
2. Your PR will be reviewed by the MindBlockLabs maintainers

Good luck! Track your progress on GrantFox.