The main thing that I am trying to achieve is to check whether the customer returned to the store at least once one week after the initial purchase. My column are organised in the following way. every customer has unique id and can always be identified:
Date | customer ID | other columns
So what I need to do is take one ID at the time, and check if that id appears in the next week from the initial purchase. If yes, then the outcome should be 1 or TRUE, if no, it should just be 0 or FALSE.
I think some kind of loop will need to be used, but since I am relatively new to KNIME, I would like to hear your suggestions. Thanks you!
I am not sure if you figured out the logic for this, but this is how I would approach it:
Identify the records for initial purchase (you can do this with the duplicate row filter)
Split the initial records from the rest of the records
Compare the rest of the records with the initial purchase based on Customer ID and apply any rules that you need (1 week, number of records if you want exactly twice in the same week)
Thank you for your reply and workflow. However, this is not exactly what I was looking for as dublicate row filter leaves only the first transaction of the customer. Since I have data for more than 3 years, this would mean that it checks only the first time, whether the customer returned in the same week but not his 10th visit for example.
The ideal final result would be basically the same table table with all the transactions where near every transaction there would be a TRUE/FALSE value, whether the customer returned in the same week. If the customer returned in the same week, that row would be deleted since we are calculating the % of customers that returned in the same week. An example of what I mean:
Initial table:
Date | ID
2021-01-01 | 11
2021-01-06 | 11
2021-02-01 | 11
2021-02-09 | 11
2021-01-01 | 12
2021-01-08 | 12
2021-02-01 | 12
2021-02-04 | 12
2021-02-06 | 12
2021-02-20 | 12
Final result (empty rows are the deleted rows):
Date | ID | returned
2021-01-01 | 11 | True
Hi @wytux10 , the way I understood the original statement was the very first time the customer made a purchase, hence why I did it that way, sorry for misunderstanding.
In terms of “one week after the initial purchase”, how do you determine what’s the “initial” purchase? I mean take the following data as example:
2021-01-01|11
2021-01-06|11
2021-01-10|11
Let’s assume that 2021-01-01 was the very first purchase for Customer ID 11 and is an initial purchase date. The next purchase was in 2021-01-06 which is within a week of the initial purchase. But would 2021-01-06 not also be the initial purchase from the 2021-01-10 point of view?
When do you “reset” to determine the initial purchase?
Unless you want to do it within a week of the calendar year, for example, every Sunday, that’s where you “reset” the calculation for initial purchase and next visit within that week.
