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') |
This code is available in my public repository: http://bitbucket.org/halotis/halotis-collection/