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.
Step 2: Under the Develop drop-down, select the SQL Runner option to open the SQL interface.
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.
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.
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.
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.
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.