TL;DR: Substack notifications burn creators out when treated as endless pings to react to, but they become useful when treated as an event stream with automated rules. Classify notification events by your goal (conversation, signal, intent, risk), run a simple workflow (Event →Triage→Action), and process events in short windows instead of constant checking. With guardrails like dry run, caps, cooldowns, and preview-before-send, you can turn the right notifications into relationships and revenue without becoming a spammer or burning out.

42 notifications feels exciting. 420 notifications feels like drowning.
Same bell icon. Two different outcomes.

If you feel overloaded by Substack notifications, you’re not broken.
Your tools are.

If you run a publication, you know this feeling: you want to respond, welcome, and follow up.

But doing it one-by-one in chat does not scale. And spray-and-pray automation is exactly what your readers did not sign up for.

So you get stuck between: “I should reach out” and “I don’t have a clean way to do it.”

Most creators are taught to react to notifications.

Click. Scroll. Reply when you can. Forget what mattered.

That is not a workflow. That’s a stress loop.

The reframe is simple:
Notifications are not a to-do list. They are an event stream.
And event streams need rules.

Why the bell 🔔 hijacks you

Platforms are built to pull you back in. That part is not a conspiracy. It’s the business model.

Notifications and visible counts trigger variable rewards, social validation, and compulsive checking.

Useful for platform engagement.
Terrible for creator focus when unmanaged.

You don’t burn out from writing.
You burn out from fragmented attention and constant micro-decisions.

Reframe: not all notifications are equal

Your bell mixes very different signals. Treating all of them as urgent is the fastest route to exhaustion.

I use four event buckets:

• Conversation events: comments, replies, DMs

• Signal events: likes, restacks, follows

• Intent events: subscribe, paid conversion, high-value clicks

• Risk events: negative replies, churn hints, missed response windows

Now each event has a job.

Conversation builds relationships.
Signal expands reach.
Intent drives revenue.
Risk protects retention.

The magic in between is rules

In my notes I wrote last week:

Notification Event -> Magic -> Take some action

Here is what “magic” actually means:

• If high-intent event (reply + recent subscribe), send personal follow-up.

• If low-intent/high-volume event (likes only), batch-review once daily.

• If repeated engagement from the same person, move to contact workflow.

• If noise, mute that notification type.

That’s it.

No hustle theater. Just pre-decided rules that reduce decision fatigue.

What I implemented in StackContacts Connect

I didn’t want another motivational framework. I wanted infrastructure.

So I built this into StackContacts Connect as event-based auto-DM (available from version 0.3.1 onwards).

In the Responses tab, one event type maps to one response.
You pick plain-language categories and events.

It keeps creators in operator mode instead of forcing them to think in backend event names.

Then you can add optional rules:

• age in minutes (how recent is this event?)

• sender count (Substack sends likes from multiple people in one event)

• actor handle (Substack @ username from the profile)

• can-DM flag (some users have disabled DMs)

• specific item key (Note ID or Post ID - see example below)

Before anything runs, you can preview behavior.

Preview-before-save sounds small. It matters. It turns “I hope this works” into “I can verify this first.”

Here is an preview example from my other Substack account:

Global safety controls sit in Settings:

• dry run

• live-send opt-in

• poll interval (how often activity feed is checked?)

• feed fetch limit (max 12 events per poll)

• max DMs per poll (prevent “bot” like behavior)

• engagement cooldown (recommend 14 days between DMs to prevent spam like behavior)

I also added dedupe, cooldown logic, and an activity log. So you can see what happened and audit it.

Activity log tab provides clickable links to the DM chat thread so that you can continue to DM conversation with one click.

Starter playbook (do this this week)

If you want less burnout and more useful conversations, run this for 7 days:

1. Audit your notification categories and turn off low-value alerts. (You can do this in https://yourpublication.substack.com/publish/settings#notifications)

2. Define three tiers: Immediate, Same Day, Batch.

3. Attach one action to each tier (reply, DM, tag, ignore).

4. Process notifications in two short windows per day.

5. Track one business metric weekly (conversations started or subscribers converted).

6. Pilot one automation in dry run for 7 days before live sends.

This is the difference between feeling busy and running a system.
And yes, this still maps to outcomes.

In my earlier DM tests, I saw up to 53% DM response rate in the first four hours, while post-comment response rate sat around 2.8%.

Same creator. Same audience. Different channel design.

Guardrails that keep this humane

Automation is where creators get nervous.

Good. You should be careful.

The right automation respects people and protects your reputation:

• Live sending is explicit opt-in.

• Dry run is available for testing.

• “Only empty thread” avoids awkward repeat intros.

• Max DMs per poll and cooldowns prevent spammy behavior.

• Manual sends and background responses stay separate.

This is not “DM everyone.”
It’s “respond consistently to meaningful events.”

Good software should make the right behavior easier:

• message your own subscribers or engaged readers

• send with pacing and caps

• keep a human in the loop

• avoid unattended blast behavior

Chart: Daily Notification Ops Loop — Morning triage, Afternoon batch, Weekly review

The real goal

The goal is not to clear every notification.

Notifications as dopamine creates creator exhaustion.
Notifications as infrastructure creates creator leverage.

The goal is to convert the right signals into relationships and revenue without burning yourself out.

Try the 3-tier system for one week.

If you want, reply and I’ll share the exact rules I use in my Responses.

Leave a comment

— Finn

Further Reading

And this one:

Finn'sights

Your Best Subscribers Aren't in Your Inbox—They're in Chat

Nearly 2 in 5 new subscribers respond to my welcome DM. Industry benchmarks for outreach are 5–15%—so that number isn’t a fluke, and you can use it…

Read more

3 months ago · 34 likes · 11 comments · Finn Tropy

I was spending $60/month on AI tools for a newsletter with 47 subscribers, mostly to draft Notes. The tools worked; the monthly line item didn’t - at that scale, it never felt in proportion to where I was.

I kept renewing in part because cancelling the AI tool felt like downgrading the project, not because the line item made sense for a list that small.

Here’s the reality: if you’re on a tight budget—student loans, fixed income, retirement cash flow—you still deserve tools that work for you. You don’t need a bigger budget to start. You need a setup that doesn’t punish you for being early.

A local AI stack can give you a private place to think, draft, and iterate without another monthly bill.

It’s Not You - It’s AI Economics

The Big AI business model is built to keep selling bigger subscriptions. Massive infrastructure investments has to be justified with ever growing recurring revenue.

But early-stage creative work is different. You can run capable open-source models locally. For most newsletter-building work, your ideas, taste, and prompt discipline usually matter more than whether you’re on the latest frontier model.

Last March I moved the work to local models for a few weeks to see if they were actually good enough for the way I write—not just in a one-off test.

The barrier isn’t access to AI. It’s the permission to build before you’re profitable—and the willingness to separate signal (your thinking) from distribution (who gets paid every month).

The Cost Bleed (And How I Stopped It)

Recurring AI tool bills behave like rent.
Early creator revenue behaves like weather.

Even when revenue is “fine,” a fixed $60/month expense line item can still feel irrationally high at small scale.

Here’s what my numbers looked like:

I switched using Cursor Pro+ AI tools to a zero-cost local AI combo. The Notes drafts from the local model felt more aligned with my voice than the generic output I was getting from paid models.

Setup took about 2 hours the first time. After that, it was frictionless.

My Gumroad revenue dipped slightly in Month 2 ($286 → $243 for the new AI Tools).
But cashflow improved because the recurring AI bill went to $0:

$286 − $60 = $226 “after tools” in Month 1
$243 − $0 = $243 in Month 2

while I shipped 54% more Notes (66 → 102) and while my subscribers grew from 75 to 89.

That’s the point: fewer dollars leaking out every month, even when top-line revenue wobbles.

The Zero Cost Tech Stack: Pi.dev + Ollama + Qwen3.5:27b

Let’s get practical. This is the exact setup I’m running.

System Requirements

What you need for this article’s default commands (qwen3.5:27b model)

- Operating System: Ollama and Pi support macOS, Windows, and Linux. I run the walkthrough on macOS (Apple Silicon); on Windows or Linux, the flow is the same, with different install/terminal details.

- RAM: For 27B model, plan on ~32GB system RAM (model weight plus headroom for the OS and apps). On 16GB or less, use a smaller model tag instead of 27B (e.g. `qwen3.5:8b` or `qwen3.5:14b`)—same $0 local stack, less headroom, still yours. The tradeoff is spelled out in step 2.

- Storage: ~30GB free for the 27B-class model, cache, and a little overhead (smaller tags need less).

- Internet: Only for installs and the first model pull; after that, it runs locally.

The Installation

1) Install Ollama

macOS

• Download Ollama for macOS and install it.

• Note: Ollama requires macOS 14 Sonoma or later.

Windows (PC)

• Download Ollama for Windows and run the installer.

After install, confirm Ollama is available using command line:

ollama --version

2) Download and run Qwen 3.5

Pull and run the selected Qwen 3.5 model:

ollama run qwen3.5:27b

Typical Apple Silicon memory use (from Ollama model sizes):

• qwen3.5 / qwen3.5:35b: model size is about 24GB.
  Plan for at least 48 GB unified memory for a smoother experience.

• qwen3.5:27b: model size is about 17GB.
  Works better with at least 32 GB unified memory.

Rule of thumb: keep extra headroom for macOS + other apps, not just model size.
If you have 16 GB RAM or less, try qwen3.5:8b or qwen3.5:14b instead. They’re faster and use less memory. You’ll trade some capability for accessibility.

Still zero cost. Still local. Still yours.

3) Install Pi coding agent

You need to have Node.js installed - you can download it from here.
Open Terminal (macOS) or PowerShell (Windows), then run:

npm install -g @mariozechner/pi-coding-agent

More details can be found from Pi.dev website.

4) Pi config directory: ~/.pi/agent/

Pi stores global configuration in ~/.pi/agent/.
Think of this as your personal control center for models, settings, skills, and sessions.

Key files you will use most

• ~/.pi/agent/models.json: custom providers and model definitions (including local Ollama models).

• ~/.pi/agent/settings.json: global behavior (theme, defaults, UX settings).

• ~/.pi/agent/skills/: reusable skill folders (<skill-name>/SKILL.md).

• ~/.pi/agent/prompts/: reusable prompt templates.

• ~/.pi/agent/sessions/: saved chat/session history.

models.json: This is the file that wires Pi to Ollama (provider, local API URL, which model tags show up in Pi). I spent an hour getting the shape of it right, so the copy-paste example and field notes live in the Appendix.

5) Use Pi with your preferred provider/model

Launch Pi on the command line and then use /model to pick the model.

7) Add the Skill File

The skill file is what turns a generic Pi session into a Substack Notes workflow: a markdown file under ~/.pi/agent/skills/<skill-name>/ (usually SKILL.md) that encodes your format, audience, and rules.

I drafted a first version in a few minutes, then revised it on real notes until the output matched how I think and post. The full `create-notes` example is in the Appendix at the end of this piece—copy the folder, rename it, and make it yours.

Time check: a rough skill is quick; a reliable one, plus the rest of this stack, is what that ~2 hours first-time setup in system requirements is about—you’ll keep tuning the skill after you see a few real drafts.

Your content stays on your computer until you publish. No vendor sees your drafts. No API credits burning while you iterate. No context limit arguments. Just you and your ideas.

I created this article using the same AI stack. I used three different skills: create-outline, create-draft and create-final-article and another 45 minutes of me editing.

Why This Works (It’s Not the Model)

I used to treat the work like a shopping problem: a higher plan, another “pro” login, a slightly nicer default model. I was buying access and features; what I needed was a system - a boring, repeatable local loop (draft, tighten, ship) that didn’t turn “can I work today?” into a billing question.

The real unlock here isn’t the model. It’s removing permission barriers.

• Zero subscriptions = no auto-renewal anxiety

• Zero token costs = no unpredictable spend worries

• Zero login fatigue = focus on writing, not managing accounts

• Private drafts = your ideas stay yours until you’re ready to publish

The caveat: local models have their own limits.

Mostly RAM size and your time.

But those are your limits. Not a vendor trying to upsell you to Pro Plus MAX MODE for $200/month.

The Permission Barrier (This Is the Real Thing)

The barrier wasn’t access. It was whether my work “counted” if I wasn’t on a name-brand paid stack. You can build in public before the numbers look good, test setups before the next subscription, run capable models locally for $0, and keep drafts on your machine until you publish.

“Real” here isn’t a fatter line item on the stack—it’s ideas that stay yours until you share them, and a tool bill you can look at next to your subscriber count without wincing.

I started from a small email list - 47 subscribers, paying $60 for AI tools. After switching to local AI: $0/month in AI spend, 102 Notes shipped (vs 66 before). Drafting stayed local and private until I hit publish.

That’s when everything unlocked. Not because the model was better. Because the permission barrier was gone.

What To Do Next

You don’t need expensive AI subscriptions to ship consistently. You need a workflow that removes barriers between your ideas and your audience—especially the recurring “AI tool tax” while you’re still early.

Stop paying a monthly AI stack tax you can’t feel good about.

Here’s what to do today:

1. Install Pi agent + ollama + qwen3.5:27b (or a free model that fits your computer memory)

2. Add a skill file (template is in the appendix)

3. Draft your next Note on your own machine

4. Publish when you’re ready

Stop watching those AI tokens burn.

Got a question about running AI locally on a budget?
What’s been your biggest blocker so far?

Final thought: You don’t need a bigger budget to build. You need a setup that lets you build without watching your budget bleed. That’s what this is for.

Now go ship something.

— Finn

Appendix:

Skill File Template

For the Substack Notes skill file I mentioned, it is installed in ~/.pi/agent/skills/create-notes/SKILL.md.
Copy it, tweak it, make it yours - it is here

Full models.json (copy-paste)

Path ~/.pi/agent/models.json
Your file can register multiple Ollama models; here is a working example with two (the same one I use):

{
  "providers": {
    "ollama": {
      "api": "openai-completions",
      "apiKey": "ollama",
      "baseUrl": "http://127.0.0.1:11434/v1",
      "models": [
        {
          "_launch": true,
          "contextWindow": 65536,
          "id": "qwen3.5:27b",
          "input": ["text", "image"]
        },
        {
          "_launch": true,
          "contextWindow": 32768,
          "id": "qwen2.5-coder:7b",
          "input": ["text"]
        }
      ]
    }
  }
}

What this means:

• id: exact model tag served by Ollama.

• contextWindow: max context tokens for that model. Depends on how much RAM memory you have.

• input: supported input types (text, optional image).

• _launch: true: model is included and can be selected in Pi.

• baseUrl: points Pi to your local Ollama API.

StackContacts Connect is a Chrome extension for newsletter creators. It lets you send direct messages (DMs) — private notes inside Substack — to people who have already subscribed to your publication (your newsletter).

You can personalize each message with the recipient’s name, limit how many people get a message in one run, and skip anyone you’ve already messaged. The extension sends one message at a time with a delay you set, so you stay in control and avoid looking like spam.

Use case examples

I’ve written two posts about how to use StackContacts Connect to automate sending DM messages to your new subscribers.

Here is the first one that covers the basic use cases:

And this is the second one that explains the more advanced use cases and DM campaigns:

Finn'sights

Your Best Subscribers Aren't in Your Inbox—They're in Chat

Nearly 2 in 5 new subscribers respond to my welcome DM. Industry benchmarks for outreach are 5–15%—so that number isn’t a fluke, and you can use it…

Read more

3 months ago · 29 likes · 8 comments · Finn Tropy

If you are wondering how this could work for your situation, please send me a DM or leave a comment below.

Leave a comment

Getting started

You need to have a Substack account and a Chrome browser to use this product.

1. Install the extension
   Download StackContacts Connect from the Gumroad store and load it as an unpacked extension in Chrome (e.g. for development). Follow the instructions from Gumroad downlink link.

2. Log in to Substack
   In the same Chrome browser, open substack.com and log in to the account that owns the newsletter you want to message from. The extension uses your existing Substack session; it does not ask for your password.

3. Open the extension
   Click the StackContacts Connect icon in your Chrome toolbar to open the popup. You should see:

   • Your name and handle (e.g. “Finn Tropy” and “@finntropy”) with your profile picture.

   • The publication you’re messaging for (e.g. “Finn’sights”). If you have more than one publication, a dropdown lets you choose which one to use.

   If you don’t see your name or publication, open Settings (gear icon ⚙ in the popup) and make sure you’re logged in to Substack in that browser.

The main popup screen

Publication (top of popup)

• If you have one or more publications, you’ll see a dropdown with the publication name (e.g. “Finn’sights”). Use it to choose which newsletter’s subscribers you’re messaging.

• If you use a StackContacts data file in Settings instead of the Substack API, you’ll see the publication name with “(from file)” and no dropdown.

Message

• This is the text that will be sent to each selected subscriber.

• You can use {{USER_NAME}} in the message. When Unique Message is on, the extension replaces {{USER_NAME}} with that person’s name (e.g. “Hi Sarah!”).

• The hint below the box reminds you: “Use {{USER_NAME}} to personalize each message.”

Number of subscribers to message

• A number between 1 and 50. This is the maximum number of DMs the extension will send in one run.

• Start small (e.g. 5–10) to test; you can increase it later.

Unique Message

• On (recommended): Each person gets a message with their Substack user name where you used {{USER_NAME}}.

• Off: Everyone gets the same text; {{USER_NAME}} is not replaced, so don’t use it.

Image Attachment (optional)

• Below the message field, you can attach an image (JPEG, PNG, GIF, or WebP) to your messages. Click the attach button or “Add image” to choose a file.

• When attached, a small thumbnail preview appears. Click Remove to clear it.

• The same image is sent with every message in the run. If you don’t attach an image, messages are sent as plain text.

Only Empty Chat

• On (recommended): The extension only messages people you haven’t already started a chat with. You avoid sending duplicate welcome messages.

• Off: The extension may include people you’ve already messaged (useful only in special cases).

Start Automation

You may see a popup request for additional permissions if you have a custom Substack domain. Allow this permission because the automation requires access to use the Substack API calls for reading your subscribers and posting a Direct Message.

See example below:

• Click Start Automation when you’re ready. A confirmation screen shows how many people will be messaged; click Start to begin or Cancel to abort before sending.

• Messages are sent one at a time with the delay you set in Settings.

• During a run you have two controls:

  • Pause — temporarily stops after the current message. Click Resume to continue where you left off.

  • Cancel — stops the run entirely. Any messages already sent stay sent, but no more will be sent. You’ll see a summary of what was sent, skipped, or failed before the run was cancelled.

• When the run finishes (or is cancelled), you’ll see how many were sent, skipped, or failed.

Campaigns tab

The Campaigns tab lets you target specific groups of subscribers with personalized messages. You can choose between two data sources: Substack Subscribers (filter by subscriber attributes) or SQL (DuckDB) (custom queries against a StackContacts database file). Campaigns are saved in the extension so you can reuse them.

Campaign list

• Open the Campaigns tab. You’ll see a list of saved campaigns (name, status, last run) and a + New Campaign button.

• Edit opens the campaign editor. Delete removes the campaign after a confirmation.

Creating or editing a campaign

1. Campaign name — e.g. “Welcome new subs” or “Re-engage lurkers.”

2. Source — Choose your data source:

   • Substack subscribers — Filter subscribers from Substack directly using segments.

   • SQL (DuckDB) — Write a custom SQL query against a StackContacts database file.

Substack subscribers source

When you choose Substack subscribers, you build segments. Each segment targets a group of subscribers with a specific message.

Segments — Each segment has three parts:

• Segment name — A label for your reference (e.g. “Paid monthly”).

• Filters — One or more conditions that narrow down which subscribers to include. All filters within a segment combine with AND logic (subscribers must match every condition).

• Message template — The text sent to subscribers who match this segment. Use {{USER_NAME}} for personalization.

How to add a filter:

Each filter row has three parts:

1. Column — What to filter on. Pick from 42 options that match the Substack dashboard, including:

   • Subscriber attributes: Type, Subscription interval, Activity, Country, State/Province

   • Engagement metrics: Emails opened, Post views, Comments, Shares, Links clicked

   • Payment data: Revenue, First paid date, Subscription source (free/paid), Expiration date, Stripe plan

   • Dates: Start date, Cancel date, Paid upgrade date, Last email open

2. Relation — How to compare. The options depend on the column type:

   

3. Value — What to compare against. The input matches the column type: a dropdown for enums, a date picker for dates, a number field for numbers, or a text field for text.

Tips:

• Click + Add filter to add another condition to the same segment.

• Click the x on a filter row to remove it.

• Rows with no column or no value are ignored.

• Click + Add segment to target a different group with a different message.

• Set a Default message that is used when a segment has no message set.

SQL source (DuckDB)

You need: In Settings > Advanced: StackContacts Data Source, choose your StackContacts database file and select a Publication Schema.

1. Who should receive this message? — A SQL query that returns one row per recipient. The query must be SELECT only (no UPDATE/DELETE). The first line must start with SELECT or WITH. Use:

   • {schema} — Replaced with your selected publication schema (e.g. pub_finntropy_substack_com).

   • {N} — Replaced with the max number of rows (from Settings > Campaigns > Max messages per run).

2. Run Preview — Runs the query and shows a results table. Column names become the tags you can use in the message (e.g. a column USER_NAME becomes {{USER_NAME}}).

3. What should the message say? — Your message template. Use tags like {{USER_NAME}}, {{email}}, or any column name from your query.

Saving and previewing

• Save Draft — Saves the campaign and returns to the list.

• Preview Messages — Shows a preview table with rendered messages. If you haven’t run a preview yet, it fetches subscribers first.
  

  

Sending a campaign

• From the preview screen you’ll see how many recipients there are and a sample of rendered messages.

• Optionally check Dry run (no messages sent) to simulate without sending.

• Click Send Campaign, confirm in the dialog, and the extension sends one DM per row (using the same delay as in Settings). You can Pause or Cancel as in the Send tab.

• Each run is recorded; the campaign’s “last run” is updated and you can see sent/failed counts.

Example SQL queries (StackContacts database)

These examples match the real StackContacts schema. Use mydb."{schema}". for the publication schema and {N} for the limit. Replace {schema} and {N} in your head when reading; the extension replaces them when you run the query.

New subscribers who don’t have a DM thread yet (welcome message):

SELECT s.user_name AS USER_NAME, s.user_id, s.user_email_address AS email
FROM mydb."{schema}".subscribers_summary s
LEFT JOIN mydb."{schema}".dm_thread_participants p
  ON s.user_id = p.user_id AND p.is_publication = false
WHERE p.thread_id IS NULL
  AND s.user_name IS NOT NULL
ORDER BY s.subscription_created_at DESC
LIMIT {N}

Existing DM threads (follow-up message; needs thread_id so the extension can reply in the same thread):

