1. Data Sources

• Onboarding dataset containing client households, service tiers, AUM values, and planning subscription indicators
• Operational workflow logs from CSAs, Operations Specialists, and Compliance Reviewers (onboarding throughput, NIGO rates, documentation accuracy, CRM task completion)
• Advisor book snapshots (AUM per advisor, household lists, planning workloads, referral activity)
• Compliance review logs (documentation checks, audit exceptions, meeting note compliance)
• Marketing funnel data from digital campaigns (lead volume, conversion rates, referral attribution)
• Technology system outputs from Orion and eMoney (AUM, revenue, sync errors, manual entry volume)
• Staffing levels across advisors, CSAs, planners, operations, compliance, and marketing

2. Data Cleaning & Preparation

• Normalized onboarding and client‑level data to ensure consistent household counts and service tier assignments
• Identified inconsistent historical fields (Acquisition_Channel, Referral_Source) and preserved them as future‑state tracking fields rather than historical KPI inputs
• Standardized timestamps across onboarding, workflow, and compliance logs to align reporting periods
• Consolidated siloed spreadsheets from CSAs, advisors, operations, and compliance into unified operational datasets
• Reconciled AUM values across advisor books, Orion/Tamarac reports, and onboarding records
• Created derived fields including: onboarding cycle time, account transfer cycle time, CRM task completion rate, documentation accuracy, advisor capacity utilization, CSA capacity utilization

3. Analytical Methods

• Client Growth Analysis
  • Calculated new clients, net new clients, referral rates, and planning adoption using onboarding records
  • Segmented households by service tier, AUM band, and planning subscription status
• AUM & Revenue Analysis
  • Standardized AUM using Orion outputs and reconciled advisor‑level discrepancies
  • Separated net new assets from market return contribution
  • Calculated advisory revenue using the firm’s tiered fee schedule
• Revenue Forecasting
  • Advisory revenue = AUM × 0.85% blended fee
  • Planning revenue = adoption rate × new clients × subscription price
  • Modeled planning adoption at 8%, 12%, and 20% depending on scenario
• Operational Efficiency Analysis
  • Measured onboarding throughput, NIGO rates, workflow SLA compliance, rework rates, and task completion times
  • Evaluated advisor and CSA capacity using clients per advisor, clients per CSA, and workload distribution
• Compliance Analysis
  • Assessed documentation completion, meeting note compliance, audit exception rates, and recurring regulatory issues
• Technology Analysis
  • Measured CRM data completeness, integration error rates, and manual entry volume across systems
• Reporting Gap Analysis
  • Compared existing reports to required KPI dashboards
  • Identified missing executive, advisor, operational, and compliance reporting
  • Mapped duplicate reports to workflow inefficiencies and data inconsistencies

4. Assumptions*

• Historical onboarding fields (Acquisition_Channel, Referral_Source) are unreliable for backdated KPI attribution
• Advisor capacity is constrained more by task mix than client count
• CSA throughput is limited by onboarding volume, custodial paperwork, and workflow fragmentation
• Compliance reviewers maintain ~250–300 files per year before exception rates increase
• CRM underutilization and manual spreadsheets introduce data quality risk
• CSA capacity threshold: 115 households
• Workflow redesign and tech optimization will materially increase throughput and reduce rework
• Unified reporting will reduce duplicate effort and improve operational clarity across departments

*Data Availability: While not all real‑world data was available due to the firm being fictional and the dataset being artificially generated, the BI/KPI framework was built to accommodate full data capture once the recommended reporting, workflow, and system integrations are implemented. The full constellation of KPIs will not be used in the analysis, however, we will identify them all for use in future business decision-making. The assessment will reflect current gaps and outlines how the firm can begin collecting these fields in a structured, reliable way going forward.

1. Existing Reports

a. Reports

• AUM & Revenue Reports – Monthly AUM and advisory fee reports generated through Orion/Tamarac

• Advisor Book of Business Snapshots – Basic client lists, AUM per advisor, and household summaries

• Compliance Review Logs – Documentation checklists, meeting note audits, and exception tracking

• Onboarding Status Trackers – CSA‑maintained spreadsheets showing client onboarding progress

• Marketing Lead Reports – Digital marketing funnel metrics (inbound leads, conversions) from HubSpot or similar tools

b. Gaps

• These reports exist, but are siloed, inconsistent, and lack executive-level integration

2. Missing Reports

a. Executive-Level

• Executive KPI Dashboard – No unified view of AUM, revenue, client growth, capacity, and risk

