)
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.”
Salesforce lacks built-in percentage calculation functions for comparing data across different time periods, making year-over-year decline analysis cumbersome and requiring manual exports.
Here’s how to create automated percentage decline tracking with live data connectivity and visual indicators that update as new opportunities close.
Automate percentage decline calculations using Coefficient
Coefficient solves this by providing automated percentage decline calculations with live data connectivity and visual indicators from Salesforce .
How to make it work
Step 1. Import opportunity data by year.
Set up two Coefficient imports from Salesforce – one filtered for 2023 closed won opportunities (Close Date between 1/1/2023-12/31/2023) and another for 2024 data. Import Amount and Close Date fields for monthly aggregation.
Step 2. Calculate monthly totals.
Use SUMIFS formulas to aggregate opportunity amounts by month for each year: =SUMIFS(Amount_Range, Close_Date_Range, “>=”&DATE(2023,1,1), Close_Date_Range, “<"&DATE(2023,2,1)) for January 2023.
Step 3. Create percentage decline formulas.
Calculate year-over-year percentage changes using =(2024_Monthly_Total – 2023_Monthly_Total)/2023_Monthly_Total*100. Use IFERROR to handle months with zero previous year data.
Step 4. Add decline indicators and automate refreshes.
Create a status column with =IF(Percentage_Change<0, ABS(Percentage_Change)&"% Decline", Percentage_Change&"% Growth") to clearly identify and quantify declines. Schedule automatic daily refreshes through Coefficient so your calculations update as new deals close.
Track percentage declines automatically
This approach eliminates manual report exports and Excel manipulations, providing real-time opportunity calculations that automatically flag percentage-based performance declines. Start tracking your automated percentage decline analysis.
