sql - 从谷歌大查询中获取广告词的电话和电话印象

Question

我正在将 google adwords 数据同步到 google big Query 存储系统。我可以通过对给定日期使用以下查询来获取关键字效果数据。但我想另外获取另外两列，例如 PhoneCalls 和 PhoneImpression，我尝试加入一些表。但是，无法将计数与带有查询结果的 adwords 门户报告相匹配。任何帮助将非常感激。

SELECT
  cs._DATA_DATE, c.ExternalCustomerId, c.Criteria,
  camp.CampaignName, adg.AdGroupName, c.ApprovalStatus,
  cs.Device, cs.AveragePosition,
  if ( SUM(clk.Clicks) != 0, SUM(clk.Clicks ), 0) AS PhoneCalls,
  if ( SUM( cs.Impressions ) != 0, SUM(cs.Impressions ), 0) AS Impressions,
  if ( SUM( cs.Interactions) != 0, SUM(cs.Interactions), 0) AS Interactions,
  if ( SUM( cs.Cost) != 0, SUM(cs.Cost/1000000), 0) AS Cost,
  if ( SUM( cs.Conversions) != 0, SUM(cs.Conversions), 0) AS Conversions
FROM `iso-gps.GLOBALPAYMENT.Keyword_XXXX` c
LEFT JOIN `iso-gps.GLOBALPAYMENT.ClickStats_9640337266` clk ON
  ( clk.CriteriaId = c.CriterionId
    AND c.CampaignId = clk.CampaignId
    AND c.AdGroupId = clk.AdGroupId
    AND clk._DATA_DATE='$Date'
    AND clk.ClickType ='CALLS' )
LEFT JOIN `iso-gps.GLOBALPAYMENT.KeywordBasicStats_XXXX` cs ON
  ( c.CriterionId = cs.CriterionId
    AND c.CampaignId = cs.CampaignId
    AND c.AdGroupId = cs.AdGroupId
    AND cs._DATA_DATE='$Date' )
INNER JOIN `iso-gps.GLOBALPAYMENT.Campaign_XXXX` camp ON
  ( camp.CampaignId = c.CampaignId
    AND camp._LATEST_DATE = camp._DATA_DATE)
INNER JOIN `iso-gps.GLOBALPAYMENT.AdGroup_XXXX` adg ON
  ( adg.AdGroupId = c.AdGroupId
    AND adg._LATEST_DATE = adg._DATA_DATE)
WHERE c._DATA_DATE = c._LATEST_DATE
GROUP BY 1,2,3,4,5,6,7,8
ORDER BY cs._DATA_DATE DESC

标签： sqlgoogle-bigquerygoogle-ads-api