Using Looker Data Actions to Make Monzo Spend Analysis More Interactive … and Actionable

Uncategorized

2 May

The other week I posted a blog about analyzing the bank transaction data Monzo make available to customers via their developer API using Looker, the BI tool I use day-to-day in my role at Qubit as Analytics Product Manager.

One of the dashboards I’ve created shows me how spend looks across a number of merchant categories, compares their average transaction values and higlights the one I visit the most (The Ginger Dog in Brighton, thoroughly recommended) and plots each of the them on a map so I can remember where they’re each located.

If I click on one of the map icons on the Spend by Location visualization Looker then pops-up a dialog showing me the individual column values for this particular merchant — the Hardwood Arms in Fulham, one visit and total/average spend amounts of £75 exactly.

As well as tagging your transactions with metadata from the Foursquare web service Monzo also adds the Twitter handle for each merchant along with their website URL; I’ll start to make this dashboard a bit more interactive then by adding these as link parameters to my merchant_name dimension definition in my LookML view definition.

dimension: merchant_name {group_label: "Monzo Transactions"label: "Merchant Name"type: stringsql: ${TABLE}.merchant_name ;;link: {label:"Merchant Website"url:"https://{{ fluentd_monzo.merchant_metadata_website._value }}"icon_url: "https://www.google.com/s2/favicons?domain=google.com"}link: {label:"Merchant Twitter Profile"url:"https://twitter.com/{{ fluentd_monzo.merchant_metadata_twitter_id._value }}"icon_url: "https://www.google.com/s2/favicons?domain=twitter.com"}

Monzo also provides Google Places API and Foursquare API merchant metadata lookup codes along with each transaction record, so I add two more link parameters to call these two web services.

link: {label:"View in Google Maps"url: "https://www.google.com/maps/search/?api=1

&query

={{ fluentd_monzo.merchant_address_latitude._value }},{{ fluentd_monzo.merchant_address_longitude._value }}

&query_place_id

={{ fluentd_monzo.merchant_metadata_google_places_id._value }}"icon_url: "https://www.google.com/s2/favicons?domain=maps.google.com"}link: {label:"View in Foursquare"url: "{{ fluentd_monzo.merchant_metadata_foursquare_website._value }}"icon_url: "https://www.google.com/s2/favicons?domain=foursquare.com"}

Now when I click on the name of a merchant within any data visualization using that data point I get a menu of links I can click on for more information.

Clicking on the View in Google Maps link passes across the merchant’s latitude, longitude and Google Places API key to the Google Maps web service, and Looker then opens a new browser tab and gives me all the information I’m looking for using the Google Places web service.

All of this is good to know but it’s only really of value if I can make this data actionable, for example by linking it to the Google Tasks web service I use to record personal, non-project related tasks and reminders. To do this I’m going to use Looker’s Data Actions feature together with Zapier’s integration with Google Tasks to tie it all together.

First, I create a Zapier Zap that listens for a webhook call and then uses the parameter values I provide from the Looker data action to to create a new Google Task. This article on the Looker Discourse website goes into more detail on how to set up Zapier webhooks integration with data actions if you’d like to set up something similar yourself.

action: {label: "Add Google Task"url: "https://hooks.zapier.com/hooks/catch/XXXXXX/fco8nm/"icon_url: "https://www.google.com/s2/favicons?domain=tasks.google.com"form_param: {name: "Task Name"type: stringrequired: yesdefault: "{{ merchant_name }}"}form_param: {name: "Due Date"type: stringrequired: yes}form_param: {name: "Action"type: textareadefault: "Book table at {{ merchant_name }}"}form_param: {name: "Urgency"type: selectoption: {name: "Low"label: "Low"}option: {name: "High"label: "High"}}}

Then I add an action parameter into my merchant LookML dimension definition that defines a form with default values, allows me to amend those values if needed and then HTTP POSTs them as the payload to the Zapier web service.

Now, another menu link item is displayed to allow me to create Google Task items when I click on merchants in a Monzo transactions dashboard, like this:

When I click on that link a dialog is then shown, giving me the option to amend the task title and details and add a due date and urgency to the task I’m about to define.

Then finally, I press Submit and then go over to the Google Tasks web interface to see my new task recorded and ready for me to action tomorrow morning.