CAMPAIGN_LOCATION_TARGET_REPORT returns mismatching information to Google Ads website

[Rashel C]

Hello,

I'm trying to execute this query:

var report = AdsApp.report(
    "SELECT Id, CampaignName, CampaignId, BidModifier, Conversions, ConversionValue, Cost " +
    "FROM CAMPAIGN_LOCATION_TARGET_REPORT " +
    "WHERE IsNegative = FALSE " +
    "AND CampaignName = '" + config.CAMPAIGN_NAME +
    " AND CampaignStatus = ENABLED " +
    " DURING " + config.DATE_RANGE
  );

I set the date range to the last 30 days. For example, for the same campaign, in the last 30 days, it returns that, for Florida, the Cost was $2.41. Meanwhile, if I look at Florida via Google Ads website,  I see the cost was $2.83. I checked multiple locations and there was a lot of mismatches. The differences are usually small, but significant when I'm trying to calculate ROAS. 

Any idea how I can solve this? I really need the info from the query to match the website.

Thanks

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/Z-MzVurjy8w) from the forum as it wasn't routed to our support queue.

Regards,
Google Ads Scripts Team

[Google Ads Scripts Forum]

Hello,

Thank you for reaching out to the Google Ads Scripts Team. Please excuse us for only getting back now as your message failed to be routed to our support queue.

Could you please provide the following information so we're able to further investigate and guide you accordingly?:

• Google Ads account ID or CID
• Name of the script
• Complete and uncropped screenshot showing the information you'd like to retrieve from the Google Ads UI

You may send these privately via the Reply to author option. Note that you may need to join the Google Group for you to use this option. If this option is not available on your end still, you may send it through our email (googleadsscr...@google.com) instead.

Best regards,

Google Ads Scripts Team

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/Z-MzVurjy8w) from the forum as it wasn't routed to our support queue.

Regards,
Google Ads Scripts Team

On Wednesday, April 19, 2023 at 9:56:49 PM UTC+8 shelb...@gmail.com wrote:

Hello,

The script name is "Location Bidding Adjuster". The account ID is 199-666-6549.

S

Since the numbers provide before were from Last 30 days, I changed it to reflect the timeframe 2023/03/15-2023/04/14. The cost for Florida via the script is returned as $2.71. The website shows it, for the same timeframe, as $2.81. See the screenshot attached.

[Google Ads Scripts Forum]

Hi,

Thanks for providing the requested information. I would like to inform you that I had to remove your message as it had contained private information. Kindly refrain from posting messages containing private information as this forum is public, moving forward. Please do scrub any private data when posting information/images here or send it privately using the Reply privately to author option or through our provided email alias in our previous response. We are hoping for your kind understanding on this.

I've observed that the query being used by the script you provided is conforming to the AdWords Query Language. I would suggest using a query conforming to the Google Ads Query Language, as the AWQL is already deprecated and is expected to not work as intended. That being said, if you'd like to retrieve data from the targeted locations of campaigns via reporting, I would suggest making use of the report type location_view as well as it's query builder in order to help you build your query. Kindly note that the filters you've set in the UI must match that of your query, such as the date range, in order to retrieve the exact same data in your report. As per the screenshot you've provided, you may instead make use of the field LAST_30_DAYS from the date range drop down and use segments.date DURING LAST_30_DAYS within your query for it to match. If you're following the Custom date you've set (from your screenshot), then your script must also use that date range instead of LAST_30_DAYS or the returned values would not match.

I've made use of the query below in order to retrieve information on the location `Florida, United States` and can confirm that it matches that which can be found in the UI:

• SELECT campaign_criterion.location.geo_target_constant, location_view.resource_name, campaign.advertising_channel_type, campaign.name, metrics.cost_micros, metrics.conversions_value, metrics.conversions FROM location_view WHERE campaign_criterion.location.geo_target_constant = 'geoTargetConstants/21142' AND campaign.advertising_channel_type = 'SHOPPING' AND campaign.name = 'Top - Desktop' AND segments.date DURING LAST_30_DAYS

Let us know if you have any further clarifications.

Reference links:

• Google Ads Scripts reporting documentation - https://developers.google.com/google-ads/scripts/docs/features/reports
• Google Ads Query Language documentation - https://developers.google.com/google-ads/api/docs/query/overview
• location_view - https://developers.google.com/google-ads/api/fields/v13/location_view
  • location_view query builder - https://developers.google.com/google-ads/api/fields/v13/location_view_query_builder

Best regards, Google Ads Scripts Team

[Rashel C]

Hello,

Thank you for helping with this. I changed my queries to your recommended version. It worked fine for Florida. However, it still does not return what I need. For that campaign, in the last 30 days, I have 2 conversions. One conversion in Harris County, Texas, United States and another one in Walton County, Florida, United States.

The query returns that I have one conversion in Florida, United States and Harris County, Texas, United States. It does not return that I have a conversion in Texas, United States and Walton County, Florida, United States. I need it to return the conversions in 4 locations: at the state level and at a county level. How do I do that?

