Decision Support System

Role: Project Lead

Tools Used: Excel, Python, Power BI

Project Overview

As part of a graduate assignment, I co-created a Decision Support System (DSS) for Target, one of America’s largest department store chains, aiming to improve operational efficiency, customer satisfaction, and revenue. Target, facing challenges with inventory management, customer retention, and sales forecasting, required a robust system to analyze customer behavior, market trends, and performance metrics. This DSS aimed to help Target make data-driven decisions, enhance forecasting, and respond more dynamically to changes in the retail landscape. 

Disclaimer: This project was created using fictitious data and is not intended for actual business use. It does not reflect or represent actual data or insights from Target Corporation. 

My Role and Contributions

• Led the project by communicating regularly with my partner, ensuring efficient collaboration and task delegation.

• Coordinated the integration of multiple data sources to ensure the system was comprehensive and reliable. 

• Guided the design and implementation of the DSS architecture.

• Oversaw data analysis and the creation of Key Performance Indicators (KPIs) to measure success. 

• Implemented an interactive dashboard to provide real-time, actionable insights.

• Created a presentation in PowerPoint to convey results.

Key Business Needs 

Customer Segmentation: Target's ability to highlight specific customer segments through personalized marketing campaigns was a central focus. The DSS helps decision-makers understand customer preferences and behaviors, boosting customer engagement and retention.

Customer Churn: By reducing churn by identifying at-risk customers, the DSS would enable more proactive customer retention strategies, contributing to cost savings and long-term customer loyalty. 

Tracking Marketing Effectiveness: The system provides real-time data to track marketing campaigns and their impacts on conversion and retention rates. 

Key Performance Indicators

Customer Lifetime Value (CLV): Identifies which segments respond to marketing efforts, aligning products with customer preferences, thus driving higher sales and improving customer satisfaction.

Conversion Rate: Identifies which segments react to marketing efforts, aligning products with customer preferences, thus driving higher sales and improving customer satisfaction.

Customer Churn: Assesses the customer retention efforts and identifies potential issues with specific segments. Improves overall satisfaction.

Average Order Value: Optimizes pricing strategies and marketing initiatives, leading to increased revenue. 

ETL Process Overview

Original Datasets:
The initial data was sourced from two CSV files on Kaggle:

1. Retail Sales Dataset

2. Customer Segmentation Dataset

Both datasets were cleaned, transformed, and appended to create a unified dataset that could be used for analysis.

Data Transformation and Cleaning Steps

1. Data Cleaning:

• Deleted Columns:

  • Retail Sales Dataset: Removed TransactionID since CustomerID is the unique identifier.

  • Customer Segmentation Dataset: Removed the Country column (non-US data irrelevant to Target) and StockCode (irrelevant as CustomerID was sufficient).

• Renamed/Transformed Columns:

  • Customer Segmentation Dataset:

    • Renamed InvoiceDate to Date and reformatted to match the retail sales dataset.

  • Retail Sales Dataset:

    • Cleaned CustomerID by removing the “CUST” prefix to match the format in the customer segmentation data.

    • Renamed PricePerUnit to UnitPrice to align with the customer segmentation dataset.

• Added Columns:

  • Customer Segmentation Dataset:

    • Added a TotalAmount column by multiplying UnitPrice with Quantity.

2. Data Unification:

• The datasets were combined in Power Query after cleaning. Additional steps to unify the data were performed:

  • Removed negative values from numerical columns.

  • Removed rows with missing CustomerID.

  • Imputation of missing data: Randomly assigned ages and genders for missing entries using Excel formulas (=RANDBETWEEN(18, 30) for age, random gender assignment).

  • Date Transformation: Changed all 2010 dates to random dates between January and December 2023 to align with the project’s timeframe.

Machine Learning and Feature Engineering

Text Classification Model

The customer segmentation dataset included product descriptions. These descriptions were used to derive product categories using a Naive Bayes classification model in Python. A vectorizer was applied to convert text data into numerical format, which was then trained and tested to predict product categories. These predicted values were then imputed into the dataset.

