Thanks for your quick reply, but here is some extra complexity in the data I shared. I'm not looking for the days between the very first and very last activity per client. I'm looking for the # days between the last activity that happened before the first sale & the actual first sale.
Sorry, let me clarify. There are 2 main types of activities in my dataset:
1) Item bought
2) All other activities
In order to identify the 2 dates to substract from each other, I need to know:
1) What was the first date when an item was bought
2) What was the date of the activity that preceded that buy. So if I I buy something on Dec 31st 2016, and I did 3 other things before that date (like visiting the website) on the 30th, the 21st & the 18th of that same month, I'm only interested in the 30th, as that's the last activity I did before buying something. The other 2 days are to be ignored.
So in this very simple table:
01/01/2017 <- second item bought
31/12/2016 <- first item bought
30/12/2016 <- last activity before buying first item
21/12/2016 <- second to last activity before buying first item
18/12/2016 <- third to last activity before buying first item
Only the dates in bold are of my interest. The number I'm looking for would then be "1" day in this case.
You can use something like the script below, but you need to fine tune it because this will give you the difference in days for every sale preceded by a non-sale activity within a client bucket:
E as Comment
(ooxml, embedded labels, table is Blad1);
,If(Client = Previous(Client) AND Activity = 'Item bought' AND Previous(Activity) <> 'Item bought',
Date-Previous(Date), Null()) AS DaysSinceLastActivity
ORDER BY Client, Date
DROP TABLE _temp_Sample;
Thanks for this. The one thing it's missing is that it should ignore any other activity / sales data after the first sale.
For client C, it returns 261 days while it should have been 1 day:
Client C did 2 activities before his very first buy. The activity relevant for me is the one which took place on September 12th (the email open). The first sale on Sept 13th is indeed the one to take into account.