Additionally, the money spent for Harris County, Texas, United States is returned as $0.70, when the Google UI shows $1.25. The screenshot is attached. Please advise.

Thank you

[Rashel C]

It seems the culprit is because it returns the numbers of clicks incorrectly. For example, it returns that Florida, US had 10 clicks, when the UI shows 12 clicks. Texas, US returns 9 clicks, UI shows 16 clicks. Screenshot attached.

Thanks

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/Z-MzVurjy8w) from the forum as it wasn't routed to our support queue.

Regards,
Google Ads Scripts Team

[Google Ads Scripts Forum Advisor]

Hi,

 

Thanks for providing more information to this. Please see my insights below:

• Upon checking the campaign `Top - Desktop` for the date range March 21 - April 19, it appears that I am seeing a different value than what you've stated and wasn't able to observe a conversion for both the locations Texas, United States and Walton County, Florida, United States; please see screenshots attached. Would you be able to further elaborate if you had included any other filter or setting in order to see that those locations mentioned recorded a conversion? Complete and uncropped screenshots showing how you were able to get the data are very much appreciated.
• As for data at the state level and county level, would you be able to provide complete and uncropped screenshots showing this from the Google Ads UI so that we can further check on our end and guide you accordingly?
• Next, would you be able to create a simple script and log the results of the query our team provided previously so that our team may be able to further investigate if there is indeed a mismatch? Kindly provide the values shown from the Google Ads UI (with the filters matching) which shows if they have mismatched:

SELECT campaign_criterion.location.geo_target_constant, location_view.resource_name, campaign.advertising_channel_type, campaign.name, metrics.cost_micros, metrics.conversions_value, metrics.conversions FROM location_view WHERE campaign_criterion.location.geo_target_constant = 'geoTargetConstants/21142' AND campaign.advertising_channel_type = 'SHOPPING' AND campaign.name = 'Top - Desktop' AND segments.date DURING LAST_30_DAYS

 

You may send these privately via the Reply to author option. Note that you may need to join the Google Group for you to use this option. If this option is not available on your end still, you may send it through our email (googleadsscr...@google.com) instead.

 

Best regards,

 

Google Ads Scripts Team

ref:_00D1U1174p._5004Q2kou7S:ref

[Rashel C]

Hello,

Please see attached the screenshots for the county and the state levels. It shows conversions in Texas, US and Florida, US, as well as Walton County, FL and Harris County, TX.

The query I'm using is:

"SELECT campaign_criterion.location.geo_target_constant,metrics.average_cost, metrics.average_cpc, metrics.clicks, campaign_criterion.bid_modifier, location_view.resource_name, campaign.name, campaign.resource_name, metrics.cost_micros, metrics.conversions_value, metrics.conversions FROM location_view WHERE campaign.advertising_channel_type = 'SHOPPING' AND campaign.name = 'Top - Desktop' AND segments.date DURING LAST_30_DAYS"

Thank you

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/Z-MzVurjy8w) from the forum as it wasn't routed to our support queue.

Regards,
Google Ads Scripts Team

[Google Ads Scripts Forum Advisor]

Hi,

 

Thank you for providing the requested information and confirming which view you have retrieved data from.

 

A different report type would be needed to get the specific data you're looking for as different data would be returned by the location when it is being narrowed down or not. On getting reports for the screenshots you've provided, you would need to use the report type geographic_view as that report can be segmented by the settings you've picked such as: States and Counties. The equivalent fields to use for segmenting are segments.geo_target_state and segments.geo_target_county which you would need to include in the WHERE clause. Please refer to the sample query below in order to get the clicks, conversions and cost (micros) for `Florida, United States` when narrowed down by States as in the UI:

var query = "SELECT campaign.name, geographic_view.country_criterion_id, geographic_view.resource_name, segments.geo_target_state, metrics.clicks, "
  + "metrics.conversions, metrics.cost_micros FROM geographic_view "
  + "WHERE segments.date DURING LAST_30_DAYS AND campaign.name = 'Top - Desktop' AND segments.geo_target_state = 'geoTargetConstants/21142'";

 

In order to retrieve the criterion_id for a specific location, you can create a report using the location_view report type and select the fields campaign_criterion.location.geo_target_constant and location_view.resource_name to check which geo target constant and criterion_id the location corresponds to; as the resource name returns a value in the following format: customers/{customer_id}/locationViews/{campaign_id}~{criterion_id}. That being said, kindly make sure that the filters you've set in the UI match your query so as to obtain the same results.

 

Let us know if you have any clarifications or if we can assist you with anything else.

 

Reference links:

• geographic_view - https://developers.google.com/google-ads/api/fields/v13/geographic_view
• location_view - https://developers.google.com/google-ads/api/fields/v13/location_view
  • location_view.resource_name - https://developers.google.com/google-ads/api/fields/v13/location_view#location_view.resource_name
  • campaign_criterion.location.geo_target_constant - https://developers.google.com/google-ads/api/fields/v13/campaign_criterion#campaign_criterion.location.geo_target_constant