AWQL query Python List Variable

[Green Leaf]

Hi

Could someone help me to code this AWQL query using Python.

I want to add a variable containing a list.

So this works - AND KeywordTextMatchingQuery - IN ['keyword 1', 'keyword 2']

And if a single keyword is a variable, then this works - AND KeywordTextMatchingQuery = " +"'"+ keyword_variable_example +"'"+ "

But if the list is a variable I can’t concatenate it.

I found this (https://groups.google.com/g/adwords-api/c/E7eF1AZc8Mw/m/nqSX9AB0BwAJ) but can’t make it work with the grammer/style that I’m using.

Can anyone help?

[Google Ads API Forum Advisor Prod]

Hello,

I'd recommend reaching out to the client library owners as they are better suited to assist you on this issue.

Regards,
Anthony
Google Ads API Team

Anthony Google Ads API Team

ref:_00D1U1174p._5004Q23vtNt:ref

[Green Leaf]

Thanks, will do

[Mat]

Hi Green Leaf,

some alternative to my solution you've linked would be to convert the list into a string using "join()" and/or insert that string into the query using ".format().

keyword_list = ["keyword1", "keyword2", "keyword3"]

keyword_list_as_string = ', '.join(str(e) for e in keyword_list)

#This should work with the AdWords API Python library:

query = ('SELECT Id, Criteria, AdGroupName FROM KEYWORDS_PERFORMANCE_REPORT '

'WHERE somecondition '

'AND KeywordTextMatchingQuery IN [{}]'.format(keyword_list_as_string)

         ) 

#And this is a Python example that does work with the Google Ads API Python library:
       shared_set_ids = [1234, 5678, 2345]

    shared_set_ids_as_string = ', '.join(str(e) for e in shared_set_ids)

    query = ('SELECT '

                'shared_criterion.criterion_id, '           

                'shared_criterion.keyword.match_type, '                         

                'shared_criterion.keyword.text, '

                'shared_criterion.type, ' 

                'shared_set.id '

                'FROM shared_criterion '

                'WHERE shared_set.id IN ({}) '

                'AND shared_set.status = ENABLED '

                'AND shared_set.type = NEGATIVE_KEYWORDS'.format(shared_set_ids_as_string)

                )

Regards 

Mat

[Mat]

...note: the KeywordTextMatchingQuery is not available in the KEYWORDS_PERFORMANCE_REPORT as my example suggests. Use the SEARCH_QUERY_PERFORMANCE_REPORT instead.

[Green Leaf]

Thanks for your help Mat.

I'm using the AdWords API Python library so looking at your first example.

I tried multiple ways (and went searching on Google again) but no luck.

This is what I thought might be the closest. Any thoughts would be much appreciated.

list_of_keywords_counted = ['keyword 1', 'keyword 2']

list_of_keywords = ', '.join(str(e) for e in list_of_keywords_counted)

.

.

.

 AND KeywordTextMatchingQuery IN [{}].format(" + list_of_keywords + ")

[Mat]

Hi Green Leaf,

As far as I can see, you're not using the .format() method correctly. You either have to put the string to be formatted in quotes or double quotes. Example (as an oneliner, to make it clearer):

"SELECT Id, Criteria, AdGroupName FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE somecondition AND KeywordTextMatchingQuery IN [{}]".format(keyword_list_as_string)

You can find more examples in the Python reference:
https://docs.python.org/3.8/library/string.html#format-examples

To help you further I'd need to have a look at the whole AWQL string you are using as well as the error messages it produces.

Regards

Mat

[Green Leaf]

Thanks Mat

The code I used:

report_query = ("SELECT KeywordTextMatchingQuery, Clicks FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE KeywordTextMatchingQuery IN [{}]".format(list_of_keywords_counted))

The error is below (I've removed the CID):

 'includeZeroImpressions': 'False', 'server': 'adwords.google.com', 'skipColumnHeader': 'False', 'skipReportHeader': 'True', 'skipReportSummary': 'True', 'isError': True, 'errorMessage': '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><reportDownloadError><ApiError><type>QueryError.INVALID_WHERE_CLAUSE</type><trigger></trigger><fieldPath></fieldPath></ApiError></reportDownloadError>'}

[Mat]

Hi Green Leaf,

according to your example above, you should not pass the list "list_of_keywords_counted" but the stringified "list_of_keywords" to the .format() method:

report_query = ("SELECT KeywordTextMatchingQuery, Clicks FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE KeywordTextMatchingQuery IN [{}]".format(list_of_keywords))

I've tested it and it works fine - as long as the keywords don't contains special characters like "+".

Therfore, I'd suggest to stringify the list instead this way:

list_of_keywords_counted = ['keyword 1', 'keyword 2', '+keyword 3']
list_of_keywords = str(list_of_keywords_counted)[1:-1]  
#this will result in a string, where the keywords are enclosed in single quotes:
#'keyword 1', 'keyword 2', '+keyword 3'

report_query = ("SELECT KeywordTextMatchingQuery, Clicks FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE KeywordTextMatchingQuery IN [{}]".format(list_of_keywords))

Regards

Mat

[Green Leaf]

Hi Mat

All sorted.

Thank you for your time and patience. You’re a legend. I really appreciate your help.