Retail Analysis

OVERVIEW

PEW Retail Inc. operates globally, selling products to customers in diverse regions. The core challenge was to consolidate and visualize key operational and financial data to answer critical business questions regarding tax liabilities, product category performance, customer value, and regional order distribution. This project aimed to deliver a robust BI dashboard that empowers stakeholders with clear, data-driven insights to optimize business strategies.

OBJECTIVES

The primary objectives of this project were to:

• Quantify tax payments across various global regions where PEW Retail Inc. conducts business.

• Analyze the number of orders received by different product categories to identify top-performing and underperforming segments.

• Identify and highlight the top 5 customers based on their order volume.

• Determine the volume of orders received by various regions, broken down by different product categories and specific products.

• Implement Row-Level Security (RLS) to ensure regional managers could only access data relevant to their respective regions, maintaining data privacy and enabling user-specific reporting.

KEY COLUMNS

The project involved integrating data from Excel into a star-schema data model, utilizing the following key columns:

Fact Table (Transaction Details):

• carrier tracking number

• currency key

• customer key

• due date

• discount amount

• (and other transaction-related columns)

Dimension Tables (Providing additional details):

• Product Category Dimension: Details about product categories (e.g., Accessories, Bikes, Clothing).

• Product Subcategory Dimension: More granular details within product categories.

• Customer Dimension: Customer details, including FirstName for identifying top customers.

• Products Dimension: Specific product information.

• Region/Geography Information: Implicitly used for tax and regional order analysis. 

TOOLS

For this Business Intelligence project, the following tools and technologies were utilized:

• Data Source: Microsoft Excel (for initial data storage).

• Data Modeling & Transformation: Power BI (for designing the star-schema data model and integrating data).

• Visualization & Dashboarding: Power BI (for building the interactive dashboard and visualizing KPIs).

• Data Analysis Expressions (DAX): Used within Power BI for creating custom measures (e.g., [Total Orders], [Top5 Customers]).

• Security Implementation: Power BI (for implementing Row-Level Security (RLS)).

APPROACH

The project followed a structured BI development approach:

Phase 1: Data Acquisition & Modeling

• Integrated data from Excel into Power BI.

• Designed a star-schema data model, establishing clear relationships between the central fact table (tracking transaction details) and various dimension tables (providing context for product categories, customers, and products). This design ensured efficient querying and analysis.

Phase 2: KPI Definition & Calculation

• Defined key performance indicators (KPIs) to address the business questions.

• Developed custom DAX measures within Power BI to calculate metrics such as total tax paid, total orders, and to identify top customers. An example DAX measure for Top5 Customers was specifically crafted to rank customers by total orders.

Phase 3: Dashboard Development & Visualization

• Built an interactive dashboard in Power BI, visualizing the calculated KPIs.

• Designed various charts and tables to present insights on tax payments by region, orders by product category, top customers, and regional order volumes across different product lines.

Phase 4: Security Implementation

• Implemented Row-Level Security (RLS) within Power BI. This involved creating specific roles for different regions, ensuring that each regional manager could only view data pertinent to their area, thereby enhancing data privacy and control.

KEY INSIGHTS

The dashboard provided several actionable insights for PEW Retail Inc. stakeholders:

• Tax Payments by Region: The U.S. incurred the highest tax burden ($751K, contributing 32% of revenue), while Canada paid significantly lower taxes ($158K). The total tax collected globally was $2,348,694.23.

• Orders by Product Category: Accessories dominated sales, accounting for 59.76% of total orders (36,092 orders), significantly outperforming Clothing by 296%. Bikes contributed 15,205 orders, with Clothing having the lowest volume (9,101 orders).

• Top 5 Customers: Eduardo was identified as the top customer with 338 orders, highlighting the importance of high-value customers.

• Regional Order Volume: The U.S. recorded the highest number of orders (21,344), demonstrating a strong market presence, being 284.02% higher than France, which had the lowest volume (5,558 orders).

IMPACTS

This project delivered significant impacts for PEW Retail Inc.:

• Optimized Tax Strategy: Insights into regional tax burdens enable the company to explore strategies for optimizing after-tax profitability, potentially by focusing on lower-tax regions or seeking incentives.

• Informed Product Strategy: Clear data on product category performance allows for strategic resource allocation, prioritizing high-performing categories like Accessories and reassessing strategies for underperforming ones like Clothing.

• Enhanced Customer Relationship Management: Identification of top customers facilitates the development of targeted loyalty programs and personalized marketing efforts to retain and attract similar high-value customers.

• Improved Regional Performance: Understanding regional order volumes allows for tailored campaigns to capitalize on strong markets (e.g., U.S.) and develop strategies to boost sales in regions with lower performance (e.g., France).

• Secure Data Access: The implementation of RLS ensures data privacy and provides customized reporting views for regional managers, fostering trust and efficient data governance.

DELIVERABLES

The key deliverables for this project included:

• Power BI Data Model: A star-schema data model integrating transaction and dimension data.

• PEW Retail Inc. Dashboard: A comprehensive, interactive dashboard built in Power BI, visualizing key performance indicators.

• DAX Measures: Custom DAX formulas for calculating specific metrics and enabling advanced analysis.

• Row-Level Security (RLS) Implementation: Configured RLS roles to manage data access based on user region.

• Insights and Recommendations Report: A summary of the key findings and actionable recommendations derived from the dashboard.