Google Data Studio: Apply custom dimension as filter using Google BigQuery data connector

Question

Do not know if Stackoverflow is the right platform to ask this question, as the technical nature might be limited. I am working on setting up a dashboard using the BigQuery data connector 'ga_sessions_YYYMMDD' connection. This connection has a couple of custom dimensions set up in Google Analytics, which I can access in the BigQuery Console, hence I know they exist and contain data. Two of these custom dimensions are Language (the language the page is being displayed in) and Country (the locale the visitor is visiting). They have a custom dimension index of 4 and 5 respectively.

I am trying to make the values from these custom dimensions FILTERS in my reports which apply to other widgets in my dashboard. I try to do this by adding a report filter based on dimension: 'customDimensions.value' and applying a filter within this report filter with 'customDimensions.index' equals 4. But this method does not work.

Can anyone help me with the correct way of setting up a custom dimension as a report filter in Google Data Studio using the Google BigQuery datasource?

I have tried a lot of things and keep asking this question, but no one seems to be able to help me. To summarize, I am simply trying to make a report filter using a custom dimension from BigQuery.

Help is hugely appreciated!

Joost

Answer 1

Your query should be compiled into a view, then the view used as datasource. From there on you use the view as it was a table.

SELECT
fullvisitorid,
( SELECT MAX(IF(index=1,value, NULL))FROM UNNEST(hits.customDimensions)) AS CustomDimension1,
( SELECT MAX(IF(index=2,value, NULL))FROM UNNEST(hits.customDimensions)) AS CustomDimension2
FROM
  `XXXXXXX`, unnest(hits) as hits

Answer 2

In data studio you can use something like case when customDimensions.index=1 then customDimensions.value end when creating a new dimension.

You just need to make sure that customDimensions.index has aggregation set to "None" beforehand. Otherwise you'll get the dimensions/metrics mix error.

It's basically the same in BigQuery:

SELECT
  (SELECT value FROM t.customDimensions WHERE index=1) cd1
FROM `project.dataset.ga_sessions_201712*` AS t

or on hit scope:

SELECT
  (SELECT value FROM h.customDimensions WHERE index=1) cd1
FROM `project.dataset.ga_sessions_201712*` AS t, t.hits AS h

If you want to understand the queries DataStudio is writing, you can see them in BigQuery query history of the connected (billing) project (if it is creating the query jobs in your name).