This dataset was generously provided by Olist, the largest warehouse in the Brazilian markets. Olist connects small businesses across Brazil to channels seamlessly and with a single contract. These merchants can sell their products through Olist Store and ship them directly to customers using Olist's logistics partners. To learn more, visit our website: www.olist.com

This dataset actually already exists on Kaggle, however I decided to create this version to fix certain things and make it easier to analyze.

Since this data comes from an online store, they are all separate from each other, which allows us to connect them and only the ID code. They also still need to be cleaned and processed, so I created this version, for anyone who wants to do an analysis without worrying about cleaning and how to merge them all into one data set. I also did attribution engineering, I think it's very useful for finding more insights.

1. order_id: Unique identifier of the order.
2. order_item_id: Identifier of the item in the order.
3. customer_id: Customer identifier.
4. customer_unique_id: Unique customer identifier.
5. customer_zip_code_prefix: Customer's ZIP code prefix.
6. customer_city: Customer's city.
7. customer_state: Customer's state.
8. product_id: Product identifier.
9. product_category_name: Name of the product category.
10. product_name_length: Length of the product name.
11. product_description_length: Length of the product description.
12. product_photos_qty: Quantity of product photos.
13. product_weight_g: Product weight in grams.
14. product_length_cm: Product length in centimeters.
15. product_height_cm: Product height in centimeters.
16. product_width_cm: Product width in centimeters.
17. seller_id: Seller identifier.
18. seller_city: Seller's city.
19. seller_state: Seller's state.
20. seller_zip_code_prefix: Seller's ZIP code prefix.
21. payment_type: Payment type.
22. payment_sequential: Payment sequential.
23. payment_installments: Number of payment installments.
24. installments_price: Price of installments.
25. price: Product price.
26. freight_value: Freight value.
27. payment_value: Total payment value.
28. shipping_limit_date: Shipping deadline date.
29. order_purchase_timestamp: Purchase timestamp.
30. order_approved_at: Order approval timestamp.
31. order_delivered_carrier_date: Delivery date to the carrier.
32. order_delivered_customer_date: Delivery date to the customer.
33. order_estimated_delivery_date: Estimated delivery date.
34. shipping_duration: Shipping duration.
35. day_of_purchase: Day of purchase.
36. month_of_purchase: Month of purchase.
37. year_of_purchase: Year of purchase.
38. month/year_of_purchase: Month and year of purchase.
39. order_status: Order status.
40. order_unique_id: Unique order identifier.

Enzo Schitini

Data Scientist • Expert Bubble.io • UX & UI @ Nugus creator

I imported the two Olist Kaggle datasets into an SQLite database. I modified the original table names to make them shorter and easier to understand. Here's the Entity-Relationship Diagram of the resulting SQLite database:

https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F2473556%2F23a7d4d8cd99e36e32e57303eb804fff%2Fdb-schema.png?generation=1714391550829633&alt=media" alt="Database Schema">

Data sources:

https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce

https://www.kaggle.com/datasets/olistbr/marketing-funnel-olist

I used this database as a data source for my notebook:

SQL Challenge: E-commerce data analysis

Brazilian E-Commerce Public Dataset by Olist

Welcome! This is a Brazilian ecommerce public dataset of orders made at Olist Store. The dataset has information of 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil. Its features allows viewing an order from multiple dimensions: from order status, price, payment and freight performance to customer location, product attributes and finally reviews written by customers. We also released a geolocation dataset that relates Brazilian zip codes to lat/lng coordinates.

This is real commercial data, it has been anonymised, and references to the companies and partners in the review text have been replaced with the names of Game of Thrones great houses.

Join it With the Marketing Funnel by Olist

We have also released a Marketing Funnel Dataset. You may join both datasets and see an order from Marketing perspective now!

Instructions on joining are available on this Kernel.

Context

This dataset was generously provided by Olist, the largest department store in Brazilian marketplaces. Olist connects small businesses from all over Brazil to channels without hassle and with a single contract. Those merchants are able to sell their products through the Olist Store and ship them directly to the customers using Olist logistics partners. See more on our website: www.olist.com

After a customer purchases the product from Olist Store a seller gets notified to fulfill that order. Once the customer receives the product, or the estimated delivery date is due, the customer gets a satisfaction survey by email where he can give a note for the purchase experience and write down some comments.

Attention

1. An order might have multiple items.
2. Each item might be fulfilled by a distinct seller.
3. All text identifying stores and partners where replaced by the names of Game of Thrones great houses.

Example of a product listing on a marketplace

https://i.imgur.com/JuJMns1.png" alt="Example of a product listing on a marketplace">

Data Schema

The data is divided in multiple datasets for better understanding and organization. Please refer to the following data schema when working with it: https://i.imgur.com/HRhd2Y0.png" alt="Data Schema">

Classified Dataset

We had previously released a classified dataset, but we removed it at Version 6. We intend to release it again as a new dataset with a new data schema. While we don't finish it, you may use the classified dataset available at the Version 5 or previous.

Inspiration

Here are some inspiration for possible outcomes from this dataset.

NLP:
This dataset offers a supreme environment to parse out the reviews text through its multiple dimensions.

Clustering:
Some customers didn't write a review. But why are they happy or mad?

Sales Prediction:
With purchase date information you'll be able to predict future sales.

Delivery Performance:
You will also be able to work through delivery performance and find ways to optimize delivery times.

Product Quality:
Enjoy yourself discovering the products categories that are more prone to customer insatisfaction.

Feature Engineering:
Create features from this rich dataset or attach some external public information to it.

