Engagment rate calculator based on 6 months

[Maanu]

Hi Community,

I have a input data as attached, I want to calculate 6 months rolling average engagement rate based on success score, where 1 means sent and engaged whereas 0 means sent but not engaged.

I want each NPI to have as many records populated as from past 6 months from min date present in data till max date present in the data
i.e. is the min date in the data is 27th April 2024 and mx date is 14th Dec 2024, each NPI should have date populated from 27th Oct 2023 till 14th Dec 2024, now for the success score column impute missing value as -1.


coming to the engagement rate, it should be calculated as standing on the current date record looking back 6 months data is there any 0 or 1 present if yes then ER = count of 1/count of 0&1, and if the data is not present within the 6 month window the ER should be imputed again with -1.

I'm attaching the input and output both here in the excel, this is a little urgent requirement please, let me know if more clarification is needed.

[Maanu]

This might require the use of iterative macros I have searched about it, but not too confident as to how to best utilize it.
I know this is a little complex ask

[Rhys_Cooper]

Hi @Maanu 

Thank you for posting this question. Was able to produce a workflow using a batch macro to achieve the desired outcome as I have understood it from your explanation. I have attached the .yxzp file that contains the workflow and the macro. Since this is a complicated problem, i have attached annotations underneath the tools to assist your understanding and debugging.

Please let me know if you need any changes or adjustments. also happy to elaborate on the logic if the annotations arent descriptive enought. Would appreicate you awarding this the solution mark if you are satisfied with this help!

Best wishes - rhys

[Maanu]

Hi @Rhys_Cooper , I have a couple of questions here, hoping u could clear on that, I am seeing u have calculated the 6months date in the future, but I want the dates to be created 6months back and so does the engagement rate calculator should also considers the count of 0 and count of 1 6 months back standing on the current record, 

also the final output is not matching in terms of engagement rate I have attached, it would be very much helpful to me if there is any correction needed to be made there

Although must say very concise and clean workflow u have created.

[Rhys_Cooper]

Hi @Maanu. Thank you for the feedback, apologies for the 6month window being forward projected not backwards. I have amended the workflow and macro to work with this switched approach. Does this fit your expected output now? Find the updated yzxp attached please. Please note i havent refreshed the macro input template data or captions yet this wont effect the functioning of this worklflow.

Regards - Rhys

[Maanu]

Hi @Rhys_Cooper , not to bug for this again but the number of records for workflow output is not consistent with the output I have provided, so for reference Im attaching mine workflow which only works for single HCP but I dont know how to put it into itterative macro like u did,

Note so if u sort by NPI asc and Date asc the first engagement record value itself is wrong, maybe its getting confusing when I say standing on the current date record look back 6 months,

so what I mean is once u have generated all the dates for a HCP and imputed the success score, standing on the first record with past6month start date look back 6 months again and then calculate engagement rate, if the data till 6 months is not present then impute by -1.

Please take a look at my workflow for single HCP if u can replicate it in itterative macro for multiple HCPs that would be great. Also note that it does consist of a python node in it.

[Rhys_Cooper · Accepted solution]

Hi @Maanu. Sorry we havent been able to get this sorted yet. Had another look through the logic and determined that since the SuccessScore imputation has no impact on the engagement score, I have worked with my friend @Pilsner to create a simpler logic route that avoids imputations other the final engagement score of -1 if there are no zeros or ones in the 6 month window (looking back). We have centred this arround the previous workflow you sent over so hopefully we are close to getting what you are after!

Please also note we have used  the direct average function in the summarize as the engagement rating calculation.

Regards - Rhys

[Maanu · Accepted solution]

Hi @Rhys_Cooper , this still requires some fine tuning in the formula for calculating the engagement rate, but this can be considered as an acceptable solution in terms of logic and optimization.

Thanks for all the help @Rhys_Cooper  and @Pilsner.

[Rhys_Cooper]

Pleased we were able to return an almost finished solution, best of luck with your project @Maanu!