home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Exploring Data in Looker

This page introduces you to data exploration with Looker. When you finish, you’ll understand where to start pulling data in Looker, how to modify a report to see more detail, and how to drill down to gain deeper insights.

add

New in Looker 7.18, modeled queries are available to users as Quick Start query options in Explores when the Upgraded Explore Field Picker Labs feature is enabled. Quick Start queries provide a helpful starting point for users in Explores.

Explores Are the Starting Point for Exploration

For this example, let’s imagine we operate an e-commerce store. The Explore page presents a number of Explores for looking at our e-commerce store data. An Explore is a starting point for a query, designed to explore a particular subject area. To access the Explore page, use the Explore menu to select an Explore:

In our e-commerce store model we have Explores for Users, Products, Orders (a purchase event), Order Items (the products associated with an Order), and Inventory Items (Products in inventory). For example, when we have questions about users, we probably want to start exploring from the Users Explore.

The data shown in an Explore is determined by the dimensions and measures you select from the field picker at the left. A dimension can be thought of as a group or bucket of data. A measure is information about that bucket of data. In Looker, dimensions appear as blue columns and measures appear as orange columns in your data table.

The query shown below displays the number of orders per day, by querying the Orders Explore, and displaying one dimension (ORDERS Created Date) and one measure (ORDERS Order Count).

In this case, all order records have been grouped together by day (the dimension). Then we’ve asked for a count of orders (the measure) for each of those days.

Next let’s add another dimension by using Looker’s query builder.

Quick Start

add

New in Looker 7.18, modeled queries are available to users as Quick Start query options in Explores when the Upgraded Explore Field Picker Labs feature is enabled. Quick Start queries provide a helpful starting point for users in Explores.

When an admin has enabled the Upgraded Explore Field Picker Labs feature, users can select a prebuilt query to load in a blank Explore that has modeled queries:

Each Quick Start query option will display the name of the query and, when available, a description. When a query is selected, the Explore automatically runs and displays the query results.

A Quick Start query can be modified once it has run by adding or removing fields from the All Fields tab, from Search results, or from the In Use tab in the field picker.

Once an Explore query has finished running, a new Quick Start query can be selected by clicking the lightning bolt icon next to the Explore name to launch the Quick Start modal:

Selecting a Quick Start query from the modal will run the query and replace the previous query’s Explore results.

For more information on how developers can model prebuilt queries for users, see the query parameter documentation page.

Adding More Dimensions for More Detail

Let’s see how many orders are from returning customers. Adding the dimension for ORDERS Is First Purchased (Yes/No) splits the counts between first-time and returning customers.

Sorting Data

Data on the Explore page is sorted by default based on the following prioritization:

  1. The first date dimension, descending
  2. If no date dimension exists, the first measure, descending
  3. If no measure exists, the first added dimension, ascending

However, you often may want to sort data differently. For example, let’s see which date has the most orders from returning customers (in other words, customers not making first purchases). Clicking on the ORDERS Count column header will sort from highest to lowest. You can sort by multiple columns by holding down the Shift key, then clicking on the column headers in the order you would like them sorted.

Note that if you reach a row limit, you will not be able to sort row totals or table calculations.

Pivoting Dimensions

Multiple dimensions are often easier to look at when you pivot one of the dimensions horizontally. Each value in the dimension will become a column in your Look. This makes the information easier to consume visually, and reduces the need to scroll down to find data. Looker supports up to 200 pivoted values.

To pivot a dimension, click PIVOT for that dimension. Before running the query, be sure that you also have included at least one unpivoted dimension and at least one measure. You can pivot additional dimensions as desired, but must always include at least one unpivoted dimension.

If there is no row of data whose value would appear in a column, that is indicated with the null value symbol, a zero with a slash across. For example, on July 23rd we shipped one accessory but no active wear or blazers.

You can also sort pivoted dimensions by clicking the title of the dimension. To sort by multiple pivoted dimensions, hold down the Shift key and then click on the dimension titles in the order you would like them sorted. When sorting a pivoted measure, any rows with values in that column are sorted first followed by rows without data in that column (indicated by the null value symbol).

Reordering Columns

You can reorder columns in the Data section by clicking on a column header and moving the column to its desired position. The Explore’s visualization will reflect the new column order after you click Run.

Columns are organized in the Data section by field type: dimensions, dimension table calculations, measures, measure table calculations, and row totals. For the most part, columns can be reordered within each field type but cannot be moved out of their field type section. For example, dimension table calculations can be rearranged among themselves, but you cannot place a dimension table calculation in between two measures. One exception, however, is that you can use the arrow next to the row totals checkbox on the Data tab to move the row total column from the far right of the data table to just after the dimension table calculations.

Columns under a pivoted dimension can be reordered, but the order of pivoted dimensions can be changed only by changing the sort order, not by manual reordering.

Removing Fields

You can remove a field from your Explore by clicking the selected field in the field picker or by choosing Remove from the column’s gear menu:

