)
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 stores timestamps in account timezone but provides no native capability to convert or display data across multiple time zones simultaneously, limiting global support team coordination.
You’ll learn how to create comprehensive multi-timezone hourly analysis that enables sophisticated international support team coordination and resource planning across multiple time zones.
Build global timezone analysis with Coefficient
HubSpot can’t create unified reports showing how ticket volume varies by hour across different regional offices or customer time zones. By importing tickets with location context, you can perform advanced timestamp manipulation for HubSpot multi-timezone analysis.
How to make it work
Step 1. Import tickets with location context.
Import HubSpot tickets along with contact or company location data to identify the relevant timezone for each ticket. Include fields like “Country” or “State” to determine timezone context.
Step 2. Create timezone conversion columns.
Build columns converting HubSpot timestamps to different timezones using =create_date + TIME(timezone_offset,0,0) for each region. Create separate columns for each timezone you need to track.
Step 3. Extract local hours for each timezone.
Use =HOUR(create_date + TIME(offset,0,0)) to create separate “Local Hour” columns for each region. This shows what time it was locally when each ticket was created.
Step 4. Build unified global pivot tables.
Create pivot tables showing ticket volume by hour across all timezones simultaneously, revealing global support patterns. This shows how ticket volume flows around the world throughout a 24-hour period.
Step 5. Analyze regional peak patterns.
Identify peak hours for each timezone separately to optimize regional staffing while maintaining global coverage. Use conditional formatting to highlight peak hours for each region.
Step 6. Create follow-the-sun visualizations.
Build charts showing how ticket volume “follows the sun” across global regions throughout a 24-hour period. This reveals natural handoff points between regional teams.
Step 7. Calculate optimal staffing handoffs.
Analyze overlapping peak hours and volume transitions to determine optimal staffing handoffs between regions. Identify when one region should take over from another based on volume patterns.
Step 8. Account for daylight saving changes.
Implement formulas that account for daylight saving time changes in different regions to maintain accuracy year-round. Use conditional logic to adjust offsets based on date ranges.
Enable global support coordination
This creates a comprehensive global view of hourly ticket patterns that enables sophisticated international support team coordination and resource planning across multiple time zones. Start building your global analysis today.
