eCommerce_database_analysis_using_MySQL

1. The Dataset is of a soft toy company - Maven Fuzzy Factory which has data of 3 years, with tables such as Orders, Website_Sessions, Website_Pageviews, Products, Order_items, and Order_items_refunds with close to 5 lakhs of records. The analysis was done as follows:

2. Traffic source analysis : Identified which channels are giving high-quality traffic, paid channels, organic search, or direct type-in. Based on the conversion rate of website sessions to orders placed the importance of source channels was determined. Understood the value of segments of paid marketing for an optimized marketing budget

3. Landing Page Analysis: Identified the landing page performance with the help of the Bounce rate Bounce rate tells the % of customers who left the website after seeing the landing page without exploring the website more.

4. Conversion Funnel Analysis: Analyzed each step the customers take to optimize their user experience. Identified the number of customers moving onto the next page and customers abandoning the page. Helps to optimize critical pain points where users are abandoning and improve the user experience there to convert those customers.

5. Seasonality Analysis: Identified when business is performing well and when not, to prepare better for upcoming spikes or slowdowns. Understood which product will be most purchased during sales and stocking it in inventory.

6. Product Level Analysis: Analyzed sales and revenue generated by products. Understood the impact of adding new products.

7. Product Level Website Analysis: Analyzed which products generated the most attention when displayed on the multi-product page by studying the conversion funnel of every product. Analyzed the impact of new products on website conversion rate.

8. Cross-selling Products: Analyzed which products customers are most likely to purchase together and thus offer smart product recommendations. Understood impact of cross-selling on conversion rates and revenue

9. Product refund Analysis: Studied the refund rates according to products, to determine if the supplier is providing quality materials. The refund rate for cheaper products is less compared to costlier products.

10. Customer Repeat Behaviour Analysis: Identified the most valuable customers and their behavior by taking into account the number of sessions, the number of transactions, average order value, and conversion rate. Understood which channels they used while coming back, to know if we were paying again for them.