The referral and reward analytics headers

Both admin tables carry an analytics panel behind an Analytics tab, next to Overview at the top of the screen. They are not decoration: the Rewards panel leads with Unpaid Liability, which is the largest number this addon produces and the one an operator ends up putting in front of an accountant.

Screen	Path	Model	Permission
Affiliate Referrals	/admin/affiliate/referral	mlmReferral	access.affiliate.referral
Referral Rewards	/admin/affiliate/reward	mlmReferralReward	access.affiliate.reward

The timeframe control, and the cards that ignore it

A timeframe selector sits above both panels: 1 Year (the default), 6 Months, 3 Months, 30 Days, 7 Days, 24 Hours.

Some cards ignore it entirely. A card declared as a snapshot drops the date window and reads the whole table as it stands right now — because a stock is not a flow. "How many affiliates exist" and "how much do we owe" do not belong to a calendar bucket, and summing per-bucket values would answer a different question wrongly.

Which cards are which is called out in the tables below. It is the first thing to check when a figure "does not match" the timeframe you selected.

The Referrals panel

Six cards, a status donut, an inflow chart and a ranked bar.

Card	Reads	Windowed?
Active Affiliates	COUNT(DISTINCT referrerId) — the size of the salesforce, not the number of referrals	Snapshot
Total Referrals	COUNT(id) — lifetime, matching the affiliate dashboard	Snapshot
Referrals per Affiliate	Total Referrals ÷ Active Affiliates	Snapshot ÷ snapshot
Referrals This Period	COUNT(id) inside the selected timeframe	Windowed
Activation Rate	ACTIVE ÷ all referrals over the window, as a percentage	Windowed
Pending Longer Than 7 Days	COUNT(id) where status = 'PENDING' and createdAt < NOW() - INTERVAL 7 DAY	Snapshot

Referrals per Affiliate is the concentration figure, and it is the reason the panel exists. Four hundred referrals from 380 people and 400 referrals from 12 people are the same row count, a completely different business and a completely different fraud posture. Near 1.0 means the programme never compounds. Above about 5 means a handful of affiliates carry it and their churn is existential.

A mean does not tell you which of those two you have — the Top Referrers ranked bar underneath does. It groups by referrerId over the whole table (ignoring the timeframe, because a salesforce is a stock), takes the top 10 by referral count, and resolves each bar's label to the affiliate's email address rather than the stored UUID. It is email and not a name because the label resolver looks for a single human-readable column on the joined model and the user record has none — it stores firstName and lastName separately, so the resolver falls through to email.

The other two charts are Status Distribution — the complete PENDING / ACTIVE / REJECTED split as a donut — and Referrals Over Time, a stacked area of the same three states, so volume and mix read in one glance.

Pending Longer Than 7 Days is your approval SLA

This card replaced a flat pending count, and the age on it is the whole point. A backlog number with no age is not a work list.

The undifferentiated pending total is still available — it is the PENDING slice of the donut beside it.

The Rewards panel

Ten cards across two groups, a claim donut, a liability breakdown, an accrual-versus-payout chart and a ranked earner list.

Group 1 — what we owe, how old it is, how much clears

Card	Reads	Windowed?
Unpaid Liability	SUM(reward) where isClaimed = false, converted to USD	Snapshot
Unclaimed Over 90 Days	The same sum, restricted to createdAt < NOW() - INTERVAL 90 DAY	Snapshot
Oldest Unclaimed Reward	MAX(TIMESTAMPDIFF(HOUR, createdAt, NOW())) over unclaimed rows, rendered as an elapsed time	Snapshot
Paid Out	SUM(reward) where isClaimed = true, converted to USD	Windowed
Total Reward Amount	SUM(reward) over everything, converted to USD	Windowed
Claim Rate by Value	Paid Out ÷ Total Reward Amount	Windowed

Unpaid Liability is read as a live stock, not a windowed figure. Money owed does not belong to a calendar bucket: it is what you owe right now, whether it was accrued this week or last year. Changing the timeframe does not change it, and that is correct.

Claim Rate by Value, not by count, because a 90% claim rate on rows hides the case where the three largest rewards are the unclaimed ones. The Reward Claim Distribution donut beside it is by row count — the two disagreeing is itself the signal that a few large rewards are sitting uncollected.

Group 2 — size, outliers, and where the obligation sits

Card	Reads	Windowed?
Average Reward Size	AVG(reward) — see the currency warning below	Windowed
Largest Single Reward	MAX(reward) — see the currency warning below	Windowed
Affiliates Earning	COUNT(DISTINCT referrerId) on the reward table	Snapshot
Accrued, Unclaimed	SUM(reward) where isClaimed = false, converted to USD, inside the window	Windowed

Accrued, Unclaimed is deliberately the window figure, unlike the lifetime obligation in group 1: it is new liability booked in this period that has not been claimed. It is also the second band of the Rewards Over Time chart below, so Paid Out plus Accrued, Unclaimed sum to everything accrued in the period. That chart is the one that answers "is the unpaid pile growing faster than payouts clear it".

