Setting up Looker Links to Dashboards and Explores through Liquid Automation

[adam_minton]

What is Drilling

When you’re exploring data, I like to think of drills as being either having horizontal qualities (i.e. show me another view of this data) and/or vertical qualities (i.e. show me a breakdown of this data). What’s important in both scenarios is understanding the context of where the user clicked. This includes understanding the source query (what filters are applied, what pivot of data, what row) and where are you targeting (what filters are applicable, how do I map the filters). 

Drilling is a really amazing capability in Looker that when done correctly, can unlock many different user journeys, but it can be hard to set up in the following scenarios as it creates a lot of extra LookML code that can be hard to manage long term. You will probably want to review the following articles if you haven’t already:

1. Drill to a dashboard or different explore

2. Multiple Drill Sets

3. Visual Drilling

If I were going to create a problem statement for creating drill experiences in Looker is that you need to think about two components: where did the user click and where is the user going.

Where did the User Click

To thoroughly understand a link in Looker, take any explore link and grab the expanded URL. Format the link here. What you’ll start to notice is that the short QID that we see in the URL contains a lot of information related to the exact query. If you do this for a drill down, you’ll notice that the filters are more explicit. When you utilize the {{link}} parameter, this expanded URL is what Looker utilizes. To better capture all of this information, we need to parse the URL in Liquid into variables. When you have the variables available, you can then start to craft links dynamically in Looker.

To set up the link automation, you will need to do the following steps:

1. Paste from the Liquid Code section, Link Automation to your manifest.

2. Create a default measure called link_generator in any view file you plan to create links out of.

measure: link_generator {
  hidden: yes
  type: number
  sql: 1 ;;
  drill_fields: [link_generator]
}

link_generator will add a measure that will be available in every query. It will be utilized in the code generation to track where the user clicked on the screen. Remember the first part of our problem statement is to understand when drilling, where did the user click and what was the context of where they clicked.

Where is the User Going

Now that we have the code setup, we can start working through some examples of how we can utilize the Link Automation.

Dashboards

When navigating to a dashboard, the filters on the dashboard are based on the name of the filters, not the LookML fields. The first thing we need to do is describe which LookML fields correspond to them.

You will be creating a variable called filters_mapping which corresponds from the LookML Field (source) to the Dashboard Filter Name (target). Liquid doesn’t have a native json type, so we will be utilizing pipes to correlate the source and target and commas for multiple fields. You can have as many fields as you want specified for the filters_mapping as this is how you are illustrating which source fields belong to which target dashboard filters.

You can see the basic example below of adding a dashboard drill onto an existing measure.

measure: total_gross_revenue {
	type: sum
	sql: ${sale_price} ;;
	value_format_name: "usd"
	filters: {
		field: is_completed_sale
		value: "yes"
	}
	drill_fields: [order_details_drill*]
	link: {
		label: "Load Customer Health Dashboard"
		url: "
		@{generate_link_variable_defaults}
		{% assign link = link_generator._link %}
		{% assign filters_mapping = 'customers.new_customer_indicator|New Customer,customers.traffic_source|Traffic Source,customers.city|Customer City,order_items.purchased_year|Purchased Date' %}
		{% assign target_dashboard = 254 %}
		@{generate_dashboard_link}
		"
	}
}

There are optional parameters that can be included prior to the @{generate_dashboard_link} which are:

• {% assign default_filters = 'Customer City=Los Angeles' %} - this will add a default filter

• {% assign new_page = true %} - this will open the dashboard in a new page instead of in the same page

A Different Explore

Loading a different explore in the drill menu is very similar to a dashboard, except that the filters_mapping is corresponding fields between two different explores.

link: {
  label: "Show Event Summary"
  url: "
  @{generate_link_variable_defaults}
  {% assign link = link_generator._link %}
  {% assign filters_mapping = 'order_items.purchased_date|events.created_date,order_items.purchased_year|events.created_date,customers.city|events.city' %}
  {% assign drill_fields = 'events.created_month,events.count_of_webpage_renders' %}
  {% assign different_explore = true %}
  {% assign target_model = 'the_look' %}
  {% assign target_explore = 'events_summary' %}
  @{generate_explore_link}
  "
}

Optional Parameters include:

• {% assign default_filters = 'order_items.purchased_date=1 year' %}

• {% assign default_filters_override = true %} - should the default or the source filters take precedence

The Same Explore & Visual Drill

