如何多列关联Google Analytics广告表和交易表统计对应交易数

你的查询存在的问题 #

• 外层SELECT语句仅选取了广告表子查询A的字段，未引用交易表子查询B的统计结果，因此返回结果缺少交易数列
• 子查询B中的COUNT(Transaction_ID)没有设置别名，即使尝试引用也无法找到对应字段
• 你单独写的交易数统计SQL中，campaign_ID字段后遗漏了英文逗号，存在语法错误
• 你当前使用的是INNER JOIN，会过滤掉有广告投放但无交易的date+campaign_ID组合，不符合广告效果分析的常规需求

修正后的单条查询语句 #

SELECT 
  A.newdate AS Date,
  A.CID AS Campaign_ID,
  A.AD_Click AS Clicks,
  A.AD_I AS Impressions,
  A.AD_COST AS Cost,
  -- 引用B表的交易统计结果，无交易时默认置0
  IFNULL(B.Count_Of_Transactions, 0) AS Count_Of_Transactions
FROM (
  SELECT 
    Date AS newdate, 
    Campaign_ID AS CID, 
    SUM(Impressions) AS AD_I, 
    SUM(Clicks) AS AD_Click, 
    SUM(Cost) AS AD_COST
  FROM `bqproject.tables.ga_ads_data`
  GROUP BY Date, Campaign_ID
) A
-- 改用LEFT JOIN保留所有广告投放记录
LEFT JOIN (
  SELECT 
    Date, 
    Campaign_ID, 
    COUNT(Transaction_ID) AS Count_Of_Transactions
  FROM `bqproject.tables.ga_ecom_data`
  GROUP BY Date, Campaign_ID
) B 
ON A.newdate = B.Date
AND A.CID = B.Campaign_ID
LIMIT 1000

补充说明 #

如果确实只需要同时存在广告数据和交易数据的匹配记录，把语句中的LEFT JOIN改回JOIN即可。

内容的提问来源于stack exchange，提问作者Mark Pitchford