When dashboards no longer answered our questions, we extracted the data and built our own system to analyze our data and to monitor our performance. Here’s how you can do it too.
Wow...this article is a fantastic "missing manual" for StackContacts product.
Thank you for sharing the details on how you extract the insights from your Substack publication data.
There are so many great tips and concrete examples of how to explore the data, build the missing data dictionary, create queries to track the metrics you care about.
I am using DBeaver as well, but often use just the command line duckdb interface to make quick SQL queries to verify some details. I'm using Google Sheets and there is a DuckDB community extension https://duckdb.org/2025/02/26/google-sheets-community-extension that allow to automate like you've shown here with Excel and ODBC interface.
Claude AI is pretty smart discovering the database structure and the fields, but it makes mistakes so providing better context helps a lot. I've used Cursor with .claude/commands folder where I store prompts that have exact SQL queries and instructions for more complex analysis - such as attribution for Gumroad sales (which content led to purchase). I'm developing these prompts iteratively (just like in your examples), troubleshooting outputs and cross-checking with other sources when the results don't look correct.
Thanks for the shoutout, Finn, and thanks for building StackContacts! We'd been trying to access our data beyond the Substack dashboards + exports, but with little luck. Your product saved us a lot of work trying to build those API calls.
Thanks for the tip on the Google Sheets connector - we'll check it out.
We've not tried out Cursor yet (thanks for sharing what you are doing there) - exploring new tools and figuring out how they can help us and our clients has been a fun side quest in this work.
Our next step is adding our data from both LinkedIn and Google Analytics, so we can compare what resonates with different audiences. If you have any ideas or have heard of someone else doing that, please let us know! :-)
Wow...this article is a fantastic "missing manual" for StackContacts product.
Thank you for sharing the details on how you extract the insights from your Substack publication data.
There are so many great tips and concrete examples of how to explore the data, build the missing data dictionary, create queries to track the metrics you care about.
I am using DBeaver as well, but often use just the command line duckdb interface to make quick SQL queries to verify some details. I'm using Google Sheets and there is a DuckDB community extension https://duckdb.org/2025/02/26/google-sheets-community-extension that allow to automate like you've shown here with Excel and ODBC interface.
Claude AI is pretty smart discovering the database structure and the fields, but it makes mistakes so providing better context helps a lot. I've used Cursor with .claude/commands folder where I store prompts that have exact SQL queries and instructions for more complex analysis - such as attribution for Gumroad sales (which content led to purchase). I'm developing these prompts iteratively (just like in your examples), troubleshooting outputs and cross-checking with other sources when the results don't look correct.
Great article!!!
Thanks for the shoutout, Finn, and thanks for building StackContacts! We'd been trying to access our data beyond the Substack dashboards + exports, but with little luck. Your product saved us a lot of work trying to build those API calls.
Thanks for the tip on the Google Sheets connector - we'll check it out.
We've not tried out Cursor yet (thanks for sharing what you are doing there) - exploring new tools and figuring out how they can help us and our clients has been a fun side quest in this work.
Our next step is adding our data from both LinkedIn and Google Analytics, so we can compare what resonates with different audiences. If you have any ideas or have heard of someone else doing that, please let us know! :-)