Refresh HubSpot health scores from product usage every night

Every weekday at 2am, refresh customer health scores in HubSpot from product usage data in Elasticsearch, then post a Slack digest of any account that flipped to red overnight so our CSMs see the at-risk list before standup.

Step 1: pull product activity from Elasticsearch. Use Elasticsearch's Search Documents to aggregate the last 30 days of events per account from our product events index. For each account, compute: active days (distinct days with at least one event), breadth of features touched (distinct feature or event name count), error events hit, and last-seen date. Group by account_id (or whichever field identifies the customer account in our index). Skip any account with fewer than 25 events in the window to avoid noise from brand-new trials.

Step 2: compute a health score and a risk reason. For each remaining account, calculate a 0 to 100 health score and assign a tier: green (70 to 100), yellow (40 to 69), red (0 to 39). Use judgement to weight the signals: a high active-days count and broad feature breadth push the score up, a stale last-seen date and lots of errors push it down. Then write a one-sentence plain-English risk reason that names the dominant driver (for example, "hasn't logged in for 12 days and only one user is active" or "error rate doubled this week on the import flow"). Include the top risk driver as a separate short label too.

Step 3: write the score back to HubSpot. For each account, use HubSpot's Search Companies to find the matching company by domain. If you find exactly one match, use Update Company to write three properties: the numeric health score, the tier (red, yellow, green), and the risk note. If the account also has known individual users tied to it, use Batch Update Contacts to stamp the same score on those contacts so the field is queryable per-user. Two important rules: never overwrite a manually-set health score (skip the write if a manual-override flag is true), and only call Update Company when the new tier actually differs from the current tier (a score nudge inside the same tier is not worth an update).

Step 4: post the morning digest in Slack. Use Slack Bot's Send a Message to post to the customer-success channel. The message should list every account that flipped to red overnight (yesterday's tier was yellow or green, today's is red), one line per account, including: company name, the score delta (for example, "score 72 → 41"), the top risk driver, and a link to the HubSpot company record. If nothing flipped to red, post a short "no red flips overnight" line so the channel knows the job ran. At the bottom of the message, list any accounts the agent could not confidently match to a HubSpot company so a CSM can fix the mapping.

Keep the run idempotent: if it runs twice in the same morning, it should arrive at the same scores and not double-post the digest. Log a short summary at the end (how many accounts scored, how many updated, how many flipped to red, how many skipped for manual override) so I can see at a glance whether the run was healthy.