Advanced SQL + MySQL for Analytics & Business Intelligence

Advanced SQL + MySQL for Analytics & Business Intelligence
Advanced SQL + MySQL for Analytics & Business Intelligence
English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 8.5 Hours | 3.29 GB

Master Advanced Databases and SQL Querying. Gain Advanced MySQL Workbench Skills w/ Advanced SQL Queries & Data Analysis

IMPORTANT NOTE: This is an advanced SQL course, which builds on the skills covered in our introductory course: MySQL for Data Analysis – SQL Database for Beginners. Please review the prerequisite skills or complete the beginner SQL course before taking this one!

If you want to develop practical, real-world analytics & BI skills using advanced SQL, you’re in the right place.

This course is unlike anything you’ve seen before, guaranteed. Instead of using random sample data and obscure demos, we’ll be working with a rich, custom-built eCommerce database that we’ve designed from scratch to help you apply your SQL skills to real projects and learn how to truly THINK like an analyst.

You’ll be playing the role of a newly hired Database Analyst for Maven Fuzzy Factory, an eCommerce start-up, and will be working directly with the CEO, Marketing Director and Website Manager to help grow the business and analyze performance along the way. You’ll use a range of advanced SQL tools and techniques, and work through actual projects that data analysts and business intelligence professionals work on every day.

COURSE OUTLINE:

Prerequisite SQL Skills Assessment

In this section we’ll address the core SQL/MySQL skills that you should be comfortable with before starting the course, including SELECT statements, aggregate functions, and table joins. If you aren’t sure how your skills stack up, we’ve included a quiz to assess your baseline SQL knowledge.

MySQL Setup & Install

For students who haven’t completed our intro course, we’ll demonstrate exactly how to download and install the tools that we’ll need to access MySQL, including MySQL Community Server & MySQL Workbench. We’ll share download guides for both Mac & PC, and run our first piece of SQL code to generate the project database that we’ll analyze throughout the course.

Traffic Analysis & Optimization

In this section we’ll use MySQL to analyze where our website traffic is coming from, how different sources perform in terms of traffic volume and conversion rates, and how we can adjust bids to optimize our budgets.

Website Measurement & Testing

In this section we’ll dive into page-level website data to compare traffic and conversion rates, and use MySQL to build and analyze conversion funnels to help optimize the customer purchase experience.

MID-COURSE PROJECT: Preparing data for the executive board meeting

Channel Analysis & Optimization

In this section we’ll dig deeper into our traffic channel mix, explore paid vs. free traffic, break down performance by device type, and write advanced SQL queries to conduct some time-series analyses to understand trending and seasonality.

Product-Level Analysis

In this section we’ll use MySQL to break down product-level sales and conversion rates, analyze cross-selling patterns, and use refund rates to keep a pulse on quality.

User-Level Analysis

In this section we’ll take a closer look at user behavior and repeat sessions, and use MySQL techniques to identify our most valuable customers and explore which channels they are coming from.

FINAL PROJECT: Building a data-driven growth story for potential investors

What you’ll learn

  • Write advanced SQL queries to explore and analyze databases with MySQL Workbench
  • Learn how to use subqueries and temporary tables to handle complex multi-step data problems
  • Solve practical hands-on assignments and real-world eCommerce use cases
  • Analyze data across multiple tables with powerful MySQL JOIN statements
  • Master unique tips and tools that you won’t find anywhere else
  • Get LIFETIME access to resource files, quizzes, assignments, and expert support
Table of Contents

Getting Started
1 Course Structure Outline
2 READ ME Important Notes for New Students
3 DOWNLOAD Course Resources
4 Introducing the Course Project – Play the Role of Business Analyst
5 Setting Expectations – Focus on Business Analysis NOT MySQL Administration
6 Looking to become a MySQL Specialist

Prerequisite Skills
7 Introduction
8 Prerequisite Skills – Taught in my SQL Database for Beginners course

MySQL Intro Setup
9 Introduction
10 MySQL Installation Overview – MySQL Community Server MySQL Workbench
11 MAC Download Community Server
12 MAC Download MySQL Workbench
13 PC Download MySQL Community Server MySQL Workbench
14 Connecting MySQL Workbench to the Server
15 MySQL Workbench Interface
16 Prepare to Create the Database
17 Creating the Maven Fuzzy Factory Database
18 Getting to Know the Database
19 USE mavenfuzzyfactory

