Identify customers ordering on multiple dates

KieranVinomofo · January 2018

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_id	order_amount	Order_id	submitted_at	amount	first_name	last_name
1	155	1058574	1-Nov-16 0:07:01	155	Molly	Smith
2	155	1058576	1-Dec-16 0:26:17	155	Melissa	Jones
3	155	1058527	1-Jan-17 0:31:28	155	Peter	Roberts
4	238.8	1058583	1-Dec-17 3:53:22	238.8	Patrick	Davies
1	449.7	1058587	1-Jan-18 7:03:25	449.7	Molly	Smith
2	135	1057703	1-Dec-17 9:03:15	135	Melissa	Jones
3	106.8	1056514	1-Nov-17 9:30:56	106.8	Peter	Roberts
4	155	620385	1-Nov-17 9:41:35	155	Patrick	Davies

Thanks

BlueRooster · January 2018

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.

BlueRooster · January 2018