I'm looking to figure out the most efficient way of calculating activity after x number of days, as defined by a user.
I've got 2 main tables, one containing the daily transaction data, and the other containing details of campaigns targeting specific customers, including a date and a name. Customers can be targeted with multiple campaigns, and the user can view activity for multiple campaigns too, however I'd like to ensure KPI's from the results aren't double counted. The days after the campaign to view is selected from a slider eg. 7 days after a campaign. I've attached a sample campaign here.
What I'm currently doing is a SUM/COUNT IF, however this seems to become pretty slow after time. I've used the following calculation for that:
Sum(IF([Activity Date] >= [Campaign Date] and [Activity Date] <= DATE([Campaign Date] + [Day]), [Amount], NULL()))
I also tried to use set analysis, creating a date table with [Campaign Date] | [Day] | [Activity Date], which had the list of activity dates associated with a specific number of days after the campaign date:
SUM({$<[Activity Date] = p(dates.activity_date)>} Amount)
The problem with this however is that if customer 1 is in campaign A but not campaign B, then the customer transactions for x days after both campaigns are calculated. Any help is much appreciated!
