Understanding where your leads come from is crucial for any marketer. Analyzing your HubSpot lead sources empowers you to identify the channels driving the most qualified leads, optimize your marketing efforts, and ultimately boost your ROI. This guide delves into performing a comprehensive HubSpot lead source analysis using the familiar and accessible platform – Google Sheets.

Why Analyze Lead Sources?

HubSpot's lead source functionality tracks the origin of your leads, providing valuable insights into your marketing channels' effectiveness. Analyzing this data unlocks a treasure trove of benefits:

• Identify Top Performers: Pinpoint the channels generating the most leads and the highest quality leads, allowing you to double down on successful strategies.
  
  

• Optimize Campaigns: Uncover underperforming channels and tailor campaigns to improve their lead generation capabilities.
  
  

• Track ROI: Evaluate the return on investment for each marketing channel by analyzing the cost per lead (CPL) and conversion rates.
  
  

• Inform Budget Allocation: Distribute your marketing budget strategically, allocating more resources to channels with the best lead generation performance.
  
  

Additionally, integrating other CRM tools like Pipedrive to Google Sheets can expand your data analysis. By combining lead sources from multiple platforms, such as HubSpot and Pipedrive, you can create a holistic view of your lead generation efforts, allowing for a more comprehensive analysis of where your leads originate and how they perform.

The Potential of Pivot Tables for Lead Source Analysis

Imagine a large spreadsheet brimming with rows and columns of HubSpot lead source data. While the raw data holds valuable information, it can be overwhelming to identify patterns and trends. This is where pivot tables come in.

Here's how pivot tables can be incredibly useful in analyzing your HubSpot lead source data:

• Identifying Top Performers: Easily see which lead sources generate the most leads, qualified leads (MQLs), or customers by placing "Lead Source" in rows and the desired metric (count, sum) in the values section.
  
  

• Comparing Conversion Rates: Analyze conversion rates from different lead sources by placing "Lead Source" in rows and the conversion rate formula (COUNT(converted leads) / COUNT(total leads)) in the values section. This quickly reveals which sources deliver leads most likely to convert.
  
  

• Drilling Down for Deeper Insights: The beauty of pivot tables lies in their interactivity. Double-click on a specific cell within the pivot table to drill down and see the underlying data for that particular combination of row and column values. This allows you to explore trends within specific lead sources.

Guide to HubSpot Lead Source Analysis in Google Sheets using Pivot Tables

1. Exporting Data from HubSpot

Let's use Superjoin to extract data from HubSpot to Google Sheets. Superjoin is a free Google Sheets add-on that enables you to link your business platforms to Google Sheets, enabling you to import live data directly into your spreadsheet.

To begin, we'll need to install Superjoin. 

Install Superjoin

1. Go to your Google Sheets account and access the Extensions menu.
   
   

2. Select "Add-ons" > “Get add-ons” to open the Google Workspace Marketplace.
   
   

3. Search for "Superjoin" and install the app.
   
   

Connect Superjoin to HubSpot

1. Launch Superjoin from the Extensions > Add-ons option.
   
   

2. Click on “Launch Superjoin”
   
   

3. Fill in the necessary details and click on “Get Started”.
   
   

4. In the sidebar, select "Connect your initial source".
   
   

5. Select the connection from the list. In our case, it is “HubSpot”. However the same is applied for other connections.
   
   

6. Tap on “Connect”.
   
   

7. In a new window, choose the HubSpot account you wish to integrate with Superjoin. After selecting, click on "Choose Account".
   
   

8. Please ensure to carefully review all the applicable terms and conditions. Once done, provide your consent by ticking the box, then click on "Connect app".

Import Data from HubSpot

1. Personalize your data import by selecting the fields you want to include and preview your data visually before importing.
   
   

2. In the pop-up window, choose "Import".
   
   

3. The data will be seamlessly imported into your Google Sheet.

2. Clean Up and Organize

• The imported data might require some cleaning. Remove unnecessary rows or columns and ensure data types are consistent (e.g., dates formatted correctly).
  
  

• Prepare Your Data: Ensure that your dataset is organized in a tabular format with appropriate headers. In this case, we have headers such as "Lead ID," "Lead Source," "Lead Status," "Conversion Date," and "Conversion Amount"

3. Identify Lead Sources

• Take a look at the "Lead Source" column to identify the different sources from which leads are generated. In our dataset, the lead sources include Website, Social Media, Email, and Referral.
  
  

4. Create a Pivot Table from the Existing Dataset

• Select Data: Highlight the entire dataset, including the headers, containing lead information, including lead sources and lead statuses.
  
  

• Insert Pivot Table: Go to the "Insert" menu, then select "Pivot table." This will open a new dialog box.
  
  

• Select “New Sheet” and click “Create”. 
  
  

• This will create a blank Pivot table in a new sheet. 
  
  

• Choose "Count of Lead ID for each Lead Source by Lead Status" from the provided selections within the Pivot Table Editor.
  
  

