Nightly BigQuery to HubSpot contact enrichment

PromptCreate

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 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

FAQs

Do I need a reverse-ETL tool like Hightouch or Census for this?

No. This workflow is the reverse-ETL pattern those tools sell — reading changed rows from BigQuery on a schedule and writing them into HubSpot as contact properties. You keep control of the SQL and the field mapping without adding another vendor.

Will this create new HubSpot contacts?

No. If an email from BigQuery does not exist in HubSpot, the workflow skips it and counts it toward the skipped total. That keeps HubSpot clean of prospects who never engaged through your normal signup or marketing flow.

What happens if the same email shows up in BigQuery twice?

The workflow deduplicates by email before touching HubSpot and keeps the row with the most recent updated-at timestamp, so HubSpot always gets the latest scores rather than a random duplicate.

Does this work on HubSpot Free?

Yes, as long as the four custom contact properties exist on your account. Contact properties and the contact search and update capabilities used here are available on every HubSpot tier.

How long does a nightly run take?

It depends on how many contacts changed in the last 24 hours. The BigQuery query returns in seconds for typical volumes, and HubSpot updates run at a few contacts per second — a few thousand changed contacts a night is a comfortable working range.