top of page
Data Analysis

Masterclass: Game Analysis With SQL

This course combines the Game Analysis With SQL and Advanced Game Analysis With SQL courses for a complete course on game data analysis.

8 Weeks
32 hrs Of Instruction

 

Next Course:     
August 2024

What You'll Learn

Game Analytics Database Setup: How to Install a local MySQL database server, setup tables and load course data. How to install an SQL client tool (DBEAVER) for querying and accessing the data. How to validate your database installation.      


Jupyter Notebooks: How to install and use Jupyter, a web-based notebook for analytics. How to use Jupyter to analyze data using SQL and Python. How to use Python libraries for game analysis. Common Python libraries for statistics, visualization, machine learning and more. 


Data Collection, Storage & Access: How is game event data captured and stored. Which cloud and storage technology is best suited to which type of game analysis.  Example data capture service and data load utility in action for a popular word game. 

 

SELECTing Data:  How to create tables with the CREATE TABLEs statement. How the SELECT statement works. How to use various result set filtering and limiting functions. How to use the database dictionary to understand database and table structures. 


Functions & Operators:  SQL math and string functions. Comparative and logical operators. The fundamental functions and operators used for complex data analysis in gaming. 


Dates & Times: Data types and their differences. Conversion from one data type to another? How to manipulate time and dates with INTERVALs. Time difference functions, date addition and date subtraction. Common date and time conversion examples used in game analysis. 


The CASE Statement: How to use the CASE statement to transform and re-classify game data. How to PIVOT and rotate data for analysis and reporting. Several examples of CASE statement usage, common in mobile game analysis. 


Joining & Aggregating Data: Why is joining and aggregating data important. The GROUP BY statement. How to combine data from multiple tables efficiently. Use of different SQL join types.


Common Game Metrics:  TOP 10 gaming metrics by category. The SQL coding required to generate each metric from raw data.   


Window Functions: How window functions can simplify complex game data analysis. Window function FRAMEs, PARTITIONs and ORDERing. RANKing and percentile functions. Rolling averages, cumulative sums and improving game analysis efficiency and insight.


Advanced Game Analysis: Using SQL to analyze game economies. Understanding game network health. Using SQL to monitor game inflows and outflows. Advanced monetization analysis and pricing. How to determine economy balance. How to use SQL for cohort analysis and LTV prediction. 


Predictive Models & Dashboards: SQL techniques for preparing data for dashboards, reports and predictive models. Examples of churn and payer prediction using SQL. Combining SQL extraction, data and Python libraries to forecast player behavior. 


Common Table Expressions (CTEs): How to use CTEs. The purpose of CTEs and code maintainability. Reducing queries complexity and improving analysis throughout. Recursive queries and when to use CTEs, sub-queries and temporary tables.


Query Optimization: The best way to run queries against large datasets. The impact of indexes. Which index type is best suited to which data. The EXPLAIN function and  query performance. How to sample with SQL and increase query throughput. 


Player Segmentation: How we can use SQL to discover player segments. Advanced analysis techniques using SQL and Python for K-Means clustering and decision trees.  Using SQL and Python for visualization and model development. 


Capstone Project: Capstone project requirements. Capstone project template and recommended approach. How to utilize assignments and course work for your capstone project.

Success strategies

Masterclass: Game Analysis With SQL

This course combines the Game Analysis With SQL and Advanced Game Analysis With SQL courses for a complete course on game data analysis.

8 Weeks
32 hrs Of Instruction

Next Course:

August 2024

This Course Includes

  • Office Hours With Instructor, Schedulable Instructor 1-On-1s

  • 32 Hours of Instruction Delivered Over 16 Lessons

  • Downloadable Lesson Slides, Downloadable SQL and Python code

  • Lesson Assignments, Lesson Quizzes, In-Class Exercises

  • Final Course Quiz, Capstone Project For Analytics Portfolio

  • Certificate Of Completion, Digital Certificate Credentials

Course Content

LESSON 01: Data Collection Storage & Access


  • Course Overview & Structure

  • How Is Game Data Collected & Accessed

  • Databases & Tables

  • MySQL Database Setup

  • DBeaver SQL Client Setup

  • Loading Data

  • Database Validation

  • Lesson Quiz & Assignment

LESSON 09: Window Functions - Part I

  • Introduction To Window Functions

  • Partitions, Frames, Bounds & Orders

  • SUM, MIN, MAX, AVG, COUNT

  • ROW_NUMBER & RANK Functions

  • Cumulative Totals & Percentiles

  • Game Analytics Examples

  • Lesson Assignment & Quiz

LESSON 02: SELECTing Data


  • CREATE Table Statement

  • The Select Statement

  • Filtering Data Part I

  • Filtering Data Part II

  • The Database Dictionary

  • Lesson Quiz & Assignment

