Sales by Time of Day (grouping by hour of day)

[BrianHenderson]

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?

[AndrewV]

Hey Brian!

Always cool to hear what you’re up to and good question! While I haven’t had to build out a report like this before, it sounds like we’d take very similar approaches. That being said, I’d highly recommend avoiding filtering, opting instead for storing hourly values like you mentioned at the end of your post. This way, you can use native search/constraint/group by functionality without having to get into filtering or advanced filters.

Give it a try and feel free to reach out to us directly with any questions at [email protected]

[jared.gibb]

I would also use the approach above, saving the hour as a value on the sale object

This would be minimal javascript and should complete fairly quickly 50-200 ms client side

Sounds like a good use case for a custom built plugin

[boston85719]

@BrianHenderson

That’ll work but it feels hacky and doesn’t help all my historical data

This is the way I do it. In the event you have historical data and need the field, run a backend workflow on the historical data…might take a bit of time, but it will get it in there.

[BrianHenderson]

Thank you very much for chiming in, guys. I appreciate it.

I suppose I’m glad that I’m not completely overlooking some obvious function that everyone uses.

Regarding creating a field to record the hour of the day in which the order was created, my only real challenge for that would be accounting for time zones. It would technically be a number or text field instead of a date field if I did that. I suppose I could fetch the hour’s value from the UTC or “Zulu” time when the order is created and then dynamically offset that when displaying the report based on the current user’s time zone or something…

I’m sure I’m overthinking it. The majority of my customers are in the US and I just a have a handful in Alaska, Hawaii, and the US Virgin Islands so I don’t have too many time zones to worry about.

[BrianHenderson]

Actually, yeah I’m overthinking this. This report is for orders for brick & mortar stores so the user’s mindset is probably going to be in the time zone in which their store is located. I’ll just use the current user’s time zone when extracting the hour during order creation.

For updating historical data, however, I will need to take different time zones into account. I have the physical address of each store, perhaps I could rig something up that looks at the ZIP code and double-checks what time zone that is located within.

[equibodyapp]

You can use something like ‘store’s location’s timezone ID’ (timezone ID on geographical addresses is natively offered in Bubble )