CODEWITHSUNDEEP
google-ads-apiawql
Abhay
Abhay

Reputation: 845

Google Ads Script (AWQL) get custom date range for reporting

I need to pull a google ads report that will get data from a fixed date (28th May) until today and push the data to a spreadsheet. I can't figure out how to define the date range for this query

I've tried googling and reading the google documentation but I can't figure it out

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/XXX');
  var sheet = spreadsheet.getSheetByName('Data')  
  var report = AdsApp.report(
    'SELECT Date, CampaignName, AverageFrequency, Impressions, ImpressionReach ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE  Impressions > 0 ' +
    'DURING 20190528,TODAY');

  sheet.clearContents();
  report.exportToSheet(sheet);
}

I need to use today as the end date instead of the campaign end date as the end date for this query as I'm trying to pull frequency as a metric and it will just show blank values if the end date is in the future.

Please let me know if there is a way to make the query work. Thanks!

Upvotes: 1

Views: 2671

Answers (2)

k1rushqa
k1rushqa

Reputation: 101

Date ranges for the report are defined in the DURING clause of the query. Date ranges can be specified in two different ways:

  1. A custom date range using regular AWQL syntax, for example:

    SELECT Id, Criteria, AdGroupName

    FROM KEYWORDS_PERFORMANCE_REPORT

    DURING 20190101,20190325

  2. A date range type, for example:

    SELECT Id, Criteria, AdGroupName

    FROM KEYWORDS_PERFORMANCE_REPORT

    DURING LAST_7_DAYS

In your case you should use:

DURING 20190528, 20190723

There is no other option for you to do that.

Upvotes: 0

CampaignCloner
CampaignCloner

Reputation: 36

The TODAY keyword acts as the "full range" of the DURING property and cannot be used as the end part (as far as I know). The following should work.

function main() {
    var endDate = new Date();
    var endRange = Utilities.formatDate(endDate, 'America/Chicago', 'YYYYMMdd');

    var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/XXX');
    var sheet = spreadsheet.getSheetByName('Data')  
    var report = AdsApp.report(
        'SELECT Date, CampaignName, AverageFrequency, Impressions, ImpressionReach ' +
        'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
        'WHERE  Impressions > 0 ' +
        'DURING 20190528,' + endRange);

    sheet.clearContents();
    report.exportToSheet(sheet);
}

Upvotes: 1

Related Questions