Introduction

In this section, we will analyze the customers of AdventureWorks Bike Sales to identify different customer segments based on their purchasing behavior, demographics, and other relevant factors. For example, the demographic segments that drive the highest sales will reveal the importance of customer education and income levels in our sales performance. Additionally, we will analyze customer commute preferences, financial situation, age groups and spend patterns, as well as the one-done and repeat customer segments. These all will reveal the insights about the potential customers in the future to target and the marketing strategies to adopt for each segment. By understanding our customers better, we can tailor our marketing efforts, improve customer satisfaction, and ultimately increase sales and profitability.

Customer Segmentation Analysis

Income and Education Segmentation

6. Which demographic segments (based on Income and Education) drive the highest overall revenue?

Solution Method: To answer this question, we will first calculate the minimum, average, maximum, standard deviation, and median of the YearlyIncome column from the Customers table. Then, we will use these statistics to segment customers into three groups: low-budget customers, mid-budget customers, and high-budget customers. Finally, we will analyze the revenue generated by each segment based on their education level to identify which demographic segments drive the highest overall revenue. This will allow us to group the customers based on dynamic thresholds derived from the data itself, rather than using arbitrary cutoffs, providing a more accurate and meaningful segmentation of our customer base.

-- define the thresholds for low, mid, and high budget customers based on the median and standard deviation of YearlyIncome

with median_cte as (
    select YearlyIncome,
           percentile_cont(0.5) within group (order by YearlyIncome) 
               over () as median_income
    from Customers
)
select 
    min(YearlyIncome) as min_income,
    avg(YearlyIncome) as avg_income,
    max(YearlyIncome) as max_income,
    stdev(YearlyIncome) as std_income,
    max(median_income) as median_income
from median_cte;


-- MIN INCOME : 10,000
-- MAX INCOME : 170,000
-- AVG INCOME : 57,305
-- MEDIAN INCOME : 60,000
-- STD INCOME : 32,285

/* RULES IF-THEN:
- INCOME <= (MEDIAN - STD) -> LOW BUDGET
- INCOME BETWEEN (MEDIAN - STD) AND (MEDIAN + STD) -> MID BUDGET
- INCOME >= (MEDIAN + STD) -> HIGH BUDGET
*/

with customer_info as 
(
    select 
        c.CustomerKey as customer_key,
        c.YearlyIncome as yearly_income,
        c.education,
        sum(fis.SalesAmount) as revenue,
        count(distinct fis.SalesOrderNumber) as num_orders
    from Customers c
    left join FactInternetSales fis
        on c.CustomerKey = fis.CustomerKey
    group by
        c.CustomerKey,
        c.YearlyIncome,
        c.education
),

customer_stats as (
    select 
        cast(avg(YearlyIncome) as float) as avg_income,
        cast(STDEV(YearlyIncome) as float) as std_income
    from Customers
)

select
    case 
        when yearly_income <= (avg_income - 0.7 * std_income) 
            then 'low_budget customer'
        when yearly_income >= (avg_income + 0.8 * std_income) 
            then 'high_budget customer'
        else 'mid-budget customer'
    end as customer_budget_segmentation, 

    education, 
    count(distinct customer_key) as num_customers,
    cast(sum(revenue) as float) as total_revenue, 
    cast(sum(num_orders) as float) as total_orders
from customer_info 
cross join customer_stats
group by
    case 
        when yearly_income <= (avg_income - 0.7 * std_income) 
            then 'low_budget customer'
        when yearly_income >= (avg_income + 0.8 * std_income) 
            then 'high_budget customer'
        else 'mid-budget customer'
    end,
    education
order by 
    total_revenue desc;

Results:

Insights:

