Calculate Engagement rate for every customer for the last 6 months

[Gautamnair06]

Hi Everyone,
I have a complex ask.

For every customer at every date, i need to add 3 new columns to the data : "Average Safety engagement in last 5 days", "Average Dosing engagement in last 5 days" and "Average Efficacy engagement in last 2 months".

This is calculated by the formula : Total Sent Topic / (Total engagement =1) for a particular topic.

It is kind of a moving average.

Guess it can be done using MACROS but I am new to alteryx and dont have much idea about it.
Request your help.

Attaching the expected Input. Sample has just data for 4 dates. 
I have a dataset with almost 50K records.

[Adriankp]

Someone might have a much more efficient solution, but at a quick glance you should be able to achieve this relatively easily using a batch macro, running one sent date each time. One example of doing it is:

1.) Start with the lastest available 'sent date'. Use DateTimeAdd to calculate the current Sent Date -5 days, to get the current sent date, and the 'start date' to use for calculating the average.

2.) Append the two dates back to your original dataset.

3.) Filter your data to only include rows where sent date is between the 'start date' and current sent date you're running.

4.) Use the cross-tab tool and group by Sent Date and customer. Choose to change column headers based on the 'Topic' column, and values based on the 'Engagement Score' column. In the method for aggregating values, choose average.

Output will look something like this for one date. Then you will of course probably want to clean up your header names etc. to make it look presentable.

The two month average for the efficacy would in this case have to be done separately, but can be done in the same macro with the same logic, just adjusting the DateTimeAdd formula to look for two months instead of 5 days.

[Gautamnair06]

Thanks a lot Adrian !!!
Can you please share the workflow file as well. Will help me a lot.

Thanks

[Adriankp · Accepted solution]

My pleasure! Attached is an example workflow for calculating the dosing and safety average for one date. You should be able to more or less duplicate the logic for calculating the average efficacy, and then combine. Some other changes of course have to be made to convert it into a batch macro.

I would recommend having a look at the Getting Started with Batch Macros article to familiarize yourself with batch macros! 😊

[Gautamnair06]

Thank you so much Adrian !! Appreciate it