A guest post by Kristi Campbell.

One of the best things for me about being a Salesforce Administrator is that I get to geek out about things like process optimization – bonus points when it ties into automating those processes through Salesforce! It can be difficult, though, to appreciate the incremental steps you take forward when a process still doesn’t feel “done”.  One of those ever-evolving processes in my Admin life right now is around the Lead lifecycle.

A bit of background – when I came into this company, there were a variety of Sales systems in place. We had acquired a handful of companies, each who brought seats of Salesforce, Pardot, plus a homegrown external Partner tool into the mix. One of my first major tasks was to facilitate consolidation into one instance of Salesforce, integrated to one instance of Pardot, and to launch a Partner Community to replace our homegrown tool. While that gives us one source of truth (hooray!) it also introduced new Lead process related questions for us to address:

  • Do we treat leads differently when we create and assign them to a partner vs. leads they input themselves?
  • Within what window of time should we expect partners to act on their leads, and how best can they update the records to show us that action has been taken? Do we automatically reassign leads that don’t show that action within a period of time?
  • Do we allow partners to export records into their own CRM and/or do we want to offer to build integrations?
  • How do we handle repeating leads? Once we assign a lead to a partner, do they “own” the lead throughout the life of the engagement with the prospect?

The last question is the one we’ll focus on here. With a variety of whitepapers, webinars & tradeshow engagements, there are often many touch points with a prospect before they hit the tipping point toward a purchase.

I originally faced this question after our first major trade show since the Org consolidation. We had a de-duplication tool in place (shout out to Clouddingo!), so I was able to easily identify the existing records (thankfully), but how to flag the repeating to their current Owners?

Our initial decision was to update their Lead Status to New, as we knew the partners were actively reviewing their new leads. But we as Admins did not love that solution and in the end neither did many of our partners; they felt it was confusing, especially without a way to easily identify which leads were truly net-new vs. repeat.

Thus, we started the evolution of a new Revisit checkbox formula on Leads. It started pretty simple:

NOT(ISPICKVAL(Status, "New")) && 
NOT( ISBLANK( Last_Status_Change_Date__c ) ) && 
OR(LastModifiedDate > (Last_Status_Change_Date__c + 1) ,DATEVALUE(LastModifiedDate) > (LastActivityDate + 1)) && 
OR(LastModifiedBy.ProfileId = "00e30000000eyco",LastModifiedBy.ProfileId = "00e800000011uFU" /*Profiles for Sys Admin and Robots*/)

Let’s break that down:

  • First, a lead can only be marked for a revisit if it’s not New. If it’s already New, it’s already on your radar, so it should be actively worked.
  • The next key factor was when the lead last changed status, given that we are assuming it’s not new. To define this, we created a Date/Time field called Last Status Change Date, which is updated via a quick Workflow Rule


With an immediate Field Update Action


For existing records that had not yet been updated, I backfilled a date so the formula would have something to work from.

  • From there, we compare that date to a variety of other System Date or Date/Time Fields to see what other action has been taken on the lead: Is that LastModifiedDate more recent? Is the LastActivityDate more recent? For more information on the difference between these two dates and how each is calculated, click here.
  • Lastly, if it was Modified, who made the modifications? If the owner or other sales user is the one who made the change, then by nature we assume they are engaging with the prospect and do not need to flag it. If the Admin or one of our automated Robot users made the most recent change, then we do want to flag it.

Bonus note: In learning to comment code, I learned to comment formulas! The /* note */ you see above helps me remember which IDs I included for this formula.

This was a great start! We created a view & report called around the new Checkbox and advised our users to edit records to clear them. Ideally, they were updating the Status, adding notes, or logging activities – re-engaging with the prospect – and those updates cleared the flag until the prospect had another touchpoint that re-flagged them for another re-visit.

As we get questions from users about the new process, we re-evaluate our assumptions from the initial formula. For example:

  • Last Activity Date uses the “The latest Date of events on a record.” While we haven’t officially rolled out activities, some users are logging completed calls, so we wanted to factor this in. But what about future activities? A task set for next month will always be greater than the Last Modified Date, so there would be no way to clear the flag.
    • So, we removed “DATEVALUE(LastModifiedDate) > (LastActivityDate + 1))” from the formula, and activities are not currently a factor.
  • We found a lot of leads flagged unnecessarily, based on unrelated Modifications we as Admins were making to leads (Data Loader, anyone?) or “updates” made through integrations. This was impacting adoption, as people didn’t “trust” the checkbox.

Truly, we want to flag if actionable modifications were made, not just any modifications.

  • To evaluate that, we created a formula field called Lead Touch that utilizes the best of Pardot Last Activity Create Date, etc. to compare against our Last Status Change.
  • What if the Lead is created with a Status different than New? For example, when a Partner initially creates a Lead based on someone they already talked to, so they start with a Lead Status of Contacted New.

Given that it was not technically changed it did not populate the Last Status Change Date, meaning Revisit was not checked when it should be. A quick update to the Workflow Rule Criteria Formula helped resolve that:





That brings us to the current state of our formula:

NOT(ISPICKVAL(Status, "New")) && NOT( ISBLANK( Last_Status_Change_Date__c ) ) && Lead_Touch__c > (DATEVALUE(Last_Status_Change_Date__c) + 1) && OR(LastModifiedBy.ProfileId = "00e30000000eyco",LastModifiedBy.ProfileId = "00e800000011uFU" /*Profiles for Sys Admin and Robots*/)

We may not be “done” with this process, but we’re certainly better off than we used to be! On one hand, we’ve removed a lot of the false positives, which helps our users engage as we proactively remind them about their Leads needing a Revisit. On the other hand, it ends up being more restrictive than I’d like given that currently the user must modify the Status to reset the Last Status Change Date and therefore clear the flag. The next version will likely involve a Last Modified By Owner Date, something populated via Workflow which accounts for last modified, completed activities and/or chatter updates by the owner. Or, maybe some other new factor we haven’t even thought of yet!

How are you tackling re-engagement from existing prospects? I’d love to hear any input for the next evolution of our process!

About Kristy Campbell

Kristi became a self-taught Salesforce Admin in 2007 while working in Service Cloud at a Hospitality Software company in Boston, MA. She is now a Certified Force.com Developer & Advanced Admin living in Charlotte, NC. In 2015 she started the Charlotte Women In Tech chapter to meet other rockstar Salesforce women who Salesforce and was interviewed about being a #GirlyGeek for the Good Say Sir! Podcast at Dreamforce. You can find her online through her blog at http://www.kristiforce.com or on Twitter @KristiForce.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.