How To Analyse Long Term Category Trends

How To: Analyse Long Term Category Trends

Good operators will always keep a close eye on their category sales mix, as it helps them to help spot any long term trends which require a response.  Maybe your sales of Gin are going through the roof and you can respond by expanding the range with some more premium or high margin products.  Or maybe (and this is a real example from our previous business), over time your cocktail sales have decreased from 40% to 20% of overall revenue, with the slack being picked up by craft beer.  This venue is no longer a cocktail bar, it now specialises very successfully in craft beer – an example of tailoring the brand and product offering to respond to the measurable market demand.

In this walkthrough I am going to show you how to use Floodlight to extract the data to inform this analysis.  You’ll need access to Floodlight Focus and a free Google account so you can use Google Sheets.

Download and Prepare Report

1. Download the ‘Analyse Long-Term Category Trends’ report from the ‘Build Your Brand’ section of Floodlight Focus.

  • Navigate to ‘Floodlight Focus’ on the left sidebar.
  • Select the ‘Build Your Brand’ tab.
  • Scroll down to ‘Analyse Long-Term Category Trends’.
  • Select required Start Date and End Date (we would advise to use the longest possible period available).
  • Click ‘Download CSV’ and save it somewhere temporary on your local drive.

2. Create a new spreadsheet in Google Sheets and import the CSV

  • Navigate to Google Sheets and click ‘Start a new spreadsheet’ from the ‘Blank’ template.

  • Rename this spreadsheet by clicking on the ‘Untitled’ section at the top and selecting a name.

  • Now import the CSV into this spreadsheet by clicking on ‘File > Import’.

  • Click on the ‘Upload’ tab and then click ‘Select a file from your computer’

  • Navigate to the CSV file historic_category_sales_perc_of_val_by_week.csv file which you saved in Step 2.  You will be presented with a set of import options.  Select ‘Replace current sheet’ for Import Action and ‘Detect Automatically’ for Separator Character and click ‘Import’.

  • You will now be presented with the percentage of overall revenue which each category has represented over each week being analysed

  • Select ‘View > Freeze > 1 row’ and then ‘View > Freeze > 2 columns’ frmo the menu for ease of navigation.

Interpret the initial data

1. Review the average mix and overall split

  • Tables of numbers can be difficult to interpret intuitively (hence we tend to supplement data with interactive visualisations in Pulse and Insight) however one column is quite informative here: the Total %.  This enables me to see the total percentage sales value by category for each category, over the entire period:

  • As you can see, over 80% of my total revenue comes from the Kitchen and Bar categories, with Bakery and Retail making up most of the rest.  The Beverage category is a very small contribution, so it’s worth looking into what these products are and whether they should really be reallocated to the Bar category.

2. Add in trend calculation

  • Now a really interesting and fairly simple calculation to add to this is a trend.  This gives a quick indication of whether the mix % is trending up or down for each of the categories.  For this you’ll need to use the following formula to compute the slope of the trend line (the C2:P2 is the data and C$1:P$1 is the date range):
  • =SLOPE(C2:P2,C$1:P$1)
  • Ensure that you only include the data (i.e. not the ‘Total’ column) and that you include the ‘$’ signs to fix the date range:

  • What you have now is a set of numbers which indicate whether the category mix is trending up or down, and by how much comparatively… however it’s pretty hard to know what to make of them!  The first thing you should do is change the formula to multiply all numbers by 100 as we were working with percentages and this will make them easier to read.  The copy and paste the formula down:

  • Click on the filter on the top cell of the ‘TREND’ column and select ‘Sort A→Z’ .  This will order them from lowest to highest.

  • However a chart will give me a better relative view of the scale of the trend, so select columns B and R by clicking on the top of column B and then Control-Click on the top of column R (Windows/Chromebook) or Command-Click on the top of column R (Mac).  Then simply select ‘Insert > Chart’ from the menu:

  • Scroll back left and you will be able to see the chart.  I like to move it to its own sheet for tidiness by clicking on the chart, then the three dots in the top right and selecting ‘Move to own sheet…’:

Interpret results and determine actions

Observations:

Despite my Bar takings representing the most significant proportion of my sales, over the past 12 weeks the share is trending downwards more significantly than any other category.  Bakery sales are also trending downwards and were down to only 7.7% of my sales last week.

My other most significant category – Kitchen – is trending slightly upward, and in some weeks has actually outperformed Bar sales.  Retail is the category which is trending upwards most strongly, and has increased from a starting percentage of 4-5% for the first 4 weeks (initial 4 week average of 4.5%) to 12.0% last week, with a peak of 17.4% the previous week (recent 4 week average of 12.3%).

Category Actions:

Bar: Investigate any factors which might be decreasing sales in this category (e.g. discounting or promotions vs. decrease in volume) to understand if there is anything I can do to arrest the slide, or whether it is expected given the current time of year and external trading conditions.

Bakery: Investigate the true costs associated with this category. As it is a small contributor in decline, I would want to question whether we continue with this category in its current form.  If I have significant overheads (e.g. energy, preparation, specialist employees), then I might consider buying products from a third party or making at a centralised location and then distributing to my various sites.

Kitchen: Investigate the products within this category which are selling well and causing this positive trend, and then actively promote these products through my marketing channels.  The market demand has clearly indicated that these have potential to be even more successful.

Retail: Investigate potential reasons behind this uptick and keep a close eye on this growth.  Stock availability is key to meeting retail demand, so ensure you keep a keen eye on your stock levels and ordering – however try not to over-order in anticipation of continued strong demand as you don’t want to unnecessarily tie cash up in inventory.