Understanding Customer Basket Value

Customer Basket value is the typical value that a customer brings to the checkout either through online sales or in store sales. Analytics of this number will help you benchmark against your competitors and help you better understand your customer in order to maximise their experience through your store.

Customer basket value can help determine whether your customers are preferring bulk or smaller purchases, understanding this value can help you optimise and cater for number of checkouts, determine the number of push vs hand held shopping carts as well as provide a baseline for management strategy to  maximise total customer basket value.

How does one calculate Customer basket value?

Typically Customer basket value is the average total amount spent by each customer over a given period of time.

Where n is the number of transactions measured and x is the total value of each transaction.

That looks good on paper but how do we do this in an actual scenario, here is the typical use case.

The typical retailer usually has a combination of systems to help support the business function of selling to customers this includes; a POS system as well as a separate ERP, a scenario could exist where they are integrated into one system, as with most business information systems it usually consists of a database backend and a front end GUI, the database is where all the information lives, thats what we are interested in.

The typical Sales system usually has the following Schema.

  • table_Product – This table contains product information such as Item Name, Description, barcode etc.
  • table_Location – This table contains location information should you have multiple stores
  • table_Stock – This table is the intersection of Product and Location and will typically have a foreign key on product_id and location_id and this should contain inventory information such as stock on hand, lead times, specific pricing etc.
  • table_Sales_header – This table is the header information for each sale and typically contains date information customer information and total value information
  • table_Sales_line – This table is the detail of each Stock item in the sales transaction and usually contains the foreign key of Sales_header_id and stock_id, the table usually contains the quantities of product ordered as well as the price recorded.

So following the example of the typical POS database schema lets build a SQL query to return total customer basket value.

SELECT AVG(total_sales_value) as Average_Basket_Value  FROM table_Sales_header

And there we have the Average basket value across the entire dataset, going back to our equation above you will notice n which is the number of observations, this value becomes important when trying to understand the spread of Customer basket values over certain dimensions, for example a total average customer basket value may not provide the detail I want to see to extract value, an example could be, I want to see the change of Average Customer basket Value over the days in the week to understand what days of trading are customers likely to spend more on average.

A typical query for this example could be 

SELECT AVG(total_sales_value) as Average_Basket_Value, DayName as Day_of_Week  FROM table_Sales_header INNER JOIN table_Calendar  ON table_Calendar.calendar_id = table_Sales_header.calendar_id  GROUP BY table_Calendar.DayName

This query should reveal the following insights, I have introduced Average Product count into the mix too but that is a topic for another post.

Changing the query to group by the start of each week reveals further insight.

I hope this post was valuable, there will be more to come, where I focus on a particular industry and reveal how we can calculate certain metrics based on typical business information systems and bring the data to life through OQLIS Analytics.

Data Potential