When the numbers are scattered, the first instinct is to build a dashboard that pulls them together. That is often the wrong order. A dashboard displays data, it doesn't make it consistent: if two sources disagree, the dashboard shows both, or quietly picks one. This guide explains the path that comes first: from sources to integration, then to the data model, and only at the end to the dashboard.
Why the dashboard is not the first step: an example with two sources
The path has four stages: the sources (where data is born), the integration (how it is brought to the same place), the data model (how it is organized and linked) and finally the dashboard (how it is shown). Skipping the stages in the middle means building the view on ground that won't hold.
An example with made-up numbers. A company looks at March sales. The online store says €96,800, the ERP says €91,300. The gap is €5,500. Who is right? Neither is wrong: they count different things. You find out with a reconciliation, one step at a time.
| Step | What you check | Effect on the store figure | Remaining gap |
|---|---|---|---|
| Start | March total from both sources | 96,800 | 5,500 |
| 1. Cancelled orders | The store counts them when created; the ERP only once fulfilled. 60 orders, worth €4,200 | 96,800 − 4,200 = 92,600 | 1,300 |
| 2. Shipping | The store includes it in sales; the ERP records it separately. €560 | 92,600 − 560 = 92,040 | 740 |
| 3. Time zone | The store records time in UTC, the ERP in local time. Nine orders (€1,050) placed around midnight on 31 March are already in April for the ERP; three orders (€310) from 1 March are still in February for the store | 92,040 − 1,050 + 310 = 91,300 | 0 |
Check: 4,200 + 560 + 740 (1,050 − 310) = 5,500, exactly the starting gap. There is a fourth problem, which doesn't change the total but shows up as soon as you drill down: product codes. In the store a table is "TAV-120-NOC", in the ERP it is "TAV120NOC". For the total it makes no difference. For sales by product it does: without a code that matches them, the two sources can't be crossed.
A dashboard built at step zero would have shown 96,800 or 91,300, and at every meeting someone would have asked "but which number is right?". The reconciliation, done once, turns into rules written into the integration.
The source inventory: a template to fill in
Before integrating, write down what you have. One row per source, with these columns:
| Source | Owner | Access | How often it changes | Identifier |
|---|---|---|---|---|
| Online store | Whoever runs ecommerce | Direct connection or export | Every order, continuously | Order number, customer ID, SKU |
| ERP | Administration | Export, database or API to be confirmed | Daily closings | Customer code, item code |
| Cost spreadsheet | Whoever fills it in | Shared file | Monthly, by hand | Often none |
The rows are examples: fill in your own. The last column is the most revealing. Where the identifier is missing or differs from one source to another, the integration will be real work.
Types of source and what to look at in each
| Type of source | Examples | What to look at |
|---|---|---|
| Ecommerce platforms and marketplaces | Online store, sales channels | Time zone, order status, how returns and cancellations are treated |
| ERPs and accounting | ERP, invoicing | When an order becomes a "sale", item and customer codes |
| Marketing tools | Advertising platforms, email | How they attribute a sale and how often past data changes |
| Spreadsheets | Costs, budgets, master lists | Who edits them, whether columns stay the same, broken formulas |
| Internal databases | Company archives | Table structure and access permissions |
| Files and documents | Periodic exports, reports received | Constant format, file names, who sends them and when |
The data model: an example with three tables
The data model is how information is split into tables and linked together. A minimal example:
- Customers: customer_id, email, country.
- Products: product_id, code, name, category.
- Orders: order_id, customer_id, date, status, amount.
- Order lines: order_id, product_id, quantity, price.
The relationships: one customer has many orders (linked by customer_id); one order has many products and one product appears in many orders (linked through the order lines). With this structure you can answer questions like "how many customers bought twice" or "which products are bought together" without rebuilding anything by hand. This is also where the reconciliation rules are set: "status" excludes cancelled orders, "date" has a single time zone, "code" is unique per product.
Update rules and what to do when a source doesn't respond
- Set the frequency per source. Orders may be needed daily, costs once a month. Refreshing everything every hour isn't necessary.
- Show the last update. Every view should say when the data was last loaded.
- If a source doesn't respond, don't show zero. A zero looks like data. Better to keep the last valid value with its date, and flag the problem.
- Retry and alert. One automatic retry, then an alert to the owner named in the inventory.
- Keep history. If a source corrects past data, you want to know, not discover it from an unexplained difference.
Signs that integration comes before the dashboard
- Two reports with the same name give different totals.
- Answering a question means joining two files by hand.
- There is no common field across sources, or it changes over time.
- Nobody can say for sure what "sale" or "customer" means.
- Every month someone spends a day making the numbers add up.
If you recognize two or more, the first investment is integration, not the view.
Typical mistakes
- Picking a "true" source without saying so. If the dashboard only takes the store, the ERP drops out of the picture. State which source counts for which number.
- Integrating without an identifier. Joining by name or description produces wrong matches that look right.
- Ignoring time zones. They are the most ordinary cause of month-end differences.
- Not writing the rules down. A reconciliation done by word of mouth gets forgotten; written down, it lasts.
When a ready-made connector is enough and when you need custom work
A ready-made connector is enough when you have one or two common sources and the questions are standard: the fields are already mapped and the definitions already chosen. It is quicker, and worth using.
Custom work is needed when the sources are unusual (an internal ERP, a company database, a system with no connector), when you must join sources with different identifiers, or when the company's definitions don't match those of a standard tool. There, integration and the data model are the real work, and the dashboard comes after.
Where Opalca Lab fits
Opalca Lab offers custom consulting and development: data source integration, API connectors and custom databases, data modeling, custom dashboards and data visualization. There is no standard price list: each project is assessed starting from the company's sources.
One example is Turnover, an agency specialized in selling on Amazon, which obtained custom Amazon reporting dashboards with Opalca Lab. More cases are on the Cases page.
Scattered sources and not sure where to start? Talk to Opalca Lab or write to us.
Keep reading
- AI readiness: how to prepare your business data
- Power BI or Looker Studio: when you need a custom solution
Frequently asked questions
What is data integration?
It is the process of bringing data from different sources to the same place, applying shared rules for definitions, dates and identifiers, so the numbers can be compared.
Why do two sources give different sales for the same month?
Usually because of different definitions: how cancelled orders are treated, whether shipping is included, which time zone the date is recorded in. Sometimes also because of different product or customer codes.
What is data modeling?
It is how data is organized into tables (for example customers, orders, products) and linked with shared identifiers, so you can answer questions without rebuilding everything by hand.
Can I build the dashboard first and fix the data later?
You can, but every disputed number will erode trust in the view. It is better to at least reconcile the main sources before showing them.
When do I need a custom project?
When sources have no ready-made connectors, when they have different identifiers to join, or when the company's definitions differ from those of a standard tool.