This hack uses only:
-
Deal rollups
-
Calculation properties
-
Close dates
-
Native HubSpot logic
Works in: Professional and Enterprise tiers
🎯 The Goal
We’re building this formula inside HubSpot:
CLV = Average Purchase Value × Purchase Frequency × Customer Lifespan
Everything will update automatically when deals close.
🧱 Before You Start
Make sure:
-
Revenue is tracked in Deal Amount
-
All revenue deals are marked Closed Won
-
Deals are properly associated to Contacts
-
Close dates are set
If revenue isn’t structured cleanly, fix that first.
STEP 1: Create Total Closed Won Revenue
Go to:
Settings → Data Management → Properties
Object: Contact → Create property
Property Setup
| Setting | Value |
|---|---|
| Label | Total Closed Won Revenue |
| Type | Calculation |
| Calculation Type | Rollup |
| Associated Object | Deals |
| Property to Roll Up | Amount (or Amount in company currency) |
| Filter | Deal stage = Closed Won |
| Calculation | Sum |
Save.
STEP 2: Create Total Number of Purchases
Create another Contact property.
| Setting | Value |
|---|---|
| Label | Total Closed Won Deals |
| Type | Calculation |
| Calculation Type | Rollup |
| Associated Object | Deals |
| Filter | Deal stage = Closed Won |
| Calculation | Count |
Save.
STEP 3: Create Average Purchase Value
Create a new Contact calculation property.
| Setting | Value |
|---|---|
| Label | Average Purchase Value |
| Type | Calculation → Custom formula |
| Format | Currency |
Formula
[properties.total_closed_won_revenue] /
[properties.total_closed_won_deals]
Save.
STEP 4: Create First Purchase Date
Create:
| Setting | Value |
|---|---|
| Label | First Purchase Date |
| Type | Calculation → Rollup |
| Associated Object | Deals |
| Property | Close date |
| Filter | Deal stage = Closed Won |
| Calculation | Minimum |
Save.
STEP 5: Create Most Recent Purchase Date
Create:
| Setting | Value |
|---|---|
| Label | Most Recent Purchase Date |
| Type | Calculation → Rollup |
| Associated Object | Deals |
| Property | Close date |
| Filter | Deal stage = Closed Won |
| Calculation | Maximum |
Save.
STEP 6: Create Customer Lifespan (Months)
Now calculate how long the customer has been active.
Create:
| Setting | Value |
|---|---|
| Label | Customer Lifespan (Months) |
| Type | Calculation → Custom formula |
Formula
DATEDIFF(
[properties.most_recent_purchase_date],
[properties.first_purchase_date],
"MONTH"
)
Save.
Note:
If they only purchased once, lifespan = 0. That’s expected.
STEP 7: Create Purchase Frequency
Create:
| Setting | Value |
|---|---|
| Label | Purchase Frequency |
| Type | Calculation → Custom formula |
| Format | Number |
Formula
IF(
[properties.customer_lifespan_months] > 0,
[properties.total_closed_won_deals] /
[properties.customer_lifespan_months],
1
)
This avoids divide-by-zero errors.
Save.
STEP 8: Create Final Customer Lifetime Value
Now build the final CLV property.
Create:
| Setting | Value |
|---|---|
| Label | Customer Lifetime Value |
| Type | Calculation → Custom formula |
| Format | Currency |
Formula
[properties.average_purchase_value] *
[properties.purchase_frequency] *
[properties.customer_lifespan_months]
Save.
🎉 You Now Have Automated CLV
It updates automatically when:
-
A new deal closes
-
Revenue changes
-
Close dates are edited
-
Historical deals are added
No manual updates required.
💡 Optional Upgrade: SaaS Version (MRR-Based)
If you track Monthly Recurring Revenue:
-
Add
MRRto Deals -
Roll up Average MRR to Contact
-
Multiply by Customer Lifespan
This is more accurate for subscription models.
📊 Make It Actionable
Create a workflow:
Trigger:
Customer Lifetime Value > 5000
Actions:
-
Set Customer Tier = High Value
-
Notify Account Manager
-
Add to VIP list
Now CLV drives segmentation.
⚠️ Common Mistakes to Avoid
-
Using report averages (not supported in properties)
-
Calculating lifespan from NOW() (creates volatility)
-
Not filtering by Closed Won
-
Forgetting to associate deals to contacts
-
Using Amount instead of Amount in company currency (multi-currency accounts)
