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.
2. Identify sources by business model rather than technology trends
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]:
- Completeness: Are all expected orders and key fields present? Missing does not necessarily mean zero.
- Uniqueness: Is each logical order represented once at the chosen grain? Giving the same POS/platform order two names does not make two sales.
- Consistency: Do order status, fulfillment, and linked payments agree, while recognizing they are separate statuses?
- Timeliness: Is the export recent enough for the decision? Yesterday's payout may contain earlier sales.
- Validity: Are dates, currencies, statuses, and quantities in allowed formats and ranges?
- 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:
- Capture at source: retain order, channel, timestamp, status, and references. Do not overwrite the original during cleaning.
- Export: date and version each file. If exports are unavailable, use a standardized manual table and spot-check samples.
- Normalize: harmonize status names, currencies, and dates.
done,delivered, andcompletedmay not mean the same thing in every system; check definitions before merging them. - Relate: connect using stable
order_idvalues and external references. For partial payments, retain a one-to-many order/payment relationship. - Reconcile: compare gross and net sales by status with receipts, fees, refunds, and outstanding amounts.
- Preserve provenance: document the source file and row, who imported it, and any transformations performed.
- 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 |
| AC-102 | 40 | Delivered, bank transfer received | |
| 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 | completed | 40 | 40 | 0 | 0 | |
| AC-103 | platform | completed | 50 | 46 | 4 | 0 |
| AC-104 | 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
- [R01] UK Government. The Government Data Quality Framework. https://www.gov.uk/government/publications/the-government-data-quality-framework/the-government-data-quality-framework
- [R02] Microsoft Learn. Understand star schema and the importance for Power BI. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- [R03] UK Government. The Government Data Quality Framework: guidance. https://www.gov.uk/government/publications/the-government-data-quality-framework/the-government-data-quality-framework-guidance
- [R04] European Commission. Principles of the GDPR. https://commission.europa.eu/law/law-topic/data-protection/information-business-and-organisations/principles-gdpr_en
- [R05] Google Analytics Help. About Analytics sessions. https://support.google.com/analytics/answer/9191807
- [R06] Google Analytics Help. Understand user metrics. https://support.google.com/analytics/answer/12253918