SELECT p.user_name AS USER_NAME, p.user_id, t.thread_id
FROM mydb."{schema}".dm_threads t
JOIN mydb."{schema}".dm_thread_participants p
  ON t.thread_id = p.thread_id AND p.is_publication = false
WHERE t.is_muted = false
ORDER BY t.last_message_timestamp DESC NULLS LAST
LIMIT {N}

All subscribers (no filter by DM thread; use with care):

SELECT s.user_name AS USER_NAME, s.user_id, s.user_email_address AS email
FROM mydb."{schema}".subscribers_summary s
WHERE s.user_name IS NOT NULL
ORDER BY s.subscription_created_at DESC
LIMIT {N}

Subscribers with a summarized interest (from subscriber_events):
Uses the subscriber_events table (opens, reads, post seen, etc.) to set SUBSCRIBER_INTEREST to the most recent post title they interacted with. Good for messages like:

“Hi {{USER_NAME}}, I noticed that you have interest in {{SUBSCRIBER_INTEREST}}. I’m curious, what brought you here and what other topics are you interested in?”

This version only includes subscribers who have no DM thread yet and who have at least one event with a post title.

Using your Substack username (handle) You can use your Substack handle (the part after @, e.g. finntropy). The publication’s user_id is taken from dm_messages.sender_handle (messages you’ve sent). Use a CTE so the handle appears once; replace yourhandle with your handle (no @):

WITH pub AS (
  SELECT DISTINCT sender_id AS user_id
  FROM mydb."{schema}".dm_messages
  WHERE sender_handle = 'yourhandle'
  LIMIT 1
)
SELECT
  s.user_id,
  s.user_name AS USER_NAME,
  s.user_email_address AS email,
  (SELECT e.post_title
   FROM mydb."{schema}".subscriber_events e
   WHERE e.subscriber_email = s.user_email_address AND e.post_title IS NOT NULL
   ORDER BY e.timestamp DESC
   LIMIT 1) AS SUBSCRIBER_INTEREST
FROM mydb."{schema}".subscribers_summary s
LEFT JOIN (
  SELECT DISTINCT thread_id, user_id
  FROM mydb."{schema}".dm_thread_participants
  WHERE user_id != (SELECT user_id FROM pub)
) p ON s.user_id = p.user_id
LEFT JOIN (
  SELECT DISTINCT p.user_id
  FROM mydb."{schema}".dm_thread_participants p
  INNER JOIN (
    SELECT DISTINCT thread_id
    FROM mydb."{schema}".dm_messages
    WHERE sender_id = (SELECT user_id FROM pub)
  ) t ON p.thread_id = t.thread_id
  WHERE p.user_id != (SELECT user_id FROM pub)
) sent ON s.user_id = sent.user_id
WHERE p.thread_id IS NULL
  AND sent.user_id IS NULL
  AND s.user_name IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM mydb."{schema}".subscriber_events e
    WHERE e.subscriber_email = s.user_email_address AND e.post_title IS NOT NULL
  )
ORDER BY s.subscription_created_at DESC
LIMIT {N}

This requires at least one message sent by you in dm_messages so that sender_handle is set. If you have no DMs yet, use the numeric user_id version above.

Required columns: Each row must include either user_id (to start a new DM) or thread_id (to reply in an existing thread). Other columns (e.g. USER_NAME, email) are for your template tags.

Settings > Campaigns

• Max messages per run — Cap on how many rows from the query are used in one send (e.g. 100).

• Require preview before send — You must run Preview before Send Campaign.

• Only allow SELECT (SQL safety) — Campaign SQL must be SELECT-only; the extension blocks UPDATE, DELETE, etc.

Settings (gear icon)

Open Settings from the gear icon ⚙ in the popup.

Your Account

• The extension auto-detects your Substack account and your publications. You’ll see “Logged in as [Your Name] (@yourhandle)” when it works.

• If you have more than one publication, use the Publication dropdown here (or in the popup) to choose which publication’s subscribers to use for the list.

• If auto-detection fails, you can enter your Publication URL manually (e.g. yourname.substack.com).

Send Settings

• Delay between sends (seconds): How many seconds to wait between each DM. We recommend 10-15 seconds. The minimum is 3.

• A higher delay is more respectful to Substack and reduces the chance of looking like spam.

Campaigns

• Max messages per run — Maximum number of recipients per campaign send (e.g. 100).

• Require preview before send — When on, you must run Run Preview (and see the results table) before Send Campaign.

• Only allow SELECT (SQL safety) — When on, campaign SQL must be SELECT-only; other statements are blocked.

Advanced: StackContacts Data Source

• This section is optional. Use it only if you use the StackContacts tool and have an export file (e.g. a StackContacts database file) that you want to use instead of the built-in Substack subscriber list.

• When a StackContacts file is configured, the extension uses that data source instead of the Substack API. You can choose a Publication Schema from the file; only publications that match your current Substack account are shown.

• If you don’t use StackContacts, you can ignore this section.

Best practices for sending DMs to new subscribers

• Only message your own subscribers. The extension is for people who have already subscribed to your publication. Don’t use it for cold outreach or people who haven’t opted in.

• Keep messages short and useful. A brief welcome, a thank-you, or one helpful link is enough. Avoid long or salesy copy.

• Use “Only Empty Chat” when welcoming new subscribers so you don’t message the same person twice.

• Respect the delay. Keep the delay at 10-15 seconds (or higher). Don’t set it to the minimum unless you have a good reason.

• Cap the number per run. Start with 5-10 messages to test; then increase if you’re happy with the result. Avoid very large batches in one go.

• You trigger each run. The extension does not send messages on its own. You click Start Automation each time. That keeps you in control and aligned with how Substack expects tools to behave.

Troubleshooting

• “Not logged in” or “Can’t reach Substack”
  Log in to Substack in this browser and try again. Check your internet connection. If it still fails, open Settings and see if the extension can detect your account.

• Wrong publication or no subscribers
  Open the popup or Settings and check which publication is selected (dropdown or “from file”). Make sure that publication has subscribers. If you use the Substack API, the extension will show how many subscribers it found when you pick a publication in Settings.

• Messages not sending or errors during a run
  Check that the message is not empty and that Number of subscribers to message is at least 1. If Only Empty Chat is on, some candidates may be skipped because you’ve already messaged them; the final count will show sent, skipped, and failed.

• Campaigns: “Load database in Settings” or “Table does not exist”
  The Campaigns tab uses the same StackContacts file and publication schema as the Send tab. In Settings > Advanced: StackContacts Data Source, choose your database file and select a Publication Schema. In your campaign SQL, use mydb."{schema}".table_name (the extension replaces {schema} with the selected schema). Table names must match your file (e.g. subscribers_summary, dm_threads, dm_thread_participants).

If you have any questions about this product or have ideas who to make it better, leave a comment or send me a DM.

Leave a comment

— Finn

This one’s for anyone who runs a newsletter on Substack. If that’s not you, no worries—see you next time.

You’re not broken. Your tools are.

If you’ve run a publication, you’ve felt it: you want to say hi to new subscribers, or follow up with people who’ve been quiet for a while.

Doing it one-by-one in the Substack chat is a lot of work—and it doesn’t scale. Doing it with sketchy automation or spray-and-pray DMs is the opposite of what your readers signed up for.

So you’re stuck between “I should reach out” and “I don’t have a good way to do it.”

StackContacts Connect is a Chrome extension built to sit in that gap. It helps you send personalized direct messages (DMs) to your own Substack subscribers, in small batches, with a delay between each send—so you stay in control and stay on the right side of how Substack expects tools to behave.

What It Actually Does

StackContacts Connect does one job: it lets you message up to 50 of your subscribers in one run, with one message at a time and a pause you choose between each send.

You write the message (or a template), set how many people to include, flip a couple of toggles, and click Start Automation.

No wizard, no dashboard—just one form and one action.

You can personalize each message with the recipient’s name. Use {{USER_NAME}} in your text; when Unique Message is on, the extension replaces it with their name as they’ve configured it in Substack Settings.

So “Hi {{USER_NAME}}, thanks for subscribing” becomes “Hi Leigh Smith, thanks for subscribing” for Leigh, and “Hi Jim Wesson, thanks for subscribing” for Jim. Same draft, one click—but each person gets a message that feels written for them.

Why not just {{FIRST_NAME}} you may ask? Based on analysis of my subscribers that would fail over 40% of the cases. I found this out by sending awkward DMs with “Hi Dr.,” or “Hi The,” to my subscribers.

Here are the user name patterns from my subscribers.

The Only Empty Chat toggle matters. When it’s on, the extension only messages people you haven’t already started a conversation with.

That’s how you welcome new subscribers without saying “Hi!” to someone you already said “Hi!” to last week. When it’s off, you can include existing threads—handy for follow-ups. You decide each time.

Why a Chrome Extension, and Why the API

Earlier designs drove Substack’s chat UI with a content script: open the page, fill the “To:” field, type the message, click Send. It worked in theory.

In practice, any Substack redesign or CSS change could break the selectors. Failures were silent and hard to debug. The code was heavier and more fragile than it needed to be.

So I switched to Substack’s own APIs. The extension talks to the same endpoints Substack’s UI uses—fetch subscribers, start a thread, send a message—via fetch() with your existing session. No DOM automation. No clicking around in a tab. One request per send, clear success or failure.

The result: fewer lines of code, more reliable behavior, and errors you can actually explain when something goes wrong.

Both the subscriber list and the actual DM sending go through the API. You don’t hand your credentials to a third-party server. The extension runs in your browser and uses the session you already have when you’re logged into Substack.

No backend. No new account. Just you, your list, and a controlled send loop.

Zero-Config and Optional Power User Mode

By default, you don’t need a database or a file. You select your publication (the extension auto-detects your primary and other publications), and it pulls subscribers straight from Substack’s subscriber-stats endpoint—the same data API Substack’s own dashboard uses. You get names, user IDs, and subscription dates. Newest subscribers first.

That’s enough for “message my 50 newest subscribers” or “welcome people I haven’t messaged yet” without touching a single file.

Power User Mode

If you use my StackContacts tool and have a local database export, you can point this extension at that file instead.

Then you can run custom SQL—for example, “my paid monthly subscribers who have no DM thread yet” or “threads where we haven’t replied in 14 days”—and use that list as the recipient queue.

StackContact database stays optional. Most users will never need it; the default path is “Substack API only, zero database file setup.”

UPDATE: The latest version has a Campaign mode - see this post:

Finn'sights

Your Best Subscribers Aren't in Your Inbox—They're in Chat

Nearly 2 in 5 new subscribers respond to my welcome DM. Industry benchmarks for outreach are 5–15%—so that number isn’t a fluke, and you can use it…

Read more

3 months ago · 29 likes · 8 comments · Finn Tropy

You’re in Control

Every run is you clicking Start Automation.

The extension doesn’t send on a schedule or in the background. It doesn’t blast hundreds of DMs while you’re away. You choose the message, the cap (1–50), and the delay between sends. A confirmation step shows you how many people will get the message before anything goes out.

During the run, you see progress (”Sending 23 of 50”) and you can Pause at any time.

That’s deliberate. Substack’s terms and content guidelines don’t spell out “bulk DM” rules in one sentence, but they’re clear on no spam, no unattended automation, and no unreasonable load.

Sending only to your own subscribers, with a delay and a per-run cap, keeps you inside those expectations. Good software doesn’t push the platform; it makes it easier to do the right thing.

Subscribers will see your DM and can respond using the Substack Chat link.

Best Practices

• Only message your own subscribers—people who’ve already subscribed to your publication.

• Keep the message short and useful—a welcome, a thank-you, one link.

• Use Only Empty Chat when welcoming new subscribers so you don’t double-message.

• Respect the delay—10–15 seconds between sends is a solid default; going to the minimum “because you can” isn’t worth the risk.

• Start small— 1...5 messages to test, then scale up if you’re happy with the result.

• You trigger each run—the extension doesn’t send on its own.

Why DMs Hit Different

In my testing I’ve seen up to 53% response rate in the first four hours—far above what I expected. For comparison: my posts get about 2.8% (comments per delivered email).

DMs are a much more powerful, personal way to start the conversation with subscribers. Same audience; different channel; the numbers speak for themselves.

Who This Is For

StackContacts Connect is for newsletter creators who want to reach their subscribers personally—without grinding through the chat UI one by one and without turning into a spammer. It’s infrastructure: a single-form tool that does one job well, stays out of your way, and leaves you in control. If that’s the gap you’re in, it’s worth a look.

Troubleshooting

• “Not logged in” or “Can’t reach Substack” — Log in to Substack in the same browser and check your connection.

• Wrong publication or no subscribers — Open your Substack Settings and confirm which publication is selected and that it has subscribers.

• Messages not sending or errors mid-run — Check that the message isn’t empty and “Number of subscribers” is at least 1. With Only Empty Chat on, some recipients may be skipped because you’ve already messaged them; the final count will show sent, skipped, and failed.

If you run a Substack publication and this sounds like your kind of tool, give StackContacts Connect a try.

Questions or feedback? Drop a comment below—I read and response to all of them.

Leave a comment

And if you’re not yet subscribed, hit the button—more creator infrastructure, tools and practical guides on the way.

Subscribe now

More creator tools and guides: Gumroad store

80% of creators can’t see their highest-value customers because Substack and Gumroad don’t talk to each other.

After analyzing my cross-platform data in StackContacts, I discovered:

• 80.09% of my Gumroad customers (736 out of 919) have never subscribed to my Substack

• These “direct customers” find my products through Substack post links, Google, or referrals—then buy without subscribing

• Only 19.91% are subscriber-customers with full engagement data

• But those subscriber-customers spend 33% more ($19.60 vs. $14.70) and engage with 17.55 posts on average

This means:

1. I’m missing engagement data for 80% of my customers (can’t nurture, can’t upsell effectively)

2. I’m leaving money on the table (if all customers engaged like subscribers, average LTV [life-time value] jumps 33%)

3. My Substack content IS working—31% of direct customer sales come from Substack links—but I’m not capturing those leads as subscribers

Imagine if you could unify both platforms into a single customer view. You could:

• See which Substack content drives Gumroad sales

• Identify high-value customers to convert into engaged subscribers

• Calculate true customer lifetime value across both platforms

• Target repeat purchase campaigns to your most engaged subscribers

• Find “dark customers” who bought but never engaged (massive untapped upside)

Let’s dig into your StackContacts data and build that unified view.

The AI Prompt: Copy-Paste Ready

Ready-to-Use StackContacts AI Prompt

Copy this prompt and paste it into Claude (Cursor IDE or Claude Desktop) connected to your StackContacts database.

Using my StackContacts tool, I want to unify my Substack and Gumroad audiences into a single customer view to understand cross-platform behavior and lifetime value.

Please run these analyses:

1. **Email Matching & Overlap Analysis**
   - Count distinct emails in subscribers_detail (Substack)
   - Count distinct emails in gumroad_sales (Gumroad)
   - Find matched emails (present in both)
   - Calculate:
     * Total Substack subscribers
     * Total Gumroad customers
     * Matched emails
     * % of customers who are subscribers
     * % of subscribers who are customers
   - This shows platform overlap

2. **Customer Segmentation by Engagement**
   - For each Gumroad customer, determine:
     * Do they have a Substack profile? (exists in subscribers_detail)
     * Do they have engagement data? (exists in subscriber_events)
   - Segment into:
     * Full Data (Subscriber + Engagement)
     * Subscriber Only (No Engagement)
     * Gumroad Only (No Substack)
   - For each segment, show:
     * Customer count
     * % of total customers
     * Average spend per customer
     * Average posts engaged (if applicable)
   - This reveals data completeness

3. **Customer Lifetime Value by Purchase Behavior**
   - Group customers by:
     * One-Time Buyer (1 product, 1 purchase)
     * Repeat Single Product (1 product, multiple purchases)
     * Multi-Product (2-3 products)
     * Power Customer (4+ products)
   - For each segment, calculate:
     * Customer count
     * Average number of purchases
     * Average LTV (total spent)
     * Average customer lifespan (days from first to last purchase)
   - Sort by average LTV (highest first)
   - This shows who your best customers are

4. **Engagement-LTV Correlation**
   - For all customers, calculate their Substack engagement level:
     * Highly Engaged (50+ events)
     * Moderately Engaged (10-49 events)
     * Lightly Engaged (1-9 events)
     * No Engagement (0 events)
   - For each engagement level, show:
     * Customer count
     * Average purchases per customer
     * Average unique products purchased
     * Average LTV
     * Average posts engaged
   - Sort by average LTV (highest first)
   - This proves whether engagement drives spending

5. **Content-to-Product Affinity Mapping**
   - For customers who engaged with Substack content before purchasing:
     * Identify which posts they read in the 30 days before buying
     * Group by product purchased
   - Show:
     * Product name
     * Post title
     * Number of buyers who read this post (min 5 buyers)
     * Total engagement count
   - Limit to top 30 product-content pairs
   - This reveals "gateway content" for each product

6. **Channel Attribution for Direct Customers**
   - For customers who are NOT Substack subscribers (Gumroad-only):
     * Analyze referrer field to see where they came from
   - Group referrers into categories:
     * Substack (Post/Note Link)
     * Twitter
     * Google Search
     * Direct/Unknown
     * Gumroad Library
     * Other Referrer
   - Show:
     * Traffic source
     * Sales count
     * Unique customers
     * % of direct sales
   - Sort by sales count (highest first)
   - This shows how non-subscribers find your products

7. **Top Customer Engagement Analysis**
   - Identify top 50 customers by total spend
   - Group into tiers:
     * VIP ($100+)
     * High Value ($50-99)
     * Medium Value ($20-49)
     * Low Value (<$20)
   - For each tier, show:
     * Customer count
     * Average spent
     * Average purchases
     * Average posts engaged
     * Average engagement events
     * Average link clicks
   - This reveals behavior patterns of your best customers

Please format results as clear tables with insights after each analysis.

Step-by-Step Instructions

1. Identify Your Database Schema

Confirm your schema prefix. This follows a pattern [pub_username_substack_com] or [pub_customdomain_com] based on what publications you have configured in StackContacts tool.

Quick check:

Show me the table names for subscribers_detail, subscriber_events, and gumroad_sales in the StackContacts tool.

2. Customize the Prompt (Optional)

Adjustments based on your business:

• Date range: Add WHERE created_at >= '2024-XX-XX' to focus on recent data

• Engagement thresholds: Adjust “50+ events” to match your posting frequency

• LTV tiers: Change dollar amounts ($100, $50, $20) to fit your product pricing

• Product limits: Increase/decrease “top 30” in Analysis #5 based on your product count

3. Execute the Prompt

1. Open Cursor IDE with Claude, or Claude Desktop with configured StackContacts MCP server.

2. Paste the full prompt

3. Wait 60-90 seconds (runs at least 7 cross-platform queries)

4. Claude returns 7 analyses showing unified customer view

4. Verify the Results

What you should see:

• Analysis 1: Overlap showing 15-25% of customers are also subscribers (typical range)

• Analysis 2: 70-85% “Gumroad Only” customers (most creators have this blind spot)

• Analysis 3: Power customers with 3-5x higher LTV than one-time buyers

• Analysis 4: Highly engaged customers spending 50-100% more than non-engaged

• Analysis 5: Specific posts linked to specific products (content affinity)

• Analysis 6: 30-50% of direct sales coming from Substack links (even for non-subscribers)

• Analysis 7: VIP customers engaging with 15-25 posts on average

Red flags to watch for:

• Overlap > 50%: Most customers are subscribers—you’re not reaching new audiences

• Overlap < 10%: Very few customers subscribe—your content isn’t converting readers

• All customers in “No Engagement”: Email matching might be failing—check data quality

• Zero content affinity: Products might not be mentioned in posts—add more CTAs

Troubleshooting:

• If Analysis #1 shows 0 matched emails: Check email formatting (lowercase, trim spaces)

• If Analysis #5 returns empty: Customers might not have pre-purchase engagement—extend window to 60 or 90 days

• If Analysis #6 shows “NULL” for referrers: Gumroad might not be tracking referrers—check your Gumroad settings

Understanding Your Results: How to Interpret the Data

What the Numbers Mean

Analysis 1: Email Matching & Overlap

Substack Emails:1,175 
Gumroad Emails: 919 
Matched:183
% Customers Are Subscribers: 19.91% 
% Subscribers Are Customers: 15.57%

Interpretation:

• 1,175 Substack subscribers (your content audience)

• 919 Gumroad customers (your product buyers)

• 183 matched emails (people in both systems using same email address)

• 19.91% of customers are subscribers - 4 out of 5 buyers never signed up for your newsletter (or used different email for Gumroad)

• 15.57% of subscribers are customers - Only 1 in 6 subscribers has bought

Key insight: You have 736 “dark customers” (80%) who bought without subscribing. These are high-intent buyers you can’t nurture, can’t upsell to, and can’t engage with through email campaigns. Massive untapped opportunity.

Analysis 2: Customer Segmentation by Engagement

| Data Completeness | Customers | % of Customers | Avg Spend | Avg Posts Engaged |
|-------------------|-----------|----------------|-----------|-------------------|
| Gumroad Only      | 736       | 80.09%         | $14.70    | 0.0               |
| Full Data         | 183       | 19.91%         | $19.60    | 17.55             |

Interpretation:

• 80.09% of customers have zero Substack data (can’t track their journey)

• 19.91% of customers have full data (subscriber profile + engagement history)

• Full-data customers spend 33% more ($19.60 vs. $14.70)

• Full-data customers engage with 17.55 posts on average (high investment in your content)

Key insight: Converting “Gumroad Only” customers to engaged subscribers could boost average LTV from $14.70 to $19.60 - this is a 33% increase.

At 736 customers, that’s $3,606 in additional revenue just from better cross-platform integration.

Analysis 3: Customer Lifetime Value (LTV) by Purchase Behavior

| Customer Segment          | Count | Avg Purchases | Avg LTV | Avg Lifespan (Days) |
|---------------------------|-------|---------------|---------|---------------------|
| Power Customer (4+)       | 25    | 5.04          | $54.40  | 190.11              |
| Multi-Product (2-3)       | 212   | 2.33          | $39.12  | 100.01              |
| One-Time Buyer            | 662   | 1.00          | $16.32  | 0.00                |
| Repeat Single Product     | 20    | 2.40          | $11.50  | 66.03               |

Interpretation:

• Power Customers (25): Buy 4+ products, spend $54.40, stay active for 190 days (6+ months)

• Multi-Product (212): Buy 2-3 products, spend $39.12, active for 100 days

• One-Time Buyers (662): 72% of customers buy once and never return, spend $16.32

• Repeat Single Product (20): Buy same product multiple times (subscriptions?), lowest LTV

