Leveraging Google BigQuery functionalities with Looker

In today’s data-oriented life, it is essential to have a modern enterprise data warehouse like Google BigQuery along with a data visualization tool like Looker to create a story based on the data for actionable insights. This blog briefs about two different real-time business scenarios which use Google BigQuery and Looker.

We are considering the following features to showcase how these BigQuery features can be leveraged when connected with Looker.

Accessing wildcard tables
Accessing metadata of the dataset

We will be using enterprise ad campaigns and sales data of four different regions to see how these BigQuery features can be leveraged in Looker. Data consists of attributes like Campaign ID, Region, Sales, Campaign Costs in various marketing channels. Regional managers have access to data specific to their regions. To examine the overall success of the ad campaign, data scientists will need data from all regions in a single table. To help data scientists have a combined view of data of all regions, we will be using the wildcard table feature.

Accessing wildcard table feature in Looker

Step 1: Establish a connection between the Google BigQuery project and Looker, then we can proceed by logging into Looker and selecting the Develop option.

Figure 1: Looker Interface

Step 2: Under the Develop drop-down, select the SQL Runner option to open the SQL interface.

Leveraging Google BigQuery functionalities with Looker — Figure 2-SQL runner option

Step 3: After opening the SQL interface, we select our BigQuery connection, project name, and dataset name.

Step 4: Once the required connections are configured, we can write our SQL query in the interface and hit enter to see the query results.

Figure 4-SQL query used to view combined data of all the regions without creating a separate table

Figure 5- Results of SQL query can be viewed in a tabular format right below query interface

Step 5: After checking the results of the query, we can add it to our LookML Project by clicking on the Add to Project option in the Settings menu.

Figure 6- Settings option in the SQL interface

Figure 7- Adding query to pre-configured LookML project.

Step 6: After adding it to the LookML project, we validate the LookML model, commit the changes and push to git, post which we will be adding the view to the BigQuery_training model and deploying it.

Figure 8- ad campaign and Sales wildcard view

Figure 9- View added to the BigQuery_training model

Step 7: We will now be visualizing our ad campaign data to analyze how the campaign has impacted sales in various regions. We will be selecting the browse option and navigating to the user folder to create the dashboard. Click on Add Tile, and then we will create a bar chart to visualize regional sales. We can see that data is displayed from all the four region tables.

Accessing metadata of the dataset

After loading and checking regional sales, one of the data scientists from the team was keen on finding out metadata information of North America’s ad campaign for documentation purposes. To access the metadata of the ad campaigns, we will be using the SQL query runner. We will follow all the steps mentioned earlier in accessing the wildcard feature and then open the SQL query runner to access the metadata.

In the SQL query runner, we will be using the below query to obtain metadata information.