Now that we have successfully started utilizing the link automation, we can start to iterate on enabling visual drill. In the example below, you can see a variable called measure is assigned and a new manifest constant called @{line_chart_1_date_1_measure}. Paste from the Liquid Code section, Default Visualizations to your manifest. Measure is utilized in the line_chart_1_date_1_measure, to associate the line color. Review Visual Drilling to understand how to create custom visualizations and store them in Looker.

link: {
  label: "Show Purchase History"
  url: "
  @{generate_link_variable_defaults}
  {% assign link = link_generator._link %}
  {% assign drill_fields = 'order_items.purchased_month,order_items.count_of_orders' %}
  {% assign measure = 'order_items.count_of_orders' %}
  @{line_chart_1_date_1_measure}
  @{generate_explore_link}
  "
}

Conclusion

Hopefully, as you read through this exercise, you can see the importance of thinking of the components of a link as individual variables. With this, you can now understand where the user clicked and create interesting user journeys in Looker.

Liquid Code

##Link Automation

constant: host {
  #Could assign a user_attribute since it won't be used with the generator
  value: "{% assign host = 'https://yourinstance.cloud.looker.com' %}"
}

constant: generate_link_variable_defaults {
  value: "
  {% comment %} Variables to default if not created {% endcomment %}

  {% comment %} User Customizable Parameters {% endcomment %}
  {% assign drill_fields = '' %}
  {% assign pivots = '' %}
  {% assign subtotals = '' %}
  {% assign sorts = '' %}
  {% assign limit = '500' %}
  {% assign column_limit = '50' %}
  {% assign total = '' %}
  {% assign row_total = '' %}
  {% assign query_timezone = '' %}
  {% assign dynamic_fields = '' %}

  {% comment %} Default Visualizations Parameters {% endcomment %}
  @{table}

  {% comment %} Default Behavior Parameters {% endcomment %}
  {% assign default_filters_override = false %}
  {% assign default_filters = '' %}
  {% assign new_page = false %}
  {% assign different_explore = false %}
  {% assign target_model = '' %}
  {% assign target_explore = '' %}

  {% comment %} Variables to be built in code below {% endcomment %}
  {% assign filters_mapping = '' %}
  {% assign target_content_filters = '' %}
  {% assign target_default_content_filters = '' %}
  {% assign host = '' %}
  "
}

constant: extract_link_context {
  value: "
  {% assign filters_array = '' %}
  {% for parameter in link_query_parameters %}
    {% assign parameter_key = parameter | split:'=' | first %}
    {% assign parameter_value = parameter | split:'=' | last %}
    {% assign parameter_test = parameter_key | slice: 0,2 %}
    {% if parameter_test == 'f[' %} {% comment %} Link contains multiple parameters, need to test if filter {% endcomment %}
      {% if parameter_key != parameter_value %} {% comment %} Tests if the filter value is is filled in, if not it skips  {% endcomment %}
        {% assign parameter_key_size = parameter_key | size %}
        {% assign slice_start = 2 %}
        {% assign slice_end = parameter_key_size | minus: slice_start | minus: 1 %}
        {% assign parameter_key = parameter_key | slice: slice_start, slice_end %}
        {% assign parameter_clean = parameter_key | append:'|' |append: parameter_value %}
        {% assign filters_array =  filters_array | append: parameter_clean | append: ',' %}
      {% endif %}
    {% elsif parameter_key == 'dynamic_fields' %}
      {% assign dynamic_fields = parameter_value %}
    {% elsif parameter_key == 'query_timezone' %}
      {% assign query_timezone = parameter_value %}
    {% endif %}
  {% endfor %}
  {% assign size = filters_array | size | minus: 1 %}
  {% if size > 0 %}
  {% assign filters_array = filters_array | slice: 0, size %}
  {% endif %}
  "
}

constant: match_filters_to_destination {
  value: "
  {% assign filters_mapping = filters_mapping | split: ',' %}
  {% assign filters_array = filters_array | split: ',' %}
  {% assign filters_array_destination = '' %}

  {% for source_filter in filters_array %}
    {% assign source_filter_key = source_filter | split:'|' | first %}
    {% assign source_filter_value = source_filter | split:'|' | last %}

    {% for destination_filter in filters_mapping %} {% comment %} This will loop through the value pairs to determine if there is a match to the destination {% endcomment %}
      {% assign destination_filter_key = destination_filter | split:'|' | first %}
      {% assign destination_filter_value = destination_filter | split:'|' | last %}
      {% if source_filter_key == destination_filter_key %}
        {% assign parameter_clean = destination_filter_value | append:'|' | append: source_filter_value %}
        {% assign filters_array_destination =  filters_array_destination | append: parameter_clean | append:',' %}
      {% endif %}
    {% endfor %}
  {% endfor %}
  {% assign size = filters_array_destination | size | minus: 1 %}
  {% if size > 0 %}
  {% assign filters_array_destination = filters_array_destination | slice: 0, size %}
  {% endif %}
  "
}

