Export quality factors into Google Sheets segmented by day

[Sebastian]

Hi there,

I just want to export quality factors like HistoricalSearchPredictedCtr, HistoricalCreativeQualityScore, HistoricalLandingPageQualityScore into Google Sheets for further evaluation.

Best case would by a segmentation by day but I don't know the correct explanation for this. Otherwise I use historical data and define a specific date but then the data in Google Sheets is overwritten with every run.

function main() {

  var awql = 

      " SELECT Date, CampaignName, AdGroupName, Criteria, Impressions, Clicks, Cost, " +

        " QualityScore, PostClickQualityScore, SearchPredictedCtr, " +

        " HistoricalQualityScore, HistoricalSearchPredictedCtr, HistoricalCreativeQualityScore, HistoricalLandingPageQualityScore " +

      " FROM KEYWORDS_PERFORMANCE_REPORT " +

      " WHERE HasQualityScore = 'TRUE' " +

      " DURING YESTERDAY ";

  var report = AdWordsApp.report(awql);

  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxx');

  var sheet = spreadsheet.getSheetByName('Input');

  

  report.exportToSheet(sheet);  

}

Do you have any advice how to optimize my code?

Best regards

Sebastian

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

There is a DayOfWeek segmentation in the report. If this is not what you are looking for, could you be a bit more specific on what you're looking for?

Thanks,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5004Q218egE:ref

[Sebastian]

Hi Matt,

thanks for your reply. At the end I would like to have a output with with the following fields and data for example:

Date | Campaign | Keyword | LandingPageQualityScore | ...

-------------------------------------------------------------

2020-06-20 | Campaign 1 | Keyword 1 | Average

2020-06-21 | Campaign 1 | Keyword 1 | Above Average

...

I want to use this data for showing the progress of the quality components.

Thanks,

Sebastian

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

If you want that specific data, then you can use the script below.

function main() {
  var awql = 

      " SELECT Date, CampaignName, Criteria, PostClickQualityScore " +

      " FROM KEYWORDS_PERFORMANCE_REPORT " +
      " WHERE HasQualityScore = 'TRUE' " +
      " DURING YESTERDAY ";
  var report = AdWordsApp.report(awql);
  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxx');
  var sheet = spreadsheet.getSheetByName('Input');
  
  report.exportToSheet(sheet);  
}

Let me know if you have further questions.

Regards,
Ejay

[Sebastian]

Okay, thanks fir your reply. But whats the code for a segmentation by day?

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

You can use a where-clause to specify which days should be included in the report. For example, you could add the line to Ejay's code after the first where-clause:

"WHERE Date IN ['2020-06-20','2020-06-20']"

Regards,
Matt

[Sebastian]

Thanks a lot for your help, Matt. My problem is solved. I realized that I have to learn more in Javascript coding. Best, Sebastian