BigQuery Data Studio Custom Query

Question

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.

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?

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

You mean retrieving data from BigQuery using a date range not previously defined in your query? such as, for instance, using a date range from the previous month when your query process only the last 7 days? — Willian Fuks
Hi @Will to clarify I want it in a way that I can pull out data in Data Studio for any date range without having to edit date range selectors in my query all the time. Not sure if it is even possible to do so though. — zkh
See BigQuery parameterized queries: cloud.google.com/bigquery/querying-data — reprogrammer

klfut klfut · Accepted Answer · 2019-06-13T04:02:47

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

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

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 Custom Query

1 Answers