Olist had made it's data public for the timeframe 2016 to 2018. Its features allows viewing an order from multiple dimensions: from order status, price, payment and freight performance to customer location, geolocation data, product attributes and finally reviews written by customers.
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.
The Data is present in Kaggle
In 2019, Olist chief executive and founder Tiago Dalvi said the company plans to expand the number of sellers it serves to 100,000 in two years from current 7000. We have data from 2016 to 2018 and will be analysing it from this perspective. The increase in sellers means increase in the commissions to Olist from the sales.
- Python: Data scrapping, cleaning, augmenting and merging and initial EDA
- Azure PostgreS Database: Storing the clean data
- SQL: ER Diagram, Relationship creation
- Tableau: Visualizations
- How is the growth in revenue (main KPI) in this timeframe?
- How to increase the seller base? Does that impact the Revenue
- Are there any regions where sellers might be not sufficient? How is it impacting the customers?
- Which products needs more attention?
- What could be the predicted revenues?
- Customer orders and it creates an order in orders dataset with status 'created'
- Order is approved and it creates the record in order_items dataset and the status is changed to 'approved'
- Order is processed and the status is changed to 'processing'
- Order is handed to logistics of Olist and the status is changed to 'shipped'
- Order is delivered to customer and the status is changed to 'delivered'
- Data is 98% accurate.
- For the 2% records, the values in date fields are not in sync. For example, in shipping limit date, there is date for 2020 which is far ahead of actual deleivered to carrier date and since our data is for until 2018, it is absurd.
- For records with order_status as invoiced, processing, shipped, unavailable and approved, there are null values in deleivered carrier date and delivered customer date. We checked the reviewes dataset and these packages were actually missing.
-
Hypothesis 1: Increasing sellers will increase our product palatte, thus attracting more customers resulting in more revenue Result: Proved Wrong Why: The Sellers were increaseing in 2018 from Jan to Aug but the Gross Merchandise Value was stagnant
-
Hypothesis 2: Average Basket Value and Average Basket Size impact the GMV. Result: Proved Wrong Why: The ABV was oscillating between 120 to 150. ABS was also stagnant (greater than 1) throughout 2017 and 2018. GMV was growing in 2017 but it became stagnant in 2018.
-
Hypothesis 3: Increasing customers base with directed marketing of products will increase revenue Result: Proved right Why: There were two parts to it: i) Customer Segmentation based on frequency of purchase. It was found that only 3% customers were returning back to Olist. Maximum revenue was being generated by only new one time customers. This has to be changed because there are limited people and thus limited new customers that would purchase on Olist. Thus, Olist should aim to encourage customers to return and buy on Olist
ii) How: By studying the product categories that frequent purchasers buy vs that one time purchasers buy It was found that Bed, Bath and Table are bought by the frequent purchasers and contribute the highest (13%) to GMV. As compared to this, the one time buyers buying Bed bath and Table contribute only 7%. Also, Furniture Decor is the new category found in frequent purchasers category that contribute more than 8% in GMV and it is not prominent for one time buyers.
Olist should focus on retaining the customers by
i) Cross or Up-selling of Bed, Bath and Table to first time customers
ii) Promote furniture decor to first time customers