Quote Follow-Up Nudge Cadence → Google Sheet

Audience: developers, agents, support · Scope: a cron script that reconciles a Google Sheet of quotes "due for a follow-up nudge" (add due, remove no-longer-due), so an n8n workflow can act on them · Last reviewed: 2026-07-24

TL;DR

Every quote sits in a Pipedrive deal stage. Left alone too long, an agent should be nudged to open the Quote Follow-Up tool and move it along. How long is "too long" depends on the stage and the quote's value (a bigger deal is chased more often). A cron script (cb_quote_nudge_build_sheet.php) recomputes, each run, the set of quotes that are overdue and overwrites a Google Sheet with one row per due quote — an n8n workflow reads that sheet and starts the nudge. Because the whole tab is rewritten every run, quotes that became due are added and quotes that no longer need a nudge (stage moved, won/lost, etc.) are removed automatically.

per-stage cadence (days)         value buckets ($ thresholds)      the clock
#__..._pipedrive_stages          Quote Follow-Up settings          quotes.pipedrive_stage_changed_on
 nudge_days_small/mid/large       CAD + USD (USD = fallback)        (UTC, stamped by BcQuoteStageStamp)
        └───────────────┬──────────────────┴───────────────────────────────┘
                        ▼
     cb_quote_nudge_build_sheet.php  →  ConfigboxModelBcquotenudge::buildAndReconcileSheet()
                        │   (daily; safe hourly; MySQL named lock; blank sheet setting = OFF)
                        ▼
     Google Sheet (quote_nudge_sheet)  ── full rewrite each run ──►  n8n reads it

What "due" means

For each eligible quote:

1. Value bucket — the quote's discounted total (getQuoteTotalDiscounted, the same number pushed as the Pipedrive deal Value) vs. the configured thresholds. small < mid_threshold, mid = [mid_threshold, large_threshold], large > large_threshold. Thresholds are per currency: CAD quotes use the CAD thresholds; USD and every other currency use the USD thresholds (USD is the fallback).
2. Cadence — stage.nudge_days_<bucket> (days). Blank = no nudge for that stage/bucket (e.g. the booked/in-production stage has all three blank, so those quotes never appear).
3. Overdue? — days_in_stage ≥ nudge_days, where days_in_stage = now(UTC) − anchor and the anchor is the agent's most recent engagement (see below).

The clock, and the bootstrap fallback

The clock is the agent's most recent engagement with the quote — the later of two UTC stamps:

• quotes.pipedrive_stage_changed_on — the deal stage last actually moved, stamped by BcQuoteStageStamp on every stage change (landing page + webhook).
• quotes.pipedrive_agent_feedback_updated_on — the last follow-up submission or a "nothing has changed" confirmation on the landing page (touchFeedbackTimestamp).

Taking the later of the two (ConfigboxModelBcquotenudge::anchor()) means an agent who responds to a nudge — even just to confirm nothing changed — restarts the cadence so they aren't re-nudged immediately. Quotes with neither stamp (both NULL, e.g. quotes that predate them) fall back to the quote's created_on as the "in stage since" anchor, so the feature works for the existing open pipeline from day one. Each sheet row's anchor_source column says which was used (stage_changed_on, feedback_updated_on, or created_on).

First-run note: with the created_on fallback, the very first runs surface the whole current backlog of already-overdue open quotes at once (that is the point — they are genuinely overdue). If a gentler rollout is wanted, tune the cadence numbers up initially, or change the fallback policy. This is the one deliberately-open behaviour; see Open questions.

Eligibility

A quote is a candidate when (mirrors the cold-quote query, ConfigboxModelBcpipedrive::getColdQuoteDeals):

