Weekly beehiiv newsletter report to Google Sheets and Slack

PromptCreate

Build a code workflow that runs every Monday at 8am (in my local timezone) and produces a weekly beehiiv performance report. The trigger is cron. This is a deterministic pipeline, no drafting or judgement, so please ship it as a code workflow with discrete nodes.

Step 1. Compute the reporting window: the previous Monday 00:00 through Sunday 23:59, plus the equivalent 7-day window before that so we can calculate week-over-week deltas.

Step 2. Call beehiiv Get Post Aggregate Stats scoped to the current 7-day window to grab the top-level totals: sends, unique opens, unique clicks, unsubscribes, and subscriber growth. Call it again for the prior 7-day window so we can compute week-over-week deltas for each metric.

Step 3. Call beehiiv List Posts with the stats expansion enabled and status filter set to confirmed, scoped to the current window, so we get each issue that shipped that week with its per-post stats (recipients, opens, clicks, unsubscribes, top clicked URL).

Step 4. For each issue in the list, build a row with these columns: publish date, post title, sends, open rate, click-to-open rate, unsubscribe rate, and top clicked URL. Use beehiiv Google Sheets Append Values to write those rows to the Newsletter KPIs tab of a Google Sheet the user picks during setup. This builds a permanent historical log.

Step 5. Compose a Slack digest string that leads with the week-over-week deltas on the top-level totals (up/down arrows and percentages), then calls out the best-performing issue of the week (highest click-to-open rate) and the worst-performing issue (lowest open rate), and finishes with a link to the Google Sheet. Post it with Slack Bot Send a Message to a channel the user picks during setup.

Config the user should be prompted for at build time: beehiiv publication id, Google Sheet id and tab name (default: Newsletter KPIs), Slack channel id, and timezone. If no issues shipped in the window, still post the top-level deltas and note that no issues shipped this week.

What does this prompt do?

Pulls last week's beehiiv totals (sends, opens, clicks, unsubscribes, subscriber growth) and every issue that shipped in that window.
Appends one row per issue to a Google Sheets tab so you build a permanent, historical log you can chart and filter.
Posts a Slack digest at 8am Monday that leads with week-over-week changes, calls out the best and worst performing issue, and links back to the sheet.
Runs on a schedule, hands-off. No manual CSV exports, no copy-paste into a thread.

What do I need to use this?

A beehiiv account with API access on the publication you want to report on.
A Google account with edit access to the destination Google Sheet (a tab called Newsletter KPIs works well).
A Slack workspace and the channel you want the Monday digest posted in.

How can I customize it?

Change the schedule (for example, Tuesday at 9am, or a bi-weekly cadence) or the timezone the 7-day window is measured in.
Point it at a different Google Sheet, tab name, or column layout, or split free vs paid audiences into separate rows.
Swap the Slack channel, add extra callouts (like a subscriber milestone), or route the digest to a private founder DM instead.

FAQs

Which newsletter numbers show up in the report?

Total sends, opens, clicks, unsubscribes, and subscriber growth for the week, plus a per-issue breakdown with open rate, click-to-open rate, unsubscribe rate, and the top clicked link.

Does it work if we only publish one issue a week?

Yes. If a single issue shipped, you get one row in Google Sheets and a Slack digest focused on that issue plus the week-over-week deltas versus the prior week.

Can I change the day and time it runs?

Yes. The schedule is a simple field. Move it to Tuesday, run it after your morning standup, or shift to a bi-weekly cadence.

Will it overwrite my existing Google Sheet?

No. New rows are appended to the bottom of your Newsletter KPIs tab so you keep a full historical log for charts and filters.

Can it post to more than one Slack channel?

Yes. Duplicate the Slack step and point each copy at a different channel, or route to a private DM for the founder and a broader channel for the team.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.