GTM Signal Engine

The problem

Sales teams waste time guessing which accounts are ready to expand. The data that answers that question already sits in the product's usage logs, but nobody looks at it, because nobody built the pipeline that would surface it. This project is that pipeline, built end to end: SQL scoring, workflow automation, CRM sync, and real-time alerts.

Proof it's live

This isn't a static mockup. The scheduler below fires on its own every four hours, and this is a real alert it sent to Telegram, unedited.

How scoring works

Every account is checked daily against three expansion signals. Each signal carries a weight, and accounts triggering more than one get a multiplier, so a company showing every sign of being ready to buy rises straight to the top of the list.

The SQL

The scoring logic is a single BigQuery view built from three stacked queries: rolling averages, signal detection, and composite scoring. Here's the core of it.

-- 7-day rolling average of daily active users, per company
AVG(daily_active_users) OVER (
    PARTITION BY company_id
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS dau_7d_avg,

-- Usage spike: today's DAU is more than 2x the rolling average
(dau_7d_avg > 0 AND daily_active_users > dau_7d_avg * 2) AS has_usage_spike,

-- Seat crunch: utilization above 80 percent
(SAFE_DIVIDE(seats_used, seats_purchased) * 100 > 80) AS has_seat_crunch,

-- Feature milestone: cumulative adoption hits 10+ features
(MAX(features_used) OVER (
    PARTITION BY company_id
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) >= 10) AS has_feature_milestone,

-- Weighted base score, then a multiplier for accounts firing
-- more than one signal at once
(CASE WHEN has_usage_spike THEN 40 ELSE 0 END)
+ (CASE WHEN has_seat_crunch THEN 35 ELSE 0 END)
+ (CASE WHEN has_feature_milestone THEN 25 ELSE 0 END) AS base_score,

CASE
    WHEN signals_firing >= 3 THEN 1.3
    WHEN signals_firing = 2 THEN 1.2
    ELSE 1.0
END AS multiplier,

LEAST(100, ROUND(base_score * multiplier)) AS signal_score

Architecture

One BigQuery view feeds three independent consumers: an n8n workflow that syncs CRITICAL and HOT accounts to HubSpot and sends Telegram alerts every four hours, an auto-synced Google Sheets export, and this live dashboard.

Live dashboard

Filter by tier or plan, sort the account table by score, and see exactly which companies the system is flagging right now.

Try it yourself

Download the sample dataset, upload it to a free BigQuery Sandbox project, and run the scoring view yourself. No billing account required.

Built with