Largest Single Reward is the cheapest guard there is against a mis-set PERCENTAGE condition: a runaway rule shows up there long before it moves any total.

Top Earners by Lifetime Reward ranks the top 10 by summed reward across the whole table. Concentration is the signal — a single affiliate holding most of the payout is a self-referral ring long before it is a star performer. Like the other ranked bar, the axis carries email addresses, not UUIDs.

The currency problem, and how each card handles it

mlm_referral_reward.reward carries no currency on its own row. The denomination lives on the joined mlm_referral_condition.rewardCurrency. A programme running one condition paying USDT and another paying NGN therefore has two units in one column, and adding them is meaningless.

Every SUM on the KPI cards declares inUSD: "condition.rewardCurrency". The engine follows that one association hop, groups the sum by the joined currency, prices each denomination at its own rate, and folds the results into a single USD figure. That covers Unpaid Liability, Unclaimed Over 90 Days, Paid Out, Total Reward Amount and Accrued, Unclaimed.

The two bar charts do not. Neither Unpaid Liability by Condition nor Top Earners carries inUSD, so both sum the raw column. For the by-condition bar that is deliberate and harmless — a bucket is one condition and therefore one currency. For Top Earners it is not: see the warning above.

Two cards deliberately carry no currency symbol at all: Average Reward Size and Largest Single Reward. Conversion denominates a total; converting the members of an average and then averaging the results answers a different question, so the engine refuses inUSD on anything but a sum. One 200-NGN reward and one 200-USDT reward really do average to 200 of nothing. A MAX across denominations is worse — it is simply whichever currency has the smallest unit. Read both as shape, not as money.

Why there is a bar chart of liability by condition

The Unpaid Liability by Condition chart sums unclaimed reward value grouped by conditionId, top 8, over the whole table.

Grouping by condition is the only way to get buckets that are single-currency by construction — one condition has exactly one rewardCurrency — so each bar is honest without any conversion at all. It is kept alongside the converted KPI because it answers a different question: where the obligation sits, before it is folded into one number. Concentration, not total.

Its bars are labelled with the condition's system name — WELCOME_BONUS, FIRST_DEPOSIT_BONUS — not the friendly title you edit on the conditions screen, and not its currency. The label resolver takes the first human-readable column it finds on the joined model, and name sits ahead of title in that order, so on a condition carrying both the machine name always wins. If you run conditions in more than one currency, the chart cannot tell you which bar is in which; the conditions screen can.

The broken-claim-flow detector

Unclaimed Over 90 Days and Oldest Unclaimed Reward are a pair, and they are the most useful two cards on the panel.

An obligation nobody has collected for three months is one of three things:

1. A dead account. The member has left. The money stays on your books forever; nothing expires it.
2. A stranded earner. Their unclaimed balance is below the Payout Threshold, so the Claim button refuses. There is no partial payout and no sweep. See Programme settings.
3. A claim flow that is failing. The payout is erroring for everyone — a wallet that cannot be created, a KYC feature gate, a wallet type that is disabled — and nobody has told you.

The third is the one worth checking first, because it is the one that is actively broken. The distinguishing question is whether anything has been claimed recently: if Paid Out over 30 days is zero while Accrued, Unclaimed keeps rising, it is not 400 patient affiliates.

Note that neither card is a claim SLA in the strict sense. There is no claimedAt column on the reward table — only updatedAt, which any later edit dirties — so a true time-to-claim cannot be measured, and it is deliberately not shown rather than shown wrongly.

How this differs from the affiliate dashboard

/admin/affiliate — the dashboard covered in Running the programme — answers different questions from these panels, and mixing them up produces figures that look like they disagree.

Question	Screen
How big is the programme, and is it growing month on month?	The dashboard's four headline metrics
Who is in the approval queue, and how long have they waited?	Referrals → Analytics
Is the salesforce broad or concentrated?	Referrals → Analytics (Referrals per Affiliate + Top Referrers)
How much do we owe, right now, in one currency?	Rewards → Analytics (Unpaid Liability)
Is the claim flow working?	Rewards → Analytics (the 90-day pair)
Which affiliates have stalled?	The dashboard's stalled-affiliate block
Which conditions carry the obligation?	Rewards → Analytics (Unpaid Liability by Condition)

The one genuine overlap is liability. The dashboard's pending queue reports an unclaimed reward count and total; the Rewards panel reports the same obligation converted into USD. When your conditions pay in more than one currency the dashboard drops the currency symbol rather than adding denominations together, and the analytics figure is the one to quote — it is the only place the programme's obligation is expressed as a single comparable number.

Related: Running the programme for the actions behind these tables, Investigating one affiliate for the per-affiliate view, and The hourly reward evaluator for what creates the rows these panels count.