Identify customers ordering on multiple dates

Hi

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:

User_idorder_amountOrder_idsubmitted_atamountfirst_namelast_name
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

 

Thanks

Best Answer

  • Valiant
    Valiant Coach
    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.

     

    Sincerely,

    ValiantSpur

     

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

Answers

  • Valiant
    Valiant Coach
    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.

     

    Sincerely,

    ValiantSpur

     

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

  • Thanks!

     

    That worked a treat, really appreciate your help!