ObHow many tasks have been done in X time frame is a common ask from marketing or sales. Before it would require a report to answer but now with DLRS we can have that answer live on the record itself. In this example I’ll go over Task Counting for Leads.
The theoretical ask this DLRS can solve for is this. Someone would love to know how many tasks in the last week have been done on a Lead. Even better they would love to get a quick count of how many tasks are on that Lead that are not from some sort of automated system. Well with DLRS you can accomplish that pretty easily. The goal is so an SDR can create a List View that only pulls in leads that have not been contacted by a person more than 2 times in the last week.
First step is on the Lead lets create a numeric field and name it Completed Tasks In The Last Week. Lets make the API name of the field DLRS_Completed_Tasks_In_The_Last_Week. Next lets go and create our DLRS. I’ll use the Custom Metadata layout as that seems to be the new hotness and most likely the future of DLRS.
- This is the Parent Object as we want to roll the count up to the Lead Record
- Activities/Task live on the Task Object
- WhoID is the field that relates Tasks/Activities to the Lead Object. It is a strange field here is a data map of it
- This is where all of our business logic will live. We will go through these step by step for this use case.
- For this use case we only want to select Tasks that have been completed. So we say that IsClosed must be TRUE.
- We also don’t want to count ones that our Marketing Automation system creates automatically but WE DO want to count those that are created when a rep sends an email through in this case Marketo Sales Insight. So we know that when a Rep creates a task via MSI Marketo creates it but ASSIGNS it to the Rep. When Marketo creates a Task from a marketing email or activity it assigns the task to itself. In this case the Assigned To field is OwnerID so we say that the OwnerID is NOT EQUAL to the ID of our Marketo user.
- We don’t want to count things that were set as complete further in the past then last week so we say ActivityDate >= LAST_WEEK. We also don’t want to count things in the future where someone might have entered in the wrong value so we use ActivityDate <= TODAY. We also don’t want to count things where there was no Date filled in so you can’t see it in the screenshot but there is ActivityDate != Null also. (If you wanted to get fancy you could set up a workflow rule to stamp the date when an activity is completed and use that as your criteria so you could have people leave the due date in the future to show they completed something early).
- When we put it all together the field ends up reading as so
IsClosed = TRUE AND OwnerId != ‘a145000000CDmlQ’ AND ActivityDate >= LAST_WEEK AND ActivityDate <= TODAY AND ActivityDate != Null
- This is where we put in all of the fields we use in #4 on separate lines, in this case IsClosed, ActivityDate and OwnerID
- We just want a count of the records so we just can put ID here and it will count how many ID’s are returned.
- We want to Count how many records are found
- This is where we want to store the results on our Lead Record. In this case we will use the field we just created DLRS_Completed_Tasks_In_The_Last_Week__c
- I want this to run in realtime
- I want this to bypass any sort of task privacy I might have set up in my org so I have it set to run as System
Once you have this record saved, you can deploy the child trigger via the manage child trigger button then go back and activate the rollup. Now you will also want to click Calculate to backfill all the records, this might best be done at night as it will take a while to run through all your lead records. Then you can build a Lead List view for the SDR’s where the DLRS_Completed_Tasks_In_The_Last_Week__c <= 2. As this is a realtime Rollup that has a date field as a criteria you might want to set up a schedule Calculate to re-run all leads where that value is above 2 every Sunday night so come Monday it is a fresh value for the SDRs to work.