Business Intelligence & Data analysis | Advanced MySQL

Name: Business Intelligence & Data analysis | Advanced MySQL
Rating: 3.6 (15 reviews)

Learn Advanced MySQL skills in real-world project as a Senior data analyst

Created byMohannad Alhariri

Last updated 3/2024

English

What you'll learn

Write Advanced SQL queries and analyze database
Learn TEMPORARY TABLES and SUBQUERIES to handle complex multistep data problem
Analyze data for eCommerce real-world case where you can solve tasks
Mastering JOIN statements across multiple tables
Get life time access to resources and videos

Course content

7 sections • 42 lectures • 7h 3m total length

Introduction5:11
1.2 Connecting database to the server5:05
1.3 Creating the Database and intro to MySQL workbench3:57
Explore the MySQL workbench interface, including schema and query editor, learn to run queries, view results, and troubleshoot errors while analyzing tables such as orders, products, and website sessions.
1.4 Course Outlines and intro to database2:37

Warming Up2:56
Analyze ecommerce data in MySQL to compare traffic sources and campaigns, compute conversions from sessions to orders, and pivot results to track device-based trends for optimizing marketing spend.
2.2 Analyzing Traffic Source13:20
2.3 Finding Top Traffic5:07
2.4 Traffic Source Breakdown6:50
2.5 Bid Analysis and Optimization- Part 118:08
2.6 Bid Analysis and Optimization- Part 26:46
Analyze conversion rates from sessions to orders by device type to identify whether desktop or mobile drives more volume, using date and utm filters (source and campaign).
2.7 Trend Analysis11:59

3.1 Introduction and outlines0:49
Analyze website page views to identify the most viewed pages, calculate bounce rates and landing page trends, and perform multi-step analysis with a temporary table and competitive analysis using Workbench.
3.2 Analyzing website pageviews- Temporary Table20:25
3.3 Calculating Bounce Rate32:42
Explore bounce rate and conversion rate across landing pages a and b, testing performance and using multi-step analysis with temporary tables to improve the preferred page.
3.4 Landing Page Analysis21:17
3.5 Landing Page Trend Analysis12:07
Analyze weekly trends for the home and landing pages, examining page search numbers, brand traffic, and bounce rate, using multi-step analysis and session-first page joins.
3.6 Building and Analyzing Conversion Funnel- Subquery30:27

4.1 Introduction1:26
Analyze each marketing channel—email, social, direct or search—by sessions, orders, and conversion rate to measure performance and guide focus; compare new channels with existing ones and analyze direct-to-brand traffic.
4.2 Analyzing channel portfolio 110:25
4.3 Analyzing Channel Portfolio7:09
4.4 Comparing Channels5:22
Compare b search and g search channels by calculating total sessions, mobile sessions, and the mobile traffic percentage within the date range for the announcement utm campaign.
4.5 Multi Channel Bidding4:34
4.6 Bid Changes Impact10:48
analyze weekly session volumes by device for g search and bid changes, calculating b search as a percentage of g search across desktop and mobile.
4.7 Analyzing Direct - Brand Traffic13:40
Analyze direct and branded traffic versus organic visits using utm source and campaign data to gauge brand strength and revenue without paid campaigns.
4.8 Traffic Breakdown9:31

6.1 Introduction1:19
6.2 Analyzing Product sales7:26
6.3 Analyzing new product6:47
6.4 Analyzing website - Product level10:39
6.5 Analyzing website pathing18:41
Analyze the click-through rate and next-page behavior from the product page, comparing three months before and after launch. Break down sessions by product ids to reveal pre vs post-launch insights.
6.6 Analyzing product conversion funnel22:16
6.7 Analyzing Cross-selling products15:19
Analyze cross-selling patterns by examining orders and order items to identify primary and cross-sell products, then test and optimize strategies while measuring conversion rate, revenue impact, and customer purchase behavior.
6.8 Analyzing product expansion11:54
6.9 Analyzing product refund5:44

