how to get the geo data of google ads one day?

[chaoming chen]

I am a google ads manager customer's developer
 the ads customer id is: 2326004878 not manager account

I use this SQL:

SELECT ad_group.id,geographic_view.resource_name, segments.geo_target_province, segments.geo_target_region, segments.geo_target_city, segments.geo_target_county, segments.geo_target_postal_code,segments.date, metrics.average_cost, metrics.average_cpc, metrics.conversions, metrics.conversions_value, metrics.clicks, metrics.ctr, metrics.impressions FROM geographic_view WHERE segments.date = '2021-11-14'

get tree items:

but google ads's background has 29 items

[Google Ads API Forum Advisor]

Hi,

Thanks for reaching out to us.

Upon checking the attached screenshots, could you provide us the screenshots translated in English labels in order to investigate further? Also, could you please provide us with the following details below, so that we can check better?

• Provide the complete request and response logs with request ID generated on your end
• Sample comparison of the Google ads UI and API where we can see the discrepancies

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

Regards,
Reminder: Share your feedback about the Google Ads (AdWords) API! Take the 2021 Google Ads API and AdWords API Annual Survey
 

Yasar Google Ads API Team

ref:_00D1U1174p._5004Q2R92V3:ref

[chaoming chen]

I use the official website laravel demo code, and the screenshot cannot be taken. The core code is as follows:

public function getAgeRangeReporting(GoogleAdsClient $googleAdsClient, $customer_id,?string $customer_name,$report_type=0)
{
if($report_type==1){
//get gender report
$sql = "SELECT gender_view.resource_name, segments.date, metrics.average_cost, metrics.average_cpc, metrics.active_view_cpm, metrics.conversions, metrics.conversions_value, metrics.clicks, metrics.ctr, metrics.impressions, ad_group_criterion.gender.type FROM gender_view WHERE segments.date = '{$this->date}'";
}else if($report_type==2){
//get geo report
$sql = "SELECT ad_group.id,geographic_view.resource_name, segments.geo_target_province, segments.geo_target_region, segments.geo_target_city, segments.geo_target_county, segments.geo_target_postal_code,segments.date, metrics.average_cost, metrics.average_cpc, metrics.conversions, metrics.conversions_value, metrics.clicks, metrics.ctr, metrics.impressions FROM geographic_view WHERE segments.date = '{$this->date}'";
}else{
//get age report
$sql =
'SELECT age_range_view.resource_name,'
. "segments.date,"
. "metrics.average_cost,"
. "metrics.average_cpc,"
. "metrics.active_view_cpm,"
. "metrics.conversions,"
. "metrics.conversions_value,"
. "metrics.ctr,"
. "metrics.impressions,"
. "metrics.clicks,ad_group_criterion.age_range.type "
. 'FROM age_range_view '
. "WHERE segments.date = '{$this->date}'";
}
// echo $sql."\r\n";
try {
$this->adsSql($googleAdsClient, $customer_id, $sql, function ($result) use ($customer_id, $customer_name,$report_type) {
/** @var GoogleAdsServerStreamDecorator $result */
foreach ($result->iterateAllElements() as $googleAdsRow) {

/** @var GoogleAdsRow $googleAdsRow */

$age_range_view = $report_type==1?$googleAdsRow->getGenderView():$googleAdsRow->getAgeRangeView();
if($report_type==2){
$age_range_view =$googleAdsRow->getGeographicView();
}

$resource_name = $age_range_view->getResourceName();
$segments = $googleAdsRow->getSegments();
$metrics = $googleAdsRow->getMetrics();

$group_info = explode('/', $resource_name);
$group_data = explode('~', $group_info[3]);
if($report_type==2){
$group_id = $googleAdsRow->getAdGroup()->getId();
}else{
$group_id = $group_data[0];
}


$money = 0;
$average_cpc = round($metrics->getAverageCpc() / 1000000, 2);

if ($metrics->getClicks() > 0 && $average_cpc > 0) {
$money = round($average_cpc * $metrics->getClicks(), 2);
}

$conversions = $metrics->getConversions();
$clicks = $metrics->getClicks();

$conversions_ctr = 0;

if ($clicks > 0 && $conversions > 0) {
$conversions_ctr = round(($conversions / $clicks) * 100, 2);
}

$group_info = DB::connection('mysql_ads')->table('group')->where('group_id', $group_id)->first(['group_name', 'customer_name']);

$metrics_data = [
'group_id' => $group_id,
'group_name' => $group_info->group_name,
'customer_id' => $customer_id,
'customer_name' => $group_info->customer_name,
'criterion_id' => $group_data[1],
'average_cost' => round($metrics->getAverageCost() / 1000000, 2),
'average_cpc' => round($metrics->getAverageCpc() / 1000000, 2),
'active_view_cpm' => !empty($metrics->getActiveViewCpm())?round($metrics->getActiveViewCpm() / 1000000, 2):0,
'conversions' => $conversions,
'conversions_value' => $metrics->getConversionsValue(),
'conversions_ctr' => $conversions_ctr,
'ctr' => round($metrics->getCtr() * 100, 2),
'clicks' => $metrics->getClicks(),
'impressions' => $metrics->getImpressions(),
'cost' => $money,
'historical_creative_quality_score' => $metrics->getHistoricalCreativeQualityScore(),
'historical_quality_score' => $metrics->getHistoricalQualityScore(),
'historical_landing_page_quality_score' => $metrics->getHistoricalLandingPageQualityScore(),
'historical_search_predicted_ctr' => $metrics->getHistoricalSearchPredictedCtr(),
'date' => $segments->getDate(),
'report_type' =>$report_type
];
if($report_type ==1){
$group_criterion = $googleAdsRow->getAdGroupCriterion();
//Google\Ads\GoogleAds\V8\Enums\GenderTypeEnum\GenderType
$gender = $group_criterion->getGender();
if($gender){
$gender_type = $gender->getType();
$metrics_data['gender_type'] = (int)$gender_type;
}
}else if($report_type == 2){
$city = $segments->getGeoTargetCity();
if($city){
$arr = explode('/',$city);
$metrics_data['city'] = $arr[count($arr)-1];
}
$province = $segments->getGeoTargetProvince();
if($province){
$arr = explode('/',$province);
$metrics_data['province'] = $arr[count($arr)-1];
}
}else{
$group_criterion = $googleAdsRow->getAdGroupCriterion();
//Google\Ads\GoogleAds\V8\Enums\AgeRangeTypeEnum\AgeRangeType
$age = $group_criterion->getAgeRange();
if($age){
$age_type = $age->getType();
$metrics_data['age_range_type'] = (int)$age_type;
}
}
DB::connection('mysql_ads')->table('group_age_rang')->updateOrInsert(
['group_id' => $group_id, 'criterion_id' => $group_data[1],'date'=>$this->date],
$metrics_data
);

}
});
} catch (\Exception $e) {
$error_data = json_decode($e->getMessage(), true);
echo $customer_id . 'get account age resource error' . $e->getMessage() . '--' . $e->getFile() . $e->getLine() . PHP_EOL;
if ($error_data['status'] === 'PERMISSION_DENIED') {
DB::connection('mysql_ads')->table('account')->where('customer_id', $customer_id)->update(['is_cancel' => 0]);
}
}
}

