Calculating Lifetime Value of a Customer in Magento

We talk a lot about calculating Lifetime Value of a customer in eCommerce. It’s actually quite significant when making decisions around marketing / advertising spend and more specifically the return you get on a channel (i.e. – PPC). But how do you actually calculate the Lifetime Value of a customer in Magento? Well, we’ve figured out a query to find that out. Running this report in Magento is a bit of a pain, but luckily a simple MYSQL query can solve your problems. The following MYSQL query gives you a list of customer emails, names, and total purchase amount over the life of the customer (since the dawn of time!).

The Query:

SELECT DISTINCT customer_email, customer_firstname, customer_lastname,
SUM(subtotal_invoiced) AS Total
FROM `sales_flat_order` AS a
GROUP BY customer_email
ORDER BY SUM(subtotal_invoiced) DESC

And the resulting output…