How can I count activities for a lead that meet certain criteria? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
David MorseDavid Morse 

How can I count activities for a lead that meet certain criteria?

Hi There - 

In outbound prospecting, they say it takes 8-9 touches before a prospect will respond.

I would like to create a lead custom field called "touch count" that will
  • count the number of activities 
  • if the lead status at the time of the activity was open or attempting
  • if the activity is a certain type (e.g., outbound email, call - left voicemail, call - did not leave vm)
  • DO NOT COUNT if the activity does not meet these criteria
I would like the value to increment automatically based on the logging of activities without requiring human to change the value.

How would you do this? 

My intent is to create views and reports that use "touch count" as a filtering criteria.

Thanks!
Jayvin AroraJayvin Arora
Yes you can. You'd have to write an apex trigger on the task record which is easy enough to do.


Heidi BroseHeidi Brose
You could write a trigger that will count activities, they aren't supported in roll up sumaries unfortunately.  Here's a link to get you started with that.  

https://success.salesforce.com/answers?id=90630000000gmFRAAY

You can also use a 3rd party app to give this information like RollUp Helper if you aren't comfortable with writing triggers.  
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI 

They have a free version that gives you 3 roll up summaries. 
 
Deepak AnandDeepak Anand
+1 to Jayvin Arora.

This might get a bit long but let me explain all the possible ways(AFAIK) and this is usually one of the most needed requirement and sometimes the most asked question!

The Problem:
Now Activity(Tasks and Events) is one kind of it's own where it can actually link itself to almost all the Objects regardless of whether it is Custom or Standard and that's the main reason why we find the Enable Activities feature on almost everything(Object) in Salesforce. Also, the relationship between the Activity Object and the Object(on which Activities is Enabled) is loosely bound or you could think of something that is synonymous to a Lookup kind of relationship and because of this very reason, we cannot create Rollup Summary(RUS) fields on the Parent Object. So in order to summarize the Child Activity Records we'll have to resort to some other techniques and they are as below:

The AppExchange Way:
So this is to utilize the Rollup Helper App[1] from Passage Technology hosted on the AppExchange. This app allows you to create RUS like fields on the Parent Object even if it has Child Objects related to the Parent via Lookup Relationships. When compared to the native RUS, the RUS created via Rollup Helper can be customized in many ways. For example, your are provided with more Summarizing Functions for e.g. the CONCATENATE function, you can setup Rollup Criteria which can even include Formula Fields Spanning Relationships(formula fields on child object), carrying Dynamically Calculated Values etc. and much more. Now the app has various editions(think like your Salesforce Editions) where they basically differ on 2 fundamental things: Number of Rollup Fields(Global and not per sObject) and Real Time Calculation. The free edition doesn't support Real Time Calculation and has an upper limit on the number of allowed RUS fields too. The free edition allows you to setup at the most 3 Scheduled Rollups which means you can create 3 RUS fields and then schedule them to run at an interval of 1 minute at the least and hence you say that - "It's not real time". But you can create RUS fields(I am not sure about the upper limit) and they will run only on the click of a button.

Now this solution can be used by any one(any edition too provided that it supports installing of AppExchange apps) since it's just a matter on installing it and then configuring via some point-and-clicks!

Andrew Fawcett for help!
This an app very similar to Rollup Helper but created by a Force.com MVP in the name - Andrew Fawcett(from Financial Force)[2] and it's called - Declarative Rollups for Lookups! So the work initially started way back in July, 2013 and the app has evolved so much and moreover it's an open source initiative too! So you'll not find it on AppExchange(but very recently it had passed the security review from AppExchange too) but you have to go to the GitHub [3] where you'll find the source code and everything else that you need to set it up and running. So just like the Rollup Helper, this one too supports - Filter Criteria on Rollups, both Real-time and Scheduled Rollups etc. all available free of cost(huge thanks to the brains behind it!)

Here is the Developer Force blog[4] which you can use to read more about this but it's a bit technical in nature plus you can visit Andy's blogs[5] to get the grip.

Just like the previous AppExchange, this can be used/maintained by any one since it's all just point-and-click to configure it.

Apex Triggers or (what some hate to hear!):
The idea is to build the Rollup Logic all written in Apex and then initiated via the Apex Triggers [6]. And yes, this solution will not require you to install any 3rd Party Apps and it's in-house solution to a trivial problem. Of-course you will need to some Developer Resources to build one and get them going and also you will have to be at least on the Enterprise Edition of the Sales Cloud since only that allows Custom Development but you'll eventually see that you are soon going to encounter many situations where you'll actually need RUS fields but you cannot due to the Platform Limitation or the schema was setup like that and you'll end up writing many Apex Triggers solely to overcome this. So I feel going with an App may look a little overwhelming initially but it will always be for the good.

Reports:
Well, I am not sure but I have sometimes seen that these Rollups are basically used to create Reports and you could also make use of the native Reporting Functions(or features) to reach somewhere closer. I have seen SteveMo solving some of them with his slick reports!

Anyways that's my two cents and I hope this helps you to decide upon one solution considering all the factors to combat the trivial war - https://success.salesforce.com/ideaView?id=08730000000BrqsAAC

[1]: Rollup Helper App (https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI)
[2]: Andrew Fawcett(from Financial Force) (https://developer.salesforce.com/forums/ForumsProfileAboutMe?userId=005F0000003FjYQIA0)
[3]: GitHub repo (https://github.com/afawcett/declarative-lookup-rollup-summaries)
[4]: Developer Force blog (https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships)
[5]: Andy's blogs (http://andyinthecloud.com/2014/02/09/new-release-spring14-declarative-rollup-summary-tool/)
[6]: Apex Triggers sample (http://www.anthonyvictorio.com/salesforce/roll-up-summary-trigger/)
David MorseDavid Morse
@jayvin @heidi and @deepak - you guys are the best...thanks so much for your helpful and fast responses!