Product Analysis

Project Title: UK Regional Product Analysis

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About Dataset & Files

Dataset Source: DataCamp

Introductory Key Points:

• The Dataset is Contained with 5 tables. Those are pointed below:

➡️ 4 dimension tables:

• Date (Primary Key Column Name: Date)

• Products (Primary Key Column Name: Product_SKU)

• Retailers (Primary Key Column Name: Retailer_ID)

• Returns (Primary Key Column Name: Order_ID)

➡️ 1 fact table, titled "Orders" , has relationship with all 4 dimension tables.

• The report is Contained with 4 Dashboards.

Click Here to Download the Report File

Dashboard - 1: Product Profiles

Visual Description:

• The Slicer  visual, titled "Select Product Name", contains all the product names reported in the "Products" table in the dataset. Report user should be able to select a single product name from the slicer. 

• The 4 card visuals  whose values automatically change based on the filter context of "Select Product Name" slicer are introduced below:

  • • 1. Order: This card shows the total number of orders for the selected product, placed by the customers.

      2. Revenue: This card shows the total revenue earned by selling the selected product.

      3. Profit: This card shows the total profit gained by selling the selected product.

      4. Profit Margin: This card shows the parcentage of profit margin made from the selected product. This profit margin is calculated from the ratio of total profit & total revenue earned from the product.

Aside from the Card visuals, other visuals in this dashboard which are free from the filter context of the slicer "Select Product Name" are briefly described below:

• The Clustered Column Chart, titled "Number of Products by Different Colors", shows the horizontal list of different product colors with their corresponding number of products. The size of bars in this visuals vary based on the number of products. From the highest (Blue, 225 products) to the lowest (Lavender, 5 products), there are total 12 different colors (mentioned in "Products" table) placed in this visual.

• The Donut Chart, titled "Order Percentages for Different Product Size", shows a comparative data of percentages among different product sizes, based on the number of products. The unique sizes of products listed in the "Products" table are: "S (Small)", "M Medium)", "L (Large)", "XL (Extra Large)", and "XS (Extra Small)". From this visual, it seems that the amount of S-size products are the largest (over 27%), although XS-size products are the smallest (under 14%).

• The Stacked bar Chart, titled "Total Products Categories by Revenue", shows the vertical list of product categories with their respective earned revenues. This visual context is similar to the "Number of Products by Different Colors" column chart where the size of the bars change by the amount of revenue earned. From the highest (Hodies & Sweatshirts, revenue $1.96M) to the lowest (Bras & Tops, revenue $0.24M), there are total 7 different product categories (mentioned in "Products" table) placed in this visual.

• The Pie Chart, titled "Total Products by Gender", shows the comparison of both number and percentages for product amounts for different genders (mainly "Men" and "Women"). This visual shows that products for men is higher (671, over 55%) than the products for women (539, under 44%), based on the reported dataset.

Dashboard - 2: Product Analysis by Dates

Visual Description:

• The Slicer  visual, titled "Select Date Range", contains all the dates reported in the "Date" table in the dataset. Report user should be able to select a range of dates from this slicer. Based on the date range selected by user, all the other visuals of this dashboard will change accordingly.

• There are 3 KPI Cards in this dashboard. In all KPI cards here, when the measured value meets the target value, then the trend axis color becomes Green, otherwise it will be Red. Those cards are briefly introduced below:

  • • 1. Monthly 5% Profit Target: This KPI shows whether the calculated monthly earned profit meets the 5% monthly profit target or not. Here the big number represents the measured monthly earned profit (based on the filter context of "Select Date Range" slicer), and the "Target"  holds the Monthly 5% profit target. The profit target is set in a way that current month profit should be 5% or more than the previous month profit. The trend axis is set for the "Start of Month", and the percentage value in the bracket shows how far the calculated profit is far from the target profit. For example, if this visual shows €62.00K with red color, it means that the measured monthly profit did not fulfill the target monthly profit.

      2. Quarterly 10% Profit Target: This KPI shows whether the calculated quarterly earned profit meets the 10% quarterly profit target or not. This visual has similar context like "Monthly 5% Profit Target" KPI card, where the profit is calculated on quarterly basis assuming 10% target. Here the trend axis is taken by the "Start of Quarter". For instance, if this visual shows €214.25K with red color, it means that the measured quarterly profit did not fulfill the target quarterly profit.

      3. Yearly 20% Profit Target: The context of this KPI is similar to both "Monthly 5% Profit Target" and "Quarterly 10% Profit Target", where the profit is calculated on yearly basis assuming 20% target. Here the trend axis is taken by the "Start of Year". As an example, if this visual shows €0.96M with red color, it means that the measured yearly profit did not fulfill the target yearly profit.

