Building a news clip database
Tracking the News
Since I moved to my current role in Bemidji State’s Office of Communications and Marketing in 2007, I’ve used a static list in InDesign to keep track of the news releases I produce each fiscal year. It looked a lot like this one, from FY2013, and it was basically the same for six straight years with some cosmetic changes every now and again. It had a simple purpose: keep an ordered list of the releases I distributed each year and track the code number assigned to each release depending on the range of distribution for each story. I moved it around, from InDesign to Pages and back to InDesign, but it was basically just a text list.
For this year I wanted to do something different; for a variety of reasons, I decided to move the list to a spreadsheet in Google Docs. It looks like this. Just a few releases into the fiscal year and it’s working perfectly fine.
In the past, I had been using Filemaker Bento to keep an archive of stories mentioning Bemidji State found in various places online. It started out as a way to keep track of stories sourced from Google Alerts, Yahoo! News alerts, Bing news alerts, etc. Eventually I added the stories we were pulling in from our clip service at BurellesLuce. I kept track of a variety of information about a particular story — where I found it, how I found it, whether I could identify it as coming from a story I had released, a number of things. Each story in the database had an entry that looked like this. This database fell by the wayside during the year that I served as interim director of the office, but the idea of this as a tool to help keep track of news mentions was solid.
This year our office is putting an emphasis on measurement; measurement of our traditional media efforts and of our social media efforts. I’m using tools like Sprout Social and Crowdbooster to track social; for traditional media, I’m using tracking functionality in Burrelles, and this year we’ve subscribed to Meltwater, which has some media tracking tools as well.
However, there are two questions that were going to be extremely difficult to answer related to print media, even with all of the tracking tools we have access to right now. Those questions:
1) Of the news releases distributed in X period of time, which one generated the highest number of stories in the media?
2) How many stories did a particular news release generate last year?
The basic data to answer those questions exists in the relationships between two existing tools – the news release inventory lists I’ve been maintaining by hand for years, and the measurement services that track our media mentions. However, nothing that I’ve done connects those two tools in any way, leaving answers to questions like these two, and others like them, just out of reach.
However, moving the release inventory from a static text list to Google Docs sparked a thought — this new spreadsheet could be moved to a database, the Bento database to track news clips could be resurrected, and I would have the data sources I would need to find the answer to those two key traditional media measurement questions.
None of the tools I was using would do the trick. Bento is a single-table database with no relationship-building capabilities, so rebuilding the clip database there was pointless. Moving the clip database to Google Docs would give me parallel spreadsheets and allow me to save all the relevant data, but I wouldn’t be able to get the information I wanted about the relationships between data in those tables. I needed an actual database tool, and I chose Filemaker Pro.
I picked Filemaker over something like PHP because of Filemaker’s ability to easily deploy to iOS devices; I figured that might come in handy if this became A Thing.
So I had a problem to solve; I had picked a tool to try and use to solve it. Off I went.
Building the database
My first task was to try and recreate the clip archive I had constructed in Bento in Filemaker. This was relatively straight-forward and not terribly difficult; the final result looked like this, which looks basically the same as the Bento version I built a few years ago. All of the information is there, save for the “related art” data box to save PDF versions of the stories; that no longer seemed necessary. Once this was built, I populated the database with about a dozen records as a starting point.
I then toyed around with deploying that database table to my iPad, just to see how that worked. It required that I set up permission levels to grant access to my iPad, and in the process of setting up this guest access I apparently inadvertently assigned an overall administrative password to the database. Which, of course, I had no chance of remembering later, which means I locked myself out of my first draft.
So I rebuilt it from scratch, tweaking a few things along the way, until I got it up and running again and repopulated with about two dozen records this time.
Once that was done, I built a second database table to hold the information I’m currently storing in Google Docs as a release inventory; that screen looks like this.
The release code that has been part of the inventories I’ve been keeping plays an important role here. It quickly occurred to me that this unique code, which had been used to segregate and sort stories by category, also could function as a unique database key. The structure of the key is straight-forward – 2014-B-X-015 is the 15th story released for Bemidji State in Fiscal Year 2014 as an extended local story (a geographic category that carves Minnesota up into zones that stories can be sent to; “extended local” draws a line through St. Cloud and includes basically everything north of that line). That structure also creates a unique identifier; there can only ever be one 2014-B-X-015; thus its suitability as a database key.
I created a connection between the media clip table and the story inventory table within the database, using that release code as a key identifier. Then, I used the portal functionality in Filemaker to pull that release code information into an individual story in the clip database; this would allow me to identify a clip as having come from a BSU news story, as I had always done in Bento, but then also indicate exactly which release in the news release inventory had been the clip’s original source. That connection looks like this in the story database; it shows whether the story was sourced from a release that came from BSU, then has a field where I can display the release code of the story it came from.This field is connected back to the story inventory table, which also dynamically displays a Bitly link to the release’s home BSU News.
The fun part came when I built a second tab for the news release inventory data table and connected that back to the clip database; it scans the connections that were made in the previous step and returns a list of every clip that has been tagged as having been sourced from that news release. So for 2014-B-X-002, the second tab lists every story in the clip database that says it came from a BSU release and also came from story 2014-B-X-002. That display looks like this.
Honestly, this is about the most-basic multi-table database you can build in Filemaker; the two individual tables are just straight-up data tables that could easily be reconstructed in Excel or some other spreadsheet, and there’s only a single point of connection between the two tables, identifying the release code for the source stories in any news clip identified to have originated from one of our news releases. Even the “magic” piece that returned the list of every story that had been tagged with a certain release code used a function that is built into Filemaker. The most challenging aspect of this was simply learning Filemaker; the documentation could be better, and the software is not at all what you would call user friendly. Still, what I built today could end up being a powerful tool for us, and I feel that to have built it start to finish in one day while learning the software on the fly as I built it was a solid achievement.
For the first time in a long time, I can say I built software. That’s a pretty excellent feeling.