Page 1 of 2
Business year end for accounting
Posted: Sat Apr 09, 2022 7:38 pm
by 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.
Re: Business year end for accounting
Posted: Mon Apr 11, 2022 1:02 pm
by ReneH4
Zipur reporting tool can do that. I use it on a daily basis, very powerfull.
Re: Business year end for accounting
Posted: Mon Apr 11, 2022 2:17 pm
by 14Steve14
ReneH4 wrote: ↑Mon Apr 11, 2022 1:02 pm
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.
Re: Business year end for accounting
Posted: Wed Apr 27, 2022 5:11 pm
by 14Steve14
14Steve14 wrote: ↑Mon Apr 11, 2022 2:17 pm
ReneH4 wrote: ↑Mon Apr 11, 2022 1:02 pm
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.
Re: Business year end for accounting
Posted: Wed May 04, 2022 7:46 am
by 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).
Re: Business year end for accounting
Posted: Thu May 05, 2022 4:03 pm
by 14Steve14
burt wrote: ↑Wed May 04, 2022 7:46 am
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.
Re: Business year end for accounting
Posted: Thu May 05, 2022 4:03 pm
by 14Steve14
burt wrote: ↑Wed May 04, 2022 7:46 am
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.
Re: Business year end for accounting
Posted: Fri May 06, 2022 6:05 am
by 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...
Re: Business year end for accounting
Posted: Fri May 06, 2022 6:15 am
by ecartz
burt wrote: ↑Fri May 06, 2022 6:05 am
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
Re: Business year end for accounting
Posted: Fri May 06, 2022 8:49 am
by 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.