X-raying LinkedIn with Google Sheets
Inspired by the presentation of Nicolas Darcis on SoSu 2022, and since “Boolean died” and results are limited to 300 per query on Google, I’d like to show you some tricks for X-raying LinkedIn.
The market I work in is relatively small, and I like to make sure I’m thorough while I’m also quick. So I created a Google Sheet template where I write & store multiple Google search query strings without having to manually write them each time when I get a new idea or keyword. I’m keeping my search tidy, easily trackable, and can remove profiles viewed already within a minute or two.
Before we get started, make sure you’re Google search is set to 100 results per page.
So here are the steps — do check out the 📽️ linked videos 📽️ for details.
1st search : “intitle:data engineer”
Open a Google Sheets document and enlist in the first 3 columns all keywords you’d like to combine, constants and variables.
I use constants like “intitle:” for the 1st search, then “inanchor:” for the 2nd search, and so on. Otherwise, the semantic search engine of Google will feed me too many irrelevant profiles, and not worth the effort. I rather play around with more variables to find new profiles, such as specific schools, tech stack, excellence. The variables list, as well as the constant, keeps growing as I progress and find new ideas along the search.
In the 4th column, copy-paste the search query URL up until the “+” sign to include “site:linkedin.com”
In case of profiles based on country, like Israel for example “site:il.linkedin.com”: https://www.google.com/search?q=site%3Ail.linkedin.com%2Fin
Now use the formula =CONCATENATE to link all the parts of a Google search query into one single cell (D2).
=CONCATENATE(“https://www.google.com/search?q=site%3Alinkedin.com%2Fin", “ “, $A$2, “ “, $B$2, “ “, C2)
Notice the space “ “ between the commas; this is needed, so the words pulled in will also have a space between them when searching in Google.
Notice the dollar signs “$A$2” in columns A and B. This is to fix the cell from which data is pulled when dragging the formula down. And feel free to play around with this.
Here is how:
Open all the search URLs generated with Multiple URL Opener.
Data Scraping
You should now have 12 Chrome tabs with the 12 opened search queries (or however you’d set up previously).
Use Instant Data Scraper to copy & paste all the LinkedIn profile URLs into a new Sheet. Instead of downloading 12 or more CSVs and Import & Appending the file to the Google Sheets, frankly, I prefer simply CTRL+A → Copy &Paste, like this:
Or, check out Apify’s Google Search Results Scraper, my absolute favourite! Scrapes several tens of Google search queries into one single CSV within minutes ❤
Data clean-up
Here you can start the clean-up already by filtering the job title, current company, location etc. and simply deleting the irrelevant ones.
Then remove the duplicate profiles.
Select the entire column → Data tab → Remove duplicates, or use the =Unique function.
Now, open the Linkedin profile URLs (~20 at a time) with Multiple URL Opener.
2nd search : “inanchor:data engineer”
Use here same constant (“intitle:”) with different keywords. Or different constant (like “inanchor:” or intitle:data developer”) and add new variables to have as many keywords as possible. Up to you 😉
Then repeat the steps of the 1st search: scrape, copy & paste and remove duplicates.
But from now on, you’ll need to start checking which profiles were viewed already in your previous search and which not.
You can do this with a simple =Vlookup function.
- A2 : new profile from the current search (value to look for)
- C:D : list of profiles already viewed in previous searches
- 2 : ‘viewed already’ I add myself, this is the value to retrieve, IF found!
- False — to look for an exact match, not approximate!
Once you drag down this formula to the bottom of the list, you’ll find the “N/A” values. These are the profiles you haven’t seen before, as they are not available in the 1st search column.
Filter on “N/A” and start reviewing them with Multiple URL Opener.
Once you’re done reviewing all N/A, copy these profiles to the viewed profiles column (“C” or a new sheet, as you wish). Let this be your index for all profiles viewed, so you’ll not have to see the same profiles again and again.
And then start with your next search combo and repeat.
Hint:
Linkedin profile URLs can vary, for example, if you download a list from your ATS. Just fix it with Find & Replace, and make sure it stays consistent:
My results
The total of 12 X-ray search queries resulted in 314 unique profiles, whereas, mind you, in theory, this should be 12 * 300 = 3600. I ended up contacting 107 (due to strict client requirements and blacklisted companies), had a cup of coffee in the meantime & time to think of what I want to do next. All, while Multiple URL Opener did the heavy lifting.
My 2nd search was for “data engineer” without “intitle:”, and although had 656 unique profiles, I ended up contacting only 30 because of relevance and “already viewed” in my 1st search.
In my 3rd search I used “initle:data engineer” again, but with different variables. After removing the profiles viewed already in my previous searches, I had 204 unique profiles and contacted 56 new prospects.
Some extras:
- Extensions that I can’t live without: Visited, Multi Highlight
- Apify‘s actor ❤ Google Search Results Scraper ❤ provides one single CSV in minutes for no matter how many search queries; free-of-charge up to 5 USD/4 GB monhtly, and if you leave a review, might get extra.
- Sheetgo bulk imports & append CSVs to Google Sheet. The free version is very limited, but there are plenty out there or whoever knows coding, can learn the Queries.
Hope you’ll find value in this, and if you have any questions or tips on how to better this, give me a shout, would love to hear from you!
Until next time, here is my blank template, feel free to copy to your drive & source away! 🍀
