Customer Segmentation

Introduction

Customer segmentation is an easy concept to understand, but is quite difficult to implement.

When used in marketing, it is commonly described as Market Segmentation and is the process of categorising existing and potential customers into groups that share similar preferences and behaviours.

As we will discover there is a multitude of factors that can be used to define the the people or businesses you promote your products to, but as a rough guide they can be characterised as follows:

• Geographic - where they are
• Demographic - who they are
• Behavioural - how and when they make their purchases
• Psychographic - what influences them

Of course, the features you use to segment your market or customers depends upon the business you are in or plan to enter.

Benefits

Market and customer segmentation can be time-consuming and costly, but it is something that all successful companies do.

To achieve significant business success, you have to be a market leader. A few lucky companies achieve leadership in large markets, but most businesses tend to succeed by focusing on some kind of niche - better to be a big fish in a small pond than a small fish in a large pond.

Segmentation is the tool you use to identify where to focus your investment in product development and marketing. If you do it well your business will be more competitive and more profitable. You will waste less time and money, and you will have happier customers.

Process

This article is for online retailers who have been trading for at least 3 years and to do the things it suggests you will need access to website traffic and conversion data, sales data customer contact details. Most online businesses will have access to this information in Google Analytics and their ecommerce platform.

Step 1 - Set clear goals

One of the danger you face when engaging in any kind of analytical project, is staying focused on the task at hand. When you begin to look at at graphs and summarised data, it is all too easy to spend time uncovering lots of interesting information that offers few benefits.

The goal of this investigation is gather the information you need to develop a business plan that will increase sales and profitability over the next 12 months.

We will achieve this be developing a clear understanding of:

1. The business you are in.
2. The customers and/or customer segments that contribute most to profits.

This will enable us to prioritise future investment in areas that can generate the most return.

Step 2 - Agree important definitions

The most important questions you need to ask at the beginning of a segmentation project are these.

What business are you in?

The answer to this question will probably change by the time you complete the segmentation, but to begin by listing the different product categories you offer and choosing the largest.

I recommend you use two measures to determine what "the largest" is, by revenue and by profit. The business that you are really in is the one that generates the most profit. Most businesses will have one product category that is the largest by both measures.

Who are your customers?

This sounds like and odd question with an obvious answer, but that isn't always the case.

When you sell subscription services, then you can reasonably claim that the people or businesses with current subscriptions are your customers. You can also define former customers, as those individuals or organisations who have cancelled their subscriptions. This article will not focus on this scenario.

When you sell products, things become more difficult. Once you have processed the transaction and delivering the goods to someone, are they still "a customer"? No, they were a customer. Ideally, they might become a returning customer, or even a regular customer, but that cannot be assumed.

Basic categorisation

When defining who your customers are, then a good place to begin is to categorise them by:

• The number of purchases they have made with "One-Off" customers who have placed a single order and "Repeat" customers who have placed multiple orders.

• Determine the status of each customer with "Current" customers being those you think might return to make future purchases and "Lost" customers who you think might return to make another purchase in the future.

Step 3 - Prepare the data

Data cleansing

Everyone who has worked with computer software will know the aphorism "garbage in, garbage out" and this is especially true in the field of data analysis. To prepare data for analysis requires the data to be processed to minimise the kinds of errors listed in Table 1.

Table 1 - Common data errors

Error	Description
Uniformity	Computers are very fussy about the way data is stored and systems vary in the way they format information. Dates have to use the same format, prices might be store in pennies rather than pounds and metric values mixed with imperial values.
Completeness	Systems are not 100% reliable. Sometimes information is missing and sometimes it is scattered across a number of files. You need to identify where this is the case and work around the issues.
Accuracy	It is not uncommon for two different systems to hold different versions of the same data. You need to determine to what extent the data in any system can be trusted.

Data dictionary

The following tables are required to perform the analysis.

Customers

This table holds a list of all customers with a summary of their sales activity.

