Lab 07 Report

Customer Segments

For this lab, I have considered three segments for customers. That are following;

1. Premium Customers

These customers are high spenders. They either buy very expansive items or buy in bulk often. Their total expenditure is greater than the defined threshould.
Threshould : Total Spenditure > $3000

SQL Code:

SELECT Customer_Name,SUM(OL.Ordered_Quantity * P.Standard_Price) AS TOTAL
FROM CUSTOMER_t C,ORDER_t O,Order_line_t OL,PRODUCT_t P
WHERE C.Customer_Id = O.Customer_Id AND
      O.Order_Id = OL.Order_Id AND
      OL.Product_Id = P.Product_Id
    
GROUP BY C.Customer_Name
HAVING SUM(OL.Ordered_Quantity * P.Standard_Price) > 3000
Order by TOTAL DESC

Database Response:

2. Frequent Cutomers

These customers are defined on the basis of number of orders they had placed. The number of orders for a customer should be greater or equal to the definded threshould.
Threshould : Total Orders Count >= 3

SQL Code:

SELECT Customer_Name, COUNT(Order_Id) AS ORDERS_COUNT
FROM CUSTOMER_t C, ORDER_t O
WHERE C.Customer_Id = O.Customer_Id
GROUP BY C.Customer_Name
HAVING COUNT(Order_Id) >= 3
ORDER BY COUNT(Order_Id) DESC

Database Response:

3. Bulk Customers

These customers are defined on the basis of number of items per order they had placed. The average number of items per order for a customer should be greater or equal to the definded threshould.
Threshould : Total Orders Count >= 5

SQL Code:

SELECT C.Customer_Name, AVG(Order_Totals.TOTAL_QUANTITY_PER_ORDER) AS Avg_Quntity_Per_Order
FROM CUSTOMER_t C,
     (
        SELECT O.Customer_Id, O.Order_Id, SUM(OL.Ordered_Quantity) AS TOTAL_QUANTITY_PER_ORDER
        FROM ORDER_t O,Order_line_t OL
        WHERE O.Order_Id = OL.Order_Id
        GROUP BY O.Customer_Id,O.Order_Id
     ) Order_Totals
WHERE C.Customer_Id = Order_Totals.Customer_Id
GROUP BY C.Customer_Id,Customer_Name
HAVING AVG(Order_Totals.TOTAL_QUANTITY_PER_ORDER) >= 5
ORDER BY Avg_Quntity_Per_Order DESC;

Database Response:

My Solution

Create three seperate cards(div) for each of the customer segment and populate each card(div) with their respective data from the database response.

