paulbalcerak

Setting aside the geekgasm that is Twitter Analytics on the whole, I’d like to take this post to describe how to produce a report on your account’s best times and days of the week to tweet. We’ll do this recipe style:

Best Times to Tweet Report
Difficulty: Easy
Time: 5-10 minutes

What you’ll need:

• A Twitter account.
• Twitter Analytics.
• Excel, or something like it. (Note: I’m using Excel 2011 for Mac, so if the functions I describe here don’t match up with what’s on your system, just do a Google search for the buttons you need to find.)

Step 1

Log in to Twitter Analytics and make sure you’re looking at the Timeline Activity view. In the upper right, click Download CSV. Make sure you download the CSV file and NOT the Excel file — this is crucial. Also, since more data leads to greater accuracy, pick the report for the last 90 days of tweets. You can add to it later to ensure even greater accuracy.

Step 2

Highlight Column B (“time”) and copy it. Now highlight Column C, right click and click Insert copied cells. Rename Column B so it says “Days of Week,” and rename Column C so it says “Times of Day.”

Step 3

It’s possible that you’re looking at a bunch of hashmarks (######) in your Days of Week and Times of Day column. We’re gonna fix that in this step. Highlight the Days of Week column, right click and click Format Cells. This’ll pop open a system dialog box with a nav bar on the left. Click Date, and select the option that lists days of the week. Now click OK. You should see the days of the week appear in your column. If you don’t, make the column wider and they should appear.

Now highlight the Times of Day column and click Format Cells, but this time in the nav, click Time. Select the option that’s formatted as 1:30 PM, and click OK. Stretch out your column if you need to.

Step 4

OK, confession time: I’m not an Excel wizard by any stretch, so these next couple steps might seem a little janky. Bear with me, because at least they’re easy.

At this point, you have a whole bunch of individual times and dates, and that’s gonna mess you up if you try to make a chart because Excel will view them as unique data points. You’re trying to figure out data for all 11 a.m.’s and Tuesday’s, for instance, so you need to generalize the data. Here’s my duct-tape way of doing that:

Highlight the Days of Week column and copy it. Now pop open a plain-text editor — on the Mac, TextEdit will work perfectly — and make sure the text type is set to plain text (you might have to Google how to do this). Once that’s done, paste in your column, click Edit > Select All and copy the text again (at this point, the text you pasted is in plain text format). Now go back to Excel and paste the copied text into the Days of Week column.

Nothing will change visually, except maybe the left-right justification, but Excel will now recognize that data as plain text, which will make generalizing the data way easier.

Follow this same process for the Times of Day column and move on to the next step.

Step 5

We’ve got a little bit more formatting to do, and then we can actually start doing stuff. Select all the data in your spreadsheet by clicking in the upper-left corner (or you can hit APPLE + A / Edit > Select All), and then in the menu at the top, click Data > Sort. In the dialog box that pops up, make sure you’ve got the box in the upper right, that says “My list has headers,” is checked. Tell it you want to sort your data by Days of the Week, on Values, from A to Z. Click OK. Now all the Fridays in the spreadsheet are bunched together, as are Saturdays and so on.

Now, more duct tape:

The spreadsheet is still looking at all these data points as individual dates (e.g. Friday, April 21) and we need it to think of them as all the same thing (e.g. Fridays). So just rename them all. Type “Friday” into the first Days of the Week box, copy it, highlight all the other Fridays and hit paste. When you’re done, it should look like this:

Now go do the same thing for all the other days of the week.

…

And we’re back. We need to do the same thing for times, so highlight all your data, and go to Data > Sort. Make sure you’ve got “My list has headers” checked, and that you’re sorting by Time of Day and sorting on Values. This time, it’s going to make more sense to order things “Smallest to Largest,” because what that’s going to do is order your times of day from earliest to latest. Hit OK, and you’ll see all the times lined up neatly. As with the Days of Week list, these times are still all being recognized by Excel as unique, so you want to rename them for the hour they exist in (e.g. 6:02 AM = 6:00 AM; 6:56 AM = 6:00 AM). Make sure you type in the appropriate AM/PM designations because Excel isn’t gonna figure that out for you.

*Note: This is one area where you can easily customize your report. I’ve chosen to look at tweets by hour, but you can easily analyze them by half hour, quarter hour and so on. All you’d do is double the data sets each time and name them appropriately. If you were segmenting by the half hour, for example, anything between 4:00 PM and 4:29 PM would be 4:00 PM, and anything between 4:30 PM and 4:59 PM would be 4:30 PM. It takes a little bit longer, but it’s also more precise.

Once you’re done with this step, we’re ready to rock….

Step 6

Select all your data and click Data > PivotTable. I like to put mine in a separate worksheet; you can do what you want.

From here, you should have something like the picture below, and we can start organizing data:

The first thing you’ll want to do is set the X and the Y axes. I like to have Times on the Y and Days on the X. To do that, click and drag “Days of the Week” in the black PivotTable Builder above to the “Column Labels” field and “Times of Day” to the “Row Labels” field. It’ll look like this:

Next, you’ll want to populate the Values field — this is where you’ll see your best times to tweet. From the PivotTable Builder, drag your retweets and/or mentions and/or favorites down into the Values field. Right away, you should notice values start to populate themselves in the main body of the table.

You’re not done yet, though. You need to make sure you’re getting averages and not just totals. Totals can be misleading because if you got, say, 15 retweets at 2 a.m. on a Tuesday one time, the data will make it seem like that’s a great time to tweet, when really, on average, that’s a terrible time to tweet. To get that average, we’re going to click the little “i” next to each variable in the Values field:

That’ll pop up yet another dialog box where you can rename each variable and average the values:

(Note: You’ll probably also want to click that button toward the lower right that says Number… and tell it to count to zero decimal places so you don’t get a bunch of overly complicated numbers.)

After you’ve done that, it’s really just a matter of cleaning up the table to look the way to want — maybe highlight the times with big numbers in them, maybe resize the columns — and parsing over the data.

You’re done!

Keep in mind, this chart can be saved and added to as you go. You could update it each month and pretty quickly have a veritable mountain of data from which to analyze data.

If you run into any trouble along the way, feel free to contact me on Twitter, Facebook, Google+ or via email: paulbalcerak [at] gmail [dot] com