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

About

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.

Trending

trending

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
  • ONS.gov.UK (latest research)
  • YouGov
  • Data.gov

Ideas

ideas

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)
  • ONS.gov.uk
  • Yougov
  • Data.gov
  • Google Scholar
  • How Stuff Works
  • Yahoo Answers (most answered)

Source & Place

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
  • Bio
  • Tweet count
  • Following
  • Followers
  • 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:

Reformat

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.

Extend

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:

=ImportFeed()

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.

=ImportHTML()

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.

=ImportJSON()

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 ImportJSON.gs
  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!


Stay Updated: Sign Up for New Blog Alerts

Follow: | | |

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

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

    Thanks,
    Dan

  8. Amit Kumar says:

    Wow,

    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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">