My Top-Secret Social Media Content Creator Hack Using Google Sheets

It can be a chore to create social media status updates from your content.

I get it.

Who wants to copy and paste text into your social media scheduling tool whenever you run out of updates?

I’d much rather spend my time doing other things, wouldn’t you?

So let’s take back some of your time by eliminating the need to waste hours each week creating status updates.

Sound good?

Cool.

And today, I’ll show you how you can not only easily generate social media updates from your blog posts, but how you’ll be able to use them again and again.

This post will cover:

• Why you need a library of social media updates for each blog post
• The reason you should share your blog posts again and again
• How to come up with multiple status updates for a blog post
• Why you are about to LOVE Google Sheets – even if you hate spreadsheets
• Ways to find your evergreen blog content
• Build your own social media content creator using Google Sheets
• The sneaky way to 10X your social media updates
• How to recycle your evergreen social media updates again and again

In the end, you’ll be able to paste in any blog URL and generate multiple social media updates in less than 2 seconds.

Check it out:

Excited? Let’s take a look and see how it’s done.

Why you need to generate a variety of social media updates

Look, it’s no secret that the majority of social media updates are usually just the title of the blog post.

And that’s OK if you aren’t into recycling your social media updates.

But you’re a smart marketer and you know the importance of stretching out your content as much as you can.

So it makes sense to create a bank of evergreen social media updates that you can use over again and again.

This way, when you reuse your status updates you have more of a variety of what gets posted. In fact, using the hack outlined in this post I’ll show you how to create social media content with ease which will all be for a single blog post.

Bonus points to sharing the variations to your different social media accounts.

Here’s a small example:

Define Your Own Social Sharing Rule https://t.co/HryfAHOERc pic.twitter.com/MmAC81j91O

— Chris Makara ???? (@ChrisMakara) August 23, 2017

Using the 5-3-2 Rule for Social Sharing https://t.co/mlZcy0NGlB pic.twitter.com/LmEYjqUOJL

— bulk.ly (@Bulkly) August 23, 2017

The reason you should share your blog posts on social media again and again

Depending on the social media channel you are posting on, there’s a good chance that the majority of your audience won’t see what you just shared.

In fact, according to Moz the average life of a tweet is 18 minutes.

Some other studies have listed it lower than that.

The bottom line is that on social media (especially on Twitter), your status updates drift off pretty quick.

This means that for the most part, your audience is not going to see your tweet.

Whether it is too much going on their feed, or the fact they were not online when you posted – your status updates tend to disappear in the blink of an eye.

So it makes sense to share your links again.

In fact here’s what Guy Kawasaki has to say about sharing the same thing again on social media:

[perfectpullquote align=”full” cite=”” link=”” color=”” class=”” size=””]”But on social media, if you’re not pissing people off, you’re probably not using it hard enough.”[/perfectpullquote]

He goes on to say:

[perfectpullquote align=”full” cite=”” link=”” color=”” class=”” size=””]And he has a response to people who complain about repeats: “If you saw my tweet twice, you don’t have a life.” Explaining that he’s simply reaching people who missed the tweet earlier usually mollifies people.[/perfectpullquote]

But don’t just take Guy’s word for it.

Even Hootsuite has posted the same thing multiple times.

Of course depending on your frequency and audience size, you’ll need to find a nice balance of how often to share the same thing. Ideally, you’ll want to know the best time to tweet or ideal times for the channel you are posting to.

But, the best way to reduce the likelihood of upsetting your followers is not only a balance of frequency, but using a social media queue that recycles multiple variations for your status updates.

How to come up with multiple status updates for a blog post

There are a number of ways you can create a variety of social media updates for your profiles.

Among them are:

• Using the blog post titles you didn’t use – chances are that you created multiple titles before you published. These work great as status updates!
• Pulling stats from your post – If your blog post cites any stats or numbers, these are perfect to use as a social media post.
• Creating a “pull quote” from your blog post.
• Paraphrasing key points found within the post

These are good ideas…but I’m more interested in automating as much of this process as I can.

So, I’m going to reveal the easiest way to create social media content at scale.

And to do this, we’re going to use Google Sheets.

[optin-monster-shortcode id=”okk7iuuzzirwsbnwbook”]