• Capacity & Staffing Dashboard – No visibility into advisor/CSA utilization or breakpoints

• Forecast vs Actual Performance Report – No comparison between projected growth and actual throughput

b. Advisor-Level

• Advisor Productivity Dashboard – No tracking of meeting volume, planning workload, referral generation, or operational bottlenecks

• Advisor Task Mix Report – No visibility into how much time advisors spend on admin vs planning vs client meetings

c. Operational-Level

• CSA Throughput & Service Level Agreement (SLA) Dashboard – No measurement of onboarding cycle time, Not-In-Good-Order (NIGO) rates, or workflow delays

• Workflow Efficiency Dashboard – No tracking of rework, task completion time, or CRM data completeness

d. Operational-Level

• Compliance KPI Dashboard – No consolidated view of documentation accuracy, review cycle time, or audit exceptions

• Risk & Exception Trend Report – No trend analysis of recurring                compliance issues

e. Gaps

• Corresponds to the firm’s largest pain points – capacity strain, workflow breakdowns, compliance pressure, and lack of standardization of process

3. Duplicate Reports

a. Reports

• AUM & Revenue Reports – Advisors, operations, and leadership each maintain their own versions

• Onboarding Status Reports – CSAs track onboarding in spreadsheets, advisors track it in email, and operations tracks it in CRM

• Client Lists & Segmentation Reports – Advisors maintain personal versions, operations maintains a master list, marketing maintains segmentation for campaigns

• Compliance Documentation Checks – Advisors track their own notes, compliance maintains a separate audit log

b. Gap

• Duplicate reports create inconsistent workflows, trigger unnecessary rework, and general confusion (Onboarding, AUM, and compliance primarily)

Dimension Tables

The BI model uses six core Dimension Tables that provide the descriptive context required for all KPI calculations and Fact Table relationships. These dimensions define the “who,” “what,” and “when” behind every operational event, enabling consistent reporting, segmentation, and executive‑level analysis. The tables included are:

DimDate

A standardized calendar table that anchors all reporting periods. Including Date, Year, Quarter and Month, the DimDate Table supports trend analysis, period‑over‑period comparisons, forecasting, and alignment across monthly, quarterly, and annual KPI cycles.

DimClient

The DimClient table refines each client household with stable attributes such as household size, client segment, servicing advisor, servicing client service associate and referral source. This dimension enables segmentation of client growth, retention, planning adoption, and revenue metrics.

DimAdvisor

The DimAdvisor table captures advisor‑level attributes such as advisor name, role, start date and team assignment. This dimension supports key KPIs including clients per advisor, capacity utilization, meeting volume, and planning workload. While these fields were not fully available in the generated dataset used for this project, a real RIA would typically maintain this information internally, making it straightforward to incorporate into the BI model as the firm adopts the recommended reporting and data‑governance practices.

DimCSA

The DimCSA table defines Client Service Associates and their operational roles, including CSA name, workflow ownership and tenure. These dimensions support analysis of onboarding throughput, task completion time, NIGO rates, workflow delays, and overall CSA capacity utilization. Although these fields were not included in the generated dataset used for this project, a real RIA would typically maintain this information internally, making it straightforward to incorporate as the firm adopts the recommended reporting and data‑governance practices.

DimAccount

The DimAccount table defines account‑level attributes such as account ID, custodian, account type, and account status (open, pending, or closed). This dimension supports core analytics including AUM reconciliation, account transfer cycle time, documentation accuracy, and net‑new asset tracking. In a real RIA environment, these fields are typically sourced directly from Orion or eMoney through standard data exports or custodial feeds. Because the generated dataset used for this project did not include access to client account platforms, these attributes are not populated in the current model; however, the BI framework is designed to incorporate them seamlessly once the firm begins collecting this information through system integrations or CSA‑driven data capture.

DimCompliance

Similar to the DimAdvisor and DimCSA tables, the DimCompliance table captures compliance reviewers and review categories, enabling KPIs related to documentation accuracy, audit exceptions, review cycle time, and regulatory findings.

The BI model uses a set of core Fact Tables that capture the measurable business events required for all KPI calculations and performance analysis. These tables record the quantitative activity behind client growth, AUM movement, advisor workload, operational throughput, and compliance reviews. While the Dimension Tables define the “who,” “what,” and “when,” the Fact Tables define the “how much” and “how often,” enabling accurate trend analysis, capacity evaluation, and executive‑level reporting. The tables included are:

FactTabClientGrowth

