Hi
I'm trying to calculate the average age of all our leads.
For leads that were created through an Event Campaign (Campaign Record Type = Event), the age should only start counting once the Campaign Start Date has passed. In this case, the Campaign Start Date should be used for the calculation.
For all other Leads the age should be calculated with the lead created date.
Has anyone implemented something similar or does anyone have suggestions on how to measure the average lead age?
Thank you.
#Salesforce Admin #Salesforce
Hey Delio,
Build this as a formula field on Lead, then average it via a Report, no custom object needed.
1. Create a Number formula field on Lead, e.g., Lead_Age_Days__c:
IF(
AND(NOT(ISBLANK(CampaignId)), TEXT(
Campaign.RecordType.Name) = "Event"),
IF(Campaign.StartDate > TODAY(), 0, TODAY() - Campaign.StartDate),
TODAY() - DATEVALUE(CreatedDate)
)
This checks: is the lead tied to a Campaign (via Primary Campaign Source/CampaignId) whose Record Type is "Event"? If yes, use Campaign Start Date once it's passed (0 if it hasn't started yet). Otherwise, use CreatedDate as normal.
Note: cross-object formula fields via Lead's CampaignId lookup to Campaign fields (StartDate,
RecordType.Name) do work, since it's a standard lookup relationship.
2. Build a Lead report, add Lead_Age_Days__c as a column, then use the built-in Average summary function on that column (click the column dropdown > Summarize this Field > Average). This gives you the average across all leads in the report, and you can group by Lead Source, Owner, etc. if you want it broken down further.
3. If you don't want leads with an unstarted Event campaign (age = 0) skewing your average downward, add a report filter excluding Lead_Age_Days__c = 0, or exclude rows where the campaign hasn't started at all, depending on whether you want them counted as "0 age" or excluded entirely.
Reference:
https://help.salesforce.com/s/articleView?id=000394930&language=en_US&type=1