• is_latest_revision = '1' — superseded revisions are ignored.
• pipedrive_status = 'open' — won/lost/blank are ignored.
• its pipedrive_stage_id maps to a known stage row (INNER JOIN on #__configbox_external_pipedrive_stages).

Then two configurable deal-type gates (classification reuses the exact Test/Internal/Regular rules of the deal push, ConfigboxModelBcquotes::updatePipedriveDeal):

• Internal quotes (agent billing email @betacalco.com) — not nudged by default (quote_nudge_include_internal, default 0).
• Test quotes (quote/opportunity name contains "test") — nudged by default (quote_nudge_include_test, default 1).
• Classification order is test → internal → regular (same as the deal push): a quote whose name contains "test" is a test quote even if the agent email is @betacalco.com.

A stage with no cadence in any bucket is skipped cheaply (before the per-quote value/classification work).

Sheet columns

One row per due quote, header in row 1 (frozen, with a basic filter). Columns, in order:

Column	Source
quote_serial	quote serial
quote_id	numeric quote id
project_id	opportunity_id (blank when not part of an opportunity)
pipeline_id / pipeline_name	the stage's pipeline (#__..._pipedrive_pipelines)
stage_id / stage_name	the quote's native Pipedrive stage
deal_probability	the stage's deal_probability
value	discounted quote total (the deal Value)
currency	ISO code (CAD / USD / …)
value_bucket	small / mid / large
nudge_interval_days	the cadence that applied (stage × bucket)
stage_changed_on	the anchor datetime used (UTC) — the later of pipedrive_stage_changed_on / pipedrive_agent_feedback_updated_on (column name is legacy)
anchor_source	stage_changed_on, feedback_updated_on, or created_on (the fallback)
days_in_stage	whole days since the anchor
agent_user_id / agent_name / agent_email	the quote's agent (its user_id)
rsm_email	the branch RSM email (fallback pipe@betacalco.com)
branch_id	agent's branch (configbox_users.custom_2)
follow_up_link	a fresh tokenized magic link for the agent (BcQuoteFollowupAuth::generateLink) — a bearer capability, never logged
generated_at	run timestamp (UTC)
last_feedback_on	the quote's last follow-up response time (UTC) — a submission or a "nothing has changed" confirmation (pipedrive_agent_feedback_updated_on). The anchor above already folds this in; this is the raw stamp as its own column, mirroring the stage-sync sheet's last_feedback_on (col Z)
quote_title	the quote name (the opportunity name when part of one) — $quote->name from the loaded quote
quote_status	the ConfigBox quote status (CURRENT / EXPIRED) — $quote->status_name. Not the Pipedrive deal status (open/won/lost), which is always open for nudge candidates
rsm_name	the branch RSM name — off the same branch record already used for rsm_email, but blank when the branch has none (no pipe@betacalco.com-style fallback, unlike rsm_email)

The trailing three (quote_title / quote_status / rsm_name) mirror the stage-sync sheet's cols W/X/Y, sourced identically.

Where the data is entered

• Per-stage cadence numbers — the backend Imported Pipedrive Stages list (Settings → Quote Follow-ups; ConfigboxModelAdminbcpipedrivestages): each stage's edit form has three editable Nudge days (small/mid/large) fields in its "Follow-up settings (editable)" group. They are admin-curated and preserved on re-import (the Pipedrive import only refreshes Pipedrive-owned columns). Leave a cell blank for "no nudge".
• Global knobs — admin → ConfigBox settings → Quote Follow-ups group (stored in #__configbox_external_settings_quote_follow_up):
  • Follow-up nudge sheet (quote_nudge_sheet, spreadsheetId#gid) — blank = feature OFF. The Google service account must have write access; the target must be a native Google Sheet.
  • Nudge value thresholds — mid/large for CAD and USD (default 10000 / 50000).
  • Include internal quotes in nudges (quote_nudge_include_internal, default 0) and Include test quotes in nudges (quote_nudge_include_test, default 1) — two Yes/No gates framed the same way (1 = include; Yes = these quotes ARE nudged). quote_nudge_include_internal was renamed from the old quote_nudge_exclude_internal (polarity flipped, behaviour preserved) by updates/0.5.75.php.

Setup / operations

1. Migrate. updates/0.5.73.php adds the stage cadence columns + the settings columns; updates/0.5.75.php renames the internal gate to quote_nudge_include_internal (polarity flipped). Both idempotent; run on the next page load.
2. Enter the cadence in the Imported Pipedrive Stages list (Settings → Quote Follow-ups), per the cadence slide.
3. Configure the Quote Follow-ups settings: the nudge sheet, the thresholds, the two gates. Point at a fresh/empty native Google Sheet tab with write access for the service account.
4. Add the cron (daily; safe to run hourly):

   15 6 * * * php /var/www/betacalco.com/cli/cb_quote_nudge_build_sheet.php

   Runs are mutually exclusive via a MySQL named lock (bc_quote_nudge_build), so an overlapping tick is a safe no-op. Each run prints the reconcile counts and, on the next line, Sheet: <url> — the sheet it just wrote (buildAndReconcileSheet() returns it as sheetUrl, built by ConfigboxModelGooglesheets::getSheetUrl(); empty, and so not printed, when no usable sheet is configured).

Turning it off: blank the quote_nudge_sheet setting (the script no-ops and leaves any sheet untouched).

Files

Path	What
…/updates/0.5.73.php	migration: stage cadence columns + settings columns
…/models/bcquotenudge.php	ConfigboxModelBcquotenudge — computes the due set (collectDueRows) and reconciles the sheet (buildAndReconcileSheet)
docroot/cli/cb_quote_nudge_build_sheet.php	cron entry point
…/models/adminbcpipedrivestages.php	the three editable nudge_days_* columns (preserved on import)
…/models/adminbcsettings.php	the quote_nudge_* settings (Quote Follow-Up group)

Reads: #__configbox_external_pipedrive_stages (cadence + stage name/probability), #__configbox_external_pipedrive_pipelines (pipeline name), #__configbox_external_user_quotes (eligibility + the pipedrive_stage_changed_on / pipedrive_agent_feedback_updated_on anchor stamps), #__configbox_users.custom_2 (branch). Writes only the Google Sheet.

Testing

Deterministic, capture-safe (everyday suite): tests/specs/agent/quote-nudge-due.spec.ts. Seeds a quote, drives the real follow-up form to put it on an Open stage, then flips that stage's cadence / the deal-type gates and asserts whether the engine considers the quote due — with no Google Sheet involved. It calls support.collectNudgeDueRow(serial) → the collect-due-rows test-support command, which runs the real ConfigboxModelBcquotenudge::collectDueRows scoped to one serial (the model's collectDueRows($settings, $serialFilter) gained an optional serial narrowing purely for this; production/cron still passes null) and returns { due, row (header-keyed), stats } without writing any sheet. Variations covered: a stage with no cadence is skipped (skipped_no_cadence); cadence 0 → due with the computed bucket/interval/anchor/link; far-future cadence → not due (skipped_not_due); the internal-exclusion gate drops an agent-owned (@betacalco.com) quote even when overdue (via set-nudge-gates); a Lost quote drops out of the candidate set (native status no longer open). See pipedrive/testing.md.

Live end-to-end (opt-in, writes the real dev Google Sheet): tests/specs/agent/quote-nudge-sheet-live.spec.ts, run with E2E_LIVE_SMOKE=1 (E2E_LIVE_SMOKE=1 npx playwright test specs/agent/quote-nudge-sheet-live.spec.ts --project=agent). It seeds a quote, drives the follow-up form to an Open stage, fakes that stage's cadence so the quote is due, runs the real reconcile and asserts the quote's row actually lands on the sheet (and, in the second test, that a not-due quote is absent until it becomes due). It relies on three test-support commands (BcTestSupport / tests/support/cli.ts): setStageCadence (fake a stage's nudge_days_*, returns the previous values to restore), buildNudgeSheet (run buildAndReconcileSheet), and readNudgeSheetRows / latestNudgeSheetRow (read the actual sheet back, header-keyed). Because the test-support endpoint runs the served code, the target environment must have this feature deployed and a quote_nudge_sheet configured. See the e2e guide and pipedrive/testing.md.

Design decisions (resolved with the team)

• Where the matrix lives: per-stage cadence columns on the imported stages table (edited in the existing backend list), not flat settings — the cadence is inherently per-stage and stays in sync with the imported stages automatically. The $ thresholds and the sheet/gates are global settings.
• Value + currency: the discounted quote total (getQuoteTotalDiscounted($id, false, false), matching the deal Value) and BcHelper::getQuoteCurrencyCode. Thresholds are separate for CAD and USD; USD is the fallback for any other currency.
• Eligibility: latest revision + pipedrive_status = 'open'. Internal excluded (configurable), test included (configurable).
• Reconcile by full rewrite: each run clears the tab and appends header + due rows (ConfigboxModelGooglesheets::clearSheet + appendSheetValues), so "remove no-longer-due" needs no row-delete API — the sheet always equals the current due set. (Deliberately not writeSheetValues: that resizes the grid, which Google rejects — "cannot delete all non-frozen rows" — when the due set is empty and the tab has a frozen header row.)
• Magic link included on every row.
• Cadence "floor" column on the slide was redundant (it equalled the large-value column) — dropped.

Open questions

• No-response / re-nudge policy: a quote stays "due" until the agent engages — its row persists every run until either the stage moves or the agent responds on the landing page. Responding restarts the clock: a normal update bumps pipedrive_stage_changed_on / pipedrive_agent_feedback_updated_on, and the "nothing has changed" button bumps pipedrive_agent_feedback_updated_on on its own (see landing-page.md), so a quote whose agent confirmed nothing changed drops out of the due set for another cadence period. If a repeat nudge every N days regardless of any engagement is wanted later, add a last_nudged_on written back per quote and factor it into the due test.
• NULL-clock bootstrap: currently falls back to created_on (see the clock).