​

Building a Retool app that’s connected to HubSpot

In this tutorial, you’ll build a tool to manage HubSpot leads 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.

​

HubSpot Setup

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.

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:

1. Log into your HubSpot account and navigate to Settings by clicking the gear icon in the top right (next to your user thumbnail).

2. On the settings page, navigate to Data Management → Objects → Contacts. Then, click Manage contact properties:

3. 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 LinkedIn property. Enter the following information:

4. 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.

1. Back on the Properties page, search for and select the Lead Status property:

2. 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.

​

Sequin Setup

Create a sync between HubSpot and your Postgres database so you can easily work with the leads you just created.

1. Go to https://console.sequin.io/signup and create an account.

2. Connect HubSpot by going through the tutorial or clicking the Add Sync button. For step-by-step instructions, read the HubSpot setup guide.

3. Follow the HubSpot authentication steps by entering your HubSpot username and password in the modal.

4. You’ll now select which HubSpot objects and fields you want to sync. For now, select the contact and company objects. You can adjust which objects and fields you sync at any time:

5. In the Destination section, you can connect to your database. Or, for the purposes of this tutorial, spin up a Sequin demo database by clicking New Sequin Database.

6. When everything is configured, click Create. Sequin will now start backfilling all your HubSpot contacts and companies to Postgres and maintain a real-time sync. You’ll be provided with credentials for you new Sequin demo database.

​

Retool Resource Setup

Sequin uses a Postgres Proxy to interface with your Sequin-synced HubSpot tables. The Proxy lets Sequin capture inserts, updates, and deletes you make in your database and commit them to HubSpot.

To add Sequin’s Postgres Proxy as a Retool resource, you can treat it as a regular Postgres resource and enter the connection details in the resource configuration:

1. In a new tab, log into Retool. In the top menu bar click Resources and then the blue Create New button.

2. Select Postgres from the list of resource types.

3. Enter the name for your resource (i.e. “HubSpot Database”) and then enter the Host, Port, Database name, Database username, and Password for your Sequin database. You can copy and paste these from Sequin. Then click the blue Create resource button:

4. Retool will confirm that your resource was created. Click Back to resources for now.

​

Retool App Setup

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.

1. On the Retool home page, click the blue Create new button and select App:

2. Give your app a name. Something like Inbound Qualifier will work just fine and then click Create app.

3. You’ll now see a blank Retool app in edit mode. To start building the app, click the + button in the left toolbar and you’ll be given a list of components to add to your app. Drag and drop a text field onto the canvas. Then, in the inspector panel on the right, enter # Inbound Qualifier as the value to give your app a nice H1 header:

This is the basic flow for adding new components to your app:

1. Drag and drop the visual components into your app.
2. Configure the data and interactions for the component.
3. Polish the UI of the component.

You’ll follow this construction pattern as you build the rest of the app from here on out.

​

Searchable leads

You are now ready to build the core functionality of your app, starting with a searchable table that shows new contacts.

​

Scaffold the UI

You’ll start by dragging and dropping the components you need for this section of the app onto the canvas:

1. Drag and drop a Container component to the top left corner of the canvas. Resize it to a width of about 9 columns.
2. Drag and drop a Table component into the main section of the container you just created.
3. 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.
4. 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:

​

Connect your leads

To add the underlying HubSpot leads to your app, you’ll query your HubSpot data via the Sequin proxy. To step into this paradigm, you’ll first query for the data you need and then expand your query to add search and filtering.

1. Click the Code icon (i.e. </Frame>) in the left toolbar and create a new resource query. The resource query editor will open at the bottom of the canvas. For the resource, select Sequin Postgres resource you created earlier. Enter the SQL statement below:

   SELECT
      contact.id as "contactid",
      contact.createdate,
      contact.firstname || ' ' || contact.lastname as "fullname",
      contact.email,
      contact.jobtitle,
      contact.linkedin,
      contact.hs_lead_status as "status",
      company.id as "companyid",
      company."name" as "companyname",
      company.numberofemployees,
      company.industry,
      company.website
   FROM
      contact
   LEFT JOIN company
       on contact.associatedcompanyid::VARCHAR = company.id
   

