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 . “%”;

Magento | | No Comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

« »