our database just tree 3 records but google ads background has 29 records 

why?

[chaoming chen]

[Google Ads API Forum Advisor]

Hi Chaoming,

Thank you for providing further details to your concern.

I am afraid that our team would not be able to investigate the issue with current details. With this, please provide instead the following details as it will greatly help in our investigation:

• complete request and response logs with request ID and request header generated on your end
• Screenshot of the UI (translated in English) where you are comparing the API result

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

Regards,

Reminder: Share your feedback about the Google Ads (AdWords) API! Take the 2021 Google Ads API and AdWords API Annual Survey
 

Ernie John Blanca Tacata Google Ads API Team

ref:_00D1U1174p._5004Q2R92V3:ref

[chaoming chen]

Another idea 

can you provide the results of customer ID: 2326004878 ran the follow SQL:

SELECT ad_group.id,geographic_view.resource_name, segments.geo_target_province, segments.geo_target_region, segments.geo_target_county, segments.geo_target_county, segments.geo_target_postal_code,segments.date, metrics.average_cost, metrics.average_cpc, metrics.conversions, metrics.conversions_value, metrics.conversions_value, metrics.clicks, metrics.ctr,metrics.impressions FROM geographic_view WHERE segments.date = '2021-11-14'

?

[Google Ads API Forum Advisor]

Hi Chaoming,

I was able to get a total of 9 records for the GAQL against the said customer id that you’ve provided with us. However, I can not provide you sensitive log results detail on this forum thread due to privacy reasons. From our end, our team would not be able to troubleshoot or further comment without first validating from the logs generated when comparing the API result with the UI. you may send to us via the Reply privately to author option.

Regards,
Reminder: Share your feedback about the Google Ads (AdWords) API! Take the 2021 Google Ads API and AdWords API Annual Survey
 

Yasar Google Ads API Team

ref:_00D1U1174p._5004Q2R92V3:ref

[Google Ads API Forum Advisor]

Hi Chaoming,

Unfortunately, we can’t share query results on the forum thread. Could you reach out to us on googleadsa...@google.com support alias along with the complete request and response logs with request ID generated on your end, so that we can share the result for the GAQL?

Also, note that I had to delete your last post on the forum thread as it contained your sensitive data in the image screenshot. 

Regards,
Reminder: Share your feedback about the Google Ads (AdWords) API! Take the 2021 Google Ads API and AdWords API Annual Survey
 

Yasar Google Ads API Team

 

ref:_00D1U1174p._5004Q2R92V3:ref