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.
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!!!