This is a guest post that originally appeared on the Sequin docs. Learn how Sequin unleashes APIs at sequin.io!
In this tutorial, you'll build a tool to manage leads in Hubspot using Retool. The app will allow your sales team to enrich and qualify new leads all in one place.
Retool doesn't come with a native HubSpot integration. And while you can use Retool's REST API integration with HubSpot, because HubSpot's API comes with rate limits and pagination, it's a bit clumsy to work with.
Luckily, Sequin lets you integrate with HubSpot using a Postgres database so you can use Retool's first class support for SQL to work with your HubSpot data. Sequin will handle all the details of the sync and the API so you can focus on building your app. In this tutorial you'll learn how this all works together to build a tool to scale your inbound sales pipeline. Outside of Sequin and Retool, you'll just need a little SQL and JavaScript to wire it all together.
The scenario for this tutorial is very common: New users sign up for your service and your sales team needs to do a little research to determine if these new users are qualified leads.
While an everyday task, this kind of workflow in HubSpot is cumbersome: Lots of repetitive copy, paste, and page reloads. Creating and maintaining a custom set of interfaces in HubSpot isn't an easy option.
This is where Retool comes in. In fact, this tutorial is inspired by an existing Retool template originally built for Salesforce. This tutorial showcases an even easier and faster route.
For this tutorial, you'll need access to a standard or dev HubSpot account. You'll work with the standard contact and company objects throughout this tutorial. You'll also work with a custom property to structure and store information the sales team finds during their research, like the link to the contact's LinkedIn profile.
To get started, create a custom LinkedIn property on the contact object in HubSpot:
- Log into your HubSpot account and navigate to Settings by clicking the gear icon in the top right (next to your user thumbnail).
- On the settings page, navigate to Data Management → Objects → Contacts. Then, click Manage contact properties:
- On the Properties page click the orange Create property button. The create new property drawer will open where you can define the basic information for your new
LinkedInproperty. Enter the following information: - Click Next to open up the Field Type step. Since you'll be storing a URL in this property, select Single-line text from the dropdown and click Create to add this new property to your contacts in HubSpot.
If you want to follow this guide exactly as written, you'll also want to customize the Lead Status property in HubSpot. These are the statuses your sales team will use to indicate if a contact is a good fit.
- Back on the Properties page, search for and select the Lead Status property:
- In the Edit property drawer, edit the options to be
New,Contacted,Qualified,Unqualified:
As a last step, create a couple new leads you can work with in this tutorial by clicking the New button on the leads page or upload this sample set by clicking Import.
Create a two-way sync between HubSpot and your Postgres database so you can easily work with the leads you just created.
- Go to https://app.sequin.io/signup and create an account.
- Connect HubSpot by going through the tutorial or clicking the Add Sync button.
- Follow the HubSpot authentication steps by entering your HubSpot username and password in the modal:
- You'll now select which HubSpot objects and fields you want to sync. For now, select the
contactandcompanyobjects. You can adjust which objects and fields you sync at any time: - In the Destination section, you can connect to your Postgres database. Or, for the purposes of this tutorial, spin up a Sequin demo database. Just click Launch or Connect and then select Launch.
- When everything is configured, click Create. Sequin will now start backfilling all your HubSpot contacts and companies to Postgres and maintain a real-time, two-way sync. You'll be provided with credentials for you new Sequin demo database:
Now, add your Sequin database to Retool just as you would any other Postgres database:
- In a new tab, log into Retool. In the top menu bar click Resources and then the blue Create New button.
- Select Postgres from the list of resource types.
- Enter the name for your resource (i.e. "HubSpot Database") and then enter the
Host,Port,Database name,Database username, andPasswordfor your Sequin database. You can copy and paste these from Sequin. Then click the blue Create resource button: - Retool will confirm that your resource was created. Click Back to resources for now.
With HubSpot successfully connected to Retool using Sequin, you are ready to build an app that allows your sales team to quickly qualify leads.
First, get the app set up in Retool.
- On the Retool home page, click the blue Create new button and select App:
- Give your app a name. Something like Inbound Qualifier will work just fine and then click Create app.
- You'll now see a blank Retool app in edit mode. To start building the app, drag and drop a text field into the header. Then, in the inspector panel on the right, enter
# Inbound Qualifieras the value to give your app a nice H1 header:
This is the basic flow for adding new components to your app:
- Drag and drop the visual components into your app.
- Configure the data and interactions for the component.
- Polish the UI of the component.
You'll follow this construction pattern as you build the rest of the app from here on out.
You are now ready to build the core functionality of your app, starting with a searchable table that shows new contacts.
You'll start by dragging and dropping the components you need for this section of the app onto the canvas:
- Drag and drop a Container component to the top left corner of the canvas. Resize it to a width of about 9 columns.
- Drag and drop a Table component into the main section of the container you just created.
- Delete the text in the header of the container. In its place, drag over a Text input component. This will serve as the search bar — so in the inspector on the right, change the label for this component to
Lead Search. - To finish this section of the UI, drag and drop a Multi-select input component into the top right of the container on the canvas. This will allow your sales team to filter the leads by status. So in the inspector, change the Label for this component to
Lead Status.
At the end of these steps, your app will look something like this:
To add the underlying HubSpot leads to your app, you'll query your Sequin database using SQL. To step into this paradigm, you'll first query for the data you need and then expand your query to add search and filtering.
- Open up the bottom panel in Retool. Select query1 from the list of queries on the left. Then, select the HubSpot Database you created earlier as the resource. Enter the SQL statement below:
1SELECT
2 contact.id as "contactid",
3 contact.createdate,
4 contact.firstname || ' ' || contact.lastname as "fullname",
5 contact.email,
6 contact.jobtitle,
7 contact.linkedin,
8 contact.hs_lead_status as "status",
9 company.id as "companyid",
10 company."name" as "companyname",
11 company.numberofemployees,
12 company.industry,
13 company.website
14FROM
15 contact
16LEFT JOIN company
17 on contact.associatedcompanyid = company.id
18- When you click the Preview button you'll see that this query pulls in the fields your sales team needs to evaluate a contact, including the custom
LinkedInfield you created earlier: - This query looks good for now, so click the Save & Run button and then name the query
get_leadsby clicking the query in the list on the left. - To connect the data from the
get_leadsquery you just created to the table component in your app, open the inspector in the right panel, select the table in the canvas, and in the data field enter{{get_leads.data}}. The double brackets (i.e.{{}}) indicate that you are using JavaScript in Retool. Then, theget_leads.datais retrieving the data from your query. - You'll see the data from your query populate the table:
With the data flowing into your app, you can now connect your search and filter inputs to your get_leads query.
- First, add search to your app by opening up your
get_leadsquery in the bottom drawer panel and add the followingWHEREclause: ThisWHEREclause is performing two actions: First, it checks if there is a value in the text input (i.e.textInput1.value). If there is nothing in the text input, then nothing happens. If there is text in the text input, then it uses Postgresiliketo search across your contact's names, emails, and companies. Remember, the double brackets indicate that you're using Javascript in the query.
1SELECT
2 contact.id as "contactid",
3 contact.createdate,
4 contact.firstname || ' ' || contact.lastname as "fullname",
5 contact.email,
6 contact.jobtitle,
7 contact.linkedin,
8 contact.hs_lead_status as "status",
9 company.id as "companyid",
10 company."name" as "companyname",
11 company.numberofemployees,
12 company.industry,
13 company.website
14FROM
15 contact
16LEFT JOIN company
17 on contact.associatedcompanyid = company.id
18WHERE ({{ !textInput1.value}}
19 or (contact.firstname ilike {{ '%' + textInput1.value + '%'}}
20 or contact.lastname ilike {{ '%' + textInput1.value + '%'}}
21 or contact.email ilike {{ '%' + textInput1.value + '%'}}
22 or company.name ilike {{ '%' + textInput1.value + '%'}}));
23- Next, add filtering to your app by adding one additional statement to your
WHEREclause: Like the priorWHEREstatement, this one also checks to see if there are any filters in themultiselectinput. If there are no values (i.e. the array is empty) then nothing happens. If there are filter values in themultiselect, then the query is filtered to just the leads with those specific statuses.
1SELECT
2 contact.id as "contactid",
3 contact.createdate,
4 contact.firstname || ' ' || contact.lastname as "fullname",
5 contact.email,
6 contact.jobtitle,
7 contact.linkedin,
8 contact.hs_lead_status as "status",
9 company.id as "companyid",
10 company."name" as "companyname",
11 company.numberofemployees,
12 company.industry,
13 company.website
14FROM
15 contact
16LEFT JOIN company
17 on contact.associatedcompanyid = company.id
18WHERE ({{ !textInput1.value}}
19 or (contact.firstname ilike {{ '%' + textInput1.value + '%'}}
20 or contact.lastname ilike {{ '%' + textInput1.value + '%'}}
21 or contact.email ilike {{ '%' + textInput1.value + '%'}}
22 or company.name ilike {{ '%' + textInput1.value + '%'}}));
23 and ({{ select2.value.length === 0 }} or contact.hs_lead_status = any({{select2.value}}));
24- Click the Save & Run button and you'll now see that when you search for a name, email, or company, the table immediately responds. But, your
multiselectisn't configured yet. To fix this, open the right panel and select yourmultiselectcomponent in the canvas. Configure the options to match the statuses in HubSpot:
Value Label Color New New "Purple" Contacted Contacted "RoyalBlue" Qualified Qualified "Green" Unqualified Unqualified "Red"
Your table is now easy to search and filter:
As a last step, clean up your table and interface to make it easy to use.
- Select the table in the canvas and open the right panel.
- Simplify the table by removing data that is helpful to the app, but not to your sales team. In this case, you can hide the
email,website,linkedin,contactid, andcompanyidcolumns from the table by clicking the eye icon in the inspector. - Next, name and format each column in your table by selecting each column in the inspector, changing the name, and setting the data type. For instance, edit the
created_datecolumn so the Column title is nowSign up, and the Column type isDate Time: - Finally, make the
statuscolumn standout. It's the key field your sales team will be working with the most. To make it pop, select thestatuscolumn, change the Column title toStatus, change the Column type toTag (Dropdown), and for Option color select JS and enter the following: This will make the color of each status tag match the colors you used in themultiselectfilter. Nice!
1{
2 {
3 item == "New"
4 ? "purple"
5 : "" || item == "Contacted"
6 ? "RoyalBlue"
7 : "" || item == "Qualified"
8 ? "green"
9 : "" || item == "Unqualified"
10 ? "red"
11 : "";
12 }
13}
14You now have a searchable table that allows your sales team to quickly filter and search leads as they come in. Next, you'll make it easy for your sales team to research and enrich leads with a click.
As your sales team finds and selects a contact in the table, you'll show the details of the contact on the right. Here, the sales person will be able to research the contact with a click and then edit the contact's details based on what they learn:
For this section, you'll repeat the same construction pattern by first scaffolding the UI, connecting the data, and then cleaning up the interface.
This section of the app is a form that is populated with data from the selected contact in the table. As before, start by getting the component parts of this section onto the canvas:
- Drag and drop a Container component next to the table. As before, delete the container title and in it's place drag and drop an Avatar component.
- Next, create the quick link buttons by dragging and dropping four Button components into the container. At this point, this section of the app will look something like this:
- Now, you'll add the editable inputs starting with Lead Status. Since you've already configured the Lead Status
multiselectcomponent in your table container, you can just duplicate that component to jump start this section of the app. To do so, right-click the Lead Status component and click Duplicate. Drag the duplicated component into your new container. You only need a single select component here (since a contact can only ever have one status). So change this component to a simpleselectcomponent by clicking the three dots in the top right corner of the inspector, selecting Switch component, and picking the Select component: - Next, drag and drop three Text Input components, one Number Input component, and one more Button component onto the canvas. Change the labels on each component so this section of your app looks like this:
You'll connect data to this section of your app in three phases. First, you'll want to populate the avatar and text input components with the details (eg. name, title, etc.) of the contact selected in the table. Next, you'll configure the quick link buttons. And finally, you'll make the form work by connecting the Update contact button to two queries that update the contacts and companies your Sequin database and HubSpot simultaneously.
- Select the Avatar component. In the inspector, set the Label field to
{{table1.selectedRow.data.fullname}}. This pulls in the name from the selected contact in your table. For the Caption pull in the contact's company by entering{{table1.selectedRow.data.companyname}}. Then, to make the avatar look nice, set the Fallback text field to{{table1.selectedRow.data.companyname}}. - Next, select the Lead Status component and set the Default Value to
{{table1.selectedRow.data.status}}. Repeat this process for the remaining Text input and Number input fields:
Field Default Value Position {{table1.selectedRow.data.jobtitle}} LinkedIn {{table1.selectedRow.data.linkedin}} Company {{table1.selectedRow.data.companyname}} Employees {{table1.selectedRow.data.numberofemployees}}
You'll now configure each Button so that your sales team can quickly research the contact by searching LinkedIn, Crunchbase, and the contact's website.
- Select the top left button and change the Text to
user. Then, configure the button's action by adding an Event handler. Click the + Add link and in the modal set the Action to Go to URL. For the URL enter: This URL is configured to pass in the selected contact's name as a search parameter to LinkedIn, saving the sales team multiple clicks. Nifty!
1{{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.data.fullname;}}
2- Repeat the steps above to configure the remaining buttons:
Button Text URL Company {{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.data.companyname}} Crunchbase {{"https://www.crunchbase.com/textsearch?q=" + table1.selectedRow.data.companyname}} Website {{table1.selectedRow.data.website}}
At this point, your app will look something like this:
As the sales team learns about the contact, they'll add a LinkedIn profile URL and enrich the leads details. When they are done, they'll click the Update Lead button to push those changes back to HubSpot. You'll now write two SQL queries that will post these changes to your Sequin database. From there, Sequin will handle the sync with HubSpot.
First, create a query to update the contact in your HubSpot database:
- Open the bottom panel and click to the + New button and select Resource query
- Select your HubSpot Database as the resource and, since you are writing data back to your database, select GUI mode. Then configure the query as follows:
- Table -
contact - Action type -
Update an existing record - Filter by -
id = {{table1.selectedRow.data.contactid}} - Changeset -
Key value pairs
- Table -
- You'll then map each column you want to update in the database to the component value in your app:
- hs_lead_status -
{{multiselect2.value}} - jobtitle -
{{textInput2.value}} - linkedin -
{{textInput3.value}}
- hs_lead_status -
- When you are done, click save and in the query list on the left name the query
edit_contact:
You just created a query to update the contact in your database. Now, create a query to update the company as well.
- Repeat the same steps above to create a new HubSpot database query in GUI mode, but this time configure the query to update your
companytable:- Table -
company - Action type -
Update an existing record - Filter by -
id = {{table1.selectedRow.data.companyid}} - Changeset -
Key value pairs
- Table -
- As before, you'll map each column you want to update in the database to the component value in your app:
- name -
{{textInput3.value}} - numberofemployees -
{{textInput4.value}}
- name -
- When your done, click save and in the query list on the left name the query
edit_company:
With your two UPDATE queries complete, you need to chain these queries together and connect them to your Update lead button so all the data for a contact updates with one click. You'll do this by creating event handlers such that when the button is clicked, you'll first trigger the edit_contact query, which will then trigger the edit_company query to complete the update. Finally, you'll trigger the get_leads query which will refresh all the data in the app. Here is how:
- Open the
edit_contactquery, and in the Event handlers section, click to + Add an on success event handler that will trigger theedit_companyquery. Save this change. - Now, open the
edit_companyquery. Again, go to the Event handlers section, click to + Add an on success event handler that will trigger theget_leadsquery. - As a last step, connect your queries to the Update lead button in your app by selecting the button in the canvas, opening the inspector on the right, clicking to + Add an event handler, and setting the Action to
Control query. Set the Query toedit_contact.
When you're done, you'll be able to easily edit and update leads:
You now have a functioning app the reads and writes data to HubSpot. To make it more usable, polish this component by changing the button colors, adding icons, and spacing out components. Here is one approach you can take:
- Add icons to the buttons and fields by selecting the button, opening the inspector, and setting Prefix icons.
- Change the colors of the buttons. In the inspector, make the save button green and help the user navigate by making styling the buttons.
- Drag and drop divider components to help space our the information.
One thing that is particularly hard to do in HubSpot that is easy to do with Sequin and Retool is allow your sales team to quickly edit multiple leads at once. For example, your team might qualify 5 new leads because they all come from the same company. Save your team the clicks by letting them just update the fields in your leads table. Here is how:
- First, make the Status field in your table editable. Select the table on the canvas, open the inspector in the right panel, and then click the Status column. In the modal, flip the switch to make the column editable. You'll now be able to double click a status in the table to open up a dropdown:
- As the sales team changes the status of multiple leads, you'll want to save these changes back to HubSpot. To do so, create a new query in the bottom panel with the following configuration:
- Resource -
HubSpot Database - Table -
contact - Action type -
Bulk update via primary key - Primary key column -
id - Array of records to update -
{{table1.recordUpdates.map(l => ({"id":l.contactid,"hs_lead_status":l.status}))}} - Event handler: Success -
Trigger get_leads
This query will take in eachcontactidfrom the array of objects intable1.recordUpdates, find the records that match theidin yourHubSpot Database, and thenUPDATEthehs_lead_statusfield in those records.
Click to Save your query and name itbulk_edit_lead_statusin the query list on the left.
- Resource -
- As a last step, connect your
bulk_edit_lead_statusquery to your table. Select the table in the canvas, open the inspector in the right panel, in the Interaction section click to + Add an event handler that will trigger thebulk_edit_lead_statusquery when the user clicksSave changes:
Now edit the status for a couple leads in the table and click Save changes at the bottom of the table:
Get ready for your inbound sales effort to scale!
You now have a custom internal tool that will help your sales team quickly qualify leads.
With Retool and Sequin, you didn't need to touch the HubSpot API docs, worry about the rate limit, write scripts to paginate queries, or consider response times. All you needed was a little SQL and a smidgen of JavaScript.
From here, tailor this app to your sales team's needs. Bring in data from your production database and join it to your HubSpot data to give your sales team even more context (Sequin can put your HubSpot data in your database!) Add more fields to let your sales team capture notes - or even trigger emails. Build in a new feature, like email list management. This is just the beginning.
Reader
