cubecart v5 Best way to export customer dbase - only customer list

[SimChris]

Hi folks

must be having a duh moment. Trying to do some different flavor data exports of our CC 5.2.13 shop for both posterity, and backups/migrations, paranoia, prior to CC6 move.

Obviously can do full exports / backups of dbase via panel, cron, phpmysql, etc. which we do.

So, I managed to easily do the "order search" from 2009 to today, filter by "order completed", export all as CSV, but obviously this will have duplicate entries for each customer on a line by line basis; seems painful to go through and manually remove duplicates for a customer who has done 100 orders with us..

There doesn't seem to be "export" button when doing a search by date range for all customers.

If I do a 'view all' under customers, there is no export option.

Is there perhaps a hack to add an export option to the customer search, or a plugin to "export all customers" that would work with v5 ?  (and presumably could be adapted to v6 if there is no export customers option in v6 either?). One of those duh things that would have been useful in CC all these years. Export newsletter doesn't help as that does not have all the customer data and we have not been using this system for newsletter push.

Thanks for any guidance on this!  :-)

Really not looking forward to manually editing the CSV/Excel file based on orders to remove dupes with sort by customer name column.

I am super rusty with phpmysql, but if anybody has the "put in this string, and select these fields, dummy..." help, man that would be so awesome. I'm not MySQL/phpmyadmin illiterate, just only have to mess with that maybe once a year, so it's not my normal hang out.

:-)

 

[SimChris]

For the moment, in lieu of any automagical solution, am trying the export SQL, then import into Excel bringing comma delim fields into columns and keeping the '' wraps we need. Then will try to clean up all the non customer record stuff and see if this will work.

I didn't look to see if there is plugin for this, but man that would be sooooooo much easier!  "export customers" .... what a concept!  ;-)

Edited August 29, 2015 by SimChris

[bsmither]

This can certainly be done and I would like the opportunity to solve this with you.

What is it exactly that admin, Newsletters, Export Mailing List tab does not meet with your needs? I understand that this is an email list, so addresses, etc wouldn't need to be considered.

We can certainly enhance this email export to contain whatever else is necessary.

Also, we can create a "Sales Report" that can aggregate sales data onto a single line, each line being unique to a customer_id. (See admin, Statistics, Best Customers tab.)

Finally, the admin, Export Catalogue need not be restricted to exporting inventory. An export plugin can be written to create a report on anything. However, the intent of the Export Catalogue plugins is to allow for outside third-parties to call for these reports (using a passcode). Sales Reports (if I recall) do not.

Finally, the Order Summary database table records the customer_id because everything else is the state of affairs at the time of the order. The customer may have changed addresses or even their name between orders. So, coalescing orders based on customer name may still leave hidden duplicates, but by email address perhaps less so, as even a customer can change the login email address.

[SimChris]

Hi, well, newsletters doesn't work for us to export customers as it doesn't have the full customer record, and we did not "auto subscribe" everybody to newsletters, so most customers not in that dbase (I looked). :-) .... think of it this way, what if we needed to import the full customer dbase into another system, such as another ecom system entirely if CC somehow went kabooky (not expected, but being paranoid ... we'd want a full customer dbase export that could be imported into anything else with all the normal customer fields for the ID, email, first, last, address fields, zip, country, shipping, billing (as in some case the company is one thing, billing is another), phone(s).

Export "by unique" email works, as no client can have two accounts with same email; so any email account would have latest iteration of any data, which is why the "order export" method not ideal for full customer export.

I can export all orders without any trouble, just not "all customers" (only about 1600 customers, so not billions).

I am actually in the main SQL export, in Excel right now

`CubeCart_customer`

I need to get this done over the weekend, so let me play with this a little longer to see if I can at least get this one darn thing done. Then go from there.

We do need the FULL customer record -- hence why newsletters option not useful (tried that first and went... oh yeah, doh), as only 5% of customers are in there as we ended up hiding the option if memory serves. Meaning, nobody after 2010 'opted in' -- and having the "optin" -- would only save those who optin -- would not save a complete customer record. Which would be nice option to have with the store, in general.

If you come up with a plugin to "export customers" -- would love to buy that :-)

Basically a full export of the customers, ala the way one can export sales from the sales history section.

Sorry, that might have been redundant. Too much coffee, not enough lunch ;-)

oh - and other reason we need FULL customer export is we are also going to do a postcard mailing to all past clients 2009-2015. :-)

[SimChris]

---------

ok, duh... this won't work via full data dump,

-- Table structure for table `CubeCart_customer` as it's missing the addresses and whatnot. Doh. ---------- Looking at the dbase.... seems like export of this: 'CubeCart_order_summary' but then filter to latest order per customer ID to get latest customer info and billing ? thanks for any feedback ... I realize this is an untypical newbie type post from me ;-)

Edited August 29, 2015 by SimChris

[bsmither]

I was recalling something about the email export list being comprehensive - all customers were included in the list - and that CubeCart would not use this list. But provided to the admin for some external activity.

But now I am seeing, with CC6, this is not the case. Maybe not with CC5 either. I may be recalling this as an export from CC3.

(I just now found something weird. I had created a couple of "Sales Report" export modules, but they aren't showing up in Sales Reports. Ther are being shown in the Customer List. Hmm... I will have to look into that.)

Anyway, if you need this really soon, we can develop a SQL query that will get you the dump you need. It will be quick and dirty. We can then make a polished effort in a plugin.

[ayz1]

 

Really not looking forward to manually editing the CSV/Excel file based on orders to remove dupes with sort by customer name column.

In Excel on the Data tab there is a remove duplicates feature. I use it a lot. Would that help?

[lexijade]

I don't see any option to export orders. Also I definitely need a way to export complete customer list as well. Thanks!