Beyond Dashboards: Act On Your Data With Looker Actions

[Francesco_V]

This blog was co-authored by Michael Hackel from Google Cloud.

Introduction

Looker is more than a powerful business intelligence (BI) tool. It’s a comprehensive data platform that breaks down the walls between insight and action, empowering business users like you to not only understand data but to act on it.

In the typical BI experience, you explore data, uncover trends, and may even identify opportunities, but then what? Often, you’re stuck exporting, emailing, or manually entering information into other systems to actually do something with those insights. Looker changes that by allowing you to:

• Trigger actions directly from your data: You can activate a marketing campaign or mark a transaction as fraudulent directly from Looker.

• Embed data experiences where you need them: You can bring the power of Looker into your existing workflows and integrate insights with actions in your applications.

If you are ready to move beyond passive dashboards and unlock the true potential of your data, learn how Looker can transform your business from insight to action.

Acting on your data

Looker actions enable a variety of use cases. One example is with churn analysis, where, after identifying high-value customers at risk, Looker can help you send personalized emails to them (for example, leveraging BigQuery and Gemini), sharing the list to your customer success team, and creating tasks on your CRM while updating a Slack channel.

This article focuses on how Looker can help write back to your data source, collecting inputs in context from your business users.

Let’s imagine a company with a complex pricing structure that involves discounts, tiered pricing, and customer-specific agreements. It’s crucial that all sales orders adhere to these policies to maintain profitability and avoid inconsistencies.

Here’s how Looker can help:

1. Policy Validation: Using the powerful data modeling capabilities of LookML, along with BigQuery machine learning (for example, anomaly detection), the controlling team defines rules that reflect the company’s pricing policies. These rules might flag orders with excessive discounts, unusual price combinations, or deviations from established customer agreements.

2. Automated Alerts: When an order violates a policy, Looker automatically generates an alert within the platform and/or sends notifications to the responsible team.

3. Actionable Insights: The controlling team investigates flagged orders directly within Looker, drilling down into details, examining customer history, and making sure the orders are following policy guidelines.

4. Workflow Integration: Looker can integrate with workflow tools or even directly with the order management system.

5. Feedback Loop: Actions taken by the controlling team are recorded and fed back into the data model, creating a closed-loop system for continuous improvement.

Multiple industries follow similar processes where it’s vital for stakeholders to analyze insights at a very detailed level and quickly act in context. For example:

• Fraud Detection: You can identify potentially fraudulent transactions based on anomalies, like unusual spending patterns, location discrepancies, or multiple accounts being linked to the same device.

• Inventory Management: You can analyze inventory levels and predict potential stockouts based on sales trends, lead times, and seasonality. With Looker Actions, you can generate purchase orders for high predicted demand items, or create new campaigns for slow-moving items.

Introducing the Looker Action Hub

The Looker Action Hub is like a command center for connecting your data insights to the tools and workflows that drive your business. It allows you to take action directly from Looker, turning insights into outcomes.

Looker’s Action API lets developers define custom actions or integrations with any language and deploy them to any web server.

Looker Action Hub is a convenient way to implement and deploy actions by implementing Action API specifications. The Looker Action Hub also provides a library of popular, pre-built actions for common business applications, making it easy to connect Looker to your existing tools. Many customers rely on the official Looker Action Hub cloud service, but you can also host an action hub on your infrastructure, where it can be deployed on virtual machines or containerized.

Next, let’s explore a serverless architecture that’s based on Cloud Functions and supports OAuth to write to BigQuery using user credentials.

The architecture behind a serverless custom action hub

Cloud Run Functions and Cloud Run are great for implementing the Looker Action API, as they both offer scalable and cost-effective ways to handle action requests, triggering custom logic or integrations with other systems without the need to manage server infrastructure.

To ensure proper integration with Looker, an API Gateway is required to guarantee that Cloud Functions’ calls are authenticated and secured. We recommend leveraging Apigee or any API gateway adopted by your organization. For less complex use cases, you might consider Google Cloud API Gateway.

We will use Secret Manager to store and rotate a few secrets.

In our example, we’ll create five cloud functions:

• Actions_List describes the actions exposed by our custom Action Hub as per the Looker Action API.

• Action_Form specifies a form for collecting user inputs.

• Action_Execute executes the actual action starting from values collected through the form.

• Action_OAuth_Initiate starts the OAuth authentication workflow.

• Action_OAuth_Callback retrieves the OAuth token and stores it in the user session in Looker.

