Home › Adwords › Scripts

Quick summary of content network clicks/cost/conversions

28 July, 2009 - 18:33 — martinjevans

I use the content network sparingly and in campaigns which are for the content network only. However, I've never felt google chooses the sites to display my ads very well so review the sites regularly. The things I'm mostly interested in are a) which sites my ads appeared on I don't think they should (or I don't want them appearing on) and b) which sites are costing a lot but delivering no conversions. The following perl script processes a TSV version of the placement report (with specific fields) into a more easily managed list I can use to add domain exceptions to my campaign.

#!/usr/bin/perl
# $Id: contentnw.pl 76 2009-07-28 07:17:15Z martin $
# (c) Martin J. Evans
#
# This script takes on stdin the report generated by google adwords placement
# report where the format is TSV (tab separated fields) containing the fields
# in this order:
#
# Ad Group, Domain, URL, Special Category, Impressions, Clicks, CTR,
# Avg CPC, Avg CPM, Cost, Conversions (1-per-click), Conv. Rate (1-per-click)
# Const/Conv (1-per-click)
#
# and produces output showing
#
# domain, impressions, clicks, cost, conversions, cost per conversion
#
# summed per domain and headed by totals.
#
# You can easily generate the google report from google adwords reporting.
#
use strict;
use Data::Dumper;

my $ignored_headings;           # ignore headings in TSV

my %domains = ();               # keyed per domain

while (<>) {
    my @columns = split("\t", $_);
    # ignore rows which don't have 13 columns as they are not expected data
    next if scalar(@columns) != 13;
    if (!$ignored_headings) {
        # skip headings
        $ignored_headings++;
        next;
    }
    my ($domain, $impressions, $clicks, $cost, $conversions) =
        ($columns[1], $columns[4], $columns[5], $columns[9], $columns[10]);
    $cost =~ s/£//g;
    #print "$domain, $impressions, $clicks, $cost, $conversions\n";
    if (!exists($domains{$domain})) {
        $domains{$domain} = {
            impressions => $impressions,
            clicks => $clicks,
            cost => $cost,
            conversions => $conversions};
    } else {
        # seen domain before - add values to existent ones to sum per domain
        my $d = $domains{$domain};
        $d->{impressions} += $impressions;
        $d->{clicks} += $clicks;
        $d->{cost} += $cost;
        $d->{conversions} += $conversions;
    }
}

#print Dumper(\%domains);
foreach (keys %domains ) {
    my $d = $domains{$_};
    eval {
        print sprintf "%-30s %5d %5d £%-.2f %5d £%-.2f\n",
            $_, $d->{impressions}, $d->{clicks}, $d->{cost},
                $d->{conversions},
                    ($d->{conversions} ? $d->{cost}/$d->{conversions} : 0) ;
    };
    if ($@) {
        print Dumper($d);
        die "Failed on key $_";
        exit 0;
    }
}

The output is like this:

domain impressions clicks cost conversions cost per click
  118316 676 £45.11 38 £1.19
aaaa.com 1 1 £0.04 0 £0.00
bbb.com 1 1 £0.09 0 £0.00
ccc.com 100 39 £2.95 3 £0.98

Trackback URL for this post:

http://www.martin-evans.me.uk/trackback/32