Skip to main content

B2B SaaS Example

In a B2B SaaS setup, BPP integrates CRM leads, subscription contracts, product usage events, and web interactions to power:

  • Action Prediction: likelihood a prospect will convert, likelihood a customer will renew or churn.
  • pcLTV: predicted recurring value (MRR/ARR).
  • RFM Segmentation: frequency of logins, recency of usage, monetary value of subscriptions.
  • Interest Analysis: feature/module affinity from usage events.
  • Signals & Audiences: activating accounts and contacts in ad platforms.

IDs & identity resolution​

SaaS typically manages multiple IDs across CRM, product, and billing. BPP unifies them under the Bytek ID.

  • CRM Contact ID (primary in CRM).
  • CRM Account ID (organization ID — entity-level, do not map as a user identifier).
  • Hashed email (SHA-256 normalized).
  • Hashed phone (optional).
  • Product user ID (from product database or auth).
  • Web cookie / GA4 ID (web behaviour).

See Identifiers & Hashing for normalization rules.

Core tables​

1. User table — CRM Contacts​

CREATE TABLE my_dataset.crm_contacts (
  crm_contact_id STRING,
  crm_account_id STRING,
  hem            STRING,
  hphone         STRING,
  first_name     STRING,
  last_name      STRING,
  job_role       STRING,
  department     STRING,
  seniority_level STRING,
  company_name   STRING,
  company_size   STRING,
  industry       STRING,
  country        STRING,
  lead_source    STRING,
  lead_medium    STRING,
  lead_campaign  STRING,
  deal_stage     STRING,    -- latest stage (history lives in the event table below)
  created_at     DATETIME,
  updated_at     DATETIME
);

2. Event table — Deal Stage History​

CREATE TABLE my_dataset.deal_stage_history (
  row_id          STRING,
  event_timestamp DATETIME,
  crm_contact_id  STRING,
  crm_account_id  STRING,
  deal_id         STRING,
  old_stage       STRING,
  new_stage       STRING,   -- e.g. Qualification, Proposal, Closed Won, Closed Lost
  deal_value      FLOAT64,
  currency        STRING,
  created_at      DATETIME
);

3. Event table — Subscription Contracts​

CREATE TABLE my_dataset.subscriptions (
  subscription_id   STRING,
  event_timestamp   DATETIME,
  crm_account_id    STRING,
  crm_contact_id    STRING,
  plan_name         STRING,   -- e.g. Starter, Pro, Enterprise
  plan_type         STRING,   -- monthly, annual
  mrr               FLOAT64,
  arr               FLOAT64,
  currency          STRING,
  status            STRING,   -- active, cancelled, trial, expired
  start_date        DATETIME,
  renewal_date      DATETIME,
  cancellation_date DATETIME,
  created_at        DATETIME
);

4. Event table — Product Usage Events​

CREATE TABLE my_dataset.product_usage_events (
  event_id        STRING,
  event_timestamp DATETIME,
  product_user_id STRING,
  crm_contact_id  STRING,
  crm_account_id  STRING,
  event_type      STRING,   -- login, feature_used, file_uploaded
  feature_name    STRING,   -- e.g. "Dashboard", "Export CSV"
  session_duration_sec INT64,
  device_class    STRING,
  browser         STRING,
  country         STRING,
  created_at      DATETIME
);

5. Event table — Web Behavioural Events​

CREATE TABLE my_dataset.web_events (
  event_id        STRING,
  event_timestamp DATETIME,
  fp_cookie_id    STRING,
  hem             STRING,
  crm_contact_id  STRING,
  event_type      STRING,   -- pageview, demo_request, pricing_click
  page_url        STRING,
  utm_source      STRING,
  utm_medium      STRING,
  utm_campaign    STRING,
  gclid           STRING,
  gbraid          STRING,
  wbraid          STRING,
  device_class    STRING,
  browser         STRING,
  country         STRING,
  created_at      DATETIME
);

Many SaaS providers enrich CRM data with external sources before bringing it into BPP, typically via an ETL pipeline (Fivetran, dbt, Airbyte). Examples:

  • Firmographics (company size, revenue, sector) from Clearbit, ZoomInfo, Apollo.
  • Technographics (which tools/stack the company uses).
  • Intent data (third-party signals of interest in relevant topics).
  • Web enrichment (IP-to-company resolution).

Store enriched attributes at contact level (user table) or account level linked to contacts — they become immediately available for feature engineering in Action Prediction and pcLTV.

Do & don't summary​

✅ Do❌ Don't
Store deal pipeline as historyOverwrite deal_stage in one row
Track subscription lifecycle as eventsKeep only a "current plan" column
Keep granular product usage eventsStore only an aggregated usage score
Always include monetary fields (MRR/ARR)Leave subscription rows without values
Link accounts and contacts via IDsMix account-level and contact-level data
Normalize + hash emails/phonesHash incorrectly (wrong casing/whitespace)
Use ETL enrichment for firmographics/technographicsLimit CRM to raw manual fields only

How BPP uses these tables​

  • Action Prediction — Target: conversion to paying subscription or renewal. Features: company size, industry, enriched firmographics, product usage frequency, campaign source.
  • pcLTV — Transactions: subscription contracts (MRR/ARR). Horizon: revenue across the subscription lifecycle.
  • RFM — Recency = last product usage or renewal. Frequency = logins / feature uses. Monetary = MRR/ARR linked to the account.
  • Interest — From web pageviews (page_url) or product features (feature_name).

Why this design works​

  • CRM + Product + Billing unified → covers the full customer lifecycle.
  • Event-based modelling → no overwritten states; complete history of deals/subscriptions.
  • External enrichment → makes models far more predictive.
  • Granular usage → key for churn prediction.
  • Consistent identifiers → ensures ad activation and model training.
Last updated on