Global Sales Analysis

OVERVIEW

Global Super Store, operating across major product categories like furniture, office supplies, and technology, faced the challenge of effectively analyzing its vast global sales data to inform strategic business decisions. This project aimed to transform raw historical sales data into actionable intelligence, enabling the store to better plan inventory, streamline operations, and gain a deeper understanding of product and customer dynamics on a global scale. 

OBJECTIVES

The primary objectives of this project were to:

• Analyze global sales trends over time to identify growth patterns and seasonal fluctuations.

• Understand sales performance across various geographical regions and countries.

• Evaluate the sales contribution and trends of different product categories.

• Identify top-performing products and understand customer purchasing behavior.

• Provide insights for optimizing inventory levels and business processes based on sales data.

• Implement robust data security measures to control access to regional sales data.

KEY COLUMNS

The project utilized a star-schema data model with an Orders table as the fact table and several dimension tables:

Fact Table (Orders):

• Category

• City

• Country

• Customer Name

• Market

• Region

• Order Date

• Unit Price

• Quantity

• Profit

• (and other order-related details like Order ID, etc.)

Dimension Tables:

• Returns Table:

  • Order ID

  • Region

  • Returned (indicator)

• People Table:

  • Person (Regional Manager Name)

  • Region (Assigned Region)

• Date Table:

  • Month Name

  • Week Number

  • Year

TOOLS

• Data Source: Historical sales data from Excel.

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

• Data Analysis Expressions (DAX): Used within Power BI for creating custom measures and calculations.

• Visualization & Dashboarding: Power BI (for building the interactive sales dashboard).

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

APPROACH

The project followed a systematic approach to build a comprehensive and secure sales dashboard:

Phase 1: Data Modeling

• Designed a star-schema data model in Power BI.

• Established the Orders table as the central fact table.

• Integrated dimension tables (Returns, People, Date) and defined relationships to enable efficient querying and analysis across various dimensions.

Phase 2: DAX Measure Development

• Created key DAX measures to derive critical business metrics:

  • As of Date: Dynamically displays the report's freshness.

  • Profit Ratio: Calculates the ratio of total profit to total sales.

  • Total Profit: Sums up the profit from all orders.

  • Total Sales: Calculates total sales by summing Unit Price * Quantity for each order.

  • UserPrincipalName: Retrieves the current user's principal name for security and personalization.

Phase 3: Dashboard Development & Visualization

• Developed an interactive sales dashboard in Power BI.

• Visualized sales trends over time, showing overall growth and specific peak periods.

• Presented sales performance by country, market, region, and product category.

• Highlighted top-performing customers and regional managers.

• Implemented bookmarks for easy navigation between Total Profit and Total Sales graphs.

Phase 4: Row-Level Security (RLS) Implementation

• Created five key roles based on Global Super Store's operating markets: Africa, Asia Pacific, Europe, LATAM, and USCA.

• Applied RLS to restrict data access, ensuring that regional managers could only view data pertinent to their assigned markets, enhancing data governance and privacy.

KEY INSIGHTS

The dashboard provided several critical insights for Global Super Store:

• Significant Sales Growth: Total Sales trended up, showing a remarkable 444.86% increase between January 2012 and December 2015. A steep incline of 101.85% ($1,255,800.5) was observed in just 5 months from July to December 2015.

• Regional Disparities: Sales varied drastically across 165 countries, ranging from $20.0 to $11,931,578.1. The United States had the highest Total Sales, significantly outperforming countries like The Gambia, which had the lowest.

• Category Performance: Technology had the highest Total Sales ($23,493,698.9), followed by Furniture ($20,086,582.5) and Office Supplies ($18,687,583.7). Office Supplies showed an interesting recent trend, rising by 88.15% ($3,005,595.9) in 3 years since 2012.

• Top Performers: Gilbert Wolff was identified as the top customer with the highest Total Sales ($387,583.4), significantly higher than the lowest performer among key individuals.

IMPACTS

This project delivered significant impacts for Global Super Store:

• Optimized Inventory Planning: Detailed sales trends and product category performance insights enable more accurate forecasting and inventory management, reducing stockouts and overstock.

• Enhanced Business Process Planning: Understanding peak sales seasons and regional performance allows for better allocation of resources and strategic planning of business operations.

• Targeted Marketing & Sales Strategies: Identification of high-performing markets, categories, and customers facilitates the development of targeted promotions and sales campaigns.

• Improved Customer Understanding: Analysis of customer behavior helps in tailoring product offerings and improving customer retention strategies.

• Secure Data Access: RLS implementation ensures that sensitive sales data is accessible only to authorized personnel, maintaining data integrity and compliance.

• Performance Monitoring: The dashboard serves as a central tool for continuous monitoring of key sales KPIs, allowing for agile responses to market changes.

DELIVERABLES

The key deliverables for this project included:

• Power BI Data Model: A robust star-schema data model integrating sales, returns, people, and date information.

• Custom DAX Measures: A set of calculated measures for key financial and operational metrics.

• Interactive Sales Dashboard (Power BI): A comprehensive dashboard visualizing sales trends, regional performance, category performance, and top customers.

• Bookmarks: Implemented bookmarks for seamless navigation between Total Profit and Total Sales views.

• Row-Level Security (RLS) Implementation: Configured RLS roles for different markets to ensure secure data access.

• Insights and Recommendations Report: A summary of key findings and actionable recommendations for business growth and optimization.