Skip to main content

Reporting

Everything downstream of ingestion.

Read path​

UI request
  → resolveQueryScope(user)   ← attaches MarketingFilter (client_id[])
  → buildWhere(scope, filters)
  → ClickHouse query against the relevant latest-state/view or ledger path
  → typed rows back to Next.js RSC

Rollups​

mv_ad_daily_rollup is a paid-acquisition reporting view. Since migration 058 it reads through v_paid_acquisition_latest; commerce, attribution, metadata, and sidecar queries use their own maintained paths. Its paid grain uses account_id and segment_key.

There is nothing to rebuild

mv_ad_daily_rollup is a plain VIEW. It stores no independent rows, so there is no rebuild step. Verify its definition and the latest-state source instead.

Do not drop it and re-create it as a MATERIALIZED VIEW … POPULATE. That is the retired migration-016 shape: an incremental trigger that summed another copy into storage on every INSERT into a ReplacingMergeTree source. It inflated production spend 8×. See ClickHouse rollups for the incident and for the read-only verifier that replaced the rebuild script.

The reporting surface (UI)​

Primary components — anti-drift references:

  • PerformanceReport — the main reporting surface and tab strip; its table uses MERGED_COLUMNS.
  • PlatformSourceDashboard — landing page for a single Ad Account (≡ Source).

Client and account controls are scope-dependent. Server-side resolveQueryScope remains authoritative even when a selector or client column is intentionally absent.

Attribution​

Immutable landing data (marketing_sales) + a rewritable attribution layer. One effective-dated fee pipeline resolves to daily grain. Attribution overrides are versioned Supabase migrations (see attribution_overrides in Marketing & Attribution); rolling back a rule change is a git revert — the rollup is a read-time view, so it reflects the reverted rules on the next query with no rebuild step.

Revenue semantics on the paid-acquisition page​

The page deliberately carries three non-interchangeable revenue surfaces: ad-platform revenue, paid-attributed CRM revenue, and full-ledger classified commerce revenue. Commerce Initial Rev $ and Commerce Upsell Rev $ come from normalized marketing_sales.transaction_class values at client grain; they are not derived from platform revenue. Deeper rows show unavailable until a connector has a verified sale-to-paid-entity association. See Sales transaction normalization & upsell classification.

Shopify/Zipify lifecycle reporting uses Shopify order lines plus targeted ORDER_EDIT sales-agreement enrichment for OCU-tagged orders. The commerce upsell take rate counts unique initial checkouts that accepted at least one upsell; multiple accepted offers on the same checkout contribute one take, while all exact upsell-line revenue remains in the ledger. Zipify's native offer conversion rate uses offer views as its denominator and therefore is a separate metric requiring the OCU Statistics export.

Query performance guardrails​

  • The ClickHouse client request timeout is currently 120 seconds; this is an upper bound, not a performance target.
  • Run test:isolation and the focused marketing query/table tests for query changes.
  • Use existing batched hierarchy/query helpers and inspect query count and scanned grain when adding drill paths.
  • The repository does not currently provide universal EXPLAIN-in-CI, a dedicated reporting replica contract, or an N+1 lint rule. Treat those as potential improvements, not shipped guarantees.