One Dataset, Three Feeds, and a 6% Revenue Overstatement
A specialty retailer sends web data from a production report suite (9.4M events/day) and mobile app data from a second report suite (2.1M events/day) into Customer Journey Analytics through the Adobe Analytics source connector. A nightly SFTP drop adds CRM order records (about 180K rows) at 02:00 UTC, but each file contains the prior three days of orders because the CRM vendor reprocesses refunds. The analytics team built one dataset with all three sources and now sees order revenue roughly 6% higher than the finance ledger, plus a spike of 'unknown' channel rows every morning. The CTO wants a single cross-device view of customer value by end of quarter, and finance refuses to accept any number that cannot be reconciled to the ledger within 1%. Late data is a known constraint: the web SDK can deliver events up to 32 hours late, and the CRM file is immutable once delivered.
1. The revenue overstatement and the daily 'unknown' channel spikes are both symptoms of the same connection design. Explain the likely causes and how you would confirm each before changing anything.
Read the worked answer
Two defects share one root cause: the CRM feed is being modelled as a second purchase event source instead of as enrichment of a purchase event that already exists.
Duplicates. The vendor file is cumulative over three days. If the connection ingests the whole file nightly with a fresh ingest timestamp, CJA's event-level deduplication (person ID + event timestamp) cannot collapse rows, because each copy of an order arrives with a different timestamp. Confirm by filtering raw CRM rows to one order ID across a five-day window and counting occurrences; then check whether the connection exposes a deduplication key or a derived field that could produce one.
Unknown channel. CRM rows carry no marketing channel, so any union with web/app events yields a null channel that the data view surfaces as 'unknown'. Confirm with a breakdown of channel by source or feed name - do not infer it from the daily total.
Revenue. If CRM order events and web purchase events coexist, every order that also fires a web purchase is counted twice. That alone is consistent with a 5–8% overstatement.
Prove the row counts before touching the data view; otherwise you will fix the display and leave the data wrong.
2. Recommend a target dataset architecture: how many datasets, which are event versus lookup, and how identity is resolved. Justify the design against the 30-day reprocessing window and 12M rows/day volume.
Read the worked answer
Design for two different questions: digital behaviour and commercial truth.
Web and app stay separate event datasets inside one connection, each retaining its report-suite semantics. Native timestamps are preserved and the 32-hour SDK lag sits comfortably inside tolerance.
CRM becomes a lookup dataset keyed on order ID. Order attributes - amount, tax, refund status, currency, order status - enrich the existing purchase event rather than adding a second revenue event. This removes double counting and lets a single row carry both channel and commercial value.
CRM also becomes a small event dataset for orders with no digital purchase (phone, field sales). Without this, the ledger will never reconcile. The event timestamp must be the original order time, never the file time, so a reprocessed file resolves to the same row rather than a new one.
Identity: pass CRM ID when known and ECID otherwise, and publish the stitch rate as a standing quality metric rather than burying it.
Volume is not the constraint at 12M rows/day; deduplication logic and timestamp correctness are.
3. Finance wants reconciliation within 1%. What validation would you run, and what would you tell them about residual variance?
Read the worked answer
Reconciliation is a definition exercise before it is a data exercise.
- Fix the grain first. Reconcile on distinct order ID count by day, not row count. Row counts will always differ because of refunds and vendor reprocessing.
- Build a side-by-side table: date | CRM order count | CJA distinct order count | delta | CRM revenue | CJA revenue | delta. Apply the same order-status definition to both sides.
- Expect three residual sources: FX (the data view converts at a daily rate; finance may book at month-end rate), refund timing (the vendor reprocesses three days later), and orders with no digital touch at all.
- Set expectations honestly: order count should tie within 0.1% once deduplicated; revenue can legitimately carry 0.5–1.5% variance from FX rate dating and refund timing unless both sides adopt identical conventions.
- Run two weeks in parallel before the CFO dashboard switches over, and publish 'Revenue' as one governed calculated metric with the definition written down.
The 1% target is achievable - but only for a metric whose definition has been agreed, not for whichever number each team happens to export.