Consolidate trending news from across the web, and find inspiration for your research topics quickly and easily:
Skip to Set-Up
This is our 3rd update of the Google Spreadsheets based Content Strategy Helper Tool.
As you’ll see, we’ve completely reworked the tool and added a large number of new social sites, content aggregators and news sources.
There’s a lot of new stuff that makes V3 much, much better than V2. We’re pretty sure you’ll love what you find.
Some features will feel familiar with the ‘Ideas’ and ‘Source & Place’ sheets still present, but we’ve also introduced a new ‘Trending’ sheet that includes details of the hottest topics of conversation right now.
Trending gives you a 10,000ft view of the most popular topics on a variety of networks across the web.
Get “Trending” data from UK & US, sources including social networks, news aggregators, government research & other data sources.
- Twitter (users, hashtags, phrases)
- Google Search Trends
- YouTube Trends
- Reddit (front page)
- Google News
- Rad URLs (top trending on Facebook & Twitter)
- All Things Now (top trending on Facebook)
- Hacker News (front page)
- LinkedIn Pulse (top shared content in the last 24hrs w/ view count)
- ONS.gov.UK (latest research)
Use “Ideas” to see the most successful current content related to your specific search query / topic.
Data sources include:
- Google News (UK & US topic related news)
- Hacker News (top performing topic related articles)
- Reddit (top)
- Digg (top)
- YouTube (most viewed topic related videos)
- Google Scholar
- How Stuff Works
- Yahoo Answers (most answered)
Source & Place
Find out more about relevant topic influencers related to your search query. With the assistance of Followerwonk, we’ve segmented the data to return bloggers or editors associated with that topic. This information should provide you with the opportunity to reach out to relevant authors for potential brand coverage.
Data retrieved includes:
- User name
- Tweet count
- Profile age
- Social Authority
Where to from here? Set some goals!
You’re going to see a lot of data, which might be somewhat overwhelming initially. Don’t worry, it makes sense quickly!
In order to get as much value from this tool as possible, you need to have an outline of some basic objectives – What are your goals? Where will the content reside? What channels do you have available? What type & how much resource do you have at your disposal?
Understanding your basic objectives will help you to assess the information for what you need, whilst avoiding getting lost in a sea of data.
There are also a few things you can bear in mind when developing your ideas, too:
Simply changing the format of an existing story is enough to generate fresh buzz & interest. Outreach has almost taken care of itself, allowing you to just follow up with the publications, authors etc who covered the original piece.
If you’ve spotted a really successful but “aged” story, why not provide an update on your site with fresh data and reach out to the original authors / publishers for their thoughts. Adding a comment to the original post is also another way to stir up some discussion.
If you’re unable to update the story, why not create a fresh angle or by creating an opinion piece?
Understand channel popularity
Keep an eye on the types of content & format that is performing better on different networks, publishers etc. Understanding what is successful puts you at an advantage when reaching out & tailoring your emails.
How does the CSHT v3 work?
In the last year we’ve encountered quite a few new obstacles with =importXML() that mean creating version 3 has been rather challenging. We’ve changed the way this tool operates, switching away from =importXML() and instead, we’re using several different methods for data collection:
e.g. =ImportFEED(A1, “items title”)
Pulls the content of RSS feeds directly to Google docs, but can be filtered to return specific properties.
In instances where a feed wasn’t available, tools like feed43.com make it really easy to convert any web page to RSS.
e.g. =ImportHTML(A1, “list”, 1)
Extracts content from HTML lists or tables. However this function will only retrieve content once from the specified URL, and will not update if the HTML page changes in anyway.
For us this is still useful as the URLs change based on the user search query, but worth bearing in mind if you’re planning on using this function in the future.
e.g. =ImportJSON(A1, “/feed/entry/title, /feed/entry/content/src”)
Ok, so this one isn’t a standard function in Google Spreadsheets, it uses a very clever custom script from Paul Gambill (developed on ImportJSON by Trevor Lohrbeer) to parse JSON in Google Docs.
Requests can be chained together to pull the exact data you require.
How to get set up:
Set up is easy, following the instructions below should take just a few minutes.
- Log into your Google Account
- Head to the tool here, and then to ‘File’ > ‘Make a Copy’
- You’ll then need to install the script to run the =ImportJSON functions, so click ‘Tool’ > ‘Script Editor’.
- Replace the placeholder content in a new script with the code from here.
- Rename the script to ImportJSON.gs
- Click ‘Save’.
Open Doc to Get Started
And that’s it! All of the functions to make the tool work have been inserted for you, so all that’s left is to pop in your search query/phrase on the ‘Ideas’ sheet (replace the text in red) and start the data analysis.
If you have any problems accessing the tool, or any suggestions for data you’d find particularly useful please do drop us a comment below, or if you’d like a hand with your content strategy, take a look at our service here.