Key insight: Your top 25 customers (2.7% of buyers) spend 3.3x more than average. And 212 multi-product buyers (23%) drive significantly higher LTV. Focus on moving one-time buyers into the multi-product segment.

Analysis 4: Engagement-LTV Correlation

| Engagement Level   | Customers | Avg Purchases | Avg Products | Avg LTV | Avg Posts |
|--------------------|-----------|---------------|--------------|---------|-----------|
| Moderately Engaged | 39        | 1.59          | 1.56         | $40.35  | 3.0       |
| Highly Engaged     | 137       | 2.03          | 1.82         | $36.29  | 20.2      |
| Lightly Engaged    | 7         | 1.57          | 1.57         | $22.97  | 0.29      |
| No Engagement      | 736       | 1.33          | 1.28         | $19.00  | 0.0       |

Interpretation:

• Moderately Engaged (39 customers, 3 posts): Highest LTV at $40.35 (2.1x higher than non-engaged)

• Highly Engaged (137 customers, 20.2 posts): $36.29 LTV, 2.03 purchases (most loyal)

• No Engagement (736): $19.00 LTV, 1.33 purchases (80% of customers)

Why is “Moderately Engaged” highest LTV? Sweet spot—they engage enough to build trust (3 posts) but aren’t “browsers” who read everything. They’re problem-solvers who read your content, find solutions, and buy.

Key insight: Engagement drives spending, but there’s a quality threshold. Getting customers to read 3-5 targeted posts is more valuable than getting them to read 20 random posts. Focus on converting the 736 non-engaged customers to “moderately engaged” status.

Analysis 5: Content-to-Product Affinity

| Product Name               | Post Title           | Buyers Who Read | Engagements|
|----------------------------|----------------------|-----------------|-------------|
| Finn Tropy Substack Pro Studio| Substack Pro Studio User Guide | 32   | 585         |
| Finn Tropy Substack Pro Studio| How To Write Notes That Convert| 19   | 253         |
| Finn Tropy Substack Pro Studio 🔥 Growth Test for Serious ...  | 19   | 285         |
| Substack Control Center | You're Publishing Weekly, But ...    | 11   | 166         |
| Substack Control Center | How I Turned My Substack Posts ...   | 9    | 99          |
| CreativeTech Pro Studio (Bundle)| Substack Pro Studio User ... | 12   | 63          |

Interpretation:

• “Substack Pro Studio User Guide” is the #1 gateway content for your Pro Studio product (32 buyers read it before purchasing)

• “How To Write Notes That Convert” drives 19 Pro Studio sales (253 engagements)

• Different posts lead to different products (Control Center buyers read different content than Pro Studio buyers)

Key insight: Each product has “gateway content”—specific posts that prime buyers. Double down on these posts:

• Reference them in product descriptions

• Link to them in follow-up emails

• Repost them as Notes

• Turn them into lead magnets

Analysis 6: Channel Attribution for Direct Customers

| Traffic Source             | Sales | Customers | % of Direct Sales |
|----------------------------|-------|-----------|-------------------|
| Other Referrer             | 324   | 278       | 33.13%            |
| Substack (Post/Note Link)  | 304   | 277       | 31.08%            |
| Direct/Unknown             | 243   | 210       | 24.85%            |
| Google Search              | 78    | 77        | 7.98%             |
| Twitter                    | 15    | 15        | 1.53%             |
| Gumroad Library            | 14    | 10        | 1.43%             |

Interpretation:

• 31.08% of non-subscribers still come from Substack links (they clicked but didn’t subscribe)

• 33.13% come from “Other Referrer” (likely other blogs, forums, YouTube, podcasts)

• 7.98% from Google Search (SEO working, but not a huge driver)

• 1.53% from Twitter (low—opportunity to grow here?)

Key insight: Substack content drives 31% of direct sales even when people don’t subscribe. Your posts are working as product landing pages. But you’re not capturing these leads—304 buyers clicked through without subscribing.

Add subscription CTAs to your product-focused posts.

Analysis 7: Top Customer Engagement Analysis

| Customer Tier| Customers| Avg Spent| Avg Purchases| Avg Posts| Avg Events|Avg Clicks|
|--------------|----------|----------|--------------|----------|-----------|----------|
| VIP ($100+)  | 14       | $120.43  | 4.14         | 16.29    | 169.0     | 3.71     |
| High Value   | 36       | $89.06   | 1.75         | 4.25     | 60.06     | 1.44     |

Interpretation:

• 14 VIP customers spend $120+ each, averaging 4.14 purchases and engaging with 16.29 posts

• 36 high-value customers spend $50-99, with lower engagement (4.25 posts)

Key insight: Your VIPs are deeply engaged (169 events, 16 posts). They’re not casual buyers - they’re superfans who read your content regularly and buy multiple products. Identify them by name and give them white-glove treatment:

• Personal thank-you emails

• Early access to new products

• Direct line to you for support

• Exclusive content or community access

Pattern Recognition

Pattern 1: The 80/20 Dark Customer Problem

80% of your customers have zero Substack engagement. They found your product, bought it, and disappeared.

Why? They discovered your content through search or referrals, clicked your product link, and bought without ever subscribing. Or they bought from a repost/share without visiting your Substack.

Action: Add a post-purchase email sequence:

1. “Thanks for buying [product]! Want to get more value? Subscribe to my newsletter for advanced tips.”

2. Include link to best posts related to the product they bought

3. Offer a content upgrade (guide, template, case study) in exchange for subscribing

Pattern 2: Engagement = 2x LTV (But Only to a Point)

Moderately engaged customers ($40.35 LTV) spend more than highly engaged ($36.29) and WAY more than non-engaged ($19.00).

Why? Reading 3-5 posts builds trust and demonstrates value without turning readers into “browsers.” Highly engaged readers might enjoy your writing but not need your products (yet).

Action: Don’t optimize for maximum engagement. Optimize for relevant engagement—get customers to read the 3-5 posts that best showcase your products.

Pattern 3: Gateway Content Exists for Every Product

Specific posts consistently appear before specific product purchases. “User Guides” dominate, but tactical “how-to” posts also drive sales.

Why? Buyers want proof of value before purchasing. Reading a user guide shows them exactly what they’ll get. How-to posts demonstrate your expertise.

Action: For every product:

1. Write a detailed user guide (even before launch)

2. Create 2-3 tactical how-to posts showing the product in action

3. Link these posts in your product description

4. Reference them in sales emails

Pattern 4: Substack Links Drive 31% of “Non-Subscriber” Sales

Nearly 1 in 3 direct customers found your product through a Substack link but didn’t subscribe.

Why? They were in “problem-solving mode”—clicked for the solution, bought immediately, and left. They weren’t thinking about long-term content consumption.

Action: Add inline subscription CTAs to product-focused posts:

• “Want more insights like this? Subscribe below.”

• “I share weekly tips on [topic]—join 1,000+ creators.”

• Pop-up on exit intent: “Before you go, grab my free [resource]”

Red Flags to Watch For

Warning Sign 1: Overlap < 10%

If fewer than 10% of customers are subscribers, your Substack content isn’t converting readers to buyers. You have an audience but no sales funnel.

Action: Audit your posts—how many mention your products? Add more product references, CTAs, and use cases. Make buying a natural next step.

Warning Sign 2: VIP Customers with Low Engagement

If your highest spenders barely engage with content, they’re discovering products elsewhere (referrals, search, ads). Your content isn’t driving revenue.

Action: Survey VIP customers: “How did you find us?” Double down on whatever channel they mention.

Warning Sign 3: All Customers Are One-Time Buyers

If you have no multi-product or power customers, your products don’t complement each other or you’re not cross-selling.

Action: Create product bundles, build upgrade paths (Basic → Pro), and send post-purchase upsell emails.

Actionable Insights

How to translate numbers into decisions:

Insight: “80% of customers have no Substack data”
Action: Launch post-purchase nurture sequence:

• Email 1: “Get more value from [product] with these posts...”

• Email 2: “Advanced tips every week—subscribe here”

• Track conversion rate (how many direct customers become subscribers)

Insight: “Moderately engaged customers spend $40.35 vs. $19.00 for non-engaged (2.1x)”
Action: Create a “New Customer Onboarding” sequence:

• Send 3-5 best posts related to their purchase

• Include product tips, case studies, and tutorials

• Goal: Move them from “no engagement” to “moderately engaged” (3-5 posts)

Insight: “Power customers buy 4+ products and spend $54.40 (3.3x average)”
Action: Identify your 25 power customers by name:

• Send personal thank-you email

• Offer exclusive early access to new products

• Ask for feedback on product roadmap

• Create a private Slack/Discord for VIPs

Insight: “Substack links drive 31% of non-subscriber sales”
Action: Add subscription CTAs to all product-focused posts:

• Inline: “Subscribe for more insights like this”

• Exit pop-up: “Before you go, grab my free template”

• Post-click: “You clicked [product link]—want weekly updates?”

Insight: “’User Guide’ posts are gateway content for multiple products”
Action: Write user guides for EVERY product:

• Publish them on Substack (not just in product docs)

• Link from product description: “Read the full guide here”

• Reference in sales emails: “See it in action”

Prioritization Framework

Priority 1: Convert Dark Customers to Subscribers

Impact: 736 customers with zero engagement—converting 20% would add 147 engaged subscribers

Actions:

1. Export list of Gumroad customers not in subscribers_detail

2. Send post-purchase email sequence:

   • “Thanks for [product]! Here are 3 posts to get more value...”

   • Include subscribe CTA

   • Offer content upgrade (template, guide) in exchange for subscribing

3. Track conversion rate weekly

4. Test subject lines, offers, and timing

Expected impact: 15-25% of direct customers subscribe, boosting future upsell opportunities

Priority 2: Create Gateway Content for Each Product

Impact: Drive more sales from existing audience by showing value upfront

Actions:

1. For each product, write:

   • Detailed user guide (400-800 words)

   • How-to tutorial showing product in action

   • Case study or before/after results

2. Publish on Substack (make public or free)

3. Link from product description: “Read the guide”

4. Reference in sales emails

5. Track views → clicks → purchases for each post

Expected impact: 20-30% increase in product page conversion rate

Priority 3: Build Power Customer Retention Program

Impact: 25 power customers at $54 LTV—losing even 5 would cost $270 in future revenue

Actions:

1. Export list of customers with 4+ product purchases

2. Send personal email: “You’re a VIP customer—want early access to [new product]?”

3. Create exclusive channel (private DMs, email list, Discord, Slack, etc)

4. Offer special perks:

   • 30% discount on future products

   • Free upgrades when you launch new versions

   • Direct priority support

5. Survey them quarterly: “What should we build next?”

Expected impact: 80-90% retention rate for power customers (vs. 50% without program)

Priority 4: Optimize Substack-to-Gumroad Conversion Flow

Impact: 31% of non-subscribers come from Substack links—improving that flow boosts sales 10-20%

Actions:

1. Audit product-focused posts—how prominent are CTAs?

2. Add multiple CTAs:

   • Inline (middle of post)

   • End of post

   • PS section

3. A/B test CTA copy:

   • “Check out [product]” vs. “Get [product] (20% off today)”

   • “Learn more” vs. “Buy now”

4. Add exit-intent pop-up on product-focused posts

5. Track: Post views → link clicks → purchases

Expected impact: 15-25% increase in Substack → Gumroad conversion rate

Case Study: Real Results from My Data

The Setup

• Publication: Finn’sights (finntropy.substack.com)

• Substack Subscribers: 1,175

• Gumroad Customers: 919

• Time Period: August 2024 - December 2025 (16 months)

• Products: 10 Substack creator tools ($0-$79)

• Hypothesis: “Unifying Substack + Gumroad data will reveal hidden revenue opportunities”

The Analysis

I ran the cross-platform insights prompt on my StackContacts database. What I discovered completely changed my product strategy.

Key Findings:

1. 80.09% of customers (736) never subscribed to my Substack (or used different email address)

2. Full-data customers spend 33% more ($19.60 vs. $14.70)

3. Power customers (25) spend 3.3x more than average ($54.40 vs. $16.50)

4. Moderately engaged customers have highest LTV ($40.35 for 3 posts vs. $19 for 0 posts)

5. “User Guide” posts are gateway content for multiple products

6. 31% of non-subscribers found products through Substack links

Surprising Findings

Surprise #1: 80% of my customers are invisible

I assumed most customers came from my newsletter.
Nope. 736 out of 919 customers (80%) bought without subscribing.

They found my products through:

• Substack post links (31%) - clicked through without subscribing

• Google search (8%) - landed on product-focused posts

• Referrals (33%) - shared by others

• Twitter (1.5%) - tiny but present

Why this shocked me: I was spending 80% of my time creating content for subscribers, but 80% of revenue came from non-subscribers.

Result: I started treating every product-focused post as a landing page with clear subscribe CTAs.

Outcome: Conversion rate of direct customers → subscribers jumped from 5% to 18% (+260% improvement).

Surprise #2: Moderate engagement = highest LTV

I expected highly engaged readers (20+ posts) to be my best customers. Wrong.

• Moderately engaged (3 posts): $40.35 LTV

• Highly engaged (20 posts): $36.29 LTV

• No engagement: $19.00 LTV

Why? Moderately engaged customers read just enough to build trust and see value. They’re not “browsers”—they’re problem-solvers who read targeted content and buy solutions.

Highly engaged readers enjoy my writing but might not have urgent problems my products solve (yet).

Result: I stopped optimizing for maximum engagement and started creating “onboarding sequences” focused on 3-5 key posts.

Outcome: New customer 30-day LTV increased from $16.50 to $24.20 (+47% improvement).

Surprise #3: I have 25 power customers worth $1,360

Before this analysis, I treated all customers the same. But 25 customers bought 4+ products, spending $54.40 each ($1,360 total).

These weren’t random buyers—they were superfans who:

• Engaged with 16+ posts on average

• Bought multiple products over 6+ months

• Actively clicked links (3.71 clicks per customer)

Why I missed them: They were buried in a list of 919 customers. No way to identify them without cross-platform analysis.

Result: I manually reached out to all 25:

• Personal thank-you email or DMs

• Offered discounts on new products

• Invited to private beta testing with early access to new products

• Asked for product roadmap feedback and features they need

Outcome: 23 replied (92% response rate), 18 bought my next product launch within 48 hours, and 3 became beta testers.

Surprise #4: “User Guide” posts drive sales across multiple products

I thought each product needed unique content. But “Substack Pro Studio User Guide” appeared in the pre-purchase engagement for:

• Finn Tropy Substack Pro Studio (32 buyers)

• CreativeTech Bundle (12 buyers)

• Substack Control Center (8 buyers)

Why? User guides demonstrate value and reduce purchase friction. Buyers want to see exactly what they’re getting before clicking “Buy.”

Result: I wrote detailed user guides for every product and published them as Substack posts (not just in product docs).

Outcome: Product page conversion rate increased from 12% to 19% (+58% improvement).

The Impact

What I changed based on cross-platform insights:

1. Launched post-purchase email sequence - Convert direct customers to subscribers

2. Created onboarding emails - Move new customers to “moderately engaged” status

3. Built VIP customer program - 25 power customers get white-glove treatment

4. Wrote user guides for all products - Published on Substack as gateway content

5. Added subscribe CTAs to product posts - Capture leads who click Gumroad links

Measurable outcomes:

• Direct customer → subscriber conversion: 5% → 18% (+260% improvement)

• New customer 30-day LTV: $16.50 → $24.20 (+47% improvement)

• Power customer retention: 92% response rate to VIP outreach

• Product page conversion rate: 12% → 19% (+58% improvement)

• Total additional revenue (6 months): ~$4,800 from better cross-platform integration

Key Takeaway

The one insight that mattered most:

You don’t have two audiences—you have ONE audience experiencing your business across two platforms.
Treating them as separate leaves massive revenue on the table.

Before cross-platform analysis, I saw:

• Substack: “I have 1,175 subscribers”

• Gumroad: “I have 919 customers”

Now I see:

• 183 subscriber-customers (engaged + buying) - nurture heavily

• 992 subscribers who haven’t bought (warm leads) - targeted sales campaigns

• 736 customers who aren’t subscribers (dark customers) - convert to engaged subscribers

• 25 power customers (superfans) - VIP treatment

Each segment needs a different strategy. And I only discovered them by unifying the data.

Why this analysis was worth doing:

Cross-platform insights revealed $4,800 in quick-win revenue opportunities I couldn’t see before:

• Converting 20% of dark customers to subscribers (future upsell opportunity)

• Boosting LTV 47% by onboarding customers to moderate engagement

• Retaining 25 power customers worth $1,360 in repeat purchases

• Improving product conversion 58% with better gateway content

That’s the difference between managing two silos and running one integrated business.

Advanced Applications: Taking It Further

Variations on the Prompt

Variation 1: Product Purchase Sequences

Identify common product purchase paths:

For customers who bought multiple products, calculate:
- Which product was purchased first, second, third, etc.
- Time between purchases
- Most common product sequences (e.g., Product A → Product B → Product C)

Create a product flow diagram showing typical upgrade paths.

Insight: Optimize product bundles and upsell sequences based on natural purchase progressions.

Variation 2: Churn Risk Scoring

Identify customers likely to disengage:

For subscriber-customers, calculate:
- Days since last purchase
- Days since last Substack engagement
- Drop in engagement velocity (month-over-month)

Flag customers showing "cooling" patterns (high engagement → low engagement).

Insight: Run re-engagement campaigns before customers churn completely.

Variation 3: Lookalike Audience Building

Profile your best customers to find similar prospects:

For power customers (4+ products), analyze:
- Which Substack posts they engage with most
- Their engagement patterns (frequency, timing, content types)
- Product categories they prefer

Find current subscribers who match these patterns but haven't bought yet.

Insight: Target “lookalike” subscribers with personalized product recommendations.

Integration Opportunities

Combine Cross-Platform with Other StackContacts Analyses:

1. Cross-Platform + Revenue Attribution

See which content drives specific customer segments:

For each customer segment (power, multi-product, one-time):
- Which posts do they engage with before buying?
- Which posts have highest conversion rate for each segment?
- Which content types (tutorials, case studies, user guides) work best?

Create segment-specific content strategies.

Outcome: Write content tailored to each customer tier’s preferences.

2. Cross-Platform + Predictive Analytics

Build purchase propensity models across platforms:

For subscribers who haven't bought yet:
- Calculate their "customer similarity score" (how closely they match existing buyer behavior)
- Identify high-propensity prospects (match power customer patterns)
- Flag subscribers entering "purchase window" (engagement spike + link clicks)

Create tiered prospect lists: Hot, Warm, Cold based on cross-platform signals.

Outcome: Prioritize sales outreach to subscribers most likely to buy.

3. Cross-Platform + Engagement Velocity

Track how Substack engagement impacts Gumroad purchasing timing:

For subscriber-customers, analyze:
- Engagement velocity in the 7 days before purchase
- Which engagement spike patterns predict buying
- Optimal time to send product offers (after X posts read, Y links clicked)

Build automated triggers: "If subscriber reads 3 posts + clicks 1 link in 7 days → send product offer email."

Outcome: Perfectly timed sales outreach based on cross-platform momentum.

Next-Level Questions

Questions to explore after building your unified view:

1. “Which products have the highest subscriber → customer conversion rate?”

   • Compare conversion rates across your product line

   • Double down on products that convert subscribers best

2. “Do power customers discover me differently than one-time buyers?”

   • Segment referrer data by customer tier

   • Invest in channels that attract high-LTV customers

3. “What’s the ROI of converting a direct customer to a subscriber?”

   • Calculate: (Subscriber-Customer LTV - Direct Customer LTV) × Conversion Rate

   • Justify investment in post-purchase nurture campaigns

4. “Which subscriber content consumption patterns predict multi-product purchases?”

   • Find common post reading sequences before power customer conversions

   • Build content funnels that replicate those patterns

5. “How does cross-platform engagement correlate with customer lifetime?”

   • Compare lifespan (first → last purchase) for engaged vs. non-engaged customers

   • Prove engagement drives retention, not just initial sales

Common Pitfalls and How to Avoid Them

Data Quality Issues

Problem 1: Email Formatting Inconsistencies

Emails might have different formats across platforms (uppercase vs. lowercase, trailing spaces, plus-addressing like user+tag@email.com).

How to spot it:

• Overlap percentage is unexpectedly low (<10%)

• Manual checking reveals customers you know are subscribers but don’t match

How to fix it:

-- Normalize emails before matching
SELECT LOWER(TRIM(email)) as email_normalized
FROM subscribers_detail

Problem 2: Missing Referrer Data

Gumroad doesn’t always capture referrer information, especially for mobile purchases or certain browsers.

How to spot it:

• Large percentage of “Direct/Unknown” traffic (>50%)

• Analysis #6 shows NULL for most referrers

How to fix it:

• Use UTM parameters in all Gumroad links: ?utm_source=substack&utm_campaign=post123

• Track UTM parameters separately in your own database

• Survey customers: “How did you hear about us?”

Problem 3: Duplicate Customers

Some customers might use multiple email addresses across platforms.

How to spot it:

• Customer count seems inflated

• Same name appears with different emails in Gumroad

How to fix it:

• Manually deduplicate VIP/power customers by name

• Use fuzzy matching on names + email addresses (advanced)

• Accept some duplication—perfect matching is impossible

Misinterpretation Risks

Risk 1: Confusing Correlation with Causation

Just because subscriber-customers spend more doesn’t mean subscribing CAUSES higher spending. Maybe high-intent buyers naturally engage more.

How to avoid it:

• Run A/B test: Send post-purchase nurture emails to 50% of direct customers, track LTV difference

• Compare before/after LTV for customers who subscribed post-purchase vs. those who didn’t

• Look for directional evidence, not absolute proof

Risk 2: Over-Optimizing for Power Customers

Focusing only on your top 25 customers might neglect the 662 one-time buyers who could become repeat customers.

How to avoid it:

• Balance attention: 40% power customers, 40% multi-product buyers, 20% one-time buyers

• Test activation campaigns for one-time buyers: “Buy a second product and get 20% off”

• Track segment transitions (how many one-time → multi-product?)

Risk 3: Assuming All Non-Subscribers Need to Subscribe

Some customers prefer transactional relationships—they want products, not newsletters.

How to avoid it:

• Respect preferences: Add “product updates only” email option

• Track unsubscribe rates for post-purchase nurture campaigns

• A/B test value prop: “Weekly tips” vs. “Product updates + tips”

Best Practices

How often to run this analysis:

• Monthly: Full cross-platform refresh to track segment changes

• After product launches: See how new products affect customer segmentation

• Quarterly: Deep dive into power customer behavior, update VIP program

• Annually: Strategic review of platform overlap, channel attribution, LTV trends

When to update your approach:

• New product line: Different products might attract different customer types

• Pricing changes: LTV thresholds might need adjustment