Why you are about to LOVE Google Sheets – even if you hate spreadsheets

Look, I know spreadsheets aren’t for everyone.

But don’t let Google Sheets scare you if you aren’t a spreadsheet ninja – because I’ll even give you a ready to go spreadsheet with all the formulas set up.

And if you want to skip the options I gave above on manually creating multiple status updates, I’ll show you how to use Google Sheets to automatically create the social media content for you.

You’re about to find a new love for Google Sheets.

So here’s how to do it step-by-step.

Create Your Google Sheet

Go to Google Sheets and create a new “Blank” spreadsheet.

Then inside your spreadsheet, you’ll create the following columns:

• Group
• Status Update
• URL
• Image URL

The next thing you need to do is start finding your evergreen blog content from your website.

[yellowbox]Get this spreadsheet ready-to-go for free – Register now for a free 6 part email course on how to create evergreen social media updates and you’ll get the spreadsheet used in this blog post for FREE. [/yellowbox]

2 Ways to find your evergreen blog content

There are a few ways you can do this which I’ll cover below. But regardless of the method you choose, be sure to add your data into the Google Sheet you created above.

Use Google Analytics to Find Evergreen Content

One of the best ways to to find your evergreen content is by looking into your Google Analytics account.

And here’s how to do it:

• Change your data range to something that will give you enough data to look at (last 3 months, 6 months, YTD, etc)
• Then go to “Behavior > Site Content > All Pages”
• From there, change the “Show Rows” setting to show 5,000 (or whatever number is higher than your number of results)
• Last thing is to export the results to a CSV file

[perfectpullquote align=”full” cite=”” link=”” color=”” class=”” size=””][Bonus Tip] – Should your website use a category structure like “/blog/”, then you can filter in Google Analytics to only show these URLs before you export. If not, you can just filter for them in Google Sheets.[/perfectpullquote]

After your file is exported, open it in Google Sheets.

Next you’ll need to delete any row that has data for a blog post that is not evergreen.

And if you have hundreds of blog posts, it might be best to sort your data to show the posts that have the most traffic, engagement, etc. to focus on your top evergreen content.

You might have noticed that the export from Google Analytics did not include the full URL of your blog post.

So what you need to do is create a formula to change these to show the full URL to your site.

In cell I2, add this formula (using your actual URL):

[perfectpullquote align="full" cite="" link="" color="" class="" size=""]=CONCAT("https://YOURSITE",A2)[/perfectpullquote]

If you entered everything correctly, you’ll see your full URL to the blog post in Cell I2. Then, you’ll need to drag the formula all the way down to the bottom of your results.

[yellowbox]Get this spreadsheet ready-to-go for free – Register now for a free 6 part email course on how to create evergreen social media updates and you’ll get the spreadsheet used in this blog post for FREE. [/yellowbox]

Use Your XML Sitemap to Find Evergreen Content

Not using Google Analytics? Bummer.

But don’t worry, here’s a nice little hack to use your XML Sitemap to get your blog posts. You do have a XML Sitemap, don’t you?

If you are not sure if you have a sitemap or not, one of the easiest things to do is try to directly access a common sitemap URL. Among the more common ones are URLs like:

• /sitemap.xml
• /post-sitemap.xml

Try either one by going to your homepage and pasting each option and hitting enter in your browser bar. If a new page loads (and not a 404 page) then that should be your sitemap. It will look something like this:

After you have found your sitemap, you will need to install this Chrome Extension. Once the extension is installed right-click over one of your URLs and select “Scrape Similar”.

This will show you all the results in a window like this:

Then click the “Export to Google Docs” button. This will export the data to a new Google Sheet.

In this sheet, you can skim the results and remove anything that isn’t an evergreen blog post.

There are other ways you can probably get a list of your evergreen content, but I find that most of the time either one of these two options work pretty good on most websites.

No matter how you find your evergreen content, you’ll need to add it to your Google Sheet.

[yellowbox]Get this spreadsheet ready-to-go for free – Register now for a free 6 part email course on how to create evergreen social media updates and you’ll get the spreadsheet used in this blog post for FREE. [/yellowbox]

Build your own social media content creator using Google Sheets

Now we’re ready to get to the good stuff.

