Monthly CTA closure rate by Owner

  • 17 June 2020
  • 3 replies
  • 185 views

Badge +1

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:

 

U7OYxBqZ3FNZhqvhULR16_33NynNHD0ETAOVwdcoVLQ8W1aXIFeoeoOTBqBH_Mcf1vfI5yzZl0oyIJStNl-gskUFBL46UEyX9iw5T0CqtJlYx-XrbJ2YBODdp9FRG38aPh0KFoFG



 

Flow of the rule as follows,

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

 

A8dmb7raIBhNsmsoEF7ggWAVaWJD_GuQC8QmZ76OxYsT9EfkgSxz8LZYfQSYsxd6J0BbHtBcPdgOff2wu3YjXZJEzZzs2pM6ej7ErYQxuAd9ueCTbpDvc1nH7w2uerfEsd1W2X8G

 

  1. 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.

 

B1GlMY1t3tpP5KPYpWY2zfCgx6aTeDeUobZsHbEWzg1B7RJTkvvvfXhCtGvpTQ_wmFF7-f-blJbiobQjrLs9EVRgQGiY9wpk9NiTpmdM-uYEBJM964Sp2SkYDRPEGUP7fNgwc51g

 

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

 

zW9yN2t9aA_A5aoOQ227Q2UeySlsfKtH8z8XFbiarCokGtFMF4je0IVkdz163b_PfHjU8sxaeKG_n6QxjipANEB1GSJrwOPNZRGGIZiC6s-FhmXpS4Cxc2ojiYKWiNLkcuCmm7XP

 

  1. 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

8ySInCgkcn6xjfnqonKrLK2fnNSlLnVuU0Ebdx05zt-OuzFnjdfytarXbLQqnFwR7bv0d7s3wKvlgTeQCqokBCPK62YPTlhOxVLk59YIIqXYC2PC5HUvkith79xgYh_GVhNRL5An

 

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

    1. isClosed = True

    2. isClosedOverDue = False

 

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

    1. isClosed = True

    2. isClosedOverDue = True


 

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

    1. isClosed = False

    2. isOverDue = False

 

  1. 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,

 

_MY7MRVKspURiIdSyvjW5a_WlKBOMD4Jj1rqeozsJXhPjL0_EZ6xWAgFbh0WiAptbYhWMtPMGwORBBajE7vCKKgIQxx5d-1XHcf8IOkCKZTnw4_nrqxeFQBikjaMnsR74exTaMQp

 

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.



 


3 replies

Userlevel 7
Badge +1

@phaneendhar_lingam Thanks for sharing it here! Bumping this to the top for more visibility 

What are you merging on when you merge each of the criteria? I am trying to accomplish something similar but based on opportunities closed/open in SFDC. I want to sum the record count and ARR for all and think I could use this concept, but don’t have an identifier to merge on.

Badge +1

Hi @a_heller , I’m merging on ‘Owner Name’ and ‘DueDate Month’ in all the Merges.

Reply