help with mysql query

Discussion in 'MySQL' started by terobau, Apr 6, 2012.

0
  1. #1
    hi all, i am trying to find out the customer lifetime value for my online store, i need to separate them in cohorts based on first purchase date
    here is what i have so far, i am a noobie and dont know if this is the right direction:

    SELECT DISTINCT email, billing_name,
    SUM(total) AS total
    FROM `orders` AS a
    GROUP BY email
    ORDER BY orderdate DESC

    what i need to find out for each group(by first order date), what other months did they buy again and how much they spent each time

    please help! thank you.
     
    terobau, Apr 6, 2012 IP
  2. superdav42

    superdav42 Active Member

    Messages:
    125
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    58
    #2
    This might give you what you are looking for:
    
SELECT MONTH(orderdate) as month, email, billing_name,
SUM(total) AS total
FROM `orders` AS a
GROUP BY email, MONTH(orderdate)
ORDER BY orderdate DESC
    Code (markup):
    Should give you an table like:
    
month, email, billing_name, total
1, somebody@example.com, john doe, $143
1, somebodyelse@example.com, jane doe, $13
2, somebody@example.com, john doe, $43
2, somebodyelse@example.com, jane doe, $1403
...
    Code (markup):
    provided orderdate is a mysql date type
     
    superdav42, Apr 7, 2012 IP
  3. Artuurs

    Artuurs Peon

    Messages:
    24
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    This is Easy man! superdav42 ++
     
    Artuurs, Apr 7, 2012 IP
  4. terobau

    terobau Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    thanks superdav42!

    i do have it working half way but i can explain better now on exactly what i am trying to do. and also the second part of my quest still needs to be tackled. this from before:
    'what i need to find out ..., what other months did they buy again and how much they spent each time'

    missing above is my group, but how can i find the other months totals that the group spends? i want to use the first ordered date as a group for now. but later would also like to know the same for every customer.

    here is my revised query:
    SELECT Min(orderdate) as cohort,
    SUM(total) AS total
    FROM `orders`
    GROUP BY email
    ORDER BY Max(orderdate) DESC

    which gives me something like this:
    2011-08-01 811.849994659424
    2012-03-12 68
    2012-03-12 36.4000015258789
    2012-03-12 330
    2011-12-02 67
    2012-03-12 29.7000007629395

    thanks (y)
     
    terobau, Apr 7, 2012 IP