Sending Custom Emails with Looker Data: Looker + Zapier Integration


(Max Reid) #1

Sending Custom Emails with Looker: Looker + Zapier Integration

While Looker’s built-in emailing functionality solves a majority of emailing use cases, suppose you want to send out an email externally in an email template that reflects your branding, while still leveraging Looker’s alerting functionality and governed metrics. Due to Looker’s flexibility in programmatically sending out data, you can leverage Looker + Zapier to send data to the 1000+ integrations that Zapier has built-in. In this case, we will use the SendGrid Action within Zapier to send custom emails, which requires a free SendGrid account and a free Zapier account.

Sample Use Case

An events company wants to send out information about sales, orders, and profit externally to the representatives for that event, the day after an event is completed. Before Looker, this process involved extracting data and sending it via email manually as a spreadsheet to the representative. Now, this process is completely automated by following the below steps.

A. SETTING UP THE LOOKER REPORT AND ZAPIER INTEGRATION

In this part, we will set up the connection between Looker and Zapier, to get the data from Looker into Zapier in a format that makes it easy to incorporate into custom emails.

Pre-requisites:

  • Look with the relevant information: In the Look, there should be a recipient email, as well as any metrics and information you want to include in the email on the same row. Note that each recipient’s metrics need to exist within a single row.
  • Zapier account: if you do not have one already, you can sign up for free here.
  • SendGrid account: if you do not have one already, you can sign up for free here.

Step 1: Creating a Look [Looker]

Create a Look with the relevant information following guidelines above.

In the below example, I’ve created a Look with the contact’s email address, name, the event name, the event date, and some metrics about that event.

Step 2: Creating the Zap [Zapier]

Open up Zapier in a new tab and login to your account. Click “Make a Zap!” in the top right corner of the page, and at the Choose an App step, type in Webhook in the search bar (see the screenshot below). See Zapier’s documentation on how to create a Zap manually.

In the “Choose Trigger” Step, select “Catch Hook” – this tells Zapier to wait for a schedule from Looker before taking the action.

Then, click “Copy to Clipboard” on the “Set Up Webhook” step.

You’ll be prompted to Pick a Child Key – type in ‘data’. This tells Zapier to only look at the data coming from Looker, as opposed to the other metadata (such as user, time of query, etc.). You can also name your Zap in the top left of the page – I named this one “Ticket Sales Update”

Click “Continue” to go to “Test This Step” and, before clicking “Ok I did this”, switch back to your Looker tab.

Step 3: Sending a Sample Webhook for Testing [Looker]

Switch back to the Looker tab and, from Looker, create a schedule with Webhook as the destination and JSON - Simple, Inline as the format. In the destination, paste that same Webhook we had copied from Step 2 above. Once you do that, click the “Send Test” button in the screenshot below.

Step 4: Ensuring the Webhook delivered successfully [Zapier]

Switch back to the Zapier tab to Test This Step, and after clicking “Ok I did this”, you should see a list of sample hooks that Zapier pulled in. In the below screenshot, you see three hooks because there are three lines of data.

Note that this step is just for testing, so as long as Zapier receives the hook and you see the underlying data for a single row when clicking on a hook, it does not matter which hook you choose to set up the Zap, as it will iterate through all rows once fully set up. I am going to choose Hook A for this example.

B. SETTING UP THE SENDGRID ACTION

Now that we’ve successfully captured the data from Looker into Zapier, we will set up a SendGrid action to turn that information into a customizable email. Note that while we are using SendGrid for this example, now that you’ve captured that data in Zapier you can send it to any of Zapier’s 1,000+ app integrations.

Step 5: Creating the SendGrid Action [Zapier]

Click the Add a Step button in Zapier, and click Action/Search. Search for SendGrid – this is what we will use to distribute the emails.

Click Continue, then click Continue at the “Choose Action” step with Send Email selected.

For Choose Account, login using your SendGrid credentials, then click Save + Continue. If you don’t have a SendGrid account, you can sign up for a free one here.

Step 6: Creating the SendGrid Email Template [Zapier]

At the Set Up Template step, you can now set up the exact email template you’d like to use, while leveraging the information from Looker. By clicking the button highlighted in screenshot below, you can choose which parts of data payload you want to insert in the To, From, From Name, Reply To, Subject, and Body.

  • NOTE: for the body of the message, you can either type into the Text section or use any custom HTML formatting in the HTML area, while incorporating data points from Looker.
  • NOTE: only one hook will show up here (the same hook we selected in Step 4) because this is just a template. Once this is set up, it will iterate through every row of data.

Final SendGrid template:

Below, you’ll see an HTML email template with different metrics and data inserted at different parts of the code. HTML is the standard language used for email templates - if your organization already has a standard one, you can leverage that here and replace placeholder values with the data points from Looker, the same way I did for the other parts (To, Subject, etc.) of the SendGrid email template above.

  • NOTE: If you don’t have a standard HTML email template you use as an organization, you can get the HTML behind any email in Gmail by clicking “Show original” in the top right corner of any email, copying the code from there below <!DOCTYPE html>, and then decoding it using a website like this one. To do that, paste the HTML from above in the bottom section of the webpage, click decode, and copy the decoded version and insert it into Zapier. Additionally, you can create email templates directly in SendGrid.

Once you’ve written your email framework or put in the HTML, go to Test This Step and Click “Send Test to SendGrid” and Zapier will let you know if the message delivered successfully. If it does, you are ready to turn on the Zap in the top right of the page.

Step 7: Creating the schedule [Looker]

Once you do that, go back to your tab with the Looker report open, and since the scheduler is still up, set that up to delivery to that same Webhook address from Step 3 at whatever cadence you desire.

Bringing it back to our sample use case, the Customer Service team can schedule this from Looker to run daily, filtered down to events that happened the day before, so they now no longer have to manually compile Event KPIs and email spreadsheets to their customers - Looker will send these automatically each morning.

  • Note: you can follow same steps but leverage Looker’s alerting capabilities by utilizing the Advanced options section of the scheduler documented here. Additionally, here is a video walkthrough of our alerting. For the above use case, one application could be the Customer Service team filtering based on a certain profit threshold, and sending a congratulatory note automatically if that profit threshold is met.

Output

This email has parameterized information in the Subject (“Birthday Party”), as well as each of the rows in the HTML table in the body of the email. If I send the Webhook from Looker, an email similar to the one below will be generated and sent for each row of data, parametrized to the recipient.

By leveraging our integration with Zapier, you can programmatically send emails, backed by Looker, with your own custom branding, while taking advantage of Looker’s alerting capabilities and governance. While we used SendGrid here, Zapier has a robust library of integrations you can now leverage following similar steps.