• Audience growth: As you scale past 5K subscribers, patterns might shift

• Platform changes: If Substack or Gumroad add new features, update queries

How to track changes over time:

• Export key metrics monthly to spreadsheet:

  • Platform overlap percentage

  • % customers with full data

  • Average LTV by engagement level

  • Power customer count

  • Top gateway content (changes over time?)

• Set alerts: If overlap drops below 15% or power customers churn, investigate immediately

Your Turn: Try It and Share

Call to Action

Ready to unify YOUR Substack and Gumroad data?

1. Copy the AI prompt from this article

2. Paste into Claude (connected to StackContacts)

3. Run the 7 analyses and review your cross-platform insights

4. Identify your dark customers and power customers

5. Reply with your findings: What % of YOUR customers have no Substack data?

Questions to explore:

• How many “dark customers” do you have?

• What’s YOUR overlap percentage? (Higher or lower than 19.91%?)

• Do engaged customers spend more for you too?

Community Engagement

Share your surprising cross-platform insights:

• “85% of my customers never subscribed—totally blind to them!”

• “Our power customers spend $200+ each—building a VIP program now”

• “Discovered that ‘How-To’ posts drive 3x more sales than case studies”

Compare across niches:

• B2B creators likely have higher overlap (subscribers become customers)

• Tool/product creators might have more direct customers (Google search traffic)

• Coaches/consultants might have deeper engagement before purchasing

Your data will differ. That’s the point—unify YOUR platforms and optimize for YOUR patterns.

Office Hours Invitation

Want help analyzing your cross-platform data?

Bring your unified customer view to weekly office hours (paid subscribers only):

• Review your platform overlap and dark customer strategy

• Discuss power customer retention programs

• Get feedback on post-purchase nurture campaigns

• Ask about advanced cross-platform scoring models

Next office hours: February 4th, 2026 at 1 PM EST - send a DM to get an invitation.

Key Takeaways

✅ 80% of Gumroad customers never subscribe to Substack—you’re blind to most of your buyers

✅ Subscriber-customers spend 33% more than direct customers ($19.60 vs. $14.70)

✅ Power customers (4+ products) spend 3.3x more than one-time buyers ($54.40 vs. $16.32)

✅ Moderately engaged customers have highest LTV—3-5 posts is the sweet spot for conversion

✅ “User Guide” posts are gateway content—they drive purchases across multiple products

✅ 31% of non-subscribers find products through Substack links—add subscribe CTAs to capture them

✅ VIP customers (top 50 by spend) engage with 16+ posts—identify and nurture them personally

✅ Run monthly cross-platform analysis to track segment changes and optimize strategies

— Finn

I study creator growth the way engineers study systems—with experiments, dashboards, and curiosity. I built StackContacts to turn subscriber data into revenue, and I share weekly AI prompts, research, and office hours to help you extract maximum value from your investment.

Get StackContacts: https://www.stackcontacts.com/

About This Article:

• Data Source: FinnTropy StackContacts database (Aug 2024 - Dec 2025)

• Analysis Method: Cross-platform email matching, cohort comparison, LTV calculation

• Platforms Unified: Substack (1,175 subscribers) + Gumroad (919 customers)

• Key Finding: 80% of customers have no Substack engagement data (736 “dark customers”)

I never meant to build a CRM. I was just trying to remember who my customers were.

But somewhere between the 50th and 159th person, my scattered notes became a system.

And that system became something I rely on more than any traditional CRM I’ve ever tried.

The Problem: Platforms Hide Your People

If you’re building products for creators—or really, any business where relationships matter—you’ve probably felt this frustration:

• Substack tells you who subscribed, but not who they are beyond an email

• Gumroad shows purchases, but not the conversation you had last week

• Substack DMs are disconnected from everything else, like your Posts and Notes

• Email feels like shouting into the void, with no memory of what resonated

Every platform gives you data, but none of them give you context. And context is what turns a subscriber into a relationship.

I needed a place to connect the dots. Not just “Emily bought my product,” but:

• What does Emily actually do?

• What feedback has she given?

• When did she last engage?

• What should I follow up on?

Traditional CRMs felt like overkill. I’m not managing a sales pipeline to close a deal - I’m trying to remember people like a human being.

So I built it differently.

Real-World Example: What This Actually Looks Like

Let me show you why this matters with a real customer story.

November 3, 2025 - Emily signs up for my newsletter

• Reads the Substack Pro Studio guide 3 times

• Purchases the product 5 hours later for $79

• I create her person file, note the fast conversion

November 21, 2025 - She buys a second product ($25)

• Update her file: total_revenue: $104

• Tag her as power_user

• Note: Multi-product buyers are rare, high-value

December 31, 2025 - She comments with a bug report

• I copy her feedback into her timeline

• Link to the development task to fix it

• Add to recommended actions: “Reply and thank her”

January 6, 2026 - I review her file for this article

• See she’s been active, but no direct DM yet

• Notice she runs her own Substack on trauma healing

Today - Emily comments on my post. I open her file. Saw she’d been a customer for over two months, gave great feedback on scheduling features, and mentioned struggling with subscriber churn.

I replied with a specific answer about churn tracking—because I remembered.

Without this system, Emily is just “another customer who bought something.”

With it, she’s:

• A power user who converts fast ($104 in 18 days)

• A feedback provider improving my products

• A potential collaborator with an aligned audience

• Someone I need personally to reach out to

That’s the difference between data and context.

The Secret: I Don’t Do This Manually

Here’s where it gets interesting.

I don’t manually write Emily’s 405-line dossier by hand. That would be insane.

Instead, I use the Cursor app (an AI code editor) to automatically generate and update markdown files by querying my StackContacts database.

How It Works

I type a slash command: /subscriber_analysis_prompt Emily

30 seconds later, I have:

• Full profile with membership status and revenue

• Complete timeline of all 22 engagement events

• Exact text of her bug report DM

• Her publication details and bio

• Purchase history with order IDs

• Recommended next actions

• Everything formatted in markdown, ready to use

Before (manual copy/paste hell):

• Open Substack, find Emily’s email

• Check the subscriber dashboard for event activity

• Go to Gumroad, search for purchases

• Check my DMs for conversations

• Go to the comments section, search for her comments

• Copy/paste everything into a note

• Try to remember what I wanted to follow up on

• Give up halfway through because it takes an hour

After (Cursor + StackContacts):

/subscriber_analysis_prompt Emily

Done. 30 seconds.

It’s Not Just People—It’s Everything

I have three slash commands for three types of business intelligence:

1. /subscriber_analysis_prompt - People analytics

• Queries 15+ database tables

• Analyzes subscriber details, events, comments, DMs, and purchases

• Generates comprehensive relationship profiles

• Example: Emily’s 405-line customer dossier

2. /update_product_metrics - Product analytics

• Reads Gumroad sales data

• Calculates revenue, trends, and customer metrics

• Updates product files with formatted tables

• Example output:

3. /update_post_data - Content performance

• Queries Substack post metrics

• Analyzes engagement, open rates, conversions

• Compares to publication benchmarks

• Example outputs:

So I have three parallel systems:

• People tracking: Deep relationship context

• Product tracking: Revenue and sales analytics

• Post tracking: Content performance and audience response

All living in markdown files. All updated on demand with a slash command.

In 30 seconds.

Why This Changes Everything

This isn’t just “automation.” It’s augmented memory.

The AI handles data gathering (the tedious part):

• Querying multiple database tables

• Correlating events across platforms

• Calculating metrics and timelines

• Finding patterns in engagement data

I handle relationship building (the human part):

• Reading the context and adding my observations

• Deciding who to reach out to and why

• Crafting personalized messages

• Responding to DMs and comments

• Updating the file with new interactions

The markdown file becomes a collaboration space between:

• The AI’s perfect memory of data

• My human judgment about relationships

The Real Power: Query Anything Instantly

I can ask Cursor AI questions like:

People queries:

• “Who are my top 10 customers by revenue?”

• “Show me everyone who commented but never bought anything.”

• “Find people who signed up in November and went silent.”

• “Who mentioned ‘churn’ in a DM or comment?”

Product queries:

• “Update all product metrics for January.”

• “Which product has the highest revenue per customer?”

• “Show me products with declining sales trends.”

• “Compare performance across all 6 products.”

Post queries:

• “Update metrics for my last 5 posts.”

• “Which posts have the highest engagement rates?”

• “Show me posts that drove paid conversions.”

• “Find posts with high open rates but low read completion.”

Cross-entity queries:

• “Find customers who bought multiple products.”

• “Which people should I reach out to this week?”

• “Show me high-value customers with recent engagement.”

• “Create a project file for the next product launch.”

It queries the database, analyzes results, and either:

• Updates existing people/product/post/support files with insights

• Generates summary lists with recommendations

• Creates new project files with follow-up actions

• Writes comparative analysis across entities

It’s like having a junior data analyst who never sleeps and has perfect recall.

Why Obsidian + Markdown, Not a Real CRM?

I’ve tried “proper” CRMs. Notion databases, Airtable, TwentyCRM, Odoo, and even custom spreadsheets.

They all failed because they optimized for capture over recall.

Here’s why this system works:

1. It’s Where I Already Think

I don’t have to “go into the CRM.”

I’m already writing daily notes, logging ideas, and planning projects. People just live in the same space where I do my daily work.

2. Markdown is Portable

All my relationship data is just text files. If the Obsidian app dies tomorrow, I still have my markdown files that work in any text editor.

3. Links Create Memory

Traditional CRMs have “related records.” Obsidian has bidirectional links that show context everywhere. It’s not a feature—it’s how the whole system works.

When I mention Emily in a daily note, it automatically appears on her timeline. When she comments on a product, it’s linked in both her file and the product file. Write it once, see it everywhere it matters.

I can navigate through links, instead of spending time searching for what I don’t fully remember.

4. Friction is Near-Zero

Creating a new person takes 5 seconds. Updating a note takes 2 seconds.

If your CRM has loading spinners, you’ve already lost.

5. It Scales with Complexity

Early on, person files were simple.

Now they have executive summaries, commercial analysis, collaboration potential, and recommended actions. The system evolved naturally because it’s just... notes.

6. AI Integration is Native

Cursor AI can read and write local markdown files directly.

No export/import steps. Super fast. Secure.

The AI assistant works with the same files I work with. It updates them from Substack, Gumroad, and Kit data in a few seconds. Subscriber details, events, DMs, posts, comments, products, sales, etc.

The Meta-Irony

I built StackContacts (a CRM for Substack creators) to help people track their subscribers.

But I use an Obsidian vault + Cursor AI to sync my subscribers from StackContacts.
The CRM builder uses a note-taking app to remember his customers.

Why? Because:

1. My vault evolves faster - I can add fields/sections instantly

2. Markdown is more flexible - I can embed diagrams, add context, and add bi-directional links freely

3. AI integration is seamless - Cursor AI can read/write markdown natively

4. It’s all local - Super fast updates, complete control, I own all my data on my computer.

What I’ve Learned Running This for 2+ Months

It Forces You to Pay Attention

When you write “Emily left a 5-star review and bought a second product,” you notice that. You don’t just see a conversion metric—you see a person who trusts your work.

Patterns Emerge from Notes

I didn’t plan to identify “fast converters” or “multi-product buyers.” But when you review 10 unique person files, you start seeing patterns. Their DMs, comments, and questions.

Those patterns inform product strategy. And Gumroad upsell messages. And the next piece of content to write.

Context Compounds Over Time

The first interaction is just data. The tenth interaction is a story. The twentieth is a relationship.

Obsidian captures all of it in one place. StackContacts syncs data from the source, and AI updates your markdown files.

It’s Only As Good As Your Discipline

This system requires writing things down. If you don’t care about your interactions with people, you lose the value. But if you do it consistently, the compound interest is massive.

But here’s the thing: with AI automation handling the data gathering, the “discipline” part becomes way easier. I just need to add my human observations to what the AI already compiled.

Who This System Is For (And Who It Isn’t)

This works if you:

• Manage 20 -1,000 human relationships (past that, automation matters more)

• Sell products or services where context matters more than volume

• Value deep relationships over conversion funnels

• Want perfect memory without manual data entry

• Willing to set up the technical foundation once

This doesn’t work if you:

• Need fully automated sales pipelines and email sequences

• Manage tens of thousands of transactional customers

• Want visual dashboards and corporate style reporting

• Need team collaboration (Obsidian is local-first, not cloud-based)

• Don’t have queryable data sources

It’s not for everyone.

But if you’re a solo founder, creator, or consultant trying to remember people better than platforms remember them, this system might change how you work.

Leave a comment

How the System Actually Works (The Technical Part)

Okay, now for the details. If you want to build something like this, here’s what’s under the hood:

Read more

A month ago, I asked AI a simple question:

Summarize how much time I have spent on my DMs over the last 30 days.

The answer: 46.3 hours in DMs. 1,723 messages sent. Active 28 out of 30 days.

That’s a full work week. In my inbox. In one month.

Between Dec 2-5, I sent 811 messages - that’s not writing, that’s operating a customer support department I don’t have.

The Real Problem (And Why Most Creators Don’t See It)

Most creators have no idea how much time they spend responding, reacting, engaging, and generally being a good Substack citizen.

We tell ourselves we’re “just checking in.”
We’re not.
We’re running micro-conversations all day long without realizing it.

The truth: Creators don’t burn out from writing. They burn out from the invisible work around writing.

The only way to get control is to do what any data-loving creator eventually does:

Measure → Analyze → Systemize.

Let me show you exactly how.

How to Analyze Your Substack Usage (So You Don’t Burn Out Like I Almost Did)

Step 1 — Measure What Actually Happens

Not what you think happens. What actually happens.

Ask your Claude AI app something like:

“Summarize how much time I have spent on my DMs over the last 30 days.”

Actual Claude AI app query

You’ll learn:

• How many messages do you send

• How many do you receive

• Active days

• Character counts

• Peak periods

• Time spent

• Outliers (your personal “DM black holes“)

You’re not doing this to judge yourself.
You’re doing it because burnout thrives in vagueness.

The moment you see the number, power returns to your hands.

How to Actually Do This (Two Paths)

You have two options:

Fast (5 minutes, paid): Automated sync with StackContacts

Free (3+ hours, extremely tedious): Manual CSV export for every single DM thread you have.

If you have 50+ DM threads, the free method becomes impractical.
Choose based on your constraint: time or money.

Here’s precisely how to get started:

Path 1: The Automated Method (5 minutes)

What you need:

• Claude AI desktop app (free)

• StackContacts app for MacOS or Windows (one-time fee)

• 5 minutes for initial setup

• Initial Sync (depends on total DM message count - can be scheduled to sync automatically)

How it works:

StackContacts automatically syncs all your Substack DMs (and a lot of other data if needed) and provides an MCP server connected to the Claude app:

✅ Fast analytics queries across all DMs
✅ Top 10 most time-intensive conversations
✅ Weekly trend analysis with spike detection
✅ Peak activity periods by day/hour
✅ Character count and message patterns
✅ Contact-level insights (response time, message frequency)

The difference:

• StackContacts: Fully automated sync, instant insights, no manual work. Free AI credits can produce this report (I tested it).

• Semi-automated method: Great for one-time analysis, requires monthly manual exports, and you may run out of free AI credits quickly

If you’re spending over 20 hours/month in DMs like I was, the automation pays for itself in saved time.

Path 2: The Free Semi-Automated Method (3 hours)

What you need:

• Your Substack DM data in CSV files

• Claude or ChatGPT (free tier works)

• a lot of patience

Step-by-step:

1. Export your DM data from Substack

Unfortunately, Substack does not offer export DM messages capability yet, but there is an easy way to do this using AI:

• Install Microsoft playwright-mcp Chrome extension on your browser

• Use the instructions in the Playwright MCP README file to install the Playwright MCP server configuration with your AI client, like Claude or Cursor.

• Go to your Substack Chat page → Direct - select a thread URL and copy to clipboard

Paste the DM thread URL on your AI Client and ask if it has access:

Do you have access to this web page https://substack.com/chat/<thread_id_redacted>

You can see this message “Playwright MCP Bridge” started debugging this browser:

If the AI confirms it has access, you can ask the AI to do the work:

Read this DM thread https://substack.com/chat/<thread_id_redacted>
1. Create a table that contains the chat thread id, timestamp, sender, and the message for all the messages in this thread 
2. Save the table  in a CSV file

Now repeat this process for each DM thread (I have 230 DM threads) - this is where patience is needed. You may need to adjust timestamps manually, as Claude suggests.

You may find a way to ask Claude AI to do all the heavy lifting for you, but you may also run out of daily free credits quickly 😄.

2. Upload CSV files to Claude or ChatGPT

• Open claude.ai or chatgpt.com

• Start a new conversation

• Upload all the CSV files (you may hit some limits on a free plan, though)

3. Ask the magic question:

Copy this exact prompt:

Analyze my Substack DM activity over the last 30 days using attached CSV files. 
Show me:
   - Total messages sent and received
   - Total estimated time spent (assume ~3 min per message)
   - Active days vs inactive days
   - My top 10 most time-intensive conversations
   - Weekly trends with spikes highlighted
   - Average message length by contact
Present findings in a clear summary with charts if possible.

4. Review the results

• Save the analysis to a doc

• Identify your personal patterns

• Note your “spike weeks”

Time investment: estimated 3 hours each/month to track trends

Cost: $0 (if using free AI tiers, but you may run out of credits)

My recommendation: I built StackContacts because doing this manually is brutal. But if you want proof before buying, export 5-10 of your most active threads and run the analysis on just those. You’ll get a taste of the insights and can decide if you want to scale it up.

Step 2 — Find Your Spikes and Patterns

Every creator has invisible danger zones:

• Launch weeks ( or final beta testing weeks)

• Viral Notes

• When you publish a strong post

• When someone large replies or boosts

• When you respond to everyone at once

• Your personal “flow days.”

Your patterns might explain your fatigue.

Step 3 — Identify High-Value vs. Low-Value Conversations

Not all engagement is equal.

You’ll see:

• Conversations that grow your community

• Conversations that deepen trust

• Conversations that help your readers or help to improve your products

• And conversations that… well… eat half your afternoon

This is where creators get trapped: We distribute our attention evenly rather than strategically.

Step 4 — Expose Your Hidden Time Sinks

Everyone has them.

Common time sinks include:

• Answering the same onboarding questions repeatedly

• Explaining product features one DM at a time

• Unintended back-and-forths that stretch into days

• Copy/pasting invite codes

• “Quick” replies that turn into micro-essays

• Responding instantly instead of batching

These aren’t bad things.
They’re just things you should systemize.

Which brings us to…

Step 5 — Turn Insight Into Systems (Not New Year Resolutions)

Creators don’t need more discipline.
We need better systems.

Try:

• Batching DM time
  e.g., two 15–20 minute sessions per day

• Template replies
  for onboarding, invite codes, discount codes, and common questions

• Redirect rules
  Long conversations → email
  Product questions → a user guide
  Support issues → a Google form or a dedicated support site
  Repeat questions → an FAQ

• Automated calendar invites
  There are many alternative services with a free tier to let customers book a time that works for them. I’ve started testing Calendly recently.

• Micro-CRM tags for your contacts
  Even simple tags like *new***, *customer*, *collab potential*, *and VIP*** allow you to segment your customer base and allocate your time more effectively.

Systems aren’t rigid.
They protect your creative energy.

You’re not removing the human touch.
You’re protecting it.

My Numbers: The Wake-Up Call

When I ran this analysis myself, here’s what AI showed me:

The Numbers Don’t Lie (But They Do Hurt a Little)

• 46.3 hours in DMs.
  A full work week in a month. I could have learned how to speak Chinese.

• 1,723 messages sent
  I’m supposed to be retired. This is crazy.

• Active 28 out of 30 days
  My streak rivaled Duolingo’s owl.

• 267,586 characters written
  Equivalent to a short book. In the last month. Inside my inbox.

And then it kept going. A full breakdown. It even counted my thinking time - scary.

With charts.
With trend lines.
With… rankings.

AI actually ranked the people I spent a whole work week with during the previous month.
My top 10 DM chats, ranked by hours spent (not insights gained), so I was disappointed.
With the average message length exposed.

Contact Messages Sent Estimated Time Avg Message Length 
C.E     158           ~1.7 hours     126 chars 
N.H     170           ~1.5 hours     105 chars 
J.S.R   76            ~1.1 hours     177 chars 
R.G     99            ~51 min        103 chars 
G.W     71            ~50 min        141 chars 
M.B     59            ~47 min        160 chars 
E.C     70            ~41 min        116 chars 
K.B     55            ~41 min        149 chars 
P.P     64            ~39 min        123 chars 
K.K     103           ~39 min        75 chars

Apparently, I have “favorite people” in my DMs, and I didn’t know it until the robot told me. I must admit that December went by way too quickly for me even to notice these patterns.

The Spike That Broke Me

The scariest part wasn’t the totals.
It was the pattern.

Between Dec 2 and Dec 5, I sent 811 messages.

That’s not writing.
That’s not communicating.
That’s… operating a customer support department.

And I don’t have a customer support department.
I am the customer support department.

There was a moment during this analysis — somewhere between “286 messages sent on Dec 3” and “avg. 155 characters per message” — when it hit me:

This is not a side gig.
This is a whole frigging job.

My Weekly Pattern

📈 Weekly Trends Report

Week 1 (Nov 12-18): 168 messages → ~4.1 hours
Week 2 (Nov 19-25): 433 messages → ~13.7 hours ⚠️ Spike
Week 3 (Nov 26-Dec 2): 589 messages → ~11.8 hours
Week 4 (Dec 3-9): 947 messages → ~19.2 hours ⚠️ Peak activity
Week 5 (Dec 10-11): 81 messages → ~2.5 hours

Dec 3–9 was a perfect storm of engagement due to the StackContacts beta launch.
I said yes to everything.
My reward was 19.2 hours of DM time in a single week.

That’s not sustainable.
That’s a slow drip to creative collapse.

For me, these patterns included:

• Answering the same onboarding questions 42 times (yes, I do have a FAQ that could be better)

• Explaining product features one DM at a time, for the 89th time

• Unintended back-and-forths that stretch into days

• Copy/pasting invite codes to my fancy new private chat support system

• “Quick” replies that turn into micro-essays

• Responding instantly instead of batching

My top 10 conversations alone took almost 10 hours.

But they were meaningful.
The other 33 threads?
Not all of them were worth the same energy.

What Changed When I Actually Implemented This System

I ran this analysis for the first time on December 10th.

The numbers terrified me enough to take action immediately.
Here’s what happened over the next 30 days:

Before Systems (Nov 12 - Dec 11):

• ⏰ 46.3 hours total in DMs

• 📊 1,723 messages sent

• 🔥 28 out of 30 days active

• 😰 19.2 hours in my worst week

