How To Find The Best Time To Tweet Using Twitter Analytics

By: Chris Makara

Knowing the best times to tweet can help your Twitter account get the most out of its social media updates.

But how do you know when the best time to tweet is?

If you are like me, I have come across dozens of blog posts and infographics telling me when I should tweet.

Unfortunately, every single one of them is wrong.

There are way too many variables that get missed out on:

  • They are not timezone specific
  • Engagement will be different depending on niche/industry
  • Quality of followers can impact engagement
  • Engagement can mean different things (retweets, @ replies, likes, etc)

Most importantly:

  • They don’t use data from your Twitter account

How can someone tell you when the best time to post on Twitter is without using data from your account?

All you are interested in finding out is what are the best times for your Twitter account to tweet to get the most engagement (whatever you might consider engagement).

Let’s truly find the best time(s) to tweet for your actual Twitter account.

Get the Excel file: Click here for the ready-to-go Excel file that makes it simple to find the best time to tweet. 

You’ll Need Twitter Analytics Set Up

If you haven’t already, you’ll need to have set up your Twitter analytics account. Don’t worry, it’s very simple.

Login to your Twitter account and then follow the prompts given here.

Once setup, Twitter will start gathering data for your account. If you just set up your account, it might take a while (a week or two) before you can really tell when the best time to tweet is.

If your Twitter analytics account has been set up for a while, then you are good to go.

Export Twitter Analytics Data To Find Best Tweet Times

In order to find your best times to tweet, we need to download your Twitter analytics data. To do this, click on the “Tweets”  tab and then the “Export data” button.

twitter analytics best time to tweet

You’ll notice that Twitter Analytics will only allow you to export data from 90 day date ranges. So if you want to use data for more than the past 90 days, you need to run multiple exports and then combine your data into one file.

When you are exporting your data, it will be downloaded as a CSV file.

Not sure what a CSV file is?

It’s the acronym for “comma separated values” which basically means the values in the data are separated by commas.

TMI?

Probably. Moving on…

Finding the Best Time to Tweet

Once your CSV has downloaded you will need to open it up in Microsoft Excel.

Click in cell A1 and then use your keyboard to create a pivot table by pressing “Ctrl+T”.

create a table with twitter analytics

You’ll be prompted with an overlay that will verify the range of data to be included as well as if your table has headers. The range should be ok and you can leave that field alone, but be sure that the checkbox is checked for the table headers.

Click ok.

In the data exported from Twitter analytics, they do not have a column that tells us the day of the week. So we’ll need to add a column to convert the date into a day name.

So in cell AO1, type in “Day of Week” and then in cell AO2, copy and paste this formula:

=TEXT((IFERROR(DATEVALUE(LEFT(TRIM(TEXT(D2,"yyyyy-mm-dd hh:mm:ss")), FIND(" +0000",TRIM(TEXT(D2,"yyyyy-mm-dd hh:mm:ss"))&" ")-1)),""))*1,"ddd")

Basically, what this formula does is take the date given by Twitter, cleans it up and ultimately returns the day of the week for that date.

convert twitter analytics dates to day of week

The way Twitter analytics creates the dates data, it appends the time of day to that cell. So we need to create a formula to extract the time of the tweet to a cell.

In cell AP1, type in “Time of Day” and then copy and paste this formula into cell AP2:

=VALUE(MOD(VALUE(TIME(MID(TRIM(TEXT(D3,"yyyyy-mm-dd hh:mm:ss")),12,2), MID(TRIM(TEXT(D3,"yyyyy-mm-dd hh:mm:ss")),15,2),"0")),1))

You’ll need to highlight column AP and then select “Time” from the cell formatting drop down.

get times tweeted in twitter analytics

Still with me? Good.

If not, you can download the ready-to-go Excel file. It’s as simple as copy and pasting in your Twitter analytics data.

Get the Excel file: Click here for the ready-to-go Excel file that makes it simple to find the best time to tweet. 

Next up, we need to create a pivot table so we can easily manipulate the data to show us exactly what we need in order to find the best times to tweet.

To create a pivot table, you will need to be on the “Design” tab of the the “Table Tools” ribbon. From here, you can click on the “Summarize With Pivot Table” button.

