One sale may appear in the point-of-sale system, a WhatsApp conversation, a delivery platform report, and the bank. Adding those records does not produce four sales: you have probably counted the same sale more than once.

Data integration does not start with buying a system. It starts by clarifying the decision you need to make and the entities, events, and periods behind each number. A small shop might combine CSV exports and manual logs. A larger operation may need a transactional system or data warehouse, but it must still define identifiers, statuses, lineage, and checks. The UK Government Data Quality Framework identifies six dimensions: completeness, uniqueness, consistency, timeliness, validity, and accuracy [R01]. Automation does not automatically make an integration trustworthy.

Here we will build an analytical record from multiple sources. Daily cash control and payment reconciliation already belong to the cash, collections, and payments guide. Our goal is to compare sales activity, availability, channels, customers, and outcomes without double counting.

1. Start with a question and define what each row represents

Before exporting anything, write the question: “How much did we sell by channel, which orders did we cancel, and how much money did we actually receive this week?” Each noun implies a different unit:

Unit Recommended identifier Question answered What it does NOT prove
Inquiry Inquiry or conversation ID How many people asked That an order was accepted
Order Unique, stable commercial ID across channels What was ordered and its status That it was collected or delivered
Order line Order ID plus line ID Which product and quantity That an order has just one line
Payment Processor or bank transaction ID Which cash movement settled That it is new revenue rather than an advance or refund
Payout Merchant payout ID Amount transferred after fees That it equals gross sales
Customer Authorized internal customer ID, where needed Relationship between one person's purchases That a website session equals one person

A table's grain—what exactly one row means—must be explicit. Microsoft Learn recommends keeping fact tables at a consistent grain; mixing order lines, entire orders, and bank transactions in one total produces misleading sums [R02]. In a small spreadsheet, separate tabs linked by IDs may be more appropriate than a formal data warehouse.

For a coffee shop, sources might include POS exports, phone or WhatsApp orders, delivery logs, inventory, and bank statements. For an appointment service, they might include a calendar, accepted estimates, cancellations, hours worked, and complaints. For an online store, orders, payment gateway records, returns, and web analytics. For a subscription app, defined product events, subscriptions, support interactions, and cancellations.

Not every source requires personal tracking. Waste can be measured using product-level daily counts. On-time service may require only order IDs, timestamps, and delivery statuses. Collect personal information only when justified by a specific purpose and applicable rules.

3. Design a data dictionary before combining records

A dictionary prevents one person from using “sale” to mean a placed order while another means money received. Define at least the field name, meaning, unit, source, owner, format, valid values, time zone, and rule for missing values.

Field Operational definition Type or example Quality rule
order_id Unique order ID across channels AC-1042 Required; unchanged when paid
source_order_id Original POS or platform ID PL-208 Pair with source, since IDs may overlap
channel Source of the original order in_store, whatsapp, platform Controlled vocabulary
event_at Time the source event occurred ISO 8601 with time zone Not the same as import date
settled_at Time a payment was settled Timestamp or empty Empty means pending/unknown, not zero
order_status Current business order status completed, cancelled, pending Preserve change history
gross_amount_pen Gross order amount Decimal PEN value Define tax and discount treatment
fee_amount_pen Platform fee withheld Decimal PEN value Record only when known
net_received_pen Cash actually received, net of known fees Decimal PEN value Zero only when confirmed
origin Source system, file, and export date POS_2026-11-14.csv Preserve lineage

In practice, add a record_type to distinguish an order, payment, refund, or settlement, along with related_order_id and a movement ID. The example field names are not mandatory; preserving their meanings is. If several currencies are involved, do not add them without conversion: retain original currency, exchange rate, date, source, and exchange differences. Also choose whether you are grouping by order date, delivery date, or receipt date. Each supports a different question.

4. Check six dimensions of data quality before trusting the totals

UK government guidance distinguishes these dimensions [R01][R03]:

  1. Completeness: Are all expected orders and key fields present? Missing does not necessarily mean zero.
  2. Uniqueness: Is each logical order represented once at the chosen grain? Giving the same POS/platform order two names does not make two sales.
  3. Consistency: Do order status, fulfillment, and linked payments agree, while recognizing they are separate statuses?
  4. Timeliness: Is the export recent enough for the decision? Yesterday's payout may contain earlier sales.
  5. Validity: Are dates, currencies, statuses, and quantities in allowed formats and ranges?
  6. Accuracy: Does the recorded value match reality when checked against evidence? A correctly formatted field can still hold an incorrect amount.

Apply proportionate controls: unique order counts, missing fields, incompatible statuses, totals by currency, comparisons with platform statements, and samples checked against receipts. Do not automatically delete every apparently duplicated record: two legitimate purchases can share an amount, time, and customer. Deduplication requires defensible keys and context. Log exceptions and resolutions.

5. Follow a minimum integration workflow without building software

Capture → export → normalize → relate → reconcile → preserve provenance → refresh. Every step addresses a specific risk:

  1. Capture at source: retain order, channel, timestamp, status, and references. Do not overwrite the original during cleaning.
  2. Export: date and version each file. If exports are unavailable, use a standardized manual table and spot-check samples.
  3. Normalize: harmonize status names, currencies, and dates. done, delivered, and completed may not mean the same thing in every system; check definitions before merging them.
  4. Relate: connect using stable order_id values and external references. For partial payments, retain a one-to-many order/payment relationship.
  5. Reconcile: compare gross and net sales by status with receipts, fees, refunds, and outstanding amounts.
  6. Preserve provenance: document the source file and row, who imported it, and any transformations performed.
  7. Refresh: select a useful frequency: daily for available capacity or cash, weekly for process improvement, monthly for low-volume trends.

