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
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.
Creating a Solution
Player Leaderboard – Tableau Public
Vintage Deck Stats – Tableau Public
