Querying a report of search term based on matching strings.

[Joseph Lindsay]

I'd like to get a report of all search terms that:

1. contain "foreign"
2. do not contain "payments"

Currently trying this:

SELECT search_term_view.search_term, search_term_view.ad_group
FROM search_term_view
WHERE segments.date DURING LAST_30_DAYS
  AND search_term_view.status = "NONE"
  AND search_term_view.search_term LIKE "%foreign%"
  AND search_term_view.search_term NOT LIKE "%payments%"

[Google Ads Scripts Forum Advisor]

Hi,

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

The provided search_term_view is a valid query and the applied filters are correct. I would suggest you use the Query Builder to build your query and the Query Validator to validate your query. 

In order to replicate the issue from our end 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.!5004Q02tJKcS:ref" (ADR-00240815)

Thanks,
 

Google Ads Scripts Team

[Nils Rooijmans]

instead of using the LIKE operator, try using  REGEXP_MATCH  and  NOT REGEXP_MATCH.

see https://developers.google.com/google-ads/api/docs/query/grammar

-->

  AND search_term_view.search_term  REGEXP_MATCH   ".*foreign.*"
  AND search_term_view.search_term  NOT REGEXP_MATCH   ".*payments.*"

Hope this helps,

Nils Rooijmans
https://nilsrooijmans.com
See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/

[Google Ads Scripts Forum Advisor]

Hi,

@ Nils - Thank you for your input.

You may follow Nil's suggestion and let us know how it goes on your end.

[Joseph Lindsay]

Nils! Just got access to your spreadsheet of scripts.

I had tried REGEXP_MATCH in the past but didn't realize you needed .*

Thanks for your help!