Null value in campaign_search_term_insight

[Alex Bijmolt]

Good afternoon,

What does it mean when value is empty/null for campaign_search_term_insight.category_label (keyword) in a campaign_search_term_insight report when reporting a Performance Max campaign? 

I would argue that when the value is empty, there is no keyword for that click or impression. Therefore it could be for example Display? But I haven't found information regarding this missing value.

Link to the documentation: https://developers.google.com/google-ads/api/fields/v14/campaign_search_term_insight

The script I used is:

function main() {

 let ss = SpreadsheetApp.openByUrl('xxxx');
 
 let query = AdsApp.report(
  `
  SELECT
   campaign_search_term_insight.category_label,
   metrics.clicks,
   metrics.impressions,
   metrics.ctr
  FROM campaign_search_term_insight
  WHERE
   segments.date DURING LAST_30_DAYS
   AND campaign_search_term_insight.campaign_id = 'xxxx'
  ORDER BY metrics.impressions DESC
  `
 );
 
 query.exportToSheet(ss.getSheetByName('xxxx'));
 
} // end main

Kind regard,

Alex Bijmolt

[Google Ads API Forum Advisor]

Hi Alex,

Thank you for reaching out to Google Ads API Forum.

Kindly note that we could only provide support to Google Ads API-related concerns and technical implementations. With this, you may note that the API only reflects the UI which means that if there is available data in the UI then the API should be able to manage or pull the same. With regards to your concern, could you share with us the full UI screenshot (without cropping) showing the data you are trying to retrieve using the said query? This is so we could further check on our end and provide more appropriate guidance. 

You may then send the requested information privately via the Reply to author option. If this option is not available, you may send the details directly to our googleadsa...@google.com alias instead.

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

Thanks,
 

Google Ads API Team

[Arthur]

Hi everyone,

If you don't mind me appending to this thread, I'd like to add a similar case with null values and a false positive in the GAQL Query Validator (v14). All based on the campaign_search_term_insight report.

1/3) Empty results - but correct number of items in result set

I modified Alex's query. In particular the two segments "search_subcategory" and "search_term" were added for more detail. Doing so resulted in the API stating that a WHERE condition of the field "campaign_search_term_insight.id" is required. Passing this as well, returned the correct number of items successfully (here 14, just as I see in the Google Ads Web UI). But all of them were empty. See query and result here:

QUERY:

"""
SELECT
campaign_search_term_insight.campaign_id,
campaign_search_term_insight.category_label,
campaign_search_term_insight.id,
segments.search_subcategory,
segments.search_term
FROM campaign_search_term_insight
WHERE
    campaign_search_term_insight.campaign_id = 'xxx'
    and campaign_search_term_insight.id = 'yyy'
"""

RESULT:

"""
{'results': [
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}},
  {'segments': {}}],
 'fieldMask': 'campaignSearchTermInsight.campaignId,campaignSearchTermInsight.categoryLabel,campaignSearchTermInsight.id,segments.searchSubcategory,segments.searchTerm'}

"""

2/3) Internal Server Error

And once I add a metric such as "metrics.impressions" to SELECT, an internal error occurs.

QUERY:

"""
SELECT
campaign_search_term_insight.campaign_id,
campaign_search_term_insight.category_label,
campaign_search_term_insight.id,
segments.search_subcategory,
segments.search_term,

metrics.impressions
FROM campaign_search_term_insight
WHERE
    campaign_search_term_insight.campaign_id = 'xxx'
    and campaign_search_term_insight.id = 'yyy'
"""

ERROR:

"""
Request made:
ClientCustomerId: xxx,
Host: googleads.googleapis.com,
Method: /google.ads.googleads.v14.services.GoogleAdsService/Search,
RequestId: DpspNLx6Qb9V8b7DCVkeZQ,
IsFault: True,
FaultMessage: Internal error encountered.

"""

3/3) False positive in GAQL Query Validator

To see the false positive in the GAQL Query Validator, please validate this query (which is the above one, but without the WHERE conditions):

https://developers.google.com/google-ads/api/fields/v14/query_validator

"""

SELECT
campaign_search_term_insight.campaign_id,
campaign_search_term_insight.category_label,
campaign_search_term_insight.id,
segments.search_subcategory,
segments.search_term
FROM campaign_search_term_insight

"""

The validation shows a valid query. But as stated above, the API requires the campaign_id and id to be filtered by, within WHERE. Hence, I expected the validator to hint for these two missing conditions.

"""

WHERE
    campaign_search_term_insight.campaign_id = 'xxx'
    and campaign_search_term_insight.id = 'yyy'

"""

So my question is this: Is there anything I am missing in formulating the GAQL query to retrieve actual data instead of the correct number of empty items?

fyi, the Python code for running the queries, if relevant:

"""

#versions:

#Python 3.11.2

#google-ads                21.2.0
#google-api-core           2.11.0
#google-auth               2.22.0
#google-auth-oauthlib      1.0.0
#googleapis-common-protos  1.59.1

from google.ads.googleads.client import GoogleAdsClient
from google.protobuf.json_format import MessageToDict

client = GoogleAdsClient.load_from_storage(xxx, version="v14")
service = client.get_service("GoogleAdsService")

query = """
xxx
"""

search_request = client.get_type("SearchGoogleAdsRequest")
search_request.customer_id = xxx
search_request.query = query
search_request.page_size = 1000

results = service.search(request=search_request)

MessageToDict(results._pb)

"""

