Cross-platform purchase-data integration is the work of reconciling records from e-commerce platforms, applications, stores, marketplaces, payment services, subscription systems and operational tools so that commercial activity is represented consistently.
The question it answers is narrow and worth stating plainly:
What happened in the transaction, and how should the records describing it be reconciled?
That is a different question from identity resolution. A transaction pipeline may consume a customer or account reference, but it should never invent person-level links. Where customer linkage is required, use documented identity-resolution confidence and keep the transactions that remain anonymous or unresolved rather than discarding them.
Why One Purchase Table Is Rarely Enough
A purchase is not a single event.
An order may be placed, authorised, partially fulfilled, amended, cancelled, returned and refunded. A payment may fail or be reversed without cancelling the order. A return may arrive before the refund is issued. An exchange combines a return, a new fulfilment and a price adjustment. A subscription generates repeated billing, service and cancellation events across months.
Collapse that into one row and every important question becomes ambiguous. Does the row represent order placement, payment, shipment or revenue recognition? Does its value include tax and delivery? Has a later refund been deducted? Is a cancelled line still counted? Which exchange item owns the discount? Was the marketplace the merchant or an intermediary? Is the customer known, anonymous or linked probabilistically?
A trustworthy model preserves the business objects and their relationships first, then produces simplified analytical views from them. Doing it the other way round is how organisations end up with three revenue numbers and no way to reconcile them.
- Model the lifecycle, not the sale Represent orders, line items, payments, shipments, returns, refunds, cancellations and exchanges as related objects. The single purchase row is an output, never the source of truth.
- Keep provenance for everything Preserve native source identifiers, ingestion references and transformation versions so any figure can be traced, reproduced and restated when a correction arrives.
- Standardise time, currency and product carefully Separate event, effective and ingestion time. Never treat a local amount as a reporting amount. Map source SKUs to a canonical product master with valid-from and valid-to dates.
- Reconcile rather than assume Orders, payments and fulfilment answer different questions and will not agree. Build explicit exception states, then age and own them.
- Accept identity, do not recreate it Store the entity reference, type, confidence band and identity version supplied by the identity system. Preserve anonymous and unlinked transactions as first-class records.
A Canonical Transaction Model
A practical canonical model should cover at least the following objects.
| Object | Purpose | Important attributes |
|---|---|---|
| Order | Commercial request or commitment | Order ID, status, channel, customer reference, order time, source |
| Line item | Product or service requested | Line ID, order ID, product ID, quantity, unit price, discount, tax, status |
| Payment | Movement or attempted movement of funds | Payment ID, amount, currency, method category, authorisation, capture, settlement |
| Shipment | Fulfilment movement | Shipment ID, line references, location, carrier, dispatch and delivery times |
| Discount | Reduction applied under a rule or promotion | Discount ID, scope, amount, allocation method, promotion reference |
| Tax | Tax amount or treatment | Jurisdiction, category, rate, inclusive or exclusive indicator, amount |
| Refund | Return of money or payment adjustment | Refund ID, original payment reference, amount, reason, time, status |
| Return | Physical or service return process | Return ID, original line reference, quantity, condition, receipt time, reason |
| Cancellation | Termination before or during fulfilment | Cancellation ID, affected order or line, quantity, reason, effective time |
| Exchange | Replacement or substitution relationship | Original line, replacement line, value adjustment, linked return and refund |
| Subscription event | Recurring commercial lifecycle event | Subscription ID, event type, service period, billing reference, renewal state |
| Customer reference | Link to the identity system | Source reference, entity type, confidence class, identity version, restrictions |
| Product reference | Link to a product master | Source SKU, canonical product ID, variant, bundle or service code |
| Channel | Origin or commercial context | Store, web, application, marketplace, contact centre, partner |
| Location | Store, warehouse, fulfilment or merchant location | Source code, canonical location ID, jurisdiction |
| Currency | Monetary denomination | Transaction currency, reporting currency, conversion metadata |
| Timestamp | Event and processing chronology | Event, effective, ingestion and update times, with timezone |
| Source identifier | Reproducible reference to origin | System, tenant, record type, native ID, source version |
Do not force every source system to behave identically. The canonical model should preserve meaningful source differences while standardising the concepts analysis actually needs.
Preserve Source Identifiers and Provenance
Never discard the native identifiers operational systems supply. Store a composite source key covering the source system, the merchant or tenant, the record type, the native record ID and the source event version.
Each canonical record should also retain the ingestion batch or message ID, the source timestamp, the transformation version, the reconciliation status, any correction or supersession relationship, and the data-quality exceptions raised against it.
Provenance is what lets you explain where a figure came from, reproduce a published output and work out which downstream products need restating when something changes. A general framework exists for representing the entities, activities and agents involved in producing information (1), though it is a provenance vocabulary rather than a retail transaction schema, so it informs the design without supplying it.
Standardise Time Without Losing Source Meaning
Store timestamps in an unambiguous interchange representation together with the source timezone or offset. The relevant international standard for date and time representation remains current, with a 2022 amendment, although the standards body has marked it for future revision (2).
Then separate the five kinds of time that get conflated. Event time is when the commercial event happened. Effective time is when it should affect the business state. Source-record time is when the source system recorded it. Ingestion time is when the integration platform received it. Processing time is when the analytical product incorporated it.
Do not replace local store times with a single universal time without retaining the original offset. Trading day, payroll, promotion and reporting boundaries usually depend on local time, and a shop that trades until midnight does not care what time it was in another country.
Use explicit reporting cut-offs. A cancellation received on Monday against an order that appeared in Friday’s report requires a restatement, not a silent overwrite.
Standardise Currency and Tax Treatment
Every monetary field should identify its currency, using the international currency code standard, which remains current following confirmation in 2021 (3).
Keep separate fields for the amount in transaction currency, the reporting-currency amount, the exchange rate, the rate source, the conversion timestamp or accounting period, and any rounding adjustment. Do not treat a local amount as though it were already converted. That single error has produced more unexplained variances than any other in my experience of these pipelines.
Tax should stay separately represented wherever the source supports it. Document explicitly whether analytical measures include or exclude tax, include import duties, treat shipping as revenue or pass-through, allocate tax and discounts to line items, and restate tax after partial returns.
The integration layer implements definitions approved by finance and tax specialists. It does not invent accounting policy, and an analyst who quietly decides how to allocate a discount has just written accounting policy.
Map Products, Variants, Bundles and Merchants
The same product may appear as an internal SKU, a marketplace listing ID, a supplier code, a barcode, an application product ID and a discontinued historical code.
Maintain a temporal crosswalk from source identifiers to the canonical product master, including valid-from and valid-to dates, variant and pack size, bundle composition, merchant or seller, mapping status, evidence, steward and confidence where the mapping is not exact.
A marketplace order may require separate identification of the marketplace, the seller or merchant of record, the fulfiller, the brand, the canonical product and the listing. Do not infer that similarly named products are identical without a verified mapping rule.
Shared identification and event standards exist for cross-enterprise visibility and for product and location keys (4)(5). They can inform interoperability but they do not replace an organisation’s own order, payment or accounting model, and adopting them does not solve source-product mapping automatically.
Preserve bundle identity and composition separately. If a bundle is overwritten by its component products, or mapped to a single SKU, the organisation cannot distinguish bundle performance from component performance and cannot reproduce the original transaction.
Detect Duplicate Feeds
Duplicates enter whenever several systems report the same business event. A point-of-sale platform and an enterprise order system both publish the sale. A marketplace API and a settlement file both carry the order value. Retries create repeated webhook events. A historical backfill overlaps an existing incremental feed. A payment gateway emits authorisation, capture and settlement records that get treated as three separate sales.
Deduplicate through a hierarchy: exact source event ID and version first, then a stable business reference, then a declared relationship between events, then controlled composite keys, and only then probabilistic duplicate detection.
Do not deduplicate on customer, amount and timestamp alone. Two legitimate transactions can share all three, and the customer who bought the same coffee twice in one morning is not a data-quality problem.
Retain the duplicate candidates and the rule used to suppress or consolidate them, so the decision can be audited and reversed.
Reconcile Orders, Payments and Fulfilment
Orders, payments and shipments answer different questions. An order says what was requested. A payment says what was authorised, captured, settled, failed or reversed. A shipment says what was fulfilled.
Build explicit reconciliation states: ordered but unpaid, authorised but not captured, paid but unfulfilled, partially fulfilled, overpaid or underpaid, fulfilled but later returned, cancelled after authorisation, refunded without a recorded return, marketplace settlement pending, and unresolved exception.
Do not require the three amounts to be equal. Partial fulfilment, tips, tax changes, substitutions, shipping charges, gift cards and split tenders all produce legitimate differences. Exceptions should be explainable, aged and owned by somebody named.
Process Returns, Refunds and Cancellations
A return is not automatically a refund, and a refund does not always follow a physical return.
The awkward cases are the normal ones: a goodwill refund with no return, a return received while the refund is still pending, a partial refund for damaged goods, a cancellation before capture, a chargeback after fulfilment, store credit instead of cash, and a refund issued in a different reporting period from the sale.
Preserve the relationship to the original order, line and payment. Allocate partial amounts using an explicit written policy. Never delete the original sale because it was later reversed.
Regulatory guidance on accuracy recognises that a record may legitimately preserve both an original event and its later cancellation or refund, provided the record as a whole is not misleading about the final position (6). Deleting history to make a table tidy is how a system becomes misleading.
Model Exchanges Explicitly
An exchange usually combines the return or cancellation of the original item, receipt or acceptance of that item, creation or fulfilment of a replacement, an additional payment or credit for the difference, and often a transfer of discount and tax.
Represent that relationship rather than overwriting the original line with the replacement product. Overwrite it and your product demand, return rate, discounting and margin analysis all quietly become wrong, in a way nobody notices until somebody asks why a discontinued item is still selling.
Model Subscriptions as Events
A subscription is not one indefinitely open purchase. Represent the start, trial, renewal, invoice, payment attempt, successful collection, service period, upgrade or downgrade, pause, cancellation request, cancellation effective date, refund or credit, and reactivation.
Separate billing status from service status. A failed payment does not necessarily end service immediately, and a cancellation request often takes effect at the end of a paid period. A model that fuses the two will misstate both churn and revenue.
Subscription events must remain distinct because billing, service access and customer status can change at different times. Flattening them into a single revenue line prevents accurate analysis of renewal, failed payment, pause, cancellation and reactivation.
Handle Late-Arriving Data and Restatements
Transaction systems revise themselves after you have reported on them. Settlement confirmations, returns, chargebacks, marketplace fees, tax adjustments, subscription cancellations, product-master corrections and customer-link corrections all arrive late.
Set an explicit policy covering open reporting windows, restatement thresholds, immutable published snapshots, current-state views, event-effective history and downstream notification.
Every dashboard should disclose the time through which its data is complete and how it treats later events. A number without that disclosure is not wrong so much as unfalsifiable.
Link Customers Only at Justified Confidence
The purchase-data system accepts identity decisions. It does not make them.
A customer-reference record should hold the source customer or account ID, the resolved entity reference where one exists, the entity type, the resolution method class, the confidence band, effective dates, the identity model or graph version, and the permitted purposes and restrictions.
Use the organisation’s governed customer and account matching methods rather than reimplementing them in the pipeline, where they will drift.
Preserve anonymous transactions, guest checkouts, transactions associated only with an account, household-scoped activity and records below the person-link threshold. Do not assign a household purchase to one person to improve profile coverage, because coverage is not the objective and that particular shortcut is how somebody receives a recommendation based on a relative’s shopping.
A corrected identity link should normally create a new version or relationship rather than rewrite the transaction.
Define Commercial Measures Precisely
Use named measures instead of an ambiguous purchase value: ordered gross value, fulfilled gross value, captured payment, settled payment, gross merchandise value, gross sales, discounts, returns, refunds, net sales, tax, delivery revenue, marketplace commission, payment fee, cost of goods and contribution.
Each measure needs a formula, the event states it includes, its tax treatment, its currency convention, its cut-off policy, its return and cancellation policy, a source authority, an owner and a version.
The accounting framework for revenue from contracts with customers has been mandatory for applicable annual periods since 1 January 2018 (7), and the standard setter completed its post-implementation review in September 2024, concluding that it was working as intended (8). It governs financial reporting rather than marketing metrics, and it does not define a warehouse schema, but it is the reason marketing measures should reconcile to finance-approved totals rather than competing with them.
Revenue, sales, bookings and gross merchandise value are not synonyms. Treating them as interchangeable in a deck is how a marketing team loses an argument with finance permanently.
Validate Against Finance and Operations
Reconcile at several levels: order count and value by source, payment captures and settlements, tax totals, shipment and fulfilment quantities, return and refund totals, marketplace payouts and commissions, store trading totals, subscription billing, and finance-approved control totals.
Differences should carry documented reasons: timing, excluded tax, currency conversion, pending settlement, partial fulfilment, gift cards, chargebacks, data cut-off or a known source outage.
A successful load is not evidence that the data is complete or economically correct. It is evidence that a file parsed.
Create Versioned Analytical Data Products
Do not expose every analyst to raw operational tables. Build governed products for defined purposes: order lifecycle, line-item sales, payment and settlement, returns and refunds, subscription revenue, product performance, customer purchase history, and channel and location performance.
Each product should declare its grain, owner, approved use, source coverage, refresh schedule, quality status, identity threshold, measure definitions, version, restatement policy and known limitations.
A customer purchase-history product should never imply that every transaction has a verified person link. Include the linkage status and make the unlinked population analysable, because it is usually the most interesting part.
Products like these earn their cost when they answer a question the operational systems cannot. My own first check on a new account was to look at the conversion history and find the peaks: where they appear only during sale periods, with no smaller lifts around new-season releases, the customer base has been trained to wait, and that pattern is visible in a purchase-history product with promotion periods marked long before it is visible in any single campaign report.
Monitor Data Quality
| Quality problem | Example | Consequence |
|---|---|---|
| Duplicate transaction | Store system and order system both publish the same sale | Inflated revenue and purchase frequency |
| Missing return | Sale recorded but the later refund never arrives | Inflated contribution and lifetime value |
| Currency mismatch | Local amount treated as the reporting currency | Distorted value and unexplained variance |
| Product mapping failure | Marketplace SKU never linked to the master product | Incomplete category and margin analysis |
| Incorrect customer link | Household purchase assigned to one individual | Inappropriate personalisation and a real privacy risk |
| Late update | Cancellation arrives after the reporting cut-off | Silently restated metrics |
| Channel inconsistency | Store and web define completion differently | Invalid comparison between channels |
UK government data quality guidance recommends understanding, documenting and improving quality throughout the process rather than treating cleansing as a final step (9). Its focus is input-data quality rather than assurance of every analytical output, but the sequencing point holds: quality applied at the end is remediation, not quality.
Monitor at least source completeness, duplicate rate, reconciliation differences, unmapped product rate, missing currency or timezone, unresolved returns and refunds, the late-arrival distribution, restatement size, identity-link distribution, the volume of anonymous transactions, control-total variance, and schema or source-contract failures.
Apply Governance and Minimisation
Transaction records can reveal sensitive circumstances even when no individual field is formally sensitive. Location, timing, merchant, product category and payment pattern together can expose health, financial, religious or family circumstances that the customer never disclosed and would not expect you to hold.
Completed purchases record behaviour. They do not establish why the customer acted, and a data model that stores an inferred motive next to an observed transaction has blurred a line that matters.
The Reconciliation Sequence
Inventory the transaction sources. Define the canonical schema. Preserve source identifiers. Standardise timestamps and time zones. Standardise currencies and tax treatment. Map products and merchants. Identify duplicate feeds. Reconcile orders, payments and fulfilment. Process returns, refunds and cancellations. Handle late-arriving changes. Link customers only at justified confidence. Preserve anonymous and unlinked transactions. Calculate net revenue and contribution consistently. Validate against finance and operational totals. Create versioned analytical products. Monitor breaks and restatements.
The output is not one supposedly perfect customer table. It is a set of traceable, versioned products that preserve the commercial lifecycle and communicate their own uncertainty. Where those products feed platform activation, the controls in CDP implementation and operating discipline apply, and where they define purchase, return and value segments for campaign work, they should be consumed through the CRM operating model rather than rebuilt inside it.
Frequently Asked Questions
Why not just build one purchase table?
Because a purchase is a lifecycle rather than an event. An order can be placed, authorised, partly fulfilled, amended, cancelled, returned and refunded, and a payment can fail or reverse without the order changing. One row cannot say whether it represents placement, payment, shipment or recognition, whether tax and delivery are included, or whether a later refund has been deducted. Model the objects and their relationships, then derive the simplified views from them.
What is the difference between a return and a refund?
A return is a physical or service process in which goods come back. A refund is a movement of money. They frequently occur separately: goodwill refunds happen with no return, returns sit received while the refund is pending, damaged goods attract partial refunds, and store credit substitutes for cash. Model them as distinct objects linked to the original order, line and payment, and never delete the original sale because it was later reversed.
How should purchase data handle anonymous transactions?
Keep them. Guest checkouts, in-store purchases with no loyalty identifier and records below the person-link threshold are legitimate commercial events, and they often represent a large share of trade. Store the linkage status explicitly so the unlinked population can be analysed rather than silently excluded, and never assign a household purchase to one individual to improve profile coverage.
Should marketing revenue figures match finance?
They should reconcile, with documented reasons for any difference, which is not the same as matching. Timing, excluded tax, currency conversion, pending settlement, partial fulfilment, gift cards, chargebacks and data cut-offs all produce legitimate variances. What causes trouble is treating revenue, sales, bookings and gross merchandise value as synonyms, then presenting whichever is largest.
How do you deduplicate transactions safely?
Work down a hierarchy: exact source event ID and version, then a stable business reference, then a declared relationship between events, then controlled composite keys, and only then probabilistic detection. Do not deduplicate on customer, amount and timestamp alone, because legitimate repeat transactions share all three. Keep the suppressed candidates and the rule that suppressed them so the decision can be audited.
What should a dashboard disclose about late-arriving data?
The time through which the data is complete, and how later events are treated. Settlements, returns, chargebacks, fees, tax adjustments and cancellations all arrive after initial reporting. Without an explicit cut-off and restatement policy, figures change quietly between viewings and nobody can tell whether the business moved or the pipeline did.
References
-
Timothy Lebo, Satya Sahoo and Deborah McGuinness, editors, W3C, PROV-O: The PROV Ontology. W3C Recommendation, identifier REC-prov-o-20130430. Published 30 April 2013; current Recommendation with recorded errata. No material commercial interest identified. A general provenance ontology; it is not a retail or financial transaction standard. Not UK law. https://www.w3.org/TR/prov-o/
-
International Organization for Standardization, ISO 8601-1:2019, Date and time. Representations for information interchange. Part 1: Basic rules. International Standard, published February 2019, with Amendment 1:2022. Confirmed in 2024 and marked for future revision, with a replacement committee draft under development. ISO sells access to its standards. Defines representations; it does not define business-day cut-offs or event semantics. Not UK law. https://www.iso.org/standard/70907.html
-
International Organization for Standardization, ISO 4217:2015, Codes for the representation of currencies. International Standard, published August 2015; reviewed and confirmed in 2021 and remaining current. ISO sells access to its standards. Defines currency-code representation; it does not govern exchange-rate selection or accounting conversion policy. Not UK law. https://www.iso.org/standard/64758.html
-
GS1, EPCIS. Technical standard, version 2.0.1, published 1 July 2025. GS1 promotes adoption of its own standards but is a standards organisation rather than an integration vendor. Models visibility events; it is not a complete order, payment, refund or accounting schema. Not UK law. https://ref.gs1.org/standards/epcis/2.0.1/
-
GS1, GS1 General Specifications. Technical standard, version 26.0.0, last modified 27 January 2026. GS1 promotes use of its identification system. Provides shared product and location keys; it does not require any organisation to adopt them and does not solve source-product mapping automatically. Not UK law. https://ref.gs1.org/standards/genspecs/
-
Information Commissioner’s Office, Principle (d): Accuracy. Regulatory guidance on UK GDPR Article 5(1)(d). No publication date shown; current live guidance. Applies to personal data; not every aggregate or corporate transaction record is necessarily personal data. UK regulator guidance, not legislation. https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/data-protection-principles/a-guide-to-the-data-protection-principles/accuracy/
-
IFRS Foundation and International Accounting Standards Board, IFRS 15 Revenue from Contracts with Customers. International financial reporting standard, issued May 2014, mandatory for applicable annual periods beginning on or after 1 January 2018, with subsequent clarifications and consequential amendments. Standard setter with no commercial interest in transaction-integration products. Governs financial reporting; it does not prescribe marketing metrics, warehouse architecture or tax advice. Not UK law, although UK-adopted international standards apply to certain entities. https://www.ifrs.org/issued-standards/list-of-standards/ifrs-15-revenue-from-contracts-with-customers/
-
International Accounting Standards Board, Post-implementation Review of IFRS 15 Revenue from Contracts with Customers. Completed standard review, 30 September 2024. The Board concluded that the standard was working as intended. Does not validate any particular organisation’s operational data model. Not UK law. https://www.ifrs.org/content/ifrs/home/projects/completed-projects/2024/post-implementation-review-of-ifrs-15-revenue-from-contracts-with-customers.html
-
Government Data Quality Hub, part of the Office for National Statistics, The Government Data Quality Framework. UK government framework, published 3 December 2020. Current published framework. Primarily addresses input-data quality; it does not assure every analytical output or prescribe retail schemas. UK government methodology, not UK law. https://www.gov.uk/government/publications/the-government-data-quality-framework
-
Information Commissioner’s Office, Principle (c): Data minimisation. Regulatory guidance on UK GDPR Article 5(1)(c). No publication date shown. Current live guidance, expressly marked under review following the Data (Use and Access) Act 2025, so the wording may change. Does not prescribe a transaction schema. UK regulator guidance, not legislation. https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/data-protection-principles/a-guide-to-the-data-protection-principles/data-minimisation/
-
Information Commissioner’s Office, Principle (e): Storage limitation. Regulatory guidance on UK GDPR Article 5(1)(e). No publication date shown; current live guidance. Retention periods depend on purpose and legal obligation, and the page does not specify a universal transaction-retention period. UK regulator guidance, not legislation. https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/data-protection-principles/a-guide-to-the-data-protection-principles/storage-limitation/
Position dated 29 July 2026. Standards and regulatory guidance change. Reference 2 is confirmed but marked for future revision, reference 10 is expressly under review following the Data (Use and Access) Act 2025, and accounting requirements depend on the reporting framework that applies to your entity. Confirm the current position with finance, tax and legal specialists before implementing any definition in this article as policy.
Use this guide as a source
If it settled an argument in your reporting, cite it, and add Herm as a preferred source so the next answer you get from search or a model comes from work with its definitions attached.
