Why conversion values change after the fact
ROAS reporting assumes conversion value is final at the moment it is recorded. In reality, value often moves. Orders are discounted after checkout. Partial refunds arrive days later. Subscriptions churn and trigger prorated credits. Revenue gets restated because the source system corrected tax, shipping, or currency.
If you simply overwrite historical conversion value, your ROAS time series will “rewrite history.” If you never update it, you’ll overstate performance and misallocate budget. The goal is to reflect reality without making every past dashboard look different each morning.
The three failure modes that ruin ROAS trends
1) Backfilled overwrites
Many connectors and ad platforms update historical conversions when value changes. If your pipeline ingests “latest state only,” yesterday’s report changes. That creates stakeholder distrust: “Which number was true?”
2) Mixed accounting models
Marketing teams often want cash-like net revenue. Finance may restate gross revenue then apply refunds later. If some channels are net and others are gross, blended ROAS becomes meaningless.
3) Uncontrolled latency windows
Discounts and refunds have a time lag. If one system updates after 2 days and another after 14, you’ll compare apples to oranges unless you set a consistent “finalization” rule.
Pick a contract for “value truth” before building fixes
You need an explicit metric contract that answers two questions:
- Which value definition? Gross (before discounts/refunds), net (after), or both.
- Which time axis? When the conversion happened, or when the adjustment happened.
Most teams end up with two parallel views:
- Booked ROAS: value attributed to the original conversion date (stable cohort view).
- Recognized ROAS: value recognized on the date adjustments occur (cashflow/ops view).
Trying to force a single number to satisfy both is what causes endless reconciliation debates.
Model adjustments as events, not edits
The most robust approach is event sourcing for value changes. Instead of overwriting the original conversion row, store a ledger of changes and derive the metric you need.
Recommended tables (conceptually)
- Conversions: immutable facts (conversion_id, timestamp, campaign/ad metadata, original_value).
- Value adjustments: one row per change (conversion_id, adjustment_timestamp, adjustment_amount, reason).
Reasons typically include discount applied, partial refund, full refund, chargeback, tax correction, currency restatement, or subscription proration.
Why this works
- You can recompute net value at any time without losing auditability.
- You can produce a stable time series by freezing a reporting window.
- You can explain deltas: “ROAS dropped because $18,240 in refunds posted this week for last month’s cohort.”
Two ways to keep time series stable
Option A: Finalization window (rolling lock)
Set a rule like: “Conversion value is allowed to change for 14 days after conversion date; after that, lock the cohort.” Your daily dashboard uses locked cohorts for historical periods and only allows movement in the most recent 14 days.
This produces stable trends while still capturing most adjustments. Choose the window based on observed refund/discount latency (e.g., 7, 14, or 30 days). If your business has long return windows, consider a longer lock for specific product lines.
Option B: Dual reporting (booked vs recognized)
Show two charts side by side:
- Booked: net value applied back to the original conversion date (cohort performance).
- Recognized: adjustments shown when they occur (operational reality).
This avoids the philosophical fight about “the one true ROAS” and reduces pressure to mutate history.
How to treat common scenarios
Restated revenue
If the source system corrects the revenue amount (e.g., tax/shipping correction, FX conversion fix), log the delta as an adjustment event rather than replacing the original. If you must accept overwrites from a source, store both previous and current values so you can reconstruct deltas.
Discounts applied after purchase
Post-purchase discounts are common with customer service concessions or retroactive coupons. Treat them as negative adjustments with a reason code of “discount.” Decide whether discounts belong in ROAS (most teams include them in net revenue to avoid overstating).
Partial refunds
Partial refunds are where naive approaches break. If you only have order-level totals, you can’t reliably map which ad-driven item was refunded. The best you can do is tie refunds to the conversion_id (order) and accept that item-level attribution is out of scope unless you ingest line items.
Store partial refunds as multiple adjustment rows if they happen in installments. This improves traceability and matches real payment flows.
Attribution and channel reconciliation rules
Even with perfect value modeling, ad platforms may not match your warehouse because:
- Platforms use different attribution windows and models.
- Some platforms report conversion value in account currency, others in transaction currency.
- Refunds may not be supported natively as negative conversions.
To keep ROAS comparable cross-channel, normalize the definition in your own dataset, then treat platform-reported ROAS as a diagnostic, not the source of truth.
Operationalizing this in a marketing data pipeline
To implement a ledger model consistently across ad, analytics, and CRM sources, you need three things: reliable IDs, refresh logic, and transformation governance.
- ID strategy: persist conversion_id/order_id and map it to click IDs when available. If you only have session-based identifiers, document the limitations.
- Incremental refresh: re-pull recent periods where changes are expected (your finalization window). Avoid full historical reloads unless you are intentionally restating history.
- Standard transformations: naming harmonization, currency conversion, and KPI calculations should be centrally defined, not rebuilt per dashboard.
This is the type of workflow Funnel.io is built to support: collecting performance and revenue signals from multiple systems, standardizing them, and delivering an analysis-ready dataset where adjustments and definitions can be governed once and reused everywhere.
What to document so stakeholders trust the numbers
- Value definition: gross vs net, and what is included (tax, shipping, discounts, refunds).
- Lock policy: the exact number of days before cohorts finalize.
- Adjustment taxonomy: reason codes and examples.
- Known gaps: where platforms can’t ingest negatives, missing line items, or unmatched IDs.
If you already have a process for turning messy requests into clear agreements, borrowing the idea of an issue intake contract for a prioritized backlog helps: define what changes are allowed, who approves, and when the pipeline is considered correct.
Practical implementation checklist
- Create an immutable conversions table and a value-adjustments table.
- Choose a finalization window (start with 14 days, adjust based on refund latency).
- Publish both booked and recognized ROAS if stakeholders need stability and cash realism.
- Normalize currency and naming before ROAS calculations.
- Monitor adjustment volume by reason to detect operational issues (spikes in refunds or retroactive discounts).
Handled this way, value changes stop being a reporting crisis. They become a measurable, explainable signal that improves budget decisions without erasing the past.
