Using Looker’s API and Airflow to send feedback request emails to users

At Milk Bar, we use Looker to serve up business intelligence across our company. Looker is our data buffet, and I expect our department heads to be able to self-serve the majority of their data requests using Looker. It’s been immensely popular, but I’ve also noticed that some people are slower to adopt Looker than others. Why might this be?

Looker provides some helpful usage charts in Admin > Usage via the i__looker Explore. Using this Explore, I can monitor usage across the company, but it’s hard to know why someone with low usage is not using the tool. I believe if someone isn’t using Looker at least once a week they either…

• Don’t have the correct data, Explores, or dashboards in Looker to make using the tool valuable and worth their time
• Had a bad experience with the tool in some way (they encountered incorrect data or perhaps are still confused about how to use Looker)
• Don’t need the tool frequently enough in their role to justify a license

When we only had a handful of Looker users, I would email each one, ask them for their feedback, and offer to meet with them if they had requests for enhancements or needed more training. As more and more people at Milk Bar get trained on Looker and start using the tool, I’m finding that approach is not scalable and is simply too time-consuming.

Hoping I could build an automated solution, I thought it should be theoretically possible to create an Airflow DAG that emails each Looker user at the start of each month and asks for their feedback. I thought I could use Looker and its API to determine which users are inactive, and send a slightly more targeted email to them to find out what might be holding them back.

Setting up the user activity Look

To get started, I set up a Look using the i__looker explore to show me how many days it had been since each user’s last query. The Look has these four columns:

• First Name (Dimension)
• Email (Dimension)
• Most Recent Query (Measure)
• Days of Inactivity (Table Calculation)

I added the First Name and Email dimensions to populate the email template later. I also added the Most Recent Query measure to calculate inactivity.

Last, I added a Table Calculation called Days of Inactivity to calculate the number of days since each user’s most recent query. It looks like this:

diff_days(${history.most_recent_query_date}, now())

I saved this setup as a Look with a table visualization and noted the Look ID for the API later (the number at the end of the Look’s URL).

Getting the Look’s data via Looker’s API

Looker provides a handy API that can do almost anything you can do in the web interface. I used the API to pull the results of the user activity Look in my Airflow DAG. The first step is to generate credentials (client_id and client_secret) for yourself, which you can do by editing your user in the Looker Admin section and adding an API key as described here.

I used the Python SDK for the Looker API, which you can install according to this Discourse post using the PyPi package lookerapi. I followed the steps in the Discourse post to authenticate using my previously obtained credentials.

Looker’s Run Look endpoint unfortunately doesn’t seem to return valid JSON at the moment (it single-quotes the fields instead of double-quoting them), so I opted for a CSV-formatted result instead.

I used the following code to convert the CSV string into a list of dictionaries, which I thought would be the easiest format to work with later on.

result = look_api.run_look(133, 'csv')
rows = [row.split(',') for row in result.strip().split('\n')]
header = [column.lower().replace(' ', '_') for column in rows.pop(0)]
users = []
for row in rows:
    user = {}
    for column, value in zip(header, row):
        user[column] = value
users.append(user)

Building our Airflow DAG

I created a simple DAG with two tasks: get_users and email. I used the cron-style schedule_interval on the DAG to send the emails out at 11 AM America/New_York on the first of each month (0 16 1 * *). Here’s what each task does:

• get_users is responsible for authenticating and hitting the Looker API to extract the user activity data from my Look as described above. It parses and pushes the response data to the email task via an XCom.
• email pulls the list of users via XCom, then divides the list into active users and inactive users based on a threshold of 7 days.

Using each user’s first name, email, and days since their last activity, it sends out a personalized email with Airflow’ssend_email utility. You will have to configure email for your Airflow instance in order for this to work (here’s one way to do it). Personally, I had our IT team set up a no-reply email address on our domain that configure Airflow to send from via SMTP.

Here’s how I set up my email Airflow task:

def email_users(**kwargs):
    users = kwargs['ti'].xcom_pull(task_ids='get_users')
    active_users = list(filter(lambda x: int(x['days_of_inactivity']) <= 7, users))
    inactive_users = list(filter(lambda x: int(x['days_of_inactivity']) > 7, users))

    logging.info('Emailing %s active users', len(active_users))
    for user in active_users:
        send_email(
            to=user['user_email'],
            cc='youremail@company.com',
            subject='How is Looker working for you?',
            html_content=active_template.format(**user),)

    logging.info('Emailing %s inactive users', len(inactive_users))
    for user in inactive_users:
        send_email(
            to=user['user_email'],
            cc='youremail@company.com',
            subject='How is Looker working for you?',
html_content=inactive_template.format(**user),)

And here’s the email template I created for inactive users.

Hi user_first_name,

I send out these automated check-ins on the first of each month to anyone who hasn’t used Looker in over a week. I noticed that you haven’t used Looker in days_of_inactivity days.

To help me make Looker relevant and useful for you, would you provide some feedback on how Looker might not be meeting your needs? We can set up 30m in person if that would be easier. Please be as candid as possible!

• Has Looker been useful to you? If not, what could we add or change that would make it more useful?

• Have you experienced anything frustrating or confusing in Looker that I could help with?

• Do you have any other questions or requests about Looker or data @ Milk Bar more broadly?

If you feel like Looker isn’t a great fit for your job in general, please let me know and we can give your license to someone else.

Let me know!

The template for active users looks very similar but also asks for positive feedback. Specifically, I’m interested in stories about how Looker improved someone’s ability to do their job or made the decision-making process easier for them. These stories are immensely valuable to me because they help validate the work I do and prove the need for business intelligence at Milk Bar.

This project only took about half a day to implement, but it saves me a lot of time and ensures I actually do something that I’m sometimes tempted to skip! I’m excited to see how this setup helps us scale feedback collection across the company. Ultimately, I hope it will help us improve our business intelligence product and help our users make better decisions.