I grew up playing with numbers. And throughout my career I have used tools like Excel and Google Sheets to understand data better and help others do so too.
This was done on my first job out of university (a researcher on the Wellcome Trust’s part of the Human Genome Project) and is still done today – albeit thankfully the tools are a little better than Excel’s graphs.
I still am proud of the first such project I undertook: I was a junior account exec at Kinross+Render and the analysis I did directly enabled the team to convince Xerox to give the PR account for the one division of Xerox UK to us. Demonstrating (not just saying) that we could deliver more.
At the time I had assumed analysis was just part of every campaign. I had assumed incorrectly.
But this is changing and for PR professionals, it’s no longer simply enough to do what was promised at the start of a campaign. Practitioners now need to understand what isn’t working in order to get better RoI for the clients – and ensure the contract renews / rolls over.
Evaluating the effect of the campaign against pre-defined goals lets you do that.
Here are some of my favourite free tools for doing this. Yes… it’s heavily Google oriented, but there’s a reason for that… Google is good.
1. Data manipulation: Google Sheets
Google Sheets not only lets you collaborate without sharing the same doc over Email and getting confused. It is free to use and lets you import data easily. And embed the graphs on a website to create a password-protected online dashboard for your clients.
It’s also got some incredibly powerful functions that few know about. Most notably the Query function, and the importXML function plus the ability to write custom scripts that lets you automate data updates and bring in custom data via APIs.
I particularly recommend starting with Macros (a way for the computer to look at what you do and turn it into computer functions). And adding the importJSON function for pulling in data via APIs.
2. News monitoring: Google News RSS
Not got the budget for Meltwater or Cision, then pull in news about your client from a Google News search. At the bottom of each is the ability to set up an RSS or mail alert. Select RSS and pull it in via the importRSS function in Google Sheets.
It won’t pick up everything. But if Google can’t find it, can your client’s customers?
Best of all, Google’s URLs are a standard format that let you extract (via the FIND, LEFT and MID functions in Google Sheets) the data that the importRSS doesn’t supply.
Drop me a line if you’d like help on how to do this.
If you have budget, I’d also recommend Webhose. It’s no longer dirt cheap ($20 per month, now $500) but is still the best coverage tracker I’ve found.
3. Social share count: SharedCount
SharedCount is a tool to pull in data on how much engagement a piece of coverage has got. Its basic level is free and will either let you copy and paste the Facebook share count stats, or pull them in via API (go back to the import JSON function I mentioned above).
4. Twitter brand monitoring: TAGS
Twitter isn’t covered by SharedCount anymore. But you can still download all mentions of your brand / its rivals using a great Twitter importer called TAGS. And from this you can also track mentions of coverage (based on looking for the headline). It’s free to use and brilliant.
5. SEO – Authority Labs
Authority Labs is a great platform that lets you track how your website ranks (in countless countries) for specific keywords (it claims up to 2000, I’ve not tested this claim but I suspect it would be slow at this level).
It normally costs a fortune, but it does have a free spreadsheet tool that links to its API so you can see what’s happening on any given day and build a picture over time.
6. Effect of coverage – Google Trends, Google Analytics, SimilarWeb
Hopefully no client still wants AVE. But many want reach still (despite this being as not, if not more ropey). If you don’t have the budget to pay for a Cision or Meltwater and still need this data, you can go to SimilarWeb’s free pages to see monthly traffic figures (visits per month / pages per visits / traffic share for the top 5 countries) for any given publication. Tabulate these for your key publications and run a sumif / averageif / vlookup formula to automatically pull this data into your coverage report.
Away from ropey stats… Google Analytics and Google Trends will give you a much better view of the effect of your campaign, allowing you to see both how many people are visiting you, and how many people are trying to find information about you after a particular activity is done.
Don’t forget when correlating to give a figure on how much you’ve improved your client’s web traffic / search figures you should be comparing like with like. Typically for B2B websites weekend traffic should be eliminated (use weekday function in Google Sheets) in B2C industries it might be the other way round. In some each day needs to be treated as a law onto itself.
7. Visualising data
Google Sheets is great for graphs that can be embedded. And for some graph types (or rather data types) it is second to none. But there are times when it doesn’t have the same feel as a truly interactive report and Google Data Studio is the best of the free platforms here. Create stunning, dynamic reports that let you compare over time, eliminate specific data points (eg show only coverage from the US, or from Europe) at the touch of a button.
We have built dynamic reports for many clients using these (and other) data sources. If you’d like a demo of how PR Evaluation can help you understand your marketing campaign results better or show off your agency / department to your client / CEO, please drop me a line.
I’m here to help.
I should stress… we are very precious about our clients’ data. The image is a top-level dashboard created using dummy data: releases are imported via an RSS feed from a randomly selected company’s site, and this is blended with Google’s demo Analytics data.