How to keep HubSpot leads list updated & without duplicates

One of the key factors to a successful campaign is reaching out to the right people.

But how do you ensure you don't import and contact the same prospects multiple times and ruin your chances of getting replies?

In this article, you'll find a hack to quickly check the status of your leads in HubSpot before import, so you don't lose time over prospecting.

Step #1 Ensure your leads' status is up to date

The first step to keeping your leads list clean is ensuring that the "lead status" property in HubSpot reflects your prospects' lifecycle.

E.g., when a deal is won, your lead status should be "Customer", or when your lead has been contacted, their status should be "Contacted".

Here's how you can automate lead status movements:

1. Go to HubSpot's workflow automation
2. Create a new workflow for each status
   → for "Customer", use this workflow:

If you want to save hours manually migrating leads data between your CRM and sales engagement tool, you can integrate your HubSpot CRM into lemlist.

This integration will allow lemlist to enrich your HubSpot database with accurate leads info, so you'll have their 100% synced overview.

Depending on your setup, you can add and update leads in HubSpot due to their activity in the lemlist campaign (such as Replied, Clicked, Marked as interested, etc.)

Following this guide, you can set up your lemlist x HubSpot integration to always know your leads' status and how to approach them for the best results!

Step #2 Check HubSpot status via Google Sheets

Once you have a Google sheets list of freshly scraped leads, here's what you should do before their import to HubSpot or lemlist:

1. Go to Extensions → Apps Script

2. Remove the code and copy-paste the following instead
 → copy and paste this code:

HUBSPOT_API_KEY = "YOUR_API_KEY"

/**
* Check if the contact is in Hubspot.
*
* @param {string} email.
* @return HS_lead_Status
* @customfunction
*/

function findHubspotContact(email){  
‍
result = hubspotLookUp(email)  
‍
while (result.includes("RATE_LIMITS")){
‍
    var randomNumber = Math.random();
‍
  // convert 5 seconds to milliseconds
  var maxSleep = 1000;

  // multiply the random number by the max sleep time
 var sleepTime = randomNumber * maxSleep;

 // use the Utilities.sleep() method to make the script wait
  Utilities.sleep(sleepTime);

   result = hubspotLookUp(email)  }    return result    }function hubspotLookUp(email) {
 var apiKey = HUBSPOT_API_KEY;
 var url = "<https://api.hubapi.com/crm/v3/objects/contacts/search>";

 var options = {
   "muteHttpExceptions": true,
   "method": "post",
   "headers": {
     "Authorization": "Bearer " +apiKey,
     "Content-Type": "application/json",
     "Accept": "application/json"
   },
   "payload": JSON.stringify({
   "properties": [ "email", "hs_lead_status","notes_last_contacted" ],
   "filterGroups":[
     {
       "filters":[
         {
           "propertyName": "email",
           "operator": "EQ",
           "value": email
         }
       ]
     }
   ]
   })
 };

 var response = UrlFetchApp.fetch(url,options);
 var responseCode = response.getResponseCode();
 var responseBody = response.getContentText();

// Logger.log(responseBody)
if (responseCode === 200) {
if (JSON.parse(responseBody).results[0]){

  // display the results in 2 columns
var results = new Array(1);let info = new Array(3);
info[0]=JSON.parse(responseBody).results[0].properties.hs_lead_status
if (JSON.parse(responseBody).results[0].properties.notes_last_contacted){
let lastContactedDate= Date.parse(JSON.parse(responseBody).results[0].properties.notes_last_contacted)
info[1]= new Date(lastContactedDate)}
results[0]=info

return results} else {return "Not in Hubspot"}
 } else {
   Logger.log(Utilities.formatString("Request failed forgot %d: %s", responseCode, responseBody));
   return Utilities.formatString("Request failed forgot %d: %s", responseCode, responseBody);
 }
}

‍

3. Get your HubSpot API Key:

• Go to HubSpot's Settings → Integrations → Private Apps
• Click on Create a Private App

• Give a name and go to Scopes
• Authorize read access to "crm.objects.contacts "

• Click on Create app
• Choose Show token and copy it

4. Paste your HubSpot API key into the Google Apps Script project
 → your setup should look like this:

5. Save your project and go back to your sheet

6. Use formula =findHubspotContact({email}) to see your new leads' exact status in HubSpot CRM

Step #3 Remove low quality contacts

You could have contacts that do nothing but damage your sender reputation. 

Any contacts based on guessed email addresses or from bought email lists are affecting your open rates. Reaching out to these contacts may be a direct violation or privacy laws within your region. Also, chances these contacts will engage with your emails are pretty slim. This tells your email service provider that you're sending spam, which may lead to your account being blacklisted. 

You can ensure you're contacting the correct email addresses with lemlist's free email checker. 

Step #4 Remove contacts that never opened your emails

Guessed email addresses aren't the only ones damaging your sender reputation.
Even valid email addresses can decrease your open rates if they never engage with your emails. Here's how you can list all contacts that didn't open any of your sent emails:
1. Select CRM > Lists from left-hand menu.

2. Click on Create list in the upper right corner.

‍
3. Name your list and add a description.
4. Select Add filter.
5. From Filters, click Contact properties, and select Last marketing email open date.

6. To edit the filter, open the drop-down menu below Last marketing email open date and select is unknown.

7. Below the created filter, select Add filter.

8. Set Filtering on to Activity (Associated Object).
9. Set the filter category to Number of email clicks.

10. To avoid removing contacts that you just added to your HubSpot lists, select is less than, and set a relevant limit.

11. Click Save in the top-right corner.

Conclusion

Keeping your leads list clean saves you hours creating campaigns for existing customers or those not ready to buy from you.

Use this Google Sheet hack and lemlist to keep in the loop with your prospects' lifecycle so you approach them at the right time and boost your sales chances!

If you want to grow your business with more advice like this, join the lemlist family, where 11k+ successful people share free outreach tips daily. 🚀