Let's Talk
  • Home
  • -
  • Website SQL Analysis
Using SQL to analyze website performance is a daily routine for data scientists. It's not necessary to always use machine learning and deep learning models to create predictive models. Solving business problems is the top task for us rather than creating models. In this project, I use an e-commerce website dataset to analyze some website problems such as traffic source, bounce rate and so on. Even though I'd like to create some predictive models in real life, I only use SQL in this project.
E-commerce Dataset
It's a simplified e-commerce dataset. Usually, there are more tables in real life, but some key tables have been used in this project.

Traffic Source
Traffic source analysis is about understanding where your customers are coming from and which channels are driving the highest quality traffic. Paid traffic is commonly tagged with tracking (UTM) parameters, which are appended to URLs and allow us to tie website activity back to specific traffic sources and campaigns. The first image below shows how many sessions are coming from each campaign. The second image shows the conversion rate from sessions to orders. The third image shows the conversion rate for both devices.



Landing Page
Landing page analysis is also very important to the website. In this SQL query, I used a sub-query to find out the top landing pages. I also could've created a temporary table which is the as the sub-query. After all, the sub-query creates a table as well.

Bounce Rate
To find out the bounce rate for each landing page, there are multiple steps that I have to do. Technically, I could've written a super long sub-query to find out the bounce rates. However, if I have to go through multiple steps, I'd like to create some temporary tables, which is easier for me and others to read.
1, I need to find the website pageview id associated with the website session id.


2, I need to identify the landing page of each session.


3, I need to count pageviews for each session to identify bounces.


4. I need to summarize total sessions and bounced sessions by landing page.



Software used: MySQLWorkbench

Portfolio