In principle, each of these actions can be implemented and deployed with different technologies and languages depending on your requirements. While designing your architecture, consider the following:

• The user should be able to access the OAuth_Initiate and OAuth_Callback actions.

• The Looker instance should be able to access Actions_List, Action_Form, and Action_Execute.

• OAuth_Callback should be able to access the Looker instance to store the OAuth token in the user state.

For more details, reference the Looker Action Hub requirements in the Looker documentation.

How to implement the Looker Action Hub

If you are ready to get started with Looker Action Hub, we will guide you through a high-level discussion of important concepts that can help you better understand how the solution works. For more granular details, see our source code, available in this github repository.

ACTION_LIST - Defining a new action hub

As per the Looker Action API, to define a new action hub, we need an endpoint that lists all actions and instructs Looker on how to interact with them.

We define an action that can be triggered by users from a visualization (cell action). This action will define a form to gather a few parameters (form_url) and will be invoked using a URL that requires OAuth authentication.

   const action = {
           name: 'mark_as_suspicios',
           label: 'Mark as Suspicious (API / OAuth)',
           supported_action_types: ['cell'],
           url: ACTION_EXECUTE,
           form_url: ACTION_FORM,
           icon_data_uri: BASE_64_ENCODED_IMAGE,
           required_fields: [{ any_tag: ['mark-suspicious'] }],
           uses_oauth: true,
       };

We also instruct Looker’s semantic layer to associate our action to any LookML field that has the tag ‘mark-suspicious’.

The git repository also includes a description of a query action. This action can be triggered by sending a complete Looker Explore. Examples of query actions include sending a marketing audience to the appropriate channel for activation, or flagging a set of orders as suspicious.

Refer to the Actions List Endpoint documentation for more information on how to describe your actions.

Finally, we can associate a name to our custom action hub and return the list of actions to Looker:

const responseBody = {
           label: 'Mark Suspicious Orders Action Hub',
           integrations: [action],
       };
res.set('Content-Type', 'application/json');
res.status(200).send(JSON.stringify(responseBody));

Configuring a new Action Hub in Looker and enabling an action

To set up a new Looker Action Hub, navigate to the Admin menu. Within the Platform section, find the Actions page and choose Add Action Hub.

To collect all actions specified by the action hub, Looker will launch a new action hub area where you can enter the URL associated with the definition action.

To better protect your action hub, configure a secret. Select Configure Authorization to provide it. Looker will call all action hub actions, adding in the header a parameter as follows:

authorization : Token token="LOOKER_SECRET"

Finally, select Refresh to view all the actions within your action hub. Choose the action you want to activate, click Enable, and follow the prompts in the wizard.

Defining and invoking action form endpoints

To enable user input, such as flagging a transaction as suspicious, we establish a Cloud Function. This function generates a form that facilitates this interaction.

const formFields = [
		# select id from Looker request
           {
           name: 'id',
           label: 'Order ID',
           type: 'string',
           interactive: false,
           description: 'Order ID',
           default: req.body.data.value
           },
		# Provide a drop down to select from
           {
           name: 'classification',
           label: 'Classification',
           type: 'select',
           options: [
               { name: 'Approved', label: 'Approved' },
               { name: 'Suspicious', label: 'Suspicious' },
               { name: 'Further Investigation', label: 'Further Investigation Needed' },
           ],
           default: 'Suspicious',
           description: 'Select Order Classification',
           },
		# define a free text form for user comment
           {
           name: 'comment',
           label: 'Comment',
           type: 'textarea',
           description: 'Provide a comment to support your decision'
           }
       ];

Looker will query the endpoint every time a user interacts with a visual and selects the action. You’ll see it’s possible to dynamically populate the form with custom input fields. Refer to the Action Form Endpoint documentation for more details.

Before we add a few considerations about OAuth actions, let’s link our action through the “mark-suspicious” tag with the order_id object as follows:

dimension: order_id {
    label: "Order ID"
    type: number
    sql: ${TABLE}.order_id ;;
    tags: ["mark-suspicious"]
}

This configuration tells Looker to make the action accessible to business users whenever the dimension order_id is utilized, upon execution displaying the form definition that’s returned by our Action_Form.

How to support OAuth

To enable our users to utilize their OAuth credentials for writing back to BigQuery, some supplementary configuration steps are necessary.

