How can I view daily sales by specific hour on Shopify?

cloudsandcoils
Tourist
3 0 3

I have found how to bring up analytics of sales by hour but it shows each hour of every day rather than all sales made from 1:00 pm to 2:00 pm regardless of date. seems that shopify also groups hour with the date so it is very difficult to extract the data I'm looking for.

 

Thanks in advance for any help.

Replies 3 (3)

TacoLlama
Shopify Partner
9 0 2

I exported my Orders, and then viewed it on Google Sheets. I added these two formulas:

# The day of the week, as a number from 1-7
=WEEKDAY(DATEVALUE(left(D2, len(D2)-5)))
#With lookup table to translate numbers to Mon, Tues, etc.
=vlookup(WEEKDAY(DATEVALUE(left(D2, len(D2)-5))),$C$130:$D$136, 2, True)

# The hour of the day
=right(left(D2,13),2)

This is what the spreadsheet looks like: 

TacoLlama_0-1671765199054.png

You can then take that and make a pivot table, using those two columns for columns + rows, and the data as sum or average of the sales data:

TacoLlama_1-1671765269896.png

 

GroundGoods
Visitor
1 0 0

Thanks! This is exactly what we're looking for but could use a little more clarification on the formulas.

TacoLlama
Shopify Partner
9 0 2

 

Here's expanded comments on the day of the week:

 

# The day of the week, as a number from 1-7
#   Trims the timezone off the end, and then converts it into a date value for Sheets, and then extracts the weekday from the calendar.
#   The output is a number from 1-7.

=WEEKDAY(DATEVALUE(left(D2, len(D2)-5)))


# With lookup table to translate numbers to Mon, Tues, etc.
#   Make a little table that has the numbers 1-7 in one column, and the days of the week in the second. Change $C$130:$D$136 to reference that little table.
#   The output is now the day of the week.

=vlookup(WEEKDAY(DATEVALUE(left(D2, len(D2)-5))),$C$130:$D$136, 2, True)

 

 
Here's the hour of the day from the sale:
 

 

# The hour of the day
#   This trims down the strings to just the two characters for the 24-hour the sale took place in.

=right(left(D2,13),2)

 

 

Both of these would probably be easier to read if it was doing substring parsing, instead of chopping up a string. I don't remember why I didn't do that originally.