mysql - SQL Repeated entries by time -
i have database tracking date transaction takes place, in addition unique buyer transaction corresponds - trying @ reservations resulted in customer purchasing again @ later date. right now, code , sample output below shows me customers repeat customers count of buyer_id, want able to see purchases (reservations) resulted in same customer purchasing again @ later time (not earlier in time, case using simple "count").
select r.id reservation_id, r.created, r.buyer_id, count(r.buyer_id) reservations r group r.buyer_id order reservation_id reservation_id created buyer_id count(r.buyer_id) 3 2007-08-14 18:28:38 438 1 7 2007-09-19 12:29:52 474 2 8 2007-09-19 13:14:54 476 1 9 2007-09-20 10:22:52 477 1 10 2007-09-25 15:27:45 485 3 11 2007-09-26 20:56:25 474 2 12 .... etc
the goal able pull additional data each reservation , see factors of service have effect on customer coming repeat purchase. in case above, buyer #474 purchased twice, want able distinguish first purchase (when he/she did indeed come purchase again, 2nd , final purchase) second purchase (after no other purchases made buyer #474). in case, goal have output row shows:
reservation_id created buyer_id count(r.buyer_id) returning 3 2007-08-14 18:28:38 438 1 0 7 2007-09-19 12:29:52 474 2 1 8 2007-09-19 13:14:54 476 1 0 9 2007-09-20 10:22:52 477 1 0 10 2007-09-25 15:27:45 485 3 1 11 2007-09-26 20:56:25 474 2 0 12 .... etc
i.e., showing how customer 474's id not show again after reservations_id 11. in excel have huge amount of rows , excel can't handle functions on such large dataset.
any or suggestions appreciated.
this long comment.
what trying called recurrent event analysis. in particular, part of branch of statistics/data mining called "survival analysis".
your approach (which possible in sql) leads biased results , biased conclusions, simple reason first purchase long ago has greater chance of returning first purchase yesterday.
my response questions never read 1 of books. however, book "data analysis using sql , excel" has 2 chapters on survival analysis. these can understand such time-to-event problems, practical perspective.
Comments
Post a Comment