Google Analytics 4 + HubSpot Lifecycle Stage: Weekly Acquisition-Quality Digest to Slack via Pipedream with Claude Narrative
Stop reporting traffic numbers. Report whether the traffic you paid for last week actually became pipeline — with a one-paragraph executive summary written by Claude.
The stack in the order it runs — data flows from the source through to where it lands.
Traffic metrics without conversion context are decorative. Most early-stage teams report GA4 sessions and source/medium breakdowns in their weekly digest, but the number that matters is: of the 400 people who came from that LinkedIn campaign, how many became a HubSpot contact, how many moved to 'Marketing Qualified Lead', and how many are now in 'Deal Created'? Without joining GA4 acquisition data to HubSpot lifecycle stages, you're optimizing campaigns against bounce rate instead of pipeline quality.
Pipedream handles this multi-API join cleanly: GA4 Data API in one step, HubSpot Contacts search in the next, a join on UTM source/medium stored as HubSpot contact properties, then Claude to write the narrative. Claude earns its place here because the output isn't just numbers — it's a paragraph that says 'Organic search drove 3x the MQL rate of paid social this week despite 40% less volume, suggesting your SEO content is attracting higher-intent buyers.' That's what executives actually read.
The critical prerequisite: your HubSpot contacts must have `utm_source`, `utm_medium`, and `utm_campaign` stored as contact properties. This requires either HubSpot's tracking code on your site — which auto-captures UTMs to cookies and passes them on form submit — or a custom implementation. If UTMs aren't being stored in HubSpot, you can only do this join at the session level using GA4's conversion events, which is less accurate. Check your HubSpot contact properties before building anything here.
One caveat on Claude: the step receives only aggregated counts and source labels — no emails, names, or individual user data. If your legal team requires stricter data controls, replace the Claude narrative step with a hardcoded template string and skip the AI layer entirely. Don't use Claude here if your contact or revenue data is PII-sensitive and you haven't reviewed Anthropic's data processing terms.
The stack (5)
Web analytics most teams already run.
The Data API makes traffic a free input for weekly ops digests.
How it runs
- 1
Create a Pipedream workflow triggered every Monday at 7 AM
In Pipedream, create a new workflow with a Schedule trigger: `0 7 * * 1` (every Monday at 7 AM UTC). Name it 'Weekly Acquisition Quality Digest'. Set timeout to 120 seconds. This runs before Monday morning standups and covers the previous Monday–Sunday week. In the first code step, define `WEEK_START` and `WEEK_END` as ISO date strings for last week using `luxon`: `DateTime.now().minus({weeks:1}).startOf('week').toISODate()` and `.endOf('week').toISODate()`.
- 2
Pull GA4 session and conversion data by source/medium
Add a Node.js code step. Authenticate against the GA4 Data API using a Google service account — create one in GCP → IAM → Service Accounts, grant it 'Viewer' on your GA4 property, download the JSON key, store it as a Pipedream environment variable. POST to `https://analyticsdata.googleapis.com/v1beta/properties/YOUR_GA4_PROPERTY_ID:runReport` with dimensions `['sessionSourceMedium', 'sessionCampaignName']` and metrics `['sessions', 'conversions', 'engagedSessions']` filtered to your date range. Parse the response into an array of objects: `{ source_medium, campaign, sessions, conversions, engaged_sessions }`. Sort descending by conversions.
- 3
Pull HubSpot contacts created last week grouped by UTM source
Add a HubSpot API call step. Use the Contacts Search endpoint: `POST https://api.hubapi.com/crm/v3/objects/contacts/search` with filter `createdate BETWEEN WEEK_START AND WEEK_END`. Properties to retrieve: `email`, `hs_lead_status`, `lifecyclestage`, `utm_source`, `utm_medium`, `utm_campaign`. Paginate using the `after` cursor — HubSpot returns up to 100 per page. Group results by `utm_source + '/' + utm_medium` in a reduce operation. For each source/medium group, count contacts by lifecycle stage: `subscriber`, `lead`, `marketingqualifiedlead`, `salesqualifiedlead`, `opportunity`.
- 4
Join GA4 sessions to HubSpot contact counts by source/medium
In a code step, merge the GA4 array and HubSpot grouped object on the `source_medium` key. For each source/medium that appears in both, compute `contact_rate = contacts_created / sessions * 100`, `mql_rate = mqls / contacts_created * 100`, `session_to_mql = mqls / sessions * 100`. Flag any source/medium where `session_to_mql` is more than 2 standard deviations above or below the mean across all sources — these are your 'overperformers' and 'underperformers'. Sources that appear in GA4 but not HubSpot (zero contacts created) are likely low-intent traffic — flag these separately.
- 5
Identify top 3 performing and bottom 2 performing acquisition channels
Sort the joined array by `session_to_mql` descending. Take the top 3 (highest conversion quality) and bottom 2 (lowest — but only include a channel if it had at least 50 sessions, to avoid flagging a channel that sent 3 visitors). Also pull out the single channel with the highest raw MQL count — this is often different from the highest rate channel and worth calling out separately. Store these as named variables: `topChannels`, `bottomChannels`, `highestVolume` for use in the Claude prompt.
- 6
Send data to Claude for a one-paragraph narrative summary
Add a step using the Pipedream Claude (Anthropic) action or an HTTP Request to `https://api.anthropic.com/v1/messages`. Model: `claude-3-5-haiku-20241022` — fast and cheap for structured summaries. System prompt: 'You are an operations analyst writing a one-paragraph weekly acquisition quality summary for a SaaS founder. Be direct and specific. Name the channels and numbers. Recommend one action.' User message: paste the stringified `topChannels`, `bottomChannels`, `highestVolume`, total contacts, total MQLs, and week dates. Max tokens: 200. Output must be a single clean paragraph — no headers, no bullet points.
- 7
Build the Slack message with data table and Claude narrative
Construct the Slack message in a code step. Structure: header `📊 *Weekly Acquisition Quality – {{WEEK_START}} to {{WEEK_END}}*`, then a summary stats row (`Total sessions: X | New contacts: Y | MQLs: Z | MQL rate: W%`), then a Slack-formatted table for the top 5 source/mediums — use monospace formatting with backticks for alignment: channel name, sessions, contacts, MQLs, session-to-MQL%. Then a divider, then the Claude narrative paragraph labeled `*AI Summary:*`. Cap the table at 5 rows. More than that and nobody reads it.
- 8
Post to Slack and optionally create a HubSpot note
Post to your `#growth` or `#product-metrics` Slack channel using the Slack API `chat.postMessage` endpoint with `mrkdwn: true`. Optionally, create a HubSpot engagement note (type: NOTE) on your 'Marketing Operations' company record using `POST /crm/v3/objects/notes` — set `hs_note_body` to the Claude narrative and `hs_timestamp` to now. This creates a searchable history of weekly performance without a separate data store. Skip this if you don't need it, but it gives your marketing team a CRM-native record they can reference in deal reviews.
Want me to build this for you instead?
Product Audit and CTO Mode run out of this same thinking. If you’re reading this thinking “I want this, but in my product” — let’s talk.
See servicesMore like this
Gmail Thread Aging + Stripe Invoice Overdue: Unified AR Follow-Up Digest to Slack via Zapier
Surface overdue Stripe invoices and the exact age of your last Gmail thread with that customer — every morning at 8:30, automatically — so AR follow-up stops living in someone's head.
Intercom Ticket Volume + Razorpay Failed Payments: Daily Support-Cost-per-Revenue Alert to Slack via Zapier
Catch the support cost blowout from Razorpay failed payments before your agents are already buried.
Outlook Calendar Load + HubSpot Deal Velocity: Weekly Ops Digest to Microsoft Teams
Every Monday at 07:30, your revenue team gets one Teams card: last week's deal pipeline movement next to each rep's actual meeting load — so you stop guessing whether low close rates are a pipeline problem or a capacity problem.