I have what seems to me a very complicated problem with a customer database our small business uses. Please excuse my lack of technical language as I’m not an Excel or database aficionado.
Basically, we want to transfer this database in to a Customer Relations Management software called Zoho (or any other CRM, really) — but the data is what I would call “multi-layered”… and it needs to be “flattened” if it’s going to fit into this CRM correctly.
Here’s a small snippet of the spreadsheet:
http://i.imgur.com/nCL2zdV.jpg
As you can see, there are multiple entries for a single customer based on separate purchase dates
So Brad Jones down there has purchased from us three separate times, and has three different sets of items for each purchase. He also has a different Sales Rep for each of those purchases.
Now what we want is to essentially merge all of Brad Jones purchase information into one line so that his contact information appears only once and his purchases are displayed in a single line. So I’m thinking it would look like this:
http://i.imgur.com/7d1ETy4.png(SD standing for sale date, ID standing for install date here)
Now I know this seems a little counterintuitive, but the problem is that the CRM we’re exporting to doesn’t seem to allow the transfer to multiple purchase orders attached to one name (or at least I haven’t figured out how to do that). So my only solution seems to be to lump Sale Date, Install Date, Sales Rep and Items into one field.
This totally sucks as far being able to do reports and data analysis, but the reason this still works for us is because we’re able to search the whole CRM as we would any search engine, so it would be possible to find items attached to customers rather quickly.
So my question is really two fold:
1) Is there an different/optimal way to manage/export this data into a CRM or similar DB?
2) HOW ON EARTH would I manage to do the data “flattening” that I suggest in the above example?
Thank anyone in advance willing to tackle—or at least take a stab at—this problem.
Bookmarks