How To–Improve Product Freshness

How To: Improve Product Freshness

Report Objective:

This report is designed to enable you to analyse time-of-day sales for specific products, to identify an appropriate product preparation schedule to meet your demand with the freshest possible products.

Extracting Data from Floodlight:

  1. Log into to your Floodlight Analytics dashboard
  2. Navigate to ‘Floodlight Focus’ in the left sidebar
  3. Select the ‘Build Your Brand’ tab from the top tabs
  4. Scroll down to the ‘Improve Product Freshness’ report in the ‘Improve Customer Experience’ section
  5. Select the desired Start Date and End Date (e.g. 8 weeks ago and today)
  6. Click the ‘Download CSV’ button
  7. Save the file average_product_sales_qty_by_hour to an appropriate location (e.g. local or cloud drive)

Report Overview:

All products with at least one sale in the selected time period will be presented in this report, along with the average number of units sold for each hour on each day of the week.

Row Structure

  • One row per product per day of the week within the period

Description Column Structure

  • categoryID: ID of the first alphabetical category assigned to the product
  • categoryName: Name of the first alphabetical category assigned to the product
  • productID: ID of the product
  • productName: Name of the product
  • dayOfWeek: Day of week
  • dayOfWeekNumber: Number for day of week to enable sorting (Monday = 1)

Crosstab Structure

  • Crosstab Columns: Each column represents a single hour of each day (24hr clock) of the week (e.g. 9 is sales from 9am-10am), and the day is assumed to go from 6am to 6am to account for late-trading venues
  • Crosstab Units: Each datapoint represents the average number of units sold for each hour of each day of the week during the time period

Summary Structure

  • Total: Sum of the average units sold for each hour of each day of the week in the period
  • categoryNameOverallTotal: Sum of average hourly unit sales for the category relating to this product
  • categoryNamePercentage: Percentage contribution of this product to the sum of average hourly unit sales
  • percentageOfAll: Percentage contribution of this product to total average hourly unit sales over the full period

How to Use This Report:

Initial Preparation

  1. Apply formatting for readability (optional)
    • Adjust column widths
    • Adjust row height
    • Bold text for top row
    • Format cells: top, centre align and wrap text
  2. Freeze panes to improve viewing experience (optional)
    • Excel:
      1. Click in cell G2
      2. View -> Freeze Panes
    • Google Sheets:
      1. Click in cell F1
      2. View -> Freeze -> Up to Current Row (1)
      3. View -> Freeze -> Up to Current Column (D)
  3. Add filter to report
    • Select columns A-Z
    • Data -> Filter

Analysis within Category

  1. Filter down to the category you are interested in analysing
    • Note that the column sum of categoryNamePercentage should be equal or very close to 100% (due to rounding errors)
      1. To quickly find this sum, highlight the column and check the bottom right of your screen
  2. Filter down to the individual day your interested in analysing
  3. Filter down to individual products if required
  4. Hide the hours you are not interested in by selecting the columns
  5. Review hourly historic average sales for each product within each target category to understand how to plan your production schedule based on expected hourly demand
  6. Build your instructions to the kitchen based on this schedule

Additional Points to Note

  1. The averages presented here are trading averages, to enable you to plan better if you have regularly changing menu items.  As such this should be interpreted as ‘for hours when you did sell any of these items, these were the average sales recorded’.
  2. If using Excel for analysis, remember to save as an Excel file (e.g. xlsx) instead of csv or you will lose formatting and formulae upon saving