Trying to filter MCC search query report for Query contains

[Lea Harper]

Google Ads UI report times out if I try to run a search term report for several days (possibly because we have over 1,000 accounts in MCC). I'm trying this script for 10 accounts and am copying the logs and then parsing in a Sheets spreadsheet (because I haven't figured out how to send directly to a spreadsheet), but the logs say the list is truncated due to having too many rows. 

Is there a way to put in a WHERE clause to filter the search queries down to only those that contain the word "near" (and not case sensitive) - thanks in advance for any help!

function main() {
	//adjust the start and end date below for the date range of your search query data
	//for now, you also need to update the list of customer IDs the report will pull for
	//I will try to figure out how to have this run across all accounts in the MCC
	//I also want to edit this to send the info to a Google Sheets URL rather than only appearing as comma separated list in the Logs here in Google Ads Scripts area
	//also need to add a filter like Ignore if Campaign contains a word, like filtering out Seller if we only want Buyer campaign search queries
	var startDate = "2020-06-01";
	var endDate = "2020-06-10";
	// The start and end date of the date range for your search query data
	// Format is yyyy-mm-dd
	var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
	var mccAccount = AdsApp.currentAccount();
	var accountIterator = AdsManagerApp.accounts().withIds(['123-456-7890', '234-567-8901']
	).get();
	while (accountIterator.hasNext()) {
	var account = accountIterator.next();
	AdsManagerApp.select(account);
	var search_queries = [];
	var queries = AdsApp.report(
	"SELECT Query,AccountDescriptiveName,AdGroupName,Conversions,ConversionRate,AverageCpc,CostPerConversion,Cost,QueryMatchTypeWithVariant,QueryTargetingStatus "+
	"FROM SEARCH_QUERY_PERFORMANCE_REPORT "+
	"WHERE QueryTargetingStatus = NONE "+
	"DURING " + dateRange
	);
	var rows = queries.rows();
	while (rows.hasNext()) {
	var row = rows.next();
	search_queries.push([row['Query'],row['AccountDescriptiveName'],row['AdGroupName'],row['Conversions'],row['ConversionRate'],row['AverageCpc'],row['CostPerConversion'],row['Cost'],row['QueryMatchTypeWithVariant'],row['QueryTargetingStatus']]);
	}
	search_queries.forEach(function (val){
	Logger.log(val);
	});
	}
	}

[Google Ads Scripts Forum Advisor]

Hi Lea,

To filter the phrase 'near', ignoring case, in your report, you can add the following where-clause:
 

    var queries = AdsApp.report(
      "SELECT Query,AccountDescriptiveName,AdGroupName,Conversions,ConversionRate,AverageCpc,CostPerConversion,Cost,QueryMatchTypeWithVariant,QueryTargetingStatus "+
      "FROM SEARCH_QUERY_PERFORMANCE_REPORT "+
      "WHERE QueryTargetingStatus = NONE "+

      "WHERE Query CONTAINS_IGNORE_CASE 'near' " + 
      "DURING " + dateRange 
    
    );

You can find AWQL documentation here.

Regards,
Matt
Google Ads Scripts Team
 

ref:_00D1U1174p._5004Q20YC6K:ref

[Lea Harper]

Thank you, Matt!

[Joseph Lindsay]

Can you update this for 2024?

I cannot get CONTAINS_IGNORE_CASE to work.

[Google Ads Scripts Forum Advisor]

Hi,

The 'CONTAINS_IGNORE_CASE' WHERE clause is not supported in the Google Ads API. I would suggest you check this document to know more about the supported operator. You may refer to the case sensitivity document. You may check this document for available reports.  

If you face any issues, provide us with the following details :

• Google Ads account ID or CID
• Name of the affected script in the account
• Error details or screenshot of the issue (if any)
• If the script uses a spreadsheet, please provide the shareable link to the spreadsheet to share a file publicly

You can send the details via Reply privately to the author option, or direct private reply to this email.
 

This message is in relation to case "ref:!00D1U01174p.!5004Q020YC6K:ref" (ADR-00038564)

Thanks,
 

Google Ads Scripts Team