• The mid-budget customers with a Bachelor’s degree contribute the highest overall revenue, followed by mid-budget customers with Partial College education and mid-budget customers with a Graduate Degree. This indicates that customers in the mid-budget segment with higher education levels are driving significant revenue for the company.
• High-budget customers with a Bachelor’s degree also contribute a substantial amount of revenue, indicating that there is a strong correlation between higher income levels and revenue generation, especially among those with a Bachelor’s degree.
• Low-budget customers with Partial College education and High School education also contribute a significant amount of revenue, suggesting that there is a sizable customer base in the lower income segment that is generating revenue for the company, particularly those with some level of college education.
• Overall, the analysis reveals that both income and education levels play a crucial role in driving revenue for the company, with mid-budget customers with higher education levels being the most significant contributors to overall revenue. This insight can help the company tailor its marketing strategies and product offerings to target these high-value customer segments more effectively.

Commute Distance and Vehicle Ownership Effect

7. Does the customer's commute distance and vehicle ownership impact their lifetime value?

Solution Method: To answer this question, we will analyze the relationship between customers’ commute distance, vehicle ownership, and their lifetime value (LTV). We will segment customers based on their commute distance (e.g., 0-1 Miles, 1-2 Miles, 2-5 Miles, 5-10 Miles, 10+ Miles) and vehicle ownership status (e.g., no car, 1 car, >1 car). We will then calculate the total revenue generated by each segment and the average revenue per customer to determine if there is a significant impact of commute distance and vehicle ownership on customer lifetime value.

with customer_info as (
    select
        cus.CustomerKey,
        cus.CommuteDistance as commute_distance,
        cus.NumberCarsOwned as num_cars,
        coalesce(sum(fis.SalesAmount),0) as revenue, 
        coalesce(count(distinct fis.SalesOrderNumber),0) as num_orders
    from Customer cus
    left join FactInternetSales fis
        on fis.CustomerKey = cus.CustomerKey
    group by 
        cus.CustomerKey,
        cus.CommuteDistance,
        cus.NumberCarsOwned
)
select
    case  
        when num_cars = 1 then '1 car'
        when num_cars > 1 then '>1 car'
        else 'no car'
    end as 'car_ownership',
    commute_distance,
    count(distinct customerkey) as num_customers,
    cast(sum(revenue) as decimal(20,3)) as total_revenue,
    sum(num_orders) as total_orders,
    cast(sum(revenue) / nullif(count(distinct customerkey),0) as decimal(10,3)) 
        as avg_revenue_per_customer
from customer_info
group by 
    case  
        when num_cars = 1 then '1 car'
        when num_cars > 1 then '>1 car'
        else 'no car'
    end,
    commute_distance
order by
    total_revenue desc,
    car_ownership;

Results:

Insights:

• Customers with no car and a commute distance of 0-1 Miles generate the highest total revenue and have the highest average revenue per customer, indicating that this segment has a strong lifetime value. It is maybe due to the fact that the customers with short distance and no car will likely purchase our bikes for commuting purposes, which may lead to higher frequency of purchases and higher overall spending on bike-related products and services.
• Customers with more than 1 car and a commute distance of 5-10 Miles also generate significant revenue, but their average revenue per customer is lower than the no car segment, suggesting that while they contribute to overall revenue, their individual lifetime value may be lower.
• Customers with 1 car and a commute distance of 0-1 Miles also have a high average revenue per customer, indicating that they are a valuable segment, although their total revenue is lower than the no car segment.
• Customers with longer commute distances (10+ Miles) tend to have lower average revenue per customer, regardless of car ownership, suggesting that longer commute distances may negatively impact customer lifetime value.
• Overall, the analysis indicates that customers with no car and shorter commute distances tend to have higher lifetime value, while those with longer commute distances and more cars may have lower lifetime value. This insight can help the company target marketing efforts towards segments with higher lifetime value and develop strategies to engage customers with longer commute distances to increase their lifetime value.

”Whale” Customers

8. Who are our "Whales" (Top 100 Most Valuable Customers), and what is their purchasing frequency?

