📊 Amazon Project

Contents

• Project Overview
• Data Sources
• Tools
• Data Preparation
• Data Analysis
• Results
• Key Learning

Project Overview

This data analysis project aims to provide insights about Amazon phone sales. By analyzing various aspects of the sales data, we seek to identify trends, make data-driven recommendations, and gain a deeper understanding of Amazon performance.

Data Sources

Amazon Dataset: The primary dataset used for this analysis is the "Phone Search.csv" file, containing detailed information about each flight made by every the carrier.

• Download from attachments

Tools

1. Applications

• PowerBI - Creating reports
  • Download here

2. Desing

• Figma: Help me to create an organized background for dashboard

Data Preparation

Power Query: Explore and cleaning the data

I focused on three things: -

• Data Understanding (Types and each column meaning).
• Check duplication and null values and replace them with suitable values.
• Remove duplicates.

Data Analysis

• Power BI: Power Query, DAX measures, and building interactive dashboards

Power Query (Data Integration):

• Explore & Cleaning Using Power Query Tool
• Rename All Columns & Explore Errors
• Coupon Applied (Replace blank with No Coupon)
• Replace any true/false with 0 and 1 to make it easy to count by changing type to whole number
• Product Availability (replace blank with Unknwon)
• Columns unit_price & unit_count are deleted due to 99% nulls (no data)
• Delivery is deleted due to no importance for analysis
• Sales Volume
  • Replace (blank, List:, More Buying Choices, Typical:, Typical price:, other) with 0
  • Replace K with 000
  • Change type to whole number
• Delete 4 Rows (B0CMDLJR6K, B07ZHPCJW3, B07Z6Q9NCZ & B09R6FJWWS) due to null values in 5 columns
• Merge Original Price with Product Price
  • Null values in original price means Original Price = Product Price (no change in price)
  • Using New Custom Column to keep all Original Price values and merge only null with Product Price

  Original Price 2 = [Original Price] ?? [Product Price]
  

• Star Rating (replace null value with mean 4.0), I calculated median and I found 4.1, by quick looking at numbers of null values, its only 3 null values, so, no strong gap between 4.0 & 4.1
• Select all columns and remove duplicates or by removing duplicates from ASIN column
• Clean Product Name by replacing wrong values with sutible values

DAX Measures:

• Add 2 Columns

Discount Price = 'Phone Search'[Original Price] - 'Phone Search'[Min Offer Price]

Product Sales = 'Phone Search'[Product Price] * 'Phone Search'[Sales Volume]

• Add 13 Measures

Amazon Choices = SUM('Phone Search'[Amazon Choice])

Amazon Prime = SUM('Phone Search'[Amazon Prime])

Average Product Price = AVERAGE('Phone Search'[Product Price])

Average Star Rating = AVERAGE('Phone Search'[Star Rating])

Best Sellers = SUM('Phone Search'[Best Seller])

CFP Percentage = AVERAGE('Phone Search'[Climate Friendly Products]) * 100

Number of CFP = SUM('Phone Search'[Climate Friendly Products])

Number of Offers = SUM('Phone Search'[Number Offers])

Number of Products = COUNT('Phone Search'[Product Name])

Total Discount Price = SUM('Phone Search'[Discount Price])

Total Ratings = SUM('Phone Search'[Number Ratings])

Total Sales = SUM('Phone Search'[Product Sales])

Total Sales Volume = SUM('Phone Search'[Sales Volume])

• Add 12 Measures for Filtering Information (for Cards)

ASIN = SELECTEDVALUE('Phone Search'[ASIN],"ASIN")

Availability Status = SELECTEDVALUE('Phone Search'[Product Availability],"Availability Status")

Best = SELECTEDVALUE('Phone Search'[ASIN],"Apple iPhone 12 | Sensyne 92 Cell Phone")

Choice = SELECTEDVALUE('Phone Search'[ASIN],"Panasonic Cordless Phone")

Coupon Applied = SELECTEDVALUE('Phone Search'[Coupon Applied],"Coupon Applied")

Current Price = SELECTEDVALUE('Phone Search'[Product Price],"Current Price")

Discount = SELECTEDVALUE('Phone Search'[Discount Price],"Discount")

Offers = SELECTEDVALUE('Phone Search'[Number Offers],"Offers")

Original Price = SELECTEDVALUE('Phone Search'[Original Price],"Original Price")

Product Rate = SELECTEDVALUE('Phone Search'[Star Rating],"Rate")

Product Ratings = SELECTEDVALUE('Phone Search'[Number Ratings],"Ratings")

Units = SELECTEDVALUE('Phone Search'[Sales Volume],"Sold Units")

Reporting:

• Make 3 reports to get valuable insights from data

Results

• Products Sales Report:
  • Samsung Galaxy S24+ Plus is the highest in sales
  • Moto G Play 2023 has a high sales volum

And based on this report, business can foucs on increase stock for high sales products and make good offers like discount coupons for low sales products when buy high sales products.

• Products Rating Report:
  • Apple IPhone XR has the highest ratings
  • Amazon Choice: Panasonic Cordless Phone
  • Best Sellers: Apple IPhone 12 & Sensyne 92 Cell Phone

And based on this report, I notce that the product with high ratings do not necessarily mean that they have high sales, business can improve ratings system by make each rate with a point which can used for discount redeem code later.

• Products Information Report:
  • Samsung Galaxy S21 5G, US Version, 256GB, Phantom Gray - Unlocked (Renewed) has the highest discount
  • Save 10% & 5% coupons are the highly used between other coupons

Key Learning

💡 Combining Python for heavy data processing with Power BI for interactive dashboards creates a powerful working.