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…
Matthew is the Co-Founder & CEO of Demac Media, a Commerce Agency serving mid-market to enterprise merchants with online and in-store technology products. Demac Media's team of 80 consists of Commerce Strategists, UX Designers, Front End Developers, and Engineers, all with experience in guiding and delivering commerce solutions for 80 retailers in North America.