With the aid of the reporting engine and some minor Excel work, it is possible for a user to analyze hourly sales data within a given location. This article seeks to provide a click-by-click walkthrough of creating such a report.
To do so:
- First, create a report within the Custom Reporting module using the Orders data source and the following data arranged as pictured:
- Hit Next. Click Completed Date Only under Filters on the next page, click Add a Constraint, and set the date range you want polled:
- Select CSV from the Preview As dropdown in the lower right and click Go to export the report.
- Optionally, save the report for future use.
- Open the spreadsheet in Excel.
- Remove the first four rows, column D, and whichever row holds the page counter at the bottom of the spreadsheet. Do so by clicking the column or row header, right clicking it, then clicking Delete. This step is necessary to create a Pivot Table and will result in an error if skipped.
- Click the header for Completed Time Only. Right click the header, and click Format Cells.
- Select Custom and set the Type to hh. Click OK.
- Select the Completed Time Only header again. Open Notepad. Copy this column into Notepad, then copy the results in Notepad back. This will give you the hours without other time/date information interfering. Click OK when prompted.
- Select the header once more, and set the format type to General.
- Click Select All in the upper left corner of the spreadsheet.
- Click the Insert tab and click PivotTable. Click OK when prompted.
- This will call up a table that will let you configure how your data is analyzed. The example below will display how many orders were placed per hour in 24-hour format.
To increase the detail of the analysis, there are a few optional additional bits of data you can pull. For example, this procedure will allow you to view an Average Order Total by hour:
- Drag Order Total to the Values table. Click it and select Value Field Settings.
- Click Average and click OK. This will add a column showing you the average total cost of an order total.
- To ensure sensible formatting, click its column header and select Currency formatting.
Other options include but are far from limited to:
- Total Revenue per Hour: Follow the procedure above, but select Summarize value field by Sum instead of Average.
- Sales per Hour by Date or Sales per Date by Hour: drag Completed Date Only to the Row Labels table either above Completed Time Only to see a day-by-day breakdown of sales, or below Completed Time Only to see a listing of what dates saw sales at a given hour.