)
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.”
Native Salesforce formulas can’t perform rolling 30-day calculations that span multiple objects like Tasks, Events, and EmailMessage records because they’re limited to single-object contexts and can’t access related list counts with date-based criteria.
You’ll need a cross-object solution that can aggregate activity data and perform complex date calculations across multiple Salesforce objects.
Build rolling activity formulas with cross-object data using Coefficient
Coefficient enables the multi-object aggregation that Salesforce formulas can’t handle. You can pull all activity types into a spreadsheet environment where rolling date calculations work seamlessly across Salesforce objects.
How to make it work
Step 1. Import cross-object activity data with custom SOQL.
Use Coefficient’s custom SOQL feature to pull all activity types: SELECT Id, WhatId, ActivityDate, Type FROM (SELECT Id, WhatId, ActivityDate, ‘Task’ as Type FROM Task WHERE WhatId IN (SELECT Id FROM Opportunity) UNION SELECT Id, WhatId, ActivityDate, ‘Event’ as Type FROM Event WHERE WhatId IN (SELECT Id FROM Opportunity)). Include EmailMessage records captured by EAC using RelatedToId matching Opportunity IDs.
Step 2. Create dynamic rolling period formulas.
Build formulas that calculate 30-day rolling activity counts: =COUNTIFS(ActivityData.WhatId,OpportunityId,ActivityData.ActivityDate,”>=”&(TODAY()-30),ActivityData.ActivityDate,”<="&TODAY()). These formulas automatically adjust the date window and work across all imported activity types simultaneously.
Step 3. Export calculated values back to Salesforce.
Use Coefficient’s scheduled exports to update a custom field on Opportunity records with your calculated rolling activity count. This makes the data available throughout your Salesforce org for workflows, reports, and automation without complex Apex triggers.
Step 4. Set up automated refresh cycles.
Schedule hourly or daily refreshes to keep opportunity activity counts current. The automated refresh ensures your rolling calculations stay accurate without manual intervention, providing real-time activity compliance monitoring.
Get accurate rolling activity calculations now
This approach provides the multi-object aggregation and rolling date calculations that native Salesforce formulas simply can’t deliver. Start building your comprehensive activity tracking system with Coefficient today.