1. CONFIGURE OAUTH CLIENT

   1. Configure an OAuth client. If you’re not familiar with how to create an OAuth client, please refer to this guide to creating OAuth authorization credentials for a Looker (Google Cloud core) instance (see the sections “Configure the user consent screen, scopes, and test users” and “Generate the OAuth client ID and client secret”).
   2. Store the client ID and client secret in Secret Manager and configure the Action_Form to access those secrets as variables (refer to this tutorial on configuring secrets for services).
   3. Add the callBack Action URL endpoint (the OAuth_Callback URL as exposed by your API Gateway) as an authorized redirect URI.

2. MANAGE THE STATE URL PARAMETER

   When an action is declared as supporting OAuth (uses_oauth: true), Looker sends a special parameter named state_url in the body of the request. As part of the authentication process, we’ll use this parameter to communicate and store the user OAuth token in the user state (check the document Sharing data through an action hub and the section Action User State in the Action API for further information).**

   The following interaction diagram exemplifies how the state_url is communicated and returned by the Action_Form:

   

   Given that the state_url parameter can change the Looker user state within an application, it’s crucial to encrypt this parameter when including it in the OAuth URL.

   // get state url sent by Looker
   const state_url = req.body.data.state_url;
   const state_string = JSON.stringify({state_url});
   const encrypted = CryptoJS.AES.encrypt(state_string,process.env.ENCRYPTION_SECRET).toString();
   const encode =  encodeURIComponent(encrypted);
   formFields.push({
       name: 'external_system_auth',
       label: 'Login with your Google Account',
       type: 'oauth_link',
       oauth_url: `${OAUTH_INITIATE}?state=${encode}`,
   });
   
   

   This code appends a login button to our form, allowing a user to start the OAuth authentication workflow by invoking the OAuth_initiate function.

3. THE OAUTH FLOW
   The OAuth flow is supported by two main functions:

• OAuth_Initiate takes the state encrypted by the Action_Form and passes it as part of the redirect_uri to ensure that the callback function receives it.

const state = req.query.state;
const authUrl = `https://accounts.google.com/o/oauth2/auth?client_id=${process.env.OAUTH_CLIENT_ID}&redirect_uri=${OAUTH_REDIRECT_URI}&response_type=code&scope=${OAUTH_SCOPE}&state=${encodeURIComponent(state)}`;
res.redirect(authUrl);

• OAuth_Callback retrieves the OAuth token and stores it in Looker, sending to the state_url an Action State that contains the user token. Refer to this example for more details.

const authCode = req.query.code;
const decoded_uri =  req.query.state;   
const decripted = CryptoJS.AES.decrypt(decoded_uri, process.env.ENCRYPTION_SECRET).toString(CryptoJS.enc.Utf8);
const state_callback = JSON.parse(decripted)['state_url'];
 
const jsonString = JSON.stringify({
      code: OAUTH_TOKEN,
      redirect: OAUTH_REDIRECT_URI
});


// send back to Looker
const response_looker = await fetch(state_callback, {
       method: 'POST',
       body: jsonString
});

This chart illustrates how the user, the action hub, and Looker interact. It also explains why the action hub needs to connect back to your Looker instance.

Once the OAuth token is stored in the user state, both the Action_Form and Action_Execute functions will receive the token as part of their request objects.

Defining and invoking action execute endpoints

Finally, we can define and execute the actual action that will write back your data to BigQuery. First of all, we retrieve the OAuth token from the user session and form parameters:

const json_state = JSON.parse(req.body.data.state_json);
const oauthToken = json_state.code;
// retrieve form data
const formData = req.body.form_params;

At this point, we can log in with user credentials and write back to BigQuery. Consider BigQuery best practices when you define your write back logic. For instance, you can consider an insert only table with your updates and rely on Looker’s semantic layer to seamlessly provide a unified experience to your user.

Finally, we can inform the Looker UI that the write back was successful and ask Looker to update the Look to show the update state of the data to your user.

res.json({
     looker: { success: true, refresh_query: true },
     message: 'Data updated successfully',
 });

The provided git repository includes a sample query action called action_execute_batch. This action demonstrates how Looker can send both the data and the underlying query from an Explore. This functionality is particularly useful for managing large datasets, for example, by utilizing BigQuery’s serverless architecture.

Taking your own Looker actions

Now that you have seen how Looker empowers organizations to act on their data by enabling workflows directly triggered from insights found in Looker dashboards or during data exploration, and learned the key aspects of implementing a Looker Action that supports OAuth, offering a complete example of an action capable of writing back to BigQuery, we recommend deploying the solution available in the git repository to understand how each component contributes to the process. Once you get started, you will be able to customize the solution to fit your specific use case.