#!/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') |