Spreadsheets, every department has them, everybody uses them. They are useful for storing the office phone directory, client lists, quarterly reports, and, if you are like me, the household budget. Although spreadsheets have grown in the number of rows, columns, and tabs they can have, they still face the limitation that they are a representation of a slice of data from your organization. Spreadsheets can aggregate and connect data from other spreadsheets, but they often face limitations because data is not consistently maintained across them. This means that to build a cohesive profile of your customer, you have to sift through each department’s spreadsheets and align the data. This is a time-consuming process and only affords a picture of what has happened in the past. There is little ability to refresh data or build accountable forecasting models.
The data in these spreadsheets is good data, it is just incomplete. Connecting these spreadsheets is the main challenge of migration to CRM.
CRMs can relate one table to another. The most powerful relationship is the one-to-many relationship. Think of an Account that may have many invoices. Let’s say you wanted to see what a customer has purchased over the past year and how long they have been a customer. That data is in two different tables. There is an Accounts table that stores customer information, including how long they have been a customer, and the Invoices table, which shows what they have purchased in the past year. In this relationship, one Account has many Invoices.
There are also hierarchical relationships. Consider a company that has multiple locations. In CRM, you can designate one location or one company as a parent, and the others as “children” to that parent, thus making sure that subordinate entities are kept together.
And then there are one-to-one relationships. This is the basic spreadsheet relationship. In these relationships, one object may be linked to only one of the other objects. For example, social security numbers, health care policies, or current residence. You may have had multiple Social Security numbers, health care plans, or residences, but you can have only one at a time. CRMs can enforce this type of relationship and reduce data errors.
Where Do You Start When Migrating from Spreadsheets to a CRM System?
So, where to start? It starts with good data architecture. The spreadsheets you have probably contain fields from multiple tables. For example, you may have a list of in-state contacts who buy a certain product from you. That spreadsheet will include fields such as first and last name, company, and contact information. There may also be columns for how much product they have bought over the past several years. These last columns are manually filled in from sales figures. This one spreadsheet contains data from five different sources (tables).
What Data Tables Matter Most in a Successful CRM Migration?
The first table you need to identify is the User table. This contains the User’s name, email address, and unique ID that identifies the User in other tables. This is typically built from a list of active users in Active Directory. Consider whether the list of users should be inclusive or limited to those with licenses for the product.
The second table is Accounts. These are your customers. The Account can be an entity, such as a Company or Organization, or an individual. In most SMB cases, we are dealing with Companies as accounts, and for simplicity’s sake, I will stick with that model. Accounts can also have hierarchical relationships with other accounts. In this way, parent and child accounts can be identified. This spreadsheet probably contains the Account name or Account ID. Each record in a table has a unique record ID. This is what allows tables to be related without having to replicate data.
If you have completed the User import, you can include employee IDs for key roles like Account Owner and Sales Person. The order in which you import the entities is important because of dependencies like these.
The third table to identify is the Contacts table. Contacts, like Soylent Green, are people. They may be associated with an Account, or not. Contacts can be associated with other Contacts. As a rule of thumb, a contact is anyone with an email address. Contacts associated with accounts should include the Account ID to ensure proper linking.
The fourth table you need to identify is Sales. The spreadsheet references data from Sales reports. In a perfect world, you could create a regular synchronization of that data with the CRM. Even if that isn’t possible, you will still need to create a table to store the sales figures. The table would contain the Salesperson ID, Account ID, Product ID, and Amount.
The fifth table you need to identify is the Products table. This is usually maintained in an ERP system. The fields you will want to capture are Product ID, Product Name, and Product Description at the very least.
That is a small example from a very specific report, but even in this example, we find that data captured on a single spreadsheet line is actually sourced from multiple entities. This means you not only have to identify the data sources, but also create an import plan to ensure IDs are established in one table before referencing them in another. For most SMBs, there are usually only a few dozen tables needed to replicate the functionality of the spreadsheets they currently use.
How Do You Ensure CRM Adoption, Data Quality, and Security After Migration?
Besides basic record keeping, many use spreadsheets to extract reports they need to do their jobs. It is important to capture staff reporting needs when moving to a CRM. Each department produces reports, but when the data is in one location, how many of those reports will still be needed? Often, the department feeds its information into a larger report for management. An integrated system can produce this report without aggregating data across multiple data sources.
The next question to ask is how people need the data represented in order to maintain it. Do we display our Contacts’ Last Name, First Name, or First Name, Last Name? Do we display accounts by Account Name or Account ID? Wireframes are mockups of the data maintenance screens people will use daily. It is important to seek user input and consensus when designing this employee-facing aspect of the CRM. If the data is easy to maintain, then you have a better chance of adoption.
And finally, there is the matter of data security. Now that everyone is working with the same data instead of private worksheets, who can update an address? Who can add or delete Accounts? All of these questions need to be considered in a data security plan. Users are assigned security roles, and fields are displayed, hidden, or locked depending on the user’s needs. There are clear processes in place for updating core data and accountability for data hygiene.
There will be many more decisions to be made, but these are the core structural decisions that every evolution from spreadsheets to CRM should follow.