Export cost data to spreadsheet

[Mads T.]

Hi,

I am new to Adwords Scripts and the answer to my question might therefore be straight forward. 

What i'm trying to do is export daily cost data into (the same) spreadsheet, so that I can compare actual cost with the budget.

This is as far as i can get in the script.

function main() {

  // Get all campaigns

  var campaignsIterator = AdWordsApp.campaigns()

      .get();

  

  var cost = 0;

  var impressions = 0;

  var clicks = 0;

  var conversions = 0;

  while (campaignsIterator.hasNext()) {

    var campaign = campaignsIterator.next();

    var Startdate = "20130101"

    var Enddate = "20131231"

    var stats = campaign.getStatsFor(Startdate, Enddate)

    cost += stats.getCost();

    impressions += stats.getImpressions();

    clicks += stats.getClicks();

    conversions += stats.getConversions();

  }

    Logger.log("Cost " + cost);

    Logger.log("Impressions " + impressions);

    Logger.log("Clicks " + clicks);

    Logger.log("Conversions " + conversions);

}

As you can se I have added impressions, clicks and conversions data too. 

This is an example of how the data could be exported to the spreadsheet:

Date              Cost         Impressions           Clicks          Conversions

20130101       500          10000                    500              25

20130102       503          10700                    501              20

etc.

Can somebody help with the code that devides the data into days and exports it into google docs?

BR

Mads

[Martin Roettgerding]

Hi,
Basically you would have to iterate through your campaigns and inside that, iterate through all the dates you want. You could do something like this:

while (campaignsIterator.hasNext()) {
   var campaign = campaignsIterator.next();

   var month = 12;
   var year = 2012;
   var day = 1;
   
   for(i = 0; i < number_of_days_you_want; i++){
      date = new Date(year, month, day + i); // should work: Dec 46 2012 gets you Jan 15 2013
      // use the date functions to make the string for startdate (=enddate); make sure to watch out for leading zeros
      ...
   }
}

And then go on and fetch the stats. As far as summing up and putting them into a spreadsheet goes, check out this thread: https://groups.google.com/forum/#!topic/adwords-scripts/Pf3BZVhbGUQ

HTH
Martin

[Mads T.]

Hi,

Thanks for helping out. I have tried all kinds of crazy ways to get it working, but i haven't succeeded yet. Maybe you could give mee some more assistance? My latest version is looking like this and it want work out:

function main() {

  // Get all campaigns

  var campaignsIterator = AdWordsApp.campaigns()

      .get();

  

  var cost = 0;

  var impressions = 0;

  var clicks = 0;

  var conversions = 0;

  while (campaignsIterator.hasNext()) {

    var campaign = campaignsIterator.next();

   var month = 12;

   var year = 2012;

   var day = 1;

   var startdate = new Date(20130101)

   var enddate = new Date(20130131)

   

   for(i = 0; i < 30; i++){

      date = new Date(year, month, day + i);

    var stats = campaign.getStatsFor(startdate,enddate)

    cost += stats.getCost();

    impressions += stats.getImpressions();

    clicks += stats.getClicks();

    conversions += stats.getConversions();

  }

    Logger.log("Cost " + cost);

    Logger.log("Impressions " + impressions);

    Logger.log("Clicks " + clicks);

    Logger.log("Conversions " + conversions);

}

}

BR

Mads

[Jelle van der Zwaag]

Hi Mads,

It is not so difficult to do. But first: do you want a report that gets generated daily? Or do you want the costs of every day the last year in on sheet?

-Jelle

PS: I notice you forgot an ';' a few times at the end of the lines.

[Mads T.]

Hi Jelle,

I would like the spreadsheet to be updated on a daily basis.

Thanks.

Mads

[Jelle van der Zwaag]

Hi Mads,

Please try this code first - if it works. And if not: post the errors here.

function main() {
 // Get all campaigns
 var campaignsIterator = AdWordsApp.campaigns().get();
 
 var cost = 0;
 var impressions = 0;
 var clicks = 0;
 var conversions = 0;

 while (campaignsIterator.hasNext()) {
   var campaign = campaignsIterator.next();

  var stats = campaign.getStatsFor("YESTERDAY");

cost += stats.getCost();
   impressions += stats.getImpressions();
   clicks += stats.getClicks();
   conversions += stats.getConversions();
 }
   Logger.log("Cost " + cost);
   Logger.log("Impressions " + impressions);
   Logger.log("Clicks " + clicks);
   Logger.log("Conversions " + conversions);

}

Pixum - Immer die schönsten Bilder

-----------------------------------------------------------------------------------------------------------
Pixum ist eine geschützte Marke der
Diginet GmbH & Co. KG - Industriestr.161 - 50999 Köln

Fon: +49 (2236) 886-0 - Fax: +49 (2236) 88 66 99

Sitz Köln, HRA 25531, Umsatzsteuer-ID: DE-209867661
Komplementärin: Diginet Management GmbH, Sitz Köln, HRB 69766  
Geschäftsführer: Daniel Attallah, Oliver Thomsen
------------------------------------------------------------------------------------------------------------
www.pixum.de  .at .be .ch .com .uk .dk .es .eu .fr .nl .no .pl ...

