Magento SQL Repeat Customers vs New Customers

First off get the total number of customers:

SELECT COUNT(*) AS grand_count FROM(
SELECT customer_email FROM mg_sales_flat_order
WHERE mg_sales_flat_order.status NOT LIKE ‘canceled’
AND mg_sales_flat_order.status NOT LIKE ‘closed’
AND mg_sales_flat_order.status NOT LIKE ‘fraud’
AND mg_sales_flat_order.status NOT LIKE ‘holded’
AND mg_sales_flat_order.status NOT LIKE ‘paypal_canceled_reversal’
GROUP BY customer_email

 

Then get the number of new customers:

SELECT COUNT(*) AS grand_count FROM(
SELECT customer_email FROM mg_sales_flat_order
WHERE mg_sales_flat_order.status NOT LIKE ‘canceled’
AND mg_sales_flat_order.status NOT LIKE ‘closed’
AND mg_sales_flat_order.status NOT LIKE ‘fraud’
AND mg_sales_flat_order.status NOT LIKE ‘holded’
AND mg_sales_flat_order.status NOT LIKE ‘paypal_canceled_reversal’
GROUP BY customer_email HAVING COUNT(*) = 1
) s

 

Then get the number of repeating customers:

SELECT COUNT(*) AS grand_count FROM(
SELECT customer_email FROM mg_sales_flat_order
WHERE mg_sales_flat_order.status NOT LIKE ‘canceled’
AND mg_sales_flat_order.status NOT LIKE ‘closed’
AND mg_sales_flat_order.status NOT LIKE ‘fraud’
AND mg_sales_flat_order.status NOT LIKE ‘holded’
AND mg_sales_flat_order.status NOT LIKE ‘paypal_canceled_reversal’
GROUP BY customer_email HAVING COUNT(*) > 1
) s

 

You can then get the outputs of the SQL to calculate your percentages with something like the below:

echo ($repeatcustomers/$totalcustomers)*100 . “%”;

echo ($newcustomers/$totalcustomers)*100 . “%”;

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.