*Solution Method: Solving this question is straightforward since we just need to calculate the total revenue generated by each customer and their purchasing frequency, then rank them based on their total revenue and identify the top 100 customers. We will also calculate the average number of orders per month for these top customers to understand their purchasing frequency. The Fact Internet Sales table contains the necessary information to perform this analysis, as it includes details about each customer’s purchases, including the sales amount and order dates. Therefore, calculating the financials and then grouping by customer id will allow us to identify the top 100 most valuable customers and analyze their purchasing frequency effectively.

select top 100

    cus.CustomerKey,
    cus.FirstName,
    cus.LastName,
    sum(fis.salesamount) as revenue, 
    count(distinct fis.salesordernumber) as num_orders,
    count(distinct cast(format(fis.orderdate, 'yyyy-MM') as varchar(20)) ) as 'num_months_purchased',
    cast(count(distinct fis.salesordernumber) * 1.0 / 
        nullif(count(distinct format(fis.orderdate, 'yyyy-MM')),0) as decimal(10,3))
        as avg_orders_per_month

from Customer cus
    left join FactInternetSales fis 
        on fis.customerkey = cus.customerkey
group by 
    cus.CustomerKey,
    cus.FirstName,
    cus.LastName
order by 
    revenue desc;

Results:

Insights:

• The top 100 most valuable customers, or “Whales,” generate a significant amount of revenue for the company, with the highest revenue being $13,295.38 and the lowest being $8,237.01 among the top 100 customers. This indicates that these customers are highly valuable to the company and contribute significantly to its overall revenue.
• The purchasing frequency of these top customers varies, with some customers making purchases consistently over several months, while others have a more sporadic purchasing pattern. For example, some customers have an average of 1 order per month, while others have an average of 1.5 or more orders per month. This suggests that while all of these customers are valuable, their purchasing behavior may differ, and the company may need to tailor its marketing strategies to engage with these customers effectively based on their purchasing frequency.
• The majority of the top 100 customers have a consistent purchasing pattern, with many of them making purchases in multiple months, indicating that they are likely loyal customers who have a strong affinity for the company’s products. If we are somehow able to make these customers even more loyal, we will be able to increase their lifetime value and further boost the company’s revenue.

Age Groups and Spend Analysis

9. What is our generational spend distribution (Age Brackets)?

Solution Method: To do accurate analysis I did not consider only the age groups but also the gender and marital status of the customers since the age groups alone will not give us the complete picture of the generational spend distribution. By segmenting customers based on their age, gender and marital status will allow us to understand the purchasing patterns of each group. This is a crucial step as the 30 years old single man will definitely have different purchasing behavior than the 30 years old married man.

To calculate the age of the customers, I have taken the last order date in the sales table as the reference point and then calculated the age of the customers using the DATEDIFF() function which find the difference in years between the birth date and the reference date. This approach allows us to determine the age of the customers at the time of their most recent purchase, which is more relevant for analyzing their spending behavior. Then, I have segmented the customers into different age groups (e.g., <25, 25-35, 36-45, etc.), gender and marital status groups and calculated the total revenue, total orders, and total products purchased for each segment. Segmentation based on the age groups, gender and marital status require just simple, straightforward CASE-WHEN sQL statements, and then we can group by these segments to get the desired results. This analysis will provide insights into which generational segments are driving the most revenue and how their purchasing behavior

with get_max_date as (
    select
        max(OrderDate) as reference_day
    from FactInternetSales
),

