Olist E-Commerce Analysis
Tool Used: Power BI | Data Cleaning, Analysis, and Visualization
Technical Documentation (Data Cleaning and Transformation)
Problem
This is a Brazilian e-commerce public dataset of orders made at the Olist Store. The dataset has information on 100,000 orders from 2016 to 2018 made at multiple marketplaces in Brazil. The dataset includes details such as the status of the order, the price and payment information, customer location, and product attributes. It also includes customer reviews. The geolocation data associate Brazilian zip codes with latitude and longitude coordinates.
There are the CSV files that need to be imported to Power BI.
This is real commercial data, it has been anonymized, and references to the companies and partners in the review text have been replaced.
The following concerns were asked by Olist Store:
- Build the overall dashboard. Investigate the company’s product volume, sales, and customers’ ratings for products.
2. Explore delivery performance. Walk through delivery performance and find ways to optimize delivery times.
3. Investigate product quality. Discover product categories that are more prone to customer dissatisfaction.
There are possible solutions to the 1st problem like increasing brand awareness, making your website user-friendly, improving customer service processes, making an image carousel, and making use of filtering algorithms.
For the 2nd problem, possible solutions are to add delivery areas, especially from the North to South parts of Brazil, to evaluate the high demand for products and why bad reviews skyrocketed from October 2017 to June 2018.
For the 3rd problem, possible solutions are to consider product performance testing, evaluate customers’ feedback, acknowledge and resolve customer complaints, train employees at all levels, and allow employees to be part of the development process.
The proposed solutions will greatly help Olist store to know what strengths and weaknesses of their website, know who’s at fault for late deliveries, and evaluate the product quality. This will help Olist e-commerce to improve the integrity and credibility of their website.
Methodology
The acquired data was examined and studied by a data analyst. The orders are recorded from various dimensions: from order status, price, payment, and freight performance to customer location, product attribute, and finally, reviews written by customers. The geolocation dataset relates Brazilian zip codes to latitude and longitude coordinates. Data cleaning, data transformation, and data visualization using Power BI were accomplished to provide the information requested by the stakeholder. The coding queries focused on the following:
- Payment_value
- Order_id
- Order_approved_at
- Review_creation_date
- Review_Score
These results were the main focus since they would show the product’s quality and the seller’s and the carrier’s delivery efficiency. It took some trial and error to collect more high-quality data, which led to high-quality visualization and recommendations for a trustworthy report.
Findings
1. Build the overall dashboard. Investigate the company’s product volume, sales, and customers’ ratings for products
The total number of products: 112.65k.
Total sales: 16.01M
Top 3 Product’s Rating:
- bed_bath_table = 3.47
- health_beauty = 3.62
- sports_leisure = 3.55
You can hover the x-axis of the “Total Product Orders” table to know the average reviews of specific products of the dataset in the power bi file.
2. Explore delivery performance. Walk through delivery performance and find ways to optimize delivery times.
The delivery performance received a 90.37% on early delivery while only 6.77% was late delivered. Based on the high number of orders in quarter 1 of 2018 and the late deliveries reaching their peak also in quarter 1 of 2018, we can conclude that the late deliveries are mostly due to a sudden increase in product deliveries that the total carriers can’t accommodate them all.
3. Investigate product quality. Discover product categories that are more prone to customer dissatisfaction.
The overall reviews show that the products are reliable and legitimate because it has an average review of 4.09, with 5 as the highest review. It can also be observed that the top bad reviews are also comes from the top-selling products.
Written Insights
Overall Dashboard
Less than 1000 orders for 2 years is very alarming. But considering their low amount of total orders, almost all of the products have a total sales of more than 500,000. They have the potential to compete with other top products.
Customer satisfaction also may suffer as late deliveries persist. Response times will become longer. Eventually, those customers will simply find better service elsewhere.
The Review score drastically increased from an average of 3.56 and maintained its review score of 4 until 2018.
Delivery Performance
The graph shows the Delivery performance of the Seller and the Carrier. It shows that there is a total of 94.55% of Carrier Late Delivery while Seller Late Dispatch accumulated only 5.45% in the past 3 years. It also shows how dedicated the seller is by giving the order on time with 97.74% of the total order passed to the carrier. It also shows that the carrier’s late delivery was increasing every quarter. This concludes that it’s mostly the carrier’s fault for having late deliveries all over the years.
Product Quality
It shows that there are total review score of 57.78% for 5, 19.29% for 4, 8.24% for 3,3.18% for 2, and 11.51% for 1. It also has a total Average Review of 4.09 which is means they have a high product quality, knowing the highest scale is 5. Overall, they have total sales of 16.01M Sales in span of 2 years. It can also be observed that the highest bad reviews were also the high number of orders sold.
Bad reviews also increased as the quarter progressed.
It can also be seen in the graph that the highest number of bad reviews goes again from Oct 2017 to June 2018. The highest number of demands for the products correlates with the highest number of bad reviews.
Due to the high demand for the products, maybe the seller did not maintain consistency for their products because of lack of time and preparation, and mostly they are also overwhelmed by the product’s demand.
Recommendations
Overall Dashboard
Investigating a company’s product volume, sales, and customer ratings can provide valuable insights into their performance, competitiveness, and customer satisfaction, helping to inform future business decisions.
- Increase brand awareness: Building brand awareness can help increase trust and repeat purchases, which in turn will impact sales.
- Make your website user-friendly: Ensuring that your website is user-friendly for all platforms, including mobile, can help increase sales. It has a mobile application for Android cellphones but not for IOS.
- Improve customer service processes: Effective customer service processes, from ordering to shipping, can increase customer satisfaction and loyalty. There are possibilities that some customers did not go back to your service because of late deliveries.
- Make an image carousel (slideshow) in your home tab that shows some products that are less marketed but have high sales. This can make the customers engage with underrated products.
- Make a recommendation of products based on what they have searched. Olist can use these 2 algorithms:
- Collaborative Filtering is a method of making product recommendations based on the preferences of similar users. The idea behind collaborative filtering is that people who have similar tastes and preferences in the past will have similar tastes and preferences in the future.
- Content-Based Filtering: This algorithm recommends items based on the properties of the items themselves. It uses information about the user’s past preferences and the attributes of the products to make recommendations.
This will make the customer check other products that will improve the sales performance of other products.
Delivery Performance
Delivery performance is a crucial aspect of an e-commerce store’s success. The ability to implement new strategies and solutions is essential to meeting customer expectations and scaling the business. By tracking and analyzing delivery performance metrics, an e-commerce store can build customer trust, increase efficiency, and scale its business.
- Assuming that Olist started last quarter of 2016, their service started to escalate as the quarters of 2017 progressed. I suggest that they will thoroughly see their records on October 2017 to June 2018 to check why they have a sudden increase in orders that might result in late deliveries.
- They should also check how 2018 late deliveries began to maintain their on-time deliveries as the quarter progresses. Their problem is also overpopulated customers from the North to South parts of Brazil. Maybe there is something that catches the attention of the customers on October 2017 to June 2018 that strikes the number of customers’ orders. They should increase deliverymen or find delivery partnerships in these areas to accumulate all deliveries, especially on notable occasions. They should research more on holidays and notable occasions that might increase the customers’ eagerness to buy products online.
Product Quality
Product quality is essential for the success of any e-commerce store. It builds customer trust, increases customer satisfaction, reduces returns and complaints, improves the brand image, and differentiates the store from its competitors.
- Consider product performance testing. Testing the product to ensure that it performs as desired can help identify any quality issues before the product is released to the market.
- Evaluate Customers’ Feedback. Feedback from customers can highlight aspects of the product that need improvement. This can help businesses to refine their products and better meet the needs of their customers.
- Acknowledge and resolve customer complaints to improve customer satisfaction levels and ensure their loyalty.
- Train employees at all levels. Providing training and development opportunities for employees can help improve their skills and knowledge, leading to higher-quality products.
- Allow employees to be part of the development process. Involving employees in the development process can lead to valuable feedback and ideas for improving product quality.