You should have a list of URLs for your evergreen blog content. And with it, I’ll show you how to automatically generate social media updates just by using your blog post URL.

Excited?

You should be.

So let’s create some social media content at massive scale!

At this point, you have at least two spreadsheets:

• The one you created at the beginning of this post
• The second is from your Google Analytics export and/or your XML Sitemap

Moving forward we will only be working in the spreadsheet you created at the beginning. Let’s call this your “Evergreen Social Updates” spreadsheet. To do that, you’ll need to copy and paste your evergreen blog post URLs into Column C (Blog Post URL). So highlight all of your evergreen blog posts URLs and then click on cell C2 of your Evergreen Social Updates spreadsheet.

So it should look like this:

I’m not sure about you, but I’ve had just about enough cut and pasting for awhile…

So, let’s make this spreadsheet do some heavy lifting for us.

In fact, let’s automatically create some status updates. Sound good?

Here’s what you’ll need to do.

First, you need to be sure your site is using Open Graph tags.

If you aren’t familiar with Open Graph tags, they’re basically the code that sites like Facebook look at to generate images and titles when you share a URL on your social media account.

If your site doesn’t have Open Graph tags installed, I suggest implementing them right away.

Not only will it help better display your content when shared on social media, but it will also allow this Google Sheet hack to work for you.

There are plenty of Open Graph plugins if you use Wordpress.

With that said, In cell B2 enter this formula:

[perfectpullquote align="full" cite="" link="" color="" class="" size=""]=importxml(C2, "//meta[@property='og:title']/@content")[/perfectpullquote]

What this formula does is it pulls in the title your blog post uses as listed in the Open Graph tags.

You’ll see it populate cell B2 with the blog post title for your blog post URL in cell C2.

Next, you’ll need to drag down the formula in Column B to the row with the last blog post URL.

You know what’s even cooler than automatically creating social media updates?

Automatically grabbing the URL of the image to use on social media.

Check this out…

In cell D2, enter this formula:

[perfectpullquote align="full" cite="" link="" color="" class="" size=""]=importxml(C2, "//meta[@property='og:image']/@content")[/perfectpullquote]

Then drag down the formula. Boom.

You’ll see that the formula has fetched the URL of your image that you would use on Facebook.

While most major social media channels already will display this same image based on the URL you share, I’ll show you how you can use this image URL later to natively attach the image to your social media update in Buffer.

Just like the formula for creating the status update, you’ll need to copy the formula down for the image URL to the last row of blog post URLs.

And just like that you can easily create dozens (or even hundreds of social media updates).

But what if you want to take it next level?

The sneaky way to 10X your social media updates

It’s pretty common practice to use your blog post title as the text for the social media update…but what if there was a way to create multiple social media updates from a single blog post?

And these updates are created automatically using Google Sheets?

You can.

So get ready to have your mind blown.

Let’s 10X your updates!

