How can I create a monthly report for the last 6 months?

[Arnaud Guissani]

Hi everyone,

I'm struggling with creating an AdWords report for the last 6 months.

For now, I have a code that gets data for each campaign for the last month and puts it into a Google Sheet.

But I'd also like to have the data for each 5 previous months in order to see the evolution. 

To get the last month's data I use this:

var stats = campaign.getStatsFor('LAST_MONTH');

I know that I could use getStatsFor(dateFrom, dateTo) but then it'd be very hard to automatize it since reports will be created every month.

Is there a way to get data for "The month before last month"...etc

Thanks in advance.

[Anash Oommen]

Hi Arnaud,

Date math is fairly straightforward in Javascript. E.g. here's how you calculate the day 6 months before. 

var dateOffset = (24*60*60*1000) * 180; //180 days
var myDate = new Date();
myDate.setTime(myDate.getTime() - dateOffset);

Cheers,
Anash P. Oommen,
AdWords Scripts Team.

[Arnaud Guissani]

Thanks for your answer Anash.

However, I might haven't been clear enough. What I'd like to have is the stats for every month for the last 6 months. 

For example, in October, I would get :

Stats for September 2014
Campaign's name | CTR | CPC
Campaign 1            1,28% 0,34€

 

Stats for August 2014
Campaign's name | CTR | CPC
Campaign 1            1,22% 0,40€
.
Stats for July 2014
Campaign's name | CTR | CPC
Campaign 1            1,23% 0,46€ 

 

Stats for June 2014

Campaign's name | CTR | CPC
Campaign 1            1,24% 0,39€
.
Stats for May 2014
Campaign's name | CTR | CPC
Campaign 1            1,54% 0,60€

 

Stats for April 2014
Campaign's name | CTR | CPC
Campagin 1            1,18% 0,56€

For now, I use 

var stats = campaign.getStatsFor('LAST_MONTH')

And that returns the last month's data.

Is there a way to get data for every month of the 6 previous months?

Thanks in advance!

[Anash Oommen]

Hi Arnaud,

Feel free to pick the necessary logic from this script:

function main() {
  var today = new Date();
  var currentMonth = today.getMonth();
  var currentYear = today.getYear();
 
  for (var i = 0; i < 6; i++) {
    var currentMonth = currentMonth - 1;
    if (currentMonth < 0) {
      currentMonth = 11;
      currentYear = currentYear - 1;
    }
    Logger.log(getDateRangeForMonth(currentMonth, currentYear));
  }
}

function getDaysInFeb(year) {
  if (((year % 4 == 0) && (year % 100 != 0)) || (year % 400 == 0)) {
    return 29;
  } else {
    return 28;
  }
}


function getDateRangeForMonth(month, year) {
  var daysInMonth = [31, getDaysInFeb(year), 31, 30, 31, 30, 31, 30, 30, 31, 30, 31]
 
  var startDate = new Date();
  startDate.setYear(year);
  startDate.setMonth(month);
  startDate.setDate(1);
 
  var endDate = new Date();
  endDate.setYear(year)
  endDate.setMonth(month);
  endDate.setDate(daysInMonth[month]);
  return [startDate, endDate];
}

Cheers,

Anash P. Oommen,

AdWords Scripts Team.

[Arnaud Guissani]

Thank you for your answer, that's very helpful!

Regards

[Arnaud Guissani]

Hi again,

I'm working on my script but I have run into a new problem.

What I don't understand is that works:

var stats = campaign.getStatsFor("20140301", "20140330");

And this doesn't:

var dates = String()
dates = '"20140301", "20140330"'

var stats = campaign.getStatsFor(dates);

I get this error message in the Logger even though it's supposed to be the same thing.

"Date range "20140301", "20140330" is invalid. Valid values: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH, ALL_TIME"

Did I do something wrong?

Regards

[Arnaud Guissani]

Well, I found another way to do it. Instead of using this :

var dates = String()
dates = '"20140301", "20140330"'
var stats = campaign.getStatsFor(dates);

I use this :

    var datecomplete = String();
    datecomplete = '20140301,20140330'
    var dateFrom = String();    
    var dateTo = String();
    dateFrom = datecomplete.slice(0,8)
    dateTo = datecomplete.slice(9,17)                                                    
    var stats = campaign.getStatsFor(dateFrom, dateTo);

It works perfectly.

[Anash Oommen]

Hi Arnaud,

I saw you solved the issue already, but just in case you are curious - dates is a single string, whereas getStatsFor takes two strings.

Cheers,
Anash

[Arnaud Guissani]

Hi Anash,

Then that explains why my first script wasn't working.

Thank you for the clarifications!