Monthly CTA closure rate by Owner

It is important for a Customer Success Director to keep tabs on work performed by various CSMs. CTAs come in really handy to keep track of the tasks completed. Below is a rule that can show you CTAs completed by each CSM on a monthly basis and if they were completed by due date or have been overdue. 

Rule Name: GS : CTA overdue percent

Rule Flow Diagram:

Flow of the rule as follows,

1. Fetch CTAs (Fetch Task) - Fetch required fields from Call To Action object,

2. Extract Due Date as Date (Transformation Task) - Due Date is Date Time field, in this transformation I’m generating a new date field Due_Date using date function on Due Date field.

3. Extract Due Date Month (Transformation Task) - Extract month value from the prior generated Due_Date field

4. Get Total Due CTAs monthly (Transformation Task) - Get the total number of CTAs due for each owner on a monthly basis - no filters are required here

5. Get Closed Non Overdue CTAs (Transformation Task) - This is similar to 4th task only that we include filters,

   1. isClosed = True

   2. isClosedOverDue = False

6. Get Closed Overdue CTAs (Transformation Task) - This is similar to 4th task only that we include filters,

   1. isClosed = True

   2. isClosedOverDue = True

7. Get Open Non Overdue CTAs (Transformation Task) - This is similar to 4th task only that we include filters,

   1. isClosed = False

   2. isOverDue = False

8. Get Open Overdue CTAs (Transformation Task) - This is similar to 4th task only that we include filters,

   1. isClosed = False

   2. OverDue = False

Now Merge all of the above tasks one after the other with the 4th Task (Get Total Due CTAs monthly) being the main task while the others are left joined (Records all records from left dataset) - we get 4 merges Merge1(#9), Merge2(#10), Merge3(#11) and Merge4(#12).

XIII. Calculate Percentages (Transformation task) - Once all the required data is available, final calculations are performed here,

Below is the list of formula fields,

1. Closed Non Overdue CTA % = (Closed_NonOverdue_CTAs/ Total_CTAs) * 100

2. Closed Overdue CTA % = (Closed_Overdue_CTAs/ Total_CTAs) * 100

3. Open Non Overdue CTA % = (Open_NonOverdue_CTAs/ Total_CTAs) * 100

4. Open Overdue CTA % = (Open_Overdue_CTAs/ Total_CTAs) * 100

*This data can be loaded into an MDA object for reporting purposes.