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.

Leveraging Google BigQuery functionalities with Looker
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.

Leveraging Google BigQuery functionalities with Looker
Figure 3-SQL interface along with required connection settings

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.

Leveraging Google BigQuery functionalities with Looker
Figure 10-Creating regional Sales chart.

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.

Figure 11-SQL query to get metadata information.

Figure 12-Metadata of ad campaign and sales North America.

Since we have put together the needed data now, the core data science team can use this for documentation.

We hope this blog helped you understand how to leverage BigQuery features from Looker.

Interested in implementing Google BigQuery using Looker? To learn more about Visual BI’s Google BigQuery & Looker Consulting, contact us here.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!