Client lifetime with mysql php
I spent a week trying to figure this out, it works somewhat by combining things from different sources, but doesn't fully work yet.
Basically, I have a table of orders in which I try to group customers by their first order date, and then show the total spent by that group so far.
This is my SQL query:
SELECT DISTINCT email, billing_name,
FORMAT(SUM(total),2) AS total,
DATE_FORMAT(MIN(orderdate), '%Y-%m') AS firstorder,
DATE_FORMAT(MAX(orderdate), '%Y-%m') AS lastorder
FROM orders
GROUP BY email
ORDER BY firstorder ASC
and with PHP I do:
$rows = array();
while($row = mysql_fetch_array($query))
$rows[] = $row;
foreach($rows as $row) {
$currMonth = $row['firstorder'];
$total += $row['total'];
if ($currMonth != $prevMonth) {
echo $currMonth.' = $'.$total';
$prevMonth = $currMonth;
$total = 0;
}
}
this gives me a list like:
2010-05 = $230.49
2010-06 = $557.32
2010-08 = $223.38
but the numbers don't add up, what am I doing wrong? and how can I show how much the group has spent in other months? this is how I ultimately want to show the data, http://www.quickcohort.com/
Please help! Thank!!
source to share
Depends on what you really need and what your data is.
If the data looks like this:
email |BILLING NAME|Total |OrderDate
----------------------------------------------
john@gmail.com |John Smith |200.00 |15/05/2010
john@gmail.com |John Smith | 15.49 |19/10/2010
john@gmail.com |Rico Swavez | 15.00 |10/08/2010
jane@gmail.com |Jane Doe |250.00 |23/06/2010
jane@gmail.com |Jane Doe |307.32 |27/10/2010
juan@gmail.com |Juan Valdez |223.38 |30/08/2010
Then...
SELECT email, billing_name,
FORMAT(SUM(total),2) AS total,
DATE_FORMAT(MIN(orderdate), '%Y-%m') AS firstorder,
DATE_FORMAT(MAX(orderdate), '%Y-%m') AS lastorder
FROM orders
GROUP BY email, billing_name
ORDER BY firstorder ASC
Will return
EMAIL | BILLING NAME |TOTAL |FIRSTORDER | LASTORDER
------------------------------------------------------------
john@gmail.com | John Smith |215.49|2010-05 | 2010-10
jane@gmail.com | Jane Doe |557.32|2010-06 | 2010-10
john@gmail.com | Rico Swavez | 15.00|2010-08 | 2010-08
Juan@gmail.com | Juan Valdez |223.38|2010-08 | 2010-08
First run your query in mysql to get the results you want? if not, then the problem is with SQL, not PHP. If SQL returns what you want, then the problem is in PHP
source to share
The following query should do the trick:
SELECT
FORMAT(SUM(z.'totalSpent'),2) AS cohortRevenueToDate,
z.'firstOrderMonth',
GROUP_CONCAT('email')
FROM
(
SELECT
'email',
SUM('total') AS totalSpent,
DATE_FORMAT(MIN('orderdate'), '%Y-%m') AS firstOrderMonth
FROM 'orders'
GROUP BY 'email'
) AS z
GROUP BY z.'firstOrderMonth'
ORDER BY z.'firstOrderMonth' ASC
I've included GROUP_CONCAT in case you're interested in each cohort composition.
source to share