I mean to do this every year, and every year something comes up that stops me – but the back end of 2019 was kind enough to leave me with a few hours to work out a way to save time with generic daily post scheduling.
A few prerequisites before we begin:
- You’ll need access to a social media management tool with a bulk upload function (we use Hootsuite, but Buffer, Zapier and others also offer a similar option).
- You’ll need to have ensured that your meta-titles and meta-descriptions are optimised and within the recommended limit (~60 for titles and ~160 for descriptions), unique and well written.
- You’ll need Screaming Frog access (or similar).
- You’ll need a bit.ly account.
Why look to bulk upload posts?
To begin with, I suppose I should explain why we’re looking at bulk posting – it’s not that we’re looking at increasing our post rate for no reason, but that we have spent several years building a library of resources for various experience levels and we’re proud of them. However, because of time restraints – after the initial share, they seldom get a second post.
This one and done approach would be fine were the resources and blogs we work on time sensitive, but those that can be updated are updated and others are what would be considered ‘evergreen’. So, the only way we were going to get these resources shared with people more regularly was to ensure that it didn’t encroach upon the time we spend producing various blogs and resources (even this is being written in part to satisfy a deadline!).
So, while it is often stated that regular and often is the way to post on social media – we’re only looking to up our posts because we’re proud of what we do, and we’d advise the same to anyone looking into it.
The crawl
First things first, you will need to obtain the URLs, titles and meta-descriptions of the blogs and/or resources you want to use as posts over the next year. To do this in Screaming Frog, you simply use the subfolder URL that you want to crawl and click start (you can also exclude regular expressions if the subfolder contains content you don’t want/need).
Then filter that list to only include 200 response codes (only live pages). You can do this by clicking on the appropriate option in the panel on the right:
Following this, you can export the list as a CSV file.
Once it’s finished downloading, you can open it up and simply copy and paste all of the data in to a Google Sheet – name the tab ‘Screaming Frog’ (or something memorable). We only need three of the columns, so you can delete each of them except for:
- Address
- Title 1
- Meta Description
You can also get rid of the top row. This just pares the list down to what we’ll need (and cuts the possibility of choosing the wrong cells later on).
Concatenation and URL shortening
Next you’ll need to create a new tab – again, give it a memorable name – this tab is where most of the work will be done (though I’ll provide a link to a template at the end if you need a head start). Most of it is concatenates – which I always do in sections as it’s easier to catch and solve problems/mistakes. As we want to track our traffic to the site from each resource, the concatenate columns will add our UTM tracking to the URL:
Concatenates and other columns
CONCAT1: – this is resource URL and the first section of the tracking code, so your formula will be:
=CONCAT(‘Screaming Frog’!A2,”utm_source=”)
You can then drag this down the column as far as your crawled list goes.
CONCAT2: – the second part of your tracking code. You can either add a cell to which this column can refer, or you can add it manually, but this is the social platform you’ll be uploading the list for.
If you choose to refer to a cell, make sure you amend the formula as you go when you drag it down, use $ signs to fix the reference, or have the name of the source populate the full column (the sheet I share later does a bit of both).
=CONCAT(A2,”twitter”)
Or
=CONCAT(A2,B2)
Using the second option is a little easier when it comes to changing the source for the next platform – in the sheet at the bottom, I’ve used a third cell to populate column B from a drop down menu.
CONCAT3: – simply adds the next section of the tracking.
=CONCAT(B2,”&utm_medium=”)
CONCAT4: – This is handled in the same way as CONCAT2 the only difference being instead of platform it’s the medium (as you’d expect).
=CONCAT(A2,”social-media”)
Or
=CONCAT(A2,B2)
CONCAT5: – same as three and one, just the next bit of the tracking code:
=CONCAT(B2,”&utm_campaign=”)
REFORMAT TITLE: – here we’re going to create our campaign title (the one that tells us which post directed traffic) using the meta-title, so your code will be something like:
=SUBSTITUTE(‘Screaming Frog’!B2,” “,”-“)
CONCAT6: – Here we’re just adding that campaign to the main URL:
=CONCAT(G2,H2)
SHORTEN URL: – this one is a little trickier.
Firstly, we need to use a couple of cells further on in the sheet to add our bit.ly authorisation details. You can find your details using the method here.
Your cells for this should look something like this:
Once these are in place, we’re going to make an API call and a concatenate in the one cell:
=importData(concatenate(“https://api-ssl.bitly.com/v3/shorten?longUrl=”,ENCODEURL(I2),”&access_token=”,$N$2,”&format=txt”))
Essentially this tells Google Sheets that we’re calling data from an external source, then provides it with a URL to request the data from (the bi.ly API address combined with our newly created long URL) and then provides it with our access credentials.
Provided you’ve successfully followed my ramblings so far, you’ll get a brief ‘loading’ message in the cell followed by the newly shortened URL.
Bulk upload posts
Date and time
After the concatenate process, this is the manual step that takes the most time. Essentially, you’ll need to decide how often you’d like to post and on what days – then manually input them (if someone can come up with a better, I’m all ears and happy to edit with a credit). My only time saver on this is to do a full month and then copy and paste the lot, then do find and replace for the four middle characters (/02/ for /03/ for example – though remembering the different number of days for each month).
Post content
This is where having a good meta-description comes in. Provided they’re all well written and under the limit – the copy here (designed to pull people in from the SERP) should make for a perfect social post. Therefore, your formula should be something like:
=(‘Screaming Frog’!C2)
If you’re looking to post the same piece of content multiple times, you’ll need to ensure the posts aren’t identical – but this can be done using another simple concatenation and adding a ‘from our news page’ or similar to the beginning or end of the post.
URL
Again, we’ve done all the work on this one, so the formula just calls another cell:
=(‘Concatenate and Shorten’!J2)
Image
Whether you use images or not (most platforms will pull through from the page these days) is up to you, but adding hundreds of unique URLs will be a pain – so maybe consider an eye-catching but generic gif for this purpose that you can use for all of the posts.
Upload
Each of the platforms will have a different approach to this, but most social media management tools do offer a bulk upload option (check the date and time requirements – these are right for Hootsuite, but others may vary).
Final thoughts
So there we have it – in principle, the way to (mostly) automate your bulk uploads. Once you’ve set this up once, you’ll simply have to copy and paste your crawl data into the appropriate page and then upload!
If you have any thoughts/comments or possible improvements, just leave a comment.
Oh, and here’s the link to the template.
Featured image by Tim Bennett on Unsplash
