Combining CRM, ERP and Spreadsheet Data for Machine Learning

The useful signal lives between systems

Your CRM knows who the sales team spoke to and when. Your ERP or accounting system knows what customers ordered, paid and returned. A shared spreadsheet somewhere knows which accounts have special pricing, which ones complained last winter, and which rep really owns the relationship.

Each system on its own gives a partial view. A model predicting which trade customers will reduce their spend needs all three: falling order frequency from the ERP, fewer sales calls logged in the CRM, and a pricing dispute noted in the spreadsheet. The value is in the combination, and so is the work.

Step one: get everything into one place

Do not build training data by exporting CSVs and joining them in a notebook. It works once and cannot be repeated, and a model that cannot be refreshed is a model with a short life.

Instead, set up regular extracts from each source into one database. For a smaller business that might be a PostgreSQL instance; for larger volumes a cloud warehouse such as Snowflake or BigQuery. Load the raw data untouched into staging tables first, then build cleaned and joined tables on top. When something looks wrong later, you can always trace it back to the original record.

Most modern CRMs and ERPs have APIs or connectors for this. Older on-premise systems may need a scheduled database extract. Our note on integrating with an ERP without replacing it covers the common routes.

Step two: agree who is who

The same customer rarely looks the same in each system. This is the part that eats the time.

The fix is a mapping table that links each source record to one master customer ID. Build it with automatic matching on reliable fields such as VAT number, company registration number or email domain, then fuzzy name and postcode matching, then a person reviewing the uncertain pairs. Keep that mapping table permanently and maintain it; it is useful well beyond the model.

Step three: choose the grain

The grain is what one row in your training data represents. It sounds like a technicality, but it decides what the model can learn and how predictions will be used.

• One row per customer per month suits churn or spend prediction, with features summarising the months before
• One row per order suits late payment, returns or fraud prediction
• One row per opportunity suits predicting which deals will close
• One row per product per week per location suits demand forecasting

Once the grain is fixed, every source has to be summarised to it. CRM activities become 'calls in the previous 30 days'; ERP orders become 'order value in the previous quarter'. Each of those summaries must use only data from before the row's date, or you create leakage, which we explain in data leakage in machine learning.

Step four: rebuild the history systems threw away

CRMs are the worst offenders. A deal's stage, a customer's segment and an account owner are usually stored as current values. When they change, the old value is gone. For a model that needs to know what things looked like six months ago, that is a problem.

There are three ways round it. Many CRMs keep a field history or audit log that can be extracted, often with some effort. ERP transactions are generally dated and can be replayed. Where neither exists, start taking daily snapshots of the important tables now. It will not help today's project, but in a year you will have a proper history.

Bringing spreadsheets in without breaking everything

Spreadsheets hold some of the most valuable context in a business and some of the most fragile data. A few rules make them usable.

1. Agree which spreadsheet is the source of truth and where it lives
2. Lock the column layout, or better, turn it into a simple form or database table
3. Add a proper identifier column that matches the master customer or product ID
4. Replace colour coding and comments with explicit columns
5. Load it automatically on a schedule, with a check that fails if the columns change

Very often this exercise shows that the spreadsheet should really be part of a system. That is how many of our custom software projects begin, and we describe the process in how we turn your spreadsheets into a system.

What it costs and what goes wrong

For a business combining a CRM, an accounting system and a handful of spreadsheets, building the combined, history-aware dataset typically takes several weeks. Identity matching and history reconstruction are the variables. The model itself may take less time than this.

What goes wrong is usually one of three things: an identifier assumed to be unique is not, a system field changed meaning partway through the history, or a spreadsheet owner reorganises their columns. Automated checks on row counts and matching rates catch most of this before it reaches a model.

When SpiderHunts takes on this kind of project through our data science service, we treat the combined dataset as a product in its own right. Reporting, dashboards and future models all benefit, so it rarely feels like wasted effort even when the first model is modest.

Frequently asked questions