Overview
In this tutorial you will learn how to write BigQuery SQL query and then use it as a custom query to create a Data Source in the Google Data Studio.
To understand the following steps it makes sense to gain an understanding of the structure of your data Ingenious provides in BigQuery.
...
The data in the fact tables are clearly structured and have a defined scheme. But it doesn’t contain much information about the master data like Advertiser’s name or URL. Instead it contains only IDs of master data. In order to present the Advertiser with its label instead of ID, it is necessary to to combine (JOIN) operational data with master data. During a JOIN, rows in two tables which have matching IDs are combined in a single row. Several reporting tools can do this immediately in the Report, but this feature is not available in most free tools. In example, Data Studio can combine (BLEND) only data sources with the same set of IDs and filters, which doesn’t allow to join at the same time multiple master data tables, i.E. Advertisers using advertiserId and Conversion Targets using targetId.
Building the Query step by step
For better clarity we will build the Query bottom up using Common Table Expressions (CTE, see https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with-clause) which looks like
...
This allows us to threat the query like a pipeline and go step by step from simply to more complicated query.
1. Filter the fact table by partitioning field
With the first query you can generally access fact tables. In most cases, fact tables in BigQuery are partitioned to increase query efficiency and reduce costs. Therefore, it is always necessary to specify a restriction in the "WHERE" part of the SQL query. Your fact tables are partitioned by date, i.E. the table finance_conversion is partitioned by the date of tracked timestamp trackedAt.
...
In order to test this and following queries in a BigQuery console, replace insights_999 by the name of dataset containing your data
2. Join a dimension to the fact table
It makes no sense to look at fact tables only. It is necessary to add more dimensions to the fact tables.
...
This query has no conflicts since the networkId form the fact table can be addressed directly, and the networkId from the network dimension table is encapsulated and accessible as net.networkId in the query result.
3. Join multiple dimension tables
Now we can join other dimension tables similar to previous section:
Code Block |
---|
WITH fact_raw AS ( SELECT * FROM `ingenious-169318.insights_999.finance_conversion` WHERE DATE(trackedAt) between '2020-01-01' and '2020-01-01' -- YYYY-MM-DD LIMIT 100 ), net AS ( SELECT networkId, name, logoUrl, CONCAT(name, ' (', networkId, ')') as label FROM `ingenious-169318.insights_999.network` ), adv AS ( SELECT id, status, name, nameEnglish, websiteUrl, CONCAT(name, ' (', id, ')') as label FROM `ingenious-169318.insights_999.advertiser` ), ct AS ( SELECT id, status, label as tech_label, name, CONCAT(name, ' (', id, ')') as label FROM `ingenious-169318.insights_999.conversiontarget` ), tcat AS ( SELECT id, categoryTitle, status, visibility, alias, CONCAT(categoryTitle, ' (', id, ')') as label FROM `ingenious-169318.insights_999.trackingcategory` ), fact AS ( SELECT * --> 4 FROM fact_raw ) SELECT fact.* ,( SELECT AS STRUCT net.* ) AS net ,( SELECT AS STRUCT adv.* ) AS adv ,( SELECT AS STRUCT ct.* ) AS ct ,( SELECT AS STRUCT tcat.* ) AS tcat FROM fact LEFT JOIN net ON fact.networkId = net.networkId LEFT JOIN adv ON fact.ownerId = adv.id LEFT JOIN ct ON fact.targetId = ct.id LEFT JOIN tcat ON fact.categoryId = tcat.id; |
4. Join dimension into REPEATED field of fact table
When we try to join the Ad Space dimension the way we did above, we will find that there is no adspaceId we can use for JOIN on the top level. The adspaceId is part of a nested REPEATED field fact.touchpoint, instead and can’t be addressed directly for the JOIN. In order to add dimension data there, we have to UNNEST the repeated field, enrich it with the master data via JOIN and combine the result into a new repeated ARRAY field. The following query will show how to do it. For simplicity, we omit joining the master data on the top level we did in the previous section.
...
Left joins not allowed here, causes error: "Query error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN"
5. Extend dimension with additional master data
A deeper look into a part of the Query in 4. shows that the Ad Space dimension contains some id columns without additional data:
...
Code Block |
---|
WITH partner AS ( SELECT id, entityId, prename, name, countryCode, companyName, status FROM `ingenious-169318.insights_999.partner` ), cat AS ( SELECT networkCategoryId, label, status, FROM `ingenious-169318.insights_999.networkcategory` ), tc AS ( SELECT trackingChannelId, name, type, CONCAT( name, ' (', trackingChannelId, ')') as label FROM `ingenious-169318.insights_999.trackingchannel` ) SELECT _ads.id, _ads.name, _ads.status, _ads.websiteUrl, _ads.categoryId, _ads.partnerId, _ads.trackingChannelId, CONCAT(_ads.name, ' (', _ads.id, ')') as label, ( SELECT AS STRUCT _cat.* ) as cat, ( SELECT AS STRUCT _partner.* ) as partner, ( SELECT AS STRUCT _tc.* ) as tc FROM `ingenious-169318.insights_999.adspace` _ads JOIN partner _partner ON _ads.partnerId = _partner.id JOIN cat _cat ON _ads.categoryId = _cat.networkCategoryId JOIN tc _tc ON _ads.trackingChannelId = _tc.trackingChannelId; |
6. Merging extended dimensions into fact query (putting 4. and 5. together)
The next query selects the facts from the fact data table and adds AdSpace dimension to the REPEATED field, additionally the AdSpace dimension itself is extended by additional information related to the Partner, Category and Tracking channel IDs contained in the AdSpace data:
...
Similarly, other REPEATED fields (or STRUCTS) in the fact table or dimension tables can be enriched by dimension data using the same approach.
7. Putting it all together (6 plus top level dimensions)
Now we add the dimensions related to the columns in the fact table as shown in “3. Join multiple dimension tables” and get our whole query
...