Mailing List Groups

[Mark]

I would like to create a mailing list from our current customer base, but, select on certain criteria, like the date of last purchase.
In other words, we need a list of 2008 customers. I'm a little confused how to do that. Do export the customer list and then delete and trhen re-import?
What do I import? A NEW list? Does this overwrite my current customer list or mailing list, or what?
Thanks!

[JustPoppin]

Did you ever come up with any answers to this? I have the same question - if I have to export customer or mailing lists every time I want to do something and then I import it back, have I broken relationships such as the connection between a customer and his orders and his CRM tickets, etc?

I feel like I've been banging my head against the wall regarding the whole 'communicating with various constituencies' issue for the last 3 weeks.

[WoodSprite]

This is something I've been struggling with as well...would love to know if you ever figured out a solution.

[GonzaloGil]

There is a GROUP editor within the mailing list system, but, its not a visual editor were you can just say "Give me all customers that ordered before X", you have to type in the SQL query to get the customers you are looking for.

There are some examples there, but unless you are good at SQL, its not going to be a simple thing to do.

Our support dept can create a custom group for you, but there is a fee (+/- $30), once that group is created you could tinker with the SQL generated to use it to create other groups as well.

For your particular question, the code you could use is as follows:
(Go to Marketing->Mailing Manager->Group editor)
Click Add group
Give it a name ie. "Customers in 2008"
Then it will prompt you for "SQL Statement", for that paste:

SELECT DISTINCT orders.oemail as email FROM orders where  orders.odate between #01/01/2008# and #12/31/2008# AND orders.order_status NOT IN (5,7) 

You can see here its pretty simple to change the dates, and create other groups for 2007, 2009, or choose customers between 2 dates of your choice.

The "NOT IN (5,7) " means, don't include INCOMPLETE orders (7) and (5) Cancelled orders.

The groups can be much more complicated than that, like, customers that purchased ABC from category XXX, and ordered over $500 over the past 3 months, and live in Florida, but, the more complex the rule the more SQL you'll need to know, or you can have one of our developers create it for you.

[JustPoppin]

When I have been in the Group Editor, I haven't seen any examples. Can you please direct us to them?

Will you provide a field list (for lack of a better description) that lists all the variables we can use in the SQL query? For instance you said (5) is Cancelled Orders - I have no way of knowing that without a directory of the fields/variable names, etc.

Also, will you please address the issue of what happens if we export customer data, edit it outside 3dcart, and then re-import it - would relationships in the database be broken?

If I export Mrs. Jones and all of her contact info and change her apartment number in Excel and then import her record back to 3dcart, did I just create a 2nd Mrs. Jones in my 3dcart customer database or did I overwrite the original Mrs. Jones, keeping database relationships, but now with the new apartment number?

[GonzaloGil]

A simple way to get a full list of the fields is to perform an export, the headers contain all the field names.

The order statuses are the only ones that are referenced by numbers, since you can rename them on the admin, the rest of the fields are pretty self explanatory.

We recently started adding sample groups, so perhaps you don't have a group on your database, you can use the one on my post as your first group.

If you export and import, as long as you keep the email the same, it will UPDATE the customer information. So if you change their apartment #, and you keep their email intact, when you import back in, it will update their apartment # as you expect.

If by chance you change their email, then, yes, it will create a new record. The record we use as key on the customer table is the EMAIL.

There is a difference between using the GROUP EDITOR which basically queries the entire database for whatever you like, vs, actually creating a subscription group and importing/exporting emails into those groups.

Essentially if you are importing emails from your own list, you may want to categorize them into subscription groups (Marketing->mailing->Subscription groups) ie. "CUSTOMER LIST FROM SHOW XYZ", but if you want to make sub groups of customers that purchased from your store, using the Mailing Manager>Group editor is best, because you can make dynamic groups, based on a query, ie. like the example i provided, all customers that purchased between 2 dates.

[JustPoppin]

Thank you for the detail.

So, for instance, if I export my entire customer list and do something like change the 1 to a 0 in the column for mailing list (to remove every customer from mailing list all at once) and then import the ENTIRE list back into 3dcart, because I did not touch the EMAIL field, all of the associations in the 3dcart database such as ORDERS and CRM that are associated with a customer will remain intact?

[GonzaloGil]

That's correct.

A common mistake people do is leave the header, but clear the column, ie. you don't want to change the customer's first name, so you clear the entire column, but, if you leave the header there, when you import the file, you would be setting everyone's first name to blank. So, make sure you don't do that.

[JustPoppin]

That's correct.

A common mistake people do is leave the header, but clear the column, ie. you don't want to change the customer's first name, so you clear the entire column, but, if you leave the header there, when you import the file, you would be setting everyone's first name to blank. So, make sure you don't do that.

Great advice. Thanks!

[WoodSprite]

Thank you, Gonzalo!