SaaS Executive Revenue Overview
An executive Power BI report tracking ARR, Net Revenue Retention and Logo Churn against target, built on a Snowflake medallion warehouse fed by Salesforce and HubSpot. A monthly ARR snapshot fact drives retention and churn, while pipeline and marketing facts explain future growth.
Audience: CEO, CFO, CRO and board members. Generated by Blueprint BI on 2026-11-19 and shown exactly as generated.
What was entered
These are the only inputs. Everything below was generated from them.
- Industry
- SaaS & Technology
- Report type
- Executive Overview
- BI tool
- Power BI
- Currency
- USD ($)
- Data sources
- Salesforce, Snowflake, HubSpot
- Columns
- None given (Claude designed the model)
- KPIs
- ARR (target $12M), Net Revenue Retention (target 110%), Logo Churn % (target 1.5%)
- Notes
- None
- Style and pages
- Corporate Clean, 4 pages
Scope
Purpose, default period and where each input column lands in the model.
- Purpose
- Are we on track to $12M ARR, are we retaining and expanding customers, and is pipeline sufficient to close the gap?
- Audience
- CEO, CFO, CRO and board members
- Default period
- Current fiscal year to date by month, refreshed daily
Business rules
- ARR. Sum of annualised recurring subscription value (MRR x 12) on active contracts at month end, excluding one-off services. Calculated in: Snowflake gold layer (dbt), monthly snapshot
- Net Revenue Retention. ARR today from customers active 12 months ago divided by those customers' ARR 12 months ago, including expansion, contraction and churn. Calculated in: Power BI measure over FactArrSnapshot
- Logo churn. A customer churns in the month their ARR falls from above zero to zero; churn % = churned logos / logos active at start of month. Calculated in: dbt flag ChurnFlag in gold; rate in Power BI measure
- Active customer. A customer with ARR greater than zero at month end. Calculated in: Snowflake gold layer (dbt), monthly snapshot
1. Architecture
From source systems to the semantic model and the people who use it.
Data flow detail
- Ingestion
- Fivetran lands raw SaaS objects; dbt builds silver and gold models daily.
- Bronze
- Raw Fivetran replicas of Salesforce and HubSpot objects, untransformed.
- Silver
- Cleaned, deduplicated, typed entities with conformed account IDs.
- Gold
- Kimball star: ARR snapshot, pipeline and lead facts with conformed dimensions.
- Semantic model
- Imported star schema from Snowflake gold views, refreshed after dbt completes.
- Caveat: Data is only as fresh as the last scheduled refresh, not real time.
- Caveat: Model size must stay within the Premium/Fabric capacity memory limit.
- Caveat: Incremental refresh needs RangeStart/RangeEnd folding to Snowflake.
Governance & security
- RLS by Region using UserRegionMap from Salesforce user territory, CEO/CFO unrestricted.
- dbt tests: unique and not-null keys, ARR non-negative, snapshot month continuity.
- ARR reconciled to billing monthly with tolerance 0.5%, alerting Finance.
- Refresh failure alerts to the BI support Teams channel.
- Certified dataset endorsement and Purview lineage.
Source detail
- Salesforce CRM: Accounts, opportunities, contracts and subscription line items.
- HubSpot Marketing: Marketing-sourced leads, MQLs and campaign attribution.
- Snowflake Billing Data: Billing and subscription invoices already landed from the billing platform.
- ARR Targets: Board-approved monthly ARR targets by segment.
| Refresh and alerts | Ready by 06:00 ET daily; failures alert the BI support Teams channel and RevOps lead |
|---|---|
| Storage mode | Import |
| History | 4 years of monthly snapshots, covering 12-month NRR lookback plus prior-year comparison |
| Environments | Dev, Test and Prod workspaces; dbt dev and prod targets |
| Deployment | Power BI deployment pipelines; dbt Cloud jobs |
| Source control | Semantic model and report as PBIP in Git, dbt project in the same repository |
2. Data model
Star schema with grain, keys and relationships.
Star schema (galaxy with conformed dimensions)
3 fact · 5 dimension tables · 9 relationships · measures on _Measures
- DateKeyINT
- DateDATE
- MonthNumberINT
- MonthNameNVARCHAR(10)sort by MonthNumber
- YearMonthNumberINT
- YearMonthNVARCHAR(8)sort by YearMonthNumber
- QuarterNumberINT
- QuarterNVARCHAR(2)sort by QuarterNumber
- YearINT
- IsMonthEndBIT
- SegmentKeyINT
- SegmentCodeNVARCHAR(10)Business key
- SegmentNVARCHAR(20)sort by SegmentOrder
- SegmentOrderINT
- OpportunityStageKeyINT
- StageNameNVARCHAR(40)Business key · sort by StageOrder
- StageOrderINT
- WinProbabilityDECIMAL(5,2)
- DateKeyINT→ DimDate[DateKey]
- CustomerKeyINT→ DimCustomer[CustomerKey]
- ProductKeyINT→ DimProduct[ProductKey]
- ARRDECIMAL(18,2)
- NewARRDECIMAL(18,2)
- ExpansionARRDECIMAL(18,2)
- ContractionARRDECIMAL(18,2)
- ChurnedARRDECIMAL(18,2)
- ChurnFlagINT
- ActiveFlagINT
- DateKeyINT→ DimDate[DateKey]
- CustomerKeyINT→ DimCustomer[CustomerKey]
- OpportunityStageKeyINT→ DimOpportunityStage[OpportunityStageKey]
- OpportunityIdNVARCHAR(18)Degenerate dimension
- CloseDateDATE
- PipelineARRDECIMAL(18,2)
- IsOpenINT
- DateKeyINT→ DimDate[DateKey]
- SegmentKeyINT→ DimSegment[SegmentKey]
- TargetARRDECIMAL(18,2)
- CustomerKeyINT
- AccountIdNVARCHAR(18)Business key
- SegmentKeyINT→ DimSegment[SegmentKey]
- CustomerNameNVARCHAR(200)
- RegionNVARCHAR(20)
- IndustryNVARCHAR(50)
- AcquisitionChannelNVARCHAR(30)
- ValidFromDATE
- ValidToDATE
- ProductKeyINT
- ProductCodeNVARCHAR(20)Business key
- ProductFamilyNVARCHAR(50)
- PlanTierNVARCHAR(20)sort by PlanTierOrder
- PlanTierOrderINT
Grain note. FactArrSnapshot has no relationship to DimOpportunityStage, so its measures are only meaningful by the other dimensions.
Grain note. FactPipeline has no relationship to DimProduct, so its measures are only meaningful by the other dimensions.
Grain note. FactArrTarget has no relationship to DimCustomer, DimProduct, DimOpportunityStage, so its measures are only meaningful by the other dimensions.
| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactArrSnapshot.DateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactArrSnapshot.CustomerKey | DimCustomer.CustomerKey | many-to-one | Single | Yes |
| FactArrSnapshot.ProductKey | DimProduct.ProductKey | many-to-one | Single | Yes |
| DimCustomer.SegmentKey | DimSegment.SegmentKey | many-to-one | Single | Yes |
| FactPipeline.DateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactPipeline.CustomerKey | DimCustomer.CustomerKey | many-to-one | Single | Yes |
| FactPipeline.OpportunityStageKey | DimOpportunityStage.OpportunityStageKey | many-to-one | Single | Yes |
| FactArrTarget.DateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactArrTarget.SegmentKey | DimSegment.SegmentKey | many-to-one | Single | Yes |
3. DAX measures
An excerpt: the first 6 of 28 measures. Download the .dax file for all of them.
ARR =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactArrSnapshot ) ) )
RETURN CALCULATE ( SUM ( FactArrSnapshot[ARR] ), _LastDate )ARR PY =
CALCULATE ( [ARR], SAMEPERIODLASTYEAR ( DimDate[Date] ) )ARR YoY % =
VAR _PY = [ARR PY]
RETURN IF ( _PY > 0, DIVIDE ( [ARR] - _PY, _PY ) )ARR Target =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactArrTarget ) ) )
RETURN CALCULATE ( SUM ( FactArrTarget[TargetARR] ), _LastDate )ARR vs Target =
VAR _T = [ARR Target]
RETURN IF ( NOT ISBLANK ( _T ), [ARR] - _T )ARR vs Target % =
VAR _T = [ARR Target]
RETURN IF ( _T > 0, DIVIDE ( [ARR], _T ) )4. KPIs and RAG rules
| KPI | Target | Direction | Green (1) | Amber (2) | Red (3) | Example |
|---|---|---|---|---|---|---|
ARR Annualised recurring subscription value of active contracts at period end. [ARR] RAG: [ARR RAG] | $12M | Higher | ≥ $12,000,000 | ≥ $11,000,000 | < $11,000,000 | $11.4M +31.2% Amber |
Net Revenue Retention Current ARR of year-ago customers / their ARR 12 months ago. [Net Revenue Retention] RAG: [Net Revenue Retention RAG] | 110% | Higher | ≥ 110.0% | ≥ 100.0% | < 100.0% | 112.4% +2.1 pts Green |
Logo Churn % Churned logos per month / customers active at start of month. [Logo Churn %] RAG: [Logo Churn RAG] | 1.5% | Lower | ≤ 1.50% | ≤ 2.00% | > 2.00% | 2.30% +0.6 pts Red |
How RAG is shown
Each KPI has a RAG measure returning 1, 2 or 3 (BLANK when there is no value), so the thresholds live in one place. In Power BI, use conditional formatting with Format style "Rules" based on the RAG measure: 1 green, 2 amber, 3 red, and an icon set on the same rule. Status is never shown by colour alone: every colour is paired with its icon shape or word.
5. Report pages
Page layouts with the theme applied. Charts show placeholder shapes; the KPI cards use Claude's sample values.
Page 1: Executive Summary
- Purpose
- Show ARR, NRR and churn against target at a glance.
- Audience
- CEO, CFO, board
- Default filter
- Current fiscal year to date
- Slicers
- DimDate[Year] (dropdown) · DimSegment[Segment] (buttons) · DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users
- Title label
- [Selected Period Label]
- SMB
- Mid-Market
- Enterprise
- Strategic
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Headline KPIs | kpi strip | values: [ARR], [Net Revenue Retention], [Logo Churn %] tooltip: [ARR vs Target %], [ARR YoY %] |
| Are we on track for ARR, NRR and churn targets? |
| ARR vs Target by Month | line | axis: DimDate[YearMonth] values: [ARR], [ARR Target] tooltip: [ARR vs Target] |
| Is ARR tracking toward the $12M plan? |
| ARR by Segment | donut | legend: DimSegment[Segment] values: [ARR] |
| Which segments make up the ARR base? |
| ARR Bridge YTD | waterfall | axis: [New ARR] values: [Expansion ARR], [Contraction ARR], [Churned ARR] |
| What drove ARR change this year? |
Page 2: ARR Growth
- Purpose
- Explain ARR growth by product, segment and region.
- Audience
- CFO, CRO
- Default filter
- Current fiscal year to date
- Slicers
- DimProduct[ProductFamily] (dropdown) · DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| SMB | £1479K | 108.0% |
| Mid-Market | £1263K | 92.2% |
| Enterprise | £1304K | 95.2% |
| Strategic | £1153K | 84.1% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| ARR Movement by Month | stacked column | axis: DimDate[YearMonth] values: [New ARR], [Expansion ARR], [Contraction ARR], [Churned ARR] |
| How do new, expansion and churn trend monthly? |
| ARR by Product Family | bar | axis: DimProduct[ProductFamily] values: [ARR] tooltip: [ARR YoY %] |
| Which product families drive ARR? |
| ARR vs Target by Segment and Region | matrix | rows: DimSegment[Segment] values: [ARR], [ARR Target], [ARR vs Target %] |
| Where is attainment behind plan? |
Page 3: Retention & Churn
- Purpose
- Track NRR and logo churn, and find at-risk segments.
- Audience
- CEO, VP Customer Success
- Default filter
- Trailing 12 months
- Slicers
- DimSegment[Segment] (buttons) · DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Northwind Logistics | £1139K | 83.1% |
| Contoso Health | £1203K | 87.8% |
| Fabrikam Retail | £1390K | 101.5% |
| Tailspin Media | £1532K | 111.9% |
| Adatum Finance | £1530K | 111.7% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| NRR Trend | line | axis: DimDate[YearMonth] values: [Net Revenue Retention], [Net Revenue Retention PY] |
| Is retention improving toward 110%? |
| Logo Churn % Trend | line | axis: DimDate[YearMonth] values: [Logo Churn %], [Logo Churn % PY] |
| Is monthly churn below 1.5%? |
| Churn by Segment | bar | axis: DimSegment[Segment] values: [Logo Churn %] tooltip: [Churned Logos] |
| Which segments churn most? |
| Top Churned Accounts | table | values: DimCustomer[CustomerName], DimSegment[Segment], DimCustomer[Region], [Churned ARR] |
| Which lost accounts hurt ARR most? |
Page 4: Pipeline & Outlook
- Purpose
- Assess whether pipeline covers the remaining ARR gap.
- Audience
- CRO, CFO
- Default filter
- Latest snapshot, closing this fiscal year
- Slicers
- DimSegment[Segment] (buttons) · DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Woodgrove Bank | £1114K | 81.3% |
| Litware Software | £1356K | 99.0% |
| Proseware Inc | £1413K | 103.2% |
| Wide World Importers | £1424K | 103.9% |
| Lucerne Publishing | £1493K | 109.0% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Open Pipeline by Stage | funnel | axis: DimOpportunityStage[StageName] values: [Open Pipeline ARR] tooltip: [Open Opportunities] |
| Where does open pipeline sit in the funnel? |
| Pipeline Coverage by Segment | bar | axis: DimSegment[Segment] values: [Pipeline Coverage] tooltip: [Open Pipeline ARR] |
| Is coverage at least 3x the remaining gap? |
| Largest Open Opportunities | table | values: DimCustomer[CustomerName], DimOpportunityStage[StageName], FactPipeline[CloseDate], [Open Pipeline ARR] |
| Which deals matter most to close the gap? |
6. AI-designed page
Page 1 as drawn by the AI Design Studio (a Pro feature), unedited.
Assumptions and open questions
| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | Reporting uses the calendar year; prior-year comparisons use calendar months. | Add fiscal columns to DimDate and rebase YoY measures on fiscal periods. |
| 2 | ARR excludes one-off services, usage overages and is net of discounts. | Change ARR logic in dbt and restate snapshot history. |
| 3 | Logo churn is counted at account level; a reduction to zero followed by return within 30 days is not churn. | Adjust ChurnFlag rule and churn counts in gold. |
| 4 | ARR targets are set by segment and month only, so target measures are not sliced by region or product. | Add RegionKey or ProductKey to FactArrTarget and relate it to those dimensions. |
| 5 | RLS region mapping comes from Salesforce user territory assignments. | Source a separate security table from HR or Entra ID groups. |
| 6 | Customer segment changes are tracked as SCD2 and NRR uses current segment. | Use historical segment on snapshot rows if cohorts must keep original segment. |
Recommendations
- Configure incremental refresh on FactPipeline by snapshot date, keeping 2 years and refreshing the last 7 days.
- Build NRR cohort logic in dbt as a monthly table if DAX performance degrades beyond 2 seconds.
- Document every measure description and publish a KPI glossary page in the Power BI app.
- Use a field parameter later to let executives switch between ARR, NRR and churn on one trend chart.
- Schedule a monthly ARR reconciliation review with Finance before the board pack subscription sends.
- Track app usage metrics and retire visuals unused after 90 days.