Hootsuite’s Bulk Scheduling
Hootsuite offers a way to bulk upload social media posts via a CSV (Comma Separated Values) file to schedule posts according to your chosen dates and times. This is an extremely useful feature of Hootsuite that saves loads of time when social media scheduling, as you can just write the dates and times to post each Tweet or post without having to schedule each one manually.
Recently, I’ve had a client whose company regulations do not permit me direct admin access to their web properties. They just wanted a hand with their social media, which can be challenging when you don’t have access to their social media accounts. Hootsuite’s bulk scheduler allows me to schedule their Tweets and Facebook/Google+/LinkedIn posts on a monthly basis, and all I have to do is send the client the CSV file each month containing the schedule, for them to approve and edit how they wish. It is then up to the client to upload the CSV file to Hootsuite and engage with their fans and followers appropriately throughout the month.
Unfortunately, I’ve found that the creation of the social schedule CSV can be a little time-consuming. Which is fine if that time was spent just on the planning and writing of social media posts. But it seemed to me personally as if the most unnecessarily time-consuming aspect of the scheduling was the typing out of dates and coming up with different times to post every day so that it looks more natural. Of course you could choose to post at the exact same time every day but that to me would just scream “Hello, I’m automated!” and look off-putting as a fan or follower.
Bulk scheduling posts across your social media platforms should not be considered a replacement for engaged social media activity. If you think that you can just bulk upload all your Tweets, Facebook status updates and other social network posts and just be done with it, it’s going to be very obvious that you’re not there to interact with your fans and followers. Instead, bulk scheduling should be used as a way to put out regular content steadily across all of your social media platforms, whilst engaging with your fans and followers simultaneously. If they comment or reply, you should be quick to respond. They’ve taken the time to engage with you, and you should take the time to return the gesture. Not only will it make you or your brand easier to relate to, but building and maintaining these relationships with your fans and followers makes the entire social media experience more enjoyable and pleasant for everyone involved. It will also encourage more users to Like or Follow you.
Creating the Hootsuite CSV Template
A quick glance at what we’re going to be programming, before we break down the code in full to see what each bit actually does so that you can adjust the Hootsuite CSV template to your own preferences and bulk scheduling requirements. At the end of this tutorial is a full uncommented example of the following code, that you should be able to just copy and paste straight into IDLE or whatever IDE/editor of your choosing for you to play around with.
# First we import all the modules we need...
import csv
from random import randrange, randint
from datetime import date, time
from calendar import monthrange, weekday
# Set our menu variable to True by default. You'll see why.
menu = True
# While our menu is True, keep asking for user input
while menu:
# Ask the user for the number representing their chosen month
month = int(raw_input("Which month to schedule for? (e.g. Jan is 1, etc)"))
# If the month number is between 1-12, then proceed with the CSV
if month>0 and month<12:
# Create/Open hootsuite.csv file to write to
open_schedule = open("hootsuite.csv", "wb")
schedule = csv.writer(open_schedule)
# For every day of the chosen month, do stuff.
for x in range(0,monthrange(2015,month)[1]):
# Unless it's the weekend, then ignore it.
if weekday(2015,month,x+1)==5 or weekday(2015,month,x+1)==6:
pass
# If it's not the weekend though!
else:
# Assign 3 random post times between these hours
hour1 = randint(9,11)
hour2 = randint(12,14)
hour3 = randint(15,16)
# Create and format the date
day = date(2015,month,x+1).strftime("%d/%m/%y")
# Write the date to the CSV followed by the post times
schedule.writerow(
[day+" "+time(hour1,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow(
[day+" "+time(hour2,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow(
[day+" "+time(hour3,randrange(0,60,5)).strftime("%H:%M")])
# Close the Hootsuite CSV file and inform user that it's ready
open_schedule.close()
print "hootsuite.csv has been created."
# Set menu to False to break out of the while loop and exit
menu = False
# If month given doesn't exist (isn't 1-12) then help the user
else:
print "No month recognised as "+str(month)
print "1. January"
print "2. February"
print "3. March"
print "4. April"
print "5. May"
print "6. June"
print "7. July"
print "8. August"
print "9. September"
print "10. October"
print "11. November"
print "12. December"
Breaking Down The Python Code
So let’s break it down into pieces…
import csv
from random import randrange, randint
from datetime import date, time
from calendar import monthrange, weekdayYou want to import from all of the necessary modules. You can just import csv, random, datetime and calendar but I’ve chosen to import just randrange and randint from random, date and time from datetime and monthrange and weekday from calendar – since I’m limited on horizontal space for code in this tutorial, it allows me to use things such as randint() without having to include the module name first like random.randint().
menu = True
while menu:Setting a menu variable to True means that we can use it for a while True loop, that we can later break out of by setting menu to False. This way our menu or schedule options will only continue to display if we have satisfied the program and created the csv file. However if we keep choosing options that aren’t accepted by the program, it will keep taking us back to the beginning.
month = int(raw_input("Which month to schedule for? (e.g. Jan is 1, etc)"))
if month>0 and month<12:
open_schedule = open("hootsuite.csv", "wb")
schedule = csv.writer(open_schedule)Here we ask the user for input, and name their answer as month. We are looking for a number between 1 to 12, 1 representing January and 12 representing December. If they give a number between 1 and 12, the program will continue creating the CSV scheduler in accordance to the chosen month. First the program creates (or opens, should it already exists) a file called hootsuite.csv. You can change the file name to anything you like – hootsuite.csv, social.csv, twitter.csv, etc… When we open or create the file, we have to give ourselves permission to write to it and not just read it.
for x in range(0,monthrange(2015,month)[1]):
if weekday(2015,month,x+1)==5 or weekday(2015,month,x+1)==6:
passNow we start actually writing to the csv file. We want to go through every single day of our chosen month, and tell our program to do stuff. Using a for loop in Python, we can tell our program to go through every date of the month between 0 and the number of the days within the chosen month. monthrange() can give us this number if we give the parameters of the year and month number (which we’ve already received from the user input – month). In this example, I’ve set the year to 2015. You could make the program ask the user for the year as we did with month, but hard coding 2015 in seems easier for a small program like this, since you’d only need to change it once a year. Lastly in the range parameters for monthrange, we see the index [1]. This is because monthrange is actually a tuple.
If we typed into the Python Shell:
>>> from calendar import monthrange
>>> monthrange(2015,1)
it would give us back (3, 31). We asked monthrange for information about January 2015, and it told us that the first day of January 2015 is a Thursday and that there are 31 days in that month. (Thursday is 3 because Monday is 0 by the way.)
In our for loop, for every day of the month, we want to check whether that day is a Saturday or Sunday. In this particular client’s industry, they are not worried about maintaining social media activity throughout the weekend since they are targeting other businesses which are likely to only operate on week days. But you can choose to remove these if/else statements from your program and just schedule for every day if you so wish. Or you can change the days to different days of the week, etc. The if statement is looking out for if weekday(2015,month,x+1) is 5 or 6. Basically, if the date (x+1 because x begins at 0) of the chosen month in 2015 is a Saturday or Sunday (5 or 6 – Monday starts at 0, remember!), then the program will pass on to the next date and not list this date in the CSV.
else:
hour1 = randint(9,11)
hour2 = randint(12,14)
hour3 = randint(15,16)
day = date(2015,month,x+1).strftime("%d/%m/%y")
schedule.writerow([day+" "+time(hour1,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow([day+" "+time(hour2,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow([day+" "+time(hour3,randrange(0,60,5)).strftime("%H:%M")])
If the day is not a Saturday or Sunday, the program will assign three different integers to three different variables: hour1, hour2 and hour3. This is because I want to schedule three posts a day. hour1 will give me a random integer between 9 and 11, hour2 between 12 and 14 and hour3 between 15 and 16. If I give date() the parameters of the year as an integer (2015 in this case), the chosen month (which we’ve conveniently assigned to the month variable) and the date of the month (x+1 because x starts at 0), we can format the date to the string “%d/%m/%y”. So the 17th September 2015 in that format, for example, would return the string “17/09/2015”.
Now that we can generate random times between specified hours however many times a day we want, we can write them to our bulk schedule CSV. This is what schedule.writerow() does; we are writing rows to the schedule file. So let’s take a look at exactly what this is doing:
schedule.writerow([day+” “+time(hour1,randrange(0,60,5)).strftime(“%H:%M”])
Within each row of the schedule CSV we are concatenating day (our formatted date) followed by a space and then the formatted time as a string. Like date() which we used to get day, time() also takes certain parameters. We can actually give time() the time in hours, minutes, seconds and microseconds. But we only need the hour to publish the social media post (generated by hour1, hour2 and hour3) and minutes. randrange(0,60,5) will give us a random number between 0 and 60, followed by the optional step of 5 which means that it will count between 0 to 60 in steps of 5 (e.g. 0, 5, 10, 15….). Though the step is an optional parameter for randrange(), the step is necessary for bulk scheduling in Hootsuite as Hootsuite will only accept posting times that end in a 5 or a 0. We then convert this time to string and format it as “%H:%M” so that 1 o’clock in the afternoon will look like “13:00”. Lastly, we want to enclose everything in [] for writerow(). This is because writerow() will only accept a sequence, and strings act like sequences of characters. Without the square brackets, each character of each row will be written to its own column. We only want to fill one column with the posting date and time, so by enclosing it in square brackets we are confining the whole string to a single item of a sequence.
Having three schedule.writerow()s like this tells our program that we want to write three posts for every date we iterate through. Remember that we’re still in the for loop! If you wanted to schedule more posts throughout the day, for example, four times a day between 9am and 5pm, we can write an additional hour4 variable and change the times of hour1, hour2 and hour3 to spread the posting times evenly. Then you would just add another schedule.writerow() for hour4. You can bulk schedule a maximum number of 11 posts per day using Hootsuite.
print "social.csv has been created."
open_schedule.close()
start = FalseOnce the program has iterated through all the dates of the month and written to the CSV file, excluding the weekends, then we come out of the for loop and can inform the user that the file has been created by printing it to the console, and then save and close the file. start = False will break us out of the while True loop, so that the program finishes and the month option won’t be displayed to us again. Though it might seem weird that the menu has been open this entire time, when the program is actually executed, it would have been less than a second since you gave the input a number. To the user, it would seem immediate.
else:
print "No month recognised as "+str(month)
print "1. January"
print "2. February"
print "3. March"
print "4. April"
print "5. May"
print "6. June"
print "7. July"
print "8. August"
print "9. September"
print "10. October"
print "11. November"
print "12. December"If you remember at the beginning of the program, we told our program to only create the CSV file if the month given by the user is between 1 and 12. So what happens if it’s not? This is where our else statement comes in. If the number given by the user is less than 1 or more than 12, the program will inform that the month isn’t recognised and display the acceptable numbers alongside their respective month names. Then the program will loop back to the beginning, as we haven’t escaped the while True statement yet by setting menu to False.
This is what it will look like to the user if they type an unacceptable number, followed by if they type an acceptable number afterwards:
Once the program has been correctly executed, you can find the .csv file in your working directory; the same folder that your Python program is in.
You can then open the CSV in Excel or OpenOffice Calc or any other spreadsheet application of your choice. Below, you can see I’ve uploaded the file to Google Sheets. Your spreadsheet application may change the format of your dates and times, but this is a very quick fix that doesn’t take more than a few clicks. Just highlight Column A and Format Number by dd/mm/yyyy hh:mm (don’t forget the space!). This is the exact and only format that Hootsuite allows when bulk scheduling, and all times must be at least ten minutes from the upload time, with only one message per time slot.
Your Final Python Hootsuite CSV Template Generator!
It should look a little something like this…
import csv
from random import randrange, randint
from datetime import date, time
from calendar import monthrange, weekday
menu = True
while menu:
month = int(raw_input("Which month to schedule for? (e.g. Jan is 1, etc)"))
if month>0 and month<12:
open_schedule = open("hootsuite.csv", "wb")
schedule = csv.writer(open_schedule)
for x in range(0,monthrange(2015,month)[1]):
if weekday(2015,month,x+1)==5 or weekday(2015,month,x+1)==6:
pass
else:
hour1 = randint(9,11)
hour2 = randint(12,14)
hour3 = randint(15,16)
day = date(2015,month,x+1).strftime("%d/%m/%y")
schedule.writerow(
[day+" "+time(hour1,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow(
[day+" "+time(hour2,randrange(0,60,5)).strftime("%H:%M")])
schedule.writerow(
[day+" "+time(hour3,randrange(0,60,5)).strftime("%H:%M")])
print "hootsuite.csv has been created."
open_schedule.close()
menu = False
else:
print "No month recognised as "+str(month)
print "1. January"
print "2. February"
print "3. March"
print "4. April"
print "5. May"
print "6. June"
print "7. July"
print "8. August"
print "9. September"
print "10. October"
print "11. November"
print "12. December"
You should be able to copy the code above and paste it straight into your IDE/IDLE.
