Best practice for importing spreadsheet of people to database of people+products

[cbsteven]

We have a family business (bicycle shop) and we maintain a Filemaker database that contains an entry for every bike purchased. There is a table of customers with contact information, and a related table of bicycles with the bike details. (Filemaker solution is something I rolled myself).

We need to be careful of duplicates because we use the customer information table to send our postcards twice a year with in-store sales information.

Old system: Customer would fill out a paper form with everything upon purchase, and once a month or so an employee would take a stack of forms and enter the data into Filemaker. They would try to be mindful of duplicates, and the system would try to warn of duplicates upon entry.

New system: Customer would be taken to a computer terminal when finalizing purchase and fill out a webform with all the info. Form is hosted by Google and the results go directly into a Google Docs Spreadsheet.

The new system is superior in almost every way. More accurate data, always up-to-date, we can easily send customers post-sale emails, etc.

The spreadsheet will have lots of duplicates by nature, for example if one person buys three bikes for himself and two kids, the spreadsheet will have three rows with the same address information on it.

I figure this is a problem that has been solved before although I am not having luck Googling for it. What would be the best way to import this spreadsheet into the database and not have a ton of duplicate Customer entries coming up?

Or, should I consider ditching the Filemaker database altogether and go to something entirely different? I would like to do this if there is something easy to use out there that already does pretty much what we need. I'd like something that is as hands-off and low-maintenance as possible, as I am the only tech-savvy person at the business and don't want it to rely on my presence.

Edited October 21, 2011 by Lee Smith
Old eyes couldn't read this small text

[David Jondreau]

Why don't you have customers entering data into the Filemaker database rather than into a Google Doc?

There's a handful of ways to deal with these issues. You can stop duplicates on entry, you can combine duplicates later, or you can filter duplicates when exporting to your mailing service.

Anyway you do it, you're going to need to have a properly structured database ( at least tables for Customers, Sales, and Bicycles/Products which Google Docs can't give you ) and you need to really think through the process. It seems a little weird for customers to be entering their own sales information, but I can see the value in the approach. Assuming that...do you have enough repeat customers that it makes sense for them to be issued a "Customer ID" they can use when entering their sales? Do you care if customers see a short list of other customers with similar names and/or addresses? What constitutes a duplicate? Is it an address or address / customer name combo? If a husband and wife each buy a bike should they each get postcards or just one for the household? Maybe those kids should be getting their own postcards?

There's probably a dozen more questions that need answering, but that should get you started ;)

[Matthew F]

I'm curious as to whether you would require this of all customers. I personally wouldn't want to register to buy an inner tube. On the other hand if I was buying a bike under warranty, or for a free service reminder, that would be a different story.

I would think that you need to protect customer privacy and not display names of other customers or allow them to search to find their own entries. You probably need a privacy policy that is available on request.

You could have an employee run the person's name to find out if they are repeat customers. If no record is found then have the customers add their own information directly into Filemaker in a kiosk setup. You would then run a process that transfers this data to your main customer database. You'd need to check for duplicates a couple of different ways (e.g. by sorting by name or address). However, minor duplicates with minor spelling changes will be hard to filter out and will require human supervision.