Daily Snowflake churn risk scores on every HubSpot contact

Every day at 8am in my timezone, score every active customer for churn risk from product usage in Snowflake and write the result back onto their contact in HubSpot, then post a Slack summary of the worst offenders to my CS channel. Trigger is a daily cron.

Step 1. Pull usage signals from Snowflake. Use Execute SQL Statement on Snowflake to run a single query that returns, for every active customer account, the following columns: account_id, primary contact email, account owner email, MRR or plan tier, logins in the last 14 days, logins in the last 30 days, number of distinct features used in the last 30 days, error events in the last 14 days, open support tickets, support tickets in the last 30 days, and days since last meaningful activity (an action like a login, a save, or an export, not just a passive ping). Only include accounts that are currently in an active subscription state. I will tell you the exact table and column names during setup. Use bind variables for any date math (today minus 14, today minus 30) and pass an explicit warehouse if my default warehouse is not set.

Step 2. Reason about risk per account. For each row, decide a churn risk tier of high, medium, low, or expanding, give a 0 to 100 numeric score (higher means more at risk), and write a one-sentence human-readable reason that names the specific signal driving the call. Examples of the reasoning you should do: an account with zero logins in the last 14 days, an open P1 ticket, and falling feature breadth is high risk; an account whose logins and feature adoption are climbing month over month is expanding; a steady-state account with normal usage and no support load is low. Do not invent signals that were not in the SQL result. If a signal is missing, say so in the reason.

Step 3. Write the scores back to HubSpot. Use Batch Upsert Contacts on HubSpot to update the matching contacts by email (idProperty = email), in batches of up to 100. Write three properties: churn_risk_tier (string: high, medium, low, expanding), churn_risk_score (number 0 to 100), and churn_risk_reason (short string, one or two sentences). These three custom contact properties should already exist in HubSpot. If you cannot find a contact for an email, skip that row and log it. Do not touch any other HubSpot properties.

Step 4. Post a Slack digest of the top 10 at-risk accounts. Use Send a Message on Slack to my CS channel (I will give you the channel ID during setup). Sort by churn risk score descending, take the top 10, and for each one include: account name, plan or MRR, risk tier and score, the one-line reason, and an @-mention of the account owner. Map the owner by looking up their Slack handle from their HubSpot owner email. Use Slack mrkdwn formatting (single asterisks for bold). Lead with a one-line summary like 'Daily churn watch: 10 accounts need a look today.' If there are zero high-risk accounts, post a single line saying 'No high-risk accounts today.' instead of a long empty digest.

Matching rule across systems: join Snowflake rows to HubSpot contacts by primary contact email (lowercased, trimmed). If my Snowflake schema uses a different shared key (for example a HubSpot contact ID stored as a column), ask me at setup which column to match on instead.

Be careful about scale. There may be thousands of active accounts. Stream through them in batches, respect HubSpot's batch upsert size of 100 per call, and do not include every account in the Slack post, only the top 10. Log how many accounts were scored, how many were upserted into HubSpot, and how many were skipped because no HubSpot contact existed.