I have just finished two Data Migrations, and I wanted to take some time to review my work, see what I did correctly and what I could have improved. I think many of these thoughts can be useful to other consultants and help them avoid some costly mistakes I have made in the past.
I have already done several Data Migrations on CRM Projects. Even though I am not a Data Migration Guru, but I think you can still find valuable tips and tricks in this guide.
This is not an exhaustive guide, and any type of comment is welcome!
My Top 11 tips for Data Migrations:
1° Start early.
I can’t emphasize this enough: Data Migration takes time, and a lot of unexpected things can happen. I have listed a few events that have happened for my own data migrations. Some could have been anticipated, others not.
- We were relying on an editor to extract data from its outdated proprietary software. The editor was not happy to see his client leave. It took him a month to send us the data, forcing us to review our calendar.
- A SQL table on the proprietary system could not be opened, despite the effort of the whole IT system and the editor. It delayed the project by a few days.
- We needed to gather all Excel files, scattered across the organization and the department. It took us a lot of time to find what was stored and where.
- We found out that the data we wanted to implement was outdated and sometimes completely wrong. We had to gather the data again from the on-the-field sales reps, losing a few precious weeks.
Of course, in a perfect world, one could have anticipated all of this. But based on my experience, it’s not the case, and it’s better to start early. On my next projects, I will go as far as asking for the data during sprint 0.
2° Start the migration from the client’s data model, not from your own software.
This is one crucial error I made during my last project. We had been building a CRM for my client for a whole year and all stakeholders were very happy with it. For the data migration, I extracted templates from all my tables and work for the client to fill them. Unfortunately, this was not the best strategy. There was data that my client “Could not lose during the migration” and that had not been developed on the future CRM.
For example, my client had developed a table to gather “Personal links” between contacts (Husband, Wife, Daughter etc.). This was precious information in their field of activity. So, we had to develop that concept last minute. This was not a huge development, but it delayed the data migration by a few days again.
That is another benefit from starting early and having the client’s data model in mind. It prevents you from starting from zero and saves you a lot of time.
You can find an excellent article from James Clear below, stating the importance of starting from an existing model. It is based on “The Toaster Project” and I strongly encourage you to read it.
3° Keep a log on every Excel file.
I have been saving every single detail of my data migrations. On every Excel file, I write down every step I am taking. It looks like this:
This is a real extract from my last data migration (Without any confidential information). There are a few advantages to do so:
- First, things will go wrong at some point in the project. A Ctrl C/Ctrl V with an Excel filter you had not seen, an interruption that causes you to delete the wrong line, your Excel file that bug at the wrong time. You need to be able to track down your modifications to lose as little time as possible.
- Data migration can be boring, at least for me. It is not my favorite part of a CRM project, and it can last for months. Keeping a “Migration diary” can give you a sense of progression. You can see that you are solving problems and making progress. That’s a nice boost for me.
- You will need to communicate with other stakeholders. You will have to keep your sponsor updated, ask for additional information from your end-users, or exchange with other editors. You can’t remember everything. This diary will help you remember things when you are sending your emails at the end of the day.
4° Know Excel shortcuts of by heart.
If your data migration involves Excel, you need to master Excel. That’s a no-brainer. There are a few indispensable formulas:
- VLOOKUP, to map data between files.
- CONCAT, to add different cells together
- IF, to create an “If… Then” logic
- CTRL + D to apply a formula to several lines
- CTLR + F to find data in your Excel Spreadsheet
- The “Conditional Formatting” to find out duplicates.
- LEFT or RIGHT formula, to get only the first or the last characters of a cell.
- Holding the CTLR button when moving across the file, to go faster.
- ALT + Tab to switch between windows.
- TRIM, to remove leading spaces, extra spaces, and trailing spaces
These are my first shortcuts that come to my mind. They probably represent about 80% of my work on Excel. If you have to do a data migration, make sure you are an Excel Guru. If you are not, take a day to train on Excel. It will be worth it!
5° Set up your environment correctly, including your hardware environment.
Data migration requires full concentration. You cannot afford to be interrupted every ten minutes. Work from home if you have to, or make sure your colleagues understand that you must focus intensively. Don’t sabotage yourself: Shut down your email and put your phone in your bag.
For the hardware, I strongly recommend using at least two screens. At some point, your ALT + Tab shortcut will not be enough. You will have to compare several documents or look at your migration instance while looking at the source file. Make sure your screen is big enough (>27″) so you can “Split windows” (WINDOWS + Left or right arrow) to have several documents displayed in the same windows.
Finally, make sure your computer is strong enough to handle the data migration. There is nothing more annoying than losing an hour of work because your laptop has crashed during data migration. Of course, you will lose a lot of time and concentration if your Excel file freezes every ten minutes. My laptop was not powerful enough to handle my last data migration, so I had to create a Virtual Machine on Azure. This worked perfectly, and if your IT budget allows it (It is around 100€ a month if you make sure to switch your VM off at the end of the day) I strongly recommend you do so.
Here is more information about Azure VM.
6° Create a direct communication canal with your client.
There will be thousands of exchanges back and forth with your client. That cannot be different: They have the business expertise and you have the IT expertise. They have been using the former software for years and they know all the tricks.
- They know which phone to choose between “Main phone” and “Primary phone”
- They know if this description field on the “opportunity tab” is worth keeping or not
- They know that you don’t have to migrate the quote priors to 2008 as they are outdated
- Etc.
This requires setting up communication with your stakeholders. You don’t need to do anything super fancy. I have been using a mix of “Teams” and “SharePoint” with my clients and it has been working perfectly. I suggest not relying too much on Emails for Data Migration, because you will have to wait. Using shared Excel files is a good practice. This will ensure everybody has the latest version. I had been losing a few hours in previous Data Migration by working on already-outdated Excel files.
7° Communicate often, especially with your sponsor.
Data Migration can be tricky for your Consultant Image. You will spend weeks working on it and your client will not see it. Very few people know how hard it can be, and how many hours you have to spend on an Excel file to make it ready for import. This is the opposite of working on the user interface, where little efforts can yield spectacular changes. Here, you can work for 40 hours on an Excel file and nobody will see it before the end.
For this reason, you will have to communicate often with your sponsor and find KPIs. This is where the “Migration Diary” is important. In my emails, I indicate how many problems I have faced, and how many I have already solved. I indicate which one is directly linked to technical issues or client requests. I indicate which table has already been imported, or which preventive action I took to address a specific threat. Depending on your relationship with the Sponsor, sending an email every day might be appropriate. Your sponsor doesn’t have to read it in detail; He or she just has to feel that you are making progress and that you are in control of your solution.
This is the same for problems. They will arise and sometimes, you will be directly responsible for them. Data Migration can be hard. If you can fix them quickly, keep them under the radar. Your Sponsor doesn’t have to know every little mistake you have made, and that could impact his/her confidence in you. But if you see that you will not be able to fix the problem in a reasonable amount of time, communicate it. Don’t try to make it look better, just communicate the facts, indicate why this has happened and what you have already done to improve the situation. If your sponsor is from the IT department, ask for his point of view. You will need to be in problem-solving mode!
Finally, send the minutes of the meeting after your calls. This will depend on the country and the culture you are working in, but even in France, this is really helpful. Clients will forget in good faith the decision they took earlier. You will probably forget about parts of what you said as well. Keeping track of what decisions you have made, and the actions everybody needs to take, is crucial for a Data Migration, even with an AGILE methodology.
8° Keep an original version of your Source files and save everything.
Your client will send you the source files for the data migration. The first thing you have to do is to make a copy of them and never touch that copy. It will be really useful for the tests, and to check you have not made any errors during the process.
Keep a copy of everything you do. If you can store them on a protected cloud solution, it’s even better (Be careful about NDA agreement, sometimes clients don’t want data saved on the cloud. In this case, store them on a USB drive).
This goes as well for Excel Sheets. I rarely delete them, I just keep track of everything.
For example, I had to link a transaction with a client for a project. I created a sheet for these transactions; Another one with the link between the client and the transactions, and another one with the VLOOKUPS. I kept these three sheets all along with the project. This was useful, given that a few days before the Data Migration my client told me that finally, transactions linked to a specific segment of the client would not be imported. This meant I could make the update quickly and without any efforts thanks to these still-existing sheets.
9° Don’t import data on the production instance without a Roll-out.
That might seem obvious, but I will still explain it.
Data Migration will not go right on the first try, not with an AGILE methodology at least. You will need to have a separate environment where you can import data and erase it without any consequences. This environment will have to be a copy/paste of the production environment, so you don’t have any surprises during the big day.
A few days before the data migration, you will have to make a “Fake Data Migration”, called a Roll-out, to see if everything is going according to plan. This will enable end-users to test the data-migration and give feedback. Ensure that you are keeping track of every little detail you have encountered during this roll-out, and how you have solved them because they will probably occur during the big day as well.
10° Don’t underestimate the testing plans.
The testing stage can take up as much time as the initial data migration sometimes.
Don’t underestimate the testing plans.
Depending on the maturity of your client, you will have to plan a few things:
- If your client is not tech-savvy, plan Excel training for the project team, and make sure they know how to use the new software. They can’t test it correctly if they don’t master these two tools.
- Prepare testing scenarios and let your end-user invent new ones. This is a very daunting task, and most people don’t know how to test a data migration. This is not their normal job. Write a few scenarios to help them out and do the first tests with them if they need you.
- Explain all the “Obvious” details. For example, indicate that data that was updated on the “Testing instance” will not be kept on the production instance (Most users think so).
- Indicate how to give feedback. You will need to teach them how to use the snipping tool, indicate where is the problem, what they should see, how they arrive to that specific page, etc. If you don’t do so, you will lose time asking for more information every time an end-user find a problem.
Data Migration can be a bit abstract for users, and most of them will know that there is an improvement only when they see their data in their new environment. This is perfectly normal. It’s like buying a flat and just looking at the plans: You will notice a lot of things when you will see your flat for real, no matter how many hours you have spent on the plans.
Don’t take it personally. There will be things that are not working, or you will feel like your client has changed their mind several times. This is normal, this is part of Data Migration. This will be way easier for them, and you, to see what’s wrong when it’s live on the testing moment.
11° Make sure you are well-rested.
Every little mistake can cost you a lot of time. I have made my biggest mistakes at the end of the day. A CTLR C & CTLR V at the last minute with a filter you haven’t seen can cost you a lot of time a few days later when you realize data don’t match.
Some of them can be even more costly. Only do the final data migration on the production moment in the morning after a few cups of coffee.
In the same idea, if it’s big data migration, don’t do too much overtime, no matter how pressing the deadlines are. You will end up wasting time by doing stupid mistakes. If you see you can’t meet the deadline, tell that to the sponsor and explain why. Ask him to do arbitration if you need to. If he wants to meet the deadline, then he will have to reduce the scope of the Data Migration.
Fatigue can also be very dangerous for your relationship with other stakeholders. You will have to remain patient with your end-users, even if some of them are not tech-savvy. You will have to be excellent on communication with the Sponsors and show that you are in control. Appearances are important in Data Migration. If you arrive exhausted at a meeting and very irritable, people will assume that you are not in control. This will then be harder for you to explain that technical difficulty is delaying the Data Migration.
I can be a little bit extreme on that point. I have days where I will only work for 5 or 6 hours. I am exhausted, and I know I will make mistakes. On the other hand, when I am sharp, I will work 12 hours and do a tremendous amount of work. Spend some time to know yourself!
In conclusion, Data migrations are hard, and you should not underestimate them.
- Make sure you don’t put yourself under unnecessary stress by starting early
- Set you up for success. Create communication channels, make sure you will not be disturbed and that your hardware can handle it
- Learn Excel Shortcuts
- Things will go wrong at some point. Get prepared. Keep a Migration diary, create a testing instance, and save everything on several locations.
- The testing phase will sometimes take up as much time as data migration. Be prepared and teach end-users how to be effective at testing.
- Make sure you are well-rested throughout the whole project and pay attention to your way of communicating with your stakeholders. They can’t know exactly what’s going on in your back end. Confidence and Honesty will be all they can rely on during that phase.
I hope these tips and tricks will be useful. If you have any additional comments, please write them in the comments, I am very curious about your feedback. If you need additional information or advice, you can reach me on LinkedIn, indicating that this is about this article.
Thanks for your time!
