Using Google Docs To Generate Hot Content Strategies [Tool]

> Skip the chit chat, give me that tool now!

It has been a little while now since my last blog post, but hopefully the wait has been worth it! I’m very excited to be writing this post and to be sharing this tool with you.

Research for new content strategies takes time

Every day in our industry we face the challenge of developing new strategies for attracting traffic to a website and staying ahead of the competition, whether this is through on or off site engagement. As marketers it’s our job to overcome these challenges to ultimately build ROI.

But before you can jump to a strategy you need to do the research:

  • What type of content is (and has been) working well – is it contextually relevant?
  • What topics are people talking about now in and around your industry?
  • What’s trending?
  • What questions are people asking?
  • What types of content is attracting social shares, mentions etc?
  • What types of websites are mentioning these topics? And more specifically, who?
  • Which platforms are being utilised to promote this content?

– In a nut shell, what is hot right now? And how are people interacting with this content?

Our research doesn’t end there, once you have the above data you should be checking out similar topics too. Answering these questions will give us the foundations to creating our own on or off-site strategy.

Check out our content generation tool

The problem we have with this process is that it naturally absorbs a lot of time, and if you are just checking out ideas for outreach purposes it is almost completely unfeasible.

For the purposes of this blog post here is a top level view of the on & off site strategy process:

Maximise return, minimise time

After checking out Tom’s epic guide to Import XML for Google docs, I was inspired to develop a tool that would effectively generate this research which could be adapted for both processes based on a single user action.

As soon as a user enters a keyword(s) into the tool, content will be returned from a number of sources all based on this keyword.

These sources can be categorised as follows:

The latest news & discussions

Google Discussions – What’s the latest [keyword] talk in the forums?

Google News & Bing News – Hot off the press [keyword] news.

What content is being shared?

Digg – the most dugg content relating to [keyword], there is also an additional column for the most recent dugg content.

Reddit – The top rated reddits relating to [keyword].

Youtube – The most viewed videos relating to [keyword].

Topsy Latest Tweets – the most popular [keyword] related tweets in the last day, including twitter name and number of RTs received.

Topsy Latest Top Trending Tweets – this is then aggregated in the same way as the previous point.

Twittorati Search – the most ‘authoritative’ tweets relating to [keyword] based on the Twitterati authority score.

All Things Now – the most popular content Facebook shares relating to [keyword].

What questions are being asked?

Yahoo Answers – questions relating to [keyword] filtered by questions with the most answers.

Wiki Answers – this is split in to two columns, the latest questions that have been asked relating to [keyword] and the top answered questions.

How Stuff Works – articles answering a problem relating to [keyword].

Content aggregators

Blog Catalog – the latest blog posts relating to [keyword].

Fark – the latest and most popular items shared on Fark relating to [keyword].

Redux – discover further video content relating to [keyword].

Helium – The latest [keyword] articles shared on Helium.

Cracked – One of my favourite sites I couldn’t leave out. A fun/comedic site returning articles relating to [keyword].

Niche

Due to the generic nature of this blog post, I haven’t included any niche specific sources in the tool, but to really get the most out of it incorporate aggregation sites relating to your target niche or even specific blogs, there are plenty of them out there which will provide an even greater insight into what works well/what’s hot in your niche right now.

The party doesn’t stop there

After going through your first sweep of the Google doc, your brain should (hopefully) be flooding with ideas for link bait, viral and useful content that could be used for outreach or on your own website, but that was for only one keyword!

Towards the end of the spreadsheet you will find three additional columns looking into keyword variants. Using the power of Uber Suggest and Google’s similar and related queries, why not incorporate these into your search for even greater content analysis and idea creation…

But wait a sec!

Before you get stuck into other queries, there’s another dimension to this tool which has been running in the background. If you head on over to sheet 2 (Source and Place), the tool will whip up possible placement opportunities for the ideas which you will have just been putting together.

