Forecasting Seasonal Keyword Traffic with Python Script

[znotes]

A few weeks back, I went to a Distilled meetup here in NYC.  SEER Interactive's Mark Lavoritano did some cool slides on the seasonality of keywords. Basically, his presentation made the point that you should not only think about which keywords you want to rank for but also WHEN they are most valuable.

This made me think...we have a lot of moving parts to our marketing efforts. Emails with interchangeable modules, a homepage with interchangeable links, and other dynamic elements for which we have to decide what themes we want to market for the week. Babies or bikes? Kitchen Gadgets or Wine Glasses?

Google Insights for Search  is a great tool which allows you to look at keyword traffic year over year. However, for many of the keywords (like the ones mentioned above), on a multi-year timeframe, it can be tough to sift out the specific weeks in which traffic repeatedly peaks year after year. What I really wanted to see was the last 5 years laid on top of each other to find the common peaks. Even better, if I could map 5 years of keyword data to a single row in a spreadsheet and then use conditional formatting to create a colorscale, I could create a sweet forecasting calendar with several keywords and use this to choose the best timing for various marketing campaigns. Here's a link to a screenshot of the calendar I created: forecasting calendar

I could have done this in excel, but I've been wanting to try out Python for a while now and decided this was a great time to do it. After some reasearch, I figured out how to import a csv into python and the rest was done with for loops and lists, which is fairly basic python. I've pasted my code below. In a nutshell, the program runs through all 5 years of traffic data and increments a count in a list whenever it sees a peak (according to a threshold called "peakInterestValue" that you set in the code). The output is a list of 52 numbers [0-5] (representing 52 weeks over 5 years). If the value is a 5, it means that all 5 years showed a peak in traffic at that week. If it's a 4, then 4 (out of 5) years showed a peak that week, etc...you can then copy/paste this to a row in an excel sheet with all your keywords, apply a color scale w/ conditional formatting, and  boom! you've got a forecasting calendar.

This code works on the exact file that Google insights exports so you don't need to format it at all. It's ready to rock.

If you want to see the code formatted and cleaned up, check it out here. If you want to see a hot mess, I've also pasted the code below. You can drop it right into a .py file and run it off a cmd prompt but you'll need to install python first:

http://www.python.org has installation info and great tutorials as well.

Enjoy!

import csv """reads a file from google insights""" """Open the last 5 years of data from Google Insights""" anniversarygiftFile2007 = csv.reader(open("anniversarygift2007.csv","r"))anniversarygiftFile2008 = csv.reader(open("anniversarygift2008.csv","r"))anniversarygiftFile2009 = csv.reader(open("anniversarygift2009.csv","r"))anniversarygiftFile2010 = csv.reader(open("anniversarygift2010.csv","r"))anniversarygiftFile2011 = csv.reader(open("anniversarygift2011.csv","r"))

"""Combines the data into a list"""

anniversarygiftFile = [anniversarygiftFile2007,anniversarygiftFile2008,anniversarygiftFile2009, anniversarygiftFile2010,anniversarygiftFile2011] """counters"""i=0j=0 """flags used to initialize lists"""

definedFlag=0

definedFlag2=0 for i in range(0,5):

j=0

for row in anniversarygiftFile[i]:         if j<=4:

"""skips the first 5 rows"""

elif j==5:

"""initialized the list on the first week of data"""

anniversarygift=[row[1]]

definedFlag = 1

if(i==4):

peakInterestWeeks=[0]

else:

"""appends the list with each row"""

anniversarygift.append(row[1])

if(i==4):

peakInterestWeeks.append(0)

if len(anniversarygift)>=52:

print("i = ",i)

if (i==0):

if(definedFlag==1):

anniversarygiftArray = [anniversarygift]

definedFlag2 = 1

elif (definedFlag == 1):

if(definedFlag2 == 1):

anniversarygiftArray.append(anniversarygift)

break

j=j+1       i=i+1

""" Now all of the data is in python lists""" i=0

j=0

""" Lower peakInterestValue to lower the traffic threshold and discover more peaks """

peakInterestValue=90

""" This is a variable to help you tweak peakInterestValue"""

peakInterestCnt = 0

for i in range(0,5):

print("i =",i)

for j in range (0,51):

if int(anniversarygiftArray[i][j])>peakInterestValue:

"""If keyword interest peaks, peakInterestWeeks[] is incremented"""

peakInterestWeeks[j]=+=1

peakInterestCnt +=1

print("Peak interest",peakInterestWeeks)print("Peak Interest Count =",peakInterestCnt)

"""peakInterestWeeks[] is printed out to a row in an excel file"""

c = csv.writer(open("anniversarygift.csv", "w"))

c.writerow(peakInterestWeeks)

[znotes]

Thanks for the feedback. You guys are right about Youmoz. I just submitted it now after adding some screenshots, more thorough directions, etc...keep an eye out for it.

-Zack

[KeriMorgret]

I totally agree with James here. This looks like it'd be a really helpful post for YouMoz! I would include some screenshots to grab the attention of readers and let them know what kind of results they'll see after they do this and why it's worth their while. Feel free to submit, we'd love to see it!

Keri

[JamesNorquay]

Hi Mate,

Nice work, I was actually thinking about doing something like this some one was talking about long term insights for search data.

Thank you for putting this togehter but, if you have time you should make a post in the YouMoz blog with this information as it would be better suited to the whole SEO community.

Kind Regards,

James.