I have loaded google ads data to Big query using data transfer. I am trying to get keyword and search keyword metrics for my analysis

I used below code from p_ads_KeywordBasicStats table

WITH stats AS (   SELECT     campaign_id AS CampaignId,     ad_group_id AS AdGroupId,     ad_group_criterion_criterion_id AS CriterionID,     segments_ad_network_type AS AdNetworkType1,     metrics_clicks AS Clicks,     metrics_cost_micros AS Cost,     segments_date AS Date,     metrics_impressions AS Impressions   FROM `micro-reserve.google_ad.p_ads_KeywordBasicStats_xxxxxx    ), keywords AS (   SELECT     ad_group_criterion_criterion_id AS CriterionId,     MAX(ad_group_criterion_keyword_text) AS Criteria,     ad_group_criterion_negative,     ad_group_criterion_status   FROM `micro-reserve.google_ad.p_ads_Keyword_xxxxxxx      GROUP BY ad_group_criterion_criterion_id, ad_group_criterion_negative, ad_group_criterion_status ), campaigns AS (   SELECT     campaign_id,     MAX(campaign_name) AS campaign_name   FROM `micro-reserve.google_ad.p_ads_Campaign_xxxxx      GROUP BY campaign_id ) SELECT    Clicks,   Cost,   Date,   Impressions,   Criteria AS Keywords,   campaign_name,   ROUND(SAFE_DIVIDE(Clicks, Impressions) * 100, 2) AS CTR FROM stats LEFT JOIN keywords   ON stats.CriterionID = keywords.CriterionId LEFT JOIN campaigns   ON stats.CampaignId = campaigns.campaign_id WHERE keywords.ad_group_criterion_negative IS FALSE    AND keywords.ad_group_criterion_status = 'ENABLED'    AND Date BETWEEN '2024-09-01' AND '2024-09-13' 

========== Using below query for p_ads_SearchQueryStats table

WITH stats AS (   SELECT     campaign_id AS CampaignId,     ad_group_id AS AdGroupId,     ad_group_ad_ad_id AS CriterionID,     segments_ad_network_type AS AdNetworkType1,     SUM(metrics_clicks) AS Clicks,     SUM(metrics_cost_micros) AS Cost,     segments_date AS Date,     SUM(metrics_impressions) AS Impressions,     MAX(search_term_view_search_term) AS Criteria,     search_term_view_status   FROM `micro-reserve.google_ad.p_ads_SearchQueryStats_xxxxxx   WHERE _PARTITIONTIME = TIMESTAMP('2024-09-13')   GROUP BY ad_group_ad_ad_id, search_term_view_status, ad_group_id, campaign_id, segments_ad_network_type, segments_date ), campaigns AS (   SELECT     campaign_id,     MAX(campaign_name) AS campaign_name   FROM `micro-reserve=.google_ad.p_ads_Campaign_xxxxxxxx   WHERE _PARTITIONTIME = TIMESTAMP('2024-09-13')   GROUP BY campaign_id ) SELECT    campaigns.campaign_name,   stats.Date,   stats.Clicks,   stats.Impressions,   stats.Cost,   stats.Criteria AS Search_Keywords FROM stats LEFT JOIN campaigns   ON stats.CampaignId = campaigns.campaign_id WHERE stats.Date = '2024-09-13' 

The above query works, but the numbers are not matching with Google Ads. Is something I am doing wrong? Can anyone advise please?

Goal: I am trying to calculate the total spend (cost) of an ad from by Google Ads data that is in BigQuery

Problem: The output of my BQ SQL does not match the output I see in Google Ads reports

Example: In BigQuery, I grab the metrics_cost_micros from AdStats table and divide it by 1m for the time period. This tells me for 'Ad 1' the total cost is $3,625.76 and avg CPC $1.21

SELECT    a.ad_group_ad_ad_name,   SUM(metrics_cost_micros)/ 1000000 AS total_spend,   AVG(metrics_cost_micros)/ 1000000 AS avg_daily_spend,   MAX(metrics_cost_micros)/ 1000000 AS max_spend FROM `[project].google_ads.p_ads_AdStats_xxx` ast LEFT JOIN `[project].google_ads.p_ads_Ad_xxx` a    ON ast.ad_group_ad_ad_id = a.ad_group_ad_ad_id WHERE a.ad_group_ad_ad_name = "Ad 1" GROUP BY ALL 

However, in Google Ads reporting tool, the same ad in the same timeframe has a total cost of $107.53 and avg CPC of $0.38

I thought currency conversion might be an issue, but it doesn't account for the discrepancy

Disclaimer...I'm a Noob

I am writing a query from CampaignStats table that aggregates based on a stripped campaign label. The query returns correct values for all metrics except Impressions and Cost. No matter what I've tried so far, this figure still doesn't match. Here are the totals for two of my campaigns from yesterday (June 17th):

CampaignStats:

Date    label   Impressions cost    clicks  avg_cpc 6/17/2022   sat_brand   2687    140.472666  15  9.3648444 

CampaignBasicStats:

Date    label   Impressions cost    clicks  avg_cpc 6/17/2022   sat_brand   699 152.620961  15  10.17473073 

Utilizing the CampaignBasicStats table, I receive aggregated totals for all metrics that match the UI, including Impressions and Cost. The issue is there are metrics in CampaignStats and getting some illumination/information on what I may not be doing correct, will help in the future.

I did a JOIN with the Campaign table originally; the below query refers to a permanent table that I pulled out separately in case this was a cause of the discrepancy.

Code Below:

  SELECT     cs.Date,      EXTRACT(ISOWEEK FROM cs.DATE) AS isoweek,      cl.label,      (SUM(cs.Cost) / 1000000) AS cost,     SUM(cs.Clicks) AS clicks,      CASE WHEN SUM(Clicks)=0 OR SUM(Cost)=0 THEN 0 ELSE     ((SUM(Cost)/SUM(Clicks))/1000000) END AS avg_cpc,      SUM(cs.Impressions) AS Impressions,      CASE WHEN SUM(cs.Clicks)=0 THEN 0 ELSE     (SUM(cs.Clicks)/SUM(cs.Impressions)) END AS ctr,      SUM(cs.Conversions) as conversions,      CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Clicks)=0 THEN 0 ELSE     (SUM(cs.Conversions)/SUM(cs.Clicks)) END AS cvr,      CASE WHEN SUM(cs.Conversions)=0 OR SUM(cs.Cost)=0 THEN 0 ELSE     (SUM(cs.Cost)/SUM(cs.Conversions))/1000000 END AS cost_per_conversion FROM    `bold-quanta-######.######_google_ads_dataset.CampaignStats_##########` cs JOIN   `bold-quanta-######.queried_permanent_tables.process_campaign_labels` cl ON   cs.CampaignId = cl.CampaignId GROUP BY   1, 2, 3 

typicallyl in sql, you can use * to mutiply columns, but I am getting an error when trying to do so in my query below within GoogleAds API - anyone face this challenge before.

Code:

import sys, json, io, gzip, sys, os from googleads import adwords import pandas as pd import numpy as np def google_ads_extract(client,customer_id, s3_path,fields,report_type,statuses,date_range, download_version, job_name):   ga_service = client.get_service("GoogleAdsService")   search_request=client.get_type("SearchGoogleAdsStreamRequest")   search_request.customer_id = customer_id     query = """         SELECT           segments.date,           ad_group.id,           ad_group.name,           campaign.id,           campaign.name,           metrics.impressions,           metrics.clicks,           metrics.clicks*metrics.average_cpc as cost,           metrics.conversions,           metrics.ctr,           metrics.average_cpc,           metrics.cost_per_conversion         FROM ad_group         where segments.date BETWEEN 20220427 AND 20220428                   limit 10         """                 print(query)   search_request.query = query      stream = ga_service.search_stream(search_request)      for batch in stream:     for row in batch.results:       print(row)         return 1 

Error:

Request made: , Host: googleads.googleapis.com, Method: /google.ads.googleads.v8.services.GoogleAdsService/SearchStream, RequestId: TevDX_z7WYF-AWUZBMVbnw, IsFault: True, FaultMessage: Error in query: unexpected input *. Traceback (most recent call last): 

Google Ads API recently discontinued an old version - https://ads-developers.googleblog.com/2021/04/upgrade-to-google-ads-api-from-adwords.html.

That being said I am trying to adjust my query to new api standards, and getting the strangest error "unexpected input DURING" -- more detail below. It would seem to be correct per this doc:https://developers.google.com/google-ads/api/docs/samples/get-hotel-ads-performance

Note the use of "DURING" in the example SQL query in the doc above.

Below is my error message and further below is script:

ERROR:

SELECT Date, AdGroupId, AdGroupName, CampaignId, CampaignName, Impressions, Clicks, Cost, Conversions, ConversionRate, Ctr, AverageCpc, CostPerConversion FROM CRITERIA_PERFORMANCE_REPORT WHERE Status IN ('ENABLED', 'PAUSED') DURING 20220427,20220428 Request made: ClientCustomerId:, Host: googleads.googleapis.com, Method: /google.ads.googleads.v8.services.GoogleAdsService/SearchStream, RequestId:, IsFault: True, FaultMessage:  Error in query: unexpected input DURING. 

script:

import sys, json, io, gzip, sys, os from googleads import adwords import pandas as pd import numpy as np def google_ads_extract(client,customer_id, s3_path,fields,report_type,statuses,date_range, download_version, job_name):   ga_service = client.get_service("GoogleAdsService")   search_request=client.get_type("SearchGoogleAdsStreamRequest")   search_request.customer_id = customer_id   query = f"SELECT {', '.join(str(x) for x in fields)} FROM {str(report_type[0])} WHERE Status IN {*statuses,} DURING {date_range}"      # query = "SELECT Date, AdGroupId, AdGroupName, CampaignId, CampaignName, Impressions, Clicks, Cost, Conversions, ConversionRate, Ctr, AverageCpc, CostPerConversion FROM CRITERIA_PERFORMANCE_REPORT WHERE Status IN ('ENABLED', 'PAUSED') DURING 20220427,20220428"                 print(query)   search_request.query = query      stream = ga_service.search_stream(search_request)      for batch in stream:     for row in batch.results:       print(row)         return 1