In April, I took part in a data challenge hosted by the Twitter data community. The challenge focused on analyzing a dataset from Olist, a well-known e-commerce company in Brazil. This dataset provided crucial details about various aspects of Olist's operations, such as product listings, sellers, and customer interactions. My objective was to explore the dataset and discover valuable insights based on specific questions. The challenge organizers meticulously curated and prepared the dataset to ensure that participants, including myself, could extract meaningful information and gain insights relevant to Olist's business.
- Validate and clean the data to improve accuracy and usability
- Analyze the data to address the problem statement
- Offer recommendations to Olist company
The Olist dataset comprises nine separate datasets, all of which are stored in CSV format:
- olist_order_items_dataset
- olist_orders_dataset
- olist_order_payments_dataset
- olist_order_reviews_dataset
- olist_products_dataset
- olist_customers_dataset
- olist_sellers_dataset
- olist_geolocation_dataset
- olist_category_dataset
The olist Data Dictionary that offers a complete overview of the dataset. This data dictionary is a valuable reference as it provides in-depth explanations of each variable and its significance within the dataset. It serves as a useful tool for understanding and interpreting the data effectively.
With the messy table on the left-hand side and the cleaned table on the right-hand side, the cleaning process involved various steps to ensure the data was accurate and consistent. In the messy table, you could see inconsistencies, missing values, duplicates, and other issues that could potentially impact the analysis. However, after applying rigorous data cleaning techniques, the cleaned table now presents a more organized and reliable dataset. The cleaning process involved handling missing values by imputing or removing them, standardizing formats, correcting inconsistencies, removing duplicates, and addressing any other data quality issues. As a result, the cleaned table provides a solid foundation for conducting meaningful analysis and extracting valuable insights from the dataset.
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
|
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
 |
| Messy | Cleaned |
|---|---|
 |
 |
- What is the total revenue generated by Olist, and how has it changed over time?
- How many orders were placed on Olist, and how does this vary by month or season?
- What are the most popular product categories on Olist, and how do their sales volumes compare to each other?
- What is the average order value (AOV) on Olist, and how does this vary by product category or payment method?
- How many sellers are active on Olist, and how does this number change over time?
- What is the distribution of seller ratings on Olist, and how does this impact sales performance?
- How many customers have made repeated purchases on Olist, and what percentage of total sales do they account for?
- What is the average customer rating for products sold on Olist, and how does this impact sales performance?
- What is the average order cancellation rate on Olist, and how does this impact seller performance?
- What are the top-selling products on Olist, and how have their sales trends changed over time?
- Which payment methods are most commonly used by Olist customers, and how does this vary by product category or geographic region?
- How do customer reviews and ratings affect sales and product performance on Olist?
- Which product categories have the highest profit margins on Olist, and how can the company increase profitability across different categories?
- Geolocation having high customer density. Calculate customer retention rate according to geolocations.
The total revenue generated by Olist was approximately R$15.9 million. The quarter with the highest revenue was the second quarter of 2018, while the lowest revenue was generated in the third quarter of 2016, with revenues of approximately R$3.3 million and R$136.23, respectively.
The total number of orders placed on Olist was 98,207. In January 2017, Olist received the highest number of orders, with approximately 7,423 orders. The lowest number of orders occurred in September 2018 and December 2016, with only one order each.
3) What are the most popular product categories on Olist, and how do their sales volumes compare to each other?
The most popular product category on Olist is "bed bath table," and as we examine the popularity from the most to the least popular categories, we observe a significant decrease in their sales volume. This decrease in sales volume from the most popular to the least popular categories suggests certain implications.
4) What is the average order value (AOV) on Olist, and how does this vary by product category or payment method?
| by payment method | by product category |
|---|---|
 |
 |
The average order value (AOV) across all payment methods on Olist is approximately R$153.44. When categorizing by payment method, voucher payments had the lowest AOV of R$62.33, while credit card payments had the highest AOV of R$162.70. Furthermore, when categorizing by product category, computers had the highest AOV, followed by fixed telephony.
| Active sellers | Active sellers |
|---|---|
 |
 |
Olist had a total of 4,312 active sellers. The highest number of active sellers was in 2018 with 2,833, followed by 1,784 in 2017 and the lowest number of active sellers was 145 in 2016. On a quarterly basis, Olist experienced an increase in active sellers throughout most quarters, except for a decline in the last quarter of 2018 (Q3) where it had 52 sellers fewer compared to the previous quarter (Q2), with 1,680 sellers in Q2 and 1,628 sellers in Q3.
| Distribution of sellers | Correlation co-effecient |
|---|---|
 |
 |
It can be concluded that sellers with review scores of 4 and 5 had the highest number of orders, indicating positive sales performance. Furthermore, sellers with higher review scores also generated higher total revenue, suggesting a correlation between positive reviews and increased sales. However, it is interesting to note that sellers with a review score of 1 had the highest average revenue, indicating that other factors may influence sales performance beyond just review scores.
7) How many customers have made repeated purchases on Olist, and what percentage of total sales do they account for?
Out of the 96,096 customers in Olist, a small fraction of 2,997 customers have made repeat purchases, implying a low customer retention rate. The majority of customers (93,099) have only made a single purchase and have not returned for additional purchases. Moreover, the sales generated by repeat customers account for only 13% of the total sales.
8) What is the average customer rating for products sold on Olist, and how does this impact sales performance?
| Average rating | Impact on Sales performance |
|---|---|
 |
 |
