BigQuery Data Studio 自定义查询 [英] BigQuery Data Studio Custom Query

问题描述

我正在尝试将 BigQuery 中的自定义查询与数据洞察连接起来.我已阅读此处的指南:https://support.google.com/360suite/datastudio/answer/6370296?hl=zh-CN&ref_topic=6370347 但我还有几个问题.

I am trying to connect my Custom Query in BigQuery with Data Studio. I have read the guide from here: https://support.google.com/360suite/datastudio/answer/6370296?hl=en&ref_topic=6370347 but I have a few more questions.

我的查询应该提取过去 7 天的漏斗流数据，因此在 Data Studio 中使用自定义查询时，如果可能，我该如何编写以便 BQ 提取数据?

My query is supposed to pull last 7 days funnel flow data so when using custom query in Data Studio, how can I write in such a way that BQ will pull the data, if possible?

如果没有，我该如何修改我的查询，让数据洞察根据我在数据洞察中定义的日期范围从 BigQuery 中提取数据?我将在数据洞察中添加日期范围选择器.

If not, how can I modify my query in such a way that Data Studio will pull the data from BigQuery based on the date range I define in Data Studio? I will add the date range selector in Data Studio.

以下是我获取目标渠道数据的查询示例.

Below is the sample of my query to get goal funnel data.

SELECT
  s0.fullVisitorId,
  s0.visitId,
  s0.firstHit,
  s1.firstHit
FROM (
SELECT
    fullVisitorId,
    visitId,
    MIN(hits.hitNumber) AS firstHit
    FROM
    (TABLE_DATE_RANGE([xxx.ga_sessions_],
                DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'),
                CURRENT_TIMESTAMP()))
    WHERE
    REGEXP_MATCH(hits.page.pagePath, '/pageA/')
    AND totals.visits = 1
    GROUP BY
    fullVisitorId,
    visitId) s0
LEFT OUTER JOIN EACH (
SELECT
    fullVisitorId,
    visitId,
    MIN(hits.hitNumber) AS firstHit
    FROM
    (TABLE_DATE_RANGE([xxx.ga_sessions_],
                DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'),
                CURRENT_TIMESTAMP()))
    WHERE
    REGEXP_MATCH(hits.page.pagePath, '/pageB/')
    AND totals.visits = 1
    GROUP BY
    fullVisitorId,
    visitId) s1
ON
    s0.fullVisitorID = s1.fullVisitorID
    AND s0.visitID = s1.visitID

推荐答案

游戏迟到了，但这里是对任何其他评论者的回应:

Late to the game, but here's a response for anyone else reviewing this:

如果我正确理解您的问题，其要点是您希望能够在数据工作室中显示自定义查询的最后 x 天.如果这是问题，那么简单的答案是提供日期范围内的所有数据.即，查看全年的数据，然后让数据洞察使用此处描述的日期范围过滤方法将其过滤掉:

If I understand your question correctly, the gist of it is that you want to be able to display the last x days of your custom query in data studio. If this is the question, then the simple answer is to provide all data within a date range. i.e., Look at data for the entire year, then let Data Studio filter it out using the date range filtering method described here:

https://www.youtube.com/watch?v=Jafy-CB148k

我相信您的问题更多地与 Google 数据洞察有关，而不是 BigQuery 查询.我发现在使用 Data Studio 时，最好让您的查询保持相当简单，然后在 Data Studio 中过滤更复杂的操作，因为它使您的图表具有更多的可定制性.考虑到上述情况，假设您没有庞大的数据集，您的查询可能如下所示:

I believe your question has more to do with Google Data Studio than a BigQuery Query. I have found when using Data Studio it's best to keep your query rather simple and then filter in Data Studio for more complex operations as it leaves you with more customizability in your graphs. With the above in mind, assuming you don't have a ginormous data set, your query could look something like this:

SELECT
  s0.fullVisitorId,
  s0.visitId,
  s0.firstHit,
  s1.firstHit
FROM (
SELECT
    fullVisitorId,
    visitId,
    MIN(hits.hitNumber) AS firstHit
    FROM [xxx.ga_sessions_]
    WHERE
    REGEXP_MATCH(hits.page.pagePath, '/pageA/')
    AND totals.visits = 1
    GROUP BY
    fullVisitorId,
    visitId) s0
LEFT OUTER JOIN EACH (
SELECT
    fullVisitorId,
    visitId,
    MIN(hits.hitNumber) AS firstHit
    FROM [xxx.ga_sessions_]
    WHERE
    REGEXP_MATCH(hits.page.pagePath, '/pageB/')
    AND totals.visits = 1
    GROUP BY
    fullVisitorId,
    visitId) s1
ON
    s0.fullVisitorID = s1.fullVisitorID
    AND s0.visitID = s1.visitID

请注意，我删除了硬编码的日期范围，现在由数据洞察的日期过滤器来过滤您需要的数据.

Notice that I removed the hard-coded date ranges, it is now up to Data Studio's date filter to filter the data you need.

这篇关于BigQuery Data Studio 自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！