Average Amount of Activity per Lead - Answers - Salesforce Trailblazer Community
  • Log In

Browse by categories

Ask Search:
ShowAll Questionssorted byDate Posted
Brittany ParahusBrittany Parahus 

Average Amount of Activity per Lead

I am wondering if someone could help me out. I created a Custom Report Type called "Leads & Activities" this report contains all leads with or without activities. The report is a Summary report segmented by Lead Owner then Lead Name. My issue is that the record count is based off the number of leads not activities. So if there is no activities list for a lead the count is 1 not 0. Because of this I can find an accurate average of activities per lead.

Please Help!
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
I used a trick from Tom Tobin called "The Power of One" 

It goes like this:  Create a custoom field on every object of your SFDC org that you use.  
  • Select FORMULA as the datatype
  • Select NUMBER,0 decimals as the result
  • Enter a  1 in the formula (that's it, just a number one)
Now add this Field to your reports and summarize it to get the count of record for each object i your report.  You can also use it to get average, percent, etc. 

All Answers

Steve MolisSteve Molis
Create a custom field on the Activty object, select Formula as the datatype and Number,0 decimals as the result.  Then enter a 1 in the Formula (that's it, just a number 1).  Then add this field to your Lead Activity report, and select Summary or Matrix as the report type and Summarize(Avg.) your custom Activity(1) field.
Steve MolisSteve Molis
PS.  and then get me a beer ->  
http://russianriverbrewing.com/brews/pliny-the-elder/

User-added image
Brittany ParahusBrittany Parahus
I created the formula like you said but the report is not coming out right. It is taking the average of every activity so for example if the lead has 3 activities it is counting the lead 3 times instead of once. So the average is calculated as 3/3 not 3/1.

I am looking to have the report segmented by Owner, so I want the average of activities per lead by owner.

Thanks!
Steve MolisSteve Molis
Hi Brittany, 
Can you post a screenshot of the current report and create a mock-up of what you want and post a screenshot of that?  With Report/Dashboard questions it's kinda to just guess and visualize what someone wants.
Brittany ParahusBrittany Parahus
Below is how the report is setup currently in SFDC
User-added image

This is what I want

User-added image

The report in SalesForce is counting the lead "Dana Mason" 4 times instead of once so the average activity per lead by Owner is coming out to be 1 when it should be 2.

Also, I tried making Lead Name one of the summary fields but the same thing was happening.

Thanks!
Britt
Steve MolisSteve Molis
So something kinda like this?

User-added image
Steve MolisSteve Molis
BTW - How many beers do you owe me now?!?  ;-D
Brittany ParahusBrittany Parahus
Yes, exactly like that. But how do you group the Account column (In my case the lead name). In other words how did you get a count of Accounts?
Steve MolisSteve Molis
Brittany are you all set now or do you still need help?
Brittany ParahusBrittany Parahus
Hi Steve,

I posted this about 4 days ago
"Yes, exactly like that. But how do you group the Account column (In my case the lead name). In other words how did you get a count of Accounts?"

Once I understand how you did this. I should be all set.

Thanks,

Britt
Steve MolisSteve Molis
I'm still waiting on that beer...
Brittany ParahusBrittany Parahus
I owe you a keg.

BTW how did you get the report to count the number of Accounts and Activity?
Steve MolisSteve Molis
I used a trick from Tom Tobin called "The Power of One" 

It goes like this:  Create a custoom field on every object of your SFDC org that you use.  
  • Select FORMULA as the datatype
  • Select NUMBER,0 decimals as the result
  • Enter a  1 in the formula (that's it, just a number one)
Now add this Field to your reports and summarize it to get the count of record for each object i your report.  You can also use it to get average, percent, etc. 
This was selected as the best answer
Steve MolisSteve Molis
You could also be a little bit better about marking your old questions as "Solved"  
Brittany ParahusBrittany Parahus
YaY!! it worked!

Thank you so much!
Joy WallisJoy Wallis
Steve,

Do you have any magic tricks for the standard report type Tasks & Events that shows all activities regardless of object they tie to?  I love the Power of One workaround, but it doesn't work for this report type because their's no master object, I assume.   The activity could be on a lead, account, or opportunity, etc. 

I need to be able to calculate the average number of activities by rep/Assigned user off all existing task records and can't figure out how to do it inside of SF. 

Thanks!
Steve MolisSteve Molis
Hi Joy,
Can you create a mock-up of what you want the report to look like with Excel or GoogleDocs and post a screenshot? 
Joy WallisJoy Wallis
Sorry, meant to attach this originally

User-added image
Steve MolisSteve Molis
Hi Joy,
My first attempt didn't go so well, I'm thinking this might be a lot harder to do using a Tasks and Events Report than it's worth (maybe impossible).  I think it will be a lot easier with a Custom Report Type (CRT) have you ever used one before?
Joy WallisJoy Wallis
I have used CRTs before and will do some testing with those.  Thanks a ton for trying!
Steve MolisSteve Molis
Hi Joy,
I have some Good News and some Bad News... 

The Good News: You can do it with a CRT 

The Bad News: You can't get the Activity Type field in a CRT
Paul FischerPaul Fischer
Hi Steve,

Thanks for all for the insightful thread. What would I include in the formula field you suggested above if I only wanted to count Activities with the Type of Call (I don't want to include Emails & other activities)?
Ilan KopeckyIlan Kopecky
So, I used the Power of One as explained above to create a report that looks like this:

User-added image

Why is my average 1 for all of these?
Sreenivasa Rao MariyavulaSreenivasa Rao Mariyavula
I'm creating summary report on Case object and I'm using 2 custom fields on case to make 2 level of grouping . First level of grouping is Service Provider Name and second grouping is RouteId .I added "Formula 1" on case object .Now my requirement is at Service Provider Name level I want to siaply the count of RouteId(Not the toatl record count) . I summarized formula 1 as sum but it's coming same as total record count.Is there any other way or something I'm doing wrong?
Amit SinghalAmit Singhal
Hi Steve, how did you manage to get that sum of users in the report above?