)
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.”
While you can write SOQL queries for individual activity objects, Salesforce’s SOQL limitations prevent direct cross-object counting and rolling date aggregations in a single query because you can’t use UNION with aggregate functions.
Here’s how to enhance SOQL capabilities for comprehensive opportunity activity counting across multiple objects with rolling date calculations.
Enhance SOQL for cross-object activity counting using Coefficient
Coefficient extends SOQL capabilities beyond Salesforce’s native limitations. You can pull comprehensive activity data and perform the cross-object aggregations that standard SOQL can’t handle, while adding rolling date calculations in Salesforce spreadsheet environments.
How to make it work
Step 1. Use custom SOQL to pull comprehensive activity data.
Set up a custom SOQL import in Coefficient: SELECT Id, WhatId, ActivityDate, CreatedDate, Subject FROM Task WHERE WhatId IN (SELECT Id FROM Opportunity) AND ActivityDate = LAST_N_DAYS:30. Create separate imports for Tasks, Events, and EmailMessage objects to capture all activity types that a single SOQL query can’t aggregate.
Step 2. Coordinate multi-query data with spreadsheet functions.
Combine your separate SOQL imports using QUERY functions for accurate multi-object aggregation. Use formulas like =COUNTIFS to count activities across all imported datasets, providing the cross-object counting that pure SOQL cannot achieve.
Step 3. Apply dynamic 30-day rolling windows.
Use Coefficient’s dynamic filtering capabilities to automatically adjust date ranges without manual SOQL modification. Point filters to cells containing date formulas like =TODAY()-30 to create rolling windows that update automatically.
Step 4. Create real-time rolling calculations.
Combine imported SOQL data with spreadsheet functions like COUNTIFS and SUMPRODUCT: =COUNTIFS(Activities.OpportunityId,A2,Activities.ActivityDate,”>=”&TODAY()-30,Activities.ActivityDate,”<="&TODAY()). These formulas create rolling 30-day activity counts per opportunity that update automatically with each refresh.
Start building advanced activity queries today
This approach provides the cross-object aggregation and rolling date calculations that pure SOQL cannot achieve while maintaining data freshness through automated refreshes. Begin creating your enhanced SOQL-based activity tracking system.