US Region Assignment

A new column, US Regions, was created by randomly assigning regional values to customer data in Python. This aimed to segment the data better to meet KPIs based on geographic regions.

Final Dataset

 After undergoing the described steps, the final cleaned and transformed dataset was written to a new CSV file, ready for analysis. This dataset, now containing useful features like TotalAmount, US Regions, and predicted product categories, was used for further analysis and to derive actionable insights for Target.

Dashboard

Power BI: KPI Dashboard Creation

• Dashboard Design and DAX Formulas:
  After cleaning and unifying the data, we used Power BI to create a KPI Dashboard. Several DAX formulas were applied to calculate key performance indicators (KPIs).

  • My Contributions:

    • Created the Customer Lifetime Value (CLV) and Average Order Value (AOV) KPIs.

    • Responsible for the primary formatting of the dashboard to ensure its cohesiveness and clarity.

  • Partner's Contributions:

    • Created the Churn Rate and Conversion Rate KPIs.

    • Supplied the additional tables necessary for these calculations.

• KPI Dashboards:
  The KPI Dashboard allowed us to track and visualize key metrics, including:

  • Customer Lifetime Value (CLV)

  • Average Order Value (AOV)

  • Customer Churn Rate

  • Conversion Rate

Insights and Impact

The KPI dashboard significantly enhanced decision-making by providing actionable insights into key performance metrics, allowing leadership to make informed, data-driven decisions. Here’s how:

1. Targeted Marketing Strategies: The 28% conversion rate highlighted the effectiveness of tailored campaigns, enabling the refinement of marketing efforts to resonate with high-value customer segments.

2. Resource Allocation: Insights into the highest average order value (AOV) in the clothing category and underperformance in other categories helped prioritize investment and promotional efforts to maximize returns.

3. Customer Retention: With a churn rate of 4%, below the industry benchmark, the dashboard underscored successful retention strategies while identifying areas to reduce churn further.

4. Revenue Optimization: The high Customer Lifetime Value (CLV) among young adult females in the southern U.S. focused on maintaining relationships with this profitable segment and replicating success across other demographics.

5. Strategic Forecasting: Real-time data visualization enabled leadership to identify trends quickly and adjust strategies proactively, fostering agility in responding to market shifts.

Presentation

References

Bridges, M. (2024). Top 10 customer segmentation and analysis KPIs. Medium. https://markbridges.medium.com/kpi-management-series-customer-segmentation-and-analysis-kpise70278643992 

Chien, C. F., & Chen, K. S. (2008). The Use of the Sales Forecasting Model to Enhance the Performance of Retailers. Journal of Business Research, 61(4), 463-471 

Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2018). Managerial Accounting. McGraw-Hill Education. 

Linker, E. (2024). The ultimate guide to KPIs in the retail industry. Improvado. https://improvado.io/blog/retail-kpi 

Petrovic, M. (2021). How to develop KPI’s in 7 steps. https://www.people.ai/blog/how-to-develop-kpis 

Sauter, V. L. (2010). Decision support systems for business intelligence. Wiley. 

Salesforce. (2024). How to calculate customer churn & revenue rate. https://www.salesforce.com/sales/analytics/customer-churn/ Statista. (n.d.). 

Target – Statistics & facts. htps://www.statista.com/topics/1914/target/#topicOverview 

Vinoline, A., & Deepika, M. (2023). A study on the effect of sales forecasting on the enterprises. Eur. Chem. Bull, 12(2), 2243-2250. DOI:10.31838/ecb/2023.12.s2.372

Nolen, L. Jeannette. (2024). Target Corporation, Britannica Money, https://www.britannica.com/money/Target-Corporation 

Muse, Tyler. (2024) Target, history oasis, https://www.historyoasis.com/tags/target 

Powell, S. (2002). Decision Support Systems: Concepts and Resources for Managers. New York: Wiley