2. 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 LinkedIn field you created earlier:

3. This query looks good for now, so click the Save & Run button and then name the query get_leads by clicking and editing the name of the query.

4. To connect the data from the get_leads query 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 source field select get_leads.

5. 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.

1. First, add search to your app by opening up your get_leads query in the bottom drawer panel and add the following WHERE clause:

   SELECT
      contact.id as "contactid",
      contact.createdate,
      contact.firstname || ' ' || contact.lastname as "fullname",
      contact.email,
      contact.jobtitle,
      contact.linkedin,
      contact.hs_lead_status as "status",
      company.id as "companyid",
      company."name" as "companyname",
      company.numberofemployees,
      company.industry,
      company.website
   FROM
      contact
   LEFT JOIN company
      on contact.associatedcompanyid::VARCHAR = company.id
   // highlight-new-start
   WHERE ({{ !textInput1.value}}
      or (contact.firstname ilike {{ '%' + textInput1.value + '%'}}
      or contact.lastname ilike {{ '%' + textInput1.value + '%'}}
      or contact.email ilike {{ '%' + textInput1.value + '%'}}
      or company.name ilike {{ '%' + textInput1.value + '%'}}));
   // highlight-new-end
   

   This WHERE clause 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 Postgres ilike to search across your contact’s names, emails, and companies. Remember, the double brackets indicate that you’re using Javascript in the query.

2. Next, add filtering to your app by adding one additional statement to your WHERE clause:

   SELECT
      contact.id as "contactid",
      contact.createdate,
      contact.firstname || ' ' || contact.lastname as "fullname",
      contact.email,
      contact.jobtitle,
      contact.linkedin,
      contact.hs_lead_status as "status",
      company.id as "companyid",
      company."name" as "companyname",
      company.numberofemployees,
      company.industry,
      company.website
   FROM
      contact
   LEFT JOIN company
      on contact.associatedcompanyid::VARCHAR = company.id
   WHERE ({{ !textInput1.value}}
      or (contact.firstname ilike {{ '%' + textInput1.value + '%'}}
      or contact.lastname ilike {{ '%' + textInput1.value + '%'}}
      or contact.email ilike {{ '%' + textInput1.value + '%'}}
      or company.name ilike {{ '%' + textInput1.value + '%'}}));
      // highlight-new-line
      and  ({{ select1.value.length === 0 }} or contact.hs_lead_status = any({{select1.value}}));
   

   Like the prior WHERE statement, this one also checks to see if there are any filters in the multiselect input. If there are no values (i.e. the array is empty) then nothing happens. If there are filter values in the multiselect, then the query is filtered to just the leads with those specific statuses.

3. 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 multiselect isn’t configured yet. To fix this, open the right panel and select your multiselect component 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:

​

Polish the table

As a last step, clean up your table and interface to make it easy to use.

1. Select the table in the canvas and open the right panel.

2. 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, and companyid columns from the table by clicking the eye icon in the inspector.

3. 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_date column so the Column title is now Sign up, and the Column type is Date Time:

4. Finally, make the status column standout. It’s the key field your sales team will be working with the most. To make it pop, select the status column, change the Column title to Status, change the Column type to Tag. For Color enter the following:

   {
     {
       item == "New"
         ? "purple"
         : "" || item == "Contacted"
         ? "RoyalBlue"
         : "" || item == "Qualified"
         ? "green"
         : "" || item == "Unqualified"
         ? "red"
         : "";
     }
   }
   

   This will make the color of each status tag match the colors you used in the multiselect filter. Nice!

You 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.

​

Enrich leads

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.

​

Scaffold the UI

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:

1. 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.

2. 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:

3. Now, you’ll add the editable inputs starting with Lead Status. Since you’ve already configured the Lead Status multiselect component 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 simple select component by clicking the three dots in the top right corner of the inspector, selecting Switch component, and picking the Select component:

4. 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:

​

