)
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’s native reporting can’t create week-over-week comparisons at hourly granularity because it lacks the ability to segment data by both time periods and time components simultaneously.
Here’s how to build sophisticated week-over-week hourly comparisons that reveal both short-term fluctuations and longer-term trends in ticket volume patterns.
Build advanced time comparisons with Coefficient
HubSpot’s comparison features only work at the daily level or higher and can’t generate multi-dimensional analysis. By importing extended date ranges, you can create sophisticated week-over-week hourly analysis using HubSpot data in spreadsheets.
How to make it work
Step 1. Import extended date range for reliable baselines.
Pull 8-12 weeks of HubSpot ticket data to establish reliable comparison baselines and identify trends. This extended timeframe helps smooth out anomalies and reveals consistent patterns.
Step 2. Create week identification columns.
Use =WEEKNUM(create_date) to assign week numbers to each ticket, enabling week-based grouping. This creates the foundation for comparing the same hours across different weeks.
Step 3. Build hour-week matrix analysis.
Create pivot tables with hours (0-23) as rows and week numbers as columns, showing ticket volumes for each hour across multiple weeks. This reveals how specific hours perform over time.
Step 4. Calculate percentage changes between weeks.
Build formulas calculating week-over-week changes using =(current_week_hour – previous_week_hour)/previous_week_hour * 100. This quantifies exactly how each hour’s volume is trending.
Step 5. Create trend visualization charts.
Generate line charts showing how specific hours perform across weeks, identifying patterns like “Monday 9 AM volume increasing 15% weekly” or seasonal fluctuations in particular time slots.
Step 6. Set up automated comparison updates.
Schedule weekly refreshes so your comparison charts automatically include new data and drop older weeks to maintain consistent comparison periods. Your analysis stays current without manual work.
Step 7. Implement anomaly detection.
Use conditional formatting to highlight hours with significant week-over-week changes (>25% variance) for investigation. This helps identify unusual patterns that need attention.
Reveal trends and enable proactive planning
This creates dynamic week-over-week hourly analysis that reveals both short-term fluctuations and longer-term trends, enabling proactive workforce planning adjustments. Start tracking your weekly trends today.