7.1 User Analysis Introduction0:37
Analyze user behavior, including repeat visits, time to repeat, and repeat channel behavior, to build conversion channels and improve conversion rates for returning customers.
7.2 Analyzing Repeat Behavior3:02
Analyze repeat behavior to identify valuable customers and track return visits by channel, using cookies, unique IDs, and date-based metrics to optimize marketing channels.
7.3 Identify Repeat Visitors11:00
Identify repeat visitors by analyzing new and repeated sessions from 2014 website sessions data, using user IDs and timestamps to guide marketing targeting.
7.4 Analyzing repeat behavior8:40
7.5 Analyzing repeat channels6:54
7.6 Conversion rates3:03
The lecture compares conversion rates and revenue per session for repeat versus new sessions using 2014 as baseline, showing 6% and 4.3 per session for new vs ~8% for repeats.

Requirements

Students are expected to have prior knowledge in SQL and solid grasp of SELECT statements, aggregate functions and join tables
If you do not have any experience or knowledge in SQL, I highly recommend to have the Data Analysis for Beginners with SQL or gain some knowledge in MySQL before participating in this course

Description

NOTE: This is an ADVANCED SQL course, built on topics and skills covered in the previous course or on prior knowledge in SQL queries.

Please have the minimum prerequisite skills or complete the Beginner SQL course to be able to complete the course.

In this course you will develop real-world analytics and Business Intelligence skills using Advanced SQL by Analyzing an ecommerce store to get insight for the performance of the website, products and user experience

GUARANTEED, instead of learning some advanced codes, in this course you will learn how to apply the advanced skills, analyzing the data and doing Business analysis too

We will walk through real project database of eCommerce store for toys from scratch

What you will learn:

Write Advanced SQL queries and analyze database.
Learn TEMPORARY TABLES and SUBQUERIES to handle complex multistep data problem.
Analyze data for eCommerce real-world case where you can solve tasks.
Mastering JOIN statements across multiple tables

You will use advanced skills such TEMPORARY TABLE, Subqueries to solve multistep data problem which make you THINK like an ANALYST

COURSE OUTLINES:

Introduction from downloading SQL server to connect the database.
In this section we will take about how to install MySQL workbench and connect the database to the server plus creating a connection, after watching this part make sure you have the relevant skills including SELECT statements, Aggregate Functions and tables joins.
Analyzing Traffic source
In this section we will warm up and do some review for some queries, then we will analyze where our traffic source coming from, how different sources perform in terms of traffic volume, calculate conversion rates, analyzing bids and optimize it, trend analysis
Analyzing website performance
In this section we will analyze website pageviews and use TEMPORAY TABLE, calculate bounce rate, analyzing landing page, and use SUBQUERIES
Analyzing Channel portfolio management
In this section we will compare channels and do multi channel bidding , also analyzing bid changes impact, traffic breakdown.
Analyzing Business Patterns and Seasonality
In this section we will dig deeper into traffic analysis and explore more about organic and paid campaign, also seasonality and business patterns analysis
Product Analysis
In this section we will break down sales on product level, conversion rates and cross-selling patterns and refund rates analysis to keep quality
User Analysis
In this section we deep analyze user behavior and analyze the repeated sessions to identify most valuable customers and explore which channels they are coming from

Who this course is for:

SQL users looking to develop advanced skills and master data analysis with Advanced SQL queries
Anyone looking to pursue a career in data analysis or business intelligence
Data analysts excel users looking to shift into business intelligence and optimize data analysis skills
Data Analyst or Business Analyst with SQL, BI professionals looking to analyze data in a real-world case database

Business Intelligence & Data analysis | Advanced MySQL

What you'll learn

Explore related topics

Course content

Introduction4 lectures • 17min

Analyzing Traffic Source7 lectures • 1hr 5min

Analyzing Website Performance6 lectures • 1hr 58min

Analyzing Portfolio & Management8 lectures • 1hr 3min

Analyzing Seasonality and Business Patterns2 lectures • 28min

Product Analysis9 lectures • 1hr 40min

User Analysis6 lectures • 33min

Requirements

Description

Who this course is for: