Note: This post represents my personal views and not necessarily those of my employer.
Someone recently forwarded me a question posted about a month ago on a LinkedIn sourcing group asking if you could efficiently transfer your connections’ birthdays (on LinkedIn, Facebook friends, Outlook, etc.) to your CRM (https://www.linkedin.com/grp/post/1176637-6016974478737682434). The group moderator responded “I don’t think it’s possible” and no other commenter really had a solution, either.
However, with a little Excel VBA macro and (additionally for Facebook) a bit of web scraping knowledge, you actually can grab your contacts’ birthdays from these two big social networks! There may be other ways to achieve this, but given how central Microsoft Excel is to recruiting and how little the average sourcer uses the time-saving capabilities that macros allow (analogous to the productivity boost one gets from bookmarklets and extensions in your web browser), it seemed worthwhile to share — even if the social networks eventually change their web page layout and these no longer work as-is.
One comment mentioned in response to the original question is the key to start us towards the solution: To see your connection’s birthdays, you can click Connections → Keep in Touch (https://www.linkedin.com/contacts/?filter=recent&trk=nav_responsive_sub_nav_network#?filter=recent&trk=nav_responsive_sub_nav_network) which does reveal connections with a birthday today, mixed in with people making job changes.
If you click “See more people to contact” repeatedly at the bottom of your results (a lot of times, as LinkedIn only displays 3 new results per click), you eventually get to people with people with recently-past birthdays. Once you’ve clicked enough times to reveal enough data to satisfy yourself, here’s what to do (or you can simply do the following once per day to process today’s birthdays without clicking):
- On your LI connections page, hold and drag your mouse down to highlight all the people you’ve exposed and select Edit → Copy (or hit Ctrl+c on Windows PCs).
- Open the attached Excel file that contains the macros. As with all .xlsb or .xlsm files, you may get a security warning, just hit OK/enable (feel free to copy the macros to your personal macros file if you know how to do that).
- Back in your Excel macros file, click your mouse in cell A1 (preferably in a new blank worksheet tab so you don’t overwrite anything), and select Edit → Paste Special → Text so you only paste the raw text (not regular paste which will grab HTML code and preserve original formatting). This should generate text in lots of cells down the first column of the Excel file.
- Now for the magic: To run the macro, In Excel’s top menu, select View → Macros → View Macros. Click on LIbirthdays once to highlight it, then click the Run button to its right. It will prompt you to type the column letter (A) where you pasted the data (or type a; it’s not case-sensitive), the last row number with data (ok to estimate/overshoot it – e.g., type 200), and the column where you want the parsed data to appear (normally, you would type B). Then it takes about 15 seconds (ok, probably over a minute if you copied thousands of LI connections!) to generate all the names and birthdays, one set per row.
- Now sort the worksheet by the Person Name column (or Birthdate column) so all the people with birthdays are clustered together.
- Copy/paste these cells into a new file and save as .csv (comma-separated values) format so you can import them into your CRM!
It’s a lot faster to add the birthdays of all your Facebook friends for the upcoming 12 months to your database:
- Open the Excel file with the macros.
- Go to the birthday reminders list for your friends at https://www.facebook.com/events/birthdays
- Scroll down just after the “Upcoming Birthdays” where you start seeing Facebook headshot images. Hold and drag your mouse down to highlight all the upcoming month’s headshots (you can continue into the following month’s worth of birthdays, and maybe another couple of months, depending on how many FB friends you have – it seems to be able to process about 400 people at a time before it starts choking, so stop around there).
- Select Edit → Copy (or hit Ctrl+c on Windows PCs) to copy all those hyperlinked headshot images.
- Back in your Excel macros file, click your mouse in cell A1 (preferably in a new blank worksheet tab so you don’t overwrite anything), and hit Ctrl+v (the equivalent of normal Edit → Paste command that preserves original formatting). Be patient: this paste can take up to 30 seconds to complete, depending on how much you copied and how fast your computer’s processor is. When done, this generates small partial headshots down the first column of the Excel file.
- If you didn’t capture the entire year’s worth (i.e., all your friends), scroll down the Facebook page starting after where you left off, and repeat the previous steps, making sure to click your mouse in an Excel cell below the previous months’ headshots so it doesn’t overwrite what you pasted before.
- Now for the magic: To run the macro, In Excel’s top menu, select View → Macros → View Macros. Click on extractHyperlinksFromFBimages once to highlight it, then click the Run button to its right. It will prompt you to type the column letter (A) where you pasted the headshots (or type a; it’s not case-sensitive), then it takes about 15 seconds (ok, probably over a minute if you have thousands of Facebook friends!) to generate all the URLs. The macro also deduplicates the URLs and sorts them.
- Select/highlight just the cells containing URLs in your Excel file that the macro generated, and select Edit → Copy (or hit Ctrl+c on Windows PCs) and paste them in a plain text file using Notepad (or if you paste into Microsoft Word, make sure to do File → Save As → and in the “Save As Type” menu, select “Plain text”), then save it to your Desktop or other convenient location.
- Create a web scraper to grab the key data from a Facebook friend’s page, which can be done with almost any tool in this category (Import.io, Kimono Labs, Outwit Hub, etc.), using <title id=”pageTitle”> as startpoint and </title> as endpoint to grab the person’s name, and <span class=”fsm fwn fcg”>Born on (startpoint) and </span> (endpoint) to grab the birthdate (page URL should be grabbed automatically, plus you have it from previous 2 steps, anyway). For the sake of time/complexity, I will not explain how to create scrapers here but all these products have good tutorials (or see my earlier SourceCon video at https://www.sourcecon.com/an-outwit-hub-video-tutorial-with-glenngutmacher/ about how to scrape with Outwit Hub).
- Now that you have the scraper, import the list of URLs from step 8 and run the scraper on those. You should now have a .csv or Excel file with each row containing the person’s name, FB profile URL and birthdate, which can be imported into your CRM! Warning: review your scraper’s output file in Excel first: Curiously, you will notice a small percentage of your results do not contain anything in the birthdate column. This is not an error — if you go to the person’s actual profile page, you will see the “born on” line does not appear (the person has changed their settings to hide this data from friends on their profile page), but it is not hidden on the birthday reminders page! Go back to the URL from step 1 and mouseover the headshot of any person who has no birthdate in the output file: The black and white bubble popup will appear that displays the birthdate. Just add those dates manually to your file. Now you’re ready to import the list into your CRM!
P.S. Besides your CRM, you can also add the Birthday Calendar that Facebook provides to your other calendars. 1) On the lower right of the birthdays page after “You can add your events to Microsoft Outlook, Google Calendar or Apple Calendar. Once you add them, they’ll stay updated”, copy the Birthdays link (a URL that begins webcal://www.facebook.com/ical/b.php). 2) Importing to Google Calendar: Go to your calendar (https://www.google.com/calendar/render?pli=1#main_7) and click the small down-pointing triangle to the right of Other Calendars in the left-column menu, select “Add by URL”, then enter the FB webcal link from step 1. 3) Importing to Outlook Calendar: Go to your calendar view, then under the Home tab, select Open Calendar → From Internet. Then enter the FB webcal link from step 1. 4) Importing to Apple Calendar: see https://support.apple.com/en-us/HT201747 This webcal method would also seem like the easy way to get the birthday info into your recruiting CRM. However, I have not found a way to export a webcal file to a .csv that has all the key fields. If you have, please reply and let us know!
Download this file…