Looking for a way to find a list of customers who have not purchased since a specificdate

[♥14steve14]

Using Phoenix 1.7.11

I am trying to find a way of listing all customers who are signed up on my website, but who have not purchased anything in the last 6 years. This is something I am thinking about to comply with GDPR and not keeping data or information longer than we need to. It may also remove lots of unneeded customer info from the database

I have SQL to find a list of customers listed by last order date but need a way to refine that list to show those that have never bought from a specific date. The SQL code I have is

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

Is there any way of adapting that so that it shows customers with no orders after a certain date.

One way I have thought of is to find the last order date for each customer, but have no idea if that is even possible in SQL

[YePix]

the magic word is "between"

 

  if (isset($_GET['start_date'])) {
    $start_date = $_GET['start_date'];
  } else {
    $start_date = date('Y-m-d');
  }
  if (isset($_GET['end_date'])) {
    $end_date = $_GET['end_date'];
  } else {
    $end_date = date('Y-m-d');
  }

.

SELECT o.customers_id, o.customers_name, o.customers_email_address, o.date_purchased 
FROM orders o where o.customers_id = '" . (int)$customers_id . "' and o.date_purchased between '" . $start_date . "' and '" . $end_date . " 23:59:59' 
ORDER BY o.date_purchased

.

<?php echo tep_draw_form('date_range', 'your_site.php', '', 'get'); ?><?php echo tep_hide_session_id(); ?>
<div class="col-sm-6 mb-3"><?php echo '<a href="javascript:document.forms[\'date_range\'].submit();"></a>'; echo 'startdate' . ' ' . tep_draw_input_field('start_date', $start_date, 'class="w3-input w3-border" onchange=\'this.form.submit();\'');?></div>
<div class="col-sm-6"><?php echo '<a href="javascript:document.forms[\'date_range\'].submit();"></a>'; echo 'enddate' . ' ' . tep_draw_input_field('end_date', $end_date, 'class="w3-input w3-border" onchange=\'this.form.submit();\'');?></div>

try and adjust.

Edited May 6, 2022 by YePix

[Jack_mcs]

3 hours ago, 14steve14 said:

I am trying to find a way of listing all customers who are signed up on my website,

If you just want to delete them, my Database Optimizer addon will do that. If you want a list and can't figure it out then you could alter its code to display rather than delete.

[♥14steve14]

2 hours ago, Jack_mcs said:

If you just want to delete them, my Database Optimizer addon will do that. If you want a list and can't figure it out then you could alter its code to display rather than delete.

Jack, thanks for that. I used your addon on the last sti=ore I had, but not on the latest one. I don't want to delete them without first checking that everything is ok, and that they are not also on our mailchimp mailing list. If they are on our mailing list I was planning on sending them one last email to let them know what we were doing with their data and why we were doing it. Hopefully that may stir them into buying something again. If nothing else it will remove years worth of data from the database.

[Jack_mcs]

@14steve14Assuming there is a mailchimp table with email addresses in it, this should return all customers that are in the Mailchimp table and have not placed an order in the last 6 years. 

Select * from
 customers c left join
 mail_chimp_email_log mc on c.customers_email_address = mc.email_address
 WHERE c.customers_id NOT IN (select o.customers_id from orders o where date_purchaced > DATE_SUB(NOW(),INTERVAL 6 YEAR)) 

 

[YePix]

Am 6.5.2022 um 10:42 schrieb 14steve14:

Using Phoenix 1.7.11

I am trying to find a way of listing all customers who are signed up on my website, but who have not purchased anything in the last 6 years. This is something I am thinking about to comply with GDPR and not keeping data or information longer than we need to. It may also remove lots of unneeded customer info from the database

I have SQL to find a list of customers listed by last order date but need a way to refine that list to show those that have never bought from a specific date. The SQL code I have is

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

Is there any way of adapting that so that it shows customers with no orders after a certain date.

One way I have thought of is to find the last order date for each customer, but have no idea if that is even possible in SQL

 

Edited May 8, 2022 by YePix

[♥14steve14]

On 5/7/2022 at 2:17 PM, Jack_mcs said:

@14steve14Assuming there is a mailchimp table with email addresses in it, this should return all customers that are in the Mailchimp table and have not placed an order in the last 6 years. 

Select * from
 customers c left join
 mail_chimp_email_log mc on c.customers_email_address = mc.email_address
 WHERE c.customers_id NOT IN (select o.customers_id from orders o where date_purchaced > DATE_SUB(NOW(),INTERVAL 6 YEAR)) 

 

Again, thanks Jack. I hate SQL and never seem to get it to work without a jump start form someone that ids in the know.

[Jack_mcs]

Glad I could help. 😊