This will give you a prompt that will ask you about the pivot table, you can just click “ok”.

create a pivot table with twitter analytics

Once you have clicked “ok”, you will be taken to a new sheet in your Excel workbook.

Now here is where it might get confusing for some, but I’ll do my best to explain.

what is the best time to tweet

In this sheet, we will create the pivot table that will show us the best time to tweet for your account. Here are the steps to do it:

  • Drag the “Engagement Rate” field to the “Values” section
  • Drag the “Day of Week” field to the “Columns” section
  • Drag the “Time of Day” to the “Rows” section
  • Highlight the engagement rate data and use conditional formatting to show the “Top 1” value
  • Format the cells to show percentage

Excel will automagically show you the best day and time to tweet based on your Twitter data.

One thing to note is that this is grouping the times by the hour the tweets occur. So it takes into account all tweets that happen within that hour to come up with the average.

Now you can decide you want to find the best time to tweet on something else besides average engagement rate. For example, you could look at:

  • Retweets
  • Likes
  • URL clicks
  • Etc.

Simply just drag the metric you want to use for determining the best time to tweet into the “Values” section of the pivot table.

How To Find the Best 10, 20, 30, etc. Times to Tweet

Finding the single best time to tweet is nice, but let’s face it…you are not going to tweet just once a week at that time?

I didn’t think so.

But check this out…

You can easily identify the top 10, 20, 30, etc. times to tweet using conditional formatting. It’s really easy to do after you set up your pivot table above.

best times to tweet

Here’s what you will need to do:

  • Highlight the data for engagement
  • Use conditional formatting to highlight the top 10, 20, 30, etc. results

You will then be able to see what days and hours make the most sense to tweet during.

Find the Worst Times to Tweet

Just as easy as it is to find the best times to tweet, you can find the worst times to tweet.

Just repeat the steps above, but instead of using conditional formatting to show the top 1 (10, 20, 30, etc) have it show the bottom 1 (10, 20, 30, etc) and your results will highlight the lower performing times to tweet.

Easy stuff.

Why You Need to Know the Best Times To Tweet

Knowing the best times to tweet can help you maximize your Twitter presence by tweeting when you tend to get the most engagement.

This should lead to more clicks, retweets, etc. each time you post.

And if you are automating your social media updates with a tool like Buffer, you can adjust your schedule to post during these days and times according to your Twitter analytics data.

best times to tweet in buffer

One option you can do is to post multiple times within the hour you are getting the most engagement. This way, you can try to maximize on the potential engagement rates during that hour.

You’ll just need to be careful with your social media automation and not overdo it.

So You Suck at Excel & Need Help

I’m not going to lie, I enjoy working in Microsoft Excel…but I know it’s not for everyone.

  • Maybe everything I said made absolutely no sense, but you want to know what the best time to tweet is?
  • Perhaps you are lazy not interested in creating this Excel file and want it handed over to you ready-to-go?

No worries, I’ve got you covered.

Get the Excel file: Click here for the ready-to-go Excel file that makes it simple to find the best time to tweet. 

Bonus Included:

When you export your Twitter data, it is not specific to your timezone – meaning that the times it says are probably not truly accurate unless you happen to be in the default timezone.

Therefore, your tweet times need to be adjusted to reflect your actual timezone.

My ready-to-go file has a simple drop-down menu to let you select your timezone and will convert all the tweet times to it. Pretty sweet, huh?

Get the Excel file: Click here for the ready-to-go Excel file that makes it simple to find the best time to tweet. 

change twitter timezone

But Wait, There’s More

Well, not really. It’s a complete file that only needs you to drop in your Twitter analytics exported data. Then on the pivot table just click “refresh data” and the table will reload showing your best time to tweet.

Get the Excel file: Click here for the ready-to-go Excel file that makes it simple to find the best time to tweet. 

When is Your Best Time To Tweet?

After you have put this actionable advice to work, I’d like you to leave a comment of when your true best time to tweet is.

It will be interesting to see all the different “best times to tweet” that everyone will end up with.

Content Upgrade Box: This should be able to be defined in the post editior with a short code or something to make this yellow box display.

Smarter social media automation is possible with Bulkly

Get back more of your time by eliminating unneccessary repetitive and manual social media scheduling tasks.