# 03 · Engineering Handoff (GitHub Issue / PR Guidance) **Repo:** `pingcap-inc/pingcap-support` → `emea_lambda_tasks` **Result DB:** TiDB Cloud `regional_support` **Owner:** Yujie / dev **Author:** Heidi · 2026-06-23 > This doc is copy-pasteable as a GitHub issue. SQL drafts (read-only, validated against the 2026-06-23 snapshot) are in [04-sql-drafts.sql](04-sql-drafts.sql). All target counts are **as-of 2026-06-23** and will drift — acceptance = query-logic reproducibility, not exact integers. --- ## Scope summary (4 changes) | # | Change | Layer | Effort | |---|---|---|---| | A | Fix weekly all-actionable P0/P1 filter (don't drop `new_signup`) | `summary` | small | | B | Expose three lightweight action queues | `summary` | medium | | C | Add BQ channel/UTM attribution lookup by `tenant_id` | `monitor` (persist) or `summary` (display) — dev choice | medium | | D | **P1 floor rule** for paid/spending unconnected (no weight rewrite) | `scoring` | small–medium | --- ## A. Fix the weekly all-actionable filter `summary` **Problem:** the daily P0/P1 list filters `emea_active_tenants` to `source_type='active_candidate'`. If the **weekly all-actionable** view reuses that same filter, it silently excludes `source_type='new_signup'` P1 rows. **Fix:** - **Daily active list** → keep `source_type='active_candidate'` (this correctly reproduces 2026-06-23 P0=3 / P1=26). - **Weekly all-actionable P0/P1** → **must include both** `active_candidate` and `new_signup`. Do not reuse the daily filter. **Acceptance:** - Daily (active-candidate only), `DATE(processed_at)=2026-06-23` → P0=3, P1=26 (matches bot). - Weekly all-actionable → strictly **≥** the daily count, and **includes** `new_signup` P1 rows that the daily view omits. Verify at least one `new_signup` P1 appears. --- ## B. Three action queues `summary` All three read from `regional_support`. **"Unconnected" must be NULL-safe:** use `COALESCE(track_step,0) <> 2` (raw `track_step <> 2` drops NULL-track_step tenants — see [01](01-current-state-and-bot-validation.md#3-the-connected-definition)). ### B1. `paid_but_unconnected` - **Source:** `emea_active_tenants` - **Predicate:** `has_credit_card = 1 AND COALESCE(track_step,0) <> 2` - **Grain:** one row per tenant (latest `processed_at`). - **Output columns (Kerry needs these to email):** `tenant_id`, tenant name / domain, contact email if available, `has_credit_card`, credit-card bind date if available, `priority`, last activity / `processed_at`. - **Target as-of 2026-06-23:** ~12 tenants (paid-but-unconnected P0/P1). ### B2. `spending_limit_unconnected` - **Source:** `emea_tenant_events` (event) joined to `emea_active_tenants` (state). - **Predicate:** `event_type='UpdateSpendingLimit' AND event_status='succeeded' AND event within last 30d AND COALESCE(track_step,0) <> 2` - **Recency window (required):** spending-limit is an **event/behavior**, same class as import (B3) — it must be windowed. All-time is 473 tenants and would swamp ops; 30d default, tunable. - **Grain:** **distinct tenant, latest event** — so event-level output would duplicate accounts. - **Output columns:** `tenant_id`, name/domain, latest spending-limit event time, `track_step`, `priority`. ### B3. `recent_imports` - **Source:** `emea_tenant_events` - **Predicate:** `event_type='CreateImport' AND event_status='succeeded'` within the **last 7 days, anchored on the bot run date** (rolling window — this is how 2026-06-22's window spanned 06-15/06-16/06-22). - **Grain:** report both **distinct tenants** and **event count** (bot already shows both, e.g. 2026-06-22 = 4 tenants / 5 events). - **Priority:** P0 when combined with strong usage/fit; P1 otherwise. > **Queue overlap is expected.** A tenant may hit multiple queues. If the bot renders a single combined actionable list, apply precedence **Import > Paid-but-unconnected > Spending-limit** so an account isn't listed three times. --- ## C. BQ channel/UTM attribution lookup `monitor` or `summary` **Goal:** expose **raw** channel/UTM fields per tenant so operators can see where an account came from. **No inference, no guessing.** - **Input key:** `tenant_id` - **BQ source:** `gcp-digital-bi.model_plg.dws_daily_external_cloud_signup_user_detail` - **Fields to surface:** `ga_channel`, `ga_utm_source`, `ga_utm_medium`, `ga_utm_campaign` - **⚠️ Join grain (most likely silent bug):** this table is **daily** — a 7-day query returned **1,595 rows**, so a `tenant_id` has multiple rows (per signup-day / per user). A naive join **fans out** the action queue. **Pick exactly one row per `tenant_id`** — recommend **first-touch (earliest signup row)**; latest-touch is acceptable, but it must be deterministic and one-per-tenant. Document which you chose. - **Output location (dev's choice):** either display in the bot/action queue, **or** persist as **nullable** attribution columns on the enriched table. Either is fine. - **Non-goals:** ❌ no new frontend tracking · ❌ no intent keyword clustering · ❌ no NexusCRM dependency · ❌ no Direct/Organic inference beyond the raw BQ fields. Sample join in [04-sql-drafts.sql](04-sql-drafts.sql) (§6). --- ## D. Scoring — P1 floor rule (NOT a weight rewrite) `scoring` **Do NOT change `total_score` weighting.** Instead, after `total_score`/`priority` is computed as today, apply a **monotonic floor**: certain commercial signals guarantee a *minimum* priority of P1. The floor can only **raise** priority, never lower it. ``` priority_final = max_priority(priority_computed, floor) where P0 > P1 > P2 > P3 > none -- "max" = the more urgent one ``` **Floor rules:** - `paid_but_unconnected` (`has_credit_card=1 AND COALESCE(track_step,0)<>2`) → **floor = P1** - `UpdateSpendingLimit succeeded` **within last 30 days** `AND COALESCE(track_step,0)<>2` → **floor = P1**. The recency window is **required** — spending-limit is an event/behavior (same class as import), and flooring all-time (~473 tenants) would swamp two operators. 30d is tunable; size with §8 first. - Already P0 → **stays P0** (floor never demotes). - `Premium` alone → **no floor** — context/watch flag only, never raises priority by itself. **Import remains strictly stronger than Premium.** **Also emit a `priority_reason`** (e.g. `paid_unconnected_floor`, `spending_limit_floor`) so operators see *why* an account is P1 and the floor is auditable. > **⚠️ The floor changes the baseline.** Today's daily P0=3 / P1=26 (2026-06-23) will shift — P1 count goes **up** as low-scored paid/spending accounts are pulled up. That is the intended effect, not a regression. **Re-snapshot the new baseline after deploy and tell ops the new numbers.** Size the impact first with §8 in [04-sql-drafts.sql](04-sql-drafts.sql) before shipping. --- ## Definition of done - [ ] A: weekly all-actionable includes `new_signup` P1; daily still reproduces P0=3/P1=26 for 2026-06-23. - [ ] B1–B3: three queues output, NULL-safe `connected`, correct grain (tenant-level dedup for spending-limit & imports), Kerry's email columns present. - [ ] C: BQ lookup returns one row per tenant, raw fields only, grain choice documented. - [ ] D: `total_score` weights unchanged; P1 floor applied **after** computation, only raises never lowers; P0 preserved; Premium gets no floor; `priority_reason` emitted; **new post-floor baseline re-snapshotted and shared with ops**. Impact sized via §8 before deploy. - [ ] Non-goals respected (no frontend tracking, intent clustering, NexusCRM, Direct/Organic inference).