MCC Adwords Script by campaign and date

Question

Good Afternoon Everyone

I am in need of developing an adwords script. This will be the first custom script that i have embarked on. All previous scripts have been example scripts from google.

I have an MCC account which contains a number of client accounts. I want a script that runs for each account within the MCC, and queries data for each day in the month. I then want this to be added to a google spreadsheet.

I have written an example of what i would like to happen, but need to pointers on how to get there

Get List of client accounts
Select Account
List item
Select 1st day in month
List item
generate report create a google document, creating a sheet for each day
select next day. Stop loop if last day in month
select next account

I have been performing the above manually for a while now and its starting to get tedious. I also find that the conversions within analytics don't filter down for a couple of days so its replying on me processing a number of ystems to pull the data in.

If i get to the point where the script is working then i will schedule it to run either on the last day of the month or the first day of the following month

Any help would be greatly appreciated

*****UPDATE*****

i have developed the following code but i am having some problem with the output.

I am expecting a google spreadsheet with - a sheet for each day of the month - a list of all campaigns, of all of the child account i have in my mcc - stats relating to each campaign for the relevant day in the month

what i am getting is

a sheet for each day of the month
the first sheet only is populated with the data

the rest of the sheets within the spreadsheet only contain the column headings

var row = 2;
var reportMonth;
var reportYear;
var reportDays;
var reportMonthName;

function main() { 
  var mccAccount = AdWordsApp.currentAccount();
  var childAccounts = MccApp.accounts().get();

  reportMonth = getPreviousMonth();
   Logger.log("Report Month" + reportMonth);

  reportYear = new Date().getFullYear()

  if (reportMonth = 12) {
    reportYear = reportYear - 1;
  }

  Logger.log("Report Year" + reportYear);

  reportDays = daysInMonth(reportMonth,reportYear);
  Logger.log("Report Days" + reportDays);
  reportMonthName = monthNumberToName(reportMonth);
  Logger.log("Month Name" + reportMonthName);

  // Create a new spreadsheet (will have private access for you only).
var reportName = "Weekly QS Report – " + reportMonthName;
var spreadsheet = SpreadsheetApp.create(reportName);

  for (i=1; i < (reportDays+1);i++) {
var reportDay;

if (i < 10) {


reportDay = "" + "0" + i;

}
else
{
reportDay = i;  
}


var reportDate = "" + reportYear + reportMonth + reportDay;


Logger.log("Create Sheet" + reportDate);
var sheet = spreadsheet.insertSheet()
sheet.setName(i)

// Write header row.
sheet.getRange("A1").setValue("Account");
sheet.getRange("B1").setValue("Campaign");
sheet.getRange("C1").setValue("Impressions");
sheet.getRange("D1").setValue("Clicks");
sheet.getRange("E1").setValue("Cost");



while (childAccounts.hasNext()) {

  var childAccount = childAccounts.next();

  MccApp.select(childAccount);

  // Select campaigns under the client account
  var campaignIterator = AdWordsApp.campaigns().get();

  // Write body of report.
  while (campaignIterator.hasNext()) {

    var campaign = campaignIterator.next();

    Logger.log("Report Date" + reportDate);

    var stats = campaign.getStatsFor(reportDate,reportDate);


    sheet.getRange("A" + row).setValue(childAccount.getName());
    sheet.getRange("B" + row).setValue(campaign.getName());
    sheet.getRange("C" + row).setValue(stats.getImpressions());
    sheet.getRange("D" + row).setValue(stats.getClicks());
    sheet.getRange("E" + row).setValue(stats.getCost()); 

    row ++;
  }
}

  }

MccApp.select(mccAccount);          
}

function getDateInfo() {
  var premonth = getPreviousMonth();
  var dayIn = daysInMonth(premonth,year);



}

function daysInMonth(month,year) {
    return new Date(year, month, 0).getDate();
}

function getPreviousMonth() {
    var d = new Date();
    var n = d.getMonth();
    n = n - 1;

  if (n = -1) {
   n = 12;
  }
  return n;
}

function monthNumberToName (monthnum) {

  var months = [];
 months[1] = 'january';
 months[2] = 'february';
 months[3] = 'march';
 months[4] = 'april';
 months[5] = 'may';
 months[6] = 'june';
 months[7] = 'july';
 months[8] = 'august';
 months[9] = 'september';
 months[10] = 'october';
 months[11] = 'november';
 months[12] = 'december';

  var myMonthName = months[monthnum];

  return myMonthName;

};

Any help would be appreciated, thanks in advance guys

Ashutosh Katoch Ashutosh Katoch · Accepted Answer · 2015-01-23T09:20:18

It would have been nice if you would have shown the research that you've done (If any). However, I assume that you have basic programming language. So you can check out the following links for reference, build up on your knowledge and then on your code.

Mcc Account Details and other actions:

1) https://developers.google.com/adwords/scripts/docs/reference/mccapp/mccapp

Sheet Manipulation and moving items to the sheet:

1) https://developers.google.com/apps-script/reference/spreadsheet/

2) https://developers.google.com/apps-script/reference/spreadsheet/sheet

To select next day you should start the loop with the JavaScript Date object and end with suitable condition.

MCC Adwords Script by campaign and date

2 Answers