• There are 2 Gauge Charts in this dashboard which are briefly explained below:

  • • 1. Current Month Order vs Target: This gauge chart shows the distance between the current month order and monthly target order. For each filter context of the "Select Date Range"  slicer, the target orders of month change. The target order is set in such a way that current month order should be at least 5% higher than the previous month order.

      2. Current Month Revenue vs Target: This gauge chart is exactly similar to the "Current Month Revenue vs Target" gauge chart, showing the distance between the current month revenue and monthly 5% target revenue.

• The Aria Chart, titled "15-days Rolling Profit", shows the calculated rolling profit for every 15 days plotted against the dates selected by the filter of "Select Date Range" slicer.

• The single Table  visual in the left side of the dashboard shows the normal totals & cumulative totals (based on years and quarters) of the earned revenue. There are total 4 columns in this table:

  • • 1. Start of Month: This column holds all the dates that represent the start of the month in the "Date"  table of the dataset.

      2. Revenue: This column displays the total amount of monthly revenue earned, and the values are placed for every start of the month.

      3. YTD Revenue: This column shows the cumulative total Year-to-Date revenue for every start of the month, where the cumulative total ends in the completion of every year.

      4. QTD Revenue: This column shows the cumulative total Quarter-to-Date revenue for every start of the month, where the cumulative total ends in the completion of every quarter.

Dashboard - 3: Returned Product Profiles

Visual Description:

• The Table  visual, titled "Monthly Profiles of Returned Products", shows the different numbers related to the returned products for all 12 months of a year, mentioned in the dataset. There are total 4 columns in this table:

  • • 1. Month: This column holds the names of all months mentioned in the "Returns"  table.

      2. Total Amount of Products Returned: This column displays the total number of returned products in each  month.

      3. No. of Categories of Returned Product: This column displays how many categories of products returned in each  month.

      4. Total Losses from Returned Products: This column shows the summation of the losses from the returned products in each month.

• There are 3 Card Visuals in this dashboard, and those are briefly explained below:

  • • 1. Total Returned Products: This card shows the total number of returned products (13,764).

      2. Total Losses from Returned Product: This card shows the total amount of losses occurred (€214.25K) from the returned products.

      3. No. of Categories of Returned Products: This card shows the total number of product categories (528) returned back to the stores.

• The Funnel Chart, titled "Total Losses from Returned Products in Each Year", shows the total amount of losses occurred in different years reported in the dataset. The years mentioned in this dataset are collected from the "Returns" table. From this chart, it is observed that the least loss (€10.06K) has been reported in the latest year (2022) when the year before that (2021) faced the most losses (€57.94K).

• The Waterfall Chart, titled "Quantities of Returned Products in Each Day of Weeks", displays some bars as a waterfall scenario visual based on the number of returned products for each week. From this chart, it can be observed that the highest products were returned in Tuesday (2,316), where the lowest products were returned in Sunday (1,727).

• The Treemap  visual, titled "No. of Products Returned by Duration", displays a comparative analysis of returned product amounts based on the returne durations. This visual shows almost half of the returned products (6,767) were returned between 50 to 100 days after ordering, while the least amount of products (2,170) were returned back to the store after more than 100 days.

Dashboard - 4: Order Analysis by States & Retailer Channels

Visual Description:

• There are 2 Slicer visuals in this dashboard, both are user-defined. The context of both slicers are taken from the "Retailers" table, where Report user can select any one item or all items (by pressing the "Select all" option). Both vertically-listed slicers are briefly introduced below: 

  • • 1. Select UK States: This slicer contains all the three UK states (England, Scotland, and Wales) listed in the dataset.

      2. Select Retailer Channel: This slicer contains all the four retailer channels (Franchise, Local store, Small chain store, Supermarket) listed in the dataset.

• The Clustered Column Chart, titled "Total Orders by Retailer Channel for Every Country", shows the different bars representing the total orders based on the UK states and Retailer channels. From this visual, it is seen that highest orders were recorded from England regarding all the retailer channels, while orders from the Wales were the lowest. Besides, most orders were created from the Franchise among all the retailer channels.

• The Map Visual, titled "Sale Profiles of 3 UK States", shows the geographical positions of all three listed USA Sates mentioned in "Retailers" table, in world map.  The bubble size of this visual varies based on the total amount of orders confirmed in differnet states by the respective customers. Report user should be able to see the State Name, Total Order, Total Revenue, Total Profit, and Total Loss of the corresponding state while putting the cursor on the city's bubble position. This visual changes based on the filter context of "Select Retailer Channel" slicer.