Transactions Per Hour Hack

[beekaboo_she]

I just joined up and saw an older post about someone wanting to figure out how to see customer transaction count per hour, unfortunately it was closed so I decided to respond more publicly here for what worked for me but would love feedback from others if there's other ways.

FROM SQUARE

1. Starting on Dashboard go to: REPORTS > SALES > SALES TRENDS
2. You should see 3 different sales graphs.
3. First Graph Only: Adjust METRICS with ORDER COUNT and NET SALES checked
4. All Graphs: Set the date to TODAY.
5. Then EXPORT the file to your desktop.
6. IMPORT the file into GoogleSheets

FROM GOOGLESHEETS

1. FILE > IMPORT
2. Choose the file you downloaded
3. You should see 3 different sections of times and numbers. Each section represents those 3 different graphs of info from Square.
4. The sections that say CURRENT will give you the information needed if you're curious about the number of transactions were completed as well as how much $ was made in each hour. Helpful to know if you're team is going fast enough and trending times.

I'd love to see about having this report but with fees deducted. Any tips on how to see this?

[Sammie_C]

Hi @beekaboo_she, I see you are fairly new to the Square Community. Welcome!

I personally am not familiar with another way of doing so. I am going to go ahead and tag in a couple of our amazing Square Champions and see if they have any knowledge on this. @TheRealChipA and @Candlestore, do you all have any insight on this?

If you all don't have any other ideas, I can definitely reach out to our Operations teams and see if they know of anything else that would be helpful.

[Candlestore · Accepted solution]

@Sammie_C  and @beekaboo_she ;

I am not sure what actual columns are trying to be produced from the exports to get the sales per hour details.

What I have done which is using Excel instead of google sheets is:

1. Download my Transactions Data   ( Transactions - Export - Details )
2. Delete similar file in a folder i have on desktop for easy access
3. Open with a Saved Excel file and click refresh ( this looks in the folder I just placed the new csv data I downloaded for the new data)
4. In the background Excels Power query does a few calculations and manipulations to export a report for me based on my previous settings before saving the excal file.

Only issue I ran into was I would see 1 for all transaction for a Time, since you still need to show PowerQuery how to average or define the time frame when a sale was made.   

Do you want sales made at say 2:31pm as a 2pm sale or a 3pm sale..... Rnd up   this is where I started giving up.

I did find this through Google Search which may assist you in doing what you want.

To round Time in Google sheets you can follow this from an AI generated response in a Google Search :

AI Generated

To round a time value to the nearest minute in Google Sheets, you can use the TIME and MROUND functions together. The formula =TIME(HOUR(A1), MROUND(MINUTE(A1),1), 0) will round the time in cell A1 to the nearest minute. If you need to round to the nearest 15 minutes, the formula would be =TIME(HOUR(A1), MROUND(MINUTE(A1),15), 0). [1, 2, 3]

Here's a breakdown of how these functions work:

• HOUR(A1) and MINUTE(A1): These functions extract the hour and minute values from the time in cell A1. [1, 2]
• MROUND(value, multiple): This function rounds the value to the nearest multiple. In the context of time, we use TIME functions to represent multiples of minutes (e.g., TIME(0, 15, 0) for 15 minutes). [1, 3]
• TIME(hour, minute, second): This function constructs a time value from the provided hour, minute, and second. [2]

Example:

If cell A1 contains the time 10:27:30, the formula =TIME(HOUR(A1), MROUND(MINUTE(A1),15), 0) will return 10:30:00, while =TIME(HOUR(A1), MROUND(MINUTE(A1),1), 0) will return 10:28:00. [1, 2]

AI responses may include mistakes.

[1] https://stackoverflow.com/questions/70661134/google-sheets-rounding-datetime-cell-to-nearest-15-mint...

[2] https://chandoo.org/wp/rounding-time-in-excel/

[3] https://www.reddit.com/r/Excel247/comments/1iwck0j/round_time_in_google_sheets_excel_tips_and_tricks...

This will have a Spreadsheet in google having Time rounded as you see fit where you could get to round all sales to the closest hour, and use a pivot Table to display how many Count sales were in a given hour.   

Since I use excel more than Sheets I am not sure if you can save and apply these calculations without rewriting the formulas.  I know Googlesheets has come along way since I started using Excel, but I am not sure what all can be done with sheets.

I hope this helps you get what you to where you want.

[Sammie_C]

This is very helpful, @Candlestore! I hope this answers your question, @beekaboo_she. Let us know if you have any other questions.