Identify customers ordering on multiple dates


I am trying to build a beast mode calculation to isolate customers that have ordered based on certain conditions.

My dataset contains all customer ordering activity, each customer is identified by a unique user ID. Customers will often order multiple times each week. Each customer order is assigned a unique order ID and it is date and time stamped.

I want to isolate customer activity to only show those who have ordered in a set period since todays date (eg- past 7 days) AND their last order before that was 12 months prior to that date.


I am unsure if this is best achieved via Beast mode or if I am better to filter/ restrict the data via ETL.

Any help would be greatly appreciated.


Example of data is below:

115510585741-Nov-16 0:07:01155MollySmith
215510585761-Dec-16 0:26:17155MelissaJones
315510585271-Jan-17 0:31:28155PeterRoberts
4238.810585831-Dec-17 3:53:22238.8PatrickDavies
1449.710585871-Jan-18 7:03:25449.7MollySmith
213510577031-Dec-17 9:03:15135MelissaJones
3106.810565141-Nov-17 9:30:56106.8PeterRoberts
41556203851-Nov-17 9:41:35155PatrickDavies



Best Answer

  • Valiant
    Answer ✓

    So I think you would be be served by putting this into an ETL.


    I would break it into the following:


    (Grab Userids who've ordered greater than 12 months ago)

    Transform 1:

    SELECT DISTINCT `Userid` FROM OrdersTable WHERE `submitted_at` < DATE_SUB(curdate(), interval 12 month)


    (Grab Userids who've ordered in past 7 days)

    Transform 2:

    SELECT DISTINCT `Userid` FROM OrdersTable WHERE `submitted_at` > DATE_SUB(curdate(), interval 7 day)


    (Grab Userids who've ordered between 12 months ago and 7 days)

    Transform 3:

    SELECT DISTINCT `Userid` FROM OrdersTable WHERE `submitted_at` > DATE_SUB(curdate(), interval 12 month) AND `submitted_at` < DATE_SUB(curdate(), interval 7 day)


    (Now you can combine them)

    Final Transform:

    SELECT * FROM Transform2 WHERE `Userid` IN (SELECT `Userid` FROM Transform1) AND `Userid` NOT IN (SELECT `Userid` FROM Transform 3)


    That will give you the list of Userids that match your criteria. 


    Hopefully this will help get you started.





    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.