HubSpot is a popular CRM app. One of the core concepts in HubSpot is “Deals”. In HubSpot, the concept of "deals" refers to the individual sales opportunities or potential transactions that your sales team is actively working on. Deals represent the progression of a lead or prospect through your sales pipeline, from initial contact to closure.
In HubSpot's Deals object, several fields are available to store information related to each deal. The specific fields may vary depending on your HubSpot subscription plan and customizations, but some common fields available in the Deals object include the deal’s name, stage, close date, probability, source, status, close reason, create and modified date. With this data you can create various analyses or reports using spreadsheet software such as Google Sheets.
Here are the top 8 analysis and reports you can generate with Hubspot deal data in Google Sheets. For each type of analysis, we’ll state the required fields and specific formulas to calculate values. Note that you may need to adjust the formulas based on your specific data structure and column names.
Deal Conversion Analysis
Analyze the conversion rates at different stages of your sales pipeline to identify bottlenecks or areas for improvement. This analysis can help you understand how effectively deals progress through each stage and where potential issues lie.
Required Fields: Deal Stage, Deal Status
Formula: =COUNTIFS(Deals!B:B, "Deal Stage 1", Deals!C:C, "Closed Won") / COUNTIFS(Deals!B:B, "Deal Stage 1")
Description: This formula calculates the conversion rate from Deal Stage 1 to Closed Won. Adjust the criteria and stage names as per your pipeline stages.
Win/Loss Analysis
Analyze the reasons behind won and lost deals to identify patterns, common objections, or weaknesses in your sales process. This analysis can help you refine your sales strategy and address any recurring issues.
Required Fields: Deal Status, Close Reason
Formula (Won Deals): =COUNTIFS(Deals!C:C, "Closed Won", Deals!D:D, "Reason A")
Formula (Lost Deals): =COUNTIFS(Deals!C:C, "Closed Lost", Deals!D:D, "Reason A")
Description: Replace "Reason A" with the specific reasons you want to analyze, and use these formulas to count the number of won and lost deals for each reason.
Deal Velocity Analysis
Measure the time taken for deals to move through the sales pipeline from creation to closure. This analysis can help you identify areas where deals tend to get stuck, enabling you to streamline your sales process and improve efficiency.
Required Fields: Deal Created Date, Deal Close Date
Formula: =AVERAGE(Deals!E:E - Deals!D:D)
Description: This formula calculates the average time taken for deals to move from creation to closure. Make sure the columns (E and D) correspond to the correct dates in your HubSpot Deals data.
Sales Performance Analysis
Evaluate the performance of your sales team by analyzing deal volume, revenue generated, or other relevant metrics. This analysis can provide insights into individual and team performance, helping you identify top performers and areas where additional coaching or support may be required.
Required Fields: Deal Owner, Deal Amount
Formula: =SUMIF(Deals!A:A, "Salesperson A", Deals!B:B)
Description: Replace "Salesperson A" with the name of the salesperson you want to analyze. This formula calculates the total deal amount associated with that salesperson.
Deal Source Analysis
Assess the effectiveness of different lead sources by analyzing the source of deals and their corresponding conversion rates or revenue contribution. This analysis can help you allocate resources more efficiently and invest in lead sources that yield the highest returns.
Required Fields: Deal Source, Deal Amount
Formula: =QUERY(Deals!A:F, "SELECT B, SUM(F) WHERE C = 'Deal Stage' GROUP BY B")
Description: This formula uses a QUERY function to group deals by the source (column B) and calculate the total deal amount (column F) for each source. Adjust the query criteria as needed.
Deal Stage Duration Analysis
Measure the average duration spent in each deal stage to identify stages that require more attention or where deals tend to get delayed. This analysis can help you optimize your sales process and improve forecasting accuracy.
Required Fields: Deal Stage, Stage Entry Date, Stage Exit Date
Formula: =AVERAGEIFS(Deals!D:D, Deals!B:B, "Qualification") - AVERAGEIFS(Deals!C:C, Deals!B:B, "Qualification")
Description: Replace "Qualification" with the specific stage you want to analyze. This formula calculates the average duration spent in that stage.
Deal Value Analysis
Analyze the distribution of deal values to identify trends, such as average deal size, the distribution of high-value deals, or the impact of deal size on win rates. This analysis can help you understand your sales pipeline's composition and set realistic revenue targets.
Required Fields: Deal Amount
Formula: =AVERAGE(Deals!F:F)
Description: This formula calculates the average deal value across all deals. You can modify it to calculate other metrics like median, maximum, or minimum deal value.
Deal Forecasting
Utilize historical deal data and pipeline metrics to forecast future revenue or predict the likelihood of deals closing within a specific time frame. This analysis can help you with sales forecasting and resource planning.
Required Fields: Deal Close Date, Deal Amount
Formula: =SUMIFS(Deals!F:F, Deals!D:D, ">="&TODAY(), Deals!C:C, "Open")
Description: This formula calculates the total deal amount for all open deals with a close date greater than or equal to today's date.
These formulas assume that the HubSpot Deals data is present in a sheet named "Deals" and that the relevant columns are appropriately mapped. Adjust the sheet name and column references based on your actual data structure.