The FactTabClientGrowth table captures all measurable client‑level growth activity, including new households added, clients lost, referral‑driven acquisitions, advisor‑sourced clients, and digital leads converted. Anchored to DimDate and DimClient, this fact table includes fields such as Date, NewClients, LostClients, NetNewClients, TotalClients, ReferralClients, AdvisorSourcedClients, DigitalLeadsConverted, AcquisitionClients, RunningTotalClients, Advisors, and Clients/Advisors. These metrics enable calculation of core KPIs including net new clients, retention rate, referral effectiveness, advisor acquisition performance, and client‑to‑advisor ratios. By structuring growth as discrete, time‑stamped events, the model supports trend analysis, forecasting, and segmentation across advisors, acquisition channels, and service tiers.

Although the onboarding dataset contains fields such as ReferralClients and Acquisition_Channel, their historical meaning and consistency are unclear. These fields appear to have been used inconsistently over time and do not reliably map to the KPI definitions required for client acquisition attribution. Rather than forcing these ambiguous fields into FactClientGrowth and risking inaccurate historical reporting, they are preserved as future tracking fields that the firm can begin populating in a structured way going forward. This approach maintains data integrity, reflects the firm’s actual data maturity, and establishes a scalable foundation for more robust acquisition analytics once standardized CRM processes are implemented.

FactAUM&Rev

The FactAUM & Revenue table captures all asset and revenue movements at both the household and account level, including total AUM, advisory revenue, planning revenue, net new AUM, market return AUM, effective fee rate, revenue per client, AUM per advisor, and revenue per advisor. Linked to DimAccount, DimClient, and DimDate, this fact table supports essential KPIs such as net new AUM, organic versus market‑driven growth, revenue yield, fee consistency, and long‑term financial trend analysis. By centralizing these quantitative measures, the model provides a reliable foundation for AUM reconciliation and firm‑wide financial reporting.

Currently, the dataset only includes yearly AUM and revenue values, which limits the granularity of trend analysis and short‑term KPI evaluation. However, the BI model is intentionally designed to scale and, once implemented within a real RIA environment, the firm can populate this table with daily and monthly custodial and portfolio management exports from systems such as Orion or eMoney. The structure of the FactAUM&Rev table ensures that more frequent data (daily balances, monthly billing, and intra‑month flows) can be incorporated seamlessly without redesigning the model.

FactAdvisoryActivity

The FactAdvisorActivity table captures advisor‑driven productivity events, including meetings held, plans delivered, plan updates, new clients sourced, referrals generated, administrative hours, planning hours, and meeting hours. Connected to DimAdvisor, DimClient, and DimDate, this fact table enables KPIs such as advisor capacity utilization, meeting volume, planning workload, referral effectiveness, and client‑to‑advisor ratios. By quantifying advisor activity at the event level, the model supports detailed productivity analysis and helps identify staffing needs, workload imbalances, and opportunities for operational support as the firm scales.

At this stage of the project, advisor‑level activity data is not available in the generated dataset which, again, limits the ability to populate this fact table fully. However, like the other tables, these fields are typically captured through CRM activity logs, planning software exports, and advisor time‑tracking workflows. The BI model is intentionally designed to incorporate this information seamlessly once the firm implements the recommended reporting practices and system integrations.

FactOperationsWorkflow

The FactOperationsWorkflow table tracks operational events across onboarding, account transfers, NIGO count, rework count, tasks completed, tasks past Service Level Agreement, CRM task completion rate and manual entry count. Anchored to DimCSA, DimAccount, DimClient, and DimDate, it supports KPIs such as onboarding cycle time, NIGO rates, workflow throughput, SLA adherence, and operational bottlenecks. This fact table provides visibility into process efficiency and highlights areas where workflow redesign or staffing adjustments may be needed.

This table will also require future population once the BI model is integrated into the firm’s workflows.

FactComplianceReview

The FactComplianceReview table captures all compliance‑related review activity, including the number of files reviewed, documentation completion rates, meeting‑notes compliance, audit exceptions, repeat exceptions, review cycle time, CRM task completion rate, and manual entry counts. Linked to DimCompliance and DimDate, this fact table enables KPIs such as documentation accuracy, exception frequency, reviewer efficiency, and overall compliance readiness. By quantifying review outcomes and operational accuracy, the model provides leadership with clear visibility into regulatory risk, process gaps, and areas where additional training or workflow refinement may be required.

For the short-term BI Model, compliance‑level review data is not included in the generated dataset, which limits the ability to fully populate this fact table. The model is designed to incorporate this information seamlessly once the firm implements the recommended documentation standards and review processes, ensuring the structure is ready for daily, monthly, and quarterly compliance activity as soon as the data becomes available.

