Database Analysis and Automation for an online Toy Store using SQL

Introduction

The project involves advanced SQL queries to explore, analyze and optimize Ecommerce database (contains around 1 million record) for an Ecommerce company using MySQL Workbench. The objective is to generate trends on website source traffic, website performance, company's performance, product analysis, user analysis and the use cases associated with them to optimize the Ecommerce database for efficient website performance and also improve the sales with the help of the insights drawn.

Technologies Used

1. MySQL
2. MySQL Workbench
3. Joins
4. Temporary tables
5. Aggregate functions
6. Triggers
7. SQL views

Traffic Source Analysis

Traffic Analysis helps to understanding where the customers are coming from and which channels (email, social,search,direct) are driving the highest quality traffic.

USE CASES:

1. Analysing search data and investing the campaigns that gives strongest CVR conversion rates.
2. Comparing user behaviour pattern across traffic sources to inform creative strategy.
3. Identifying opportunities to identify the wasted spend by company /scale high conversion traffic.

Website Performance Analysis

Website Performance analysis is performed to understand which pages are seen the most by users, to identify where to focus on improving the business. A/B Testing and funnel conversion are adopted in analysing website performance.

USE CASES:

1. Finding the most viewed pages by the customers.
2. Identifying the most common entry pages to the website- the first thing a users sees.
3. To understand how those pages perform the business objectives for above two cases.

Product Sales Analysis

To analyse how each product contribute to the business and how product launches impact the overall portfolio.

USE CASES:

1. Analysing the sales and revenue by products.
2. Monitoring the impact of adding a new product to product portfolio.
3. Watching product sales trends to understand overall health of business.

User Behaviour Analysis

To understand user behaviour and identify some most valueable customers.

USE CASES:

1. Analysing repeat activity to see how often customers are coming back to visit your site.
2. Understanding which channel customes use when they come back.

Company's Performance Data Analysis

USE CASES:

1. To tell the story of the company's growth using trended performance data.
2. Analyse current performance and use available data to assess upcoming opportunities.

Conclusion

This project illustrates making use of SQL to analyzing and optimizing the Ecommerce databases for efficient Ecommerce and web analytucs. The use of Joints, Aggregate Functions, Temporary Tables, Triggers and SQL Views to analyse and optimize the Ecommerce database for efficient website performance which in turn improves the sales of the business using insights drawn and providing recommendations.