Thank you and kind regards,

Arthur

[Martin Roettgerding]

Hi,

From what I have seen, the new campaign_search_term_insight report doesn't work as intended. Basically, you cannot get search terms from this report: once you include segments.search_term, the report fails. I've tested this via API and Ads Scripts – no difference. Arthur's queries are basically the most reduced ones and should be ideal to confirm this.

Aside from that, there are some constraints to this report that are neither mentioned in its documentation nor caught by its query builder. Instead, you have to learn about them through error messages. So far:

1. 'segments.search_term' can only be selected along with 'segments.search_subcategory'
2. campaign_search_term_insight can only be selected when filtering by a single 'campaign_search_term_insight.campaign_id' in WHERE clause
3. "segments.search_subcategory","segments.search_term" can only be selected when filtering by a single 'campaign_search_term_insight.id' in WHERE clause

2 and 3 mean that you need to use both campaign_search_term_insight.id and  campaign_search_term_insight.campaign_id to get search terms. First off, it seems redundant to require the campaign_id when there's already the insight's id. 

The restriction to one insight per query makes the report hard to use. Let's say you want to get all search terms for a campaign (provided the report would work as apparently intended).

In the UI: Navigate to the report and click download. You'll get a complete report with columns for categories, subcategories, search terms, and all metrics. Easy.

Via API: 

1. Run the report with a simple query that only gets you campaign_search_term_insight.id
2. (Loop) For each campaign_search_term_insight.id: Run a report with campaign_search_term_insight.id in the WHERE clause

For a PMax shopping campaign, this easily means thousands of queries. This can't be how we're supposed to use this report...

tl;dr

1. Querying for search terms results in an error
2. The documentation is incomplete
3. The restriction of one insight per query complicates things unneccessarily.

Thanks for reading, have a good week!

Martin

[Google Ads API Forum Advisor]

Hi Martin,

Thank you for coming back to us and for providing your insights about this matter. I hope you are doing well today.

In relation to your concern, for us to validate and further investigate on the mentioned issues, please provide us your complete logs with CID or uncropped screenshot from Google Ads UI (with data filter), this will help us to further check if this is related to API bug and raise this concern to a wider team. We are looking forward to the needed information, thank you for your patience and understanding.

Have a great day.
 

[Martin Roettgerding]

Hi,

I've sent you two customer IDs privately. However, as far as I can see, it's the same in all accounts.

Here is a little Ads Script that demonstrates the error. It grabs a campaign id, uses that to grab an insight id and finally uses both for a simple query that fails (I've used Ads Scripts since it seems the easiest way to demonstrate what happens with pure API as well.)

function main() {
  // Get campaign.id from the campaign with the highest cost
  let campaignId = AdsApp.report('SELECT campaign.id, metrics.cost_micros FROM campaign WHERE segments.date DURING LAST_MONTH ORDER BY metrics.cost_micros DESC').rows().next()['campaign.id'];
 
  // Get the top insight's id (avoid uncategorized search terms with id = 0)
  let queryInsightId = 'SELECT campaign_search_term_insight.id, metrics.impressions FROM campaign_search_term_insight WHERE segments.date DURING LAST_MONTH AND campaign_search_term_insight.campaign_id = "' + campaignId + '" AND campaign_search_term_insight.id != 0 ORDER BY metrics.impressions DESC';
  let insightId = AdsApp.report(queryInsightId).rows().next()['campaign_search_term_insight.id'];
 
  // Now query the data for this insightId from this campaignId
  let query = 'SELECT campaign_search_term_insight.category_label, segments.search_subcategory, segments.search_term FROM campaign_search_term_insight WHERE segments.date DURING LAST_MONTH AND campaign_search_term_insight.campaign_id = "' + campaignId + '" AND campaign_search_term_insight.id = "' + insightId + '"';
  let searchTermInsightsReport = AdsApp.report(query);
  let fail = searchTermInsightsReport.rows().totalNumEntities();
}

[Mat]

Hi,

I'm in the same boat as Alex, Arthur and Martin and I'm also struggling from one unexpected error message to the next. I would like to add, that the same errors occur in the customer_search_term_insight report. I would also like to suggest to name these reports as in the UI - namely with "..._insights" instead of "..._insight" - if you are already in the process of correcting the errors.

Thank you

Mat

[Google Ads API Forum Advisor]

Hi Mat,

Thank you for chiming in this thread. However, we will need the complete request and response logs with request-id and request header generated on your end or an uncropped screenshot from Google Ads UI (with data filter) in order to further investigate this issue. Kindly provide these details so we can proceed with our investigation. You may send the information to us via Reply privately to author option.

Reference links included in this email:
request - https://developers.google.com/google-ads/api/docs/concepts/field-service#request
response - https://developers.google.com/google-ads/api/docs/concepts/field-service#response
request ID - https://developers.google.com/google-ads/api/docs/concepts/call-structure#request-id
request header - https://developers.google.com/google-ads/api/docs/concepts/call-structure#request_headers