# B2B SaaS Revenue Intelligence: Connect Billing, CRM and Product Data: editable worksheet

All worked records are illustrative. Replace assumptions with your own reviewed inputs.

## Build the account view

Illustrative data flow: billing subscriptions + CRM account map + product activity aggregated by account → reconciliation checks → account review list → named owner.

Keep subscription-level detail separately. Aggregate each source to its intended grain before joining; joining raw events to subscription rows can multiply revenue.

| Field | Source and purpose |
| --- | --- |
| account_id | Stable internal key; do not rely on email alone |
| subscription_id | Billing key; several subscriptions may belong to one account |
| recurring_amount, currency, period_months | Billing inputs for monthly normalisation |
| crm_stage, owner | CRM qualification state and responsible person |
| activity_count, window_start, window_end | Defined product activity over comparable windows |
| source_timestamp | Freshness of each source, not just dashboard refresh time |
| match_method, match_confidence | Exact ID, reviewed mapping or unresolved match |

## Three example accounts

All records below are synthetic. Amounts exclude one-off charges; currencies remain separate.

| Account | Billing | Activity | Review decision |
| --- | --- | --- | --- |
| Cedar | GBP 1,200 annually = GBP 100/month | 20 active days then 4 over equal 28-day windows | Owner checks adoption and seasonality; decline is not a churn probability |
| Birch | USD 300 monthly = USD 300/month | Feed unavailable | Investigate the feed; do not report zero usage or trigger a churn campaign |
| Elm | GBP 600 quarterly = GBP 200/month | No verified product-account mapping | Review identity match before scoring or campaign activation |

The GBP subtotal is 300/month; the USD subtotal is 300/month. There is no combined monetary total without a documented currency-conversion policy. Reconcile these figures back to subscription rows before adding scores.

## Reconcile before interpreting

- Check expected key uniqueness and one-to-many relationships at every join. Keep unmatched records in an exception report.
- Define treatment of discounts, credits, cancellations and one-off charges with the billing owner. Monthly normalisation is an operating metric, not recognised revenue.
- Distinguish no activity from unavailable or incomplete events. Display each source's refresh timestamp.
- Account for delayed billing updates. Save the snapshot date so later changes can be traced.

## Start with review rules

Use a small number of inspectable rules: usage decline with a healthy feed, upcoming renewal needing attention, or repeated usage-limit events suggesting an expansion conversation. Each alert needs observations, uncertainty, an owner and a next step.

An initial priority score is a heuristic. Calling it a probability requires calibration against later outcomes. Record whether reviewers accepted each alert, what they did and what happened afterwards. Assess false positives by cohort and account type before adding predictive models.

## Use the view in daily work

Review the exception queue before exporting any campaign audience. Give account owners the underlying observations, not only a red badge. Refresh at a cadence agreed with the source owners; label stale data rather than silently reusing it.

## Your working record

| Field | Your input |
| --- | --- |
| Owner | |
| Data sources and dates | |
| Objective | |
| Assumptions to validate | |
| Exceptions | |
| Review date | |
| Decision and supporting evidence | |
| Next action | |