Current KPI Coverage

The KPIs currently available in the BI model represent the metrics that can be reliably calculated using the generated dataset. These measures focus on client growth, advisor ratios, and high‑level AUM and revenue trends—providing a foundational view of firm performance even without full CRM, custodial, or planning system integrations. The dashboard includes KPIs such as net new clients, total clients, referral‑driven growth, advisor‑sourced acquisitions, digital lead conversions, and client‑to‑advisor ratios. Together, these metrics establish a baseline understanding of acquisition effectiveness, retention, and advisor capacity.

Because the dataset only includes yearly AUM and revenue values, the model also supports high‑level financial KPIs such as total AUM, advisory revenue, planning revenue, net new AUM, and effective fee rate. While these measures lack daily or monthly granularity, they still provide meaningful insight into long‑term growth trends and revenue consistency. These have been included in the Dashboards provided in the Data Highlights and Visual Summary Section of this module.

Future KPI Calculations

As Northwest Wealth Partners integrates custodial feeds, CRM activity logs, planning software exports, and workflow data into the BI model, the firm will be able to unlock a significantly broader and more sophisticated KPI suite. The model has already been architected to support these metrics, and the underlying Fact and Dimension tables are structured to absorb daily, monthly, and event‑level data as soon as it becomes available. This enables the BI environment to evolve from foundational yearly reporting into a fully operational performance‑management system.

With complete system integrations, the BI model will support advanced KPIs across growth, revenue, advisor productivity, operational efficiency, compliance accuracy, and forecasting. These include Net New AUM, Revenue, Revenue per Advisor, Revenue per Client, Total Clients, New Clients, Lost Clients, Net Client Growth, YoY Client Growth, Clients per Advisor, Advisor Capacity Utilization, Advisor Workload, Advisor Activity Score, Advisor Productivity Trend, Onboarding Cycle Time, Transfer Cycle Time, NIGO Rate, Rework Rate, SLA Breach Rate, Documentation Completion Rate, Meeting Notes Compliance, Audit Exception Rate, Manual Entry Volume, Advisor Staffing Requirement, CSA Staffing Requirement, Forecasted Revenue, and Forecasted AUM Growth. Together, these KPIs will provide leadership with a comprehensive, real‑time view of firm performance, supporting strategic planning, staffing decisions, workflow optimization, and long‑term growth forecasting. The formulas for these KPIs have been assembled in the Data Model Spreadsheet provided at the beginning of this section.

As the firm’s data‑governance maturity increases, these KPIs can be refreshed daily, allowing Northwest Wealth Partners to transition from retrospective reporting to proactive management and predictive analytics.

KPI Summary

The firm’s core KPIs indicate not only stable historical performance but a strong foundation for continued growth. Consistent year‑over‑year client expansion demonstrates that the firm’s acquisition and retention strategies are working, providing a reliable base for scaling advisor capacity and long‑term revenue. As Total Clients increase, the firm gains greater predictability in planning staffing, service delivery, and operational workflows.

AUM growth continues to track upward, driven by both market performance and organic inflows. This strengthens the firm’s ability to invest in technology, talent, and client experience enhancements. Because revenue moves directly with AUM under the current fee structure, sustained asset growth ensures a stable financial runway for strategic initiatives.

Acquisition Channel totals show a healthy concentration in referrals, reinforcing the firm’s reputation and client satisfaction. This channel mix positions the firm to deepen its referral programs, strengthen advisor advocacy, and reduce reliance on paid marketing. As referral volume grows, client acquisition costs will decrease, improving overall profitability.

Service Tier totals remain balanced, giving the firm a clear view of where future capacity investments should be made. A stable tier mix allows leadership to refine service models, adjust pricing strategies, and ensure that advisors and CSAs are aligned with the needs of each client segment. Over time, this segmentation insight will support more targeted service delivery and improved client retention.

Overall, the KPI set highlights a firm that is well‑positioned for continued expansion. The combination of predictable client growth, increasing AUM, strong referral dynamics, and stable tier distribution provides leadership with the clarity needed to plan capacity, optimize workflows, and invest confidently in future growth.

Trend Analysis

Client and asset trends continue to show steady, predictable momentum, positioning the firm for scalable growth. Total Clients Over Time reflects consistent year‑over‑year expansion, driven by reliable new household acquisition. This upward trend provides leadership with a stable foundation for long‑term capacity planning, enabling more accurate forecasting of advisor workload, CSA support needs, and service delivery requirements.