constant: build_filter_string {
  value: "
  {% assign filter_string = '' %}
  {% assign filters_array_destination = filters_array_destination | split: ',' %}
  {% for filter in filters_array_destination %}
    {% if filter != blank %}
      {% assign filter_key = filter | split:'|' | first %}
      {% assign filter_value = filter | split:'|' | last %}
      {% if content == '/explore/' %}
        {% assign filter_compile = 'f[' | append: filter_key | append:']=' | append: filter_value %}
      {% else %}
        {% assign filter_value = filter_value | encode_url %}
        {% assign filter_compile = filter_key | append:'=' | append: filter_value %}
      {% endif %}
      {% assign filter_string = filter_string | append: filter_compile | append:'&' %}
    {% endif %}
  {% endfor %}
  {% assign size = filter_string | size | minus: 1 %}
  {% if size > 0 %}
  {% assign filter_string = filter_string | slice: 0, size %}
  {% else %}
  {% assign filter_string = '' %}
  {% endif %}
  "
}

constant: build_default_filter_string {
  value: "
  {% assign default_filter_string = '' %}
  {% assign default_filters = default_filters | split: ',' %}
  {% for filter in default_filters %}
    {% assign filter_key = filter | split:'=' | first %}
    {% assign filter_value = filter | split:'=' | last %}
    {% if content == '/explore/' %}
      {% assign filter_compile = 'f[' | append: filter_key | append:']=' | append: filter_value %}
    {% else %}
      {% assign filter_value = filter_value | encode_url %}
      {% assign filter_compile = filter_key | append:'=' | append: filter_value %}
    {% endif %}
    {% assign default_filter_string = default_filter_string | append: filter_compile | append:'&' %}
  {% endfor %}
  {% assign size = default_filter_string | size | minus: 1 %}
  {% if size > 0 %}
  {% assign default_filter_string = default_filter_string | slice: 0, size %}
  {% endif %}
  "
}

constant: build_explore_link {
  value: "
  {% assign explore_link = '' %}
  {% if host != '' %}
    {% assign explore_link = explore_link | append: host %}
  {% endif %}
  {% if content != '' %}
    {% assign explore_link = explore_link | append: content %}
  {% endif %}
  {% if target_model != '' %}
    {% assign explore_link = explore_link | append: target_model | append: '/' %}
  {% endif %}
  {% if target_explore != '' %}
    {% assign explore_link = explore_link | append: target_explore | append: '?'  %}
  {% endif %}
  {% if drill_fields != '' %}
    {% assign explore_link = explore_link | append: drill_fields %}
  {% endif %}
  {% if target_content_filters != '' %}
    {% assign explore_link = explore_link | append: target_content_filters %}
  {% endif %}
  {% if vis_config != '' %}
    {% assign explore_link = explore_link | append: vis_config %}
  {% endif %}
  {% if pivots != '' %}
    {% assign pivots = '&pivots=' |append: pivots %}
    {% assign explore_link = explore_link | append: pivots %}
  {% endif %}

  {% if subtotals != '' %}
    {% assign subtotals = '&subtotals=' |append: subtotals %}
    {% assign explore_link = explore_link | append: subtotals %}
  {% endif %}

  {% if sorts != '' %}
    {% assign sorts = '&sorts=' |append: sorts %}
    {% assign explore_link = explore_link | append: sorts %}
  {% endif %}

  {% if limit != '' %}
    {% assign limit = '&limit=' |append: limit %}
    {% assign explore_link = explore_link | append: limit %}
  {% endif %}

  {% if column_limit != '' %}
    {% assign column_limit = '&column_limit=' |append: column_limit %}
    {% assign explore_link = explore_link | append: column_limit %}
  {% endif %}

  {% if total != '' %}
    {% assign total = '&assign=' |append: total %}
    {% assign explore_link = explore_link | append: total %}
  {% endif %}

  {% if row_total != '' %}
    {% assign row_total = '&row_total=' |append: row_total %}
    {% assign explore_link = explore_link | append: row_total %}
  {% endif %}

  {% if query_timezone != '' %}
    {% assign query_timezone = '&query_timezone=' |append: query_timezone %}
    {% assign explore_link = explore_link | append: query_timezone %}
  {% endif %}

  {% if dynamic_fields != '' %}
    {% assign dynamic_fields = '&dynamic_fields=' |append: dynamic_fields %}
    {% assign explore_link = explore_link | append: dynamic_fields %}
  {% endif %}
  "
}