Analyzing Traffic Sources
20 Introduction
21 Analyzing Top Traffic Sources
22 ASSIGNMENT Finding Top Traffic Sources
23 SOLUTION Finding Top Traffic Sources
24 ASSIGNMENT Traffic Source Conversion Rates
25 SOLUTION Traffic Source Conversion Rates
26 Bid Optimization Trend Analysis
27 ASSIGNMENT Traffic Source Trending
28 SOLUTION Traffic Source Trending
29 ASSIGNMENT Bid Optimization for Paid Traffic
30 SOLUTION Bid Optimization for Paid Traffic
31 ASSIGNMENT Trending w Granular Segments
32 SOLUTION Trending w Granular Segments

Analyzing Website Performance
33 Introduction
34 Analyzing Top Website Pages Entry Pages
35 ASSIGNMENT Finding Top Website Pages
36 SOLUTION Finding Top Website Pages
37 ASSIGNMENT Finding Top Entry Pages
38 SOLUTION Finding Top Entry Pages
39 Analyzing Bounce Rates Landing Page Tests
40 ASSIGNMENT Calculating Bounce Rates
41 SOLUTION Calculating Bounce Rates
42 ASSIGNMENT Analyzing Landing Page Tests
43 SOLUTION Analyzing Landing Page Tests
44 ASSIGNMENT Landing Page Trend Analysis
45 SOLUTION Landing Page Trend Analysis
46 Building Conversion Funnels Testing Conversion Paths
47 ASSIGNMENT Building Conversion Funnels
48 SOLUTION Building Conversion Funnels
49 ASSIGNMENT Analyzing Conversion Funnel Tests
50 SOLUTION Analyzing Conversion Funnel Tests

MID-COURSE PROJECT
51 Introduction
52 ASSIGNMENT Mid-Course Project
53 SOLUTION Mid-Course Project

Analysis for Channel Portfolio Management
54 Introduction
55 Analyzing Channel Portfolios
56 ASSIGNMENT Analyzing Channel Portfolios
57 SOLUTION Analyzing Channel Portfolios
58 ASSIGNMENT Comparing Channel Characteristics
59 SOLUTION Comparing Channel Characteristics
60 ASSIGNMENT Cross-Channel Bid Optimization
61 SOLUTION Cross-Channel Bid Optimization
62 ASSIGNMENT Analyzing Channel Portfolio Trends
63 SOLUTION Analyzing Channel Portfolio Trends
64 Analyzing Direct Brand-Driven Traffic
65 ASSIGNMENT Analyzing Direct Traffic
66 SOLUTION Analyzing Direct Traffic

Analyzing Business Patterns and Seasonality
67 Analyzing Business Patterns Seasonality
68 ASSIGNMENT Analyzing Seasonality
69 SOLUTION Analyzing Seasonality
70 ASSIGNMENT Analyzing Business Patterns
71 SOLUTION Analyzing Business Patterns

Product Analysis
72 Introduction
73 Analyzing Product Sales Product Launches
74 ASSIGNMENT Product-Level Sales Analysis
75 SOLUTION Product-Level Sales Analysis
76 ASSIGNMENT Analyzing Product Launches
77 SOLUTION Analyzing Product Launches
78 Analyzing Product-Level Website Pathing
79 ASSIGNMENT Product-Level Website Pathing
80 SOLUTION Product-Level Website Pathing
81 ASSIGNMENT Building Product-Level Conversion Funnels
82 SOLUTION Building Product-Level Conversion Funnels
83 Cross-Selling Product Portfolio Analysis
84 ASSIGNMENT Cross-Sell Analysis
85 SOLUTION Cross-Sell Analysis
86 ASSIGNMENT Product Portfolio Expansion
87 SOLUTION Product Portfolio Expansion
88 Analyzing Product Refund Rates
89 ASSIGNMENT Analyzing Product Refund Rates
90 SOLUTION Analyzing Product Refund Rates

User Analysis
91 Introduction
92 Analyzing Repeat Visit Purchase Behavior
93 ASSIGNMENT Identifying Repeat Visitors
94 SOLUTION Identifying Repeat Visitors
95 ASSIGNMENT Analyzing Time to Repeat
96 SOLUTION Analyzing Time to Repeat
97 ASSIGNMENT Analyzing Repeat Channel Behavior
98 SOLUTION Analyzing Repeat Channel Behavior
99 ASSIGNMENT Analyzing New Repeat Conversion Rates
100 SOLUTION Analyzing New Repeat Conversion Rates

FINAL PROJECT
101 Introduction
102 ASSIGNMENT Final Project
103 SOLUTION Final Project

Wrapping Up
104 Resources Next Steps
105 More from Maven Analytics

SNEAK PEEK MySQL Database Administration – SQL Database for Beginners
106 SNEAK PEEK Creating a Table Using a SQL Query
107 SNEAK PEEK Creating Enhanced Entity Relationship Models