• 🧠 Mental state: Exhausted, reactive, always “catching up”

After Systems (Dec 12 - Jan 9):

• ⏰ 18.6 hours total in DMs (↓ 59%)

• 📊 428 messages sent (↓ 75%)

• 🔥 18 out of 30 days active (↓ 10 days)

• 😌 6.2 hours in my worst week (↓ 68%)

• 🧠 Mental state: Focused, intentional, creative energy restored

The Systems That Made the Difference

1. Batching DM Time (Saved ~8 hours/week)

Before: Checked DMs constantly throughout the day
After: Two 20-minute DM sessions (8:30 am, 4 pm)

Result:

• Stopped context-switching 15+ times/day

• Responses became more thoughtful (not reactive)

• No loss in relationship quality

2. Template Library (Saved ~5 hours/week)

I created 8 template responses for recurring questions:

• Beta invite code request

• Product feature question

• Onboarding help

• Discount code inquiry

• “How do I get started?”

• Technical troubleshooting

• Collaboration interest

• General thank you

Before: Wrote every response from scratch
After: 60% of messages now use templates (customized slightly)

Result:

• Faster response times (better user experience)

• More consistent messaging

• Less mental energy per response

Here’s my most-used template (feel free to steal):

Hi [Name]! 👋

Thanks for your interest in [Product].
Here’s your beta invite code: [CODE]
Quick Start: Download from [link]
Follow the setup guide: [link]
Join support chat: [link]

Most common questions answered here: [FAQ link]
Let me know if you hit any snags!
— Finn

3. Redirect Rules (Saved ~4 hours/week)

I stopped trying to answer everything in DMs.

New routing system:

• 📧 Complex questions → “Let’s continue this via email: finntropy@...“

• 📚 Product features → “Great question! Full guide here: [link]”

• 🐛 Bug reports → “Please submit here so I don’t lose track: [form]”

• 💬 Long strategy convos → “This deserves a proper chat. Book 15 min: [cal link]”

Result:

• Better answers (right medium for the question)

• Nothing gets lost

• DMs stay short and actionable

4. The “24-Hour Rule” (Saved sanity)

New personal policy:

I don’t respond to non-urgent DMs within 24 hours.

Why this works:

• Kills the expectation of instant replies

• Let me batch responses thoughtfully

• Readers still get great answers (just not instantly)

• No one has complained

Exception: Paying customers or critical bugs get same-day responses.

The Surprising Benefits

Beyond just time savings, the systems created:

• Better relationships: Ironically, batching DMs made my responses more thoughtful. People noticed.

• Clearer boundaries: Readers respect the structure. No one expects instant replies.

• More creative energy: The 27.7 hours I reclaimed went into writing, building, and actual rest.

• Higher quality conversations: By routing complex topics to email or calls, we have deeper discussions.

The One Thing I Wish I’d Known Earlier

Systems don’t make you less human.

Systems protect your humanity.

When you’re drowning in 46 hours of DMs, you:

• Rush responses

• Copy-paste generic answers

• Miss important signals

• Resent the work

When you have systems, you:

• Give thoughtful responses

• Have energy for the critical conversations

• Notice patterns and opportunities

• Actually enjoy the community

The goal isn’t to ignore your readers.
The goal is to serve them better with less chaos.

Why This Matters

Creators don’t burn out from writing.

They burn out from:

• the friction around writing

• the endless context switching

• the invisible energy leaks

• the belief that “ we have to do everything ourselves.“

Substack is meant to be joyful.
Human.
Sustainable.

Your DMs should enhance your creativity - not quietly consume it.

When you understand your patterns, you can design your days around:

• deeper focus

• better boundaries

• higher-quality connections

• and actual rest

That’s how you grow without breaking.

The Bottom Line: Measure, Systemize, Protect

Here’s what I learned from analyzing 46.3 hours of DM time:

Most creators aren’t burning out from writing.

They’re burning out from the invisible work around writing.

The DMs.
The context switching.
The belief that “being available” means “being always on.”

It doesn’t.

You can be:

• Highly responsive AND have boundaries

• Community-focused AND protect your creative time

• Human AND systematic

The data permits you to build systems.

Your Next Steps

This week:

1. Run the DM analysis (use the prompt above)

2. Identify your personal “spike weeks.”

3. Create one template response for your most common question

This month:

1. Implement batching (two 20-minute DM sessions/day)

2. Build your redirect rules (email, forms, guides)

3. Track your time savings

This quarter:

1. Refine your systems based on what works

2. Measure the impact on your creative output

3. Decide if automation makes sense for your volume

Want This Automated?

If you discovered you’re spending 15+ hours/month in DMs (like most active Substack creators), manually tracking this becomes tedious.

That’s why I built StackContacts.

It includes Substack DM data that you can ask questions with AI:

• Time spent per conversation

• Weekly trends and spikes

• Your top 10 most time-intensive DMs

• Peak activity patterns

• Template usage and response times

All data synced as you use Substack.

The goal: Spend less time analyzing, more time creating.

Current status: StackContacts for Mac and Windows is available
(macOS with Apple silicon M series CPU only)
Setup time: 5 minutes

→ Get the StackContacts app

If you prefer the manual method, that’s great too. The important thing is to measure and protect your creative energy.

— Finn

P.S. If you run this analysis and discover something surprising, I’d love to hear what patterns you find. Reply or leave a comment.

Leave a comment

PS. I’m Still Below The Average Joe

Over the last 30 days, I spent 10.26 hours per week on Substack, which is about 1.46 hours per day. According to the Internet, people spend 2.4 hours per day on social media.
I’m trying to keep my social media usage below the average and focus on bringing value to my readers.

Read more

Four days after launching this newsletter on Christmas Eve, something happened that changed everything.

I got my first annual paid subscriber.

But that’s not the only thing that’s been happening. I’ve been heads-down building features you asked for, fixing bugs you found, and making StackContacts work better for you every day.

This update covers:

• Automatic Sync - Your subscriber data stays fresh without you lifting a finger

• Churn Tracking - Get your churn metrics and analyze patterns for unsubscribes

• Two bug fixes - Events and DMs data collection fixed

• A milestone - First annual subscriber in 4 days

• An announcement - Office hours starting January 7, 2026

Let me walk you through what’s new and what’s next.

Feature #1: Automatic Sync

What It Is

StackContacts now syncs your subscriber summary data automatically in the background. No more remembering to click the sync button. No more stale data when you need it most. This feature is available from version 0.1.24 onwards.

Here’s how it works: When StackContacts is running, it periodically checks for new subscriber summary data from Substack. If there are new subscribers or churned ones, it syncs them automatically. You don’t have to do anything. Just leave the StackContacts app running in the background.

Before vs. After

Before:

• Open the StackContacts app

• Click the Sync button

• Wait for it to complete

• Remember to do this regularly

• What happens when you forget? Stale data, missed insights

After:

• StackContacts runs in the background

• Data syncs automatically on a daily or weekly schedule

• Your database stays fresh without manual intervention

• Wake up to updated data every morning or on Monday at 9:05 AM

How to Use It

Automatic sync is enabled by clicking the “Enable scheduled sync” checkbox. Select the Frequency (Daily or Weekly), and set the time (it uses your local time from the browser).

You need to run it at least once manually using “Run Sync now” to add the credentials to an internal database that tracks the scheduled jobs. Your selected publication is also stored there.

The UI shows the Next run, and if you want to stop automatic Sync, click the “Disable” button.

Just keep StackContacts running (it can run in the background), and your Subscriber Summary data will stay up to date.

If you want to verify it’s working, check the sync status in the app. You’ll see when the last sync completed and when the next one is scheduled.

Here are the Weekly Frequency options. You can select the Day of Week - this helps you to get fresh data before your weekly review, for example.

Why This Matters

Fresh data means better insights. When your churn tracking analysis runs, it uses the latest subscriber data. When you’re looking for revenue attribution, you’re seeing the most recent sales. Automatic sync ensures you’re always working with current subscriber information.

This also sets the foundation for future features that need real-time data - like alerts when high-value subscribers disengage, or notifications when specific content drives sales.

We start this feature with Subscriber Summary data because it is critical to capturing churned subscribers and to adding other data sources in the future roadmap.

Feature #2: Subscriber Churn Tracking

What It Is

Churn tracking identifies subscribers who have left your publication by comparing historical snapshots of your subscriber base. It automatically creates a dated snapshot of all current subscribers every time you sync, building a historical record that lets you identify exactly when subscribers churn.

The feature uses two tables: subscribers_snapshots (append-only historical records) and subscriber_events (an engagement timeline with a merge mode to prevent duplicates). You can query these tables to analyze churn patterns and review engagement history for churned subscribers.

The Problem It Solves

Substack only shows you unsubscribes after they happen. By then, it’s too late—the subscriber is gone, and you’ve lost the opportunity to re-engage them or understand why they left.

With churn tracking, you can:

• See exactly when subscribers churned by comparing snapshots across dates

• Analyze engagement patterns for churned subscribers to identify warning signs

• Track churn trends over time to spot patterns

• Review the complete event history of churned subscribers (emails opened, posts seen, etc.)

How It Works

StackContacts maintains two tables to track subscribers:

1. subscribers_summary — Current subscribers (updated on each sync using merge mode)

2. subscribers_snapshots — Historical snapshots (append-only, never deleted)

Automatic snapshot creation:

When you sync subscriber data (Subscriber Summary checked), the app automatically:

• Updates subscribers_summary with your current subscriber list

• Creates a new snapshot in subscribers_snapshots with today’s date (YYYY-MM-DD format)

• Each snapshot record includes a snapshot_date field plus all subscriber data from that day

Key implementation details:

• Primary key: ["snapshot_date", "subscription_id"] ensures one record per subscriber per date

• Write mode: Append-only (write_disposition="append") preserves all historical snapshots

• Automatic: Snapshots are created automatically whenever you sync subscriber summary data

• Event tracking: The subscriber_events table uses merge mode to prevent duplicate events so that you can analyze engagement history for churned subscribers without table bloat

Finding churned subscribers:

Compare snapshots from different dates using SQL queries. A subscriber who appears in an older snapshot but not in a newer one has churned between those dates.

What You Can Do With It

• Identify churn dates: Compare any two snapshot dates to see who churned in that period

• Analyze churn patterns: Track churn rates over time, by subscription tier, or by other attributes

• Review engagement history: Query subscriber_events for churned subscribers to see their last activities (emails opened, posts seen, etc.)

• Build retention reports: Calculate retention rates between snapshot dates

• Segment analysis: Compare churn rates across different subscriber segments

Example Use Case

Scenario: You want to find subscribers who were active on December 15th but churned by December 22nd, and see what their engagement looked like before they left.

Step 1: Identify churned subscribers

-- Find subscribers who were active on Dec 15 but churned by Dec 22
USE pub_finntropy_substack_com; 

SELECT 
    old.subscription_id,
    old.user_email_address,
    old.subscription_interval,
    old.subscription_created_at
FROM subscribers_snapshots old
WHERE old.snapshot_date = '2025-12-15'
  AND NOT EXISTS (
    SELECT 1 FROM subscribers_snapshots new
    WHERE new.subscription_id = old.subscription_id
      AND new.snapshot_date = '2025-12-22'
  )
-- See the last 10 events for each churned subscriber
SELECT 
    se.subscriber_email,
    se.text AS event_type,
    se.timestamp,
    se.post_title
FROM subscriber_events se
WHERE se.subscriber_email IN (
    -- Subquery from Step 1
    SELECT old.user_email_address
    FROM subscribers_snapshots old
    WHERE old.snapshot_date = '2025-12-15'
      AND NOT EXISTS (
        SELECT 1 FROM subscribers_snapshots new
        WHERE new.subscription_id = old.subscription_id
          AND new.snapshot_date = '2025-12-22'
      )
)
ORDER BY se.subscriber_email, se.timestamp DESC
LIMIT 10

This shows you exactly when subscribers churned and what their engagement looked like before they left, giving you insights to prevent future churn.

Bug Fixes: What We Fixed

Bug #1: Duplicate Subscriber Events Causing Database Bloat

What the bug was: Every time you synced subscriber events (like “Received email”, “Opened email”, “Post seen”), the app was adding all events to the database again, even if those events were already stored from a previous sync. This created duplicate rows for the same events.

How it affected users:

• Your database grew much faster than it should have (we saw cases where the events table tripled in size)

• Queries became slower as the table filled with duplicate data

• Storage space was wasted on redundant information

• It made it harder to analyze engagement patterns accurately

What we fixed: We changed the events table to use “merge” mode instead of “append” mode. Now, when the same event is synced again, it updates the existing record instead of creating a duplicate. Each event is uniquely identified by its timestamp, subscriber email, and event type, preventing duplicates.

How to verify it’s fixed: After syncing subscriber events multiple times, check your subscriber_events table. You should see approximately the same number of unique events even after numerous syncs, rather than the count growing rapidly each time. The table size should grow only at the rate of new events, not continuously multiplying in size.

Bug #2: Duplicate Direct Messages Causing Database Bloat

What the bug was: Similar to the events bug, every time you synced direct messages (DMs), the app was adding all messages to the database again, even if those messages were already stored from a previous sync. This created duplicate rows for the same messages.

How it affected users:

• Your database grew unnecessarily large with duplicate message records

• Queries on DM data became slower

• Storage space was wasted

• Made it challenging to track message history and conversations accurately

What we fixed: We changed the DM messages table to use “merge” mode instead of “append” mode. Now, when the same message is synced again, it updates the existing record instead of creating a duplicate. Each message is uniquely identified by its message ID (either from the API or generated from thread ID, timestamp, sender, and content).

How to verify it’s fixed: After syncing DM messages multiple times, check your dm_messages table. You should see roughly the same number of unique messages even after numerous syncs, rather than the count growing rapidly each time. The table size should grow only at the rate of new DMs, not continuously multiplying in size.

The Process

These bugs were found through my beta testing program. I’m grateful for everyone who took the time to report issues - it’s the only way StackContacts gets better.

Fixing bugs quickly matters. When something breaks, it breaks your workflow. That’s why I prioritize bug fixes and get them resolved as soon as possible.

If you find something that doesn’t work right, please report it. Your feedback directly shapes what gets fixed next.

Milestone: First Annual Paid Subscriber

The Story

Four days after launching this newsletter, I got my first annual paid subscriber.

That’s not just a number. That’s someone who saw value in what I’m building and committed to supporting it for a full year. That’s trust. That’s validation that this newsletter is worth paying for.

The Context

I launched this newsletter on Christmas Eve with a simple goal: help StackContacts users extract maximum value from their data. I didn’t know if anyone would pay for it. I didn’t know if the content would resonate.

Four days later, I had my answer.

Gratitude

To that first annual subscriber: Thank you!

Your support means more than you know. You’re not just a subscriber - you’re a co-builder. Your feedback, your questions, and your engagement shape what this newsletter becomes.

And to all the early supporters who signed up in those first days: Thank you for taking a chance on something new. You’re the foundation this community is built on.

What’s Next

This milestone isn’t an endpoint - it’s a starting point. It’s proof that there’s demand for deep, practical content about data-driven growth. It’s motivation to keep building, keep writing, keep helping creators see what their data is really telling them.

I’m committed to making this newsletter worth every dollar you invest in it. That means more weekly posts, more AI prompts, more case studies, and more ways to turn your StackContacts data into actionable insights.

If you’re reading this and haven’t upgraded yet, I’d love to have you join. You’ll get access to all the weekly analysis posts, the full AI prompt library, and direct support when you need it.

Announcement: Office Hours Starting Jan 7, 2026

What Are Office Hours

Starting January 7, 2026, I’ll be hosting weekly office hours for paid StackContacts subscribers. These are live calls where you can bring your questions, your data, and your challenges - and we’ll work through them together.

Read more

You’re running a query in Claude. You want to export your most engaged subscribers for an outreach campaign. You have 23,000 active readers.

The AI returns 487 rows and says “results truncated.”

You try again. Maybe 503 rows this time. Still truncated.

The data is definitely there—you can see the counts, query it, and analyze it. But you can’t get it out in a usable format.

This isn’t a bug. It’s not a StackContacts limitation. Your database has no problem with 3,000 rows or 300,000.

You’re hitting the ceiling of the conversation layer.

And if you’re doing any serious outreach, enrichment, or cross-platform analysis, this limitation will cost you time, opportunities, and money.

Let me show you what’s actually happening—and then how to route around it completely.

Why Exports Cap Around ~500 Rows

Here’s the confusion: The data is there, so why can’t I export it?

The answer is in the architecture.

StackContacts uses something called an MCP (Model Context Protocol) server. It sits between the AI and your database:

AI (Claude / Cursor)
        ↓
MCP Server (context-limited)
        ↓
Database (no row limits)

When you ask Claude to query your data, the MCP server fetches the results and passes them back through the AI’s context window.

Here’s the problem: AI models have a finite context window. They can only hold so much text at once. Large result sets—especially with many columns—quickly exceed that limit.

When that happens:

• The MCP server truncates results

• The AI tells you “results limited to 500 rows”

• Your export is incomplete

Important: This is not a database limit. Your database has no 500-row cap. The data isn’t missing. You’re just trying to pass too much information through a channel that was designed for conversation, not bulk data transfer.

Think of MCP as a reading window, not the filing cabinet.

MCP is brilliant for exploration, pattern recognition, and ad-hoc analysis. It’s what makes talking to your database feel natural.

But when you need to extract large datasets—for outreach tools, CRM imports, or enrichment pipelines—you’re using the wrong tool for the job.

The Mental Model: Two Layers, Two Use Cases

Understanding this architecture changes how you work with StackContacts.

Layer 1: The MCP Server (Exploration)

• Great for: “Show me my top posts by engagement.”

• Great for: “Which subscribers opened in the last week?”

• Great for: Summarization, pattern finding, quick samples

• Limited by: AI context window (~500-1000 rows depending on columns)

Layer 2: The Database (Extraction)

• Great for: “Export all active subscribers with >3 opens”

• Great for: Bulk operations, outreach lists, data migrations

• Great for: Feeding other systems (CRM, email tools, enrichment APIs)

• Limited by: Your disk space (effectively unlimited)

Most StackContacts users only use Layer 1 because it’s easy and conversational.

But if you want to do anything at scale—outreach, partnerships, enrichment, cross-platform matching—you need Layer 2.

The Solution: Step Outside the Conversation Layer

Here’s the high-level workflow:

1. Use AI to design the query (still valuable!)

2. Run the query directly against the database (bypassing MCP)

3. Export results to CSV (outside the AI context window)

4. Use the file however you want (outreach, CRM, analysis tools)

This works because:

• SQL runs where the data lives (no data transfer limits)

• CSV export happens outside the AI context

• You get complete, untruncated results

• No row limits except disk space

The AI is still useful—it helps you write the query. But you don’t ask it to return 3,000 rows. You ask it to generate SQL, then you run that SQL directly.

What This Unlocks

Once you have this workflow, you can:

• Export your full subscriber list for outreach tools

• Pull high-intent leads for manual follow-up

• Create segmented lists for different campaigns

• Feed enrichment APIs with complete contact data

• Build source-of-truth CSVs for cross-platform analysis

• Archive snapshots for historical comparison

No truncation. No missing data. No “results limited” warnings.

You separate exploration (AI + MCP) from extraction (SQL + CSV).

And suddenly StackContacts stops feeling like a conversation tool with limits—and starts feeling like infrastructure.

Below 👇 are the details on how you unlock your next StackContacts superpower.

Critical First Step: Finding Your Schema Name

Before running any queries, you need to know your schema name. StackContacts creates a separate schema for each publication, based on your publication name.

What’s a Schema?

Think of schemas as folders in your database. Instead of:

SELECT * FROM subscribers_detail

You’ll actually query:

SELECT * FROM pub_yourname_substack_com.subscribers_detail

StackContacts uses the pattern: pub_<your-publication-slug>

How to Find Your Schema Name

Option 1: Ask Claude (Easiest)

Simply ask through the MCP connection:

“What schemas exist in my StackContacts database?”

Or:

“Show me the list of tables in my database”

Claude will respond with something like:

pub_finntropy_substack_com.subscribers_detail
pub_finntropy_substack_com.substack_posts
pub_finntropy_substack_com.gumroad_sales
...

The prefix (pub_finntropy_substack_com in this example) is your schema name.

Option 2: Query Directly

If you’re using Python or the DuckDB CLI:

import duckdb
import os

db_path = os.path.expanduser("~/Library/Application Support/StackContacts/app_db.duckdb")
con = duckdb.connect(db_path, read_only=True)

# List all publication schemas
schemas = con.execute("SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'pub_%'").fetchall()
print("Your publication schemas:")
for schema in schemas:
    print(f"  - {schema[0]}")

# Or list all tables with their schemas
tables = con.execute("""
    SELECT table_schema, table_name 
    FROM information_schema.tables 
    WHERE table_schema LIKE 'pub_%' 
    ORDER BY table_schema, table_name
""").fetchall()
print("\nAvailable tables:")
for schema, table in tables[:10]:  # Show first 10
    print(f"  {schema}.{table}")

Option 3: Derive from Your Substack URL

Your schema name follows this pattern:

• Substack URL: yourname.substack.com

• Schema name: pub_yourname_substack_com

Examples:

• finntropy.substack.com → pub_finntropy_substack_com

• my-newsletter.substack.com → pub_my_newsletter_substack_com (hyphens become underscores)

• Custom domain users may also have schemas like pub_www_yourdomain_com

Using Your Schema in Queries

Once you know your schema, you have two options:

Option 1: Set the default schema (Simplest)

Set your schema once at the start of your session, then use simple table names:

-- Set your default schema
SET schema 'pub_finntropy_substack_com';

-- Now you can query without prefixes
SELECT * FROM subscribers_detail;
SELECT * FROM gumroad_sales;

Option 2: Use fully-qualified table names

Prefix every table with the schema name:

-- No SET schema needed, but more typing
SELECT * FROM pub_finntropy_substack_com.subscribers_detail;
SELECT * FROM pub_finntropy_substack_com.gumroad_sales;

Recommendation: Use SET schema at the beginning of your session or script. It’s cleaner and all the examples in this article will work without modification.

Pro tip: If you have multiple publications synced to StackContacts, you’ll have multiple pub_* schemas. Use SET schema to switch between them easily.

Step-by-Step: Generating the Right SQL with AI

You still want the AI to help you write queries—SQL is precise, and Claude knows your schema. Here’s how to prompt it effectively.

Prompt Template

Use this format when asking Claude to generate export queries:

Generate a SQL query for StackContacts that exports all active subscribers where:
- membership_state IN ('free_signup', 'subscribed') (includes free and paid subscribers)
- email_disabled = false (actively receiving emails)
- last_opened_at is within the last 90 days
- Include these columns: email, name, source, num_emails_opened, last_opened_at, membership_state
- Order by num_emails_opened descending
- Assume I've already set my schema with "SET schema 'pub_...';"
- Format it for direct execution against DuckDB

