SayMore deep-dive knowledge base

Per-subscriber forensic dataset for every account that ever billed for PMS - Saymore. One row per subscriber.

Files

• docs/saymore-deep-dive-kb.csv — the dataset. 1,648 rows × 25 cols. Regenerated by re-running the build (see below).
• docs/product-onboard-cohort-analysis.md — methodology underpinning the bridge alignment and SQL building blocks.

Schema (25 columns)

Identity / account

First SayMore line

SayMore lifecycle (across all SM lines for this subscriber)

Portfolio context

Bridge alignment

Cohort summary (from this dataset)

The 350 "no bridge match" rows are subscribers whose firstInvoiceOn falls in some year but the bridge didn't classify them as inflow — meaning the bridge already saw them in opening. Most likely cycle-renewals (replacement BG created in a later year, but bridge has them tracked from earlier). The 134 in this bucket whose naïve cohort_year=2020 is the biggest concentration — these are the COVID-era renewal-cycle entries.

How to rebuild

node .tmp_sm_kb_build.cjs   # see git history for the script body

Two halves:

1. Call /dashboard-revenue-bridge for 2018–2026, build subscriber → (bridge_year, bridge_class) map
2. Run the unified BQ query (template below), augment with bridge map, write CSV

Script automatically calls ?auto_csv=true&csv_title=saymore-deep-dive-knowledge-base on the worker so a copy also lands in R2 at a worker-served URL.

Unified SQL (rebuild from scratch)

