Power BI Model for Complete Pipeline Visibility

The client is a leading global data, information, and analytics company that provides legal research solutions, business insights, and risk management tools to customers across law, government, corporations, and academia. A division of RELX, the company combines vast legal, regulatory, and news databases with advanced technologies such as machine learning and artificial intelligence to help professionals improve decision making, productivity, and compliance.

The Challenge  

The existing Market link data model lacked an integrated view connecting Leads, Opportunities, Opportunity Products, and the Contact and Employee information associated with both. This fragmented structure made it difficult to perform complete pipeline analysis, evaluate sales touches, or connect lead and opportunity lifecycles in a consistent manner.

Decision Foundry’s Approach  

DF adopted a structured and data-driven approach to tackle the client’s challenges:  

• Assessment and Planning: Conducted a comprehensive assessment of the existing data infrastructure to identify bottlenecks and areas for improvement.  
• To address the problem, a customized Lead–Opportunity Data Model was built in Power BI with the following characteristics
1. Performance Optimization :Model built in Power BI to reduce load on the Server. Query execution reduced from 10–15 minutes to approximately 5 minutes.
2. Alignment with existing Architecture :Model structure references includes additional enhancements required by Sales.
3. Enhanced Reporting Support Additional logic and data transformations were implemented to meet specific reporting needs that are not support natively.
• Technology Selection: Chose industry-leading technologies, PowerBI to build a robust data solution.  
• Integration Strategy: Developed a strategy to integrate tables into Power BI using reporting table by consolidation of Multiple Fact Tables.
• Optimization: Focused on optimizing tables and processes to enhance query performance and reliability.  
• Implementation Roadmap: Established a phased implementation plan to ensure minimal disruption to business operations.  

Solution Implementation    

The implementation of  DF’s solution was characterized by several key steps:       

• Creating Custom Models in PBI : Built a custom models in PBI allowing for seamless data integration and improved data accessibility.  
• Daily Data Refresh: Implemented a daily refresh and mechanism to cross-check the data accuracy.
• Medallion Architecture: Leveraged a medallion architecture to streamline data processing and support dashboard functionalities.  
• Process Optimization: Optimized data tables and processes to handle gigabytes of data efficiently, improving overall system performance.  
• Conflicting Contact and Employee Identifiers: Leads and opportunities had different level, even when linked to each other.

To resolve this ambiguity:

• Separate Contact tables for Leads and Opportunities were maintained.
• Separate Employee tables were maintained as well. This preserved accuracy while avoiding mismatches in relationship mappings.

Incomplete Lead–Opportunity Mapping in Marketlink

Marketlink’ s only contained opportunities explicitly associated with leads, causing:

• Missing opportunities that existed independently in the opportunity table.
• Incomplete reporting from a dashboard perspective.

The new model incorporates all leads and all opportunities, ensuring no data gaps.

• Use of Only Required Dimensions and Metrics

To optimize performance, only the necessary fields for reporting were included, reducing model weight and improving efficiency.

• Consolidation of Multiple Fact Tables

Instead of relying on a Galaxy Schema with:

1. Fact Lead
2. Fact Opportunity
3. Fact Opportunity Product

These were combined into one unified fact table, simplifying relationships and improving query speed.

Additional Marketlink matrix tables (e.g., contact-lead, contact-opportunity, contact-product, contact-account matrices) were intentionally not used to prevent:

• Relationship ambiguity
• Performance degradation
• Unnecessary model complexity

Results and Impact  

The project delivered significant improvements and measurable outcomes:  

• Enhanced Reporting Reliability: Reports are now reliable and trusted by the client, eliminating the need for cumbersome Excel transformations.  
• Centralized Access: Provided centralized access to a comprehensive database, facilitating ad-hoc analytics and improving decision-making speed.  
• Business Overview: Enabled a holistic view of the business, supporting strategic insights and growth opportunities.  
• Client Expansion: The success of the project prompted the client to express interest in automating ad-hoc analytics and exploring additional data sources.  
• The custom Power BI Lead–Opportunity Data Model successfully resolves major structural limitations in the original Marketlink model. By consolidating data sources, simplifying schema design, and resolving identifier inconsistencies, the new model enables comprehensive reporting across the lead and opportunity lifecycle. It delivers faster performance, richer insights, and more accurate analysis—fully aligned with Sales team needs and optimized for scalable future enhancements.

Client Testimonial  

Excellent work! The dashboard provides comprehensive visibility into all leads and greatly supports planning for campaigns and overall marketing strategy. We’re delighted with the level of detail captured—truly impressive. – Client”

Key Takeaways  

• The custom Power BI Lead–Opportunity Data Model successfully resolves major structural limitations in the original Marketlink model
• The use of Power BI optimized data processes like consolidating all the fact tables and use of only required measures and dimensions significantly improving query performance.  
• The project resulted in improved decision-making capabilities and strategic business insights for the client.  
• Positive client feedback and interest in expanding the scope of work underscore the project’s success.