IMPORTANT: Do not execute this query. Just show me the SQL.

Why this works:

• You explicitly say “do not execute” (prevents MCP truncation)

• You mention the schema is already set (Claude will use simple table names)

• You specify exact columns that exist in subscribers_detail table

• You filter early (reduces noise in your export)

Important field clarifications:

• is_subscribed = true means PAID subscriber (not “subscribed to emails”)

• is_subscribed = false means FREE subscriber

• Use membership_state to filter by subscriber type:

  • 'free_signup' = free subscribers receiving emails

  • 'subscribed' = paid subscribers

• Use email_disabled = false to get only active email recipients

Common field names in StackContacts:

• Identifiers: email, name, user_id

• Status: membership_state, email_disabled, is_paused, is_paying_regular_member

• Dates: last_opened_at, last_clicked_at, subscription_created_at, last_subscribed_at

• Engagement: num_emails_opened, num_email_opens, links_clicked, num_web_post_views, num_comments

• Revenue: is_paying_regular_member, amount_paid, total_revenue_generated

• Attribution: source, free_attribution, referring_pub_id

If you want fully-qualified names instead:
Add this line to your prompt: “Include the full schema prefix in table names (pub_.)”

Key Tips

Be explicit about columns: Don’t use SELECT *. Specify exactly what you need. This keeps your CSV clean and reduces file size.

Filter early: Apply WHERE clauses to narrow results before export. Don’t export 30,000 rows and filter in Excel. Filter in SQL.

Sanity-check the WHERE clause: Make sure the conditions make sense. Common mistakes:

• Excluding subscribers you actually want

• Using the wrong date columns

• Forgetting to handle NULL values

Example queries you might generate:

Note: These examples assume you’ve set your schema first with SET schema 'pub_<yourname>_substack_com';

-- Set your schema once
SET schema 'pub_finntropy_substack_com';

-- All active subscribers (free and paid) who opened recently
SELECT 
  email, 
  name, 
  source, 
  num_emails_opened,
  last_opened_at,
  membership_state
FROM subscribers_detail
WHERE membership_state IN ('free_signup', 'subscribed')
  AND email_disabled = false
  AND last_opened_at > NOW() - INTERVAL '90 days'
ORDER BY num_emails_opened DESC;

-- Free subscribers with high engagement (potential upgrade candidates)
SELECT 
  email, 
  name, 
  num_web_post_views, 
  num_comments,
  links_clicked,
  num_emails_opened
FROM subscribers_detail
WHERE membership_state = 'free_signup'
  AND email_disabled = false
  AND num_web_post_views > 5
ORDER BY num_web_post_views DESC;

-- High-engagement subscribers for partnership outreach
SELECT 
  email, 
  name, 
  num_emails_opened,
  num_web_post_views,
  last_opened_at,
  activity_rating
FROM subscribers_detail
WHERE membership_state IN ('free_signup', 'subscribed')
  AND email_disabled = false
  AND num_emails_opened > 10
  AND last_opened_at > NOW() - INTERVAL '30 days'
ORDER BY activity_rating DESC;

Remember: Just run SET schema 'pub_<yourname>_substack_com'; once, then all your queries use simple table names.

Once you have the SQL, you’re ready to run it.

Running the Query Against the Database

StackContacts uses a local DuckDB database—a single file stored on your machine. This makes direct access straightforward: no server setup, no authentication, just point a tool at the file.

Locating Your StackContacts Database File

Your DuckDB database is at:

/Users/<your-username>/Library/Application Support/StackContacts/app_db.duckdb

Replace <your-username> with your actual Mac username. To find your username, open Terminal and run:

echo $HOME

Or ask Claude: “Where is my StackContacts database file located?” — the MCP server knows the exact path.

If you open your StackContacts application, the Data tab / Database Management shows you the database location, see screenshot below:

Install DuckDB CLI (One Command)

Open Terminal and run this single command:

curl -L https://github.com/duckdb/duckdb/releases/download/v1.4.3/duckdb_cli-osx-universal.zip -o /tmp/duckdb.zip && unzip -o /tmp/duckdb.zip -d /tmp && sudo mv /tmp/duckdb /usr/local/bin/ && rm /tmp/duckdb.zip && echo "DuckDB installed successfully!"

What this does:

• Downloads DuckDB CLI

• Unzips it

• Moves it to a folder in your system path

• Cleans up temporary files

• You’ll be asked for your Mac password (this is normal for sudo)

To verify it worked:

duckdb --version

You should see something like v1.4.3.

Find Your Schema Name (Required First Step)

Before running any exports, you need to know your publication’s schema name. Open Terminal and run:

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'pub_%'"

You’ll see output like this (if you have multiple publications):

┌────────────────────────────────────────┐
│              schema_name               │
│                varchar                 │
├────────────────────────────────────────┤
│ pub_finntropy_substack_com             │
│ pub_finntropy_substack_com_staging     │
│ pub_stackcontacts_substack_com         │
│ pub_stackcontacts_substack_com_staging │
│ pub_www_finnsights_com                 │
│ pub_www_finnsights_com_staging         │
└────────────────────────────────────────┘

Copy this schema name — pub_finntropy_substack_com - you’ll use it in all your export commands below. If you are using a Substack custom domain, you would look for something like pub_www_finnsights_com

Export Your Data (Copy-Paste Ready Commands)

Replace pub_YOUR_SCHEMA_HERE with your actual schema name from above.

Export all active subscribers (free and paid):

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; COPY (SELECT email, name, source, num_emails_opened, last_opened_at, membership_state FROM subscribers_detail WHERE membership_state IN ('free_signup', 'subscribed') AND email_disabled = false ORDER BY num_emails_opened DESC) TO '$HOME/Downloads/active_subscribers.csv' WITH (HEADER true)"

Export subscribers who opened recently (last 90 days):

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; COPY (SELECT email, name, num_emails_opened, last_opened_at FROM subscribers_detail WHERE membership_state IN ('free_signup', 'subscribed') AND email_disabled = false AND last_opened_at > NOW() - INTERVAL '90 days' ORDER BY num_emails_opened DESC) TO '$HOME/Downloads/recent_openers.csv' WITH (HEADER true)"

Export high-engagement subscribers (10+ opens, active last 30 days):

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; COPY (SELECT email, name, num_emails_opened, num_web_post_views, last_opened_at FROM subscribers_detail WHERE membership_state IN ('free_signup', 'subscribed') AND email_disabled = false AND num_emails_opened > 10 AND last_opened_at > NOW() - INTERVAL '30 days' ORDER BY num_emails_opened DESC) TO '$HOME/Downloads/high_engagement.csv' WITH (HEADER true)"

Export free subscribers with high engagement (potential upgrade candidates):

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; COPY (SELECT email, name, num_web_post_views, num_comments, links_clicked FROM subscribers_detail WHERE membership_state = 'free_signup' AND email_disabled = false AND num_web_post_views > 5 ORDER BY num_web_post_views DESC) TO '$HOME/Downloads/potential_upgrades.csv' WITH (HEADER true)"

Your CSV files will be saved to your Downloads folder.

Test Before Exporting (Optional but Recommended)

Want to see a preview before exporting? Run this to see the first 10 rows:

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; SELECT email, name, num_emails_opened, membership_state FROM subscribers_detail WHERE membership_state IN ('free_signup', 'subscribed') AND email_disabled = false LIMIT 10"

Check the count of rows you’ll export:

duckdb "$HOME/Library/Application Support/StackContacts/app_db.duckdb" "SET schema 'pub_YOUR_SCHEMA_HERE'; SELECT COUNT(*) FROM subscribers_detail WHERE membership_state IN ('free_signup', 'subscribed') AND email_disabled = false"

Exporting to CSV (The Clean Way)

All the export commands above will create CSV files in your Downloads folder. The files will have headers (column names) and be formatted for easy import into Excel, Google Sheets, or any other tool.

What You Get

Each CSV export includes:

• Headers - First row has column names

• Clean data - Properly formatted for spreadsheets

• No row limits - Complete, untruncated results

Common Fields You Can Export

When creating your own export commands, here are the most useful fields:

Basic Info:

• email - Email address

• name - Subscriber name

• source - Where they signed up

Engagement:

• num_emails_opened - Total emails opened

• num_email_opens - Total open events (can open same email multiple times)

• num_web_post_views - Web post views

• num_comments - Comments left

• links_clicked - Links clicked

Status:

• membership_state - Subscriber type: 'free_signup' (free) or 'subscribed' (paid)

• email_disabled - Whether emails are disabled (true/false)

• is_paused - Subscription paused (true/false)

• is_paying_regular_member - Paying subscriber (true/false)

Important: is_subscribed = true means PAID subscriber, not “receiving emails”. Use membership_state = 'free_signup' for free subscribers and email_disabled = false for active email recipients.

Dates:

• last_opened_at - Last email open date

• last_clicked_at - Last link click date

• subscription_created_at - When they subscribed

Creating Your Own Export

To modify the exports above:

1. Change the filename: Replace the part after TO

TO '$HOME/Downloads/MY_CUSTOM_NAME.csv'

1. Change the columns: Edit the SELECT clause

SELECT email, name, num_emails_opened

1. Change the filters: Edit the WHERE clause

WHERE is_subscribed = true AND num_emails_opened > 5

1. Change the sorting: Edit the ORDER BY clause

ORDER BY num_emails_opened DESC

Troubleshooting

Error: “command not found: duckdb”

• The installation didn’t complete. Try running the install command again.

Error: “No such file or directory”

• Your database path is different. Ask Claude: “Where is my StackContacts database?”

Error: “Schema does not exist”

• You didn’t replace pub_YOUR_SCHEMA_HERE with your actual schema name

• Run the “Find Your Schema Name” command above to get it

Empty CSV file

• Your WHERE clause filtered out all rows

• Try running the “Check count” command to see how many rows match

Common Pitfalls (and How to Avoid Them)

Pitfall 1: Pulling Everything Instead of Intent-Driven Subsets

The mistake: Running SELECT * FROM subscribers_detail because you want “all the data.”

Why it’s bad:

• Huge file with columns you don’t need

• Harder to process and analyze

• Often includes sensitive fields you shouldn’t export

Fix: Always start with the use case.

• Why am I exporting this?

• What will I do with it?

• Which columns do I actually need?

Then write your query accordingly.

Pitfall 2: Forgetting to Exclude Churned/Unsubscribed Users

The mistake: Exporting subscribers without checking their status.

Why it’s bad:

• You might contact people who unsubscribed (bad for deliverability and reputation)

• You waste enrichment credits on dead leads

• Your outreach list is polluted

Fix: Always filter on subscription status and email preferences:

-- For all active email recipients (free and paid)
WHERE membership_state IN ('free_signup', 'subscribed')
  AND email_disabled = false

-- For only free subscribers receiving emails
WHERE membership_state = 'free_signup'
  AND email_disabled = false

Pitfall 3: Exporting PII Without a Plan

The mistake: Creating CSV files with full names, emails, and engagement data, then leaving them in Downloads.

Why it’s bad:

• Privacy risk

• Compliance issues (GDPR, CCPA)

• Data leak potential

Fix:

• Only export what you need

• Store exports securely (encrypted folders, password-protected)

• Delete exports when you’re done with them

• Never commit CSVs to public repos

Pitfall 4: Re-Running Massive Queries Unnecessarily

The mistake: Running a 10,000-row export every time you want to check one thing.

Why it’s bad:

• Slow

• Wastes database resources

• Creates unnecessary files

Fix:

• Use LIMIT during testing

• Save your verified exports and reuse them

• Use AI + MCP for quick spot-checks, SQL + CSV for bulk operations

When to Use MCP vs. Direct DB Access

You now have two tools. Here’s when to use each.

Use MCP (AI + Conversational Queries) When:

• Exploring patterns: “What are my top posts by engagement?”

• Asking open-ended questions: “What’s interesting about my subscribers?”

• Pulling small, focused samples: “Show me 10 subscribers who opened recently”

• Iterating quickly: You’re not sure what you’re looking for yet

• Summarizing and analyzing: The AI provides insights, not just data

Example: “Which of my Notes performed best last month?”

Use Direct SQL + CSV Export When:

• Doing outreach: You need a clean list for email tools, CRM, or manual follow-up

• Feeding another system: Enrichment APIs, analytics platforms, partner dashboards

• Creating a source-of-truth dataset: Archival snapshots, cross-platform joins

• You care about completeness: Missing 200 rows because of truncation isn’t acceptable

• Repeatability matters: You’ll run this export weekly or monthly

Example: “Export all active subscribers with >5 post views for a partnership outreach campaign.”

Turning This Into a Repeatable System

Once you’ve done this once, make it systematic.

Suggested Export Cadence

Weekly:

• High-intent leads for outreach

• New subscribers to welcome/segment

• Active but non-converting subscribers

Monthly:

• Full active subscriber snapshot

• Engagement metrics for reporting

• Churn risk analysis exports

Quarterly:

• Complete historical archive

• Deep segmentation analysis

• Cross-platform matching (Substack + Gumroad + Twitter, etc.)

Save Your SQL Queries

Create a queries/ folder and save your verified SQL:

queries/
  export_active_subscribers.sql
  export_highintent_leads.sql
  export_monthly_snapshot.sql
  export_to_csv.sh  # Shell script wrapper

Each file contains the full query, ready to run. Add comments for context:

-- High-Intent Leads Export
-- Purpose: Free subscribers with high web engagement, active recently (potential upgrades)
-- Use case: Manual outreach, upgrade campaigns
-- Expected rows: ~200-500 depending on publication size

-- Set your schema first
SET schema 'pub_finntropy_substack_com';

-- Then run the query with simple table names
SELECT 
  email, 
  name, 
  num_web_post_views,
  num_comments,
  last_opened_at,
  activity_rating
FROM subscribers_detail
WHERE membership_state = 'free_signup'
  AND email_disabled = false
  AND num_web_post_views > 5
  AND last_opened_at > NOW() - INTERVAL '30 days'
ORDER BY activity_rating DESC;

Shell script wrapper for automation:

#!/bin/bash
# export_to_csv.sh

DB_PATH="$HOME/Library/Application Support/StackContacts/app_db.duckdb"
EXPORT_DIR="$HOME/stackcontacts_exports"
DATE=$(date +%Y-%m-%d)

mkdir -p "$EXPORT_DIR"

python3 << EOF
import duckdb
import os

con = duckdb.connect("$DB_PATH", read_only=True)

# Read query from file
with open('queries/export_active_subscribers.sql', 'r') as f:
    query = f.read()

# Export to CSV
con.execute(f"""
  COPY ({query})
  TO '$EXPORT_DIR/active_subscribers_$DATE.csv'
  WITH (HEADER true)
""")

print(f"Export complete: $EXPORT_DIR/active_subscribers_$DATE.csv")
EOF

Now running exports takes 30 seconds, not 30 minutes.

Optional Upgrades (Future Roadmap Ideas)

As you get comfortable with this workflow, you might want:

• Scheduled exports: Cron jobs that run your export scripts daily/weekly

# Add to crontab: Run every Monday at 9am
0 9 * * 1 /path/to/export_to_csv.sh

• Export pipelines: DuckDB → CSV → enrichment API → CRM, fully automated

• Custom dashboards: Tools like Metabase or Superset that query DuckDB directly

• Alerting: Watch your database for conditions and notify you

# Check for high-intent subscribers daily
import duckdb, os
con = duckdb.connect(os.path.expanduser("~/Library/Application Support/StackContacts/app_db.duckdb"), read_only=True)

# Set your schema (replace with yours)
con.execute("SET schema 'pub_finntropy_substack_com'")

# Query with simple table name and actual fields
count = con.execute("SELECT COUNT(*) FROM subscribers_detail WHERE num_web_post_views > 10 AND last_opened_at > NOW() - INTERVAL '7 days'").fetchone()[0]
if count > 50:
    print(f"Alert: {count} high-intent subscribers!")

These are all possible because you have direct access to the database file.

Power Users Don’t Fight Limits—They Route Around Them

MCP limits aren’t a bug. They’re a design decision optimized for conversation, not extraction.

Once you understand that, the path forward is obvious:

• Use MCP for exploration and pattern recognition

• Use SQL for extraction and bulk operations

• Keep them separate, and both work beautifully

The 500-row wall isn’t a wall. It’s a sign you’ve graduated to power user territory.

Most creators will stay in Layer 1 (MCP) because it’s comfortable. But if you’re doing serious outreach, segmentation, or cross-platform work, Layer 2 (direct SQL) is where the leverage is.

StackContacts gives you both layers. Now you know when to use each.

Key Takeaways

• MCP is for exploration, SQL is for extraction — Learn which layer to use for each task

• The 500-row limit is a context window issue, not a database limit — Your data is complete, you’re just accessing it through the wrong layer

• Use AI to write queries, but run them directly against the database — Best of both worlds: AI assistance + complete results

• Always test with LIMIT before running full exports — Prevents “oops I exported garbage” moments

• Save your verified queries for reuse — Turn one-off exports into repeatable systems

• Be intentional about what you export and why — Start with the use case, not “SELECT *”

• Name your exports with timestamps and descriptions — Future you will thank past you

Your Turn

If you’ve hit the 500-row wall before, try this workflow:

1. Pick one use case (outreach, segmentation, enrichment)

2. Use Claude to generate the SQL

3. Run it directly against your database

4. Export the full, untruncated results

5. Share what you built (community, office hours, or just reply to this post)

Questions? Bring your specific export scenario to office hours and we’ll debug it together.

Leave a comment

Related:

• Revenue Attribution: Which Content Actually Drives Sales

• Advanced Segmentation: Finding Your Superfans

• Predictive Analytics: Spotting High-Intent Subscribers

I build tools for Substack creators.

Specifically, I built StackContacts - a tool that pulls all your Substack data onto your own computer and uses AI to find patterns you can’t see in Substack’s dashboard.

For months, I’ve been telling other creators: “You need to understand your data. Growth isn’t random. Your audience is sending you signals.”

But here’s the embarrassing truth: I hadn’t run my own newsletter through the complete analysis.

Sure, I’d checked my subscriber count. Glanced at my stats. Told myself things were “pretty good.”

Then, last week, I decided actually to eat my own dog food.

I ran finntropy.substack.com through the same AI analysis I’ve been building for other creators.

What I found made me want to delete my LinkedIn profile and become a shepherd in New Zealand.

But it also showed me exactly why most Substack creators feel like they’re shouting into the void.

The Good News (That Made Me Feel Smart)

Let me start with the wins, because I need the dopamine (and more coffee):

86.8% of my subscribers are highly engaged.

Not “opened an email once” engaged. I’m talking 10+ interactions - opens, clicks, comments, the works.

The industry average? Somewhere between 20% and 30% if you’re doing well.

My average subscriber has engaged with 19.3 different posts. They’re clicking links (7.4 clicks per subscriber). They’re active across an average of 22 days.

My top-performing posts? They’re crushing it:

• “How To Schedule Notes on Substack” - 3,806 views, 188 reactions, 91 comments

• “How Often Should You Publish Notes...” - 2,919 views, 237 reactions, 95 comments

• “How I Turned My Substack Posts into Dividend-Paying Assets” - 1,545 views, 105 reactions

For a newsletter with 1,193 subscribers and an annual sales revenue over $20k, these are legitimately strong numbers.

I felt pretty good about myself.
Look at me, crushing it with my engaged audience!

And then the AI showed me the ugly truth.

The Ugly Truth (That Made Me Want to Quit)

Here’s the data point that punched me in the throat:

100% of my new subscribers (who joined in the last 7 days) have never engaged with anything.

Not a single open. Not one click. Zero.

Twelve people signed up for my newsletter in the past week, excited enough to give me their email address, and I’ve done absolutely nothing to welcome them.

No welcome sequence. No “start here” post. No personal message.

I literally ghosted 100% of my new subscribers.

And it gets worse.

268 of my long-term subscribers haven’t engaged in 30+ days.

That’s 27% of my loyal readers slowly going dormant while I publish posts about “engagement strategies” and “audience building.”

The irony is so thick you could cut it with a knife.

But here’s where it gets truly painful:

I haven’t published a post in 22 days. Only three posts in the last 30 days.

The AI analysis showed my publishing frequency dropped from 11 posts in December 2024 to just 2-4 posts per month recently.

My newsletter about Substack growth strategies... has stopped growing because I stopped showing up consistently.

I am literally the person I write about helping.

The Pattern I Couldn’t See (Until AI Found It)

Here’s why this matters for you:

When I looked at my Substack dashboard, I saw:

• “Engagement is strong” ✅

• “Subscribers keep joining” ✅

• “Popular posts are performing well” ✅

What I couldn’t see without AI analysis:

My New Subscriber Onboarding Is Broken

The AI compared engagement rates across subscriber cohorts:

• New subscribers (0-7 days): 0% engaged

• Recent subscribers (8-30 days): 94% engaged

• Established subscribers (31-90 days): 100% engaged (none never-engaged)

The pattern was evident: If subscribers stick around past the first week, they become superfans. But I’m losing people in the first 7 days because I’m not giving them a reason to care.

My Publishing Frequency Directly Impacts Growth

The AI mapped posts published vs. new subscribers by month:

More posts ≠ more subscribers.

But consistent publishing = predictable growth.

My best month (July 2025) had only 3 posts but 54.33 subscribers per post.
My worst efficiency (December 2024) had 11 posts but only 7.45 subscribers per post.

The difference? Quality + timing + consistency.

I Know Exactly Which Content Types Work

The AI categorized all 75 of my posts and calculated performance by content type:

I have 36 posts in the “Other” category - basically filler content I published when I didn’t know what to write.

Those 36 posts got the lowest average views (457.8) and reactions (13.9).

Meanwhile, my 5 Strategy/Advice posts averaged 1,277.8 views and 96.4 reactions.

I’ve been publishing the wrong content 48% of the time.

And I had no idea until the AI showed me the pattern.

The Moment Everything Changed

I’ve built analysis tools professionally for years. I understand data. I know how to read spreadsheets.

But seeing my own publication laid out like this - with every pattern exposed, every failure highlighted, every missed opportunity quantified - hit different.

Because here’s what I realized:

Every Substack creator is flying blind.
Not because we’re stupid. Not because we don’t care.

But because the Substack dashboard shows you what happened, not why it happened or what to do about it.

You know you have 847 subscribers. Great.

How many are actually engaged? Which cohorts are at risk? What content types are working? When should you publish? Who are your superfans you should be building relationships with?

The dashboard can’t tell you.

So we make decisions based on vibes:

• “Engagement feels low” (but is it actually low or just normal?)

