MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
- Hello good folks and merry Xmas and happy Hannukah!
I want to design a highly automated prospect-scraping system that streamlines prospect’s email gathering. Eventually, I aspire to create an automated prospecting and lead-scoring system that will later be integrated with Hubspot for more advanced analytics. I’m hoping someone here might be able to help me with either of these tasks. On this post I'll be addressing the scraping part, and will be leaving the scoring part for later. Any help would be extremely appreciated as I know neither VBA nor Python.
Automation of Email Scraping and Scoring
I recently started using hunter.io manually for scrapping email address and I'm looking to automate the process using Excel. I have a paid subscription with them so I can use their API if only I knew how to. The software pack I am currently using for this task is:
- Microsoft Excel 365
I envisage the process as follows: (and open to suggestions)
- I manaully feed the prospect's full name and their company's URL respectively into their appropriate columns.
- A calling to hunter.io API is made using the prospect's name and account URL.
- The result of this automated attempt is broken down and put into different cells, as follows:
a. Email address
b. Email Verification Score
c. Email Verification Methods (server-validation, internal sources, external sources or a combination thereof)
d. Is there a company-internal source (i.e. prospect's email found on company domain). In the following example there are 5 internal verification sources (but I need to know only yes/no for internal):
e. No. of external verification sources. In the following example there is 1 external verification source: https://imgur.com/Tq62ZzY
f. Date of most recent verification source. In the above example the date is july 28, 2020.
4. I add the prospect's full name into one cell and the company's domain into another cell. The macro would then automate filling all the fields a-f.
5. >strong class="_12FoOEddL7j_RgMQN0SNeU" is a professional, non-generic email address which hunter.io can both >strong class="_12FoOEddL7j_RgMQN0SNeU">validete by and find plenty of external sources for. Example: https://imgur.com/se6vrRi.
6. Handling lack of information: If hunter.io can’t find any emails on the domain, or can find only generic address (e.g. [email protected]..., for example: https://imgur.com/7c1pyXa), then I’d like it to attempt to find a verified version of the prospect’s email address by trial and error- i.e. trying all to common email patterns, (e.g. [first].[last]@domain.com, [f][last]@domain.com) until something comes up. Once something does come up, then to put it the result in the appropriate 'email' cell.
7. Exceptions: If the email address is only validated without any supporting sources and the only possible address that the search yielded was a single first/last name address e.g. [email protected] or [email protected], I’d like excel to generate some form of notification (doesn't have to be an actual pop-up notification, can be just color)indicating to me that an additional search on Linkedin is warranted to check if there is more than one John or one smith in the company .
6. Email Quality Scoring: The quality of the email address would be give a score, which would be into a different column, preferably on another spreadsheet on the same workbook – based on the level of verification with a maximum score of 10/10. I will leave this subject to my next post.
Here's a link to the template I prepared: Lead Generation Template - for email scrapping with hunter.io
and you can also find a screenshot of the template below:
Screenshot of lead-gen template
Thanks in advance to all who are ever so inclined to help a stranger in need!