See llms.txt for all machine-readable content.

Back to Templates

Route scored GTM signals from BigQuery to HubSpot with Telegram and Google Sheets

Created by

Created by: Utkarsh Kapoor || utkarsh-automates
Utkarsh Kapoor

Last update

Last update 2 days ago

Categories

Quick overview

This workflow runs every 4 hours to query scored GTM signals from Google BigQuery, syncs high-scoring accounts into HubSpot as contacts, refreshes a Google Sheets archive of the flagged rows, and posts a tiered status report (or no-signal notice) to Telegram with error alerts.

How it works

  1. Runs every 4 hours on a schedule.
  2. Queries Google BigQuery for rows in the scored_signals table with signal_score ≥ 50, sorted by score descending.
  3. If no results are returned, sends a “no new signals” message to Telegram.
  4. If results exist, formats each row into HubSpot contact fields (including derived email and signal metadata).
  5. Upserts the contacts into HubSpot in batches of 10, setting custom properties such as signal_score, signal_type, and last_signal_date.
  6. Builds a Telegram summary grouped by signal tier (Critical/Hot/Warm) and posts the report to Telegram.
  7. Clears the target Google Sheets tab (keeping the header row) and appends the latest BigQuery results to keep a fresh archive of flagged signals.
  8. If the workflow errors, sends a Telegram alert with the failing step and error message.

Setup

  1. Add a Google Cloud service account credential in n8n with BigQuery and Google Sheets access, and share the target Google Sheet with the service account email.
  2. Update the BigQuery project ID and SQL query to point to your dataset/table, and ensure the table includes fields like company_id, company_name, signal_score, signal_tier, signal_type, and last_signal_date.
  3. Add a HubSpot private app token credential with Contacts read/write permissions and create the HubSpot contact properties signal_score, signal_type, and last_signal_date.
  4. Add a Telegram bot credential and replace YOUR_TELEGRAM_CHAT_ID in all Telegram messages.
  5. Set the Google Sheets document ID and sheet/tab identifier in both Sheets steps, and ensure the header columns match the BigQuery output you want to archive.