[Федорчук Юрий]

Hi, Jelle van der Zwaag

Thank you for very important script.

Please tell me, how to import all result data into an existing spreadsheet?

[Jelle van der Zwaag]

Hi,

First: You need to make a new Google Spreadsheet and copy the key-parameter of the url of this Spreadsheet. In the script you need insert this id at <insert id here>.

var today = new Date();

var todayNum = today.getDate();

var yesterday = new Date();

yesterday.setDate( today.getDate() - 1 );

var yesterdayStr = Utilities.formatDate( yesterday, "CET", "dd.MM.yyyy" );

function main() {

 // Get all campaigns

 var campaignsIterator = AdWordsApp.campaigns().get();

 

 var cost = 0;

 var impressions = 0;

 var clicks = 0;

 var conversions = 0;

 while (campaignsIterator.hasNext()) {

   var campaign = campaignsIterator.next();

  var stats = campaign.getStatsFor("YESTERDAY");

cost += stats.getCost();

   impressions += stats.getImpressions();

   clicks += stats.getClicks();

   conversions += stats.getConversions();

   

   

 }

 var spreadsheet = SpreadsheetApp.openById("<insert id here>");

var sheet = spreadsheet.getActiveSheet();

sheet.getRange( 2, 1, 1, 1 ).setValue( "Cost" );

     sheet.getRange( 3, 1, 1, 1 ).setValue( "Impressions" );

     sheet.getRange( 4, 1, 1, 1 ).setValue( "Clicks" );

     sheet.getRange( 5, 1, 1, 1 ).setValue( "Conversions" );

 

sheet.insertColumnAfter(1);

sheet.getRange( 1, 2, 1, 1 ).setValue( yesterdayStr );

sheet.getRange( 2, 2, 1, 1 ).setValue( cost );

sheet.getRange( 3, 2, 1, 1 ).setValue( impressions );

     sheet.getRange( 4, 2, 1, 1 ).setValue( clicks );

     sheet.getRange( 5, 2, 1, 1 ).setValue( conversions );

 

 

   Logger.log("Cost " + cost);

   Logger.log("Impressions " + impressions);

   Logger.log("Clicks " + clicks);

   Logger.log("Conversions " + conversions);

}

This should work. Just run it every day at 8AM and you will get the data in the sheet.

Kind regards,

Jelle

[Федорчук Юрий]

Thanks a lot. It's really work!

[Mads T.]

Hi Jelle,

Thanks for your reply. We are getting close to what i'm looking for. :-)

The script you have made depends on that your running it every day at 8 AM. It would be nice if you could loop your way through the dates, just like i tried to do in the script above and with a output close to this:

Date              Cost         Impressions           Clicks          Conversions

20130101       500          10000                    500              25

20130102       503          10700                    501              20

Is that possible?

BR

Mads

[Jelle van der Zwaag]

Hi Mads,

I played around and this should work. The variable numDays says how far in the past you want to go. But it is always from today. Good luck with it.

var numDays = 5;


/* Stop editing */

function main() {

for ( i = 1; i <= numDays; i++ ) {

// Get all campaigns

var campaignsIterator = AdWordsApp.campaigns().get();

var cost = 0;

var impressions = 0;

var clicks = 0;

var conversions = 0;

while (campaignsIterator.hasNext()) {

var campaign = campaignsIterator.next();

var stats = campaign.getStatsFor( getDayFromToday( i ), getDayFromToday( i ) );

cost += stats.getCost();

impressions += stats.getImpressions();

clicks += stats.getClicks();

conversions += stats.getConversions();

}

var spreadsheet = SpreadsheetApp.openById("<inser id here>");

var sheet = spreadsheet.getActiveSheet();

sheet.getRange( 1, 2).setValue( "Cost" );

sheet.getRange( 1, 3 ).setValue( "Impressions" );

sheet.getRange( 1, 4 ).setValue( "Clicks" );

sheet.getRange( 1, 5 ).setValue( "Conversions" );

 

sheet.insertRowAfter(1)

sheet.getRange( 2, 1 ).setValue( getDayFromToday( i ) );

sheet.getRange( 2, 2 ).setValue( cost );

sheet.getRange( 2, 3 ).setValue( impressions );

sheet.getRange( 2, 4 ).setValue( clicks );

sheet.getRange( 2, 5 ).setValue( conversions );

 

Logger.log("Cost " + cost);

Logger.log("Impressions " + impressions);

Logger.log("Clicks " + clicks);

Logger.log("Conversions " + conversions);

}

}

