Aggregated Heatmap of User Activity by 24H buckets on Y-axis and the day of the week on X-axis

Grafana Dashboards
1

Sorry new user so limited to 2 links and 2 embedded media items…

What Grafana version and what operating system are you using?

grafana version 9.4.1 local installation
macOS Ventura version 13.0.1

What are you trying to achieve?

I want to plot user activity over time as aggregated counts by weekday and 1h buckets per day. Much like on the Ethereum gas fees tracker: https://ethereumprice.org/gas

How are you trying to achieve it?

I want to let Grafana do the math so this is the query and result:

SELECT created_at, 1 as count
FROM your_database

What happened?

This is the result for the Hourly heatmap plugin:

image
image1416×521 27.3 KB

The result is nearly what I want but I want to aggregate the days into weekdays and show cumulative counts for those days in the visualization. So it has a max of 7 days on the X-axis and I can select the timeframe on which to apply the query

Either I get a table that doesn’t aggregate as I need a datetime column OR I get a table that does aggregate as such:

SELECT 
  DATE_FORMAT(created_at, '%W') AS day_of_week,
  DATE_FORMAT(created_at, '%H') AS hour_of_day,
  DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour_window,
  COUNT(*) AS count
FROM typical_orders
WHERE created_at >= DATE_SUB('2022-10-17', INTERVAL 7 DAY)
GROUP BY day_of_week, hour_of_day, hour_window
ORDER BY FIELD(day_of_week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), hour_of_day, hour_window;

image

Did you follow any online instructions? If so, what is the URL?

I’ve installed the Hourly Heatmap plugin https://grafana.com/grafana/plugins/marcusolsson-hourly-heatmap-panel/ and sources such as these:

https://community.grafana.com/t/how-can-i-change-graph-x-axis-to-show-days-of-the-week-monday-tuesday-wednesday-etc/60813/3

It would seem this must be possible somehow, but how? We’re so close…

2

I am looking for the same solution, and have yet to find it. We do not have the ability to add plugins, so its refreshing to know the plugin specifically made for this doesn’t accomplish it.