Acknowledgements

Thanks to Olist for releasing this dataset.

OlistStar Dataset Builder

Extracting Features from the Olist Brazilian E-Commerce Dataset with the aim of simplifying the extraction process and making it public, I have created a SQL code and implemented some exciting transformations on the dataset. These transformations have been specifically designed to predict customer satisfaction through the score assigned to each order.

The Olist Brazilian E-Commerce dataset is a valuable resource that provides insights into the e-commerce landscape in Brazil. Building upon this extensive dataset, I have carefully extracted relevant features and now share them with the community. By leveraging my SQL code and applying various transformations, I have uncovered powerful insights.

The primary objective of this effort was to develop meaningful features that enable the prediction of customer satisfaction based on the order score. These transformations have been meticulously crafted to provide actionable information and unlock the secrets behind customer satisfaction.

By making this dataset available, I hope that fellow researchers and Kaggle enthusiasts can benefit from these transformations and explore new possibilities in the field of customer satisfaction analysis. Together, we can enhance our understanding of this vital aspect of e-commerce and develop innovative solutions to improve the customer experience.

Please do not hesitate ask any question and thank you for olist to share with us original dataset.I just cleaned and manipulated by creating new dataset.

Marketing Funnel by Olist

Welcome! This is a marketing funnel dataset from sellers that filled-in requests of contact to sell their products on Olist Store. The dataset has information of 8k Marketing Qualified Leads (MQLs) that requested contact between Jun. 1st 2017 and Jun 1st 2018. They were randomly sampled from the total of MQLs.

Its features allows viewing a sales process from multiple dimensions: lead category, catalog size, behaviour profile, etc.

This is real data, it has been anonymized and sampled from the original dataset.

Joining with Brazilian E-Commerce Public Dataset by Olist

This dataset can also be linked to the Brazilian E-Commerce Public Dataset by Olist using seller_id. There you will find information of 100k orders, price, payment, freight performance, customer location, product attributes and finally reviews written by customers.

Instructions on joining are available on this Kernel.

Context

This dataset was generously provided by Olist, the largest department store in Brazilian marketplaces. Olist connects small businesses from all over Brazil to channels without hassle and with a single contract. Those merchants are able to sell their products through the Olist Store and ship them directly to the customers using Olist logistics partners. See more on our website: www.olist.com

A seller join Olist through a marketing and sales funnel that was made public at this dataset. Description of steps:

1. Sign-up at a landing page.
2. Get contacted by a Sales development Representative (SDR), confirm some information and schedule a consultancy.
3. Consultancy is made by a Sales Representative (SR). The SR may close the deal (lead sing up) or lose the deal (led leaves without sign in)
4. Lead becomes a seller and starts building his catalog on Olist.
5. His products are published on marketplaces and ready to sell!

Attention

1. A seller MQL might come from multiple sources (he might subscribe on two different landing pages, for instance).

Examples of Landing Pages

https://i.imgur.com/jKZTP5e.png" alt="Example of a landing page"> https://i.imgur.com/mAljYcq.png" alt="">

Data Schema

The data is divided in multiple datasets for better understanding and organization. Please refer to the following data schema when working with it: https://i.imgur.com/Jory0O3.png" alt="">

Inspiration

Here are some inspiration for possible outcomes from this dataset.

Customer Lifetime Value:
How much a customer will bring in future revenue?

SR/SDR Optimization:
Which SR or SDR should talk with each kind of lead?

Closing Prediction:
Which deals will be closed?

EDA:
Just Have Fun!

Acknowledgements

Thanks to Olist for releasing this dataset.

This dashboard was created from data published by Olist Store (a Brazilian e-commerce public dataset). Raw data contains information about 100 000 orders from 2016 to 2018 placed in many regions of Brazil.

The raw datasets were imported into Excel using “Get data” option (formerly known as “Power Query”) and cleaned. An additional table with the names of Brazilian states was also imported from the Wikipedia page.

A Data Table about payment information was created based on imported statistics with the usage of nested formulas. Then, proper pivot charts were used to build an Olist Store Payment Dashboard which allows you to review the data using a connected timeline and slicers.

Bu veri seti, Brezilya e-ticaretindeki müşteri davranışlarını, sipariş ayrıntılarını ve ödeme işlemlerini analiz etmek amacıyla derlenmiştir. Veri setinin temel kaynağı, küçük işletmeleri ülke genelindeki müşterilerle buluşturan Brezilya merkezli bir çevrim içi pazar yeri olan Olist platformudur. Siparişler, ödemeler, müşteri yorumları ve satıcı ayrıntılarına ilişkin veriler içeren bu veri seti; müşteri memnuniyeti, satın alma alışkanlıkları ve işletme performansı gibi konularda bilgi edinmeyi amaçlamaktadır. Brezilya ve dünya genelinde giderek önem kazanan e-ticaret sektöründen ilham alarak hazırlanan veri seti; market sepeti analizi, zaman serisi tahmini ve müşteri segmentasyonu gibi çeşitli analizler için sağlam bir temel sunmaktadır.

This dataset was compiled to analyze customer behaviors, order details, and payment transactions within Brazilian e-commerce. The dataset's primary source is the Olist platform, a Brazilian online marketplace that connects small businesses to customers across the country. It includes data on orders, payments, customer reviews, and seller details, aiming to support insights on customer satisfaction, purchasing patterns, and business performance in the e-commerce sector. Inspired by the growing significance of e-commerce in Brazil and globally, the dataset provides a robust foundation for various analyses, from market basket analysis to time series forecasting and customer segmentation