LinkedIn
Twitter
Oh joy
Who doesn’t love scheduling highly important reports to customers/prospects/team members/mothers/friends just to find that they’re being unsubscribed from everyday? I know I sure do. I also love tracking these events down such that I can call my mom and remind how her important it is for her to see the daily report on top used search keywords on IMBD’s website.
How do I track these…
Quite simply, actually. I utilize the system__activity model is what powers the usage panel, and in short is a LookML model included in the product that allows Admin’s to sift through Looker’s internal database. I essentially use the two following looks to follow unsubscribe events, and whom grew tired of my souper shweet datas.
Quick Note: The following urls are query urls. They’re meant to be tacked on to the end of your host…don’t worry, I’ll provide examples.
The (near) magic keys
Disclaimer: I will not be posting example result sets due to the nature of the data…you know, I don’t think you’d want your email address handed out publicly
Both of the following urls utilize the event_attribute explore which tracks all events and some helpful info.
This first url is a list of all emails that have had a report scheduled to them split by date. If you want, you can go as granular as time.
/explore/system__activity/event_attribute?fields=event.created_date,event_attribute.value&f[event_attribute.name]=recipient&sorts=event.created_date+desc&limit=500&column_limit=50&vis=%7B%7D&filter_config=%7B%22event_attribute.name%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22recipient%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%7D%5D%7D&dynamic_fields=%5B%5D&show=data%2Cfields%2Cfilter&origin=share-expanded
An example of how to apply this url is below, which is a the url for learn.looker.com. If you don’t have access to learn, and are a prospect/client, please feel free to reach out to support@.
https://learn.looker.com/explore/system__activity/event_attribute?fields=event.created_date,event_attribute.value&f[event_attribute.name]=recipient&sorts=event.created_date+desc&limit=500&column_limit=50&vis=%7B%7D&filter_config=%7B%22event_attribute.name%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22recipient%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%7D%5D%7D&dynamic_fields=%5B%5D&show=data%2Cfields%2Cfilter&origin=share-expanded
This second url will display all of the unsubscribe events by date.
/explore/system__activity/event_attribute?fields=event.created_time,user.name,event_attribute.value,event_attribute.name&f[event.category]=scheduler&f[event.name]=unsubscribe&sorts=event.created_time+desc&limit=500&column_limit=50&vis=%7B%7D&filter_config=%7B%22event.category%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22scheduler%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%7D%5D%2C%22event.name%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22unsubscribe%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%7D%5D%7D&dynamic_fields=%5B%5D&show=data%2Cfields%2Cfilter&origin=share-expanded
Some caveats
Looker only keeps record of the schedule_event_id for an unsubscribe event. If that event was triggered by a Looker user then the event.user_id will be non-null. If the event was triggered by a non-Looker user/an external email, i.e. my mom wrt my awesome IMDB report, then the event.user_id will be null, thus the email will also be null.
If you are seeing null values you’ll have to use your noggin to cross reference the looks above and figure out whom received an email the day of a null unsubscribe event, and whom out of that bunch is an external email. Then you can start pestering.
