License New BSD license
Lines 44
Keywords
AdSense (2) csv (2)
Permissions
Owner: Stou S.
Viewable by Everyone
Editable by All Siafoo Users

Import AdSense reports into a database Atom Feed 0

In Brief Since the Advanced Reports in AdSense aren't very useful this script will create the schema and import your CSV data into an actual database (or MySQL if you like... oh snap!) so you can do some analysis.... more
# 's
 1import csv
2from datetime import datetime
3from sqlalchemy import create_engine, Table, Column, Integer, Text, MetaData, ForeignKey, Date, Float, Integer
4from sqlalchemy.orm import mapper, sessionmaker
5
6
7filename = '/tmp/report.csv'
8engine = create_engine('postgres://postgres@localhost/sample_db')
9
10metadata = MetaData()
11adsense_table = Table('adsense', metadata,
12 Column('id', Integer, primary_key=True),
13 Column('date', Date),
14 Column('channel', Text),
15 Column('impressions', Integer),
16 Column('clicks', Integer),
17 Column('ctr', Float),
18 Column('ecpm', Float),
19 Column('earnings', Float)
20 )
21
22class AdSense(object):
23 def __init__(self, row):
24 self.date = datetime.strptime(row[0], '%m/%d/%y')
25 self.channel = row[1]
26 self.impressions = int(row[2])
27 self.clicks = int(row[3])
28 self.ctr = float(row[4].strip('%'))
29 self.ecpm = float(row[5])
30 self.earnings = float(row[6])
31
32metadata.create_all(engine)
33mapper(AdSense, adsense_table)
34
35Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
36session = Session()
37
38reader = csv.reader(open(filename), delimiter='\t')
39
40count = 0
41
42for r in reader:
43 try:
44 a = AdSense(r)
45 session.save(a)
46
47 count += 1
48 except ValueError:
49 print 'Skipping invalid row', r
50
51 session.commit()
52
53print 'Loaded %i rows' % count

Since the Advanced Reports in AdSense aren't very useful this script will create the schema and import your CSV data into an actual database (or MySQL if you like... oh snap!) so you can do some analysis.

To use go into AdSense 'Advanced Reports' then select 'Show->Channel Data' and set "Group By" to 'Both'. Click on 'Display Report' then the 'CSV' link and save the report somewhere. Modify the script to your environment by changing the filename to point to your saved report, and the database string to reflect your database setup.

Note

SQLAlchemy is required.

Warning

Your CSV file might be UTF-16. You will have to convert it to UTF-8 or ASCII with iconv -f UTF-16 -t UTF-8 in.csv > out.csv (or whatever other tool you like)