WITH all_lines AS (
  SELECT
    a.accountNumber AS subscriber_id,
    TRIM(a.address1City) AS city, a.address1County AS county,
    a.accountManager.fullName AS am,
    bg.id AS bg_id,
    o.orderNumber AS order_number, o.signedDate AS signed_date,
    ol.salesOrderDetailId AS sod_id,
    ol.product.productGroup.name AS pg,
    ol.product.name AS product_name,
    ol.baseAmount AS base_amount,
    ol.dateOfCancellation AS line_cancellation,
    ri.firstInvoiceOn AS billing_start, ri.expireOn AS expire_on,
    ri.totalLineItemAmount AS ri_total, ri.invoicePeriod AS ri_period,
    ri.recurringInvoiceDetails AS ri_details
  FROM `listingmanager-1529856313699.DYNAMICS_DATA.Accounts_and_related_data` a,
    UNNEST(a.orders) o, UNNEST(o.salesOrderDetails) ol,
    UNNEST([ol.billingGroup]) bg, UNNEST(bg.recurringInvoices) ri
  WHERE ol.product IS NOT NULL
    AND COALESCE(ol.product.subscription, FALSE) = TRUE
    AND bg.id IS NOT NULL AND COALESCE(ri.isDeposit, FALSE) = FALSE
    AND COALESCE(ri.totalLineItemAmount, 0) > 0
    AND COALESCE(ri.invoicePeriod, 0) > 0
    AND ri.firstInvoiceOn IS NOT NULL
    AND (ol.parentItemGuid IS NULL OR ol.baseAmount > 0)
),
lines_filtered AS (
  SELECT * EXCEPT(ri_details) FROM all_lines
  WHERE ARRAY_LENGTH(ri_details) = 0
     OR sod_id IN (SELECT rid.salesOrderDetailId FROM UNNEST(ri_details) rid)
),
bg_totals AS (
  SELECT bg_id, billing_start, SUM(base_amount) total_base, COUNT(*) line_count
  FROM lines_filtered GROUP BY bg_id, billing_start
),
lines_mrr AS (
  SELECT lf.*,
    CASE
      WHEN bt.total_base > 0 THEN lf.ri_total * (lf.base_amount / bt.total_base) / lf.ri_period
      WHEN bt.line_count > 0 THEN lf.ri_total / bt.line_count / lf.ri_period
      ELSE lf.ri_total / lf.ri_period
    END AS monthly_value
  FROM lines_filtered lf
  JOIN bg_totals bt ON lf.bg_id = bt.bg_id AND lf.billing_start = bt.billing_start
),
sm_subs AS (
  SELECT subscriber_id, MIN(billing_start) first_sm_start
  FROM lines_mrr WHERE pg = 'PMS - Saymore' GROUP BY subscriber_id
),
first_sm_line AS (
  SELECT s.subscriber_id, l.order_number, l.signed_date, l.billing_start,
         l.expire_on, l.line_cancellation, l.product_name, l.monthly_value,
         l.am, l.city, l.county
  FROM sm_subs s
  JOIN lines_mrr l
    ON s.subscriber_id = l.subscriber_id
   AND l.billing_start = s.first_sm_start
   AND l.pg = 'PMS - Saymore'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY s.subscriber_id ORDER BY l.monthly_value DESC) = 1
),
all_sm AS (
  SELECT subscriber_id,
         MIN(billing_start) first_sm_start,
         MAX(LEAST(IFNULL(expire_on, TIMESTAMP('2099-01-01')),
                   IFNULL(line_cancellation, TIMESTAMP('2099-01-01')))) AS last_sm_end,
         MAX(line_cancellation) AS last_sm_cancellation
  FROM lines_mrr WHERE pg = 'PMS - Saymore' GROUP BY subscriber_id
),
pre_sm_groups AS (
  SELECT l.subscriber_id,
         STRING_AGG(DISTINCT l.pg, '; ' ORDER BY l.pg) AS group_list,
         COUNT(DISTINCT l.pg) AS group_count,
         SUM(l.monthly_value) AS pre_sm_mrr_total
  FROM lines_mrr l
  JOIN sm_subs s ON l.subscriber_id = s.subscriber_id
  WHERE l.pg != 'PMS - Saymore'
    AND l.billing_start < s.first_sm_start
    AND (l.expire_on IS NULL OR l.expire_on > s.first_sm_start)
    AND (l.line_cancellation IS NULL OR l.line_cancellation > s.first_sm_start)
  GROUP BY l.subscriber_id
),
group_first_starts AS (
  SELECT subscriber_id, pg, MIN(billing_start) AS first_pg_start
  FROM lines_mrr GROUP BY subscriber_id, pg
),
post_sm_added AS (
  SELECT g.subscriber_id,
         STRING_AGG(DISTINCT g.pg, '; ' ORDER BY g.pg) AS added_groups
  FROM group_first_starts g
  JOIN sm_subs s ON g.subscriber_id = s.subscriber_id
  WHERE g.pg != 'PMS - Saymore' AND g.first_pg_start > s.first_sm_start
  GROUP BY g.subscriber_id
),
group_last_ends AS (
  SELECT subscriber_id, pg,
         MAX(LEAST(IFNULL(expire_on, TIMESTAMP('2099-01-01')),
                   IFNULL(line_cancellation, TIMESTAMP('2099-01-01')))) AS last_pg_end,
         LOGICAL_OR(expire_on IS NULL AND line_cancellation IS NULL) AS has_open_ended
  FROM lines_mrr GROUP BY subscriber_id, pg
),
post_sm_lost AS (
  SELECT g.subscriber_id,
         STRING_AGG(DISTINCT g.pg, '; ' ORDER BY g.pg) AS lost_groups
  FROM group_last_ends g
  JOIN sm_subs s ON g.subscriber_id = s.subscriber_id
  WHERE g.pg != 'PMS - Saymore'
    AND NOT g.has_open_ended
    AND g.last_pg_end > s.first_sm_start
    AND g.last_pg_end < CURRENT_TIMESTAMP()
  GROUP BY g.subscriber_id
),
live_sm AS (
  SELECT DISTINCT subscriber_id
  FROM `listingmanager-1529856313699.fcr_operations.active_clients`
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM `listingmanager-1529856313699.fcr_operations.active_clients`)
    AND product_group = 'PMS - Saymore'
),
biz AS (
  SELECT SAFE_CAST(subscriber_id AS INT64) subscriber_id,
         ANY_VALUE(AccountName) account_name,
         ANY_VALUE(bizname) bizname,
         ANY_VALUE(main_category_name) main_category
  FROM `listingmanager-1529856313699.SUPERSET_MASTER.dim_listings_with_subscriber_id`
  WHERE subscriber_id IS NOT NULL GROUP BY subscriber_id
)
SELECT
  s.subscriber_id,
  b.account_name, b.bizname, b.main_category,
  fl.city, fl.county,
  CASE WHEN REGEXP_CONTAINS(LOWER(fl.city),
       r'^[ ]*(dublin([ ][0-9]+w?)?|dublin city|cork([ ]city)?|galway([ ]city)?|limerick([ ]city)?|waterford([ ]city)?)[ ]*$')
       THEN 'Metro' ELSE 'Other' END AS locality,
  fl.am,
  EXTRACT(YEAR FROM s.first_sm_start) AS cohort_year,
  fl.order_number AS first_sm_order_number,
  fl.signed_date AS first_sm_signed_date,
  fl.billing_start AS first_sm_billing_start,
  fl.product_name AS first_sm_product_name,
  ROUND(fl.monthly_value, 2) AS first_sm_mrr,
  fl.line_cancellation AS first_sm_line_cancellation,
  asml.last_sm_end, asml.last_sm_cancellation,
  IF(ls.subscriber_id IS NOT NULL, TRUE, FALSE) AS currently_alive_sm,
  IFNULL(psg.group_list, '') AS pre_sm_groups,
  IFNULL(psg.group_count, 0) AS pre_sm_group_count,
  ROUND(IFNULL(psg.pre_sm_mrr_total, 0), 2) AS pre_sm_mrr,
  IFNULL(psa.added_groups, '') AS post_sm_added_groups,
  IFNULL(psl.lost_groups, '') AS post_sm_lost_groups
