BigQuery views

The bigquery/ directory at the repository root holds eleven .sql files. Each is a complete CREATE OR REPLACE VIEW (or TABLE) statement, checked in so the definition is reviewable, diffable, and deployable by hand.

These files are not deployed by CDK or SAM. Nothing in the pipeline creates or refreshes them. A change to a .sql file takes effect only when someone runs it against BigQuery:

bq query --project_id=bigqueryexport-183608 --use_legacy_sql=false < bigquery/v_lost_deal_reeng.sql

A handler reading a view that has drifted from its checked-in definition is the failure mode to watch for — the code review passed, the query did not change. Re-run the file after merging any change to it.

The five consumed by handlers

These four views plus one summary drive live behaviour. The membership and priority rules inside them are documented per segment in Re-engagement segments; this page is the structural reference.

View Consumer Segment

v_amazon_seller_contact_engagement_summary

phone-agent — Phone.CampaignTargetReEngagement

Amazon seller campaign

v_email_clicked_re_engagement_summary

morpheus-hubspot — Hubspot.EmailClickReEngagement

Email click

v_supplier_account_created_reeng

morpheus-hubspot — Hubspot.SupplierAccountReEngagement

Supplier account created

v_unattended_meeting_reeng

morpheus-hubspot — Hubspot.UnattendedMeetingReEngagement

Unattended meeting

v_lost_deal_reeng

morpheus-hubspot — Hubspot.LostDealReEngagement

Lost or stalled deal

All five live in bigqueryexport-183608.outreach_app.

v_amazon_seller_contact_engagement_summary

Joins outreach_app.CampaignTarget and outreach_app.Call against HubSpot contact and deal state to find campaign targets whose call disposition warrants another attempt.

id                              disposition
hubspotcontactid                contactid
lifecycle_stage
deal_id                         deal_status         deal_stage      deal_owner
last_call_engagement_timestamp  engagement_owner_id engagement_disposition
last_active_task_id             task_created_date   task_due_date   task_status
outreach_last_call_timestamp    priority

priority is emitted by the view, but phone-agent recomputes its own in Python rather than trusting it — the only one of the five segments that does. The last_active_task_id and task_status columns are the warehouse’s view of suppression; the handler still re-checks PostgreSQL before creating a task.

v_email_clicked_re_engagement_summary

Contacts who clicked a link in a campaign email, joined from outreach_app.email_campaign_targets to HubSpot contact, deal, and engagement state.

id                              email               contactid
hubspot_contact_id              lifecycle_stage
deal_id                         deal_status         deal_stage      deal_owner
last_call_engagement_timestamp  engagement_owner_id engagement_disposition
last_active_task_id             task_created_date   task_due_date   task_status
last_clicked_at                 priority

Structurally near-identical to the Amazon seller view — the same deal, engagement, and task columns — differing in its entry signal (last_clicked_at instead of a call disposition) and in carrying email for identity resolution.

v_supplier_account_created_reeng

Deals where a supplier account was created but the funnel stalled.

dealid              contact_id          first_name      last_name
email               phone               hubspot_owner_id
deal_stage          deal_status         pipeline
created_date        deal_amount
last_call_date      total_calls         disposition_id  disposition_label
days_since_last_call                    priority_score

The three deal-centric views (supplier_account_created, unattended_meeting, lost_deal) share a shape the contact-centric ones do not: they carry the contact’s first_name, last_name, email, and phone inline, so a handler can create a HubSpot task with a readable subject line without a second lookup.

priority_score here is a raw numeric score, converted to a task priority band in Python — see score-to-priority conversion.

v_unattended_meeting_reeng

Deals with a booked meeting the prospect did not attend. The widest of the five.

dealid              contact_id          first_name      last_name
email               phone               hubspot_owner_id    bdr_rep
deal_stage          deal_status         pipeline
created_date        deal_amount
last_meeting_date   meeting_outcome     total_meetings
last_activity_date  total_activities    days_since_last_activity
priority_score

bdr_rep is unique to this view: a no-show is routed back to the BDR who booked the meeting rather than through the round-robin, because that person already has the context. See Ownership and round-robin.

meeting_outcome distinguishes a genuine no-show from a rescheduled or cancelled meeting.

v_lost_deal_reeng

Closed-lost deals worth revisiting.

contact_id          first_name          last_name       email       phone
contact_owner_id
dealid              deal_stage          closed_lost_reason
deal_last_updated   last_modified_date  hubspot_owner_id
last_call_date      total_calls         disposition_id  disposition_label
days_since_last_call                    priority_score

closed_lost_reason is what makes the segment selective — a deal lost on price or timing is worth another call, one lost as "not a fit" is not. It carries both contact_owner_id and hubspot_owner_id because a lost deal’s owner and its contact’s owner frequently differ after a reassignment.

Reporting views

Defined, correct, and consumed by nothing in the pipeline. They exist for dashboards and manual analysis.

Object Purpose

v_call_summary

Per-call reporting. Reads hubspot4.v_engagements_with_owner, a portal-managed view that is not defined in this repository.

v_email_campaign_summary

Per-campaign email totals — sends, opens, clicks, bounces.

v_email_campaign_daily_summary

The same metrics bucketed by day.

v_email_campaign_daily_summary_unpivoted

Long-format variant of the daily summary, one row per metric, for charting tools that want tidy data.

v_email_campaign_sankey

Stage-to-stage transition counts for a funnel diagram.

vm_contacts_summary

A table, not a view — CREATE OR REPLACE TABLE in hubspot4.

vm_contacts_summary is materialised rather than virtual: it collapses HubSpot contact version history and joins each contact’s most recent CALL engagement, which is expensive enough that dashboards querying it repeatedly would be wasteful. The vm_ prefix marks the distinction.

Being a table, it is only as fresh as its last manual rebuild. Nothing schedules it. Anything reading it should treat its contents as a snapshot of unknown age.

v_call_summary depends on hubspot4.v_engagements_with_owner, which is managed in the BigQuery console. A change there can break the view with no corresponding commit here.

Conventions

Deduplicate first

Every view over hubspot4 opens with the ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedat DESC) … WHERE rn = 1 collapse. The export is append-only. See the deduplication pattern.

Emit a numeric score, not a band

Views produce priority or priority_score as a number. Converting it into a task priority is Python’s job, so the thresholds can be tuned without re-running DDL.

Return snake_case

View columns are snake_case (last_call_engagement_timestamp), unlike the camelCase of the operational schema. They are read positionally or by name into dictionaries, never mapped to ORM models.

Suppression columns are advisory

last_active_task_id, task_status, and task_due_date exist so a view can pre-filter, but they are as stale as the export. Every handler re-checks has_active_task_for_contact() against PostgreSQL before writing.

Changing a view

  1. Edit the .sql file under bigquery/.

  2. Run it against bigqueryexport-183608 — nothing deploys it for you.

  3. Verify the column list still matches what the handler reads; a renamed column produces a KeyError at runtime, not at deploy time.

  4. If the membership or priority rule changed, update Re-engagement segments and Daily call lists in the same pull request.