Dynamically define date range in report query?

[chelse...@attacat.co.uk]

Hello,

I'm trying to pull data from the campaign performance report for dynamic date ranges.

I know, for instance, that I can query yesterday's data with the following DURING statement (DateRangeLiteral):

// query YESTERDAY's data

  var report = AdWordsApp.report(

    "SELECT Impressions, Clicks, Cost, AveragePosition, Conversions, ConversionValue, CampaignStatus, AdNetworkType1 " +

    "FROM   CAMPAIGN_PERFORMANCE_REPORT " +

    "WHERE CampaignStatus = ACTIVE " +

    "DURING YESTERDAY");

Or with this one (Date,Date): 

"DURING 20130321,20130321");

But what if I want to query the day before yesterday, based on today's date? Ideally, I'd like it dynamically defined in a variable...

var today = new Date();
var dayBeforeYesterday = today.getDate() - 2;

...that I then reference in the DURING statement of the query:

"DURING " + dayBeforeYesterday + "," + dayBeforeYesterday);

Is this at all possible? At the moment, the script is not recognising the variable as a valid date range in the query.

Any help would be greatly appreciated!

Thanks,

Chelsey

[Ewan Heming]

Hi Chelsey,

Sure, you could do this by defining a function to add days to a date, and another function to format it appropriately. One way of adding days to a date is to add the number of milliseconds in a day it:

function addDays(dt, days) {
    var milisInDay = 86400000;
    return new Date(dt.getTime() + (milisInDay * days));
}  

Next, you need to format it for use with the reporting service:

function formatDate(dt) {
    var day = dt.getDate();
    day = (day > 9) ? day : "0" + day;
    var month = dt.getMonth() + 1;
    month = (month > 9) ? month : "0" + month;
    var year = dt.getFullYear();
    return "" + year + month + day;
}

Now you can use these two functions to run your report:

function main() {

    var today = new Date();

    var startDate = endDate = formatDate(addDays(today, -2));


    var report = AdWordsApp.report(
            "SELECT Impressions, Clicks, Cost, AveragePosition, Conversions, " +
            "ConversionValue, CampaignStatus, AdNetworkType1 " +

            "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
            "WHERE CampaignStatus = ACTIVE " +

            "DURING " + startDate + "," + endDate);
}

Regards,

Ewan

[chelse...@attacat.co.uk]

Hi Ewan,

Thanks for your reply! I've been working with similar formulas to generate the date, but my issue is when I try to run the query in the reporting service - the AWQL line that defines the time period (DURING) doesn't seem to recognise the variables startDate and endDate (or my equivalents). I get the following error:

"Parsing error. Please check your selector."

Any ideas?

Chelsey

[Ewan Heming]

Hi Chelsey,

The code you posted above is creating a variable, called "dayBeforeYesterday", that is calculated as the current day of the month minus two; for example, the current date is March 24, so the "getDate" method will return the integer 24, and the variable will hold the number 22, which isn't a valid date for use in AWQL. 

You need to create a new date that is two days in the past, then format it as a valid text string using code similar to the "formatDate" function from my last post. To create the date, you can pass a UNIX timestamp to the constructor, in a similar way to the "addDays" function I used, or you can use the "setDate" method:

var today = new Date();

var dayBeforeYesterday = new Date();
dayBeforeYesterday.setDate(today.getDate() - 2);  

Regards,

Ewan

[chelse...@attacat.co.uk]

Thanks Ewan! It seems to be working now. :)