)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Long enterprise sales cycles need sophisticated time-based decay formulas to prevent outdated activities from artificially inflating account health scores. But Salesforce formula fields have severe limitations for date-based calculations and can’t handle rolling time windows or exponential decay functions efficiently.
Here’s how to build robust scoring decay formulas using familiar spreadsheet functions that automatically adjust as time passes.
Build dynamic time-decay scoring with Coefficient
Coefficient provides robust scoring decay capabilities using standard spreadsheet functions. You can implement exponential decay, linear decay, or stepped decay models that automatically update as time passes, ensuring account prioritization remains accurate for Salesforce outbound sales efforts.
How to make it work
Step 1. Choose your decay model based on sales cycle length.
For fast-moving environments, use exponential decay: =Activity_Weight * EXP(-0.1 * (TODAY() – Activity_Date)). This reduces activity influence by about 10% per day. For longer enterprise cycles, use linear decay: =MAX(0, Activity_Weight * (1 – (TODAY() – Activity_Date)/90)) where activities lose influence linearly over 90 days.
Step 2. Implement stepped decay for practical application.
Create practical decay thresholds: =Activity_Weight * IF((TODAY()-Activity_Date)<=30, 1, IF((TODAY()-Activity_Date)<=60, 0.7, IF((TODAY()-Activity_Date)<=90, 0.4, 0.1))). This gives full weight for 30 days, then steps down to 70%, 40%, and finally 10% for very old activities.
Step 3. Apply engagement-type specific decay rates.
Different activities should decay at different rates. Email opens might decay in 7 days while demo requests stay relevant for 45 days. Build separate decay formulas for each activity type based on their typical relevance windows.
Step 4. Set up automatic refresh and historical tracking.
Schedule daily refresh so decay calculations update automatically as time passes. Use Snapshots to capture point-in-time scores and analyze decay effectiveness over different time periods. This lets you optimize decay parameters based on actual sales outcomes.
Keep account scores current without manual work
Dynamic scoring models update automatically as time passes, ensuring account prioritization reflects current engagement levels rather than stale historical data. You can easily test different decay parameters and see immediate impact on scoring distribution. Start building time-aware account scoring today.
