Power BI Template – RevOS Documentation & Support

1. Introduction

This document provides a comprehensive guide to using the RevOS Power BI Template designed for analyzing HubSpot data. The template enables users to visualize and analyze deals, contacts, companies, and deal pipelines, facilitating data-driven business decisions with ease. It includes predefined relationships, measures, and visualizations tailored for deal analysis. Additionally, the template serves as a flexible foundation, allowing users to integrate new data and create customized visuals for personalized reporting.

2. Getting Started: Setting Up the Connection

Prerequisites

Before using the template, ensure the following:

Power BI Desktop is installed on your computer.

You can find Template under this link:

A service account email, service account JSON key and Dataset Name are available. You can find all this information on RevOS site under Integrations.

💡

If you haven't connected Power BI or Microsoft Fabric yet, please refer to this article for a step-by-step setup guide.

You have access to HubSpot data, including the required tables (deals, contacts, companies, and deal_pipelines).

💡

You are not limited to this data. The template is preconfigured with these tables, but you can always load additional data from your data source to extend and customize your reports.

Step-by-Step Connection Guide

Open the Template:

Launch Power BI Desktop.

💡

Please Note: Ensure you have the latest version of Power BI Desktop installed. Using an outdated version may lead to compatibility issues and unexpected problems.

Open the RevOS HubSpot template file.

Enter Dataset Details:

Upon opening the template, you will be prompted to specify:

Dataset Name: Enter the name of your HubSpot dataset.

HubSpot_UID: Provide your unique HubSpot identifier.

Provide Service Account Credentials:

Input the service account email.

Input the service account JSON key.

Wait for Data Loading:

After connection setup, data connection may take up to 15 minutes.

Data loading can take longer. It is depending on size of your data.

Verify the Connection:

Check the imported data tables and relationships to confirm successful setup.

3. Using the Template

Semantic Model Overview

The semantic model is structured to ensure seamless data relationships while maintaining the uniqueness of IDs across all tables. This design allows for flexible expansion while preserving data integrity. Key aspects of the model include:

Central Structure and Connections

hubspot_deals as the Central Table:

The hubspot_deals table serves as the primary connector, linking all other tables.

Connections to hubspot_companies and hubspot_contacts are made through intermediary "connection" tables. This approach prevents duplicates and ensures each table retains unique IDs for companies, contacts, and deals.

Direct Connection with hubspot_deals_pipelines:

Due to its structured nature, the hubspot_deals_pipelines table is directly linked to hubspot_deals without creating duplicates.

Dates Table for Filtering:

A dates table is created based on the closedate property from hubspot_deals. This table enables the use of a dates filter on the report page, allowing for efficient time-based filtering.

Adding Extra Data to the Template

To include additional data in your Power BI template, follow these steps:

Navigate to "Get Data":

In Power BI Desktop, select the "Home" tab from the ribbon at the top of the screen.

Click on the "Get Data" button, then select "More..." to access the full list of data sources.

Search for Google BigQuery:

In the "Get Data" window, type "BigQuery" into the search bar.

Select Google BigQuery from the search results and click "Connect".

Log in and Browse Tables:

After connecting, log in with your Google account credentials.

Once logged in, Power BI will display all available tables in your corresponding workspace (named after the your dataset).

Select and Load Tables:

Browse through the listed tables in your workspace.

Choose the ones you wish to integrate into your analysis and click "Load" to import them into Power BI.

💡

Tip: After importing the tables, ensure they are properly connected to the semantic model. Use Power BI's Model View to create relationships between the new tables and existing ones if necessary. This will ensure seamless integration and accurate reporting.

Overview of Predefined Visuals

The template includes multiple useful visuals. All visuals are interconnected, allowing users to filter page elements dynamically based on selections made in tables or other visuals. Key visuals include:

Dates Filter: Focus on the required time period to refine your analysis.

Net Revenue, Lost Amount, and Pipeline Revenue Metrics: Summarize key revenue data at a glance.

New vs. Existing Business Pie Chart: Display the proportion of new and existing business deals.

Won vs. Lost Deals Pie Chart: Highlight the count of deals that were won or lost.

Deal Path to Success Funnel: Visualize the journey of deals from initial stages to closure, including the success rate of closed deals.

Revenue Trends Over Time Line Chart: Track Net Revenue, Lost Amount, and Pipeline Revenue over a defined period.

Companies Table: Provide details on the number of deals per company, along with their won/lost percentages.

Deals Table: List individual deals with contact information and current statuses.

Practical Use Cases

Performance Analysis:

Identify your most profitable clients and top-performing companies.

Analyze the win rate to optimize sales strategies.

Revenue Tracking:

Monitor revenue trends and assess the impact of lost deals.

Company-Specific Insights:

Compare deal performance across different companies.

Use visual breakdowns to focus on high-priority clients.

4. Additional Notes and Troubleshooting

Data Refresh:

Ensure periodic refresh to keep the data up to date.

Known Issues:

Problem: Data not loading after 15 minutes. Solution: This is a rare issue. Power BI will notify you if there is invalid data or table errors. If the connection process continues for more than 30 minutes, check the following:

Verify your internet connection.

Ensure you are using the latest version of Power BI Desktop.

5. FAQs

Q: Can I modify the template after setup? A: Yes, you can customize visuals and measures to suit your specific needs. You can also get more data from your datasource.

Q: What types of HubSpot data can I integrate beyond the default tables?

A: You can integrate any data available in your HubSpot account, such as marketing campaign performance, customer feedback, or custom properties. Make sure the data structure aligns with Power BI requirements.

Q: What if my HubSpot data contains custom fields?

A: The template is designed with default HubSpot properties in its measures and visuals. Custom properties are not preconfigured in the template but can be added after setup. Once the template is connected to your data, you can map your custom fields during the data import process and include them in your visuals and measures as needed. This flexibility allows you to tailor the reports to your specific requirements.

Q: How can I optimize performance for large datasets?

A: Use DirectQuery mode instead of Import mode, limit the number of visuals on a page, or apply filters to reduce the dataset size before importing it into Power BI.

Q: Can I automate the data refresh process?

A: Yes, you can schedule automatic refreshes using Power BI Service. Ensure your data source credentials are correctly configured and up to date.

Q: Can I share the reports with other team members?

A: Yes, you can share Power BI reports via Power BI Service by publishing them to your workspace. Ensure team members have appropriate permissions and access to the data.

Q: How do I ensure data security when sharing reports?

A: Use row-level security (RLS) in Power BI to restrict data visibility based on user roles. Avoid sharing sensitive credentials like JSON keys directly with team members.