Create an automated Hubspot custom dashboard with Google Spreadsheet and Data Studio
Introduction
This article is intended for managers that want to automate stuff in a MVP kinda way without the need of their technical team. In this case we’ll create an automated Hubspot custom dashboard from scratch using Google Spreadsheet and Google Data Studio.
I am Alex, one of the founders at Make it — Tech Startup Studio. We build startups as a Venture Studio. Check us out here: makeit-studio.com. If you have any questions, insights, comments, suggestions, ideas or whatever you are up to, you can reach out to me here on my Linkedin profile. Looking forward to hear from you.
My commitment:
- It’s free
- It’s straightforward
- It gets the job done
Knowing Javascript is not necessary if you just copy & paste my code but it might be a plus to customise your dashboard further.
If you are just looking for a simple example on how to fetch your contacts from Hubspot and write them into a Google Spreadsheet, I recommend reading this other article I wrote: How to retrieve your contacts from Hubspot in Google Spreadsheet using App Script
If you don’t use Hubspot but just want to create a custom dashboard, you might want to check my previous article: How to create a custom dashboard for your startup’s KPIs with Google Spreadsheet and a Raspberry Pi ?
Context
Our company invests a lot of money into putting our brand, products or services out there. But what generates the most qualified leads? At what point do our leads convert or go away? At the end of the day, we want to invest in the channels that work the best but we need that data to make smart decisions.
In the past, we didn’t use any metrics, and the decisions were based on our intuition, which is fine to a certain extend. However, one might be surprised at what the numbers can tell us about our businesses and often, our intuitions give us false signals.
Let’s dig in
In order to achieve our mission we’ll need to do the following:
- Create a development account on Hubspot
- Use a Google Spreadsheet to authenticate to Hubspot using its APIs
- Retrieve the deals from Hubspot using its APIs
- Use Google Spreadsheet to prepare the data according to our needs
- Connect our Google Spreadsheet with Google Data Studio and create a report
Let’s go.
Create a development account on Hubspot
This is pretty straightforward by creating a developer account on Hubspot.
Once our account is ready, we’ll need to create an application to retrieve our Client ID and Client secret. Those will be used to connect our Google Spreadsheet and our Hubspot CRM account.
Make sure to indicate the right scopes in the settings of your Hubspot app as shown below:
Use a Google Spreadsheet to authenticate to Hubspot using its APIs
I have used this tutorial if you need more info, but we will go through each step.
First we need to create a new Google Spreadsheet in our Google Drive. Once the Spreadsheet open, let’s go to Tools > Script editor, which will open a new tab in the browser.
We’ll need to add a library to authenticate using OAuth2:
- Click on the menu item Resources > Libraries…
- In the Find a Library text box, enter the script ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click the Select button. This is the easiest way to add the library to our project, but check out this article if you want to add the code manually into the project.
- Choose a version in the dropdown box (usually best to pick the latest version).
- Click the Save button.
Now, let’s write some code step by step. If you are a techie with some expertise in JS, you might want to just read the full code here. For the other ones, just follow the steps.
First we’ll need to define some variables for the authentication part :
var CLIENT_ID = '…'; // Enter your Client ID
var CLIENT_SECRET = '…'; // Enter your Client secret
var SCOPE = 'contacts';
var AUTH_URL = 'https://app.hubspot.com/oauth/authorize';
var TOKEN_URL = 'https://api.hubapi.com/oauth/v1/token';
var API_URL = 'https://api.hubapi.com';Let’s add the following functions that will handle the authentication part:
function getService() {
return OAuth2.createService('hubspot')
.setTokenUrl(TOKEN_URL)
.setAuthorizationBaseUrl(AUTH_URL)
.setClientId(CLIENT_ID)
.setClientSecret(CLIENT_SECRET)
.setCallbackFunction('authCallback')
.setPropertyStore(PropertiesService.getUserProperties())
.setScope(SCOPE);
}function authCallback(request) {
var service = getService();
var authorized = service.handleCallback(request); if (authorized) {
return HtmlService.createHtmlOutput('Success!');
} else {
return HtmlService.createHtmlOutput('Denied.');
}
}
Now we need the function to run the authentication:
function authenticate() {
var service = getService(); if (service.hasAccess()) {
// … whatever needs to be done here …
} else {
var authorizationUrl = service.getAuthorizationUrl();
Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
}
}
Let’s test the authentication part. If you have filled in the Client ID and Client secret and you run the authenticate function, you will need to:
- Retrieve the Authorization URL that will is shown in the Logs (View > Logs).
- Open that URL in your browser, your Hubspot accounts will show up.
- Select the Hubspot account you want to connect to your Spreadsheet
Well done, you are in!
Retrieve the deals from Hubspot using its APIs
Let’s keep it simple, let’s just do the bare minimum to retrieve that data to populate our spreadsheet. We’ll then use the tools of Google Spreadsheet to prepare the data according to our needs.
(If you are a developer, you will be tempted to go crazy and work on the data in javascript, however, I would suggest not reinventing the wheel and use the built in features of Google Spreadsheet.)
Let’s start by getting your pipeline stages. You can copy & paste the following function into your Script editor underneath the existing code:
function getStages() {
// Prepare authentication to Hubspot
var service = getService();
var headers = {headers: {'Authorization': 'Bearer ' + service.getAccessToken()}};
// API request
var pipeline_id = "default"; // Enter your pipeline id here.
var url = API_URL + "/crm-pipelines/v1/pipelines/deals";
var response = UrlFetchApp.fetch(url, headers);
var result = JSON.parse(response.getContentText());
var stages = Array();
// Looping through the different pipelines you might have in Hubspot
result.results.forEach(function(item) {
if (item.pipelineId == pipeline_id) {
var result_stages = item.stages;
// Let's sort the stages by displayOrder
result_stages.sort(function(a,b) {
return a.displayOrder-b.displayOrder;
});
// Let's put all the used stages (id & label) in an array
result_stages.forEach(function(stage) {
stages.push([stage.stageId,stage.label]);
});
}
});
return stages;
}This piece of code will:
- Connect to Hubspot.
- Get the full default* pipeline configuration including its stages.
- It will sort the stages accordingly.
- Put the results into an array we can use later on.
*Hubspot CRM let’s you configure multiple pipelines. You should change default by any other pipeline id you might have setup. Check this page out if you want more info about the CRM pipelines API from Hubspot.
Now we can also retrieve all the deals flowing through our pipeline. This piece of code will take care of that:
NB: I have added a custom field “source” to our deals in our Hubspot configuration. This is not a default field from Hubspot. So I suggest you add this to your deals or change “source” by any other custom field you might want from your deals.
function getDeals() {
// Prepare authentication to Hubspot
var service = getService();
var headers = {headers: {'Authorization': 'Bearer '+ service.getAccessToken()}}; // Prepare pagination
// Hubspot lets you take max 250 deals per request.
// We need to make multiple request until we get all the deals. var keep_going = true;
var offset = 0;
var deals = Array(); while(keep_going) {
// We’ll take three properties from the deals: the source, the stage & the amount of the deal
var url = API_URL + "/deals/v1/deal/paged?properties=dealstage&properties=source&properties=amount&limit=250&offset="+offset;
var response = UrlFetchApp.fetch(url, headers);
var result = JSON.parse(response.getContentText()); // Are there any more results, should we stop the pagination
keep_going = result.hasMore;
offset = result.offset; // For each deal, we take the stageId, source & amount
result.deals.forEach(function(deal) {
var stageId = (deal.properties.hasOwnProperty("dealstage")) ? deal.properties.dealstage.value : "unknown";
var source = (deal.properties.hasOwnProperty("source")) ? deal.properties.source.value : "unknown";
var amount = (deal.properties.hasOwnProperty("amount")) ? deal.properties.amount.value : 0;
deals.push([stageId,source,amount]);
});
}
return deals;
}
This piece of code will:
- Connect to Hubspot.
- Get all the deals and add the following properties: stage, source & amount of the deal.
- Put the results into an array we can use later on.
More info about how to retrieve the deals can be found here.
Ok, we have the data in arrays, but now if we want to be able to play with them using Google Spreadsheet we need to print the data into sheets. Let’s first create two sheets into our spreadsheet, let’s call them Stages and Deals.
var sheetNameStages = "Stages";
var sheetNameDeals = "Deals";function writeStages(stages) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetNameStages); // Let’s put some headers and add the stages to our table
var matrix = Array(["StageID","Label"]);
matrix = matrix.concat(stages); // Writing the table to the spreadsheet
var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
range.setValues(matrix);
}function writeDeals(deals) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetNameDeals); // Let’s put some headers and add the deals to our table
var matrix = Array(["StageID","Source", "Amount"]);
matrix = matrix.concat(deals); // Writing the table to the spreadsheet
var range = sheet.getRange(1,1,matrix.length,matrix[0].length);
range.setValues(matrix);
}
This piece of code will respectively write the stages and deals into the right sheets by the following steps:
- Connect to the right sheet.
- Prepare a table with the right columns.
- Adding the data to the table.
- Writing the data to the sheet.
That’s it! Most of the code is done, we just need to create a simple routine that we can call recurrently using Project triggers:
function refresh() {
var service = getService();
if (service.hasAccess()) {
var stages = getStages();
writeStages(stages); var deals = getDeals();
writeDeals(deals);
} else {
var authorizationUrl = service.getAuthorizationUrl();
Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
}
}
This routine will:
- Try to connect to Hubspot
- If it succeeds it will get the data and print it to the appropriate sheets
- If it doesn’t succeed (probably the first time you run the script) it will print the Authorization URL in the Logs (View > Logs) that you will need to copy & paste into your browser.
Now we can tell our script to run the refresh() function every hour to keep retrieving the most up to date data. You can go to Edit > Current Project’s Triggers.
You can find all the code here for your convenience as well as the Google Spreadsheet.
It’s time to test our script and see what happens. Don’t forget, the first time you will run the script, you will need to authenticate to Hubspot as explained before. Then, if the code runs without errors (which it should if I did my job properly ;-)), you can go to your Google Spreadsheet and see the populated data in the proper sheets.
Use Google Spreadsheet to prepare the data according to our needs
We were able to retrieve the data from Hubspot with above script. Now, we can use Google Spreadsheet to prepare the data as we want it before sending it to Google Data Studio. So, what do I want to know ?
- Are there any bottlenecks in my pipeline ? In other words: what is the current number of deals per stage in the pipeline.
- Is my pipeline sufficiently provisioned with money so I can forecast my cash flow ? In other words: What is the current amount of money in each stage.
- What channels work the best and in which one should I invest more ? In other words: What are the conversion rates and amounts of money generated per source.
For all of those numbers and with the data I got from Hubspot, I just need to create a few Pivot tables. To create one, we go to our Google Spreadsheet and click on Data > Pivot table.
Before creating our first Pivot table though, we’ll need to link our Stages sheet with our Deals sheet. Indeed, our Deals sheet contains our StageIDs but we’d rather have a full table containing the label of our Stages as well, otherwise our report will only display the IDs which is not really user friendly.
In the Deals sheet, go the D column and add this line in the first cell of the column:
=ARRAYFORMULA(IFERROR(VLOOKUP(A:A,Stages!A:B,2,FALSE),"Unknown"))Let’s decompose this line:
- VLOOKUP(A:A,Stages!A:B,2,FALSE): Please match the A column with the table in Stages and get me the value in the second column.
- IFERROR: If you don’t find any match, just let me know as ‘Unknown’
- ARRAYFORMULA: Apply this rule to all my column.
Okay, we are ready, let’s create our first Pivot table to get the current number of deals per stage.
Create a new Pivot table, it will generate a new sheet that you can rename by Stages: Count. Follow the following steps in the Pivot table editor:
- Select a Data range. The data range should be the whole data range in your Deals sheet.
- Add a Row and choose StageName
- Add a Value and choose StageID and set Summarize by: COUNTA
You now have a table containing the amounts of deals per stage. We’ll use this table in our Google Data Studio report.
We can do the same to retrieve the amount of money in each stage. Let’s create a new Pivot table but instead of choosing StageID as Value, let’s take Amount and set Summarize by: SUM.
To get the conversion rates per source we can create a next Pivot table with the following parameters:
- Row: Source
- Column: StageName
- Value: StageID Summarize by COUNTA
We can then add a column at the end of the pivot table where we calculate the conversion rate. This will depend on your stages but in my case the stage where the conversions happen is called Closed Won. I therefore calculate my conversion rate by dividing the Closed Won column with the Grand Total one.
Finally, to get the amount of money generated by source I create a last Pivot table with following parameters:
- Row: Source
- Column: StageName
- Value: Amount Summarize by SUM
The Closed Won column indicates the amount of money generated by each source.
We are ready with all the datasets required so far, let’s go now to Google Data Studio for the last piece ;-)
Connect our Google Spreadsheet with Google Data Studio and create a report
In the above steps, we retrieved the data from Hubspot with its API using OAuth2. Then we prepared the data with Google Spreadsheet. We are now ready to display that data in a nice custom dashboard using Google Data Studio.
It is pretty straightforward so I am not going to go too much into details, I will just tell you the basics to get started. First, we’ll need to create a new report. Then we’ll need to connect a data source. Data Studio let’s you choose between different data sources and in particular a Google Spreadsheet, which is the one we’ll need obviously.
Of course, you might want to check all the other ones as well, especially Google Analytics, Adwords or any other tools you might already use for your business. There are even community connectors as well (unfortunately, no Hubspot connector exists to this date).
Let’s create our first bar chart showing the conversion rate per source.
When adding a plot, a menu appears on the right where we can edit the data source. At this time we need to add the data source from the Sources: Count & Conversion Rates sheet in which we created the pivot table containing the conversion rates.
When you add a Google Spreadsheet data source, it will let you configure the different fields that are detected from the chosen sheet. Usually it autofills the Aggregation column with SUM when it detects numbers. In most cases I just toggle all those fields as None.
Once the connection has been made, we should add the proper Dimension and Metrics. For instance, we can setup Source for the Dimension and Conversion Rate as metrics to get the above graph.
We will need to add a data source into Data Studio for each sheet or pivot table we created in Google Spreadsheet. Once all the data sources have been added, we can just replicate the above steps to create all the graphs, scorecards or any other fancy tool you want to add to your custom dashboard.
Bonus: What about having the data over time to see the evolution of our performance day by day ?
For instance, I want to know how leads evolve over time, where our team is improving or lacking, what channels I should review as they haven’t sent any leads since a few days or lastly what the impact it had on the sales pipeline when one sales person of our team was sick last week.
There is an easy way to log the data over time. Here’s a piece of code you can add to the script to achieve this:
function logSources() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sources: Count & Conversion Rates");
var getRange = sheet.getRange("M3:M13");
var row = getRange.getValues();
row.unshift(new Date);
var matrix = [row]; var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Log: Sources");
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn(); // Writing at the end of the spreadsheet
var setRange = sheet.getRange(lastRow+1,1,1,row.length);
setRange.setValues(matrix);
}
Here’s what it does:
- It copies the right column in the right sheet and stores it into an array.
- It adds the date at the beginning of the array.
- It selects and pastes the data in the last row of the Log: Sources sheet.
We can do the same for the Stages.
Lastly, we can add the project triggers so the data is logged once a day.
The new data can then be plugged into our Google Data Studio similarly as explained above.
Final result
You will find a screenshot of our final custom dashboard here below after following the above steps. It’s pretty straightforward, and we can already have a pretty awesome overview of what’s happening in our sales pipeline. We just need to wait a couple of days to see the evolution over time and see how our sales team is performing.
For your convenience, please find the following used documents:
- Complete Google App Script: click here
- Sample Google Spreadsheet: click here
- Sample Google Data Studio dashboard: click here
Next steps ?
At the time I write this tutorial, no community connector exists to connect Hubspot CRM to Datastudio. It might be a good thing to actually build it so others will be able to benefit from it. However, using Google Spreadsheet as an intermediate step gives your more flexibility and power to do whatever suits your needs. If Hubspot is your data source, Data Studio your dashboard, you might want to consider keeping your Spreadsheet as a “brain” to interpret your data before sending it and displaying it with Data Studio.
Please comment to get your insights.
Questions or suggestions ? Contact me on LinkedIn or check us out at here.
