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

问题描述

我正在尝试将BigQuery中的自定义查询与Data Studio连接起来.我已经从这里阅读了该指南: 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?

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

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:

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

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 Data Studio的关系要大于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

请注意，我删除了硬编码的日期范围，现在由Data Studio的日期过滤器来过滤所需的数据.

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屋！