3 Simple Salesforce Formula Fields You Need to Create

Salesforce.com is an incredibly robust CRM that allows you to do almost anything with the right operations and development personel.

For those of us that aren’t Salesforce developers or have an army of operations personnel to help us optimize our Salesforce, there are many simple tricks that can maximize the power of the platform.

Here are three simple formula fields that can help you derive insights from the raw data in your CRM and help you optimize your sales, marketing and customer care efforts.

1. Returning the Hour of a Date/Time Field

Analyzing events by hour is a great way to understand how your business changes by the hour of the day. In order to get this analysis you’ll need to create a formula field that returns a number and use this formula to return the hour of a Date/Time field.

VALUE( MID( TEXT(Call_Start_Time__c -(5/24)), 12, 2 ) )

In this case, “Call_Start_Time__c” is the Date/Time field that we want to pull the hour from. You must then adjust this hour to your time zone from GMT. Since EST is 5 hours behind GMT, we are subtracting 5 from 24 in this example to return the hour of an EST time zone event. If you wanted the hour from an organization that was in PST you would subtract 8 from 24. A quick Google search can help you understand how many hours to subtract to render the hour properly for your time zone.

Knowing the hour of certain events can be incredibly helpful. There are three use cases that come to mind I’d like to explore further.

1. Analyzing your activities by hour.

Which hours of the day are your reps most productive?

• By using this formula field with the time that your reps make calls and emails you can understand which hours of the day are the most productive. When is your team generating the most activity that’s likely to lead to revenue?
• Which hours are calls and emails most likely to connect? By understanding which hours of the day that your prospects are most likely to pick up the phone, open or reply to an email you can structure your team so they are investing their time creating activity when the activity is most likely to result in a successful action.When should they dial the phone and make calls? When should they send emails? Ensure your team is investing their time to achieve the maximum return.

2. Analyzing your cases by hour.

• Which hours of the day do cases get completed fastest? By understanding the volume of your cases by hour you can properly schedule your customer care reps. Do you have hours where your response time is lagging? You might need to add more staff during certain hours to keep your SLAs.
• Which hours of the day do cases get resolved with the best feedback? This one is interesting. Taking a look at your CSAT rating by hour can lead to interesting insights. Are there certain hours where you are consistently receiving lower or higher ratings than others? Why?

3. Analyzing your leads by hour.

• What is your conversion rate by hour for different lead sources? This will help you day part your spend to target the hours that are most likely to convert and drive revenue. Pay close attention to this by different lead sources as you’ll notice variations by source. Facebook, for example tends to drive more leads later at night as more people are online and on the social network but these leads may convert at lower rates. By pairing the number of leads and conversion by hour with spend data you can monitor your CPL by hour to help optimize your marketing spend.

• What is your lead response time by hour? Since a quick response is crucial to maximize lead conversion it’s important to ensure your leads are handled quickly. Analyze this by hour of the day and by rep to ensure you are staffing properly and you have people ready to react to the leads that are coming in.

*The example above is for a Date/Time field. You can use this formula to extract the day of the week from a Date Field in a numeric or text format.

2. Returning the Day of the Week of a Date/Time Field

CASE( MOD(DATEVALUE(Call_Start_Time__c) – (DATE(1900, 1, 7)), 7), 0, “Sunday”, 1, “Monday”, 2, “Tuesday”, 3,”Wednesday”, 4, “Thursday”, 5, “Friday”, 6, “Saturday”,”Error”)

In this case Call_Start_Time__c is the Data/Time field we want to pull the day of the week from. Similar to the hour of the day, there are three use cases that come to mind.

1. Analyzing your calls by day of the week.

• Which days are your reps most productive?

• Are you reaching more of your prospects on specific days of the week?

2. Analyzing your cases by day of the week.

• Are you staffed optimally to handle your case load?
• How does your CSAT and case feedback vary by day of the week?

3. Analyzing your leads by day of the week.

• Which days are you most likely to convert leads? Many advertising platforms like Google or Facebook allow you to easily spend more on certain days of the week where you are more likely to convert. By maximizing spend on the days you are most likely to convert leads and minimizing spend on the days where you are least likely to convert can have a huge impact on the ROI of your marketing efforts.

3. Returning a True Value Based on Time of an Event

(Duration_In_Minutes__c >= 3)

In this example, Duration_In_Minutes is how long each call lasts. One advantage of having your sales calls synced to Salesforce and tracking each dial is to understand how the length of call affects performance.

This formula makes it very easy for you to see how often your reps connect with their prospects and actually have a conversation. The formula returns a true result if the Duration_In_Minutes__c field is greater than or equal to 3 minutes.

This formula field allows you to easily analyze your rep performance and their efficiency. Success in sales (especially if you have an SDR team) is not about how many calls that each rep makes. When it comes to generating revenue, it’s about how many calls you make each day where you are able to connect with someone and have a meaningful conversation.

By understanding each salesperson’s call to connect ratio, your managers can easily identify who is effectively communicating with and engaging prospects. By having access to call recordings in your CRM you can quickly listen to the right calls to maximize your coaching time and immediately improve the performance of salespeople that aren’t properly engaging prospects.

For customer care departments it could be helpful to determine which calls are exceeding an acceptable length. Creating a formula field that flags calls as true for calls that last longer than a certain amount of minutes can help you understand how successful your employees are at resolving customer issues.

Monitoring the percentage of calls that exceed a certain length can be a great metric to help you ensure that your team is efficiently resolving issues. By reviewing recordings of these calls you can train your customer care team to limit these calls and improve your customer experience.

‍