|Getting orders off the net and into a database?|
Talking to other people with small ecommerce sites, the #1 dirty secret of ecommerce seems to be that many merchants are having to enter the data for each order by hand, once or even several times.
Ideally of course you'd have a central database or shopkeeper program and the orders would be tranferred into it automatically (or in batches, with just a little handling necessary). Then if you were doing offline authorization the CC info would be automatically imported into the CC program.
To do this gracefully, the orders have to arrive in a format that lends itself to scripting, perferably with some sort of delineators (if thats the right word) that makes it easy to get them into the right slot. Most of the shopping carts I've seen don't do this - they just cram it all together.
I'd be interested in knowing how others are handling this.
I'd say it depends on the volume. If you only get a small handful of orders, is it worth the time & expense to setup the automation? Maybe, maybe not.
I've got a small site that uses the free mal's cart. We're just now getting to average 1 order per day so it's still not worth automating yet. Mal's does offer a tool that can keep order info in a db. Sounds similar to what you're talking about. I just don't need it yet.
We don't actually take "orders," but we do deal with customer inquiries which need to be put into a database. I've been doing this successfully all of about two weeks, so there are no doubt some here with lots more expertise.
That said, we're parsing info from our e-mail form and - without any intervention on our part - creating a new contact in Outlook with all the relavant data and simultaneously appending the info to a .csv database. I was able to go back and pull info from several years of inquiries and create contacts, which makes life much easier. new inquiries are parsed automatically.
I am using an Outlook add-in called CaTrap. A few caveats:
1) You need to be using Outlook
2) It won't work on Outlook XP (custom actions are broken in Outlook XP) unless you are also running Windows XP with the newest updates to Outlook. I have been told that update restores custom actions for XP users, but I have not yet tried to verify that
3) Like a lot of Outlook add-ins, Ca-Trap has trouble closing Outlook - the program looks closed but actually leaves processes running. The only problem is that if you automatically back up your data overnight, your Outlook file will be busy and won't get backed up. Restarting solves this, but it is a less than elegant solution.
4)If you have custom fields not found in a standard Outlook contact, you will need to be pretty comfortable with Outlook to successfully implement and manage your new contact form.
There are other programs that will parse data from e-mail as well - a more universal solution might be Message Parse, which works with several e-mail clients (including Outlook, Outlook Express, Netscape, and Eudora)and also has a POP engine so it can directly query a mailbox. Several bulk e-mail programs have message parsing capability built in, though I have not spent enough time with any of them to make recommendations.
The bottom line is that there are lots of solutions to your problem. What will work best for you depends on what data are you are dealing with and what program you want to send it to. But any form that sends data back to you on separate lines with some sort of ID in front of the data should provide you with enough to automate your process. Good luck.
What you are doing, though, may be easier than what I am trying to do. I've played with a dozen or two email parsing programs, and just about all of them can only handle the headers and treats the body as one field - they cannot go into the body of the message and extract data and sort it. Fine if you are trying to sort a few years of email by subject or sender, but not much use for parsing a typical order, especially because the orders as they come from most shopping carts don't delineate the fields so it is hard for a parser to know where to put what. If you know each field is going to appear on its own line, and blank fields will always be represented by a full, empty line it can be done, or if the fields come labled , but otherwise its a mess.
Actually, most of the data I pull comes from the message body - name, address, arrival/departure dates, number of travelers, etc. I would think (while having absolutely no expertise to back the thought up;)) that you should be able to find a shopping cart that sends you something other than a jumble of numbers - even if there is a just a comma separating the fields you can still parse it pretty easily.
And although I did not mention it earlier, I would think the security of any data coming to you from your cart (credit card numbers?) would be something to worry about no matter what you do with the data once you get it.
But again, I don't actually use a shopping cart. I am, however, successfully and very happily parsing customer data.
I've built several databases that do what you mention. We use MySQL to accomplish it and then built a custom Admin tool which allowed the user to export the data (orders not yet processed) into a txt file on thier end. All done under an SSL connection with the db and all of the data within the db is encrypted. The txt file format was dependent upon the program the client was using for CC processing. In some cases, the CC processor won't give you the details of what the fields need to be because they have their own program they want you to buy (at $500 and up).
The store I've setup doesn't store CC numbers but we store customer information and order information for bookkeeping.
What I did was write a cron job that emails the orders as a txt file attachment nightly to the person doing the accounting. They just import the txt file into excel and then from there, into whatever they want to use.
An "order" table is only an "activity" table.
We use PHP to store "selected" customer info into an order table in a MySQL database. "Selected" being no credit card details that would be available to anyone who hacked their way into that database. We carry a unique session number for each visitor throughout the order process to prevent duplicates entered into the database. A no cache/expiring "send order page" is targetted onSubmit of the order. It (a)produces an online order confirmation/thank you page, (b)e-mails us a formatted order/packing slip, (c)e-mails the customer a formatted order confirmation/shipping advice, and (d)enters "selected" order details into that order table of the database.
However, this order table has a very limited use. There are many, many cirumstances that arise making the captured order data inaccurate. Customers cancel orders, orders later changed by the customer(via e-mail, phone-in), orders we cannot fulfill, fraudulent orders, orders not accepted owing to confirmation e-mail returned to us (bogus e-mail address on order), orders not processed owing to failure of credit card acceptance, orders refused by customer when shipped, orders returned at a later date, etc.,etc.
Our order table is just used to get a visual summary of "activity" on the site during the day. An in-office, off-line customer database is what is actually maintained.