OList-Store-Analysis

Project Overview

In this project, I conducted a comprehensive analysis of customer behavior, product trends, and order patterns within the Brazilian E-commerce powerhouse, Olist. The main objectives were to create a robust database, perform data joins, and conduct statistical inquiries to extract meaningful insights. Visualization was done using Power BI for a more interactive and insightful representation of the findings.

Tools Used

1. Microsoft Excel - Utilized Power Query in Microsoft Excel for meticulous data cleaning. Addressed inconsistencies, removed duplicates, handled missing values, and standardized data formats.

2. SQL - Employed SQL queries and scripts to perform exploratory data analysis and generate key performance indicators

   Link to the sql code :- Olist SQL file

3. Power Bi - Developed interactive visualizations using Power BI to effectively communicate insights to stakeholders.

Dashboard

Exploratory Data Analysis

1. Weekday vs. Weekend Payment Statistics

• Description : Analyzing payment behavior differences between weekdays and weekends.
• Analysis : Segregating payment transactions based on weekdays and weekends. This involves examining the volume of transactions, total payment amounts, and popular payment methods during these periods.
• Insights : Understanding whether there’s a variance in spending habits or preferred payment methods between weekdays and weekends. This information can be crucial for marketing campaigns or resource allocation.
• Findings :
  • Mondays and Tuesdays consistently generate higher sales compared to other weekdays, indicating a trend in consumer spending behavior on these days.
  • A majority (77%) of Olist’s customers are one-time purchasers without repeated transactions, indicating a challenge in retaining customers post their initial purchase.

1. Average Price and Payment Values from Customers of Sao Paulo City
   • Description : Analyzing the average price and payment values made by customers residing in Sao Paulo city.
   • Analysis : Determining the average purchase price and payment amounts made by customers from Sao Paulo city.
   • Insights: Understanding the spending patterns of customers in this specific geographical location and whether they differ significantly from other regions. This information could influence regional marketing strategies or product offerings.
   • Findings :
   • Customers in Sao Paulo City demonstrate a relatively higher average payment value ($143.07) compared to the average price ($112.29). This suggests that customers in this city tend to spend more per transaction. * Sao Paulo City stands out as the top city in terms of payment value or sales, contributing a substantial $2.6 million, i.e 22% to the overall sales highlights the significance of this city in driving revenue for Olist. This indicates the importance of focusing marketing efforts or tailoring services to maximize returns from this key market.
2. Relationship Between Shipping Days vs. Review Scores
   • Description : Investigating the potential impact of shipping duration on customer review scores.
   • Analysis : Analyzing the correlation between the number of days taken for shipping and the review scores provided by customers.
   • Insights : Identifying if there’s a relationship between longer shipping times and lower review scores. This information is valuable in optimizing logistics and customer satisfaction strategies.
   • Findings :
     • Identified a negative correlation between shipping duration and the review scores provided by customers.
     • The negative correlation suggests that extended shipping times might impact customer satisfaction, potentially leading to lower ratings or dissatisfaction with the overall shopping experience.

Recommendations

• Prioritizing quicker delivery times or providing clearer communication about expected delivery dates could positively influence customer perceptions and satisfaction.
• Implementing targeted marketing campaigns aimed at both retaining existing customers and capitalizing on high sales days.
• Focusing on enhancing customer service to encourage repeated engagement and foster loyalty.