I have been hard at work testing out different approaches to Adwords. One of the keys is that I’m scripting up a lot of the management of campaigns, ad groups, keywords, and ads. The Adwords API could be used but there’s an expense to using it which would be a significant expense for the size of my campaigns. So I have been using the Adwords Editor to help manage everything. What makes it excellent is that the tool has import and export to/from csv files. This makes it pretty simple to play with the data.
To get a file that this script will work with just go to the File menu in Google Adwords Editor and select “Export to CSV” You can then select “Export Selected Campaigns”. it will write out a csv file.
This Python script will read those output csv files into a Python data structure which you can then manipulate and write back out to a file.
With the file modified you can then use the Adwords Editor’s “Import CSV” facility to get your changes back into the Editor and then uploaded to Adwords.
Having this ability to pull this data into Python, modify it, and then get it back into Adwords means that I can do a lot of really neat things:
- create massive campaigns with a large number of targeted ads
- Invent bidding strategies that act individually at the keyword level
- automate some of the management
- pull in statistics from CPA networks to calculate ROIs
- convert text ads into image ads
Here’s the script:
#!/usr/bin/env python
# coding=utf-8
# (C) 2009 HalOtis Marketing
# written by Matt Warren
# http://halotis.com/
"""
read and write exported campaigns from Adwords Editor
"""
import codecs
import csv
FIELDS = ['Campaign', 'Campaign Daily Budget', 'Languages', 'Geo Targeting', 'Ad Group', 'Max CPC', 'Max Content CPC', 'Placement Max CPC', 'Max CPM', 'Max CPA', 'Keyword', 'Keyword Type', 'First Page CPC', 'Quality Score', 'Headline', 'Description Line 1', 'Description Line 2', 'Display URL', 'Destination URL', 'Campaign Status', 'AdGroup Status', 'Creative Status', 'Keyword Status', 'Suggested Changes', 'Comment', 'Impressions', 'Clicks', 'CTR', 'Avg CPC', 'Avg CPM', 'Cost', 'Avg Position', 'Conversions (1-per-click)', 'Conversion Rate (1-per-click)', 'Cost/Conversion (1-per-click)', 'Conversions (many-per-click)', 'Conversion Rate (many-per-click)', 'Cost/Conversion (many-per-click)']
def readAdwordsExport(filename):
campaigns = {}
f = codecs.open(filename, 'r', 'utf-16')
reader = csv.DictReader(f, delimiter='\t')
for row in reader:
#remove empty values from dict
row = dict((i, j) for i, j in row.items() if j!='' and j != None)
if row.has_key('Campaign Daily Budget'): # campain level settings
campaigns[row['Campaign']] = {}
for k,v in row.items():
campaigns[row['Campaign']][k] = v
if row.has_key('Max Content CPC'): # AdGroup level settings
if not campaigns[row['Campaign']].has_key('Ad Groups'):
campaigns[row['Campaign']]['Ad Groups'] = {}
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']] = row
if row.has_key('Keyword'): # keyword level settings
if not campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']].has_key('keywords'):
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['keywords'] = []
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['keywords'].append(row)
if row.has_key('Headline'): # ad level settings
if not campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']].has_key('ads'):
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['ads'] = []
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['ads'].append(row)
return campaigns
def writeAdwordsExport(data, filename):
f = codecs.open(filename, 'w', 'utf-16')
writer = csv.DictWriter(f, FIELDS, delimiter='\t')
writer.writerow(dict(zip(FIELDS, FIELDS)))
for campaign, d in data.items():
writer.writerow(dict((i,j) for i, j in d.items() if i != 'Ad Groups'))
for adgroup, ag in d['Ad Groups'].items():
writer.writerow(dict((i,j) for i, j in ag.items() if i != 'keywords' and i != 'ads'))
for keyword in ag['keywords']:
writer.writerow(keyword)
for ad in ag['ads']:
writer.writerow(ad)
f.close()
if __name__=='__main__':
data = readAdwordsExport('export.csv')
print 'Campaigns:'
print data.keys()
writeAdwordsExport(data, 'output.csv') |
#!/usr/bin/env python
# coding=utf-8
# (C) 2009 HalOtis Marketing
# written by Matt Warren
# http://halotis.com/
"""
read and write exported campaigns from Adwords Editor
"""
import codecs
import csv
FIELDS = ['Campaign', 'Campaign Daily Budget', 'Languages', 'Geo Targeting', 'Ad Group', 'Max CPC', 'Max Content CPC', 'Placement Max CPC', 'Max CPM', 'Max CPA', 'Keyword', 'Keyword Type', 'First Page CPC', 'Quality Score', 'Headline', 'Description Line 1', 'Description Line 2', 'Display URL', 'Destination URL', 'Campaign Status', 'AdGroup Status', 'Creative Status', 'Keyword Status', 'Suggested Changes', 'Comment', 'Impressions', 'Clicks', 'CTR', 'Avg CPC', 'Avg CPM', 'Cost', 'Avg Position', 'Conversions (1-per-click)', 'Conversion Rate (1-per-click)', 'Cost/Conversion (1-per-click)', 'Conversions (many-per-click)', 'Conversion Rate (many-per-click)', 'Cost/Conversion (many-per-click)']
def readAdwordsExport(filename):
campaigns = {}
f = codecs.open(filename, 'r', 'utf-16')
reader = csv.DictReader(f, delimiter='\t')
for row in reader:
#remove empty values from dict
row = dict((i, j) for i, j in row.items() if j!='' and j != None)
if row.has_key('Campaign Daily Budget'): # campain level settings
campaigns[row['Campaign']] = {}
for k,v in row.items():
campaigns[row['Campaign']][k] = v
if row.has_key('Max Content CPC'): # AdGroup level settings
if not campaigns[row['Campaign']].has_key('Ad Groups'):
campaigns[row['Campaign']]['Ad Groups'] = {}
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']] = row
if row.has_key('Keyword'): # keyword level settings
if not campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']].has_key('keywords'):
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['keywords'] = []
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['keywords'].append(row)
if row.has_key('Headline'): # ad level settings
if not campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']].has_key('ads'):
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['ads'] = []
campaigns[row['Campaign']]['Ad Groups'][row['Ad Group']]['ads'].append(row)
return campaigns
def writeAdwordsExport(data, filename):
f = codecs.open(filename, 'w', 'utf-16')
writer = csv.DictWriter(f, FIELDS, delimiter='\t')
writer.writerow(dict(zip(FIELDS, FIELDS)))
for campaign, d in data.items():
writer.writerow(dict((i,j) for i, j in d.items() if i != 'Ad Groups'))
for adgroup, ag in d['Ad Groups'].items():
writer.writerow(dict((i,j) for i, j in ag.items() if i != 'keywords' and i != 'ads'))
for keyword in ag['keywords']:
writer.writerow(keyword)
for ad in ag['ads']:
writer.writerow(ad)
f.close()
if __name__=='__main__':
data = readAdwordsExport('export.csv')
print 'Campaigns:'
print data.keys()
writeAdwordsExport(data, 'output.csv')
This code is available in my public repository: http://bitbucket.org/halotis/halotis-collection/
Technorati Tags: adwords editor, csv, google, ppc, Python
Someone asked me to make the source code available in a repository somewhere. One place to grab all the scripts I’ve shared on this blog. Well I’m starting to make them available on 
Here’s a simple web crawling script that will go from one url and find all the pages it links to up to a pre-defined depth. Web crawling is of course the lowest level tool used by Google to create its multi-billion dollar business. You may not be able to compete with Google’s search technology but being able to crawl your own sites, or that of your competitors can be very valuable.
Ok, so this isn’t my script but it’s a much nicer version of