Getting error when fetching shopping_performance_view by dates

[Jay Wilner]

Hello,

Thanks for reading my post. I am sure I am doing something really obviously wrong but any help would be appreciated.

The code giving me problems is below:

function getFilteredShoppingPerfView(daysAgo){
  var today = new Date();
  var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - daysAgo);
  var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  let fromDate     = ' 2023-01-01';
  let toDate = ' 2023-10-10';


    var query =

      'SELECT segments.product_item_id, segments.product_custom_attribute3, segments.product_title,metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
      'FROM shopping_performance_view ' +
      'BETWEEN' + fromDate + toDate

 
    var products = [];
    var count = 0;
    //fetch the report from api
    var report = AdWordsApp.report(query);
    var rows = report.rows();
    while (rows.hasNext()){
        var row = rows.next();

Whenever i run the above, i get the following error:

QueryError.UNEXPECTED_INPUT: Error in query: unexpected input BETWEEN. at getFilteredShoppingPerfView (Code:39:17) at main (Code:14:18)

I checked my GAQL query in the Query validator tool and it shows valid query

as shown in attached screenshot.

I am thinking the issue is an incorrect space or semicolon somewhere?

Thanks again in advance for helping!

Jay

[Sigurd Fabrin]

The query in the query builder and the one in your code are not the same ..

Sigurd

[Jay Work]

Thanks for responding. True they’re not exactly the same but it shows the similar structure. I just didn’t select all the fields.

On Oct 10, 2023, at 7:56 AM, Sigurd Fabrin via Google Ads Scripts Forum <adwords...@googlegroups.com> wrote:

The query in the query builder and the one in your code are not the same ..

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/skOj-w4FqtQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/b60c329b-efe0-4880-89d0-6a8576fa06f6n%40googlegroups.com.

[Jay Wilner]

Hello Sigurd Fabrin again, I know you are busy so appreciate your comment about code not being the same.

I did build the exact query in the query builder and validated it. you can see in attachment below:

But I can't use that validated query as is, but put the entire GAQL query inside quotes to store in a variable ('query') that I then pass to AdWordsApp.report(query):

var query =

      'SELECT segments.product_item_id, segments.product_custom_attribute4, segments.product_title,metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
      'FROM shopping_performance_view ' +
      'WHERE segments.date ''2023-01-01'' AND ''2023-12-31''. <----------------------------------I think the issue is the quotes being incorrect, or spacing is wrong.

 
    var products = [];
    var count = 0;
    //fetch the report from api

    var report = AdWordsApp.report(query);  <----------------------------------Here we call the GAQL query variable

[Sigurd Fabrin]

Hi Jay,

You are not including all the necessary parts of the query, so it will never work. More on GAQL syntax here https://developers.google.com/google-ads/api/docs/query/grammar

A good trick is to read code backwards. It forces the brain to read what the text actually is, rather than what we interpret it to be.

Another good trick is to separate everything out in multiple lines and use indentation. It makes it much easier to spot mistakes.

Here's an example script you can use for comparison

const settings = {
  url: 'https://docs.google.com/spreadsheets/etc',
  sheet: 'Sheet1',
  lookBackDays: 90 // Look back this number of days
}
function main() {
  const timeZone = AdsApp.currentAccount().getTimeZone();
  const format = 'yyyy-MM-dd';
  const today = new Date();
  const oneDay = 1000*60*60*24;
  const startDate = Utilities.formatDate(new Date(today-(oneDay*settings.lookBackDays)),timeZone,format);
  const yesterday = Utilities.formatDate(new Date(today-oneDay),timeZone,format);
  const query =
  `SELECT
    segments.date,
    campaign.name,
    campaign.id,
    metrics.cost_micros
  FROM
    campaign
  WHERE
    metrics.cost_micros > 0
    AND segments.date BETWEEN "${startDate}" AND "${yesterday}"`;
  const response = AdsApp.search(query);
  const data = [];
  while (response.hasNext()) {
    const row = response.next();
    data.push([
      row.segments.date,
      row.campaign.name,
      row.campaign.id,
      row.metrics.costMicros/1000000
    ])
  }
  data.unshift([
    'Date',
    'Campaign name',
    'Campaign id',
    'Cost'
  ]);
  const sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);
  sheet.clearContents(); // delete any old data first
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}

Sigurd

[Google Ads Scripts Forum Advisor]

Hi Sigurd,

Thank you for reaching out to the Google Ads Scripts support team.

Thank you for providing the solution for the issue “QueryError.UNEXPECTED_INPUT”. However, please note that we have tried to execute the script which you have provided and I would like to inform you that we are getting the “ReferenceError: settings is not defined” error.

In order to investigate further, kindly provide us with the following details:

• Google Ads account ID / CID
• Name of the affected script
• Screenshot of the issue
• If the script uses a spreadsheet, please provide a shareable link to the spreadsheet. You can follow this guide to share a file publicly.

 

This message is in relation to case "ref:_00D1U1174p._5004Q2pQeXi:ref"

Thanks,
 

Google Ads Scripts Team

[Sigurd Fabrin]

