Blog

Mining Reddit With Google Sheets

As you know, Reddit rocks. There are Subreddits for just about anything you can think of, and each Subreddit typically has a very strong and knowledgeable user base – Typically. This means that there are tons of questions being asked and various discussions being had that center heavily on the niche that the Subreddit caters to. In other words – content ideas galore!

I can almost guarantee there are way better tools out there to do this, but I put together my own Google Sheet tool that pulls the post titles and URLs from a Subreddit, which you can then parse through to find potential content gaps and content ideas that you should write.

Disclaimer: This tool is definitely not great – but it has helped me come up with new ideas.

Disclaimer 2: This is set up to only pull internal post URLs (known as Self Posts). This isn’t really a problem though, since many of the questions and discussions will be “self posts” on Reddit. If you’re looking for a tool that will pull all of the cute cat pictures from /r/cats – this is not the tool.

 

How To Use Google Sheets to Mine Content Ideas from Reddit

 

  1. First you will need to make a copy of this Google Sheet that I have made. Click the link, click “File”>”Make a Copy”> and then name it whatever you’d like. I’d suggest naming it “Taylor Tomita rocks” – but that’s up to you.

make a copy google sheet

  1. Once you make your copy, you will then navigate to your desired Subreddit that you want to pull content ideas from. I will use /r/AskReddit for this example.

Askreddit

 

  1. Enter the Subreddit’s URL into the “Subreddit URL” field of the Google Sheet.

subreddit url

Keep in mind that this will only pull the data from the page you are on (there are typically 25 results per page). If you are wanting to pull the data from the next page you will need to navigate to the next page and paste that page’s URL into the “Subreddit URL” field. (Forgive me, I am not a Google Doc mastermind and I couldn’t get it to work otherwise).

You can also enter one of the sorting URLs (/new, /rising/, /top…etc) in the Subreddit URL field. This can be great to find new questions being asked, or the most popular discussions in each Subreddit.

 

  1. Next you will enter the Subreddit Text (the name) into the “Subreddit Text” field of the Google Sheet. This field is CASE SENSITIVE, so add it exactly how it shows in the actual Subreddit’s URL.

You will also need to add an apostrophe ( ‘ ) to the end of the Subreddit Text. I couldn’t make this work without doing that. Again, I am not a Google Sheets mastermind.

For example: for /r/AskReddit you would simply enter: AskReddit’

subreddit text

  1. Once you do this you should start seeing the Post URL and Post Title fields in the Google Sheet start to populate. You should now have a list of all of the Post Titles and URLs on the page that you entered.

Depending on how the Subreddit is formatted, this may pull random Reddit URLs from the Sidebar of the Subreddit. This is somewhat of an annoyance, but I haven’t had it be an actual problem. You’ll see this happening below.

the good stuff

 

  1. Copy the Post Titles and URLs you are interested and paste them in your own Excel Doc or Google Sheet doc. Be sure to use “Paste Special>Paste Values Only” when you do this. Otherwise you will just paste a bunch of Functions and that will suck. Don’t do that.

 

As of now, I don’t have this set up to automatically refresh with new data or anything too special. However, it can be a quick thing to jump into and grab some ideas if you’re struggling to find something to write about.

Anyway. I hope this tool ends up being useful to someone! I know it has helped me grab some great content ideas for the niche site that I manage!

If anyone has any tips/tricks on how to make this thing better, I am all ears. Get in touch!