Calculating Average Orders Per Customer in Magento

Introduction

This guide explains how to calculate the average number of orders per customer in a Magento database using SQL queries and PHP.

Step 1: Retrieve Total Number of Orders

Start by running a SQL query to get the total number of orders excluding certain statuses like ‘canceled’ and ‘fraud’. This query will run against the Magento order table:

$result = mysqli_query($con, "
    SELECT COUNT(mg_sales_flat_order.status) AS grand_count
    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'
");
$row = mysqli_fetch_array($result);
$totalorders = $row['grand_count'];

Step 2: Retrieve Total Number of Customers

Next, calculate the number of unique customers by querying the distinct count of customer emails under similar order status conditions:

$result = mysqli_query($con, "
    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
    ) s
");
$row = mysqli_fetch_array($result);
$totalcustomers = $row['grand_count'];

Step 3: Calculate Average Orders Per Customer

Finally, calculate the average number of orders per customer by dividing the total number of orders by the total number of customers:

$decimal_places = 2;
$average_orders = number_format($totalorders / $totalcustomers, $decimal_places);
echo "Average orders per customer: $average_orders";

Conclusion

This method provides a clear view of how frequently your customers are ordering. Such metrics are vital for understanding customer engagement and developing strategies to increase sales.

Further Assistance

If you encounter any issues or have questions about these calculations, please leave a comment below or contact support for additional help.

Magento SQL Average Total Orders Per Customer