转移服务数据差异:Google Ads(AdWords)<>大查询 [英] Transfer Service Data Discrepancy: Google Ads (AdWords) <> BigQuery

问题描述

有人从Google Ads数据传输到BigQuery的数据有问题吗?

Has anyone had issues with data from Google Ads data transferred to BigQuery?

我使用了Google Ads传输服务，并通过查询来验证传输的数据时，发现在Google Ads Web门户中看到的内容与已传输到BigQuery的内容之间存在差异.一些差异非常严重.

I used the Google Ads transfer service and while validating the transferred data by querying it, I noticed discrepancies between what I see in the Google Ads web portal and what has been transferred to BigQuery. Some of the discrepancies are quite egregious.

我已经联系了Google Cloud支持，BigQuery专家电话支持，Google Ads API团队，他们每个人都将我引荐给其他人.我希望我可以在此联系一位工程师，或者可以澄清此问题的人预期与否.

I have contacted Google Cloud support, BigQuery specialist phone support, Google Ads API team, and every single one of them has pointed me to someone else. I'm hoping that I can reach one of the engineers here or someone who can clarify if this is expected or not.

虽然点击和费用数据大部分都还可以(不能完全消除差异)，但是没有一天印象数据与我在网上看到的数据相匹配.差远了.据我了解，在处理所有数据时，最近的数据可能会有所变化，因此我也查看了过去的数据.

While click and cost data is mostly ok (not completely clear of discrepancies), there is not one day in which the impression data matches with what I see online. Not even close. It's my understanding that recent data can change a bit while all of the data is processed, so I looked at data from the past too.

我什至尝试说明UTC与美国东部之间的时区差异.

I even tried account for timezone difference between UTC and Eastern US.

以下是我使用的两个查询.第一个分组是按天分组，另一个分组是试图查看是否调整了时区，这有什么区别.

Below are two queries I used. The first one grouping by day and the other one trying to see if adjusting for timezone made any difference.

#standardSQL
SELECT 
  _PARTITIONTIME as pt,
  SUM(Impressions) AS Impressions,
  SUM(ActiveViewImpressions) AS ActImpressions,
  SUM(Clicks) AS Clicks,
  (SUM(Cost)/1000000) AS Cost
FROM
  `12345.supermetrics.p_AdStats_[ACCT_ID]`
GROUP BY
  pt
ORDER BY
 pt DESC

.

#standardSQL
SELECT
  newDate,
  SUM(Impressions) as Impressions,
  SUM(Clicks) as clicks,
  SUM(Cost) as Cost
FROM (
  SELECT 
    Impressions,
    Clicks,
    (Cost / 1000000) as Cost,
    CAST(TIMESTAMP_ADD( TIMESTAMP(Date), INTERVAL (HourofDay - 5) HOUR) AS DATE) AS newDate
  FROM
    `12345.supermetrics.p_HourlyAdGroupStats_[ACCT_ID]`
)
GROUP BY
  newDate
ORDER BY
  newDate ASC

推荐答案

我发现有差异的两个原因:

I found two reasons for discrepancies:

1. 回填的结束日期设置不正确:这是专有的"，也就是说，这是第一天，不会提取任何数据.
2. 转换"窗口:Google Ads Transfer的标准刷新窗口为7天.如果在Google Ads中将窗口设置为标准30天，则单击后7到30天后发生的任何转化都不会出现在BQ中.这也可能会影响Google稍后更改的其他数据，例如点击欺诈造成的展示次数，点击次数和/或费用.

您可以在传输设置中调整刷新窗口.如果设置为1，则没有每日回填.值0表示默认刷新窗口(7天).

You can adjust the refresh window in the transfer settings. There is no daily backfill if set to 1. Value 0 indicates default refresh window (7 days).

这篇关于转移服务数据差异:Google Ads(AdWords)<>大查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！