Stats Aggregation

Conversion totals are the numbers people argue about. A report that sums the raw conversion table every time it opens gets slower as the account grows, and a report that sums a cached copy keeps the old total after a conversion is corrected, deleted or counted twice. Routy solves both with one table.

Every conversion is written to a staging table as it arrives. A background job sweeps that table every minute and merges what it finds into a daily fact table, one row per account, conversion date, click and currency, with one numeric column per conversion type. Reports read that table instead of recomputing from the conversion log, and join it to the click when they need the campaign, the traffic source, the device or the country.

Then, at 02:00 every day, a second job rebuilds the last seven days of that table from the conversion records themselves. It recomputes each conversion type's total for every click in the window, writes what it gets, and deletes rollup rows that no longer have a conversion behind them. That last step is the one to notice: a conversion you removed yesterday leaves the rollup too, rather than sitting there as a total nobody can account for.

This isn't a screen. There's no stats aggregation page, nothing to switch on and no job to schedule. What you can choose, since June 2026, is how a report aggregates each of its columns.

• Conversion totals that reconcile. The nightly pass reads conversions.conversions for the window, recomputes every conversion type's sum per click, and makes the rollup match. A figure in a report can be traced back to the conversions that produced it.
• A merge every minute, so a conversion is normally in aggregated reports within a minute or two of arriving. The merge takes up to 50,000 staged rows per pass and only one pass runs at a time, so a burst drains over several passes rather than piling into one long query.
• No double counting on a retried postback. A postback event id is recorded once in reports.processed_postback_events, so the same postback arriving twice applies once.
• New conversion types without a schema change. The fact table is created and widened at runtime, with a column per conversion type plus five spare, so defining a type doesn't wait on a migration.
• A choice of aggregation per report column. A fact column declares which of Sum, Avg, Min, Max and Count it supports, and you pick one per column per run. Facts default to Sum, which is what every report did before this existed.
• A single summary row when you ask for facts and no dimension at all, instead of an error.
• Zero instead of a blank, where zero is the honest answer. A Sum or a Count with nothing behind it comes back as 0. An Avg, Min or Max with nothing behind it stays null, because a missing average isn't zero and showing it as one would be a wrong number rather than an empty cell.
• Months kept rather than rolled off. The fact table is partitioned by month on the conversion date and prior months stay in place, so a late conversion is still written into the month it belongs to.

Two limits worth knowing. The rollup's day boundary is UTC, so a conversion just after midnight in your time zone can fall on the previous rollup day even though your report columns render in your own time zone. And the nightly rebuild covers the last seven days, which is the window corrections arrive in; a correction to a conversion from last quarter reaches the rollup on the next merge, not through a reconcile.

The three tables

reports.conversions_fact_stage takes conversions as they arrive. It's unlogged, because losing it would cost nothing: the nightly rebuild can recreate anything that was in it.

reports.conversions_fact_daily is what reports read. Its key is your account, the conversion date, the click and the currency, and the currency is held in a generated column that stores an empty string when a conversion has no currency, so a conversion without one can still be part of the key rather than being dropped. The value columns are ct_<type id>_value, nullable to keep the table small, and a read treats null as zero.

reports.conversions_fact_rollup_state holds the last merge time and how many rows it moved, which with a count of the staging table is how we tell whether the merge is keeping up.

Why the dimensions aren't in the rollup

The rollup stores one row per click, not a pre-computed cross-tab. When a report asks for conversions by traffic source by day, it joins the rollup to clicks.affiliate_clicks on the click id and groups by what it finds there: the campaign, the dynamic parameter, the external click id, the landing URL, the referrer, the device, the browser, the operating system and the country.

That's the trade. A cross-tab per dimension combination would be faster for the combinations it covered and useless for the ones it didn't, and the set of things people group by keeps growing. Keeping the grain at the click means every dimension the click carries is available to group by, and the sums are already done.

Choosing an aggregation

GET /v1/report-builder/{report}/metadata returns each column with its supportedAggregations. Facts usually offer all five; dimensions offer Count or nothing.

When you run the report, each entry in columns can be a plain column id, which takes that column's default, or an object giving the column id and the aggregation you want: { "columnId": "revenue", "aggregation": "Avg" }. The two forms can be mixed in one request, so a client that was sending plain ids keeps working untouched. An aggregation the column doesn't support is refused with a 400 that names the column and lists what it does support, rather than being silently swapped for a sum. Sending rawMode: true turns aggregation off and returns the rows as they are.

Which engine answers

Every report family can read from Postgres or from ClickHouse, decided per request from configuration rather than by a deploy. Postgres is the default. The knob exists per family, per role and per account, so a family can be moved over on its own and one account can be moved back without touching anyone else. The clicks path to ClickHouse is written and tested and currently switched off.

The reason to pre-aggregate is obvious and the reason to reconcile isn't, so the second one is the point. Conversions change after they're recorded. A network restates a month, a duplicate postback gets cleaned up, a conversion is voided. A cache that only ever adds will carry that stale total until someone notices, and the person who notices is usually the one being paid against it. Rebuilding the recent window from the source, deletions included, is what lets a report quote the rollup instead of recounting the conversion log.

The staging table is the other half. Conversions arrive in bursts, and a postback that retries sends the same conversion again. Writing arrivals straight into a shared total means the burst competes with every report running at the time, and the retry is counted twice. Staging the arrivals and merging them on a timer keeps both away from the table reports are reading.

Picking an aggregation decides whether a column means anything. Summing is right for commission and wrong for a rate: a conversion rate summed across thirty days is a number with no meaning, and before mid-2026 that was the only thing a report could do with it. Choosing Avg per column is what makes a rate column usable, and keeping Sum as the default is what stopped every existing report changing its answer the day it shipped.