Listings Deep Dive — Client History & Rankings (KB)

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

Column	Source	Notes
subscriber_id	Accounts_and_related_data.accountNumber	INT64
account_name	dim_listings_with_subscriber_id.AccountName	CRM-side name
bizname	dim_listings_with_subscriber_id.bizname	Public GP listing name
main_category	dim_listings_with_subscriber_id.main_category_name	GP primary category
city / county	Accounts_and_related_data.address1City / address1County	Trimmed city; raw county
locality	Derived	Metro if city matches Dublin/Cork/Galway/Limerick/Waterford (incl. Dublin postal codes), else Other
am	Accounts_and_related_data.accountManager.fullName	Last/First format from CRM

First SayMore line

Column	Source	Notes
cohort_year	EXTRACT(YEAR FROM MIN(firstInvoiceOn))	Year of subscriber’s earliest SM billing start. Naïve cut — may put renewal-cycle subscribers in the wrong year. Cross-reference with bridge_cohort_year.
first_sm_order_number	orders.orderNumber	The order containing the earliest-billing SM line
first_sm_signed_date	orders.signedDate	When the order was signed
first_sm_billing_start	ri.firstInvoiceOn	Canonical billing start
first_sm_product_name	salesOrderDetails.product.name	E.g. Saymore - Recurring Maintenance, Saymore Ultimate
first_sm_mrr	Proportional split from RI total	Just this line’s portion of the BG’s recurring invoice, not the whole bundle
first_sm_line_cancellation	salesOrderDetails.dateOfCancellation	NULL if this line wasn’t cancelled

SayMore lifecycle (across all SM lines for this subscriber)

Column	Source	Notes
last_sm_end	MAX of latest expireOn/dateOfCancellation across all SM lines	Effective end of SayMore relationship. 2099-01-01 if any line has no end date set (open-ended).
last_sm_cancellation	MAX of dateOfCancellation across SM lines	NULL if never cancelled.
currently_alive_sm	Boolean	Has any SM line in latest active_clients snapshot. Authoritative for “still a SayMore client today”.

Portfolio context

Column	Source	Notes
pre_sm_groups	;-joined list of product groups	Distinct subscription product groups (≠ PMS - Saymore) the subscriber was actively billing for at first_sm_billing_start. Empty = no prior recurring billing.
pre_sm_group_count	INT	Number of distinct pre-SM product groups
pre_sm_mrr	EUR	Sum of proportional monthly value across all pre-SM active subscription lines at SM start
post_sm_added_groups	;-joined list	Product groups whose first ever firstInvoiceOn postdates first_sm_billing_start — i.e. new product groups added after SM
post_sm_lost_groups	;-joined list	Product groups (≠ SM) where all RIs have ended (no open-ended ongoing line) AND the last end was after SM start. Caveat: PMS - Snapsync may show up here for legacy clients who migrated from SnapSync to SayMore — that’s not a real product loss, it’s the same product under a new label.

Bridge alignment

Column	Source	Notes
bridge_cohort_year	First year the subscriber appeared in the revenue bridge’s inflow detail	Matches dashboard revenue-bridge “New + Renewed + Returned” classification. Use this for any analysis you want to reconcile against the dashboard UI. Empty = subscriber appeared only in opening, not inflow (likely a long-tenured cycle-renewal).
bridge_class	new / renewed / returned	The classification the bridge gave them in their first inflow year. new = no prior FCR billing; renewed = existing FCR client (any group) added SM; returned = appeared, dropped, came back within the same year.

Cohort summary (from this dataset)

Bridge cohort year	n	Alive today	Retention	New	Renewed	Returned
2019	113	23	20%	73	40	0
2020	181	67	37%	72	109	0
2021	227	73	32%	153	74	0
2022	136	51	38%	96	40	0
2023	186	77	41%	122	64	0
2024	152	82	54%	83	69	0
2025	187	173	93%	112	74	1
2026 YTD	116	115	99%	50	66	0
No bridge match	350	161	46%	–	–	–
Total	1,648	822	50%	761	536	1

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.