With the help of Topsy I have pulled out ‘experts’ relating to the keyword you will have typed earlier. To help filter out the junk, I have incorporated another filter which searches the users twitter profile to determine how many times they have mentioned your keyword (naturally works best with single generic keywords), and the number of followers they have. Finally, the website that is featured on their bio has been extracted. Together they will help you to identify potential outreach placements.

Ok so this helps for outreach, how can this actually help create content for my own website?

Analysing the data collected within this tool will tell you what types of content are working for your niche. If there are questions being asked over and over again, why not consider introducing useful guides for people on your website, or if there are a lot of different questions being asked why not develop a Q&A system.

You should constantly be looking at how you could potentially capitalise on the latest news to gain traffic, just tread carefully as some can be more controversial than others.

Analyse the types of content that is attracting a lot of social buzz and how the websites have went about promoting this content – is it a video?, an infographic?, a topical post?, a creative piece or an article that triggers an emotional response? Etc.

Strategies based on this research can begin to evolve, but before you become fixated on delivering i.e. a Q&A system – break out the box, find out what (in this instance) awesome Q&A systems are currently out there, and take look at what makes them stand out from the crowd. List their positive characteristics, but also take note of the negatives – this identifies potential gaps that you could exploit in your strategy.

Whether it’s a QA system, an infographic or whatever, research every fragment of existing strategies, look for the holes and how you can make this even better and more unique – finding gold takes time, but if you mine for long enough you are going to eventually strike it rich!

Version 2 Update

An update for this tool has now been released, please make copy of the latest version here:

http://seogadget.com/content-strategy-generator-tool-v2-update/

I am very excited to be sharing this tool with you and would love to hear your thoughts.

Image credit: Tom T



Stay Updated: Sign Up for Webinar & New Blog Alerts

