Push BigQuery audience segments into HubSpot with review

Build me an audience activation desk where our growth ops person turns warehouse data in Google BigQuery into HubSpot segments without waiting on an analyst. One rule shapes the whole app: nothing writes to HubSpot unless I click push. Running an audience, diffing it, checking the CRM, and asking for an explanation are all read only, and the push button is the single place where data leaves the app and reaches customers.

The home view is a library of saved audience definitions. Each definition stores a name, a description, and the SQL behind it, and I can create, edit, and delete definitions inside the app. List them with the name, description, when the audience was last run, when it was last pushed, and how many contacts went out in that last push.

Opening a definition runs its SQL with the Google BigQuery Run Query (Synchronous) operation and shows the matched customers in a table, with the total matched count displayed prominently at the top. Each query is expected to return an email column plus any number of score or attribute columns. Under the count, show a diff against the membership recorded at the last push: who newly entered the segment and who dropped out since I last activated it. Mark new and dropped rows in the table and let me filter down to just those groups.

Before any write can happen, run HubSpot Search Contacts across the matched email addresses and show me, in a banner above the table, how many of them do not exist in the CRM yet. This check runs automatically as part of opening the segment, since it only reads.

I can select individual rows with checkboxes or take the whole segment, then click push. Pushing calls HubSpot Batch Upsert Contacts keyed on email, in batches of up to 100 records, stamping segment membership onto a contact property along with the score columns returned by the query, so our marketers can build active lists and workflows off those properties inside HubSpot itself. Show a confirmation dialog first summarizing how many contacts will be written and how many are new to the CRM. No other part of the app writes to HubSpot contacts.

Log every push inside the app: which audience, who ran it, when, how many contacts were written, and how many were skipped or failed. Show the log on the segment detail view and also as a global log across all audiences. The membership snapshot captured at each push is what the next run diffs against.

From a segment I can also spin up follow-up work. Let me pick the top accounts in the audience, resolve their owners with HubSpot Search Companies and HubSpot List Owners, and create follow-up tasks against those owners with HubSpot Create Task, with a subject and due date I set in the app.

Add an Explain this segment button that kicks off a background agent. The agent profiles the audience against a baseline population, running further queries with Google BigQuery Run Query (Synchronous) and using Google BigQuery Get Table to understand the schema and columns of the underlying tables. It writes a plain-English summary back into the app covering who is actually in this audience, how it differs from the baseline, and what changed since the last push. Store the summary on the segment and show it on the detail view with its run history. When the agent finishes, it also posts that summary to our growth channel with the Slack Bot Send a Message operation. The agent reads from BigQuery only and never writes to HubSpot.

Persist the saved audience definitions, the membership snapshot from each push, the full push log, and the agent summaries. Record the signed-in user on every push and every agent run so the log always shows who did what.