I am trying to build a report to show sales totals per hour of the day for any given date range. The goal is to help my customers identify what their busiest times of day are so they can staff their stores appropriately. A fun extension of this idea is to build a graph or heat map to better visualize it, but I’ll do that after figuring out how to get this data.
My first thought was to use the grouping function, but that only allows grouping by day or month when dealing with dates.
Next, I tried used the Toolbox plugin to generate a list of hours (0 to 23) to display in a repeating group of numbers and then doing a search per row for orders with an advanced filter for the creation date with “:extract hour” that matched that cell’s number (hour of the day). That worked, HOWEVER since it involves filtering (which is performed client-side) then the data was very slow to populate for any date range longer than a day or two. There’s just too much data to download and sift through on the client’s device.
One more thought I had to accomplish this is to create a field on the Order data type that is just titled “Hour” and it extracts the hour when an order is created. That’ll work but it feels hacky and doesn’t help all my historical data. It would be very easy to group by that field, however.
Has anyone figured out how to make a report of sales by time of day for a variable date range?