How to keep HubSpot leads list updated & without duplicates

February 20, 2023
X
min.

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
Untitled
  • 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

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

What you should look at next
AI sales
7 AI email assistants to boost your sales efficiency
In this article, get a breakdown of the 7 best AI email assistants on the market that will help you simplify your sales process.
Mihaela Cicvaric
August 9, 2023
AI sales
Cold outreach guide for beginners - how to get started with AI
Your complete guide to using AI in cold outreach, for beginners. Learn how sales reps use AI to meet their goals and level up their strategies.
Julia Hamilton
July 25, 2023
AI sales
3 steps to generate personalized outreach sequence with AI
In this article, we'll show you 3 simple steps to create an outreach sequence that generates replies up to 2 times faster than your current approach.
Mihaela Cicvaric
July 3, 2023
AI sales
Top 20+ resources to learn about Artificial Intelligence
There’s so much mention of AI, but where does one actually start? To save you some time, we’re sharing the list of the top 23 resources that helped us revolutionize our business in this article.
Mihaela Cicvaric
June 28, 2023
AI sales
3 steps to create detailed ICPs and buyer personas with AI
Master the art of crafting buyer personas and ICPs with AI. Discover 3 proven steps to understand your audience's needs and boost your outreach success.
Julia Hamilton
June 27, 2023
AI sales
How to crush your sales goals with generative AI (+ top AI tools)
How to drive sales growth with generative AI and the top AI sales tools. Automate tasks, personalize campaigns, and optimize performance for success.
Julia Hamilton
June 25, 2023

Book your
first meetings
this week!

Accelerate your business growth and start generating revenue with lemlist
Try for free
No card required.
Try for free
No card required.
YES