Customer segmentation helps an organisation to promote its brand, services or products in a way that attracts customers to patronise them.
Well-executed analyses help companies gain a deeper understanding of the customer experience, which leads to them adding value to whatever services they offer, thereby increasing customer loyalty.
Depending on the priority concern in your organisation, you may need to segment your customers for several reasons:
Create strategies to improve customer retention by identifying factors contributing to customer churn. Data on customer engagement or transactional data can be analysed to uncover opportunities for improvement.
Understand the long-term profitability of customers by calculating their potential long-term value. It enables the organisation to prioritise customer acquisition and loyalty programs.
Study purchasing patterns, product preferences, browsing habits, or other behavioural aspects to better understand the needs and inspirations of your customers. The information obtained from this can be used to guide product development and marketing campaigns.
Use customer information to develop targeted offers, recommendations, or personalised services. Businesses can improve customer satisfaction and encourage repeat business by taking individual preferences and behaviour.
Now let's find some insights...
This project aims to understand customer behaviour, preferences, and value of a fictitious eCommerce Store to guide strategic decisions and promote business growth.
Data used in this analysis is fictitious eCommerce data by Brian Graves. You can find a sample database here.
Questions to answer :
Total Revenue generated by each product category and its associated brands
Number of purchases for each product category and associated brand on their most recent order dates
Customers at risk of churn
Average time spent on website before first purchase for each customer (The query will also filter for customers who visited the website but did not make any purchase)
Question 1: Total Revenue generated by each product category and its associated brands :
I will provide two approaches to this problem. In the first scenario below, a window function is used to retrieve the total revenue for each product category and associated brand:
The result is ordered by the order_value in descending order (aliased as 'Revenue').
An alternate approach uses ROLLUP in the GROUP BY clause to create hierarchical groupings and compute aggregate values at different levels. It is a way to generate subtotals and grand totals across a specified set of columns.
Rows with null values for the brand column represent the total revenue for the Product category. The last row with nulls in both product category and brand columns shows the overall total for all product categories.
Switch the columns in the ROLLUP function to generate a similar result for the brand column.
Question 2: What is the number of purchases for each product category and associated brand on their most recent order dates:
Another good metric to consider would be sales velocity, which refers to the rate at which products are sold within a given period.
(Total Units Sold) / (Time Period)This helps to identify:
Best selling products
Evaluate seasonal trends
Optimise inventory management
Question 3: Customers at risk of churn:
Tom Brown and Mike Williams are at risk of churn due to having problems with delivery and damaged products, respectively.
To address individual customer concerns, a message could be sent to Tom Brown assuring him of an improved delivery process moving forward.
In Mike Williams's case, there could be a product replacement for him according to the company's return policies.
The company may explore appropriate packaging techniques to protect items and streamline delivery processes in the future.
Note: In a real-life situation, we could also consider the recency and changes in frequency of purchase to be a determining factor for churn
Technical Challenge faced:
I got an error trying to join the two tables because the review_text column is of the CLOB datatype. Oracle does not support certain operations with such datatypes.
You cannot specify a column of a CLOB datatype in a SELECT... DISTINCT CLAUSE so I had to create a new column of varchar2 datatype and then copy the data to the new destination column using The UPDATE statement
Question 4: Average time spent before first purchase for each customer ( The query will also filter for customers who visited the website but did not make any purchase) :
The query helps the business understand its customers' engagement level before making their first purchase. It provides insights into customers' time on the business' website or platform before converting. This information can be valuable for evaluating the effectiveness of the user experience and the content provided.
The customer with ID 122 did not make any purchases on their first visit to the website.
A business can create retargeting or remarketing campaigns for such customers to engage them and potentially convert them into customers.
CONCLUSION
Customer segmentation can be a game-changer for a business when used effectively.
It's not just about dividing customers into groups; it's about building meaningful relationships, understanding what makes your customers tick, and delivering value that resonates with them.
Thank you for visiting. See you in another blog!