"we are getting the “ReferenceError: settings is not defined” error."

settings is declared on the very first line. I suspect that you may have skipped the part above where the main function begins 

const settings = {
  url: 'https://docs.google.com/spreadsheets/etc',
  sheet: 'Sheet1',
  lookBackDays: 90 // Look back this number of days
}

Sigurd

[Jay Wilner]

Here is the entire full code. I think the problem is with my GAQL syntax.

here is most recent error message

10/11/2023 2:47:29 PM
Ca: QueryError.INVALID_VALUE_WITH_DURING_OPERATOR: Invalid date literal supplied for DURING operator: dateYTD. at getFilteredShoppingPerfView (flowboost labelizer 1.1 copy:36:17) at main (flowboost labelizer 1.1 copy:14:18)

• Google Ads account ID / CID: 781-438-3043
• Name of the affected script: Flowboost Labelizer 1.1
• Screenshot of the issue-see attached
• If the script uses a spreadsheet, please provide a shareable link to the spreadsheet: https://docs.google.com/spreadsheets/d/1hDdtE3AmyzTytoFJsvpNoI3ZIqynr0iqSwrDEvfoyO0/edit?usp=sharing

====================================================================

// Flowboost Labelizer 1.1 by Floris de Schrijver

var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1hDdtE3AmyzTytoFJsvpNoI3ZIqynr0iqSwrDEvfoyO0/edit#gid=0";
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName('Flowbelizer');

var breakevenRoas = 6.0; // set it 10-20% lower than the account target
var convRate = 1.5; // conversion rate on average in shopping in past daysAgo
var impressionThreshold = 50; // less than 50 means a ‘zombie’ or ‘no-index’
var daysAgo = 90; // days you like to look back at


function main(){
  var products = getFilteredShoppingPerfView(daysAgo);
  writeData(products,sheet);

}

function getFilteredShoppingPerfView(daysAgo){
    var today = new Date();
    var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - daysAgo);
    var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

    let dateYTD     = " segments.date BETWEEN '2023-01-01' AND '2023-12-31' "; // pull from future dates to simulate a 'YTD' range

    var query =

      'SELECT segments.product_item_id, segments.product_custom_attribute4, segments.product_title,metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
      'FROM shopping_performance_view ' +

      'WHERE segments.date DURING dateYTD'

 
    var products = [];
    var count = 0;
    //fetch the report from api
    var report = AdWordsApp.report(query);
    var rows = report.rows();
    while (rows.hasNext()){
        var row = rows.next();

        var item_id = row['segments.product_item_id'];
        var custom_label4 = row['segments.product_custom_attribute4'];
        var title = row['segments.product_title'];
        var impressions = row['metrics.impressions'];
        var ctr = row['metrics.ctr'];
        var clicks = row['metrics.clicks'];
        var costMicros = row['metrics.cost_micros'];
        var cost = costMicros/1000000;
        var conversions = row['metrics.conversions'];
        var conversionValue = row['metrics.conversions_value'];

        //calculate cpa, roas and % conv
        if ((cost==0) || (conversions==0)) {
            var cpa = 0;
        }
        else {
            var cpa = cost/conversions;
        }
        //roas
        if ((conversionValue==0) || (cost==0)) {
            var roas = 0;
        }
        else {
            var roas = conversionValue/cost;
        }
         //conv Rate
        if ((clicks==0) || (conversions==0)) {
            var convRate = 0;
        }
        else {
            var convRate = conversions/clicks;
        }
 
        var isProductType = '';
        //define threshold to make decision based on min clicks
        var minClicks = 100/convRate*0.5;

        if (impressions < impressionThreshold){
            isProductType = 'zombie'; // zombie meaning no impressions accruing
        }
        else if (roas >= breakevenRoas*1.3) {
            isProductType = 'over-index';
        }
        else if (roas >= breakevenRoas) {
            isProductType = 'index';
        }
        else {
            isProductType = 'under-index';
        }

        // push product row to temp array
        products.push([item_id, custom_label4, title,impressions, ctr,clicks, cost, conversions, conversionValue, cpa, roas, convRate,isProductType]);
        count+= 1;
    } // end of while loop
   
    Logger.log(count);
    return products;
  }

function writeData(products,sheet){
  sheet.clearContents();
  sheet.clearFormats();
  //create header row
  sheet.appendRow(["item id", "custom label4","title", "impr", "ctr","clicks", "cost", "conv.", "revenue","cpa",
    "roas","% conv", "isProductType"]);
  //write product report values all at one time to gsheet
  var myNumRows = products.length;
  var myNumCols = products[0].length;
  var myDestinationRange = sheet.getRange(2, 1, myNumRows, myNumCols);
  myDestinationRange.setValues(products);
}

[Jay Wilner]

Thank you Sigurd. I will be trying your code out soon! The trick may be this statement you provided: segments.date BETWEEN "${startDate}" AND "${yesterday}"`;

I didnt realize i needed to wrap the variable with "${variable}"

Appreciate you helping me.👍🏼

[Jay Wilner]

It worked Sigurd! The key for me was the template literal, the backtick. And also the placeholder with curly braces: ${expression}