You can also remove all fields in an Explore using the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows).

Once you have removed fields, click Run to get the new query results.

New Explore Field Picker

To enable the new upgraded field picker experience, an admin needs to enable the feature on the Labs page in the admin panel.

The new field picker includes the following elements:

  1. Explore name — Displays the name of the current Explore.
  2. Search bar — Displays the search bar.
  3. All Fields tab — Displays all available fields for an Explore.
  4. In Use tab — Displays all Explore fields that are currently in use.
  5. Field-specific information and actions — Displays a field’s current and potential functions in an Explore, as well as more details about a field.
  6. Explore summary — Displays the total number of fields in an Explore in the bottom left corner, and the Go to LookML link in the bottom right. Go to LookML directs users to the explore definition in its LookML project. This link is only visible to users with the see_lookml permission.

Field-Specific Information and Actions

The icons next to each field provide more information about the field and indicate the options available for that field:

These icons appear on the All Fields and In Use tabs.

The icon immediately to the left of a field name identifies the field’s data type, such as # for number, A for string, or (calendar icon) for date. The data type icon appears at all times:

The icons to the right of a field are visible when a field is active in an Explore, or when you hover a cursor over a field that is not active in an Explore. The icons can be clicked to filter or pivot by the field, or to provide more field information:

  1. Pivot icon — Click this icon to pivot or unpivot a field in an Explore. This icon will appear gray when a field is not pivoted and blue when a field is pivoted.
  2. Filter icon — Click this icon to add a field as an Explore filter or to remove a field as a filter. This icon will appear gray when a field is not a filter and blue when it is an active filter.
  3. Information icon — Click this icon to see more detail about a field:
    • All users will see the field’s data type, description (when available), and LookML field name (in view_name.field_name syntax):
    • Users with the see_lookml permission will see the definition of the LookML field’s sql parameter, as well as an option to navigate to that field in the LookML project:
  4. Three-dot Options menu — The three-dot Options menu is only available if the Custom Fields Labs feature is enabled, and to users with the create_table_calculations permission. Users with the create_table_calculations permission can click the three-dot Options menu to quickly create custom fields based on a field’s type. For example, the three-dot menu below shows the custom field options available for the Profit dimension, which is a number data type:

All Fields Tab

The All Fields tab is the starting place for building an Explore. It contains all the fields that are available to select for a query. Similarly to the classic Explore field picker, fields are organized alphanumerically by type (dimensions, followed by measures) under the name of the view or view label in which they are defined. Each field will show field-specific information and actions, such as a field’s current and potential functions in an Explore:

Selected fields will appear highlighted in gray, and corresponding field icons (pivot, filter) will appear blue when active. For example, the field Profit in the above field picker is highlighted in gray, indicting that it is selected. You can tell that this field is not pivoted or filtered because all corresponding field icons are gray.

Click a field from the All Fields tab to add it to or remove it from an Explore query. Additionally, you can click the appropriate icon to filter, pivot, or perform other field-specific actions from the All Fields tab.

If the Custom Fields Labs feature is enabled, custom fields and table calculations are listed under Custom Fields; and users with the create_table_calculations permission can create, select, and view these by clicking the blue Add button or by choosing a custom field option from a field’s three-dot Options menu. Users must have the create_table_calculations permission to see the Custom Fields view label or the three-dot menu.

In Use Tab

The In Use Tab shows all fields that are currently active in an Explore, organized alphanumerically by view or view label, and whether they are dimensions or measures:

The In Use tab also displays an updated Explore summary at the bottom of the tab. The bottom left corner displays the total number of active fields in an Explore. A Go to LookML link is available in the bottom right to users with the see_lookml permission. Go to LookML directs users to the explore definition in its LookML project. The example above shows that there are currently five total active fields in the Explore.

The icons next to each field indicate the field’s current and potential functions in an Explore query. For example, the field Created Year is currently filtered, as indicated by the active blue filter icon.

When a field is in use, you can remove it from an Explore by clicking the field’s name.You can also remove all selected fields (including custom fields and table calculations) by clicking Clear all, or you can remove all fields (including custom fields and table calculations), except those that are active filters, by clicking Clear fields, keep filters. Alternatively, you can choose to filter, pivot, or perform other field-specific actions from the In Use tab by clicking the appropriate field icon.

If the Custom Fields Labs feature is enabled, custom fields and table calculations are listed under Custom Fields when used in an Explore; and users with the create_table_calculations permission can create, select, and view these by clicking the blue Add button or by choosing a custom field option from a field’s three-dot Options menu. Users must have the create_table_calculations permission to see the Custom Fields view label or the three-dot menu.

Search Bar

The new field picker search bar returns results weighed by popularity and by whether field names, types, data types, or descriptions match a search string.

Clicking in a blank search bar displays a list of Popular Fields to choose from. Popular Fields are fields that are most commonly selected by users in an Explore and can be helpful when you’re creating an Explore from scratch:

