Wordle Score Tracker: A Data Wrangling and Analysis Project

Project Overview

Introduction

This project automatically collects and analyzes daily Wordle scores from three players. The primary goal is to create a consolidated, running tally of scores throughout each month, and determine an overall winner at the end of the month to provide a fun and competitive way to track performance. The entire data pipeline, from submission to spreadsheet calculation, is automated, showcasing a practical application of data wrangling and process automation.

View the Live Project

The Problem

Our friend group enjoys the lively competition of sharing our daily Wordle scores. However, manually tracking these scores in a group chat quickly became cumbersome and prone to errors. We needed a centralized and automated way to:

  • Collect scores from each player easily and consistently.

  • Calculate a daily winner and a running monthly total.

  • Present the live data in an easy-to-understand format.

The Solution

I developed an automated workflow that uses Zapier to parse incoming emails containing Wordle scores. These scores are then sent to a Google Sheet, which is built to automatically clean the data, calculate daily and monthly scores, and display the results in a leaderboard style interface.

Tools and Technologies

  • Data Collection: Email

  • Automation: Zapier

  • Data Storage & Analysis: Google Sheets

  • Website/Display: Google Sheets

Project Implementation

1. Data Collection

The process begins with each of the three players submitting their daily Wordle score via email through the NYT app. Zapier extracts the subject line from the NYT app's email share feature and creates a new entry in Google Sheets under the "Email Parser Data" sheet. This sheet extracts the player, puzzle ID, and score. It then populates the master score table with this structured data for further analysis and refinement.

2. Automation with Zapier

A "Zap" was created in Zapier to automate the data pipeline. Here’s how it works:

  • Trigger: The Zap is triggered when a new email arrives in the Zapier mailbox through Email Parser by Zapier.

  • Action: Zapier's email parser then extracts the subject line of the email.

  • Final Action: The extracted subject line, along with the sender's name and date, is then sent as a new row to a designated Google Sheet.

3. Data Wrangling and Spreadsheet Logic

The Google Sheet is the core of the project's data analysis. It's organized into several tabs:

  • Email Parser Data: This sheet receives the raw data directly from Zapier (Sender, Email Address, Date, Email Content).

  • Data Cleaning & Transformation: The sheet then extracts key data points to be used for further manipulation (Player, Puzzle ID, Result). For example, it extracts just the puzzle ID (e.g., "1293" from "Wordle 1,293 3/6") and converts it into a numerical format. This is also where any error handling for incorrect submissions would occur.

  • Master Score Table: This sheet does the heavy lifting. It contains the formulas to further clean and wrangle the data to calculate the daily winner and the running monthly totals. The puzzle ID and result is matched to a date (weekends/holidays are not included), and the rest of the calculations are performed. This is also where missed days are accounted for. The scoring system is as follows:

    • 1st Place (Lowest Score): 2 points

    • 2nd Place: 1 point

    • 3rd Place: 0 points

    • Puzzle Failed (X/6): -1 point

    • Missed Day: The player’s average score for the month + 1 point

    • Dashboard/Summary: The final sheet presents the data in a clean, easy-to-read format. It includes:

      • A results board with the current monthly standings.

      • A daily log of scores.

      • A scoreboard to track running total points and guesses.

Challenges and Future Improvements

Challenges

  • Inconsistent Email Parser Extraction: Initially, the full functionality of Zapier’s Email Parser was utilized, but yielded inconsistent results. The incoming data from NYT was riddled with commas, dashes, and special characters beyond the scope of the parser. The workaround was to import only the subject line of the email, and parse it using regex formulas within the spreadsheet.

  • Calculating Monthly Averages for Missed Days: Each column spans multiple months and years, so it was a challenge to calculate the average score plus one within a given month. After exploring approaches using indexing and filter functions, a simpler approach using averageif combined with a key generation technique using numerical year and month proved superior.

Future Improvements

  • Web-Based Submission Form: To further standardize submissions, a simple web form could be created to replace the email submission.

  • Enhanced Dashboard: The data could be piped to a more robust data visualization tool like Looker Studio or Tableau Public for more advanced analytics and interactive charts.

  • Player Statistics: Additional metrics could be calculated, such as average score, win percentage, and streaks.

  • Additional Players: The Wordle Score Tracker is designed for 3 players only, but could be scaled up to accommodate more players.

Conclusion

This project successfully automated a manual and error-prone process, creating a fun and engaging way for our group to track Wordle scores. It served as a practical exercise in data pipeline creation, automation, and data wrangling. By leveraging simple, no-code tools like Zapier and the power of spreadsheet functions, a robust and scalable solution was developed.

Feel free to explore the read-only Google Sheet to see the data and formulas in action!