This action will generate a pivot table containing the counts of converted, unconverted, and total values for each lead source. 

• However, this table may not contain all the required metrics. To add a new metric, let’s say “Amount”, click on the  “Add” option under the Pivot table editor.
  
  

• Select “Amount” from the options.
  
  

This action will add the Amount metric to the pivot table.

5. Calculate Conversion Rates

In a new column titled "Conversion Rate", use a formula to calculate the conversion rate for each lead source. To obtain the percentage, divide the number of converted leads by the total number of leads, then multiply by 100. For example in the cell F5 type: =(C5/E5)*100

This would derive your Email Conversion Rate. Do this for all other totals.

6. Analyze and Interpret

• With your data cleaned and organized, analyze the results. Look for trends in lead generation across different channels.
  
  

• Identify the top channels in terms of lead quantity and quality (consider conversion rates).

5. Visualize Your Findings

• Visual aids such as charts and graphs are effective instruments for illustrating your discoveries. Google Sheets offers a variety of chart options, like bar charts or pie charts, to visually represent your lead source data.
  
  

• Create clear and concise charts highlighting key takeaways, such as the top lead channels or the distribution of leads across different sources.

6. Actionable Insights

• Based on your analysis, formulate actionable insights to optimize your marketing strategy.

• Consider allocating more resources to high-performing channels.

• Develop strategies to improve the effectiveness of underperforming channels.

• Track the impact of these changes over time by repeating the analysis at regular intervals.

Advanced Tips and Techniques

This guide has equipped you with the essential tools and knowledge to perform a comprehensive HubSpot lead source analysis in Google Sheets. Now, let's explore some advanced tips and techniques to elevate your analysis and gain even deeper insights:

Custom Scripts with Google Apps Script

• Conditional Formatting Automation: Utilize Google Apps Script to automate conditional formatting rules based on specific criteria within your lead source data. This can dynamically highlight top-performing and underperforming lead sources for a quick visual reference.
  
  

• Customizable Data Transformations: Go beyond the capabilities of built-in formulas. Write custom scripts to manipulate and transform your data in unique ways to answer specific business questions.
  
  

• Scheduled Data Cleaning and Validation: Automate data cleaning tasks like removing duplicates or formatting inconsistencies using Google Apps Script. This ensures the accuracy and integrity of your analysis over time.

Leveraging Third-party Integrations

The Google Sheets ecosystem offers a vast array of add-ons that can further enhance your lead source analysis:

• Data Visualization Tools: Explore add-ons like "Chartify" or "Plotly" for creating even more sophisticated and interactive charts and visualizations within your dashboard.
  
  

• Data Storytelling Tools: Consider add-ons like "Narrative Science" or "Grow with Google" to generate data-driven narratives and reports, transforming raw data into compelling stories for stakeholders.
  
  

• Data Extraction Tools: Employ applications like Superjoin to automate the import of data from different connectors like HubSpot and Chargebee.
  
  

• Marketing Analytics Integration: If you use other marketing analytics platforms like Google Analytics or Facebook Ads, explore add-ons that allow you to import data from these platforms alongside your HubSpot data. This provides a holistic view of your marketing performance across different channels.

Advanced Segmentation Strategies

• Lead Scoring Integration: If you utilize lead scoring in HubSpot, consider incorporating lead score data into your analysis. This allows you to segment leads based on their score and analyze lead source performance concerning lead quality.
  
  

• Time-based Segmentation: Analyze lead source performance across different periods (e.g., weekdays vs. weekends, months, quarters). This can reveal seasonal trends or identify specific times when certain lead sources perform better.
  
  

• Campaign-specific Segmentation: Go beyond just high-level source analysis. Leverage UTM parameters or campaign tags within your HubSpot data to segment leads by specific marketing campaigns within each source. This allows you to pinpoint the most effective campaigns within each channel and identify areas for improvement.

Remember

• Focus on Actionable Insights: Don't get lost in the data. The ultimate goal is to translate insights into actionable marketing strategies and tactics.
  
  

• Regular Review and Iteration: Schedule regular reviews of your lead source analysis dashboard. As your marketing efforts evolve and new data becomes available, refine your analysis and adapt your strategies accordingly.
  
  

• Data Security and Governance: Ensure you have proper data security and governance practices in place, especially when sharing your analysis with others

Conclusion

By performing a comprehensive HubSpot lead source analysis in Google Sheets, you gain valuable insights into the effectiveness of your marketing efforts. This empowers you to make data-driven decisions, optimize your campaigns, and ultimately achieve

Say goodbye to tedious data exports! 🚀

Are you tired of spending hours manually exporting CSVs from different tools and importing them into Google Sheets? 

Superjoin is a data connector for Google Sheets that connects your favorite SaaS tools to Google Sheets automatically. You can get data from these platforms into Google Sheets automatically to build reports that update automatically.
Bid farewell to tedious exports and repetitive tasks. With Superjoin, you can add 1 additional day to your week. Try Superjoin out for free or schedule a demo.