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.
Google BigQuery
HubSpotOperationsSalesData SyncLead EnrichmentEvery 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.
Additional information
What does this prompt do?
- 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 do I need to use this?
- 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 can I 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
Frequently asked questions
Do I need a reverse-ETL tool like Hightouch or Census for this?
Will this create new HubSpot contacts?
What happens if the same email shows up in BigQuery twice?
Does this work on HubSpot Free?
How long does a nightly run take?
Related templates
Stop paying a reverse-ETL vendor to move four columns.
Sync warehouse scores into HubSpot on your own schedule, with your own SQL, and no per-row pricing.