How we Built a Press Target List of 300+ Qualified and Interested Journos, in 10 Minutes
Note: the Google Sheets Add-on is now deprecated, but you can replicate most of its functionality with some code you can copy and paste from here.
Are you finding it hard to identify press targets for your latest release or product update?
Targeting relevant journalists that might be interested in covering your company on their blog or news outlet can be pretty difficult. Where do you even start? Buy a list? Manually trawl through tech blogs and news sites? Tracking down journalists is one thing, tracking down journalists that are interested in what you do is a different story altogether.
Faced with this same problem we thought long and hard about how we should source our press targets and all the time it was staring us in the face.
Who are they? Where are they?
Think about it! What do journalists who you want to write about your company, already write about??Yep. Your competitors!Most tech companies with an online presence have a press, or in the news section, on their website. It’s usually a page on their site where they collate links to press coverage on their company. A gold mine for relevant journalists if you will.
We’re going to show you how you can, in a matter of minutes, trawl your competitors sites for press coverage and extract the names of the journalists. We’ll create a list of relevant journos based on who has been writing about our competitors. The idea is pretty simple list all of our competitors, gather their press coverage, extract the authors automatically from those links and start hunting for emails.
Here’s what you’ll need:
- A Google spreadsheet
- An AYLIEN Text Analysis add-on
- A few competitors with press pages on their sites
Step 1. Gather the links
To get the links into a spreadsheet we’re going to use a pretty standard XML Function:
This will pull all of the links on that specific page into our spreadsheet. Some of them, you will want to keep and some you can forget about.
Grab the ones that are relevant, all the press coverage ones, and paste them into your second sheet. Once we have all the links gathered in one sheet we’re gonna start analyzing the links and extracting what we’re after, in this case, authors names.
Step 2. Analyze the links
Analyzing the links is really easy. You’ll need to get a free copy of the AYLIEN Text Analysis add-on from the add-on store, if you haven’t already done so. If you’re new to using the add-on this tutorial guide will help.
Once you’ve got that running, you’ll see it populate on the right-hand side of your spreadsheet, you can start analyzing the links and extracting author names.
Use the function:
This will extract the author’s name from the web page. The author function is one of many AYLIEN specific functions available with the add-on.
Drag the function down as you would with any Google Sheets function to populate authors for the rest of the URLs.
There you have it, the names of a couple of hundred journalists, pulled from one page and laid out nicely for you in a spreadsheet.
Step 3. Find their emails (the tricky part)
The hardest and most laborious part of this hack is a bit of a pain in the behind! Getting the email addresses for these PR targets. For a while, we used Stack Lead (acquired by Linkedin) and more recently we tried Hey Press, but sometimes you just have to put in the hard yards and jump on google.
A nice little trick we use though is by speeding up the search process using the hyperlink function
=HYPERLINK("http://google.com/search?q=" & author_cell & " " & REGEXEXTRACT(url_cell, "^https?://([^/?#]+)(?:[/?#]|$)") & " email")
This brings you straight to a new tab in your browser, that searches for your targets name, the domain and email in the one search. You’ll usually find an email address for them nestled somewhere in those search results. Otherwise, you’ll find a Twitter handle or Linkedin account which is also a good place to start.