email address exporter

[athena]

Has anyone done this or does anyone know how to get emails from the database by sign-up date or by customer number?

For instance: I used the Email Address Exporter module to download everyone who subscribes to the newsletter. The following month, I did it again to add the new subscribers but because the list comes out alphbetically I can't tell who I already have on my newsletter list - which I since edited to exclude those who changed their mind and unsubscribed when I sent out my last newsletter.

Please don't tell me to use a service...I"ve been that expensive route and am trying to do this all inhouse.

Can email exporter be tweaked to collect by date or customer number?

[DrByte]

You have a few options:

1. When a customer tells you they want off the list, either you or they can go into their account and unsubscribe them from the list.
2. If you want to alter the order in which records are exported, you can edit the SQL query in the appropriate record of the query_builder table, and change the ORDER BY clause to some other value.
3. You could keep a list of "exclusions" and then just be sure to remove them from the exported list after you export, before using the list for mailing.

[athena]

Thanks for answering so fast...

1 & 3 I've been doing manually but still the list over flows from the previous month and it gets tedious. For instance the latest download of active newsletter subscribers for the last 3 months is 247 but we've only had 200 new customers since last newsletter so I know I must have some of these emails already. I do set GroupMail to not repeat email address but the free version I'm using will not let me export the edited list for safe-keeping and so I need to keep a seperate accurate list of subscribers as we also get subcribers from outside of zen.

So that leaves me with option #2 I believe. I don't know how to do the following (2):

2. If you want to alter the order in which records are exported, you can edit the SQL query in the appropriate record of the query_builder table, and change the ORDER BY clause to some other value.

[athena]

would I actually be editing admin/email_export.php ? I can't find ORDER BY there...or is this a sql statment I would run in the admin/tools/Install SQL Patches window? Not real comfortable running squl queries but have done it a few times.

[DrByte]

You would have to edit raw SQL data in the database, inside the query_builder table itself.

[athena]

found this from another thread and will work on it:

INSERT INTO query_builder (`query_id`, `query_category`, `query_name`, `query_description`, `query_string`, `query_keys_list`) VALUES ('', 'email,newsletters', 'All customers who have purchased product #103', 'All customers who have ever purchased product #103, ignoring newsletter-subscription status.', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o, TABLE_ORDERS_PRODUCTS op WHERE c.customers_id = o.customers_id AND o.customers_id = c.customers_id AND o.orders_id = op.orders_id AND op.products_id = 103 GROUP BY c.customers_email_address ORDER by c.customers_lastname, c.customers_firstname ASC', '');

[DrByte]

You will find SEVERAL queries listed in that table.
ONLY edit the ONE that you are selecting from the pulldown list when you're doing your email address exports.
And you do NOT need to "INSERT" any new records.

[athena]

I'm not getting it I guess.

Where do I find ORDER BY? I guess what I need is a sql statment I would run in the admin/tools "run sql patches" window.

Can it start with "select"?

would i be creating a new audience list?

[athena]

sorry drbyte...did not see that post above...

[athena]

ok,

I changed in my MEMO > text field in my database...at least i think i changed it...still producing same order with no id:

select c.customers_id, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = '1' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_id ASC