Active Supporter「多久沒開信才算不活躍」門檻分析
Companion Report — 開信衰退曲線與門檻建議
這份報告回答一件事：把 active supporter 定義成「12 個月內開過信」——這個 12 個月是主觀設定的值嗎？該設在哪？One question: is the 12-month cut-off for an active supporter arbitrary, and where should it really sit?
結論先講Bottom line：
在 HubSpot 可觀測的資料裡，開過信的人有 99.6% 集中在 12 個月內、曲線在 約 8 個月處手肘轉折（兩種演算法一致）。因此就「開信近期度」單一維度而言，門檻設在 6–8 個月比 12 個月更貼近真實衰退轉折；但更關鍵的發現是——HubSpot 行銷寄送史只有約 18 個月（始於 2025-01-24），12 個月以外根本沒有可觀測的長尾，因此「12 個月夠不夠長」這個問題在現有資料裡無法完整回答；真正的長尾要靠 SFMC 舊資料補。點擊資料太稀疏（只有開信者的 4%），無法當作 active 的第二定義維度。Among contacts HubSpot can observe, 99.6% of all openers last opened within 12 months and the cumulative curve elbows at ~8 months (both algorithms agree). So on the single axis of open-recency, a 6–8 month cut-off matches the real bend better than 12. But the bigger finding: HubSpot's marketing-send history is only ~18 months (since 2025-01-24), so there is no observable long tail beyond 12 months at all — the question 'is 12 months long enough?' cannot be fully answered from current data; the true tail needs legacy SFMC data. Click data is too sparse (4% of openers) to act as a second dimension of the active definition.
可寄達母體 MailableMailable pop
615,524
TWD × 曾送達≥1封 × 可寄可願TWD × delivered≥1 × reachable
開信 50% 落點 MedianOpen p50
2.5 個月mo
開過信者的中位數距上次開信median months-since-last-open, among openers
曲線手肘 ElbowElbow
8 個月mo
弦距法＋Kneedle 一致chord + Kneedle agree
HubSpot 從未開信Never-opened
56.1%
345,046 人（被18個月視界虛增）inflated by 18-mo horizon
建議與待決事項 Recommendations & open decisionsRecommendations & open decisions
- 開信近期度門檻建議 6–8 個月Set the open-recency cut-off at 6–8 months：曲線手肘在 8 個月、80% 開信者落在 5.8 個月內。12 個月會把大量早已不互動的人算成 active。the curve elbows at 8 months and 80% of openers fall within 5.8 months; 12 months labels many long-disengaged people as active.
- 「時間一個維度」對 active 定義已足夠；點擊不要進定義One time dimension is enough; do NOT add click：只有 10,942 人（開信者的 4.0%）有任何點擊紀錄，太稀疏、雜訊大，不足以當第二層 high-intent 定義。only 10,942 contacts (4.0% of openers) have any click; too sparse and noisy to serve as a second high-intent tier.
- 需你裁決：長尾要不要用 SFMC 補Decision needed: extend the tail with SFMC? 待決open：HubSpot 看不到 18 個月以外；SFMC 欄位涵蓋 564,026 名 TWD 的舊互動。要不要納入以還原真實長尾（代價：定義從『純開信』變『開信或點擊』混合）。HubSpot sees nothing past ~18 months; the SFMC field covers 564,026 TWD contacts' legacy activity. Fold it in to reconstruct the true tail? (trade-off: the metric shifts from open-only to open-or-click.)
- 捐款人以旗標呈現、不硬排除Treat donors as a flag, not a hard exclude：捐款人開信更近期（中位數 1.6 vs 非捐款人 2.9 個月）；門檻決策母體以非捐款人（enews 供應者池）為主，捐款人另附對照。donors open more recently (median 1.6 vs 2.9 months for non-donors); base the threshold on non-donors, show donors alongside.
必讀限制Read first：本圖畫的是「距上次開信的存量分布」，不是嚴格的「再互動機率隨時間衰退」。被系統壓抑或落入 graymail 的人不再收到信、last-open 被凍結；且 Apple Mail 隱私保護（MPP）會用機器預抓虛增開信，使開信曲線比真實人為互動更「年輕」。This shows the stock distribution of last-open dates, not a strict re-engagement-probability decay. Suppressed / graymail contacts stop receiving mail so their last-open freezes; and Apple Mail Privacy Protection (MPP) inflates opens with machine prefetch, making the open curve look younger than true human engagement.
為什麼現在的「12 個月」是任意值——母體與審查問題Why the current 12-month cut-off is arbitrary — population & censoring
現行 enews 名單池（約 474,876 人）在建立時，已用一條『12 個月未開信＝壓抑』的規則把人移除。用這個池去看『多久沒開信』是循環論證——超過 12 個月的尾巴早被切掉。因此本分析改用『全 TWD × 曾被寄過信』的母體重建，並把不活躍相關的壓抑條件全部拿掉。The current enews pool (~474,876) is built with a rule that already suppresses anyone who has not opened in 12 months. Measuring 'how stale are opens' on that pool is circular — the beyond-12-month tail was already cut. So we rebuild from a 'all-TWD × ever-sent' population with every inactivity-based suppression removed.
| 母體漏斗步驟Waterfall step | 人數Count | 說明Note |
|---|---|---|
| 錨點：currency=TWD（All TW）Anchor: currency=TWD 已驗證Verified | 1,688,697 | 文件錨點 1,687,011，漂移 +0.10%doc anchor 1,687,011, +0.10% drift |
| − 從未被寄過行銷信（delivered=0）never delivered a marketing email | −1,041,957 | 多為連署者/dummy/SFMC 舊聯絡人petition-only / dummy / legacy |
| = 曾送達 ≥1 封delivered ≥1 | 646,740 | 只有 38% 的 TWD 被 HubSpot 寄過行銷信only 38% of TWD ever HubSpot-mailed |
| − 退訂/拒聯 + 硬彈跳（聯集）opt-out/refused + hard-bounce (union) | −160,989 | hs_optout 21,047 / FR_optout 7,783 / 硬彈 6,134 等hs_optout 21,047 / FR_optout 7,783 / hard-bounce 6,134… |
| = 主母體（非捐款人，含捐款人另計）primary (non-donor) | 485,751 | vs enews 474,876 → 大 +10,875（未套12月壓抑）+10,875 vs enews 474,876 |
| + 捐款人（以旗標保留）donors (kept as flag) | +129,407 | D2：捐款人不硬排除D2: donors not hard-excluded |
| = 可寄達母體（本報告曲線母體）mailable population (curve base) 已驗證Verified | 615,524 | 開信曲線的分母來源denominator source |
最關鍵發現：資料視界只有約 18 個月Key finding: the data horizon is only ~18 months
HubSpot 最早的行銷寄送是 2025-01-24（至今約 17.8 個月）。因此 hs_email_last_open_date 不可能早於此——『距上次開信 >18 個月』的桶在結構上必為空。移除 12 個月壓抑也救不回長尾，因為長尾在 HubSpot 根本不存在。HubSpot's earliest marketing send is 2025-01-24 (~17.8 months ago). So hs_email_last_open_date cannot predate it — the '>18-months-since-open' buckets are structurally empty. Removing the 12-month suppression does not recover the tail, because the tail simply does not exist in HubSpot.
審查旗標 engaging_networks_not_open_email_in_12_months 目前 351,327 人為真，但近 12 個月新建的 contact 中為真者＝0——代表這是 凍結的遺留旗標、已不再滾動更新。真正的長尾互動落在 SFMC 欄位 sfmc_email_open_or_click_last_activity_date（564,026 名 TWD 有值）。The censoring flag engaging_networks_not_open_email_in_12_months is true for 351,327 contacts, but true for 0 of those created in the last 12 months — it is a frozen legacy flag, no longer rolling. The real long-tail engagement lives in the SFMC field sfmc_email_open_or_click_last_activity_date (564,026 TWD contacts have a value).
HubSpot 最早的行銷寄送是 2025-01-24（至今約 17.8 個月）。因此 hs_email_last_open_date 不可能早於此——『距上次開信 >18 個月』的桶在結構上必為空。移除 12 個月壓抑也救不回長尾，因為長尾在 HubSpot 根本不存在。HubSpot's earliest marketing send is 2025-01-24 (~17.8 months ago). So hs_email_last_open_date cannot predate it — the '>18-months-since-open' buckets are structurally empty. Removing the 12-month suppression does not recover the tail, because the tail simply does not exist in HubSpot.
審查旗標 engaging_networks_not_open_email_in_12_months 目前 351,327 人為真，但近 12 個月新建的 contact 中為真者＝0——代表這是 凍結的遺留旗標、已不再滾動更新。真正的長尾互動落在 SFMC 欄位 sfmc_email_open_or_click_last_activity_date（564,026 名 TWD 有值）。The censoring flag engaging_networks_not_open_email_in_12_months is true for 351,327 contacts, but true for 0 of those created in the last 12 months — it is a frozen legacy flag, no longer rolling. The real long-tail engagement lives in the SFMC field sfmc_email_open_or_click_last_activity_date (564,026 TWD contacts have a value).
Q1 · 大家多久沒開信？——開信衰退曲線How stale are opens? — the open-decay curve
下圖：開過信的人（270,478 人）依『距上次開信月數』的累積百分比。20% 在 0.6 個月內、50% 在 2.5 個月內、80% 在 5.8 個月內；曲線手肘在 8 個月。紅色區＝HubSpot 資料視界（約 18 個月）以外，結構性無資料。Cumulative share of openers (270,478) by months-since-last-open. 20% within 0.6 mo, 50% within 2.5 mo, 80% within 5.8 mo; elbow at 8 mo. Red zone = beyond HubSpot's ~18-month horizon, structurally no data.
開信累積 % (分母＝開過信者)open cumulative % (denom = openers)
每月分桶人數（含獨立『從未開信』桶）Per-month bucket counts (with separate never-opened bar)
『從未開信』345,046 人（母體的 56.1%）獨立呈現，不併入任何月數桶、不參與百分位；此比例被 18 個月視界虛增——SFMC 時代開過信者在 HubSpot 顯示為空。最新一桶（0–1 個月）最大，部分為 Apple MPP 機器開信。The never-opened bar (345,046, 56.1% of pop) is shown separately, never mixed into a month bucket nor used for percentiles; it is inflated by the 18-month horizon. The largest bar (0–1 mo) is partly Apple-MPP machine opens.
讀圖注意Reading note：累積 % 的分母是『開過信的人』，不是全母體。全母體有 56% 從未在 HubSpot 開過信（多因視界，並非真正的沉睡名單）。the cumulative % denominator is 'people who have opened', not the whole population; 56% of the population never opened in HubSpot — mostly a horizon artifact, not a dead list.
補充視圖：SFMC 長尾——HubSpot 視界以外的真實互動Supplementary view: the SFMC long tail — real engagement beyond HubSpot's horizon
這是不同的訊號，數字不可與開信曲線混用A different signal — never blend these numbers with the HubSpot open curve：
欄位 sfmc_email_open_or_click_last_activity_date＝SFMC（HubSpot 之前的行銷平台）時代的『最後開信或點擊』日期，母體＝564,026 名有此值的 TWD（未套排除，原始互動近期度）。field sfmc_email_open_or_click_last_activity_date = last open-OR-click in the SFMC era (the pre-HubSpot platform); population = 564,026 TWD with a value (raw recency, no exclusion filter).
SFMC 最後互動 累積 %SFMC last-activity cumulative %
| 指標Metric | SFMC 長尾tail | HubSpot 開信（對照，不同訊號）HubSpot open (compare — different signal) |
|---|---|---|
| p20 / p50 / p80 (個月mo) | 11.6 / 12.7 / 16.0 | 0.6 / 2.5 / 5.8 |
| 手肘 ElbowElbow | 11 個月mo | 8 個月mo |
| 欄位資料範圍field date range | 2024-12-04 → 2025-12-07 （約 8–20 個月前；本身也受限）(~8–20 months ago; itself horizon-limited) | |
長尾檢視結論：12 個月以外確實存在大量『曾經活躍』的人，但更深的尾巴仍無資料。 這 564,026 人的最後 SFMC 互動全部落在約 8–20 個月前（中位數 12.7 個月），其中 70.7% 在 12 個月以前——這正是 HubSpot 開信曲線看不到的族群（他們在 HubSpot 顯示為『從未開信』，實際上 SFMC 時代有過互動，並非沉睡名單）。但 SFMC 欄位本身在約 20 個月處截止（最早 2024-12-04），所以 20 個月以外更深的尾巴仍無任何可觀測依據。因此：可辯護的門檻推理上限約在 20 個月；再遠沒有資料。若要把 active 定義延伸到 12 個月以外，應以『HubSpot 開信 或 SFMC 互動』的合併近期度為準（但兩訊號不可直接相加）。Tail verdict: substantial 'once-engaged' people DO exist beyond 12 months, but the deeper tail still has no data. These 564,026 contacts' last SFMC activity all falls ~8–20 months ago (median {sfmc['p50']} mo), and 70.7% of them older than 12 months — exactly the cohort HubSpot's open curve cannot see (they read as 'never opened' in HubSpot but engaged in the SFMC era; not a dead list). But the SFMC field itself stops at ~20 months (earliest {sfmc['field_min']}), so anything beyond ~20 months remains unobservable. Defensible threshold reasoning therefore extends to ~20 months and no further. To extend the active definition past 12 months, use a combined 'HubSpot-open OR SFMC-activity' recency (the two signals must not be summed directly).
Q2 · 點擊呢？兩條曲線有沒有分開？Click — do the two curves split?
開信 openopen點擊 clickclick
| 分離度指標Divergence metric | 數值Value | 門檻Rule | 判定Verdict |
|---|---|---|---|
| 中位數月差Median month diff | 2.8 個月mo | ≤ 2 | 超標exceeds |
| KS 最大垂直距離KS max vertical | 23.5 pp | ≤ 10 pp | 超標exceeds |
| 手肘月差Elbow month diff | 0 個月mo | — | 一致aligned |
| 點擊基數／開信基數Click base / open base | 10,942 / 270,478 = 4.0% — 點擊資料極稀疏click base is extremely thin | ||
判定：時間一個維度足夠；點擊不進 active 定義。 形式上兩線分離（中位數差 2.8 個月、KS 23.5pp 都超標），照事先宣告的規則會說『行為要進定義』。但分離主要來自點擊基數太小（只有開信者的 4.0%，全母體 1.8%）造成的形狀雜訊，而非穩健的行為訊號。因此誠實的答案是：『點擊太稀疏、不足以當 active 的第二定義維度』本身就是對這題的回答——用開信近期度單一維度即可。Verdict: one time dimension is enough; click does NOT enter the active definition. Formally the two curves diverge (median diff 2.8 mo and KS 23.5pp both exceed the pre-declared thresholds → the rule would say 'add behaviour'. But the divergence is driven by click's tiny base (4.0% of openers, 1.8% of the population) producing shape noise, not a robust behavioural signal. The honest answer: 'click is too sparse to serve as a second dimension' is itself the answer — use open-recency alone.
MPP 虛增MPP inflation：Apple Mail 隱私保護會用機器預抓虛增『開信』（開信日期可能是機器行為），點擊不受影響、可信度更高。若真要在開信與點擊間取捨，點擊在語意上更乾淨——但它太稀疏。這強化了『開信曲線比真實人為互動更年輕』的推論。Apple Mail Privacy Protection inflates opens with machine prefetch (an open date can be a machine event); clicks are unaffected and cleaner in principle — but far too sparse. This reinforces that the open curve reads younger than true human engagement.
Q3 · 三種價值群衰退得一樣嗎？Do the three value personas decay differently?
國際觀型傳統價值型監督型
| 價值群Persona | 開信者∩母體openers∩pop | p50 | p80 |
|---|---|---|---|
| 國際觀型 | 40,292 | 2.4 | 5.9 |
| 傳統價值型 | 40,898 | 2.2 | 5.9 |
| 監督型 | 14,994 | 2.0 | 5.7 |
三群 50% 落點差距 0.4 個月。The three personas' median points span 0.4 months.差 ≤2 個月 → 單一全域門檻即可；否則建議按群檢視。≤2 mo → one global threshold works; otherwise review per persona.
建議門檻與影響試算Recommended threshold & pool-size simulation
| 候選門檻Cut-off | Active 池（含捐款人）Active pool (all) | 占母體% of pop | 占開信者% of openers | 非捐款人池non-donor pool |
|---|---|---|---|---|
| 6 個月mo | 222,366 | 36.1% | 82.2% | 173,397 |
| 12 個月mo | 269,274 | 43.7% | 99.6% | 207,878 |
| 18 個月mo | 270,478 | 43.9% | 100.0% | 207,926 |
| 24 個月mo | 270,478 | 43.9% | 100.0% | 207,926 |
建議：以 6–8 個月為開信近期度門檻（手肘 8 個月、80% 落在 5.8 個月）。關鍵洞察：把門檻從 12 拉到 18 或 24 個月，active 池幾乎不變（269,274 → 270,478）——因為 HubSpot 12 個月以外沒有可觀測對象。所以真正的槓桿不是『12 定得對不對』，而是『6–8 個月能更精準地聚焦近期活躍者』，以及『長尾要不要用 SFMC 補』。Recommend a 6–8 month open-recency cut-off (elbow 8 mo, 80% within 5.8 mo). Key insight: moving the cut-off from 12 to 18 or 24 months barely changes the active pool (269,274 → 270,478) — because HubSpot has no observable contacts beyond 12 months. The real lever is not 'is 12 correct' but 'a 6–8 month cut-off focuses more tightly on the recently active', plus 'whether to extend the tail with SFMC'.
方法與驗證 Methodology & reproducibilityMethodology & reproducibility
母體定義Population definition
- 參考日 REF_DATE = 2026-07-17（Asia/Taipei），以外部參數傳入腳本；月差＝FLOOR(DATE_DIFF(REF_DATE, last_open, DAY)/30.44)。REF_DATE = 2026-07-17, passed as a script parameter; months = FLOOR(DATE_DIFF(REF_DATE,last_open,DAY)/30.44).
- 納入：currency=TWD ＋ hs_email_delivered≥1（曾送達至少一封行銷信）。Include: currency=TWD AND hs_email_delivered≥1 (delivered at least one marketing email).
- 排除：退訂/拒聯（marketing_opt_out, do_not_email, hs_email_optout, do_not_contact, fundraising_appeals_opt_out）＋ 硬彈跳（hs_email_hard_bounce_reason_enum, engaging_networks_hard_bounced, sfmc_hard_bounce_count≥1）＋ dummy。不套任何不活躍壓抑。Exclude: opt-out/refused + hard-bounce + dummy. NO inactivity suppression.
- 捐款人（donor_status）以旗標保留、非硬排除；曲線分『含/不含捐款人』。donor_status（Active/Lapsed/Inactive Donor）比 ebull 捐款池（122,661）廣。Donors (donor_status) kept as a flag; curves split with/without donors. donor_status is broader than the ebull donor pool (122,661).
欄位（已對 portal 驗證）Fields (verified against the portal)
| 內部名稱Internal name | 語意Meaning | 驗證Verify |
|---|---|---|
| hs_email_last_open_date | 距上次開信last marketing-email open | Properties API |
| hs_email_last_click_date | 距上次點擊last marketing-email click | Properties API |
| hs_email_delivered | 送達計數（無 sent 屬性）delivered count (no 'sent' prop exists) | Properties API |
| hs_email_first_send_date | 首次寄送（定視界）first send (horizon) | MIN via sorted search |
| engaging_networks_not_open_email_in_12_months | 審查旗標（僅查證、不排除；已凍結）censoring flag (frozen; not used to exclude) | 近12月新建=0created≤12mo = 0 |
| sfmc_email_open_or_click_last_activity_date | SFMC 時代最後互動（長尾補值來源）SFMC-era last activity (tail source) | 564,026 TWD 有值has value on 564,026 |
資料路徑與可重現Data path & reproducibility
- BigQuery 不足：gpea-engage.Matrix.Contact（SF 鏡像）無開信欄位；Atlas_AI.Contact 有欄位但僅 256,593 列（AI 子集），無法涵蓋 169 萬母體。故走 HubSpot CRM Search API 逐桶計數（每次只回 total、不拉紀錄）。BigQuery insufficient: Matrix.Contact (SF mirror) lacks open fields; Atlas_AI.Contact has them but only 256,593 rows (AI subset). So we used HubSpot CRM Search API counting per bucket (reads `total`, no record pull).
- 累積計數用『減法恆等式』：母體∩日期 = 基底∩日期 − 基底∩日期∩排除聯集（受 API 5-group/18-filter 上限限制，日期條件用 3–4 個 material 排除；EN 硬彈 ~2,476 略去，<0.5%）。Cumulative counts via a subtraction identity within the API's 5-group/18-filter limit; date-conditioned unions use 3–4 material exclusions (EN hard-bounce ~2,476 omitted, <0.5%).
- 手肘：max-distance-to-chord（主）＋ Kneedle（交叉驗證），兩法一致＝8 個月。Elbow: max-distance-to-chord (primary) + Kneedle (cross-check), both = 8 months.
- 來源檔（_exports/，時戳命名）：門檻對帳 gate_results.json / gate_summary.txt；曲線 curves_raw.json / curves_summary.json / buckets.csv；抽樣 spotcheck_10_contacts.json。每個 Search 查詢的 filter body 存 curves_calls.json。Source files (_exports/, timestamped): gate_results.json / gate_summary.txt; curves_raw.json / curves_summary.json / buckets.csv; spotcheck_10_contacts.json; every Search filter body in curves_calls.json.
對帳與抽樣Reconciliation & spot-check
- 錨點：currency=TWD = 1,688,697 vs 文件 1,687,011（+0.10% 同步漂移）。Anchor currency=TWD = 1,688,697 vs doc 1,687,011 (+0.10% sync drift).
- 12 個月交叉比對：主母體『12 個月內開過信』= 207,877，小於現行 enews 池 474,876——因兩者不同量（474,876 是完整資格池含大量非近期開信者；207,877 只是近期開信者）。同類（母體 vs 母體）比對：主母體 485,751 > enews 474,876，符合預期。12-month cross-check: primary population 'opened within 12 months' = 207,877, smaller than the enews pool 474,876 — different quantities (474,876 is the full eligibility pool incl. many non-recent openers; 207,877 is only recent openers). Like-for-like: primary 485,751 > enews 474,876, as expected.
- 抽樣 10 名 contact（ids 於 spotcheck_10_contacts.json）逐一核對開信/點擊/送達；其中 3/4『HubSpot 未開信』者有 SFMC 互動日期，直接證明 18 個月視界污染。Spot-checked 10 contacts (ids in spotcheck_10_contacts.json); 3 of 4 'HubSpot never-opened' had an SFMC activity date — direct evidence of the 18-month horizon contamination.
誠實限制 CaveatsCaveats
- 凍結尾巴：被壓抑/落入 graymail 者不再收信，last-open 凍結；本圖是存量分布，非嚴格再互動率。Frozen tail: suppressed/graymail contacts stop receiving mail; this is a stock distribution, not a strict re-engagement rate.
- 資料視界：HubSpot 行銷寄送始於 2025-01-24（~18 個月），12 個月以外無可觀測長尾。Horizon: HubSpot sends start 2025-01-24 (~18 mo); no observable tail beyond 12 months.
- MPP：開信被 Apple 機器預抓虛增，開信曲線偏年輕。MPP: opens inflated by Apple machine prefetch; open curve skews young.
- 點擊稀疏：僅開信者 4.0% 有點擊，點擊曲線統計力弱。Click sparsity: only 4.0% of openers have a click; the click curve is underpowered.
- 捐款人定義：donor_status 比 ebull 池廣，為待決事項。Donor definition: donor_status is broader than the ebull pool — an open decision.
數字標示Provenance: 已驗證 Verified 直接查得direct · 代理 Proxy 近似approx · 估算 Est. 推估inferred。拉取日Pulled 2026-07-17.