New Client trends remain strong, indicating that the firm’s referral dynamics, advisor relationships, and onboarding experience are performing effectively. As new household acquisition continues at a predictable pace, the firm can confidently invest in workflow automation, advisor development, and targeted service enhancements without introducing operational strain.

AUM trends show healthy growth, supported by both market appreciation and organic inflows from new clients. Because revenue scales directly with AUM under the current fee structure, this trend strengthens the firm’s financial stability and provides a reliable runway for strategic initiatives such as technology upgrades, expanded client service offerings, and talent acquisition, once the workflow redesign implementation period stabilizes.

Segmentation trends remain stable. Acquisition Channel totals continue to favor referrals, reinforcing strong client satisfaction and advisor advocacy. This trend reduces acquisition costs and supports long‑term profitability. Service Tier totals show a consistent distribution, giving clear leadership insight into where future capacity investments should be directed and how service models can be refined to better support each tier. Overall, the trend lines indicate a firm that is expanding predictably, strengthening its financial position and, when the workflows are updated, building the operational stability required for future growth. These trends give leadership confidence to invest strategically, optimize capacity, and enhance client experience as the firm continues to scale.

Executive Commentary

The firm’s performance this period reinforces a strong foundation for continued growth and operational scalability. Consistent increases in Total Clients and AUM signal that the core value proposition remains compelling and that the client experience is supporting long‑term retention. As these trends continue, the firm are positioned to expand advisor capacity, refine service delivery models, and invest confidently in operational infrastructure.

New Client acquisition remains steady, with referrals continuing to serve as the most productive channel. This trend highlights the strength of advisor relationships and client satisfaction. Going forward, the firm will prioritize initiatives that deepen referral engagement, including enhanced client advocacy programs and advisor‑driven outreach strategies. Strengthening this channel will reduce acquisition costs and improve long‑term profitability.

AUM growth provides a reliable financial runway for strategic investment. With revenue tracking directly alongside AUM, the firm can allocate resources toward technology modernization, workflow automation, and expanded client service offerings. These investments will improve advisor efficiency, reduce operational friction, and support a more scalable service model as the client base grows.

Service Tier distribution remains stable, giving us clear visibility into where future capacity planning should be focused. Maintaining a balanced tier mix allows us to refine staffing models and ensure that advisors and CSAs are aligned with the needs of each client segment. Over time, this will support more personalized service delivery and improved retention across all tiers.

Overall, the firm enters the next period with strong momentum and a clear path forward. The combination of predictable client growth, increasing AUM, and stable segmentation provides leadership with the confidence to pursue strategic initiatives that enhance capacity, strengthen client experience, and support sustainable long‑term expansion, post-workflow redesign.

1. Total Clients – The number of active client households at year‑end

2. New Clients – Households added during the reporting period

3. Net Client Growth – Year‑over‑year change in Total Clients driven by new household acquisition

4. AUM – Total assets under management at period end, inclusive of market movement and new inflows

5. Revenue – Calculated using the firm’s fee schedule applied to AUM

6. Acquisition Channel Totals – Count of new households by referral, digital, COI, or other acquisition sources

7. Service Tier Totals – Number of households assigned to each service tier (Core, Enhanced, Premier)

1. Client KPIs (Total Clients, New Clients, Net Growth)

• Owner: Client Services Associates (CSAs)

• Responsible for:

– Tracking household additions

– Managing onboarding throughput

– Maintaining workflow execution

– Identifying opportunities to improve client experience

– CSAs ensure client‑level data accuracy that feeds into firm‑level reporting

2. AUM & Revenue KPIs

• Owner: CEO / Managing Partner – Founder 1

• Responsible for:

– Overseeing firm level AUM movement

– Market impact and fee based revenue trends

– Ensures accuracy in revenue calculations

– Supports financial planning and budgeting

– Validates AUM inputs provided by advisors and operations

3. Acquisition Channel KPIs

• Owner: Digital Marketing Specialists (Marketing Team)

• Responsible for:

– Ensuring accurate attribution of new households

– Evaluating channel effectiveness

– Monitoring funnel performance

– Guiding investment in referral programs and digital campaigns

– Provides channel level insights for growth planning

4. Service Tier KPIs

• Owner: COO / Director of Advisor Services – Founder 2

• Responsible for:

– Maintaining tier assignments

– Monitoring advisor and CSA capacity across segments

– Ensuring service delivery models remain aligned with client needs

– Oversees operational throughput and service tier consistency