To perform a search:

  1. Enter a search term in the search bar. The search term can be a full or partial field name, a field type (dimension, measure), dimension data type (for example, yesno, number, string, date), or measure data type (for example, count, sum, average).
  2. The search results display the information for each matching field, including:
    • The field’s name
    • An icon depicting the field’s data type
    • The field’s description (when available)
    • The field’s view or view label
  3. Hovering over a field in the search results reveals icons that indicate the field’s current and potential Explore functions. When a field is currently active in one of the three following ways, the corresponding icon will appear blue. The icon will appear gray when the field is inactive. For example, the selected field in the search results above, Profit, is inactive in the Explore — for this reason, all three icons appear gray.
    • Clicking the + icon adds a field to the Explore results table. If a field is already in an Explore results table, the gray inactive + will be replaced by an active blue x. Click the active blue x to remove the field from the Explore.
    • Clicking the double-arrow icon adds a field to an Explore table as a pivot. If a field is currently pivoted, the field can be unpivoted by clicking the active blue double-arrow icon, or by clicking the active blue x icon to remove the field from the Explore table entirely.
    • Clicking the funnel icon adds a field as an Explore filter. If a field is an active filter, it can be removed by clicking the active blue filter icon.

Active Explore fields that are surfaced in search results are marked with an inline blue dot to the right of the field name. For example, in the screenshot above, the field ID is currently active in the Explore, as indicated by the inline blue dot. Remove active Explore fields by hovering over the field name and clicking the active blue x.

Displaying Totals

Sometimes a summary of your data is useful. You can add column totals to your report by clicking the Totals checkbox in the upper right and then running the report:

You can also add row totals to your report, but only if you’ve added a pivot to your report:

If you’ve added row totals, and your query exceeds any row limit that you’ve set, you will not be able to sort the Row Totals column (although you can sort dimension and measure columns as normal). This is because you might be missing rows in your data that should be included in your totals. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).

There are some cases when totals won’t be available:

Additionally, there are some things to keep in mind about how totals work in certain situations:

In the situations described in the third and fourth bullets, above, it is possible to calculate totals only for the data you can see. To do so, you’ll need to use a table calculation, explained later on this page. For a column total, use sum(${view_name.field_name}). For a row total, use sum(pivot_row(${view_name.field_name})).

For information about displaying subtotals in table visualizations, see the Table chart options documentation page.

Drilling Down into the Data

Every query result is the starting point for another query. Clicking on any data point will drill down, creating another query refined by the data point you clicked. In the example below, we see that August 2, 2017 has had 189 orders. Clicking on the count of 189 takes us to details about those specific records.

Drilling Deeper …

In the drill overlay, we can see all of the orders placed on August 2, 2017. From here we can:

And Deeper Still …

If we click on the Order History field for Brendon Smith, we see a list of all the orders by Brendon Smith.

Of course, this isn’t the end of the road. Like any query in Looker, the results are linked so you can keep drilling, exploring, and arriving at new insights.

Drilling into Dashboards

If your Looker admin has enabled the Dashboards in Drill Menus Labs feature, dashboards will appear in the drill menu if they have a filter on the field you are drilling into.

For example, the “State” field in this Explore is used as a filter for three dashboards:

When you click on a dashboard in the drill menu, Looker displays that dashboard while applying the value you chose as the filter value for the dashboard:

For more information about how filters on dashboards are assigned to fields, see the Adding filters to user-defined dashboards documentation page.

Copying Values

Looker makes it easy to copy all the data from a table column. To do so, hover over a column label, click the gear icon, and then choose Copy Values:

This data can then be pasted into a document or a tool like Excel.

Using Links and Actions

The presence of either links or actions is indicated by an ellipsis (…) following the data in a field.

Using Links

Your Looker developers may have added clickable links to your data:

When you click on the data in the field, Looker provides an option to open the destination of the link. In the example above, the developers added a link to the Brand column. When you click on a brand listing, Looker provides an option to perform a Google search for that brand name.

Using Data Actions

Your Looker developers may have added data actions to the dimensions or measures in your data. With data actions, you can perform tasks with other tools directly from Looker, such as sending an email or setting values in other applications. These data actions appear in the drill menu under an Actions heading:

In the example above, the Phone field has a link to the Twilio service. When you click the phone number and select the Twilio action, Twilio prompts you to enter a message. Twilio then sends that message to the phone number.

Features for Developers

Depending on your permissions, you may see several features designed for Looker developers.

The gear drop-down menu next to each field in the field picker provides a Go to LookML link that lets Looker developers see their data modeling code. The SQL tab in the Data section lets them see the query that Looker sends to the database to get the data.

The Get LookML option in the Explore gear menu allows developers to copy LookML for the Explore’s query that can then be used to add a tile to LookML dashboards, to improve query performance with aggregate tables, or to define native derived tables.

Conclusion

Now that you know how powerful the Looker Explore page is for building queries, displaying results, and discovering insights through iterative searches, you might want to limit your results to just the data you’re interested in.

Top