Connect the data

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 in your Sequin database and HubSpot simultaneously.

​

Populate the fields

1. Select the Avatar component. In the inspector, set the Label field to {{table1.selectedRow.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.companyname}}. Then, to make the avatar look nice, set the Fallback text field to {{table1.selectedRow.companyname}}.

2. Next, select the Lead Status component and set the Default Value to {{table1.selectedRow.status}}. Repeat this process for the remaining Text input and Number input fields:

   Field	Default Value
   Position	{{table1.selectedRow.jobtitle}}
   LinkedIn	{{table1.selectedRow.linkedin}}
   Company	{{table1.selectedRow.companyname}}
   Employees	{{table1.selectedRow.numberofemployees}}

​

Configure the quick link buttons

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.

1. 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:

   {{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.fullname}}

   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!

2. Repeat the steps above to configure the remaining buttons:

   Button Text	URL
   Company	{{"https://www.linkedin.com/search/results/companies/?keywords=" + table1.selectedRow.companyname}}
   Crunchbase	{{"https://www.crunchbase.com/textsearch?q=" + table1.selectedRow.companyname}}
   Website	{{table1.selectedRow.website}}

At this point, your app will look something like this:

​

Connect the form

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 using the Sequin HubSpot Proxy.

First, create a new Retool resource for the Sequin HubSpot Proxy:

1. Open the query editor by clicking the Code button (i.e. </Frame>) in the left rail and clicking the + button. In bottom panel select your Sequin Postgres resource.
2. Now, you’ll create the udpate query. For simplicity, use the GUI mode and configure the query as follows:
   • Table: contact.
   • Action type: Update an existing record.
   • Filter by: id = {{table1.selectedRow.contactid}}.
   • Changeset:
     • hs_lead_status: {{multiselect2.value}}
     • jobtitle: {{textInput2.value}}
     • linkedin: {{textInput3.value}}
3. 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.

1. Duplicate the Sequin HubSpot Proxy query you just created, but this time configure the query to update the company object:
   • Table: company.
   • Action type: Update an existing record.
   • Filter by: id = {{table1.selectedRow.companyid}}.
   • Changeset:
     • name: {{textInput3.value}}
     • numberofemployees: {{numberInput1.value}}
2. When you’re 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:

1. Open the edit_contact query, and in the Event handlers section, click to + Add an on success event handler that will trigger the edit_company query. Save this change.
2. Now, open the edit_company query. Again, go to the Event handlers section, click to + Add an on success event handler that will trigger the get_leads query.
3. 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 to edit_contact.

When you’re done, you’ll be able to easily edit and update leads:

​

Polish

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:

1. Add icons to the buttons and fields by selecting the button, opening the inspector, clicking + on Add-ons and setting Prefix icons.
2. Change the colors of the buttons. In the inspector, make the save button green and help the user navigate by making styling the buttons.
3. Drag and drop divider components to help space our the information.

​

Bulk actions

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:

1. 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 three dots … next to the Status column and select Editable.

   

2. Now the the Status field can be edited in the table, you need to define the status options (just as you did before in with the filter and form). To do so, click on the status column in the inspector. In the Add-on section, click Option list. Then manually define the four options: New, Contacted, Qualified, and Unqualified:

   

3. 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.changesetArray.map(l => ({"id":l.contactid,"hs_lead_status":l.status}))}}
   • Event handler: Success - Trigger get_leads

   This query will take in each contactid from the array of objects in table1.changesetArray, find the records that match the id in your HubSpot Database, and then UPDATE the hs_lead_status field for those records.

   Click to Save your query and name it bulk_edit_lead_status in the query list on the left.

   

4. As a last step, connect your bulk_edit_lead_status query to your table. Select the table in the canvas, open the inspector in the right panel, in the Add-on section click to add + an event handler that will trigger the bulk_edit_lead_status query when the user clicks Save 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!

​

Customize

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!)[https://docs.sequin.io/sync-process/self-hosted] Add more fields to let your sales team capture notes - or even trigger emails. This is just the beginning.