Exploring in Looker
After learning how to browse through others’ reports and dashboards, many users go the next step and learn how to create their own reports, harnessing their organization’s data for their own specific needs. This page will show you how to get started exploring data.
Where to Start?
There are many different ways to go to the Explore view.
- The most direct will be from the Explores tab found on the reporting landing page in the left sidebar menu. Explores are divided into Clients & Staff or Financial, just as in Analytics & Reporting today, with the same subject areas including Client Passes & Plans, Clients, Enrollments, Monthly Business Metrics, Payroll, Transactions, Invoices, etc.
- You can start an Explore from all the filters and fields in a Look. From any Look, click on the gear icon in the upper right to find and select Explore from Here.
- To access Explore from a Dashboard, hover over one of the tiles to use the three-dot icon to find and select Explore from Here.
If you decide to access the Explores from a Look or a Dashboard, you will access the associated content area that was used to build the Look or Dashboard.
The Basics
Let's use the Enrollments explore as an example.
- Click one or more grey fields (called dimensions) to group your data. Click one or more orange fields (called measures) to add information about those groups, such as totals and counts.
- Click Filter, if desired, to add a filter to your report based on that field. More about Filtering data can be found here.
- Set the condition for any filters you’ve added. You can also click the Custom Filter checkbox in the upper right of the filters section for more flexible options.
- If desired, choose a visualization type, such as a bar chart in this case. You can click on the gear menu in the upper right of the visualization section to customize your chart.
- Click Run.
Adding More Dimensions for More Detail
Let’s see what types of passes or plans do people use to pay for their visits. Adding the dimension for Paid With Type by just left single click on the field on the left panel. Click Run to re-run your query.
Sorting Data
By default, it's sorted by Enrollment Count descending. Clicking on the Paid With Type column header will sort from highest to lowest. Clicking on the column header again will change the sort order. 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 you don't actually want to Pivot on a dimension, you can click on the gear icon next to the dimension and click on Unpivot.
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, there are no enrollments with Coaches Plan for Service Category Group B.
You can also sort pivoted dimensions by clicking the title of the dimension. To sort by multiple pivoted dimensions, hold down the Shift key, 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. 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 between two measures.
Columns under a pivoted dimension can be reordered, but the order of pivoted dimensions can only be changed 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.
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, 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 (though 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:
- Totals are only available for measures, not dimensions.
- Certain types of columns won’t total, due to database limitations, or because the value would not make sense as a total. For example, you can’t add together a list of words.
Additionally, there are some things to keep in mind about how totals work in certain situations:
- Columns that count unique items might not add up as you expect, since the same item might show up in several categories, but only be counted as one unique item in the totals.
- Some table calculations that perform aggregations, such as calculations using percentile or median, might not add up as you expect. This is because table calculations calculate totals using the values in the total row, not using the values in the data column.
- If you’ve filtered your report by a measure, totals may appear to be too high. However, in actuality, what you’re seeing is a total for your data before the measure filter is applied. In other words, the measure filter may be hiding some data from your report, even though that data is included in the total.
- Similarly, if you’ve placed row or column limits on your report, and your report exceeds that limit, totals may also appear to be too high. However, what you’re seeing is a total for your data before the limits are applied. In other words, the limits may be hiding some data from your report, even though that data is included in the total.
In situations 3 and 4 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 in this tutorial. For a column total use sum(${view_name.field_name}). For a row total use sum(pivot_row(${view_name.field_name})).
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 there are 4 enrollments with Gold Membership. Clicking on the count of 4 takes us to details about those specific records.
Drilling Deeper …
In the drill overlay, we can see the 4 enrollments paid with Gold Membership.
From here we can:
- Click the Explore from Here button to open an Explore that uses the fields in the drill overlay as a starting point.
- Click the Download Results button to download the data, using the same options as shown on this page.
- Click on the value in the Full Name field to go to the person's profile in Pike13 main site.
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.
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.
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. Learn more in Filtering and Limiting Data.