)
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.”
CRM platforms restrict custom formula fields from performing cross-record calculations, making it nearly impossible to create connect rate fields that calculate percentages across multiple leads per rep. These limitations force you to work with incomplete or inaccurate metrics.
Here’s how to build the custom connect rate fields you need using spreadsheet calculations with live CRM data.
Build custom percentage fields using Coefficient
The fundamental issue is that CRMs can’t reference other records in formula fields. When you need to calculate a rep’s connect rate, you’re asking the system to look at all leads assigned to that rep and perform mathematical operations across those records – something most CRM formula engines simply can’t do.
Spreadsheets excel at this type of cross-record calculation while maintaining real-time connections to your CRM data.
How to make it work
Step 1. Import your base connection tracking data.
Pull leads or contacts with connection tracking fields, rep assignments, and relevant date ranges. Apply filters to focus on the time periods and territories that matter for your analysis.
Step 2. Create calculated columns for rep aggregation.
Add columns for “Total Leads by Rep” using =COUNTIFS(rep_column,”Rep Name”,date_column,”>=”&start_date) and “Connected Leads by Rep” using =COUNTIFS(rep_column,”Rep Name”,connection_column,”Yes”). These become your custom field foundations.
Step 3. Build your percentage calculation field.
Create a “Connect Rate %” column with =(Connected_Leads/Total_Leads)*100. Add conditional formatting to highlight performance levels and make the data visually actionable.
Step 4. Set up rep-level summaries and pivot analysis.
Use pivot tables or UNIQUE/FILTER functions to create rep-specific connect rate summaries. Include time-based calculations like monthly trends and territory comparisons that CRM custom fields can’t handle.
Step 5. Export calculated values back to your CRM.
Push your calculated connect rates back to your CRM as custom field values. This gives you the best of both worlds – sophisticated calculations and CRM integration.
Get the custom fields your CRM can’t provide
Custom connect rate fields help you track rep performance with the precision your sales process demands. Stop working around CRM formula limitations and start building the custom fields you actually need.