constant: generate_dashboard_link {
  value: "
  {% assign content = '/dashboards-next/' %}
  {% assign link_query = link | split: '?' | last %}
  {% assign link_query_parameters = link_query | split: '&' %}
  {% assign target_content_filters = '' %}
  {% assign host = '' %}

  {% if new_page %}
    @{host}
  {% endif %}

  @{extract_link_context}
  @{match_filters_to_destination}
  @{build_filter_string}

  {% if default_filters != '' %}
    @{build_default_filter_string}
  {% endif %}

  {% if default_filters_override == true and default_filters != '' %}
    {% assign target_content_filters = default_filter_string | append:'&' | append: filter_string %}
  {% elsif default_filters_override == false and default_filters != '' %}
    {% assign target_content_filters = filter_string | append:'&' | append: default_filter_string %}
  {% else %}
    {% assign target_content_filters = filter_string %}
  {% endif %}

  {% comment %} Builds final link to be presented in frontend {% endcomment %}
  {{ host | append:content | append:target_dashboard | append: '?' | append: target_content_filters }}
  "
}

constant: generate_explore_link {
  value: "
  {% assign content = '/explore/' %}
  {% assign link_path =  link | split: '?' | first %}
  {% assign link_path =  link_path | split: '/'  %}
  {% assign link_query = link | split: '?' | last %}
  {% assign link_query_parameters = link_query | split: '&' %}
  {% assign drill_fields = drill_fields | prepend:'fields='%}

  {% if different_explore == false %}
    {% assign target_model = link_path[1] %}
    {% assign target_explore = link_path[2] %}
  {% endif %}

  {% if new_page %}
    @{host}
  {% endif %}

  @{extract_link_context}

  {% if different_explore %}
    @{match_filters_to_destination}
  {% else %}
    {% assign filters_array_destination = filters_array %}
  {% endif %}

  @{build_filter_string}

  {% if default_filters != '' %}
    @{build_default_filter_string}
  {% endif %}

  {% if default_filters_override == true and default_filters != '' %}
    {% assign target_content_filters = filter_string | append:'&' | append: default_filter_string | prepend:'&' %}
  {% elsif default_filters_override == false and default_filters != '' %}
    {% assign target_content_filters = default_filter_string | append:'&' | append: filter_string | prepend:'&' %}
  {% else %}
    {% assign target_content_filters = filter_string | prepend:'&' %}
  {% endif %}

  {% comment %} Builds final link to be presented in frontend {% endcomment %}
  @{build_explore_link}
  {{explore_link}}
  "
}


##End of Link Generation


##Default Visualizations

constant: line_chart_1_date_1_measure {
  #Required
  #measure
  value: "{% assign vis_config = '{\"point_style\":\"circle\",\"series_colors\":{\"' | append: measure | append: '\":\"#CE642D\"},\"type\":\"looker_line\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: table {
  value: "{% assign vis_config = '{\"type\":\"looker_grid\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: table_no_cell_viz {
  value: "{% assign vis_config = '{\"type\":\"looker_grid\",\"series_cell_visualizations\":{}}' | url_encode | prepend: '&vis_config=' %}"
}

constant: column {
  value: "{% assign vis_config = '{\"type\":\"looker_column\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: bar {
  value: "{% assign vis_config = '{\"type\":\"looker_bar\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: area {
  value: "{% assign vis_config = '{\"type\":\"looker_area\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: line {
  value: "{% assign vis_config = '{\"type\":\"looker_line\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: map {
  value: "{% assign vis_config = '{\"type\":\"looker_map\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: pie {
  value: "{% assign vis_config = '{\"type\":\"looker_pie\"}' | url_encode | prepend: '&vis_config=' %}"
}

constant: single {
  value: "{% assign vis_config = '{\"type\":\"single_value\"}' | url_encode | prepend: '&vis_config=' %}"
}

##End of Default Visualizations