)
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.”
HubSpot can’t aggregate different ticket activities like creation versus updates into unified hourly views because it lacks the ability to combine multiple timestamp fields in a single report.
You’ll learn how to combine multiple data sources to create comprehensive ticket activity aggregation that shows total hourly workload from all ticket activities.
Combine multiple ticket activities with Coefficient
HubSpot treats ticket creation and modification as separate events without providing tools to merge them into comprehensive activity analysis. But you can use multi-source data combination to aggregate all ticket activity using HubSpot imports.
How to make it work
Step 1. Create dual import strategy.
Set up two separate imports – one filtering for newly created tickets using “Create Date” and another for recently updated tickets using “Last Modified Date”. This gives you complete visibility into all ticket activity.
Step 2. Extract hour components from both timestamp types.
Use =HOUR(create_date) for new tickets and =HOUR(modified_date) for updates. This creates separate hour columns that you can analyze independently or combine for total activity.
Step 3. Build combined activity calculations.
Aggregate both activities by hour using =COUNTIFS(new_hour_column,A2) + COUNTIFS(update_hour_column,A2) where A2 represents each hour from 0-23. This shows total ticket activity regardless of type.
Step 4. Apply weighted activity analysis.
Create formulas that weight different activities based on effort required: =(new_tickets * 1.0) + (updated_tickets * 0.7) to reflect that updates typically require less effort than new ticket creation.
Step 5. Create activity composition breakdowns.
Build stacked charts showing the composition of hourly activity (new versus updates) to understand workload distribution. This reveals whether busy hours are driven by new tickets or existing ticket work.
Step 6. Schedule synchronized refreshes.
Set both imports to refresh simultaneously, ensuring your combined hourly analysis stays current with all ticket activity. Use dynamic filtering to ensure both imports cover the same date ranges.
Step 7. Calculate rolling activity averages.
Build rolling averages of combined hourly activity to smooth out daily variations and identify consistent patterns. Use formulas like =AVERAGE(OFFSET(B2,-6,0,7,1)) for 7-day rolling averages.
Get complete hourly workload visibility
This approach provides complete visibility into hourly ticket workload that accounts for all forms of ticket activity, not just creation events. Start aggregating your ticket activity today.
