Skip to content

Building a Single Source of Truth Across ERP, Commerce and Marketing

An anonymised engineering account of reconciling ERP, commerce and marketing data through explicit definitions, transaction lineage and exception checks.

NetSuiteCustom APICeligoArchitecture DesignERP OptimisationSystem Integration

Agree the definition before trusting the dashboard

The challenge

Reconciling business definitions and transaction references across operational systems.

An anonymised engineering account. Customer identities, operating figures and engagement dates are withheld. The focus is the problem, design decisions and validation, rather than a claim of commercial improvement.

A business can have several internally consistent reports that disagree. An ERP may recognise a posted transaction, a commerce platform may show an accepted order, and a marketing tool may attribute a purchase using its own window. Copying these values into a shared dashboard does not resolve the different definitions.

The engineering requirement is to preserve lineage and explain differences before presenting a single view. Refunds, cancellations, time zones, currency treatment and missing transaction references all need explicit handling.

The approach

Create a definition register

Agree the meaning of each reported measure, its source, its date basis and the person or function responsible for approving it. Keep an operational order measure separate from a financial measure when they answer different questions.

Preserve transaction lineage

Retain stable source references and the relationship between an order, fulfilment, refund and financial record. Use controlled mappings for customer and product references. A record that cannot be matched belongs in an exception process, not an unexplained adjustment.

Build from the correct grain

Identify whether a dataset represents an order, a line, a payment or a refund. Aggregate only after joins have been checked for duplication. Keep adjustments and deleted or cancelled records visible so a later refresh does not silently change the interpretation of a previous report.

Reconcile before presenting

Compare source and destination populations using a consistent cutoff. Investigate unmatched records, repeated keys and differing amounts. Explain legitimate differences such as timing or attribution rather than forcing every report to display the same number.

Make reporting changes accountable

Version the extraction and transformation rules. Document refresh timing and late-arriving data. Give changes to a metric definition an approval path and make the impact on existing reports visible.

Design outcome and validation

The design produces a traceable reporting foundation: an agreed definition, a known source and a route back to the underlying transaction. A single source of truth means governed interpretation, not the disappearance of every legitimate difference between systems.

Validation evidence to retain

Use a controlled sample covering completed orders, cancellations, partial refunds and late adjustments. Verify joins at their intended grain and explain every unmatched record. Confirm that a repeated import does not duplicate results and that a refresh uses the documented cutoff.

Retain definitions, lineage, reconciliation exceptions and approval records. No customer revenue, reporting improvements, processing volumes or other business figures are disclosed.

Related projects

Services behind the approach

Let’s connect

Ready for a more connected business?

Tell us where you are and where you want to go. We'll help you work out the next step.

Book a free consultation