Business year end for accounting

[14Steve14]

After a bit of advice to find out what other business do at their year end for accounting.

Throughout the year we delete inactive customers online with GDPR. These are customers that have signed up and not purchased anything. We generally delete them after 4 months.

What we now have is 24000+ customers, some of which have not bought from us for 7 or more years. What is the best way to remove these customers. We only need to keep customer data for 6 years online with UK legislation.

Is there something simple we can do to remove customers who have not bought anything in 7 years. May be there is currently an addon that may do what we want but I can't see anything in any description.

[ReneH4]

Zipur reporting tool can do that. I use it on a daily basis, very powerfull.

[14Steve14]

Zipur reporting tool can do that. I use it on a daily basis, very powerfull.

I will contact Preston and see whether this addon will do as I want. Thanks for the suggestion.

[14Steve14]

Zipur reporting tool can do that. I use it on a daily basis, very powerfull.

I will contact Preston and see whether this addon will do as I want. Thanks for the suggestion.

The addon does not really do what I want apparently. Preston did send through some untested SQL code to try on the database and it finds a load of customers which I am reluctant to delete just in case it is not doing what it should. Already had to upload the backup just in case it did something funny.

[burt]

I wonder if an email blast would work with these customers ? Maybe something like

Code: Select all

Hey {NAME}, we noticed you haven't bought anything from us for X years - we're still online and thriving and hope you are too!  If you are no longer interested in our services, you can use the new Europe wide privacy rules to delete your account with 1 click!  Simply log in at www.yourshop.co.uk/gdpr.php - or if you need our help to do this please email us at a@b.com and we'll be happy to help!

One of the VIP codes I made was some updates for the GDPR page, one of which is a "delete me" button - there is also a "download all" button as well if memory serves.

Would the benefit of doing this (potential sales) outweigh the negatives ("how dare you email me" etc).

[14Steve14]

I wonder if an email blast would work with these customers ? Maybe something like

Code: Select all

Hey {NAME}, we noticed you haven't bought anything from us for X years - we're still online and thriving and hope you are too!  If you are no longer interested in our services, you can use the new Europe wide privacy rules to delete your account with 1 click!  Simply log in at www.yourshop.co.uk/gdpr.php - or if you need our help to do this please email us at a@b.com and we'll be happy to help!

One of the VIP codes I made was some updates for the GDPR page, one of which is a "delete me" button - there is also a "download all" button as well if memory serves.

Would the benefit of doing this (potential sales) outweigh the negatives ("how dare you email me" etc).

Food for thought there Gary. I currently do not have that button installed, but may try what you are suggesting. We just need to be able to get the customers email, name and whether they are an email subscriber or not from the database. If we could get the day they last bought something that may be good as that could get a mention in the email. We could then send a nice email through Mailchimp and like you say, it may get a response, a few sales and if people then do not click the email, or open the email, we know what to do with them. That sort of reporting is available in Mailchimp as standard so that's not a worry.

Will have to look into whether GDPR would allow this if they have never signed up to be contacted. If its possible I may have to contact Preston and see if he has any ideas and can come up with the sql or something. I hate sql, just dont understand it.

[14Steve14]

I wonder if an email blast would work with these customers ? Maybe something like

Code: Select all

Hey {NAME}, we noticed you haven't bought anything from us for X years - we're still online and thriving and hope you are too!  If you are no longer interested in our services, you can use the new Europe wide privacy rules to delete your account with 1 click!  Simply log in at www.yourshop.co.uk/gdpr.php - or if you need our help to do this please email us at a@b.com and we'll be happy to help!

One of the VIP codes I made was some updates for the GDPR page, one of which is a "delete me" button - there is also a "download all" button as well if memory serves.

Would the benefit of doing this (potential sales) outweigh the negatives ("how dare you email me" etc).

Food for thought there Gary @burt . I currently do not have that button installed, but may try what you are suggesting. We just need to be able to get the customers email, name and whether they are an email subscriber or not from the database. If we could get the day they last bought something that may be good as that could get a mention in the email. We could then send a nice email through Mailchimp and like you say, it may get a response, a few sales and if people then do not click the email, or open the email, we know what to do with them. That sort of reporting is available in Mailchimp as standard so that's not a worry.

Will have to look into whether GDPR would allow this if they have never signed up to be contacted. If its possible I may have to contact Preston and see if he has any ideas and can come up with the sql or something. I hate sql, just dont understand it.

[burt]

As always with snippets of code, this is UNTESTED ... use phpmyadmin in both cases...

This should be a list of buyers with date of last purchase...ordered by oldest;

Code: Select all

SELECT customers_name, o.customers_email_address, date_purchased 
FROM orders o 
ORDER BY date_purchased

This should be a list of customers who have never ordered;

Code: Select all

SELECT customers_firstname, customers_lastname, customers_email_address 
FROM customers 
WHERE customers_id 
NOT IN (SELECT customers_id FROM orders)

That'll get you some of the way I guess...

[ecartz]

Code: Select all

SELECT customers_firstname, customers_lastname, customers_email_address 
FROM customers 
WHERE customers_id 
NOT IN (SELECT customers_id FROM orders)

An outer join is probably more efficient:

Code: Select all

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address 
FROM customers c LEFT OUTER JOIN orders o ON c.customers_id = o.customers_id
WHERE o.orders_id IS NULL

[14Steve14]

Thanks @burt and @ecartz

Both options produced an output which was good. Many thanks.

The full list of all customers and the date of the order is huge and needs to be refined to only show the last order date of each customer I think, so shall look into learning more about SQL.

The second bit of sql gives the same output as the inactive customer addon that I use to delete customers after 4 months. The addon does show whether the customer is signed up for a newsletter so we could email them first to explain what we are doing and why, and that may get a response or sale. Will give it a try.