Table name: Customer

Field name	Data type	Description
UniqueCustomerReference	string	Unique customer reference
TotalOrders	int	Number of orders placed by the customer
TotalGoodsExcTax	money	Total value of all goods ordered excluding tax
TotalShippingExcTax	money	Total value of shipping paid excluding tax
ProcessDate	datetime	Date list was processed
FirstOrderDate	datetime	Date of first order
LastOrderDate	datetime	Date of last order
CustomerType	string	The type of customer ("One-Off" or "Repeat")
CustomerStatus	string	The status of customer ("Current" or "Lost")
PostalCode	string	The postcode area of the customer
Country	string	The country where the customer resides
CustomerAge	int	Number of days between first and last order
AverageOrderValue	money	Average order value excluding tax
AverageOrderInterval	int	Average time elapsed between orders
DaysSinceLastOrder	int	Time elapsed since last order date and processing date in days
UserDefined1	string	An spare field that might indicate if the customer is a business or consumer

How to determine customer status

Unless you sell subscriptions, there is no way of knowing for certain if and when a customer is going to return and make a purchase, but by analysing customer buying patterns we can determine the probability of them doing so.

We can do this by looking at the time elapsed from when people place their one order and then their second order. So long as you have been trading for sufficient time (greater than 19 months) you can then plot a frequency distribution of the time elapsed between a one order and the another order from the same customer.

Orders

Table name: Order

Field name	Data type	Description
SourceOrderID	string	Unique order reference used by the database
OrderDateTime	datetime	Date and time the order was received
OrderReference	string	Unique order reference used by people (can be same as the SourceOrderID)
OrderGoodsValue	money	Total value of the goods including any discounts and excluding tax
OrderShippingValue	money	Shipping charge applied to the order excluding tax
OrderTaxValue	money	The tax applied to the order
OrderTotalValueExc	money	Total value of order excluding tax
OrderType	string	The type of order ("New" or "Repeat")
CustomerType	string	The type of customer ("One-Off" or "Repeat")
CustomerOrderSequence	string	The place in the sequence of orders placed by the customer
CustomerTimeSinceLastOrder	string	If a "Repeat" order, this is the number of days since the last order placed by the customer
CustomerOrderType	string	The type of the order placed by the customer ("One-Off","First","Next", "Last")
OrderYear	int	The calendar year the order was placed
OrderMonthOfYear	int	The month the order was placed in (1 = January, 12 = December)
OrderDayOfWeek	int	The day of the week that the order was placed in (1 = Monday, 7 = Sunday)
OrderHourOfDay	int	The hour of the day that the order was placed in (9 = 0900 >= t < 1000)
OrderYearMonth	int	An integer combining the year and the month the order was placed (202001 = January, 2020)
UserDefined1	string	An customisable field
UserDefined2	string	An customisable field
UserDefined3	string	An customisable field
UniqueCustomerReference	string	Unique customer reference (anonymised)
ProcessDate	datetime	Date list was processed

Order details

Table name: OrderDetail

Field name	Data type	Description
UniqueCustomerReference	string	Unique Customer Reference
TotalOrders	int	Number of orders placed by the customer
TotalGoodsExcTax	money	Total value of all goods ordered excluding tax
TotalShippingExcTax	money	Total value of shipping paid excluding tax
ProcessDate	datetime	Date list was processed
FirstOrderDate	datetime	Date of first order
LastOrderDate	datetime	Date of last order
CustomerType	string	The type of customer ("One-Off" or "Repeat")
CustomerStatus	string	The status of customer ("Current" or "Lost")
PostalCode	string	The postcode area of the customers
CustomerAge	int	Number of days between first and last order
AverageOrderValue	money	Average order value excluding tax
AverageOrderInterval	int	Average time elapsed between orders
DaysSinceLastOrder	int	Time elapsed since last order date and processing date in days
UserDefined1	string	An spare field that might indicate if the customer is a business or consumer