E-Commerce Sales Analysis

Project Overview

AdventureWorks is a global manufacturer of cycling equipment and accessories. The management team needs a way to track KPIs (sales, revenue, profit, returns), compare regional performance, analyze product-level trends, and identify high-value customers.

Data Source

A folder containing raw csv files, which contain information about transactions, returns, customers, and sales territories.

Data Cleaning and Preparation

The following tasks were performend in the initial data preparation

• Data loading and inspection
• Creating new categorical columns like Order quantity type (Single Item, Multiple Items), customer priority (Standard or Priority) etc
• Adding more useful columns to the Calendar table

Data Modeling

A relational data model was created. Below is the data model created:

DAX Measures

Several DAX measures were created, some of which a listed below:

% of All Orders = DIVIDE([Total Orders], [All Orders])

% of All Returns = DIVIDE([Total Returns], [All Returns])

10-day Rolling Revenue = 
CALCULATE(
    [Total Revenue],
    DATESINPERIOD(
        'Calendar'[Date],
        MAX(
            'Calendar'[Date]
        ),
        -10,
        DAY
    )
)

90-day Rolling Profit = 
CALCULATE(
    [Total Profit],
    DATESINPERIOD(
        'Calendar'[Date],
        MAX(
            'Calendar'[Date]
        ),
        -90,
        DAY
    )
)

All Orders = 
CALCULATE(
    [Total Orders],
    ALL('Sales Data')
)

All Returns =
CALCULATE(
[Total Returns],
ALL('Returns Data')
)

Average Retail Price = AVERAGE('Product'[ProductPrice])

Average Revenue Per Customer = 
DIVIDE(
    [Total Revenue], [Total Customers]
)

Bike Return Rate = 
CALCULATE(
    [Return Rate], 'Product Categories'[CategoryName] = "Bikes")

Bike Returns = 
CALCULATE([Total Returns], 'Product Categories'[CategoryName] = "Bikes")

Bike Sales = CALCULATE(
    [Quantity Sold], 'Product Categories'[CategoryName] = "Bikes")

Bulk Orders = 
CALCULATE([Total Orders],'Sales Data'[OrderQuantity] > 1)

High Ticket Orders = CALCULATE([Total Orders], 
FILTER('Product', 'Product'[ProductPrice] > [Overall Average Price]))

Order Target = [Previous Month Orders]*1.1

Order Target Gap = [Total Orders] - [Order Target]

Overall Average Price = CALCULATE([Average Retail Price], ALL('Product'))

Previous Month Orders = 
CALCULATE(
    [Total Orders],
    DATEADD(
        'Calendar'[Date],
        -1,
        MONTH
    )
)

Previous Month Profit = 
CALCULATE(
    [Total Profit],
    DATEADD(
        'Calendar'[Date],
        -1,
        MONTH
    )
)

Previous Month Returns = 
CALCULATE(
    [Total Returns],
    DATEADD(
        'Calendar'[Date],
        -1,
        MONTH
    )
)

Previous Month Revenue = 
CALCULATE(
    [Total Revenue],
    DATEADD(
        'Calendar'[Date],
        -1,
        MONTH
    )
)

Profit Target = 
[Previous Month Profit]*1.1

Profit Target Gap = [Total Profit] - [Profit Target]

Quantity Returned = SUM('Returns Data'[ReturnQuantity])

Weekend Orders = 
CALCULATE([Total Orders], 'Calendar'[Weekend] = "Weekend")

Total Profit = [Total Revenue] - [Total Cost]

Total Orders = DISTINCTCOUNT('Sales Data'[OrderNumber])

Total Revenue = SUMX(
    'Sales Data', 'Sales Data'[OrderQuantity]*
    RELATED(
        'Product'[ProductPrice]
        )
    )

Total Returns = COUNT('Returns Data'[ReturnQuantity])

YTD Revenue = 
 CALCULATE(
    [Total Revenue],
    DATESYTD(
        'Calendar'[Date]
    )
 )

Report

A report was created with the following pages:

Executive Dashboard

Map

Product Detail

Customer Detail

Dashboard with Slicer Panel open