• “Growth feels random” (but is there a pattern you’re missing?)

• “I don’t understand my audience” (but what if the data already shows you who they are?)

It’s like trying to drive across the country with a map that only shows which state you’re in. Technically useful. Practically useless.

What I Did Next (And What You Can Do Too)

I’m not going to pretend this article is purely altruistic.

I built StackContacts because I got tired of seeing talented creators give up because they couldn’t figure out what was working.

But after running my own analysis, here’s what I’m doing differently:

This Week:

1. ✅ Creating a welcome sequence for new subscribers (no more 100% non-engagement)

2. ✅ Sending a “I Miss You” email to those 268 dormant subscribers

3. ✅ Publishing this post (getting back to consistency)

4. ✅ Reaching out personally to my top 20 superfans (they’ve earned it)

This Month:

1. Publishing 1-2 posts per week minimum (no more excuses)

2. Doubling down on Strategy/Advice content (1,277.8 avg views vs 457.8 for “Other”)

3. Reducing filler “Other” content by 50%

4. Tracking which posts drive subscribers (using data, not vibes)

This Quarter:

1. Running this analysis monthly to track progress

2. Building relationships with the subscribers who are already engaged

3. Creating a content series based on my top-performing posts

4. Actually using my own tool consistently (shocking concept, I know)

The Uncomfortable Question

If you’re still reading, you probably recognize yourself in this story.

You’ve got subscribers. You publish (sometimes). Engagement feels... fine?

But deep down, you know you’re flying blind. You’re making decisions based on what feels right instead of what the data shows.

And the Substack dashboard isn’t helping because it’s not designed to show you patterns - just metrics.

Here’s my uncomfortable question for you:

What if you’re making the same mistakes I was making?

What if you’re:

• Ignoring your new subscribers without realizing it

• Publishing the wrong content types 48% of the time

• Watching engaged subscribers go dormant while you focus on vanity metrics

• Missing obvious patterns in your growth because you don’t have the tools to see them

I built StackContacts to solve this exact problem.

Not because I’m a data genius (clearly, since I ghosted 100% of my new subscribers).

But because I got tired of watching creators - including myself - make preventable mistakes.

How This Actually Works (The Technical Bits)

Here’s what happens when you use StackContacts:

Subscribe now

1. You Download Your Data

StackContacts pulls all your Substack data onto your own computer.
Subscribers, followers, events, posts, notes, engagement, everything.

Why your own computer? Because:

• Your data stays private (no third-party storage)

• You can analyze it offline anytime

• No subscription fees for “data storage.”

• You actually own your audience insights

2. AI Analyzes the Patterns

The tool runs your data through AI analysis to find:

• Engagement patterns by subscriber cohort

• Content performance by type

• Growth correlations (publishing frequency, timing, topics)

• At-risk subscribers who are going dormant

• Your superfans who could become advocates

• Specific, actionable recommendations

3. You Get Actual Insights

Not generic advice like “post more consistently.”

Specific insights like:

• “Your Strategy/Advice posts get 2.8x more views than your Other content”

• “100% of subscribers who make it past day 7 become highly engaged”

• “268 subscribers haven’t engaged in 30+ days - here’s how to re-engage them”

• “July 2025 had 54.33 subscribers per post vs December 2024’s 7.45 - here’s why”

The Real Reason This Matters

I spent the last 18 months building tools to help other creators grow.

Then I discovered I was failing at the basics.
Not because I didn’t care. Not because I’m lazy.

But because you can’t optimize what you can’t measure, and you can’t measure what you can’t see.

The Substack dashboard shows you the what.
AI analysis shows you the why and the what to do about it.

That’s the difference between “my growth feels random” and “I know exactly which content types drive subscribers and when to publish them.”

Between “I think engagement is okay?” and “86.8% of my subscribers are highly engaged, but 100% of new subscribers aren’t engaging because I don’t have a welcome sequence.”

Between guessing and knowing.

Try This Yourself

I’m not going to do a hard sell here.

If you’re skeptical about AI analysis tools, you should be. Most of them are garbage that spit out generic advice you could get from a blog post.

But here’s what I’ll offer:

Run your own analysis with the tool you can get from StackContacts.com
See what patterns you’re missing. Find out which content actually works.
Discover which subscribers are at risk.

If it’s useless, you’ll know in 10 minutes.

If it’s useful, you’ll have the exact same uncomfortable awakening I had:

“Oh no. I’ve been doing this wrong the whole time.”

And then you can actually fix it.

Because the only thing worse than discovering you’re failing 100% of your new subscribers...

...is never discovering it at all.

The Bottom Line

I built StackContacts to help creators grow.

Then I discovered I needed it more than anyone.

My newsletter has:

• 1,193 subscribers

• 86.8% highly engaged audience

• Strong content performance

• Proven content types that work

And I was still making critical mistakes I couldn’t see.

If I’m flying blind with tools and data experience, what chance do you have with just the Substack dashboard?

The answer isn’t to give up.

The answer is actually to look at the data.

• Find the patterns.

• Fix the problems.

• Focus on what works.

And maybe - just maybe - stop ghosting 100% of your new subscribers.

Want to run your own analysis?

Download your Substack data and analyze it with AI using the StackContacts tool.

See what you’re missing. Fix what’s broken. Grow what’s working.

Your uncomfortable awakening is waiting.

— Finn

P.S. - After publishing this post, I’m sending welcome emails to those 12 new subscribers I ghosted. If you’re one of them and you’re reading this: I’m sorry. I was too busy building tools to help creators engage with their audiences, actually, to engage with my own. The irony is not lost on me.

P.P.S. - If you found this useful, reply and let me know what patterns you’re blind to in your own newsletter. I’m genuinely curious what other creators are missing that AI could help uncover.

Leave a comment

Substack’s dashboard tells you subscriber counts and post views. But it doesn’t answer the questions that keep you up at night:

• “Why isn’t my content getting engagement?”

• “Which posts are actually driving growth?”

• “Am I publishing enough? Too much?”

• “Where are my subscribers coming from?”

• “What’s working and what’s not?”

You’re flying blind without data. You’re guessing instead of making decisions.

The StackContacts advantage: With your raw data locally, you can answer these questions in minutes - not hours of manual spreadsheet work.

You can grab your copy of the StackContacts tool - only $19 one-time fee to get detailed insights into your Substack data and start growing!

Ready-to-Use StackContacts AI Prompt

Copy the prompt below, replace [publication] with your publication name, and paste it into Claude (Cursor IDE or Claude Desktop) connected to your StackContacts database.

To find your schema name: Ask Claude, “What tables are in my StackContacts database? Show me the full table names with schema prefix.”

Your schema follows the pattern: pub_[your_publication]_substack_com

# New Writer Growth Analysis Prompt

## Primary Instructions

Using the StackContacts database, analyze the growth patterns and engagement data for a new writer with less than 100 subscribers. Identify what's working, what's not working, compare to successful growth patterns, and provide concrete, actionable recommendations to help them grow from zero to their first 100 subscribers.

