Content Strategy Helper Tool – Version 3

Consolidate trending news from across the web, and find inspiration for your research topics quickly and easily:

CSGT Search


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.

What’s new?

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.

Data includes:

  • 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)
  • Digg
  • (latest research)
  • YouGov



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)
  • Yougov
  • 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:

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 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.

  1. Log into your Google Account
  2. Head to the tool here, and then to ‘File’ > ‘Make a Copy’
  3. You’ll then need to install the script to run the =ImportJSON functions, so click ‘Tool’ > ‘Script Editor’.
  4. Replace the placeholder content in a new script with the code from here.
  5. Rename the script to
  6. 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.

Have We Been Helpful?

Thank you for reading a post from our Creative Outreach / PR Research archives. Have we been helpful? If you'd like to engage our services or speak with one of our consultants about a project, contact us and we'll be in touch.

Stay Updated: Sign Up for New Blog Alerts

Follow: | | |

Tags: , , , , | Categories: Creative, Outreach / PR, Research

35 thoughts on “Content Strategy Helper Tool – Version 3

  1. Max says:

    Wow – thats crazy shit! Awesome idea and great tool, thanks for sharing – lets see how useful its for the german market ;)

  2. Daniel Butler says:

    Hey thanks Max!

    Do let us know how you find it, shouldn’t be too difficult for us to further localize the tool in the future if we can collect a list of useful sites :)

  3. Adam Butler says:

    OH MY!

    I can’t even remember how long I’ve waited for the old generator to be fixed, I absolutely loved that tool.

    Thank you so much Daniel, I can’t wait to try this out.

    Seriously, the original was one of my go-to SEO tools for the longest time. I just let out half a scream in my office when I saw that this had been updated.

    Thank you thank you thank you!

  4. Adam Butler says:

    My google UK and US columns don’t seem to be working?

  5. caryism says:

    UMMMM – This is SICK, thanks for sharing this team!

  6. Sangati says:

    Google news imports do not work. Need to be fixed.

  7. Daniel Butler says:

    Hi Adam, anything Google related is extremely sensitive to retrieval requests. Despite being a Google Spreadsheet, if they’re hit too hard in quick succession they temporarily disable access for that user.

    Given that Google News features for both the UK and US in the ideas and trending tabs, that’s technically 4 simultaneous requests for data on initial load of the document. I’d suggest just waiting a minute or two between changing your keywords.


  8. Amit Kumar says:


    First of all, a special thanks goes to your for summarising all the information at a place and constructing a well explained guide. This is really an amazing Tool and people are going to find it so helpful.

    Keep helping by sharing your ideas !!

  9. Regina says:

    How Stuff Works and Google Scholar imports does not seem to be working. Otherwise, thanks for such a great resource.

  10. Daniel Butler says:

    Thanks for the awesome feedback guys :)

    Regina – I’ve updated the Xpath for Scholar, so that’s now pulling through ok. How Stuff Works seems to be working for me though(?). I’d just assume that if no data is returned, there genuinely is no expert articles on the topic you entered for that site.

  11. Joe Ryan says:

    Awesome stuff Daniel. Thanks for sharing!

  12. Depesh says:

    This plus Buzzsumo FTW. Nice one Dan & Richard.

  13. Wally says:

    This is an awesome tool, thank you so much for posting it!

  14. Anne says:

    I can’t get to “make a copy.” It’s greyed out. Ideas?

  15. Anne says:

    I got it! I’m sorry. You can just delete my earlier comment. Was my own silly fault.

  16. Gav says:

    Cracking idea.

    1st time user, trying to copy doc across using safari. But safari keeps crashing when 1st loading.

    any ideas to resolve

  17. That’s not something we can help you with sorry! If you’re running a new install and all the latest versions all I can say (and this will be annoying!) is run it on a Windows 8 machine running chrome.

  18. Wow – what an great idea and awesome tool. Thank you so much for sharing. Will test very soon and see if it will work well for non English markets too.

  19. Lazy Django says:

    These are the days people are very crazy about content strategies if it better we have perfect too right? Here it is, Its very cool and Extraordinary tool.. Thanks for sharing such a cool tool..

  20. Tim says:

    That was kinda fun to watch. Thanks for sharing!

  21. Chris Kerr says:

    This looks like such an amazing tool, which comes as no surprise from the builtvisible team. I am having difficulty loading the =ImportJSON script from Git. The page is returning HTTP 503 – page temporarily unavailable. I’m on chrome running OS X Yosemite version 10.10.2.

    Thanks and keep up the great work.


  22. Daniel Butler says:

    Hi Chris, looks like the site went down for maintenance though seems to be accessible now – can you please try again? Thanks,

  23. Chris Kerr says:

    @Daniel Fixed and Sorted. Thank you.

  24. Aziz Shaikh says:

    Awesome but I don’t find any changes in other 2 tabs Source & place and Trending. Do i need something to understand maybe i may have missed out in reading?

  25. Daniel Butler says:

    Hi Aziz, the Trending sheet does not update as this is fixed to show what is trending now in the UK & US. The Ideas and Source & Place sheets will update following your keyword input, I’d suggest just double checking the set up process particularly the importJSON() script as that’s where the error is likely to have occurred if there is one :)

  26. Mary B says:

    This could be a gamechanger, Daniel! Thanks for sharing! I just followed the steps above, and it seems to be working correctly (even though it’s not pulling results for every source/column like Digg and Reddit). The most recent hits are coming from March… and it’s June. Any way to update this to view more recent results? The trending results are current though… it’s just the “Ideas” that are a little outdated. Thanks!

  27. Catalin says:

    This is an awesome tool, thank you so much for posting it!

  28. Deborah Kay says:

    What time period do you use to pull the data from? If I wanted to change the country for Google (e.g. singapore) – can and how do I do that? (sorry..novice here…)

  29. Hunter says:

    Hi Daniel, I am so glad that i came across your blog post. This tool definitely is promising. Quick question currently this tool populates only 30 articles .Any hack to increase the numbers of rows to be displayed.

    Thanks again

  30. Daniel Butler says:


    @Hunter – unfortunately there is no way to do this, as the tool just scrapes the content from the URL its given. You could probably pull the paginated results (if there is one) but would take quite a bit of tinkering.


  31. Tom Lowery says:

    Sounds like a great tool, but pardon my ignorance. I don’t understand how to “Replace the placeholder content in a new script with the code.” Can you provide a few “idiot proof” steps?


  32. Daniel Butler says:

    Hi Tom,

    That’s ok, so when you open a new script it’ll already contain some code. That just needs to be deleted, and then the code as linked above copy/pasted in place of that content, and voila.

  33. Just downloaded the spreadsheet. But messed up the jSON script. A detailed article about jSON script setup would be helpful for the newbie content marketer like me.



Leave a Reply

Your email address will not be published. Required fields are marked *