The average rating on Olist is 4, indicating a positive impact on sales performance. This high average rating suggests that customers are generally satisfied with their purchases, which can lead to increased customer trust, loyalty, and higher sales. The positive word-of-mouth recommendations from satisfied customers may also attract new customers, further contributing to revenue growth. Additionally, products with higher average ratings are likely to be perceived as reliable and of good quality, increasing their desirability and potentially driving higher sales volumes.
9) What is the average order cancellation rate on Olist, and how does this impact seller performance?
| Cancellation rate | Effect on Sales performance |
|---|---|
 |
 |
Olist's cancellation rate of 0.48 suggests a low percentage of order cancellations, indicating that the majority of customers complete their purchases. This positive trend can be seen as a favorable sign for Olist's sales performance, indicating that customers are content with their buying decisions and have trust in the products and services provided by Olist.
Computer accessories reign supreme as the best-selling items on Olist, closely followed by health and beauty products, watches and gifts, bed and bath essentials, and sport and leisure items.
11) Which payment methods are most commonly used by Olist customers, and how does this vary by product category or geographic region?
| Number of orders with each payment methods | Payment method by geographical location |
|---|---|
 |
 |
Credit card was used for 76505 orders, making it the most common payment method. Boleto followed with approximately 19784 orders, while voucher and debit card were used for around 3866 and 1528 orders, respectively. In São Paulo, "Health Beauty" products were purchased 71 times using debit card as the payment method. Additionally, "Bed Bath Table" and "Health Beauty" were popular product categories paid for with credit cards. In Minas Gerais, credit card payments were mainly associated with "Health Beauty" products, in São Paulo, voucher payments were most commonly associated with auto products, while boleto payments were used for "Bed Bath Table" products. Credit cards were frequently used for "Computer Accessories" and "Health Beauty" products.
Excellent! which has a 5-star rating, received approximately 56,902 reviews. Out of the total purchases of 23,220 products , a significant number, 5-star ratings were given. On average, each product in the generated a revenue of R$160.78. Interestingly, as we move down the rating score, the revenue decreases. This observation implies that Olist may have pricing issues that need to be addressed in order to adjust and optimize revenue.
13) Which product categories have the highest profit margins on Olist, and how can the company increase profitability across different categories?
The profit margins across different product categories vary significantly. The highest profit margin is observed in the "Fixed telephony" category with a percentage profit margin of 327.78, followed by "Signaling and security" (287.02) and "Drinks" (286.87). On the other hand, categories like "Furniture mattress and upholstery" (88.63) and "Home appliances 2" (91.01) have relatively lower profit margins.
14) Geolocation having high customer density. Calculate customer retention rate according to geolocations.
Geolocations with higher customer densities indicate areas where the company has a larger customer base and potentially higher market demand. Geolocations with relatively higher customer densities include Acre (4.94%), Rondonia (4.35%), Mato Grosso (3.42%), Rio de Janeiro (3.34%), and Goias (3.27%). The customer retention rate is a measure of the percentage of customers who make repeated purchases. It provides insights into customer loyalty and the effectiveness of the company's strategies to retain customers.
To improve customer retention and address low ratings on certain products, here are some recommendations:
- Olist should improve product quality and consistency to meet customer expectations. (Note: Consistently delivering high-quality products builds trust and loyalty among customers.)
- Olist should enhance customer service by training representatives and implementing efficient support channels. (Note: Providing excellent customer service ensures prompt and effective resolution of concerns, fostering positive customer experiences.)
- Olist should personalize the customer experience using data and segmentation strategies. (Note: Tailoring recommendations, offers, and communication based on customer insights creates a personalized and engaging experience.)
- Olist should implement loyalty programs to reward repeat purchases. (Note: Loyalty programs with exclusive discounts and incentives encourage customers to stay engaged and loyal to the brand.)
- Olist should gather and act on customer feedback to make improvements and enhance satisfaction. (Note: Actively listening to customer feedback allows for identifying areas of improvement and demonstrates a commitment to customer satisfaction.
Regarding the low-rated/high revenue products:
- Olist should identify and resolve quality issues through process improvements and rigorous checks. (Note: Analyzing low-rated products helps address recurring quality issues, ensuring better product performance.)
- Olist should improve customer communication to manage expectations. (Note: Clear and detailed product information helps customers understand the product's features, limitations, and usage, reducing negative experiences.)
- Olist should proactively address customer concerns by responding to negative reviews. (Note: Promptly and professionally addressing negative reviews shows the company's dedication to resolving issues and improving customer satisfaction.)
- Olist should continuously improve products based on feedback and market trends. (Note: Regular evaluation and innovation keep products aligned with customer needs and preferences.)
- Olist should offer exceptional customer service to regain trust and loyalty. (Note: Going above and beyond to resolve issues and provide excellent service can turn negative experiences into positive ones, rebuilding customer trust.) By implementing these recommendations and considering the accompanying notes, Olist can enhance customer retention, address low ratings, and foster stronger customer relationships.