Implementation Report of Lab 07

Overview

The objective of this lab was to incorporate business intelligence into the Pine Valley Furniture Company web application by adding a Customer Segmentation Analysis module. This lab builds upon the RBAC system developed in Lab 06 and introduces purchase-behaviour-based segmentation visible to employees through a dedicated interface.

Segmentation divides the customer base into distinct groups based on purchasing habits, enabling targeted marketing and data-driven business decisions.

HTML, ASPX & VB Code

IAD Lab 07 Code.pdf

Problem 1(a) Customer Segment Definitions

The following six segments were defined based on purchase behaviour:

Segment	Definition / Criteria
Premium Customers	Top 5 customers by total amount spent across all orders.
Frequent Customers	Customers with more than 3 orders placed in total.
Bulk Buyers	Customers who have ordered more than 15 units in total across all orders.
At-Risk Customers	Customers whose last order was placed more than 1 year ago.
Loyal Customers	Customers with 2 or more distinct orders (repeat buyers).
New Customers	Customers whose first order was placed within the last 60 days.

Problem 1(c) SQL Queries for Segmentation

Premium Customers top 5 by total spending:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    SUM(OL.Ordered_Quantity * P.Standard_Price) AS TotalSpent
FROM CUSTOMER_t C
JOIN ORDER_t O       ON C.Customer_Id = O.Customer_Id
JOIN Order_line_t OL ON O.Order_Id    = OL.Order_Id
JOIN PRODUCT_t P     ON P.Product_Id  = OL.Product_Id
GROUP BY C.Customer_Id, C.Customer_Name
ORDER BY TotalSpent DESC;

Frequent Customers more than 3 orders:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    COUNT(O.Order_Id) AS TotalOrders
FROM CUSTOMER_t C
JOIN ORDER_t O ON C.Customer_Id = O.Customer_Id
GROUP BY C.Customer_Id, C.Customer_Name
HAVING COUNT(O.Order_Id) > 3
ORDER BY TotalOrders DESC;

Bulk Buyers more than 15 units ordered:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    SUM(OL.Ordered_Quantity) AS TotalUnits
FROM CUSTOMER_t C
JOIN ORDER_t O       ON C.Customer_Id = O.Customer_Id
JOIN Order_line_t OL ON O.Order_Id    = OL.Order_Id
GROUP BY C.Customer_Id, C.Customer_Name
HAVING SUM(OL.Ordered_Quantity) > 15
ORDER BY TotalUnits DESC;

At-Risk Customers last order over 1 year ago:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    MAX(O.Order_Date) AS LastOrderDate
FROM CUSTOMER_t C
JOIN ORDER_t O ON C.Customer_Id = O.Customer_Id
GROUP BY C.Customer_Id, C.Customer_Name
HAVING MAX(O.Order_Date) < DATEADD(YEAR, -1, GETDATE());

Loyal Customers 2 or more distinct orders:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    COUNT(DISTINCT O.Order_Id) AS OrderCount
FROM CUSTOMER_t C
JOIN ORDER_t O ON C.Customer_Id = O.Customer_Id
GROUP BY C.Customer_Id, C.Customer_Name
HAVING COUNT(DISTINCT O.Order_Id) >= 2
ORDER BY OrderCount DESC;

New Customers first order within last 60 days:

SELECT TOP 5 C.Customer_Id, C.Customer_Name,
    COUNT(O.Order_Id) AS TotalOrders
FROM CUSTOMER_t C
JOIN ORDER_t O ON C.Customer_Id = O.Customer_Id
GROUP BY C.Customer_Id, C.Customer_Name
HAVING MIN(O.Order_Date) >= DATEADD(DAY, -60, GETDATE());

Problem 3 Test Cases for Segmentation Analysis

#	Test Case	Input / Condition	Expected Output
1	No customers in a segment	Select "Frequent Customers" when no one has more than 3 orders	Table is empty and message "No customers found in this segment." is shown
2	No customers in all segments	Select "Show All Segments" when orders data is empty	All segment sections show "No customers found in this segment."
3	Different data in each segment	Some segments have customers while others do not	Each segment shows correct results; empty ones show no-data message
4	Voucher clicked without Premium selected	Select a different segment and click "Send Discount Voucher"	Message shows "Please select Premium Customers first."
5	No premium customers exist	Select "Premium Customers" but no matching data exists	Message shows "No premium customers found."
6	Unauthorized access	Open Segmentation page without Employee login	User is redirected to Employee Login page
7	No segment selected	Click "Show Segment" without selecting any option	No action is performed and no error is shown

When the "Send Discount Voucher" button is clicked after selecting Premium Customers, a discount voucher is sent to all premium customers.

Technical Details

The application uses ASP.NET Web Forms with VB.NET and connects to SQL Server via ADO.NET (SqlConnection, SqlCommand, SqlDataAdapter, DataTable).

Session variables (Session("Role"), Session("Employee_Id"), Session("Customer_Id")) control access. Every employee-facing page validates the role on Page_Load and redirects unauthenticated users to the appropriate login page. The segmentation module is entirely employee-side and is unreachable from the customer-facing navigation.

Dynamically generated controls (GridViews inside the "Show All" panel) are rebuilt on every postback inside btnShow_Click since dynamic controls do not survive the ASP.NET page lifecycle automatically.

Implementation Report