How can I add Conversion Data to this Script

[mark_uk]

Hi

I am using this scrip to report on the top level Impression/Click performance. Is it possible to add converions (both types), conv rate and cost per conversion?

https://developers.google.com/adwords/scripts/docs/solutions/keyword-performance

I am not in anyway a developer so please go easy on me.

Cheers

Mark

[Alexander Wang]

Certainly Mark.

The first thing you'd want to do is manually update the spreadsheet's "Report" sheet. This is the sheet that contains all of the data. The other sheets are optional and contain charts built off of this data. The simplest way to include conversions is to manually add a column for each new stat you want to track. Four new stats means four new columns to the right of "Cost".

If you can get by with using converted clicks and click conversion rate (you can manually calculate cost per converted click), you can update the "outputQualityScoreData" function with the following bolded changes:

function outputQualityScoreData(sheet) {
  // Output header row
  var header = [
    'Quality Score',
    'Num Keywords',
    'Impressions',
    'Clicks',
    'CTR (%)',
    'Cost',
    'Converted Clicks',
    'Click Conversion Rate',
    'Cost per Converted Click'
  ];
  sheet.getRange(3, 2, 1, 6).setValues([header]);


  // Initialize
  var qualityScoreMap = [];
  for (i = 1; i <= 10; i++) {
    qualityScoreMap[i] = {
      numKeywords: 0,
      totalImpressions: 0,
      totalClicks: 0,
      totalCost: 0.0,
      totalConvertedClicks: 0,
    };
  }


  // Compute data
  var keywordIterator = AdWordsApp.keywords()
      .forDateRange('LAST_WEEK')
      .withCondition('Impressions > 0')
      .get();
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    var data = qualityScoreMap[keyword.getQualityScore()];
    if (data) {
      data.numKeywords++;
      data.totalImpressions += stats.getImpressions();
      data.totalClicks += stats.getClicks();
      data.totalCost += stats.getCost();
      data.totalConvertedClicks += stats.getConvertedClicks();
    }
  }


  // Output data to spreadsheet
  var rows = [];
  for (var key in qualityScoreMap) {
    var ctr = 0;
    var cost = 0.0;
    var clickConversionRate = 0.0;
    var costPerConvertedClick = 0.0;
    if (qualityScoreMap[key].numKeywords > 0) {
      ctr = (qualityScoreMap[key].totalClicks /
        qualityScoreMap[key].totalImpressions) * 100;
      if (qualityScoreMap[key].totalClicks > 0) {
        clickConversionRate = (qualityScoreMap[key].totalConvertedClicks /
            qualityScoreMap[key].totalClicks) * 100;
      }
      if (qualityScoreMap[key].totalConvertedClicks > 0) {
        costPerConvertedClick = qualityScoreMap[key].totalCost /
            qualityScoreMap[key].totalConvertedClicks;
      }
    }
    var row = [
      key,
      qualityScoreMap[key].numKeywords,
      qualityScoreMap[key].totalImpressions,
      qualityScoreMap[key].totalClicks,
      ctr.toFixed(2),
      qualityScoreMap[key].totalCost,
      qualityScoreMap[key].totalConvertedClicks,
      clickConversionRate,
      costPerConvertedClick
      ];
    rows.push(row);
  }
  sheet.getRange(4, 2, rows.length, 6).setValues(rows);
}

You would also make the exact same changes to the "outputPositionData" function except use "positionMap" instead of "qualityScoreMap".

If this is not enough data, you'd need to update the script to make use of reports to download the keyword performance report instead. We only support certain stats directly. If you want other stats, you're only other option in scripts is to fetch it via a report. For example, the keyword performance report also supports "ConversionsManyPerClick", "ConversionValue", "ConversionSignificance", etc. Most of the script would stay the same, but instead of using a keyword iterator in the "Compute data" section, you'd fetch a report and iterate over its rows:

// Compute data
var report = AdWordsApp.report(
    "SELECT Id, QualityScore, Impressions, Clicks, Cost, Conversions, ConversionsManyPerClick" +
    "FROM KEYWORD_PERFORMANCE_REPORT " +
    "WHERE Impressions > 0 " +
    "DURING LAST_WEEK");
var rows = report.rows();
while (rows.hasNext()) {
  var row = rows.next();
  var data = qualityScoreMap[row["QualityScore"]];
  if (data) {
    data.numKeywords++;
    data.totalImpressions += row["Impressions"];
    data.totalClicks += row["Clicks"];
    data.totalCost += row["Cost"];
    data.totalConvertedClicks += row["Conversions"];
  }
}

If you have time and are willing, I encourage you to take a look at codecademy's javascript tutorial:

http://www.codecademy.com/en/tracks/javascript

Using AdWords scripts doesn't require an advanced level of javascript knowledge. Having even a basic understanding would make it easy to make tweaks to existing scripts and even write brand new scripts.

Cheers,

Alex