Creating Leaderboard and Deck Dashboards from MTGO Vintage Results Data

Problem

Vintage players on Magic: the Gathering Online (MTGO) collect and collate match results and deck selection data for each player following online tournaments. We want to clean and format the dataset so it can stored in a database. Additionally, we would like to use the dataset to create publicly visible tools for players such as dashboards and leaderboards.

Data Collection and Data Cleansing

Jupyter Notebook – Python

ETL Process

Pull latest dataset from the Vintage community’s Google Sheet.
Subset data if we are not running a full table update.
Clean and format dataset:
- Replace blank cells with 0 or NULL.
- Format column data types.
- Propagate Event Type column data into each Results record.
- Format Event Date.
- Create unique Event ID column.
- Split dataset into two tables (Events and Results).
- Add columns for Entries and Day of Week to Events table. Drop Details column.
- Rename Rank column to Finishes because ‘rank’ is a reserved keyword.
- Drop duplicate or superfluous columns.
Generate SQL Insert and Update command strings.
Connect to MS Azure SQL Database using pyodbc package and run generated SQL commands.

**Cleaned and formatted Results and Events tables.**

Creating a Solution

Player Leaderboard – Tableau Public

Vintage Deck Stats – Tableau Public