how to avoid empty result when dimension is not set on data

[Pablo Fernandez]

Hi.
with the following query I'm able to collect costs with country and city as columns.

SELECT
segments.date,
geographic_view.country_criterion_id,
segments.geo_target_region,
metrics.clicks,
metrics.impressions,
metrics.cost_micros,
customer.id,
customer.resource_name,
geographic_view.resource_name,
customer.currency_code,
customer.descriptive_name,
segments.geo_target_city
FROM geographic_view
wHERE segments.date BETWEEN '2023-05-08' AND '2023-05-10'



if I add the dimension segments.geo_target_county, the result goes empty as any of the countries in my data has county. The same with the dimension segments.geo_target_district.

I do know countries in my data that has district but no city, and others having cities but no district. I need to retrieve them all, and I do not know what country has one or another (I only know specific ones having this caracteristic).
If I put every column that I need, I get an empty result. 

How can I achieve that? with SQL I could use COALESCE(segments.geo_target_district, ' ' ) but this is not allowed in GAQL.

Is the only way to do that to iterate over every dimension to get them all separately?

The dimensions I need to retrieve are

segments.geo_target_city, segments.geo_target_county, segments.geo_target_district, segments.geo_target_province, segments.geo_target_metro, segments.geo_target_state, segments.geo_target_most_specific_location

Thanks

[Google Ads API Forum Advisor]

Hello,

Thanks for reaching out to the Google Ads API Team.

I understand that with the query you have provided, you are not getting any results when you try to add segments.geo_target_county into your query. Kindly note that Google Ads API mirrors the Google Ads UI. I would suggest trying to send requests by adding these segments separately. With that, you will be able to get the data. However, kindly refer to the provided segmentation guide as this will explain how segmentation will affect the data that you are pulling.

• segmentation guide - https://developers.google.com/google-ads/api/docs/reporting/segmentation 

If the issue persists, can you please provide us with an uncropped screenshot of the Google Ads UI that shows the data you are trying to pull and the complete request and response logs with request ID generated on your end so our team can investigate further and provide recommendations?

• 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

Regards,

Google Ads API Team

ref:_00D1U1174p._5004Q2lCMxp:ref

[Pablo Fernandez]

Thank you

As stated in the documentation you provided me, 

When adding some segments, the metrics in the summary row may decrease

When segments.keyword.info.match_type is added to a query with FROM ad_group_ad, that segment is telling the query to only get the rows of data that have keywords, and remove any row that is not associated with a keyword. In this case, the metrics would be lower because it would exclude any non-keyword metrics.

so, when some data of an ad has no city involved (because belongs to a different Region type or simply there is no opening but the country itself), the summation for costs would be less than the cost reported for the hole country.

 

does exist ANY way to obtain those rows with null values for city dimension? I can only think on iteration over the distinct segments.geo_target_XXX dimensions.

Also, where do exist the 'Criteria ID' dimension, stated in geotarget constants as unique identifier for the target type (i.e. Department 
Oruro Department,Bolivia has Criteria ID 9069872, with parent 2068, Bolivia Country)? I could only find Parent ID as geographic_view.country_criterion_id.

[Google Ads API Forum Advisor]

Hi Pablo,

Thank you for getting back to us. I hope that you are doing well today.

Moving forward to your concerns:

• does exist ANY way to obtain those rows with null values for city dimension?

• • As mentioned in the documentation about segmentation (https://developers.google.com/google-ads/api/docs/reporting/segmentation#selectability_between_segments_and_metrics), I'm afraid that the metrics in the summary row may indeed decrease when adding some segments. That being said, the thing that we would suggest you do is remove the segment field from a query so that you can see the data you are referring to.

• where do exist the 'Criteria ID' dimension, stated in geotarget constants as unique identifier for the target type (i.e. Department Oruro Department,Bolivia has Criteria ID 9069872, with parent 2068, Bolivia Country)?

• • I saw in the pdf file that you provided that it is indeed under the criteria id. For us to further check and answer this concern accurately, could you please provide us the full non-cropped and non-redacted screenshot of the UI?

In addition, please also provide us the complete request and response logs with request ID generated on your end so our team can investigate further and provide recommendations?

• 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

You can provide it via Reply privately to the author option. If this option is not available, then send it instead on this email address googleadsa...@google.com.

Kind regards,

[Pablo Fernandez]

Thank you, but let me reformulate the question.

I'm running a Google Ads Scripts (GoogleAds->Tools and Settings->Bulk Actions->Scripts). The query being run is 

SELECT  
    segments.date,
    customer.id,
    customer.resource_name,
    customer.descriptive_name,
    segments.geo_target_most_specific_location,
    geographic_view.country_criterion_id,
    segments.geo_target_region,
    customer.currency_code,
    metrics.cost_micros

FROM geographic_view
wHERE segments.date BETWEEN 2023-05-08 AND 2023-05-10

I need to match in this kind of result with the geo target constant downloaded here: https://developers.google.com/google-ads/api/data/geotargets

This data has the following structure:

Criteria ID | Name | Canonical Name | Parent ID | Country Code | Target Type | Status
20083 | Cochabamba Department |  Cochabamba Department,Bolivia | 2068 |  BO | Department | Active

20084 | La Paz Department | La Paz Department,Bolivia | 2068 | BO | Department | Active

In my query results, I would need to retrieve the Criteria ID identifier as a dimension (i.e. either 20083 or 20084), but only found Parent ID as geographic_view.country_criterion_id (corresponding with Parent ID 2068, shared on both rows).

As stated in this other thread (https://groups.google.com/g/adwords-api/c/5BurwkRZYsw/m/6y1bvJ0bCgAJ), I would not need to download the file but just making the next specific query as a second step:

"SELECT geo_target_constant.canonical_name,
geo_target_constant.country_code,
geo_target_constant.id,
geo_target_constant.name,
geo_target_constant.status,
geo_target_constant.target_type
FROM geo_target_constant
WHERE geo_target_constant.resource_name = 'geoTargetConstants/<<criterion ID>>'"

I dont want to match by country name, I need the row identifier (a.k.a., segmenting the data with city, department, region or other), so I would replace the WHERE condition with

WHERE geo_target_constant.id = SOME_DIMENSION_I'M_LOOKING_FOR
----

So, my question is where to find this geo_target_constant.id (meaning, which resource has it as a valid segmentation), so I can check if this would be a nice fit for my pursposes.

Again, I don't mind splitting in several steps, I want the final solution that mimic adding a segmentation for Criteria ID

Thank you

[Google Ads API Forum Advisor]

Hi Pablo,

Thank you for returning to our team.

Regarding your concern, please note that Google Ads API mirrors the Google Ads UI. Meaning the Google Ads API should be able to retrieve or manage the data available in the Google Ads UI. That being said, as requested, so our team can provide accurate recommendations to you, can you please the below details?

• Complete request and response logs with request ID and request header generated on your end.
• Complete screenshots (without cropping) from the Google Ads UI showing the data 

You can provide it via Reply privately to the author option. If this option is not available, then send it instead on this email address googleadsa...@google.com.

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

Regards,