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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
<!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.
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.
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.
Does it allow us to modify the unsubscribe link in the email? Looker email provides a link to unsubscribe from the schedule. However, we would like to add our own link or modify this link to navigate to our own webpage to handle the subscription. Is there a way to do so using this integration?
It seems Zapier free version does not allow Webhook integration any more. Need to have the premium version