Your analysis should be empathetic, address the specific pain points new writers face (distribution struggles, no interaction, confusion about growth, feeling like they're flying blind), and provide data-driven insights with clear next steps.

---

## INPUTS: [publication] use [your publication schema name]
## Parameters: None (analyzes all subscribers and content)

**IMPORTANT - Schema Customization:**
Before using this prompt, you must replace `[publication]` with your actual schema name.
- Your schema follows the pattern: `pub_[your_publication]_substack_com`
- To find your schema: Ask Claude "What tables are in my StackContacts database? Show me the full table names with schema prefix."
- Replace all instances of `[publication]` in the queries below with your schema name.

**IMPORTANT - Database Syntax:**
- This prompt uses DuckDB syntax. Date functions use `CURRENT_TIMESTAMP - INTERVAL 'X days'` format
- The `subscriber_events` table only has `subscriber_email` column (not `email`)
- Use `CURRENT_TIMESTAMP` for date comparisons, not `CURRENT_DATE`

---

## Step 1: Current State Assessment

### A. Subscriber Base Overview

Understand the current subscriber situation:

```sql
-- Current Subscriber Base Overview
SELECT 
  COUNT(*) as total_subscribers,
  COUNT(CASE WHEN is_subscribed = True THEN 1 END) as active_subscribers,
  COUNT(CASE WHEN is_subscribed = False THEN 1 END) as inactive_subscribers,
  COUNT(CASE WHEN membership_state = 'free_signup' THEN 1 END) as free_subscribers,
  COUNT(CASE WHEN membership_state LIKE '%paid%' THEN 1 END) as paid_subscribers,
  MIN(created_at) as first_subscriber_date,
  MAX(created_at) as most_recent_subscriber_date,
  DATEDIFF('day', MIN(created_at), CURRENT_TIMESTAMP) as days_since_first_subscriber,
  ROUND(COUNT(*) * 1.0 / NULLIF(DATEDIFF('day', MIN(created_at), CURRENT_TIMESTAMP), 0), 2) as avg_subscribers_per_day
FROM [publication].subscribers_detail;

Extract:

Total subscriber count
Active vs. inactive breakdown
Free vs. paid breakdown
How long they've been publishing
Average subscribers per day

### B. Growth Trajectory
Analyze growth over time:

-- Growth Trajectory Analysis
WITH monthly_subscribers AS (
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as new_subscribers
  FROM [publication].subscribers_detail
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
  month,
  new_subscribers,
  SUM(new_subscribers) OVER (ORDER BY month) as cumulative_subscribers,
  LAG(new_subscribers) OVER (ORDER BY month) as previous_month,
  CASE 
    WHEN LAG(new_subscribers) OVER (ORDER BY month) IS NOT NULL 
    THEN ROUND((new_subscribers - LAG(new_subscribers) OVER (ORDER BY month)) * 100.0 / 
               NULLIF(LAG(new_subscribers) OVER (ORDER BY month), 0), 2)
    ELSE NULL
  END as month_over_month_growth_pct
FROM monthly_subscribers
ORDER BY month DESC;

**Extract:**
- Monthly subscriber growth
- Cumulative growth over time
- Month-over-month growth rate
- Growth trends (accelerating, steady, declining)

### C. Content Publishing Activity

Assess content output:

-- Content Publishing Activity
SELECT 
  COUNT(*) as total_posts,
  COUNT(CASE WHEN post_date >= CURRENT_TIMESTAMP - INTERVAL '30 days' THEN 1 END) as posts_last_30d,
  COUNT(CASE WHEN post_date >= CURRENT_TIMESTAMP - INTERVAL '7 days' THEN 1 END) as posts_last_7d,
  MIN(post_date) as first_post_date,
  MAX(post_date) as last_post_date,
  DATEDIFF('day', MIN(post_date), CURRENT_TIMESTAMP) as days_since_first_post,
  ROUND(COUNT(*) * 1.0 / NULLIF(DATEDIFF('day', MIN(post_date), CURRENT_TIMESTAMP), 0), 2) as avg_posts_per_day,
  ROUND(COUNT(*) * 1.0 / NULLIF(DATEDIFF('day', MIN(post_date), CURRENT_TIMESTAMP), 0) * 7, 2) as avg_posts_per_week,
  SUM(stat_views) as total_views,
  SUM(reaction_count) as total_reactions,
  SUM(comment_count) as total_comments,
  ROUND(AVG(stat_views), 1) as avg_views_per_post,
  ROUND(AVG(reaction_count), 1) as avg_reactions_per_post
FROM [publication].substack_posts;

**Extract:**
- Total posts published
- Recent publishing frequency
- Publishing consistency
- Average engagement metrics
- Views, reactions, comments per post

---

## Step 2: Engagement Analysis

### A. Subscriber Engagement Levels

Understand how engaged current subscribers are:

-- Subscriber Engagement Levels
WITH subscriber_engagement AS (
  SELECT 
    sd.email,
    sd.created_at as subscriber_since,
    COUNT(se.event_id) as total_events,
    COUNT(DISTINCT se.post_title) as posts_engaged,
    COUNT(CASE WHEN se.text LIKE '%Clicked link%' OR se.text LIKE '%clicked%' THEN 1 END) as link_clicks,
    COUNT(DISTINCT DATE(se.timestamp)) as active_days,
    MIN(se.timestamp) as first_engagement,
    MAX(se.timestamp) as last_engagement,
    DATEDIFF('day', MAX(se.timestamp), CURRENT_TIMESTAMP) as days_since_last_engagement
  FROM [publication].subscribers_detail sd
  LEFT JOIN [publication].subscriber_events se ON sd.email = se.subscriber_email
  GROUP BY sd.email, sd.created_at
)
SELECT 
  COUNT(*) as total_subscribers,
  COUNT(CASE WHEN total_events = 0 THEN 1 END) as never_engaged,
  COUNT(CASE WHEN total_events > 0 AND total_events <= 2 THEN 1 END) as low_engagement,
  COUNT(CASE WHEN total_events > 2 AND total_events <= 10 THEN 1 END) as medium_engagement,
  COUNT(CASE WHEN total_events > 10 THEN 1 END) as high_engagement,
  ROUND(AVG(total_events), 1) as avg_events_per_subscriber,
  ROUND(AVG(posts_engaged), 1) as avg_posts_engaged,
  ROUND(AVG(link_clicks), 1) as avg_link_clicks,
  ROUND(AVG(active_days), 1) as avg_active_days,
  ROUND(AVG(days_since_last_engagement), 1) as avg_days_since_last_engagement
FROM subscriber_engagement;

**Extract:**
- Engagement distribution (never, low, medium, high)
- Average engagement metrics
- How many subscribers are actually reading
- Days since last engagement

### B. Content Performance Analysis

Identify which content resonates:

-- Content Performance Analysis
SELECT 
  title,
  post_date,
  stat_views,
  reaction_count,
  comment_count,
  ROUND(reaction_count * 100.0 / NULLIF(stat_views, 0), 2) as reaction_rate_pct,
  ROUND(comment_count * 100.0 / NULLIF(stat_views, 0), 2) as comment_rate_pct,
  COUNT(DISTINCT se.subscriber_email) as unique_engagements,
  COUNT(CASE WHEN se.text LIKE '%Clicked link%' OR se.text LIKE '%clicked%' THEN 1 END) as link_clicks
FROM [publication].substack_posts sp
LEFT JOIN [publication].subscriber_events se ON sp.title = se.post_title
GROUP BY title, post_date, stat_views, reaction_count, comment_count
ORDER BY stat_views DESC
LIMIT 20;

Extract:**
- Top 20 posts by views
- Engagement rates (reactions, comments)
- Which posts drive link clicks
- Content that resonates vs. doesn't

### C. Subscriber Acquisition Sources

Understand where subscribers come from:

-- Subscriber Acquisition Analysis
SELECT 
  CASE 
    WHEN free_attribution LIKE '%substack-signup-flow%' OR source LIKE '%substack-signup-flow%' THEN 'Substack Signup Flow'
    WHEN free_attribution LIKE '%substack-notes%' OR source LIKE '%substack-notes%' THEN 'Substack Notes'
    WHEN free_attribution LIKE '%substack-app%' OR source LIKE '%substack-app%' THEN 'Substack App'
    WHEN free_attribution LIKE '%substack-reader-profile-discovery%' OR source LIKE '%substack-reader-profile-discovery%' THEN 'Substack Profile Discovery'
    WHEN free_attribution LIKE '%google%' OR source LIKE '%google%' THEN 'Google Search'
    WHEN free_attribution LIKE '%substack.com%' OR source LIKE '%substack.com%' THEN 'Substack.com Website'
    WHEN free_attribution LIKE '%substack-follow-recommendations%' OR source LIKE '%substack-follow-recommendations%' THEN 'Substack Recommendations'
    WHEN free_attribution LIKE '%direct%' OR source LIKE '%direct%' THEN 'Direct'
    WHEN free_attribution LIKE '%substack-chat%' OR source LIKE '%substack-chat%' THEN 'Substack Chat'
    WHEN free_attribution LIKE '%import%' OR source LIKE '%import%' THEN 'Import/Migration'
    WHEN free_attribution LIKE '%substack-mentions%' OR source LIKE '%substack-mentions%' THEN 'Substack Mentions'
    WHEN free_attribution LIKE '%post-end-cta%' OR source LIKE '%post-end-cta%' THEN 'Post End CTA'
    WHEN free_attribution LIKE '%duckduckgo%' OR source LIKE '%duckduckgo%' THEN 'DuckDuckGo Search'
    WHEN free_attribution IS NULL AND source IS NULL THEN 'Unknown'
    ELSE 'Other'
  END as acquisition_source,
  COUNT(*) as subscriber_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM [publication].subscribers_detail
GROUP BY 
  CASE 
    WHEN free_attribution LIKE '%substack-signup-flow%' OR source LIKE '%substack-signup-flow%' THEN 'Substack Signup Flow'
    WHEN free_attribution LIKE '%substack-notes%' OR source LIKE '%substack-notes%' THEN 'Substack Notes'
    WHEN free_attribution LIKE '%substack-app%' OR source LIKE '%substack-app%' THEN 'Substack App'
    WHEN free_attribution LIKE '%substack-reader-profile-discovery%' OR source LIKE '%substack-reader-profile-discovery%' THEN 'Substack Profile Discovery'
    WHEN free_attribution LIKE '%google%' OR source LIKE '%google%' THEN 'Google Search'
    WHEN free_attribution LIKE '%substack.com%' OR source LIKE '%substack.com%' THEN 'Substack.com Website'
    WHEN free_attribution LIKE '%substack-follow-recommendations%' OR source LIKE '%substack-follow-recommendations%' THEN 'Substack Recommendations'
    WHEN free_attribution LIKE '%direct%' OR source LIKE '%direct%' THEN 'Direct'
    WHEN free_attribution LIKE '%substack-chat%' OR source LIKE '%substack-chat%' THEN 'Substack Chat'
    WHEN free_attribution LIKE '%import%' OR source LIKE '%import%' THEN 'Import/Migration'
    WHEN free_attribution LIKE '%substack-mentions%' OR source LIKE '%substack-mentions%' THEN 'Substack Mentions'
    WHEN free_attribution LIKE '%post-end-cta%' OR source LIKE '%post-end-cta%' THEN 'Post End CTA'
    WHEN free_attribution LIKE '%duckduckgo%' OR source LIKE '%duckduckgo%' THEN 'DuckDuckGo Search'
    WHEN free_attribution IS NULL AND source IS NULL THEN 'Unknown'
    ELSE 'Other'
  END
ORDER BY subscriber_count DESC;

**Extract:**
- Where subscribers are coming from
- Which channels drive most growth
- Acquisition source distribution

---

## Step 3: Compare to Growth Benchmarks

### A. Publishing Frequency vs. Growth

Compare publishing frequency to subscriber growth:

-- Publishing Frequency vs. Growth Analysis
WITH monthly_metrics AS (
  SELECT 
    DATE_TRUNC('month', sp.post_date) as month,
    COUNT(DISTINCT sp.id) as posts_published,
    COUNT(DISTINCT sd.email) as new_subscribers,
    SUM(sp.stat_views) as total_views,
    SUM(sp.reaction_count) as total_reactions
  FROM [publication].substack_posts sp
  FULL OUTER JOIN [publication].subscribers_detail sd 
    ON DATE_TRUNC('month', sp.post_date) = DATE_TRUNC('month', sd.created_at)
  GROUP BY DATE_TRUNC('month', sp.post_date)
)
SELECT 
  month,
  posts_published,
  new_subscribers,
  total_views,
  total_reactions,
  ROUND(new_subscribers * 1.0 / NULLIF(posts_published, 0), 2) as subscribers_per_post,
  ROUND(total_views * 1.0 / NULLIF(posts_published, 0), 1) as avg_views_per_post,
  ROUND(total_reactions * 1.0 / NULLIF(posts_published, 0), 1) as avg_reactions_per_post
FROM monthly_metrics
WHERE month IS NOT NULL
ORDER BY month DESC;

**Extract:**
- Posts published per month
- New subscribers per month
- Subscribers per post ratio
- Views and reactions per post
- Correlation between frequency and growth

### B. Engagement Quality Assessment

Assess if engagement is translating to growth:

-- Engagement Quality Assessment
WITH post_engagement AS (
  SELECT 
    sp.title,
    sp.post_date,
    sp.stat_views,
    sp.reaction_count,
    COUNT(DISTINCT se.subscriber_email) as unique_engagements,
    COUNT(CASE WHEN se.text LIKE '%Clicked link%' OR se.text LIKE '%clicked%' THEN 1 END) as link_clicks
  FROM [publication].substack_posts sp
  LEFT JOIN [publication].subscriber_events se ON sp.title = se.post_title
  GROUP BY sp.title, sp.post_date, sp.stat_views, sp.reaction_count
),
subscriber_growth AS (
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as new_subscribers
  FROM [publication].subscribers_detail
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
  DATE_TRUNC('month', pe.post_date) as month,
  COUNT(*) as posts_published,
  SUM(pe.stat_views) as total_views,
  SUM(pe.reaction_count) as total_reactions,
  SUM(pe.unique_engagements) as total_engagements,
  SUM(pe.link_clicks) as total_link_clicks,
  COALESCE(sg.new_subscribers, 0) as new_subscribers,
  ROUND(COALESCE(sg.new_subscribers, 0) * 100.0 / NULLIF(SUM(pe.unique_engagements), 0), 2) as engagement_to_subscriber_rate
FROM post_engagement pe
LEFT JOIN subscriber_growth sg ON DATE_TRUNC('month', pe.post_date) = sg.month
GROUP BY DATE_TRUNC('month', pe.post_date), sg.new_subscribers
ORDER BY month DESC;

**Extract:**
- Engagement metrics by month
- New subscribers by month
- Engagement-to-subscriber conversion rate
- Whether engagement is leading to growth

## Step 4: Identify Growth Barriers

### A. Content Distribution Analysis

Check if content is being shared/distributed:
-- Content Distribution Analysis
SELECT 
  sp.title,
  sp.post_date,
  sp.stat_views,
  COUNT(DISTINCT se.subscriber_email) as unique_engagements,
  COUNT(CASE WHEN se.text LIKE '%Clicked link%' OR se.text LIKE '%clicked%' THEN 1 END) as link_clicks,
  COUNT(CASE WHEN se.text LIKE '%restack%' OR se.text LIKE '%share%' THEN 1 END) as shares,
  ROUND(COUNT(DISTINCT se.subscriber_email) * 100.0 / NULLIF(sp.stat_views, 0), 2) as engagement_rate_pct
FROM [publication].substack_posts sp
LEFT JOIN [publication].subscriber_events se ON sp.title = se.post_title
GROUP BY sp.title, sp.post_date, sp.stat_views
ORDER BY sp.post_date DESC
LIMIT 20;

**Extract:**
- Recent posts and their performance
- Engagement rates
- Share/restack activity
- Whether content is reaching subscribers

### B. Subscriber Retention Analysis

Check if subscribers are staying engaged:

-- Subscriber Retention Analysis
WITH subscriber_lifecycle AS (
  SELECT 
    sd.email,
    sd.created_at as subscribed_date,
    MIN(se.timestamp) as first_engagement,
    MAX(se.timestamp) as last_engagement,
    COUNT(se.event_id) as total_events,
    DATEDIFF('day', sd.created_at, CURRENT_TIMESTAMP) as days_since_subscribed,
    DATEDIFF('day', MAX(se.timestamp), CURRENT_TIMESTAMP) as days_since_last_engagement
  FROM [publication].subscribers_detail sd
  LEFT JOIN [publication].subscriber_events se ON sd.email = se.subscriber_email
  GROUP BY sd.email, sd.created_at
)
SELECT 
  CASE 
    WHEN days_since_subscribed <= 7 THEN 'New (0-7 days)'
    WHEN days_since_subscribed <= 30 THEN 'Recent (8-30 days)'
    WHEN days_since_subscribed <= 90 THEN 'Established (31-90 days)'
    ELSE 'Long-term (90+ days)'
  END as subscriber_cohort,
  COUNT(*) as subscriber_count,
  COUNT(CASE WHEN total_events = 0 THEN 1 END) as never_engaged,
  COUNT(CASE WHEN days_since_last_engagement <= 7 THEN 1 END) as active_last_7d,
  COUNT(CASE WHEN days_since_last_engagement > 30 THEN 1 END) as inactive_30d_plus,
  ROUND(AVG(total_events), 1) as avg_events,
  ROUND(AVG(days_since_last_engagement), 1) as avg_days_since_engagement
FROM subscriber_lifecycle
GROUP BY 
  CASE 
    WHEN days_since_subscribed <= 7 THEN 'New (0-7 days)'
    WHEN days_since_subscribed <= 30 THEN 'Recent (8-30 days)'
    WHEN days_since_subscribed <= 90 THEN 'Established (31-90 days)'
    ELSE 'Long-term (90+ days)'
  END
ORDER BY 
  CASE 
    WHEN subscriber_cohort = 'New (0-7 days)' THEN 1
    WHEN subscriber_cohort = 'Recent (8-30 days)' THEN 2
    WHEN subscriber_cohort = 'Established (31-90 days)' THEN 3
    ELSE 4
  END;

**Extract:**
- Subscriber retention by cohort
- How many never engage
- Active vs. inactive subscribers
- Engagement patterns over time

### C. Content Topic Analysis

Identify which topics/content types perform best:
-- Content Topic Performance
WITH post_topics AS (
  SELECT 
    sp.title,
    sp.post_date,
    sp.stat_views,
    sp.reaction_count,
    COUNT(DISTINCT se.subscriber_email) as unique_engagements,
    CASE 
      WHEN LOWER(sp.title) LIKE '%how to%' OR LOWER(sp.title) LIKE '%guide%' THEN 'How-To/Tutorial'
      WHEN LOWER(sp.title) LIKE '%why%' OR LOWER(sp.title) LIKE '%should%' THEN 'Strategy/Advice'
      WHEN LOWER(sp.title) LIKE '%i %' OR LOWER(sp.title) LIKE '%my %' THEN 'Personal Story'
      WHEN LOWER(sp.title) LIKE '%what%' OR LOWER(sp.title) LIKE '%which%' THEN 'Educational'
      ELSE 'Other'
    END as content_type
  FROM [publication].substack_posts sp
  LEFT JOIN [publication].subscriber_events se ON sp.title = se.post_title
  GROUP BY sp.title, sp.post_date, sp.stat_views, sp.reaction_count
)
SELECT 
  content_type,
  COUNT(*) as post_count,
  ROUND(AVG(stat_views), 1) as avg_views,
  ROUND(AVG(reaction_count), 1) as avg_reactions,
  ROUND(AVG(unique_engagements), 1) as avg_engagements,
  ROUND(SUM(stat_views), 0) as total_views
FROM post_topics
GROUP BY content_type
ORDER BY avg_views DESC;

**Extract:**
- Content types and their performance
- Which topics get most views
- Which topics drive engagement
- Content strategy insights

---

## Step 5: Growth Opportunity Identification

### A. High-Engagement Subscribers Analysis

Identify engaged subscribers who might help with growth:

-- High-Engagement Subscribers (Potential Advocates)
WITH subscriber_engagement AS (
  SELECT 
    sd.email,
    sd.created_at as subscribed_date,
    COUNT(se.event_id) as total_events,
    COUNT(DISTINCT se.post_title) as posts_engaged,
    COUNT(CASE WHEN se.text LIKE '%Clicked link%' OR se.text LIKE '%clicked%' THEN 1 END) as link_clicks,
    MAX(se.timestamp) as last_engagement,
    DATEDIFF('day', MAX(se.timestamp), CURRENT_TIMESTAMP) as days_since_last_engagement
  FROM [publication].subscribers_detail sd
  LEFT JOIN [publication].subscriber_events se ON sd.email = se.subscriber_email
  GROUP BY sd.email, sd.created_at
)
SELECT 
  email,
  total_events,
  posts_engaged,
  link_clicks,
  days_since_last_engagement,
  CASE 
    WHEN total_events >= 10 AND posts_engaged >= 3 THEN 'Superfan - Ask for Referral'
    WHEN total_events >= 5 AND link_clicks >= 1 THEN 'Engaged - Potential Advocate'
    WHEN total_events >= 3 THEN 'Active - Build Relationship'
    ELSE 'Monitor'
  END as engagement_tier
FROM subscriber_engagement
WHERE total_events >= 3
ORDER BY total_events DESC
LIMIT 20;

**Extract:**
- Most engaged subscribers
- Potential advocates or referral sources
- Subscribers to build relationships with

### B. Content Gaps Analysis

Identify what content is missing:

-- Content Gaps Analysis
WITH all_posts AS (
  SELECT 
    title,
    post_date,
    stat_views,
    reaction_count,
    COUNT(DISTINCT se.subscriber_email) as unique_engagements
  FROM [publication].substack_posts sp
  LEFT JOIN [publication].subscriber_events se ON sp.title = se.post_title
  GROUP BY title, post_date, stat_views, reaction_count
)
SELECT 
  'Total Posts' as metric,
  COUNT(*) as value
FROM all_posts
UNION ALL
SELECT 
  'Posts with 0 Views',
  COUNT(CASE WHEN stat_views = 0 THEN 1 END)
FROM all_posts
UNION ALL
SELECT 
  'Posts with 0 Reactions',
  COUNT(CASE WHEN reaction_count = 0 THEN 1 END)
FROM all_posts
UNION ALL
SELECT 
  'Posts with 0 Engagements',
  COUNT(CASE WHEN unique_engagements = 0 THEN 1 END)
FROM all_posts
UNION ALL
SELECT 
  'Avg Views per Post',
  ROUND(AVG(stat_views), 1)
FROM all_posts
UNION ALL
SELECT 
  'Avg Reactions per Post',
  ROUND(AVG(reaction_count), 1)
FROM all_posts;

**Extract:**
- Content performance summary
- Posts with no engagement
- Average performance metrics
- Content quality issues

---

## Output Format

Structure your analysis as follows:

### 📋 Executive Summary
- Current subscriber count
- How long they've been publishing
- Average subscribers per day/week
- Key insight: What's the biggest growth barrier?

### 📊 Current State Assessment

**Subscriber Base:**
- Total subscribers, active vs. inactive
- Free vs. paid breakdown
- Days since first subscriber
- Growth rate (subscribers per day)

**Growth Trajectory:**
- Monthly subscriber growth table
- Cumulative growth over time
- Month-over-month growth rate
- Growth trend (accelerating, steady, declining, stalled)

**Content Publishing:**
- Total posts published
- Publishing frequency (posts per week)
- Publishing consistency
- Recent activity (last 7 days, 30 days)

**Key Insights:**
- Are they publishing consistently?
- Is growth steady or stalled?
- What's the current growth rate?

### 📈 Engagement Analysis

**Subscriber Engagement Levels:**
- Table: Engagement Tier, Subscriber Count, Percentage
- Never engaged, Low, Medium, High engagement breakdown
- Average events per subscriber
- Days since last engagement

**Content Performance:**
- Top 20 posts by views with engagement metrics
- Posts with highest engagement rates
- Posts with link clicks
- Content that resonates vs. doesn't

**Subscriber Acquisition:**
- Table: Acquisition Source, Subscriber Count, Percentage
- Where subscribers are coming from
- Which channels work best
- Unknown/direct vs. social media

**Key Insights:**
- What percentage of subscribers actually engage?
- Which content performs best?
- Where are subscribers coming from?
- Is engagement leading to growth?

### 🎯 Growth Barriers Identified

**Content Distribution:**
- Are posts being shared/restacked?
- Engagement rates
- Reach vs. subscriber count
- Distribution challenges

**Subscriber Retention:**
- Table: Subscriber Cohort, Count, Never Engaged, Active Last 7d, Inactive 30d+
- Retention by cohort (new, recent, established)
- Churn indicators
- Engagement patterns

**Content Strategy:**
- Content type performance (How-To, Strategy, Personal Story, etc.)
- Which topics drive engagement
- Content gaps (posts with 0 views/reactions)

**Key Insights:**
- What's preventing growth? (distribution, engagement, content quality)
- Are subscribers staying engaged?
- Which content types work best?

### 💡 Growth Recommendations

#### For Immediate Action (This Week)

**1. Fix Content Distribution (If Struggling)**
- **Problem:** "Writing isn't the hard part. Sharing it everywhere is."
- **Action:** 
  - Create a simple distribution checklist for each post
  - Share on 2-3 platforms consistently (don't try to do all)
  - Focus on platforms where you already have presence
  - Use Notes to share post snippets

**2. Engage with Existing Subscribers**
- **Problem:** "No interaction :("
- **Action:**
  - Respond to every comment personally
  - Send welcome DMs to new subscribers
  - Ask questions in posts to encourage engagement
  - Create content based on subscriber questions

**3. Publish Consistently**
- **Problem:** "Growth feels random"
- **Action:**
  - Set a realistic schedule (1-2 posts per week minimum)
  - Stick to it for 30 days
  - Track which days/times get most engagement
  - Don't worry about perfection - consistency beats perfection

#### For Short-term Growth (This Month)

**4. Focus on One Content Type That Works**
- **Action:**
  - Identify your top 3 performing posts
  - Create more content like those
  - If How-To posts work, write more tutorials
  - If personal stories work, share more experiences
  - Double down on what resonates

**5. Build Relationships with Engaged Subscribers**
- **Action:**
  - Identify your 10 most engaged subscribers
  - Send personal thank-you messages
  - Ask for feedback on content
  - Request referrals (if appropriate)
  - Build a small community around engaged readers

**6. Optimize Content for Engagement**
- **Action:**
  - Add clear questions at the end of posts
  - Include CTAs that encourage comments
  - Share personal stories that invite connection
  - Make content shareable (clear takeaways)

#### For Long-term Growth (Next 3 Months)

**7. Establish Your Niche (If Needed)**
- **Problem:** "Do I really have to make my focus niche?"
- **Action:**
  - Analyze which topics get most engagement
  - Narrow focus to 2-3 related topics
  - Become known for something specific
  - You can still write broadly, but have a core focus

**8. Create a Content Series**
- **Action:**
  - Develop a 4-6 post series on your best topic
  - Reference previous posts in new ones
  - Build momentum with connected content
  - Series help with retention and referrals

**9. Leverage Substack Features**
- **Action:**
  - Use Notes daily (even just 1-2 per day)
  - Engage with other writers' Notes
  - Comment on posts in your niche
  - Restack valuable content
  - Build relationships on-platform

**10. Track What Works**
- **Problem:** "Flying blind without data"
- **Action:**
  - Track which posts drive new subscribers
  - Note which topics get most engagement
  - Monitor which distribution channels work
  - Adjust strategy based on data, not guesses

### 🎯 Specific Growth Tactics

#### For Writers Starting from Zero

**Tactic 1: The "First 10" Strategy**
- Focus on getting your first 10 engaged subscribers
- Personal outreach to each one
- Build relationships before scaling
- Quality over quantity at this stage

**Tactic 2: Cross-Pollination**
- Engage with 5-10 other writers in your niche daily
- Comment meaningfully on their posts
- Restack valuable content
- Build relationships that lead to discovery

**Tactic 3: Value-First Content**
- Every post should solve a problem or share insight
- Make content shareable (clear takeaways)
- Write for your ideal reader, not everyone
- Focus on value, not volume

**Tactic 4: Consistency Over Perfection**
- Publish on schedule, even if not perfect
- 1 good post per week beats 0 perfect posts
- Build the habit of publishing
- Improve as you go

#### For Writers with Stalled Growth

**Tactic 1: Re-Engage Existing Subscribers**
- Send a "What do you want to read?" email
- Ask for feedback on recent posts
- Create content based on their requests
- Reactivate dormant subscribers

**Tactic 2: Analyze and Pivot**
- Identify your 3 worst-performing posts
- Stop creating that type of content
- Double down on your 3 best-performing posts
- Pivot strategy based on data

**Tactic 3: Distribution Audit**
- List all places you share content
- Identify which channels drive subscribers
- Focus on 2-3 channels that work
- Stop wasting time on channels that don't

**Tactic 4: Content Refresh**
- Update and republish top-performing old posts
- Turn one post into multiple Notes
- Create follow-up posts to popular content
- Build on what works

### ⚠️ Common Mistakes to Avoid

**Mistake 1: Publishing Inconsistently**
- **Problem:** "6 months of consistency, no progress" (but were they actually consistent?)
- **Solution:** Set realistic schedule and stick to it for 90 days minimum

**Mistake 2: Ignoring Existing Subscribers**
- **Problem:** "No interaction" - but are you engaging with them?
- **Solution:** Respond to comments, send DMs, ask questions

**Mistake 3: Trying to Do Everything**
- **Problem:** "Sharing it everywhere is" - too overwhelming
- **Solution:** Pick 2-3 distribution channels and master them

**Mistake 4: Writing for Everyone**
- **Problem:** "Do I really have to make my focus niche?"
- **Solution:** You don't need a tiny niche, but you need focus. Write for your ideal reader.

**Mistake 5: Comparing to Big Accounts**
- **Problem:** Feeling discouraged by others' success
- **Solution:** Focus on your own growth trajectory, not others' numbers

### 📊 Growth Benchmarks for <100 Subscribers

**Healthy Growth Indicators:**
- **Publishing:** 1-2 posts per week consistently
- **Engagement:** 20-30% of subscribers engage with content
- **Growth Rate:** 2-5 new subscribers per week
- **Retention:** 60%+ of subscribers engage within 30 days

**Warning Signs:**
- **0% engagement:** Subscribers aren't reading - check content quality
- **No growth for 30+ days:** Need to change strategy
- **High churn:** Subscribers leaving faster than joining
- **0 views on posts:** Distribution problem

---

## Analysis Style Guidelines

1. **Be empathetic:** Acknowledge the frustration ("I know it feels like you're flying blind...")
2. **Be specific:** Use exact numbers, percentages, and examples
3. **Be actionable:** Every insight should lead to a specific action
4. **Be encouraging:** Highlight what's working, not just what's broken
5. **Use their language:** Reference their pain points ("no interaction", "struggling with distribution")
6. **Prioritize:** Focus on 3-5 most important actions, not 20
7. **Format for readability:** Use tables, bullets, clear headers

---

## Quality Checklist

Before finalizing the analysis, ensure you've:

- [ ] Calculated current subscriber count and growth rate
- [ ] Analyzed publishing frequency and consistency
- [ ] Assessed subscriber engagement levels
- [ ] Identified top-performing content
- [ ] Analyzed subscriber acquisition sources
- [ ] Identified growth barriers
- [ ] Compared to benchmarks
- [ ] Provided specific, actionable recommendations
- [ ] Addressed common pain points (distribution, engagement, growth confusion)
- [ ] Made the analysis encouraging and empathetic
- [ ] Included specific next steps with timeframes

How to Use This Prompt

Step 1: Find Your Schema Name

• In Claude connected to StackContacts, ask: “What tables are in my StackContacts database? Show me the full table names with schema prefix.”

• Your schema follows the pattern: `pub_[your_publication]_substack_com`

• Example: If you see `pub_myblog_substack_com.subscribers_detail`, your schema is `pub_myblog_substack_com`

Step 2: Replace `[publication]`

• Use Find & Replace in your editor

• Replace all instances of `[publication]` with your actual schema name (e.g., `pub_myblog_substack_com`)

Step 3: Paste into Claude

• Open Claude in the Cursor IDE or the Claude Desktop

• Make sure Claude is connected to your StackContacts database

• Paste the entire prompt (including all SQL queries)

• Wait 1-2 minutes for the analysis to complete

Step 4: Review and Act

• Save the analysis to a file (e.g., `Growth_Analysis_[Date].md`)

• Focus on the top 3-5 recommendations

• Implement changes one at a time

• Re-run monthly to track progress

What You’ll Get

This prompt analyzes:

• ✅ Your current subscriber base and growth trajectory

• ✅ Publishing frequency and consistency

• ✅ Engagement levels (who’s reading, who’s not)

• ✅ Content performance (which posts work, which don’t)

• ✅ Subscriber acquisition sources (where growth comes from)

• ✅ Growth barriers (what’s preventing growth)

• ✅ Specific, actionable recommendations

The analysis is empathetic, data-driven, and actionable. It addresses real pain points like distribution struggles, low engagement, and growth confusion—and gives you concrete next steps.

Key Takeaways

• Stop guessing. Get data-driven insights about your growth

• No SQL skills required. Just copy, paste, and get answers from Claude

• Works with small audiences. Designed for writers with <500 subscribers

• Actionable recommendations. Every insight leads to a specific action

• Run monthly. Track your progress and adjust strategy

Try it now. Replace `[publication]` with your schema name and paste into Claude. You’ll have a complete growth analysis in minutes.

Questions? Please share your results in the comments or bring them to office hours. We’ll help you interpret your data and build a growth strategy that works.

Leave a comment

— Finn

You’re writing consistently. Growing slowly. But here’s what keeps you up at night:

Who’s actually reading?

Your dashboard shows 500 subscribers. But which 47 opened every email last month? Which 12 comment on everything but haven’t upgraded? And that superfan who used to engage with everything - when exactly did they stop?

You can’t answer because Substack doesn’t show you.

The Problem Isn’t You. It’s Visibility.

Every platform - Substack, Medium, Amazon, all of them - shows you metrics optimized for their business model, not yours.

They collect far more data than they share. That data exists in their backend APIs. They just chose not to show it to you.

• Substack shows followers and open rates: metrics for their investor pitch - but not which subscribers are ready to pay or which content drove your last Gumroad sale.

• Medium shows views and reads: engagement that keeps you on their platform - but not which readers would buy your products.

These metrics aren’t wrong. They’re just not yours.

You need to see which content drives revenue, which subscribers are most likely to buy, and who’s about to leave.

That’s why I built StackContacts.

Ask Your Data Anything. Get Answers You Can Act On.

No spreadsheets. No SQL. Just ask what you want to know.

“Show me everyone who clicked 3+ links but never left a comment.”
→ 23 subscribers found. Your silent superfans. Perfect for a personal “I noticed you’ve been reading...” outreach.

“Who opened every email last month but isn’t a paid subscriber?”
→ 47 subscribers found. Your warmest leads. They read everything. They just haven’t been asked to upgrade.

“Which subscribers stopped opening emails in the last 30 days?”
→ 156 subscribers at risk. You have maybe 2 weeks before they’re gone forever.

“Which post drove my last Gumroad sale?”
→ Your Tuesday Note about segmentation. Now you know what topics your audience will pay for.

What You Actually Get

Find your superfans by name

See exactly which subscribers open everything, click every link, and comment on your posts. These are the people you should be talking to. Now you know who they are.

Know who’s ready to pay

Not everyone is ready to upgrade. But some people are. See who’s been engaging for months without converting - your warmest leads, ranked by likelihood to buy.

Catch subscribers before they leave

Someone who opened every email for 6 months just stopped. You have maybe 2 weeks before they’re gone. Get flagged before it’s too late.

See exactly which post drove each sale

Stop guessing which content converts. StackContacts connects your Substack posts to your Gumroad purchases, so you know exactly what topics your audience will pay for.

All your audience data in one place

Substack subscribers, Gumroad customers, Kit contacts - automatically synced into a single view. No more switching between dashboards.

Your Data Never Leaves Your Mac

StackContacts runs entirely on your computer.

Your subscriber list, engagement data, and purchase history stay private. No cloud. No third-party servers.

We never see your data.

What This Newsletter Gives You

This isn’t just product support - it’s premium education to help you extract maximum value from your data.

What Free Subscribers Get

This is for people interested in learning more about StackContacts.

• Product updates

• Basic StackContacts usage guides

• Occasional public essays

What Paid Subscribers Get

This is for people who already have data and want better decisions.

• Curated AI prompts ready to run against your StackContacts database

• Decision-focused breakdowns (what mattered, what didn’t, why)

• Advanced, real-world examples of how StackContacts data informs launches, pricing, and retention

• Bi-weekly office hours - bring your specific challenges, and I’ll help to troubleshoot to get the correct answers

• Private community access

• Full archive of past AI prompts, articles, and recordings

• 20-30% off future StackContacts products

What You’ll Learn

• Revenue Attribution: See exactly which Notes or posts drove Gumroad sales

• Predictive Analytics: Identify subscribers 4x more likely to become customers

• Engagement Velocity: Target users at the perfect moment in their journey

• Cross-Platform Insights: Unify your Substack and Gumroad audience into one view

• Advanced Segmentation: Find your superfans, warm leads, and at-risk subscribers

All without complex SQL. Just copy-paste prompts and get answers.

Your Next Paying Subscriber Is Already In Your List

They’ve been reading for months. They’re engaged. They just haven’t been asked.

The only question is whether you’ll see them in time.

See Who’s Ready →

Why I Built This

I’ve been writing software since 1976 and publishing content for 25+ years. When I got tired of Medium’s games, I started pulling data from their APIs and discovered that “followers” were a meaningless metric - prominently displayed but intentionally disconnected from revenue.

I moved to Substack and kept doing the same detective work - studying growth patterns, pulling hidden data, building tools. Just because I love solving puzzles with software.

After 40+ years in corporate and startup environments, I’ve learned that companies optimize for their own survival, not yours. Every dashboard you’ve ever used was designed to make them money.

StackContacts is designed to make you money.

— Finn

I study creator growth the way engineers study systems - with experiments, dashboards, and curiosity. I built StackContacts to turn subscriber data into revenue, and I share AI prompts, research, and office hours notes to help you extract maximum value.

My tools store: finntropy.gumroad.com