Brazil E-Commerce Dashboard - Power BI

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.

Tasks

1. Investigate the company' s production volume, sales, and customer product ratings. 2. Walk through the delivery performance, and find ways to optimize delivery times. Optimizing delivery times is a great way to ensure that our customers get the packages on time. To do this, you d need to find out which of the parties are causing the most delay in the deliveries. 3. Discover product categories that are more prone to customer dissatisfaction.

Data Visualization (Dashboard)

Data Insights

1. The average order fulfillment process is the time it takes customers to receive their ordered item, which is 12 days. This further suggests that the company may need to explore ways to shorten the order fulfillment process, such as streamlining the supply chain or improving the delivery logistics. 2. It only takes 10 minutes for an order to be confirmed in the e-commerce store. This indicates that the company's online ordering process is efficient and user-friendly, which could contribute to a positive customer experience. 3. From 2016 to 2018, the carrier pickup time has improved from 13 days to only 3 days, a significant improvement. However, the average duration of last-mile delivery from carrier to customer has increased by around 4 to 5 days. This trend may be alarming, and the company may need to identify the reasons behind the increase and take appropriate measures to improve the delivery time. 4. Heavy items requiring truck delivery, such as furniture, computer sets, and bed and bath items, have the highest average order fulfillment time before they are delivered to customers. This is because these items may require more resources, such as workforce and transportation before they can be delivered, which prolongs the order fulfillment process. The company may need to explore ways to optimize its delivery process for heavy items. 5. States located in the north of Brazil, such as Roraima, Amapa, and Amazonas, have the highest average order fulfillment time, 27-29 days before the orders are delivered. This tendency may be due to transportation risks, rising gas prices, and the number of orders received in these areas. This insight suggests that the company may need to explore ways to improve its delivery logistics in these areas to shorten the order fulfillment time. 6. Overall, the company can deliver ordered items 93.23% of the time, which is an outstanding achievement as it means that the company can meet the estimated delivery date most of the time. This insight suggests that the company is doing an excellent job in terms of delivery logistics, which could contribute to a positive customer experience and feedback.

Recommendations

1. With the average last-mile delivery duration showing a gradual increase in 2017 and 2018, it is essential to analyze the data to identify potential causes and solutions. This analysis could involve examining delivery routes, identifying bottlenecks, and evaluating carrier performance. Once this analysis is complete, we can schedule an alignment meeting with our partner carrier to discuss the results and collaborate on implementing solutions to improve last-mile delivery times. 2. To reduce last-mile delivery duration, ensuring that our courier service has an adequate workforce or riders to deliver goods, especially during peak seasons, is vital. We should consider analyzing historical delivery data to determine peak season delivery volume and identify opportunities to increase staffing during these periods. This could involve implementing flexible staffing solutions or partnering with third-party logistics providers to augment our delivery resources. 3. Providing Rewards & Recognition for our warehouse and delivery riders can effectively motivate our team and help them meet their daily/monthly quota of deliveries. This could involve providing bonuses or incentives for meeting or exceeding delivery goals and creating a positive work environment that encourages teamwork and collaboration. 4. To reduce last-mile delivery duration in Northern Regions, we could consider building or renting a warehouse to improve logistics and streamline delivery routes. We could also hire delivery riders or logistics providers with local knowledge and expertise to help optimize delivery routes and reduce delivery times. We should analyze historical delivery data and consider population density and road infrastructure to determine the most effective strategies for lowering delivery duration in this region. 5. Partnering with other logistical companies specializing in heavy or oversized deliveries can effectively reduce order fulfillment duration for these types of deliveries. We should analyze historical data on heavy and oversized deliveries to determine the frequency of these orders and identify potential partners that can assist with fulfilling them. We should also evaluate potential partners based on factors such as pricing, delivery times, and quality of service to ensure that we select the most effective partners for our needs.