SunX Research Data, A Division of SunX LLC

Country Research

Cleaning Data From Many Systems: A Guide for Asia Pacific Teams

If your company sells in more than one country, your data is probably a mess. Sales live in one system, finance in another, and every market has its own formats, currencies, and ideas about what a word means. This guide covers how we get all of that into one structure people can trust, and why the cleaning stage matters more than anything built on top of it.

Why scattered data starts so many arguments

Most companies in Asia Pacific have plenty of data. What they don’t have is data that agrees with itself. Ask two teams how many customers you had last quarter and you’ll get two numbers. Nobody’s lying. One team counts everyone who has ever bought from you, and the other counts only people who bought in the past twelve months. Same words, different questions.

Cleaning exists to fix this, and the problem gets worse with every market you add. Each country might run a different finance package and a different customer system, plus its own spreadsheet habits. Dates, currencies, units, and codes all follow local custom. One office captures a postcode on the customer record, and another doesn’t. One team reports revenue before tax and another after.

Any single difference is minor. Stack them up and your regional report ends up adding numbers that were never meant to be added. You find out months later, in a meeting where people argue about whose figure is right instead of deciding what to do.

It’s fixable, though. What follows is the process we use when we build pipelines and structure datasets for clients.

Step 1: Map your sources

Before you clean anything, write down where the data lives. Start with the obvious places, like finance software and customer databases. Then go looking for the rest: spreadsheets that individual teams keep, files partners email you, exports from third-party platforms.

For each one, note:

  • who owns it, and who can actually explain it
  • how often it’s updated
  • what the key fields mean
  • how data gets in, automatically or by hand
  • what’s already known to be wrong with it

It’s tedious, and it’s also where you’ll find most of your hidden problems. You’ll spot the same figure stored in three places, and you’ll spot the measure nobody records anywhere.

Step 2: Agree on definitions before tools

The most useful cleaning work usually isn’t technical. It’s getting people to agree on what a word means. “Active customer”, “net revenue” and “lead time” each need one definition that every market uses.

Get the people who know each source into a room and settle it in writing. Sometimes markets legitimately need different definitions, local regulation being the usual reason. In that case keep both, label them clearly, and don’t blend them. It’s much cheaper to argue about a definition before the analysis than after a report has gone round the company.

What you walk away with is a short data dictionary: each field, what it means, which values are allowed, and who owns it. Anyone new, whether an analyst or a developer, can then read it instead of guessing.

Step 3: Standardise formats

Once the definitions are settled, you can decide how things get stored. Pick one convention for each of these in the cleaned dataset:

  • Dates and times: one format, with time zones handled on purpose
  • Currencies: keep the original currency, and have a clear rule for conversion
  • Units: one measurement system, with the conversions written down
  • Names and addresses: consistent spelling, casing and structure
  • Codes and categories: a single list of product codes, region codes and status labels

Wherever you can, keep the original value next to the standardised one. If someone questions a figure later, you can trace it back to what the source system actually said. Changes you can’t trace are the ones that cost you people’s trust.

Step 4: Find and fix quality problems

Data problems tend to fall into the same few groups, and each has a standard check:

  • Duplicates: the same customer, order or transaction showing up more than once
  • Missing values: blank fields, plus placeholders that look real but mean nothing
  • Impossible values: negative quantities, future dates, ages that can’t be right
  • Inconsistencies: a country spelled five different ways, or one product sitting in two categories
  • Broken totals: figures in the cleaned data that no longer reconcile with the source

Decide up front how you’ll handle each. Some can be fixed automatically with a rule. Others need someone who knows the business to make the call. A few should be left out and reported, so nobody assumes they were quietly fixed.

Step 5: Validate against the source

After cleaning, test what you’ve got. Reconcile totals against the source systems, compare record counts before and after, and pick some individual records and follow them through by hand.

If the cleaned data differs from the source, you should be able to say exactly why. A difference with a documented reason is fine. A difference nobody can explain is a warning sign.

Step 6: Automate what repeats

If someone does the same cleaning by hand every month, it will eventually get done differently. People change roles, shortcuts creep in, and the process drifts.

A pipeline applies the same rules every time. We write ours in Python and SQL and keep them in Git and GitHub, which also gives you a record of what ran and when. If a rule changes, you can see the change and undo it. If someone questions a number, you can show how it was produced.

Cloud infrastructure then stores the cleaned data securely and can grow as you add markets, systems, and users.

Step 7: Document everything

This is the step people skip and then regret. Write down what each step does, why each rule exists, who approved it, and what to do when something breaks. Write it for whoever takes over in a year. That person may well be you.

A quick example

Say a company sells in five Asia Pacific markets, each with its own sales system. Head office wants one monthly report on revenue by product. Three of the systems record revenue before tax and two record it after. Product codes are different in every market, and two systems use a different date format.

For that company, the process would look like this: map the five sources, agree on one definition of revenue and convert everything to it, build a single product code list with a lookup table for each market, standardise the dates, check the monthly totals against each system, and schedule the whole thing to run automatically. The monthly report then pulls from one trusted dataset instead of five separate files.

What good looks like

A solid data foundation has a few things in common. Everyone works from the same definitions. Every figure traces back to its source. Checks run automatically and flag problems early. The documentation is up to date. And the people who rely on the data trust it enough to stop double-checking it.

That last one matters most. A clean, documented foundation isn’t exciting to hand over, but it’s the reason every dashboard and forecast built later can be believed.

If your data is spread across systems in the region, we can help you build that foundation. Get in touch.