LESSON 10: Window Functions - Part II

  • MEDIAN, MODE & NTILES Functions

  • FIRST_VALUE & LAST_VALUE Functions 

  • LEAD & LAG Functions

  • Rolling Averages

  • Window Aliases

  • Examples In Gaming

  • Lesson Assignment & Quiz

LESSON 03: Functions & Operators


  • Logical Operators

  • Comparative Operators

  • Math Functions

  • String Functions

  • In-Class Exercise

  • Lesson Quiz & Assignment


LESSON 11: Sub-Queries & Temporary Tables

  • Sub-Queries Introduction

  • Temporary Tables Introduction

  • Self-Join, INTERSECT & IN

  • Performance Considerations

  • Examples In Gaming

  • Lesson Assignment & Quiz

LESSON 04: Dates & Times


  • Not My Types

  • Dates & Times Overview

  • Manipulating Dates

  • Date & Time Conversion

  • Examples In Gaming

  • In-Class Exercise

  • Lesson Quiz & Assignment


LESSON 12: Common Table Expressions

  • CTE Introduction

  • Combining Multiple Tables

  • Recursive Queries

  • Examples In Gaming

  • SubQueries vs Temporary vs CTE

  • Lesson Assignment & Quiz

LESSON 05: The CASE Statement


  • CASE Statement Overview

  • Classification

  • PIVOTIng Data

  • Examples In Gaming

  • In-Class Exercise

  • Lesson Quiz & Assignment

LESSON 13: Query Efficiency

  • Indexes & Optimization

  • The Query Execution Plan

  • The EXPLAIN Function

  • Estimating Query Performance

  • Efficient Querying Using Sampling

  • Query Performance Best Practices

  • Lesson Quiz & Assignment

LESSON 06: Joining & Aggregating Data


  • Why Join & Aggregate Data?

  • GROUP BY Statement

  • Efficient Aggregation

  • Joining Tables

  • Examples In Gaming

  • In-Class Exercise

  • Lesson Quiz & Assignment


LESSON 14: Jupyter Notebook & Python Integration

  • Installing Jupyter Notebooks

  • Access To MySQL Database

  • Python Libraries For Data Analysis

  • SQL Workshop: Gaming Analysis With Python

  • Lesson Assignment & Quiz

LESSON 07: Common Game Metrics - Part I


  • Overview Of Top 10 Metrics

  • Acquisition

  • Engagement 

  • Capstone Project - Part I

  • In-Class Exercise

  • Lesson Quiz & Assignment

LESSON 15: Advanced Analysis Workshop - Part I

  • Combining Metrics For Dashboards

  • Preparing Data For Model Development

  • SQL Workshop: Economy Analysis

  • SQL Workshop: Segmenting Players By Engagement & Monetization

  • Capstone Project - Part I

  • Lesson Assignment & Quiz

LESSON 08: Common Game Metrics - Part II


  • Retention

  • Monetization

  • Capstone Project - Part II

  • In-Class Exercise

  • Lesson Quiz & Assignment

LESSON 16: Advanced Analysis Workshop - Part II

  • SQL Workshop: Churn Model Development

  • SQL Workshop: Payer Prediction Model 

  • Capstone Project - Part II

  • Final Course Quiz


Requirements

  • This is a complete SQL course, from fundamentals to advanced analysis techniques.

  • Learners are not required to have previous experience in relational databases, coding, or SQL. 

  • Recommended: candidates taking this course would benefit from 1-2 years of analysis experience as either a business analyst, product manager or similar role.

  • Learners will need to be able to install MySQL and DBEaver software and associated drivers on a Windows or Mac computer to be able to access the course data.

Description

This course provides a comprehensive journey into SQL with a specialized focus on gaming analytics across mobile, PC, and console platforms. It seamlessly integrates beginner-friendly SQL concepts with advanced game analytics techniques.


Featuring all content from the Game Analysis With SQL and Advanced Game Analysis With SQL courses, this course offers a bundled discount to master SQL. 


For those unfamiliar with SQL, the course covers fundamental SQL operations such as table creation, data selection, functions, operators, and data aggregation through summarization and table joins. With approximately 2GB of simulated and real game data, learners delve into creating gaming metrics from raw data using SQL.


From the fundamentals of SQL, this course focuses on advanced SQL workshops using windowing functions, temporary tables and common table expressions, allowing more complex analysis of retention, in-game economies, monetization and player segmentation. Utilizing a MySQL database and Jupyter Notebooks, learners gain hands-on experience with all the SQL needed for game analysis.


Beyond traditional analytics, learners explore predictive modeling for metrics like Lifetime Value (LTV), player churn, and payer propensity. They also learn segmentation techniques based on user engagement and spending behavior, along with insights into A/B testing and feature lift analysis for optimizing player experiences and predicting key metrics like Daily Active Users (DAU) and revenue.


This course features several assignments and quizzes to ensure that learners develop a full understanding of the course materials, are able to complete their capstone project and are eligible for a course completion certificate with distinction.


bottom of page