Magento SQL Repeat Customers vs New Customers

Introduction

Understanding the composition of your customer base is crucial for effective marketing and sales strategies. This guide will show you how to categorize customers into new and repeating groups using SQL queries in Magento.

Step-by-Step SQL Queries

1. Total Number of Customers

To get the total number of unique 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
) AS total;

2. Number of New Customers

To find the number of customers who have placed only one order:

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
) AS new_customers;

3. Number of Repeating Customers

To count customers who have placed more than one order:

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
) AS repeat_customers;

Calculating Percentages

After retrieving the data, use PHP to calculate the percentage of new and repeating customers:

<?php
// Assuming $newcustomers and $repeatcustomers are obtained from the above queries
echo "Percentage of Repeating Customers: " . ($repeatcustomers / $totalcustomers) * 100 . "%";
echo "Percentage of New Customers: " . ($newcustomers / $totalcustomers) * 100 . "%";
?>

Conclusion

By performing these queries, you can gain valuable insights into the behavior and distribution of your customer base in Magento. This information is crucial for developing targeted marketing campaigns and improving customer retention strategies.

Further Assistance

If you have any questions or need further clarification on implementing these SQL queries and calculations, please leave a comment below or contact support.