1. In your Google Sheet, let’s create a new tab and call it H2 (click on the + sign in the lower left of Google Sheets near the tabs of the workbook)
2. Create the same four columns as your original spreadsheet
3. In Cell B2 enter the formula =iferror(IMPORTXML(C2, “//h2”),“”)
4. Copy the URLs of one of your blog posts from your first tab
5. In your H2 tab, click on Cell C2 and paste in your blog post URL
6. Wait a few seconds.
7. You should see a list of updates created before your eyes.

[MIND = BLOWN]

What is happening is the Google Sheet is extracting all the H2 title tags from your blog post and the open graph image for the blog post.

Not sure what an H2 tag is?

It’s basically a heading tag used in HTML code.

If the spreadsheet did not work for you, perhaps your subheadings use H3. And if so, click on the H3 tab and follow the same steps above.

You’ll want to grab the formula you used in the “Social Image” column of your first tab and paste it into the “Social Image” of your H2 tab. Don’t forget to drag the formula down a bit so it creates the image URLs for you if you want them.

Once the Google Sheet suggests social updates for you based on your H2 tags, you’ll need to copy all of them starting on row 2 and paste them into the “Content Upload” tab in the next available row.

Be sure to include the data in Columns A-D of the H2 tab.

Column A will be empty when you copy it, but you can fill it in on your “Content Upload” tab.

Now, there might be some H2 updates created that don’t make sense or need some tweaking. Be sure to read each one and decide if you want to keep it or not.

After spending just a few minutes you can easily create multiple status updates for every single blog post you have.

In the end you’ll have dozens or even hundreds of social media updates to use across your social media accounts.

[yellowbox]Get this spreadsheet ready-to-go for free – Register now for a free 6 part email course on how to create evergreen social media updates and you’ll get the spreadsheet used in this blog post for FREE. [/yellowbox]

How to recycle your evergreen social media updates again and again

Now that you have created all of these evergreen social media updates, wouldn’t it make sense to upload them somewhere just once and have them automatically sent to your Buffer account again and again?

This is where Bulkly comes in.

Bulkly connects with your existing Buffer account and will drip status updates into your queues as little or as often as you want. Forever.

You’ll be able to upload the Google Sheet you created above into your Bulkly account in just a few clicks.

Best of all, once you have imported your spreadsheet and selected your settings in Bulkly, you’ll never have to worry about manually adding content to your Buffer account.

There’s just a few steps to make it happen and here’s how it works:

• Complete Column A in your spreadsheet and export the Google Sheet as a CSV file
• Import your CSV file to Bulkly
• Select how many times per (day/week/month) to drip your updates into your Buffer account
• Choose which social media accounts you would like the updates sent to

Already created your Bulkly account? Cool, let’s move on.

Organize Your Blog Posts for Social Media Updates

Once the great things about spreadsheets (and Bulkly), is that you can organize your status updates into groups.

For example, if your blog covers a wide range of topics you can group together the similar ones. Let’s say you have a blog that has topics like:

• Hacks
• Tips

Instead of lumping these all together, you can create groups for these in your Google Sheet.

You may have noticed that in Column A we aptly named it “Group Name”. This was not a mistake 🙂

Within this column you’ll need to enter at least one group name. And if you want to segment your posts topically, categorically, etc. you will do so by using different group names.

So taking the topics mentioned above, in Column A you could use the following group names:

• Hacks
• Tips

All you would need to do is assign them to the appropriate posts like this:

Once you have that completed, all that is left is to export your Google Sheet as a CSV file.

Here’s how to do it:

• Click File > Download As > Comma Separated Values (CSV, current sheet)

Now that you have your social media content downloaded, it’s time to get it added to your Bulkly account.

Upload Your CSV to Recycle Your Evergreen Blog Content Social Media Updates

Log into your Bulkly account and click on the “Content Upload” tab on the left. On the next page click on the “Upload Content (CSV)” button and locate your CSV file.

After a few few seconds, every single one of your evergreen blog post social media updates will appear in the groups you assigned them to. By default, you will see the first group based on alphabetical order. You can view your other groups by going to the main page of the “Content Upload” section.

Inside each group you can edit any of your updates (or delete any you don’t want to use).

You might recall that in Google Sheets we automatically created a URL to the image for each blog post. In Bulkly you will see the image URL. And when Bulkly sends the social media update to Buffer, it will be attached to your status update. Then when it is posted, it will be natively attached to your social media update on channels that support image uploads on posts.

After you are set with your content, you’ll need to choose the settings for this group of posts.

The trick to having Bulkly endlessly drip your updates to Buffer is to enable “Recycle Posts” by clicking on it so it is activated.

With Bulkly you can also shuffle the order of the posts so that they are not sent in the same order to your Buffer account.

Under the schedule drop down, you can select how often you would like these updates dripped to Buffer (daily/weekly/monthly).

Next, click on the social media accounts shown in order to let Bulkly know which accounts to send these updates to.

Then, click the “Activate” button.

You should begin seeing updates drip into your account within a few hours (based on your settings).

Repeat this process for any additional groups you created.

And that’s it.

You’ll never have to manually add these updates to your social media accounts again.

Winner, winner – chicken dinner.

Want even more social media content creator hacks?

If you thought this post blew your mind, I’ve created a whole 6-part email course around evergreen social media updates (including what I covered here).

I’ll show you other ways to create an endless supply of social media updates to schedule on your social media accounts.

[optin-monster-shortcode id=”lvdi3efb9q9bp0hfetjp”]