AdWords Script to Display the Full GDN URL’s

Google Sheet That Shows Last 30 Days of Your GDN Pages

Most PPC managers have at least dabbled with Google’s Display Network. And while Google AdWords makes it easy to see the domains, the full URL’s are better downloaded into a spreadsheet to review. And personally, I like things easy.

So, the following script displays all of your account’s placements on the Google Display Network for the past 30 days that have at least 1 click on a Google Spreadsheet. This can be toggled to Clicks > 5, or Impressions > 100, etc.

The first step is to create your own Google Sheet. If you’ve never done it, it’s easy. Just google ‘Google Sheet’. Now click through and create one. Rename the starting sheet to ‘URL’, or the script will not run correctly.

The format of this script is initially written for ecommerce clients. So, if you’re account is not receiving revenue data, there are a couple tweaks to make it look better.

Change the ‘var columnRow’ line to the following:
var columnRow = [column1,column0,columnurl,column2,column3,column4,column6,column7,column8,column9,column13];

And change the ‘var tableRows’ line to this:
var tableRows = {campaign,adgroup,url,clicks,impressions,ctr,”$”+cpc,”$”+cost,conversions,”$”+costconv,convrate];

I’d love to hear the craziest things you find using this. Leave them in the comments below!

function main() {

var SPREADSHEET_URL = 'YOUR_GOOGLE_SHEET';
  
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var sheet = spreadsheet.getSheetByName('URL');

var column1 = "Campaign"
var column0 = "AdGroup"
var columnurl = "URL"
var column2 = "Clicks"
var column3 = "Impressions"
var column4 = "CTR"
var column5 = "Avg Pos"
var column6 = "Avg CPC"
var column7 = "Cost"
var column8 = "Conversions"
var column9 = "Cost/Conv"
var column10 = "Avg Order Value"
var column11 = "Revenue"
var column12 = "ROAS"
var column13 = "Conv Rate"


var columnRow = [column1,column0,columnurl,column2,column3,column4,column6,column7,column8,column9,column10,column11,column12,column13];
sheet.clear();
var cell = sheet.getRange("A1:Z1");
cell.setFontWeight("bold");
sheet.appendRow(columnRow);
  
var report = AdWordsApp.report(
   "SELECT CampaignName,AdGroupName,Url,Clicks,Ctr,ConversionValue,ConversionsManyPerClick,Cost,Impressions,ConversionRateManyPerClick,AverageCpc,ValuePerConversionManyPerClick,CostPerConversionManyPerClick" +
   " FROM URL_PERFORMANCE_REPORT " +
   " WHERE Clicks > 0 " +
   "DURING LAST_30_DAYS");
var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var campaign = row["CampaignName"];
    var adgroup = row["AdGroupName"];
    var url = row["Url"];
    var ctr = row["Ctr"];
    var clicks = row["Clicks"];
    var cpc = row["AverageCpc"];
    var cost = row["Cost"];
    var conversions = row["ConversionsManyPerClick"];
    var valueconv = row["ValuePerConversionManyPerClick"];
    var convvalue = row["ConversionValue"];
    var costconv = row["CostPerConversionManyPerClick"];
    var impressions = row["Impressions"];
    var convrate = row["ConversionRateManyPerClick"];
    var iconvvalue = parseInt(convvalue);
    var icost = parseInt(cost);
    if (icost < 0.01) {
    var roas = 0.00
    } else {
    var roas = iconvvalue/icost;
    }
    
    
var tableRows = [campaign,adgroup,url,clicks,impressions,ctr,"$"+cpc,"$"+cost,conversions,"$"+costconv,"$"+valueconv,"$"+convvalue,roas,convrate];
sheet.appendRow(tableRows); 
    

  }

  
};