customer_info as (
    select
        cus.CustomerKey,
        -- we should find age using datediff() function
        DATEDIFF(year, cus.BirthDate, reference_day) as customer_age,
        cus.Gender as customer_gender,
        cus.MaritalStatus as customer_marital_status,
        sum(fis.SalesAmount) as customer_revenue,
        count(distinct fis.SalesOrderNumber) as customer_total_orders,
        sum(fis.OrderQuantity) as customer_total_products
    from Customer cus
    left join FactInternetSales fis
        on cus.CustomerKey = fis.CustomerKey
    cross  join get_max_date
    group by 
        cus.CustomerKey,
        DATEDIFF(year, cus.BirthDate, reference_day),
        cus.Gender,
        cus.MaritalStatus
)
select
    -- segment the customers into ages
    case  
        when customer_age < 25 then '<25'
        when Customer_age between 25 and 35  then '25-35'
        when customer_age between 36 and 45 then '36-45'
        when customer_age between 46 and 55 then '46-55'
        when customer_age between 56 and 65 then '56-65'
        else '66+'
    end as age_group,

    case 
        when customer_gender = 'F' then 'Female'
        when customer_gender = 'M' then 'Male'
        else 'Other'
    end as gender,
    case    
        when customer_marital_status = 'S' then 'Single' 
        when customer_marital_status = 'M' then 'Married'
        else 'Other'
    end as marital_status,
    count(distinct CustomerKey) as num_customers,
    coalesce(sum(customer_revenue),0) as total_revenue,
    coalesce(sum(customer_total_orders),0) as total_orders,
    coalesce(sum(customer_total_products),0) as total_products
from customer_info
group by 
    case  
        when customer_age < 25 then '<25'
        when Customer_age between 25 and 35  then '25-35'
        when customer_age between 36 and 45 then '36-45'
        when customer_age between 46 and 55 then '46-55'
        when customer_age between 56 and 65 then '56-65'
        else '66+'
    end,

    case 
        when customer_gender = 'F' then 'Female'
        when customer_gender = 'M' then 'Male'
        else 'Other'
    end,

    customer_marital_status,
    case    
        when customer_marital_status = 'S' then 'Single' 
        when customer_marital_status = 'M' then 'Married'
        else 'Other'
    end
order by
    total_revenue desc;

Results:

Insights:

• The age group of 36-45 years old contributes the highest total revenue, with both single and married customers leading in terms of spending. This suggests that this age group is a key demographic for the company, and marketing efforts should be focused on targeting both single and married customers within this age range.
• The 46-55 age group also contributes significantly to total revenue, with married customers leading in terms of spending. This indicates that married customers in this age group are an important segment for the company, and marketing strategies should be tailored to appeal to their preferences and needs.
• The 25-35 age group shows a strong presence of single customers contributing to total revenue, indicating that this segment may have different purchasing behavior compared to married customers. Marketing efforts targeting single customers in this age group may be effective in driving sales.
• The 56-65 and 66+ age groups contribute less to total revenue compared to the younger age groups, but married customers in these age groups still show a significant contribution. This suggests that while these age groups may not be the primary target for the company, there is still potential for growth by targeting married customers within these age ranges.
• Overall, the analysis reveals that the 36-45 age group, particularly single and married customers, is a key demographic for the company in terms of revenue generation. Marketing strategies should be tailored to target this age group effectively, while also considering the preferences and needs of other age groups to maximize overall revenue.

Customer Retention Rate Analysis

10. What is our Customer Retention Rate (One-and-Done vs. Repeat Buyers)?

Solution Method: To calculate the customer retention rate, we will first categorize customers into two groups based on their purchasing behavior: “One-and-Done” customers who have made only one purchase and “Repeat Buyers” who have made more than one purchase. We will then calculate the total number of customers in each group and the total revenue generated by each group. Finally, we will calculate the retention rate for each group by dividing the number of customers in that group by the total number of customers and multiplying by 100 to get a percentage. This analysis will provide insights into the proportion of customers who are repeat buyers versus those who are one-and-done, which can help the company understand its customer retention dynamics and identify opportunities for improving customer loyalty. There is an important consideration in the calculation of retention rate, which is to ensure that we are not dividing by zero when calculating the retention rate percentage. To address this, we will use the NULLIF function in SQL to handle cases where the total number of customers is zero, preventing any division by zero errors and ensuring that our retention rate calculation is accurate and robust.

