LTV in Salesforce (Part 2): Expected Revenue

In the previous article, I calculated LTV as the total sum of closed won Opportunities using the roll-up summary field. That is a simple way to understand how much existing customers have already paid. However, it can’t help you to understand future ones.

This article will cover calculating LTV metrics based on averages and setting up a Salesforce dashboard.

The Formula

There are a lot. I chose the one I found trustworthy.

LTV = (Average Purchase Value) × (Average Purchase Frequency) × (Customer Lifespan)

• Average Purchase Value: The average amount spent by a customer per transaction.
• Average Purchase Frequency: How often a customer purchases in a given period.
• Customer Lifespan: The average duration a customer stays with the company

Calculating Metrics using Salesforce Report

Prerequisites

• You have Lifespan calculated on Account level.
• You have a custom report type with Opportunity as the source object and fields from Account.

Step 1: Creating a Report

• Create a new report using the report type mentioned in the prerequisites.
• Fields: Group Rows - Account Name, Columns - Opportunity Name, Amount.
• Filters: Stage - Closed Won, Closed Date - Choose a specific year, e.g. Previous FY.

Step 2: Calculating Average Purchase Value

Average Purchase Value - average Amount for Closed Won opportunities. Use the standard AVG function for the Amount field.

Step 3: Calculating Average Purchase Frequency

Average Purchase Frequency - the average number of Closed Won opportunities per customer. Create Summary Formula (Salesforce Help), see the screenshot for formula details.

Step 4: Calculating Customer Value (Optional)

Customer Value - average spending per customer per year. Create Summary Formula as a multiplication of the previously created metrics, see the screenshot for formula details.

Step 5: Calculating Average Customer Lifespan

Customer Lifespan could be calculated as the difference between the start date of the earliest contract and the end date of the latest contract, but I assume the value is already calculated and stored on Account level.

Add the existing Customer Lifespan field to report and apply AVG function.

Step 6: Calculating LTV

Create Summary Formula as a multiplication of the previously created metrics, see the screenshot for formula details.

Visualizing LTV Metrics using Salesforce Dashboard

• Create a new dashboard, click + Widget, and choose the created report.
• All calculated metrics should be available as Measure for Metric Chart.

The Result

Use data to understand your customers better:

• Identify patterns for high- and low-LTV customers. Look at what your most valuable customers have in common (contract type, renewal behavior, stakeholders) to find more like them. At the same time, analyze low-LTV accounts to find out the root cause.
• Compare LTV with Customer Acquisition Cost (CAC) as you might be spending too much on acquiring customers who don’t stay long enough to be profitable. Use this insight to adjust your marketing budget or focus on more profitable customer segments.

PS. I’m currently working on two product ideas.

Account Management Assistant gives sales recommendations based on your relationship with the account.

Resource Management App helps to plan resource allocation, improve utilization, and predict future needs.

Send me a DM or book a time with me if interested.