Nightly BigQuery to HubSpot contact score sync
Push warehouse-computed engagement scores, churn risk, LTV, and top feature into HubSpot contacts every night, keyed by email.
Integrations
Google BigQuery
HubSpot
Type
Categories
- Operations
- Sales
Every night at 2am, run a BigQuery SQL query using Run Query (Synchronous) that returns every contact whose warehouse-computed scores changed in the last 24 hours. The query should return one row per email address with these columns: email, product_engagement_score, churn_risk_band, ltv_bucket, and most_used_feature. Filter on a last-updated timestamp so only rows that changed in the last 24 hours come back.
Before making any HubSpot calls, deduplicate the result set by email. If the same email appears more than once, keep the row with the most recent updated-at timestamp and drop the rest so we never overwrite fresh values with stale ones.
For each deduped row, use HubSpot Search Contacts with an email-equals filter to find the matching contact. If the search returns no results, count it as skipped and move on — do not create the contact. If the search returns a match, use HubSpot Update Contact against that contact ID and write the four warehouse values into these custom contact properties: product_engagement_score, churn_risk_band, ltv_bucket, most_used_feature. Assume the properties already exist in HubSpot.
Track three counters as you go: enriched (contact found and updated successfully), skipped (no HubSpot contact for that email), and errored (search or update failed for a reason other than not-found — for example a rate limit or a validation error). On any individual row error, log the email plus the error message and continue with the next row. Do not abort the run.
When every row is processed, log a single one-line summary in this shape: "Enriched X, skipped Y, errored Z out of N contacts." That is the only output of the workflow.
What it does
- Reads changed contact scores from your data warehouse every night and pushes them into HubSpot
- Matches warehouse rows to HubSpot contacts by email and updates four custom properties: engagement score, churn risk band, lifetime value bucket, and most-used feature
- Handles duplicate emails by keeping the freshest score, and quietly skips people who are not in HubSpot yet
- Ends each run with a one-line summary of how many contacts were enriched, skipped, or errored
What you’ll need
- A Google BigQuery project where your product analytics or scoring model lives
- A HubSpot account with permission to read and update contacts
- Four custom contact properties already created in HubSpot: product engagement score, churn risk band, LTV bucket, and most-used feature
- A SQL query (or view) in BigQuery that returns one row per email with the four score columns and a last-updated timestamp
How to customize it
- Change the schedule — run every hour, twice a day, or only on weekdays instead of nightly at 2am
- Swap in your own SQL or point at a different BigQuery view to change which scores get synced or which rows qualify as changed
- Rename the four HubSpot properties, add more columns, or remove ones you do not use
- Send the end-of-run summary to Slack or email instead of only logging it
Use cases
- Data Sync
- Lead Enrichment