You can keep one Orders table and one Movements table linked by order_id. If you produce a convenient final view, explicitly declare one row per order and aggregate payments before joining. Otherwise, an order with three payments can appear three times. Do not silently drop unmatched payments through a restrictive join; place them on a discrepancy list for review.

6. Worked example: POS, WhatsApp, and platform without double counting

All figures are hypothetical. On one day, “Almuerzo Cerca” has five orders: AC-101, AC-102, AC-103, AC-104, and AC-105. The operator also enters AC-103 into the POS while the delivery platform records it, so it is not a second sale.

Raw exports before reconciliation:

Source Order Order amount (S/) Additional information
POS AC-101 30 Delivered, paid in cash
POS AC-103 50 Operating copy of external order
WhatsApp AC-102 40 Delivered, bank transfer received
WhatsApp AC-104 35 Canceled before preparation; no payment
Platform AC-103 50 Delivered; S/4 fee, S/46 settled
Platform AC-105 45 Delivered; S/3 fee, S/42 net payout pending

Adding the six raw amounts produces S/250, an incorrect total: it includes a duplicated S/50 order and a canceled S/35 order. The normalized one-row-per-order view is:

Order Original channel Final status Completed gross sales (S/) Net cash received (S/) Fee (S/) Pending net payout (S/)
AC-101 in_store completed 30 30 0 0
AC-102 whatsapp completed 40 40 0 0
AC-103 platform completed 50 46 4 0
AC-104 whatsapp cancelled 0 0 0 0
AC-105 platform completed 45 0 3 42
Total — — 165 116 7 42

The reconciliation is 165 − 7 = 116 + 42 = S/158, assuming no other discounts, refunds, taxes withheld, or settlement differences. The canceled order contributes no sale or receipt. Had it been paid and later refunded, we would record the advance and refund as linked movements rather than erase the transaction. Order AC-105 can be sold today and paid tomorrow: sales and cash answer different questions. Withheld platform fees belong in a separate cost record; a net payout is not proof that gross sales equaled the payout.

Handling a discrepancy: if the platform reports S/41 pending while the order and published fee imply S/42, retain both amounts and flag the difference. Do not rewrite numbers solely to make the sheet balance; investigate an adjustment, discount, error, or holdback.

7. Protect privacy, access, and traceability

Combining WhatsApp, payment records, and analytics can reveal more about a person than is required for a business decision. The European Commission explains purpose limitation, data minimization, accuracy, retention limits, and transparency when the GDPR applies [R04]. This is a European legal framework, not a universal rule for every country or business.

For an operational spreadsheet, limit access, remove unnecessary personal fields, document purposes and retention, do not share statements containing full bank details, log material changes, protect sensitive backups, and keep a recoverable copy. A delivery-punctuality analysis does not need customer phone numbers. Do not attempt to identify anonymous website visitors or combine analytics IDs with contact records without a lawful basis and a suitable risk assessment.

8. Do not mistake web metrics or automation for data quality

In Google Analytics, a session is a period of interaction with a website or app; it is not equivalent to one unique person [R05]. Google also defines different user metrics depending on reporting context [R06]. A click is not an order, and a purchase may have several order lines. Connect these metrics to verified sales only when the relationship is technically defensible and privacy requirements are met.

Scale Reasonable starting approach When to move on
Few orders and channels Manual log with unique IDs and daily checks Repeated errors or unreasonable effort
Periodic exports Excel/Sheets with source and reconciliation tabs More sources, volume, or need for history
Recurring integrations Scheduled imports with validations and alerts Secure APIs and people available to supervise
Large volume and multiple products Formal data model, warehouse, and quality processes Decisions and risks justify cost and maintenance

A dashboard connected to poorly organized data displays errors faster; it does not correct them. Choosing reproducible indicators is covered in how to define business metrics and KPIs. Visualization and review routines belong to another article in this pillar.

Reusable template: data dictionary and reconciliation log

Minimum data dictionary: field | unit/grain | definition | source | format | valid status | missing-value rule | owner | refresh frequency | access. One-row-per-order log: order_id | order_date | delivery_date | channel | status | currency | gross_amount | fee | net_collected | net_pending | external_reference | source_file | review_status. Discrepancy log: id | order | source A | source B | difference | possible explanation | evidence | owner | resolution status.

Add a separate movements tab for partial payments and refunds; never treat those as additional sales. These columns can be copied to Sheets, Excel, or a notebook without committing to a particular vendor.

Common mistakes, limitations, and final outcome

Joining on amount alone, treating blanks as zeros, summing sales and payouts, confusing customers with sessions, mixing time zones, adding soles and dollars without conversion, deleting cancellations without a trail, reusing non-global order IDs, or automating an unverified process. A reconciled total alone does not prove there is no fraud or that legal accounting obligations have been met.

What you should have by the end: a field dictionary, unique-order table, separate movement record, discrepancy list, and refresh/review routine. This foundation supports indicators built on understandable, traceable data; it does not replace required accounting records or tax systems.

References