The Vision That Turned Into a Maze of Problems

My journey of building a Substack Dashboard for analytics

Photo by Kalea Jerielle on Unsplash

When I first considered creating an analytics dashboard for Substack writers, the idea was vague—a simple tool that could offer personalized insights.

But as I started digging in, that vision unraveled into complex interconnected problems, each demanding meticulous attention.

• What kind of community could I establish to discuss, validate, and meet their needs?

• How many users would sign up and want to get access? How can I scale the system accordingly?

• How could I provide insights to other Substackers offering reports and analytical insights?

• How would I collect the data required to provide these reports and insights? How would I keep the data fresh and valuable over time in an automated manner?

Every free weekend, every late-night hour stolen from sleep became another step toward solving these puzzles.

Wrestling with self-doubt

With each new challenge, a nagging question surfaced: Is this even worth it?

The effort, the time, the technical deep dives—would any of it pay off? Would writers even care about these analytics? These doubts hovered in the background, but my curiosity and drive to solve problems have kept pushing me forward.

I launched a Substack Growth and Analytics survey to determine how many Substackers were interested in my vision. The survey data confirmed that 75% of participants rated this as high or very high value.

Survey results

Last weekend, I built a demo dashboard, recorded a video showing how to use it, and created a waiting list with 20 open slots.

So far, 13 brave souls have signed up for the Substack Dashboard waiting list. Some even paid to reserve their spot for using the product, even though I offered the waiting list for free.

Thank you for your vote of confidence.

Obsession fuels discovery

Despite the uncertainty, my obsession with making this work led me to learn and implement new capabilities:

1. High-Performance Data Pipelines

Pulling meaningful data from Substack’s APIs at scale requires a robust system. The previous pipeline I created in November 2024 was slow; it took me 12 days to collect the original dataset.

I researched scalable, high-performance frameworks and turned to Scrapy to build a new data pipeline that efficiently extracts and processes data into a PostgreSQL database. In the last three days and nights, I have pulled and updated data from

• 87,292 users

• 111,724 publications

• 494,020 subscriptions

• 1.3 million notes

• 2.3 million posts

2. Expanding Discovery Beyond Expectations

Initially, I could only pull a limited number of Substack users and publications due to API limitations.

Public API response

However, through extensive research, I uncovered new APIs that unlocked six times more Substack users and eight times more publications.

Even better, I found new ways to map audience relationships and subscription patterns, opening up more profound analytical possibilities than I had initially imagined.

3. Automating the Data Pipeline with Monitoring & Logging

With more data came more complexity. I built an event-driven, automated pipeline to handle ingestion, with monitoring and logging to track job execution. Estimating job completion times and detecting anomalies became critical to keep the system reliable.

An event like a Gumroad purchase can trigger the data collection. This capability helps to ensure the data stays fresh and relevant for Substack users.

Dashboard to track running jobs

The pressure to deliver

As if the technical hurdles weren’t enough, an upcoming call with a fellow Substacker interested in analytics added a new layer of urgency.

I wanted to showcase all the new data, which meant rushing to operationalize the system before the meeting.

The pressure mounted, and I had to balance quality with speed, ensure the insights were valuable, and hope the effort would resonate with others.

The road ahead

This project has been a mix of obsession, doubt, and discovery. It’s pushed me to learn, adapt, and refine the vision and execution.

Whether this dashboard becomes the game-changer I hope for or simply another learning experience, one thing is sure—each step forward is another piece of the puzzle falling into place.

Thank you for supporting my work.