with customer_info as 
(
    select
        cus.CustomerKey,
        coalesce(count(distinct fis.SalesOrderNumber),0) as num_orders,
        sum(fis.SalesAmount) as revenue
    from customer cus
    left join FactInternetSales fis 
        on fis.CustomerKey = cus.CustomerKey
    group by cus.CustomerKey

),

retention_groups as (
    select
        case 
            when num_orders > 1 then 'repeat-buyers' 
            when num_orders = 1 then 'one-and-done'
            else 'no order'
        end as retention_group,

        count(distinct CustomerKey) as num_customers ,
        sum(revenue) as total_revenue
    from customer_info
    group by 
        case 
            when num_orders > 1 then 'repeat-buyers' 
            when num_orders = 1 then 'one-and-done'
            else 'no order'
        end
)

select
    retention_group,
    num_customers,
    total_revenue,
    cast(num_customers * 1.0 / nullif(sum(num_customers) over (),0) as decimal(10,3)) 
        as retention_rate_pct
from retention_groups
order by
    total_revenue desc;

Results:

Insights:

• The majority of customers (62.9%) are “One-and-Done” buyers, meaning they have made only one purchase, while 37.1% of customers are “Repeat Buyers” who have made more than one purchase. This indicates that a significant portion of the customer base consists of customers who do not return for additional purchases, which may suggest that there are opportunities to improve customer retention and encourage repeat business.
• Despite being a smaller percentage of the customer base, “Repeat Buyers” contribute a significantly higher total revenue compared to “One-and-Done” buyers. This suggests that “Repeat Buyers” are more valuable to the company in terms of revenue generation, and efforts to increase the retention rate and convert “One-and-Done” buyers into “Repeat Buyers” could have a substantial impact on overall revenue.
• The retention rate percentage indicates that there is room for improvement in customer retention, as a large portion of customers are not returning for additional purchases. The company may want to consider implementing strategies such as loyalty programs, personalized marketing, or improved customer service to encourage repeat purchases and increase the retention rate, ultimately driving more revenue from the existing customer base.

Conclusion

In this domain we obtained the following key insights:

• Initially, we analyzed the purchasing behavior of customers segmented by education and income. We have found out that customers with mid-level income and higher education levels (Bachelor’s and Graduate Degrees) contribute the most to the overall revenue, indicating that this segment is a key demographic for the company.
• Then, we considered the effect of commmute distance and vehicle ownerships on the purchasing behavior of customers. We have clearly found out that the customers with short distance and no car are our main revenue drivers. That’s completely logical as they need bikes for commuting short distances, so they prefer purchasing bikes from our company.
• Moreover, we analyzed the order volume, lifetime value and retention rate of the whale customers - the customers that are in the top 100 based on the money spent for our products. We have found out that there is not a straight pattern among these as some of them purchase frequently but some purchase less. We should aim to make them more loyal and hence our main targeted customers are those in this case.
• Next, we analyzed the generational spend distribution by segmenting customers based on their age, gender and marital status. We have found out that the 36-45 age group, particularly single and married customers, is a key demographic for the company in terms of revenue generation. Marketing strategies should be tailored to target this age group effectively, while also considering the preferences and needs of other age groups to maximize overall revenue.
• Finally, we analyzed the customer retention rate by categorizing customers into “One-and-Done” and “Repeat Buyers”. We have found out that the majority of customers are “One-and-Done” buyers, but “Repeat Buyers” contribute significantly higher total revenue. This indicates that there are opportunities to improve customer retention and encourage repeat business, which could have a substantial impact on overall revenue.

In the next section, we will analyze our products and determine which products are the best sellers, which products have the highest profit margins, and how product performance varies across different customer segments. This analysis will help us optimize our product offerings and marketing strategies to maximize revenue and profitability.