My current employer is an executive search firm recruiting candidates for the real estate development and construction industries. I lead their internet marketing efforts; I also am in charge of market research. If I can deliver competitive industry intelligence to the recruiters in our office, they can utilize it to match executive job candidates with employers who need them but can´t find them on their own.
The Challenge
There are a very large number of companies worthy of being tracked by my company. ENR (Engineering News & Record) creates different authoritative topic-specific lists containing names of the biggest commercial construction firms. Builder Online lists the largest residential homebuilding firms. Between these two sources, I´ve identified about 1,700 companies where it would be valuable to track each & every news story about them. Fortunately, on any given day, only a small percentage of these companies will make noteworthy news…however, in order to make sure I catch all the valuable information, I need to monitor all of them.
Most logically, I would have to implement some sort of RSS solution in order to track 1,700 companies. Manual entry of 1,700 feeds into a feedreader isn´t an option; I don´t have any programming skills; and I don´t have a budget to find someone to program a solution.
So what did I do?
The Solution: Part 1
When I examined a typical Google News RSS URL, I can determine that the only variable information within each lengthy URL is the term being searched:
From this information, I can quickly and simply reproduce a Google News RSS URL for each item in my list quickly and easily in Excel.
I then do the following (which is more clearly illustrated in the attached spreadsheet that I strongly encourage you to download by clicking this link):
1) Once I open Excel, I place my entire list of relevant companies into Column B.
2) In Column A, I place the first portion of the Google RSS URL (from http:// up to the variable portion).
3) In Column C, I place the final portion of the URL (&output=rss).
4) I also know from experience that in order to make my feed as relevant as possible, I want to use exact match wherever I can. “%22” is the symbol used for “quote”. I append “%22” as the last characters in Column A and the first characters in Column C.
5) I make sure that the code in Columns A & C is copied onto every row containing a relevant company in Column B.
I also need to perform the following global steps in Column B:
1) Replace “space” and “&” with “+”
2) Replace “++” with “+”
3) Delete all instances of “apostrophe”.
Here comes the fun part:
1) I widen the columns so that there is plenty of white space to the right of the text in each column.
2) I save the document as a Formatted Text Space Delimited (.prn) document.
3) I reopen the document, choose “Delimited”, click “Next”, and then click “Finish”.
4) I then globally replace “space” with nothing, generating the list of long RSS URL´s.
The Solution: Part 2
(…catches breath…)
In each of the feed readers I´ve used, I´ve noticed that one can bulk import feeds into them if they are in OPML, something I admit to not understanding too well. I realized that if I could convert my URL´s into OPML, I can then get my entire list of company news RSS feeds into a feedreader. I found Feedshow Goodies through Google, dropped my URL´s into the form located on this page, and clicked “Create OPML” (note that it will process up to about 200 URL´s at once). I then saved each OPML file to my hard drive.
I then made the mistake of thinking that my trusty Feedreader 3.07 would cleanly accept 1,700 new feeds. The first OPML import of 200 feeds ate up all my internet bandwidth and slowed my computer to a crawl. Google Reader was the only other feedreader that I´ve used, so I signed into it and uploaded 9 large OPML files.
It worked. Google Reader allowed me to seamlessly (if somewhat slowly) import 1,700 feeds and read them all together, allowing me to get a real time news update of all the commercial & residential construction companies that I desired to track.
My Public Feed (Note I configured it somewhat differently than in the sample above).
Suggestions
1) Even though the Google Reader is doing all the “heavy lifting”, having it open tracking 1,700 feeds will hog your memory, especially in IE. I found that opening Google Reader in Firefox or Opera instead cut significantly the amount of memory used.
2) The one drawback to this process is that the “title” of each feed is the long ugly Google RSS URL instead of something more descriptive.
3) If you have a lot of stories in your Google Reader, go to Preferences and check “In Expanded View, Mark Items as Read as You Scroll Past Them”. This feature is a tremendous help in wading through voluminous amounts of data.
4) I found that creating Google News feeds for all news created “in the past week” gave me the best results. A Google News feed for all company news for each company led to too much information and news feed covering just the past day´s news seemed to miss stories. In this example, I used News RSS URL´s for all company news because they are the most user-friendly. To use this mashup with “time-restricted” Google News, you will need to click on “Advanced News Search”, do a sample search with a defined time period, generate the News RSS URL and copy the first portion of it into Column A of your spreadsheet before following the proscribed process.
I now can track news from all 1,700 companies in my Google Reader. Kudos to those good folks at the ´Plex.