45 thoughts on “Using Google Docs To Generate Hot Content Strategies [Tool]

  1. Christiaan says:

    Wow! Would love to test this, but it seems like you can’t make a copy if you’re only permitted to view the preadsheet…

    Cheers

  2. danielbutler says:

    Whoops! Thanks for letting me know, this has now been fixed. Just log in then you can make a copy :)

  3. Can’t wait to try, but I cannot make a copy. Am I missing something? Thank you

  4. Yes, working, thank you.

  5. Hi Daniel,

    i have not posted a comment on here for ages, but wow this is a really good use of Google docs. I am always impressed with all the different uses they have and this one is a great time saver to get started with content generation ideas!

    thanks for sharing.

  6. Karen Fovargue says:

    This is a fantastic tool and nice and quick to use, nice work this will save me tons of time = big win :-)

  7. Craig Addyman says:

    WOW – This is awesome!!

    Shhtandard!!!!!

  8. Christiaan says:

    Thanks! This is great. Time to experiment :)

  9. Jon says:

    Hey Daniel,
    do you know how far back Topsy pulls the info on mentions?
    Great article mate!
    twitter @JonSEM

  10. Alex says:

    This is awesome! This is almost exactly what I do manually. Sweet tool Daniel. Thanks for sharing it :D

  11. danielbutler says:

    Hi Jon,
    Thanks for that – this part of the data is based on the last 30 days of user activity.

  12. danielbutler says:

    My pleasure, glad you like it :)

  13. Robin Moore says:

    Hey Dan – looks really good. I will give it a play and give you feedback.

  14. Jen says:

    Wow, this sheet is a serious time saver! Thanks for putting in the effort to create such an awesome tool.

    Quick question: the Google Discussions column isn’t working for me. I only get #Value!

  15. Louis Ri says:

    Hi Dan, Congrats on the new tool. I’ll give it a good tinkering with tomorrow and give you my feedback.

  16. Samantha says:

    This is BASICALLY the coolest thing I’ve seen out of the SEO community in a loooong time. You absolutely have no idea how excited I am right now haha.

  17. danielbutler says:

    Hey Jen, great to hear you are enjoying the tool and thanks for the feedback! Sometimes this value is returned if queries are run in close proximity, as we are querying Google for this column of data ;-)

    If this was for another content type, the likelihood is a similar behaviour from the data source or there is no data available based on that particular keyword entered.

  18. Graeme Mac says:

    Tool looks great. I can’t save a copy for some reason tho – any ideas?

  19. Roxanne_B says:

    A tool that combines organisation and creativity? Perfect!

  20. danielbutler says:

    Hmm strange, are you logged in?

  21. Deb Ward says:

    I downloaded the tool, but can’t seem to enter anything into the B3 cell. Nothing happens.

  22. Tahir Fayyaz says:

    Thanks for sharing this a great insight into how you do your research. I was doing something very similar but instead of pulling the results into the google spreadsheet I created links to the landing pages on different aggregation sites for various keywords.

    I was then scraping those aggregated pages for the links and using the excel SEO tools extension (that I found through your blog) filtered links with a lot of twitter mentions, G+ and facebook likes. Just wanted to share another method to build on top of this as I have learned so much from this blog

  23. Michelle says:

    Daniel, you are beyond awesome. Seriously this tool is amazing!! I’ve tried to do similar with a smaller number of sites and completely failed to create anything as stable as this!

  24. Innes says:

    I do use this tool but this is not something I have thought of doing to say the very least. This is an interesting way and has got me thinking.

  25. Paul says:

    Hi, this content tool sound and looks great, but when i download it i am unable to change the keyword in cell B3. ANY IDEAS!!!

  26. Jason Cook says:

    This is seriously good stuff, Daniel. Thank you for taking the time to put this together. Between you guys and the Distilled team, we have some pretty powerful SEO tools at our disposal now. Many thanks!

  27. Sam says:

    Does anyone know how to fix the Topsy Latest Tweets section of this document? I’ve played with some XML in google docs before, but here, I’m stumped.

  28. Eldad says:

    Wow!

    Keep up and Best of luck to you

    Eldad Caspi

  29. Aman says:

    Daniel,

    Nice tool mate, looking forward to getting access to it and using it.

    Great work.

  30. Alex' says:

    Can you make the Google doc public again so we can have a go at it please.

    Thanks

  31. danielbutler says:

    Hi Alex, thank you for bringing this to my attention. Not to sure how this became blocked, but I have updated the tool and should now be working ok.

  32. as long as it not too complicated cus i’m a bit of a muppet with this sort of stuff but it looks really good

  33. Zip Heaters says:

    This is a good example of just how to make the most of Google Docs. I also like that fact that this is free advice.

  34. Robby says:

    This a great way to use Google Docs and certainly helps keep track of the necessary steps for content writing. We use Google Apps so this will be extremely beneficial for us. Thanks for the information.

  35. Adam says:

    Hi Daniel

    Google appear to have changed the search strings meaning google news and google discussions columns aren’t populated. Any chance of a fix?

    Thanks

  36. Michelle says:

    Wow, just wanted to thank you guys for building and sharing this amazing tool! I can’t seem to get any results for a few columns like Google Discussion, News and the Topsy columns but even with those errors, it’s still a very valuable tool!

  37. Rosemary says:

    This is fantastic thank you so much!!!

  38. Adam says:

    Hi Daniel, I’m a different Adam (see 3 posts up!) but I’m having the same problem.
    All of the Google fields have no data? But I’m not sure where I need to go to update that?

  39. danielbutler says:

    Hey Adam, thanks for the feedback, an update for the tool will be out very soon – please watch this space!

  40. Patrick says:

    Hey, watching this space for an update on the tool. Thanks Daniel for these helpful ideas on content generation. Nice. Nice.

  41. AndyO says:

    Also watching this space for an update on the tool — and I echo the sentiments of others in saying that it does seem quite a useful content resource.

  42. danielbutler says:

    Hi all, a new update has been released! – please grab the latest copy here https://seogadget.co.uk/content-strategy-generator-tool-v2-update/

    Let me know what you think of the new features :-)

  43. William rock says:

    I would love to see the tool in action, it sounds like a great idea.

  44. Rob says:

    This is cool but the hover button only reveals the source for the first line of results. What can you do about this?

Comments are closed.