I was shocked. My team and I had been working with the customer on an implementation for months. The project was running long sure but cancellation? The new CFO decided to scrap the whole project and start again with an ERP he was more familiar with. If we had been closer to our planned cutover date when he onboarded, would it have led to a different outcome?
I’ve seen it happen more than once: ownership changes, leadership turnover, even businesses that didn’t survive the disruption, all mid-implementation. A well-run ERP project for a mid-sized manufacturer should take about 8-10 months. If it runs longer, and many do, clients hit change fatigue, lose momentum, and burn out, which is exactly the kind of delay that let my CFO story happen. The lesson: get your data in order so you can minimize the disruption to your business and complete your project before someone else decides to complete it for you. There are three key areas where even the best-intentioned clients struggle to prepare, which drags your project into the danger zone: historical data, static data, and dynamic data.
You Don’t Need the Old Maps Anymore
News flash: your ERP vendor is not going to fix your historical data. They are going to send you templates and say ‘populate these.’ Without knowing your business and your systems, implementers are not positioned to nor have they budgeted for this type of cleanup.
Do not plan to migrate historical transactions into the new ERP. It can be extremely complicated, expensive, and will elongate the implementation. Nine times out of ten the ROI just is not there. If there are key attributes you need from your historical data, have the consultants add those attributes to one of the custom fields available in the table structure. All ERPs have some personalization capability.
Plan to start as clean as possible in your new system. Cleanup efforts will take months, not weeks for most businesses, so take it seriously. Consider building a cross functional data cleansing team and running the cleanup as a separate internal project. Best practice is to keep a copy of your legacy data in a read-only database (assuming you do not have to keep paying for a license or hosting costs) or to extract it so it can be queried by a BI tool when needed. After you have been living on the new system for a year you will find your team will barely reference it.
Note: data preparation is not an IT function because IT does not know the data. The team needs to know the data. IT’s role is to pull it from its hiding places, not clean and organize it.
Whose Boots are These?
Static data is non-transactional information that does not change frequently. Often these data types exist in multiple systems like QuickBooks, HubSpot, Salesforce, or even your internal database. If you are like most customers, these records have been building up for years in your legacy systems and are difficult to reconcile. Here are the most common ones and some ideas for preparing the data ahead of your implementation:
Chart of Accounts: This is the first thing loaded and also your best shot to redesign it. New segmentation makes coding and analysis much easier.
Customers: Limit to active in the last 3 years, deduplicate every ID, and standardize terms i.e. pick one convention for ‘Net 15’ and stop.
Contacts: Confirm names, titles, addresses, phone numbers, and email for all key relationships
Vendors: Confirm accurate lead times at the SKU level. MRP is only as good as this number
Parts / Items: Verify descriptions, costs, units of measure, make/buy status, and lead times
BOMs: Update work centers, operations, and run/setup times. Start collecting shop-floor labor now
Employee Master Data: Active employees only. These are needed for logins, labor collection, and security groups
Warehouses: Define location types and bin/aisle structure. Count and clean before kickoff, then again before cutover
These are representative of the types of data I see regularly, and I am sure there are things I have missed. Trying to do this data extraction and transformation effort mid-implementation is a big problem. Here is how it plays out in actuality: configuration takes a month, training takes two months, data, planned to take a few weeks to load, comes in like a wrecking ball and derails all the project timelines by taking months to extract, transform, and load. Dates push back to accommodate, initial training is forgotten, and the VAR starts to hit you up with change orders to retrain. By then one or two of your core team will quit from burnout walking out the door with months of individualized consulting knowledge going with them… With data, it is an ounce of prevention equals a ton of cure.
You Can’t Step in the Same River Twice
I love this line from Disney’s Pocahontas and it is a good metaphor for dynamic data. Transactional data can be more tricky than static data. During the go-live process, a copy of your existing systems is taken and those values are used as starting values in the new system. A good implementer will rehearse this migration process fully right before the system pilot. Data migrators often use status flags to determine which data should be brought over. When these data records have not been maintained properly for some time these flags are not sufficient. Take purchase orders for instance, how many POs are showing open in your system right now? How old are they? Are there still open lines showing supply? MRP will look at that data, not knowing it is invalid, and refrain from sending the right ‘buy’ signals if it thinks raw material is already en route.
When I was on the other side of the fence leading an implementation at the company I was working for this was a big problem for us. The purchasing team knew what was valid and what wasn’t, but they did not maintain the system to keep it aligned with that reality. When data was brought over in the migration it brought the garbage with it. The system’s recommendations did not make sense and confidence was quickly lost in the software. Listen up leaders… this is why you cannot allow the ‘off the grid’ spreadsheets to run a company. If the ERP is wrong it needs to be corrected, not bypassed.
Let’s review some common dynamic data and things you can do to prepare ahead of time:
- Open Purchase Orders: Sort oldest-to-newest or even better, highest-dollar-first. Close what you will not receive against and validate quantities, dates, and costs
- Open Sales Orders: Close stale demand signals
- Open Work Orders: Close what is invalid and push new jobs past go-live where you can
- Open AR / AP: Migrates at the invoice level. Double-check cash application accuracy
- GL Opening Balances: Have substantiation ready for every line before cutover
- Inventory Balances: Wall-to-wall physical count at every site, close to go-live. Adjust actual quantities, don’t just reserve for what’s missing
Six Months Out, Not Six Weeks
Static data can be cleaned up over time but dynamic data integrity needs to be maintained all the way up through go-live. I have yet to meet a manager that has a full understanding of how much bad data they have in their functional area. Get started with these efforts in earnest a full six months ahead of the implementation. If you do not have time to clean your data up before the project starts, how are you going to have time to do the project over?
If it seems insurmountable, Trail Guide can help. Download this free field guide from our Resources page to get you started and if you need help, give us a call.