function getDayFromToday( i ) {

var today = new Date(  );

var yesterday = new Date(  );

yesterday.setDate( today.getDate( ) - i );

var yesterdaysMonth = yesterday.getMonth(); // Jan = 0, is used to get month out of months-array + to calculate daysInMonthThisYear

var yesterdayString = Utilities.formatDate( yesterday, "CET", "dd" );

var yesterdayInt = parseInt( yesterdayString, 10 );

var yesterdaysMonthString = Utilities.formatDate( yesterday, "CET", "MM" );

var thisYear = yesterday.getFullYear();

var thisYearString = thisYear.toString();

/* get the date ranges */

var dateFirstDayMonth = thisYearString + yesterdaysMonthString + "01";

var dateYesterday = thisYearString + yesterdaysMonthString + yesterdayString;

return dateYesterday;

 }

[Jelle van der Zwaag]

Hi Mads,

I made the function function getDayFromToday() much much simpler and easier to understand. Please use the code below.

var numDays = 5;

function main() {

for ( i = 1; i <= numDays; i++ ) {

// Get all campaigns

var campaignsIterator = AdWordsApp.campaigns().get();

var cost = 0;

var impressions = 0;

var clicks = 0;

var conversions = 0;

while (campaignsIterator.hasNext()) {

var campaign = campaignsIterator.next();

var stats = campaign.getStatsFor( getDayFromToday( i ), getDayFromToday( i ) );

cost += stats.getCost();

impressions += stats.getImpressions();

clicks += stats.getClicks();

conversions += stats.getConversions();

}

var spreadsheet = SpreadsheetApp.openById("0AjxLaNR5hTIsdFJsOEl5M2lkUzhlNlRKZ3VuOUJqRlE");

var sheet = spreadsheet.getActiveSheet();

sheet.getRange( 1, 2).setValue( "Cost" );

sheet.getRange( 1, 3 ).setValue( "Impressions" );

sheet.getRange( 1, 4 ).setValue( "Clicks" );

sheet.getRange( 1, 5 ).setValue( "Conversions" );

 

sheet.insertRowAfter(1)

sheet.getRange( 2, 1 ).setValue( getDayFromToday( i ) );

sheet.getRange( 2, 2 ).setValue( cost );

sheet.getRange( 2, 3 ).setValue( impressions );

sheet.getRange( 2, 4 ).setValue( clicks );

sheet.getRange( 2, 5 ).setValue( conversions );

 

Logger.log("Cost " + cost);

Logger.log("Impressions " + impressions);

Logger.log("Clicks " + clicks);

Logger.log("Conversions " + conversions);

}

}

function getDayFromToday( days ) {

var today = new Date(  );

var otherDay = new Date(  );

otherDay.setDate( otherDay.getDate( ) - days );

var dateotherDay = otherDay.getFullYear().toString() + Utilities.formatDate( otherDay, "CET", "MM" ) + Utilities.formatDate( otherDay, "CET", "dd" );

return dateotherDay;

 }

[Mads T.]

Hi Jelle,

 

You are a genius!!

 

Thank you very much!

 

BR

 

Mads

[Jelle van der Zwaag]

Hi Mads,

Thanks. Made my day. :)

Glad I could help.

[Ankit Singh]

If I have to run it at some other time (say 10 AM IST) will there be any problem in the output.

[Ekaterina Sokolova]

Hey everyone, just came across your group. I don't really have any experience with Javascript (this is the language that AdWords is using, right?), and I am trying to play around with different scripts to accomplish my goals. I found Jelle van der Zwaag's script very useful. I am trying to figure out cost for specific campaigns (they are all named appropriately). Does anyone know what variable I need to add to this script to make it work?

function main() {

  var today = new Date();

var todayNum = today.getDate();

var yesterday = new Date();

yesterday.setDate( today.getDate() - 1 );

var yesterdayStr = Utilities.formatDate( yesterday, "CET", "MM.dd.yyyy" );

 // Get all campaigns

 var campaignsIterator = AdWordsApp.campaigns().get();

 

 var cost = 0;

 var impressions = 0;

 var clicks = 0;

 var conversions = 0;

 while (campaignsIterator.hasNext()) {

   var campaign = campaignsIterator.next();

  var stats = campaign.getStatsFor("YESTERDAY");

cost += stats.getCost();

   impressions += stats.getImpressions();

   clicks += stats.getClicks();

   conversions += stats.getConversions();

   

   

 }

 var spreadsheet = SpreadsheetApp.openById("<insert id here>");

var sheet = spreadsheet.getActiveSheet();

sheet.getRange( 2, 1, 1, 1 ).setValue( "Cost" );

     sheet.getRange( 3, 1, 1, 1 ).setValue( "Impressions" );

     sheet.getRange( 4, 1, 1, 1 ).setValue( "Clicks" );

     sheet.getRange( 5, 1, 1, 1 ).setValue( "Conversions" );

 

sheet.insertColumnAfter(1);

sheet.getRange( 1, 2, 1, 1 ).setValue( yesterdayStr );

sheet.getRange( 2, 2, 1, 1 ).setValue( cost );

sheet.getRange( 3, 2, 1, 1 ).setValue( impressions );

     sheet.getRange( 4, 2, 1, 1 ).setValue( clicks );

     sheet.getRange( 5, 2, 1, 1 ).setValue( conversions );