Segmentation.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Segmentation.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Lab 07</title>
    <style>
        body{
            background-color: #f4f7f6;
            margin: 0;
            padding: 20px;
        }

        .main-container{
            padding-top:50px;
            display:flex;
            gap: 25px;
            justify-content: center;
            flex-wrap:wrap;
        }

        .List{
            list-style-type: none;
            padding-left: 0;
            margin: 0;
        }

        .List-box{
            background-color: #ffffff;
            border-radius: 10px;
            box-shadow: 2px 4px 8px rgba(0, 0, 0, 0.1);
            padding: 20px;
            width: 300px;
        }

        .List-box h1{
            font-size: 1.4rem;
            color: blue;
            margin-top: 0;
            padding-bottom: 10px;
            border-bottom: 2px solid #007bff;
        }

        .titleBar{
            background-color: cadetblue;
            color:blue;
            text-align: center;
            padding: 10px;
            border-radius: 10px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div class="titleBar">
            <h1>Customer Segments</h1>
            <br />
        </div>

        <div class="main-container">
            <div class="List-box">
                <h1>Premium Customers: </h1>
                <asp:BulletedList ID="Premium" runat="server" CssClass="List"></asp:BulletedList>
            </div>
    
            <div class="List-box">
                <h1>Frequent Customers: </h1>
                <asp:BulletedList ID="Frequent" runat="server" CssClass="List"></asp:BulletedList>
            </div>
            
            <div class="List-box">
                <h1>Bulk Buyers: </h1>
                <asp:BulletedList ID="Bulk" runat="server" CssClass="List"></asp:BulletedList>
            </div>
        </div>   
    </form>
</body>
</html>

Segmentation.aspx.vb

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page

    Private Sub Premium_Customers()
        Dim conn As SqlConnection
        conn = New SqlConnection

        Dim constr As String
        constr = "Data Source=WIN-3G30IJDBABE\SQLEXPRESS;Integrated Security=True;Database=PVFC"

        conn.ConnectionString = constr

        Dim cmd As SqlCommand
        cmd = New SqlCommand

        cmd.Connection = conn

        cmd.CommandText = "SELECT Customer_Name,SUM(OL.Ordered_Quantity * P.Standard_Price) AS TOTAL "
        cmd.CommandText &= "FROM CUSTOMER_t C,ORDER_t O,Order_line_t OL,PRODUCT_t P "
        cmd.CommandText &= "WHERE C.Customer_Id = O.Customer_Id AND "
        cmd.CommandText &= "O.Order_Id = OL.Order_Id AND "
        cmd.CommandText &= "OL.Product_Id = P.Product_Id "
        cmd.CommandText &= "GROUP BY C.Customer_Name "
        cmd.CommandText &= "HAVING SUM(OL.Ordered_Quantity * P.Standard_Price) > 3000 "
        cmd.CommandText &= "Order by TOTAL DESC "


        Dim reader As SqlDataReader

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            While reader.Read()
                Dim name As String
                name = reader("Customer_Name")
                Premium.Items.Add(name)

                Premium.BorderStyle = BorderStyle.None

            End While


        Catch ex As Exception
            form1.InnerHtml = ex.Message
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub


    Private Sub Frequent_Customers()
        Dim conn As SqlConnection
        conn = New SqlConnection

        Dim constr As String
        constr = "Data Source=WIN-3G30IJDBABE\SQLEXPRESS;Integrated Security=True;Database=PVFC"

        conn.ConnectionString = constr

        Dim cmd As SqlCommand
        cmd = New SqlCommand

        cmd.Connection = conn

        cmd.CommandText = "SELECT Customer_Name, COUNT(Order_Id) AS ORDERS_COUNT "
        cmd.CommandText &= "FROM CUSTOMER_t C, ORDER_t O "
        cmd.CommandText &= "WHERE C.Customer_Id = O.Customer_Id "
        cmd.CommandText &= "GROUP BY C.Customer_Name "
        cmd.CommandText &= "HAVING COUNT(Order_Id) >= 3 "
        cmd.CommandText &= "ORDER BY COUNT(Order_Id) DESC "

        Dim reader As SqlDataReader

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            While reader.Read()
                Dim name As String
                name = reader("Customer_Name")
                Frequent.Items.Add(name)

                Frequent.BorderStyle = BorderStyle.None

            End While


        Catch ex As Exception
            form1.InnerHtml = ex.Message
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub


    Private Sub Bulk_Customers()
        Dim conn As SqlConnection
        conn = New SqlConnection

        Dim constr As String
        constr = "Data Source=WIN-3G30IJDBABE\SQLEXPRESS;Integrated Security=True;Database=PVFC"

        conn.ConnectionString = constr

        Dim cmd As SqlCommand
        cmd = New SqlCommand

        cmd.Connection = conn

        cmd.CommandText = "SELECT C.Customer_Name, AVG(Order_Totals.TOTAL_QUANTITY_PER_ORDER) AS Avg_Quntity_Per_Order "
        cmd.CommandText &= "FROM CUSTOMER_t C,( "
        cmd.CommandText &= "SELECT O.Customer_Id, O.Order_Id, SUM(OL.Ordered_Quantity) AS TOTAL_QUANTITY_PER_ORDER "
        cmd.CommandText &= "FROM ORDER_t O,Order_line_t OL "
        cmd.CommandText &= "WHERE O.Order_Id = OL.Order_Id "
        cmd.CommandText &= "GROUP BY O.Customer_Id,O.Order_Id ) Order_Totals "
        cmd.CommandText &= "WHERE C.Customer_Id = Order_Totals.Customer_Id "
        cmd.CommandText &= "GROUP BY C.Customer_Id,Customer_Name "
        cmd.CommandText &= "HAVING AVG(Order_Totals.TOTAL_QUANTITY_PER_ORDER) >= 5 "
        cmd.CommandText &= "ORDER BY Avg_Quntity_Per_Order DESC; "

        Dim reader As SqlDataReader

        Try
            conn.Open()
            reader = cmd.ExecuteReader()

            While reader.Read()
                Dim name As String
                name = reader("Customer_Name")
                Bulk.Items.Add(name)

                Bulk.BorderStyle = BorderStyle.None

            End While


        Catch ex As Exception
            form1.InnerHtml = ex.Message
        Finally
            cmd.Dispose()
            conn.Close()
        End Try
    End Sub

    Private Sub form1_Load(sender As Object, e As EventArgs) Handles form1.Load

        Premium_Customers()
        Frequent_Customers()
        Bulk_Customers()

    End Sub
End Class

Output

Automating Targeted Marketing

To automate targeted marketing, we can set up a system that automatically runs our SQL queries every month and connects the results to a messaging and shipping service.

Step 1: Assign Specific Rewards

Premium Customers: The system will automatically send a complimentary physical gift to their address via courier, and trigger a personalized "Thank You" SMS/Email.
Frequent Customers: The system will automatically email them a "Loyalty Discount Code" (e.g., 10% off) to reward them for their continuous visits.
Bulk Buyers: The system will automatically email them a wholesale product catalog or offer them special volume-based discounts.

Step 2: Automate the Workflow

Monthly Scheduled Task: We set up a scheduled job in the database to run the three segmentation queries on the 1st of every month.
Trigger Communications: The results are automatically passed to an email/SMS software that sends out the digital discounts and "Thank You" messages.
Track Sent Items: We create a table in our database that will keep track of the given reward.The database updates the tracking table to keep the logs of customers who received a reward

Test Cases

To ensure the robustness of the customer segmentation platform, we must test how the SQL logic and our frontend UI handle various data scenarios.

Test Case 1: No Customer in a Particular Segment

Scenario: The database currently has no customers who meet the threshold for a specific category (e.g., no one has spent over $3,000 to qualify as Premium).
How to Test: We will alter the SQL query for Premium Customers and increase the threshold such that it fetches zero records.and load the page.
Expected Outcome: The SQL query should safely return 0 rows. The "Premium Customers" card should simply render empty without breaking the page layout.

Observed Output

Test Case 2: Uneven Distribution Across Segments

Scenario: There is uneven distribution of customers in the segments.
How to Test: Our data already have uneven distribution of customers amoung the segments.
Expected Outcome: Because we used CSS Flex, the size of each card should be consistent with the size of other cards.

Observed Output

Test Case 3: Overlapping

Scenario: A highly active customer meets the criteria for multiple segments simultaneously.
How to Test: Our data already have overlapping data in segments (eg Contemporary Casuals)
Expected Outcome: The customer's name should appear in multiple lists.

Observed Output

Problem Statement

Customer Segments

1. Premium Customers

SQL Code:

Database Response:

2. Frequent Cutomers

SQL Code:

Database Response:

3. Bulk Customers

SQL Code:

Database Response:

My Solution

Output

Problem Statement

Automating Targeted Marketing

Step 1: Assign Specific Rewards

Step 2: Automate the Workflow

Problem Statement

Test Cases

Test Case 1: No Customer in a Particular Segment

Test Case 2: Uneven Distribution Across Segments

Test Case 3: Overlapping