How to filter by CampaignName when using the ACCOUNT_PERFORMANCE_REPORT.

[MAP]

Hi Everyone,

I'm working on a script to automate monthly reporting of a large account (150+ active campaigns).

The script works except that we can't use the WHERE clause we intended (CampaignName CONTAINS) because we use the ACCOUNT_PERFORMANCE_REPORT and we get the expected error:

"Column 'CampaignName' is not valid for report type ACCOUNT_PERFORMANCE_REPORT. Double-check your SELECT clause. (line 144)"

Here is the code that represents what we want to achieve:

var fields = {
            "Clicks":"Clicks", "Impr.":"Impressions", "CTR":"Ctr", "Avg. CPC":"AverageCpc", "Cost":"Cost",
            "Converted clicks":"Conversions", "CPA":"CostPerConversion", "CR":"ConversionRate", "View-through conv.":"ViewThroughConversions",
            "Conversions":"ConversionsManyPerClick"
        };            

var awql = "SELECT " + fieldsValues.join(", ") + " " +
                "FROM ACCOUNT_PERFORMANCE_REPORT " +
                "WHERE CampaignName CONTAINS " + SETTINGS[currentActiveSheet.getName()] + " " +
                "DURING " + dateRanges[i];

Each dateRange represents a month.

We need to use a CampaignName filter because we need to pull out only the information for specific campaigns (identified by name) and get one row per awql request. Each AWQL request feeds different data to each sheet depending on what was the campaignName that was searched.

I know we can use other Report Types that have the CampaignName field (like the CAMPAIGN_PERFORMANCE_REPORT) but this report type gives us multiple rows per awql request, with information specific to each campaign but we need to get only one row. For example, for each month, get data for all campaigns that have "USA" in their name and put the single row obtained into the "USA" sheet. Then pull data for all campaigns that have "France" in their name and put the single row obtained into the "France" sheet., etc.

Is there a way to do this? For example is it possible to use a campaignName filter after the first AWQL request to request the wanted data? I think it might be possible to use the campaign selector but don't know what would be the best way to combine these 2 filtering methods (if it's possible at all).

Thank you very much in advance,

Marc.

[Matt Greenland]

Hi Marc,

I don't think there's any way to do what you want to do with just one report query. The way the WHERE clause acts is more like filtering rows out of a report than affecting the data used for a report -- so it doesn't make sense for the account report to have a filter on campaign names.

I think your best bet here is to aggregate the data into one total row, and put it into the spreadsheet yourself. The code might look something like this:

var report = AdWordsApp.report('select ' + fieldsValues.join(', ') + ' from CAMPAIGN_PERFORMANCE_REPORT ' +
    'where CampaignName contains ' + SETTINGS[currentActiveSheet.getName()] + ' during ' + dateRanges[i]);
var rows = report.rows();

// A map from field name to total value
var total = {};

// Sum up all the matching report rows
while (rows.hasNext()) {
  var row = rows.next();
  for (var i = 0; i < fieldsValues.length; i++) {
    var field = fieldsValues[i];
    totals[field] = (totals[field] || 0) + parseFloat(row[field]);
  }
}

// Convert the "total" map into a list with the same order as the list of fields
var totalRow = [];
for (var i = 0; i < fieldsValues.length; i++) {
  var field = fieldsValues[i];
  totalRow.push(totals[field]);
}

// Push the data into the sheet -- clear any existing contents, append
// the field names as a header row, and then append the total stats row.
currentActiveSheet.clear();
currentActiveSheet.appendRow(fieldsValues);
currentActiveSheet.appendRow(totalRow);

Hopefully this helps.

Thanks

Matt

[MAP]

Hi Matt,

Thank you very much for the solution. I was also thinking about adding the fields values and then pasting the totals into the spreadsheet but I was thinking that there was a simpler or more direct workaround (for example using labels, the campaign selector or something else).

I was thinking that maybe it's possible to do the following:

For example, first we extract the data only from the campaigns we want to work with (by using Labels, the CAMPAIGN_PERFORMANCE_REPORT or the campaignSelector). So in this way we obtain a dataset we can work with and apply our second filter. Then we use the ACCOUNT_PERFORMANCE_REPORT to extract the data only from the data filtered on step 1. Is it possible to do that (kind of like using 2 awql queries to create a single report)?

From what I've seen it's not possible to select a specific dataset from where to extract the data with an AWQL query but we'd appreciate if we could have a confirmation about this.

Thanks.

Marc.

[Matt Greenland]

Hi Marc,

Yes, to the best of my knowledge, something like that isn't possible via scripts right now, since we don't get summary/total rows as part of our reports. I'll see if that's something we can do any time soon, but for now I think you'll have to add the data up yourself.

(Sorry for the late reply)