ANALYSIS OF THE OPERATING PERFORMANCE FOR E-COMMERCE COMPANY
Task description
You work in a consulting group. Your client, an e-commerce company from Brazil, has requested a complete analysis of their operating performance. They want a detailed report covering three main parts: general dashboard, delivery performance, and product quality. Based on this analysis report, the company’s management will decide the future development of the business. The dataset contains information on 100k orders from 2016 to 2018 made at several marketplaces in Brazil. Various order data are recorded: from order status, price, payment, and freight performance to customer location and product attributes. Also, you have access to customer reviews. The geolocation dataset relates Brazilian zip codes to latitude/longitude coordinates. It is actual commercial data that has been made anonymous; references to the companies and partners in the review text were replaced.
Task 1: Import Data to Power BI
Task 2: Perform Data cleaning and transformation.
a. Replace blank values and drop duplicates.
b. Add geolocation to customers and buyers.
Task 3: Create a data model that will represent the relationship between datasets.
Task 4: Create a general dashboard. Investigate the company's production volume, sales, and customer product ratings.
Task 5: Exploring delivery performance
a. Create a new page in the report view and name it as Delivery Performance.
b. Walk through the delivery performance and find ways to optimize delivery times.
c. Calculate DAX measures for the seller's late dispatch and courier's late delivery.
d. Here are the DAX columns and measures you can calculate and use in your visuals:
- Difference between purchase and approval in minutes.
- Difference between approval to deliver to customers in days.
- Difference between delivered to customer and estimated delivery in days.
- If the package was delivered later than the estimated delivery date, then it's late.
- Difference between delivered to the carrier and estimated delivery in days.
- If the package was sent to the carrier later than the estimated delivery date, then it's a seller's late dispatch. If the package was sent to the carrier 5 or more days earlier than the estimated delivery date but the delivery date to the customer is later than the estimated date, then it's a carrier's delay.
e. Provide a written comment with your insights and recommendations.
Task 6: Investigating product quality
a. Create a new page in the report view and name it as Product Quality.
c. Calculate DAX measures for bad reviews (those less than 3), and build a time series visualization.
b. Discover product categories that are more prone to customer dissatisfaction.
