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!).
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…