FROM sm_subs s
LEFT JOIN first_sm_line fl ON s.subscriber_id = fl.subscriber_id
LEFT JOIN all_sm asml ON s.subscriber_id = asml.subscriber_id
LEFT JOIN biz b ON s.subscriber_id = b.subscriber_id
LEFT JOIN pre_sm_groups psg ON s.subscriber_id = psg.subscriber_id
LEFT JOIN post_sm_added psa ON s.subscriber_id = psa.subscriber_id
LEFT JOIN post_sm_lost psl ON s.subscriber_id = psl.subscriber_id
LEFT JOIN live_sm ls ON s.subscriber_id = ls.subscriber_id
ORDER BY s.first_sm_start, s.subscriber_id

The Node augmenter then merges in bridge_cohort_year and bridge_class from per-year /dashboard-revenue-bridge?productGroup=PMS - Saymore calls (see docs/product-onboard-cohort-analysis.md for orchestrator skeleton).

Suggested deep-dive queries against this CSV

Open in pandas / Excel / BQ-uploaded table:

• Channel mix at SM signing: pivot pre_sm_groups to see which prior products correlate with SM survival. E.g. pre_sm_groups CONTAINS 'DMS - Websites' survival rate vs no-website.
• Discount-cohort effect: filter first_sm_mrr < 30 and compare retention vs first_sm_mrr >= 50. Tests whether the 2020 €35-median MRR cohort churned worse than full-price cohorts.
• AM accountability: group by am, rank by currently_alive_sm / count(*) to see which AMs retain SM clients best (with sample-size guardrail).
• Category × locality survival: pivot main_category × locality × currently_alive_sm. Find categories where SayMore actually sticks.
• Cross-sell paths: filter post_sm_added_groups != '' and see what FCR products win SM clients later (DMS - SEA? DMS - SEO?). Compare retention of post-SM-upsold clients vs SM-only.
• Lost-with-SayMore: filter currently_alive_sm = FALSE and look at post_sm_lost_groups — do clients usually churn SM alone or as part of a full pull-out?
• Bridge vs naive cohort delta: filter bridge_cohort_year IS NULL (the 350 unmatched) — these are renewal-cycle replacements not "new" signings. Useful to exclude from new-business analysis.

Gotchas

1. SnapSync ≠ SayMore loss. pre_sm_groups CONTAINS 'PMS - Snapsync' or post_sm_lost_groups CONTAINS 'PMS - Snapsync' typically reflects the legacy SnapSync → SayMore migration, not a real cross-sell or churn. Treat as same-product label change. The earlier memory feedback_saymore_snapsync.md covers this.
2. pre_sm_mrr = 0 doesn't mean "new client". It means no recurring billing was active exactly at SM start. Could be a delayed-start client who paid Setup/DEA pre-SM. Cross-reference with bridge_class — renewed with pre_sm_mrr = 0 = signed pre-CY but recurring delayed.
3. first_sm_mrr is the proportional split of that BG's RI, not the full RI total. A €50 SM line bundled with a €30 DMS - Dir line on the same BG will show €50 in first_sm_mrr, even though the BG bills €80/month.
4. bridge_cohort_year is sparse for older subscribers — 350 of 1,648 have no bridge classification. They're not missing data, they're real subscribers the bridge sees in opening (carryover from a year we didn't run the bridge for, or replacement-BG cases). Their naïve cohort_year is in the CSV — use that for those rows or exclude depending on the question.
5. main_category is GP-listing-based, not CRM-based. Subscribers without an active GP listing won't have a category — bizname and main_category will be blank for those.
6. am may be Success, Customer Success — that's the CS bucket (high-touch retention team), not orphan accounts. Don't treat as untouched. See memory feedback_cs_bucket_high_care.md.

FCR Dashboard documentation · generated from docs/ · keep counts verified, not guessed.