Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

LookML

LookML design patterns, best practices, hacks, and knowledge sharing

  • 2,208 Topics
  • 4,890 Replies

2208 Topics

W
weitzenfeldMember
asked in LookML

If foreign_key is explore's primary_key, then one_to_one

If this is my explore: - view: helpscout_conversation sql_table_name: public.helpscout_conversation fields: - dimension: id primary_key: true type: int sql: ${TABLE}.id … and this is my model: - explore: helpscout_conversation label: Helpscout Conversations joins: - join: helpscout_conversation_facts foreign_key: id relationship: one_to_one Shouldn’t relationship: one_to_one be unnecessary, because it’s joining on the primary key of both tables so it has to be one_to_one? Looker seems to assume many_to_one in this situation if I don’t specify. I wonder if it’s making that assumption for the sake of being consistent, or if there’s something I’m missing.

1
ian
Looker Staff
7 years ago
R
RexMember
asked in LookML

Best Practices for Reporting On Taxonomies

Hi there if i have a taxonomy table in relational SQL like this id name parent_id 1 bar 2 2 foo NULL 3 biz 1 4 baf 2 5 bang 4 6 bam NULL What are some tactics (USING Redshift That doesn’t have Recursive CTE’s) i might use to allow a person to report on sums based on events for each name so that they can report on taxonomy in a way that is flexible and intuitive? I have a possible solution but I am not sure it’s my best choice to give users a simple UI to report on Taxonomy as they choose: Background: * I know that the max depth of the taxonomy is 6 levels deep * I am ok with joining the table a bunch of times and occasionally expanding the number of levels if i have to * I am also ok with dumping the data an using a language to iterate through the taxonomy to create a new tabular set of tuples to show the relationship of every section of the graph. * I do not have strong requirements yet on how we want to report on the taxonomy.

1
lloydtabb
Looker Staff
7 years ago
L
Looker Staff
lindseyLooker Staff
asked in LookML

How to escape special characters using SQL blocks

LookML is based on YAML, and in YAML has a number of special characters. For example : and | are special characters. In certain situations, these special characters can cause parse errors: You can fully escape this SQL block adding a pipe | to the LookML and putting the SQL on the next line: - dimension: test sql_case: 'One': | ${id} = 'String: other stuff' - dimension: special_char sql: | "|/" ${id} * 4

0
L
Looker Staff
7 years ago
B
bradMember
published in LookML

KPI Queries Vs Funnel (Cohort) Queries

I was taking a look at this pattern here: [Analytic Block] Simple Funnel Blocks About This Block A simple funnel is a great way to look at events or transactions. Starting with just a few events, you can quickly get a sense of how users traverse your site or application. By adding in more events, you can add much more depth to the picture. Many data sets include multiple different kinds of transactions, which are often written into separate tables in the database. A funnel consolidates multiple transactional tables into a single table for easy analytics - providing insigh… One of the downsides to using that approach is that it doesn’t really work with cohort type funnels. For example, Of the users who joined during this period, how many of them ordered a product? Looking at distinct is not going to work because you still aren’t joining by the cohort date. To solve this I use a different model where I have the events as columns as they occur. For examp

2106
lloydtabb
Looker Staff
7 years ago
L
Looker Staff
lindseyLooker Staff
asked in LookML

Using multi-line SQL blocks in Looker

In Looker, you may want to use multi-line SQL blocks to define the SQL underlying a dimension or a derived table. At first try, you might get a parse error. In order to do this, you will want to add a pipe character | after the sql: parameter, and then start your SQL on the next line. For example: - dimension: compound_buckets sql: | # pipe here! CASE WHEN ${orders.destination} = 'US' THEN ${us_buckets} WHEN ${orders.destination} = 'CA' THEN ${canada_buckets} ELSE ${intl_buckets} END

0
L
Looker Staff
7 years ago
E
errows
published in LookML

Daily, Weekly, Monthly active users

Hi, I have used this post as an example for our analysis. Nice chart with daily and monthly actives. I’d like to add a weekly active users line to it. Not too sure how to… Any idea? Thanks

4512
E
7 years ago
lloydtabb
Looker Staff
lloydtabbLooker Staff
asked in LookML

Comparison Against Population

How can we compare % of total SKUs that jeans account for in brand A, B, and Other? To give context/baseline to a comparison we can introduce a “Rest of Population” group. Currently we can filter brand to A & B and pivot but this only compares A to B. Alternatively, we can filter out A & B to get “Rest of Population”. In this example we’ll use liquid parameters to build a query with both segments. How its Done: We create a select field and a comparitor dimension. These fields will be used in place of the original dimension (brand) when filtering and slicing reports. brand_select, brand_comparitor Example Model: - connection: thelook - scoping: true - explore: products - view: products fields: - dimension: id type: int primary_key: true sql: ${TABLE}.id - dimension: brand_name sql: ${TABLE}.brand - dimension: category_name sql: ${TABLE}.category - dimension: sku sql: ${TABLE}.sku - filter: brand_select suggest_

5
lloydtabb
Looker Staff
7 years ago
A
andoMember
asked in LookML

Splitting a Sum over Months

I have some some views with the following dimensions: total value (int field), start date (time field), num_months (int field). I’m trying to create a look that will do the following: Split up the total value into num_months pieces Display that value on each month for num_months from start_date Splitting up the total value seems simple enough and I’m planning on doing this either with a SQL query or a table calculation. The more difficult piece looks like it’s going to be mapping the num_months to specific months. An example would be a row which has 10 as num_months, 2014-07-01 as the start_date and 1000 as the total value. The desired look would have the value of 100 for each month from 2014-07-01 until 2015-05-01—10 months. Does anyone have any ideas on how to implement this? I was thinking I could make a month_num timeframe and calculate the actual time using a calculation.

5
lloydtabb
Looker Staff
7 years ago
Zam
ZamMember
asked in LookML

Creating a type count measure for non-primary keys

###Quick Intro Looker natively translates a type: count measure into a COUNT(*). The sql: parameter has no effect on the produced SQL, which means that the below measure will still produce a COUNT(*). - measure: count type: count sql: ${name} I’ve been approached with expectations that the above would produce a COUNT(name), which would count the rows of non-null name values. ###The Looker Way I want to write the following SQL statement in Looker to count all the rows with non-null name values: SELECT COUNT(name) FROM users The way to replicate this in LookML terms is by filtering a type: count measure on the LookML field’s NOT NULL equivalent. To do this, use the filters parameter. - measure: name_count type: count filters: name: '-NULL' For more information on filter expression syntax please see our doc here. NOTE: NOT NULL is for integers and -'NULL' is for strings. The SQL produced by the above measure is as follows: SELECT COUNT(CASE WHEN NOT (users.name IS NULL)

0
Zam
7 years ago
S
Looker Staff
savageLooker Staff
asked in LookML

Multi-Level Peer Comparisons w/ Share of Wallet Application

Multi-Level Peer Comparisons w/ Share of Wallet Application Understanding and benchmarking how a single entity (product, customer service agent, promotion, etc.) compares to its peers is an essential tool for data analysts. How we define that peer set matters, and there are often multiple levels of peer hierarchy. This pattern allows us to answer questions such as: How does one item’s performance compare to that of similar items in the same or a different product line? What percent of customer’s market baskets are dedicated to specific products? How is that changing over time compared to similar or dissimilar products? Are certain products affecting the purchase patterns of other products? Data Types and Technical Info This pattern will work with data from most any e-commerce store, two-sided marketplace, retailer, healthcare company, government organization, or any transactional data which contains categories and hierarchies. Example Output Suppose I have an eCommerce store that sel

0
S
Looker Staff
7 years ago
B
bowen1
asked in LookML

Is it possible to reference a PDT within the SQL of the model of that PDT?

i.e. - view: sample_table derived_table: sql: | SELECT -- some pre computed column for this derived table sample_table.value / 2 as half_value FROM sample_table fields: - dimension: sum_all_half_values type: number sql: SUM(SELECT half_value FROM ${sample_table.SQL_TABLE_NAME})

2
B
7 years ago
J
Joel_PinkhamMember
asked in LookML

Best way to clean user entered address stringers

Prior to November 2014, my company allowed users to enter their state as a freeform text input. As a result, a large amount of state data uses varied inputs. e.g. New Jersey could be selected as “NJ” or “New Jersey” or “N.J.” Currently I’m achieving it with a giant CASE WHEN but this doesn’t seem so efficient.

3
J
7 years ago
V
VaiteMember
published in LookML

How to let the Users compare any event or Page URL's impact on Session and Lifetime performance

We built a Sessions table derived from Events, that tracks only the 10 most important events that can happen during a session. We had 200+ events for which I could not see their impact on Sessions conversion rate and performance: this would have been too costly in terms of memory. The Sessions derived table also included very little Page URL details: too costly to include tracking for the performance of each different banner ad referring traffic. We could only access that level of detail at Events level. The standard pattern in our main Sessions table, for each key event: SUM(CASE WHEN event = ‘MainFunnel’ … ) etc. Users were asking me “How might we see the impact of this specific Tooltip?” “What was the impact of version x.1.2. of the ABTest?” “How did users who came through this tweet convert compared to that post?” “What about users on this new OnePlus?”… Anything so granular could not be addressed in their standard Sessions Explore. The syntax below allowed me to let a User pick t

1080
V
7 years ago
A
Andrei_GigirtuMember
asked in LookML

Grouping product skus

Hi, I have the following problem and if anyone has a clue how to solve this it would be amazing. The Model: A lingerie set called B has 10 sizes (SKUS). I want to have a report that displays: Style B: Total Aug Ordered 2999 | Total Ordered Sept 4000 | etc Style C: Total Aug Ordered 1000 | Total Ordered Sept 2000 | etc The table schema is below: Table products - contains main products (products with names, description, pictures) and also skus (sizes) id name sku Table product_sizes (Links a main product to its children sizes) product_id -> foreign to products size_id -> foreign to products Orders: product_id -> foreign to products (size/sku only) qty ordered Thank you, Andrei

11
A
7 years ago
Zam
ZamMember
asked in LookML

Partition user access by table name with a shared view file

This article is deprecated. Please see this one on user attributes and dynamic schema/table name injection.

0
Zam
7 years ago
Zam
ZamMember
posted in LookML

[Retired] Drill to Dashboard Using HTML and Query Parameters

The content of this article has been updated and migrated to a Looker Help Center article.

2
Zam
7 years ago
Zam
ZamMember
asked in LookML

Drill using a sparkline or other images

###Objective Add a nice sparkline to encourage users to link out to wherever the sparkline points. I’ve most often seen sparklines used as links to dashboards, but that doesn’t mean we are limited to such behavior! Using Looker’s html parameter to deploy custom drill you can link your users anywhere. ###How do we do it? The sparkline is built into Looker’s file system. Self-hosted Looker customers can add custom images and use the same syntax to add images to a dimension/measure. - dimension: sparkline_drill type: string sql: ${name} html: | {{ linked_value }} <a href="/dashboards/model/dashboard_name?name={{ value }}" target="_new"> <img src="/images/qr-graph-line@2x.png" height=20 width=20></a> ###What does it look like? Going to steal an example from the drill to dashboard article… Example screenshots below. Overview Dashboard. Sparklines link to individual dashboards for each brand (highlighted in red), where the value from a given row is passe

0
Zam
7 years ago
W
weitzenfeldMember
asked in LookML

In what situations are you using Symmetric Aggregates?

I’ve read Lloyd’s Post on symmetric aggregates a bunch of times, but I’m still not sure if/how I’d use the feature. I’m still avoiding one-to-many joins in my modeling - using them feels like playing with fire. So I’m curious: are you using symmetric aggregates? If so, in what situation? Are there situations in which Symmetric Aggregates would be considered a best practice?

0
W
7 years ago
L
Looker Staff
lindseyLooker Staff
asked in LookML

How to comment out a block of code

In order to comment out an entire block of code in the LookML editor, following these steps: Select the lines you want to comment out Press ⌘+/ on a Mac, or ctrl+/ on a Windows computer Your code should now be commented out with a # symbol, like this: # - dimension: id # type: int # primary_key: true # sql: ${TABLE}.id Read about all the keyboard shortcuts available in Looker here. Commenting out SQL blocks If you are commenting out lines that are entirely within a SQL code block, you will want to manually add SQL comment notation. Note that SQL comments must appear only in SQL blocks that are indented beyond the LookML parameter above it. This looks like: Multi-line comments: - view: user_order_facts derived_table: sql: | /* SELECT users.id as user_id , COUNT(*) as lifetime_orders , MAX(orders.date) as most_recent_purchase_date FROM orders GROUP BY user.id /* Single line comments

0
L
Looker Staff
7 years ago
segahm
segahmMember
asked in LookML

Subtracting Measures from Dimensions and vice versa

First of all, this is not a valid operation in the SQL/LookML world. Just as it is NOT possible to perform SELECT (SUM(order_items.amount) - product.cost) AS order_profit FROM order_items LEFT JOIN order_items.product_id = product.id But the end result is possible. Let’s walk through a few examples: ###1) Measure::Measure operation Let’s assume we have the following views and dimensions/measures defined: - view: orders fields: - dimension: id primary_key: true - dimension: order_price type: number - view: product fields: - dimension: product_id primary_key: true - dimension: product_cost - view: order_items fields: - dimension: order_items_id primary_key: true - dimension: order_id - measure: total_product_cost type: sum sql: ${product.product_cost} - dimension: product_id - explore: order_items joins: - join: orders foreign_key: order_items.order_id - join: product foreign_key: order_items.product_id And our

1
lloydtabb
Looker Staff
7 years ago
anika
Looker Staff
anikaLooker Staff
asked in LookML

About the Errors category

[Replace this first paragraph with a short description of your new category. This guidance will appear in the category selection area, so try to keep it below 200 characters. Until you edit this text or create topics, this category won’t appear on the categories page.] Use the following paragraphs for a longer description, as well as to establish any category guidelines or rules. Some things to consider in any discussion replies below: What is this category for? Why should people select this category for their topic? How is this different than the other categories we already have? Do we need this category? Should we merge this with another category, or split it into more categories?

1
colin
Looker Staff
7 years ago
V
VaiteMember
published in LookML

Compare custom Date ranges, or any custom tiers. Enable end users to run these queries at will

How to allow end users to compare their custom date ranges in the Explores in just one query? Comparing 2 or more standard date intervals such as week, month is straightforward thanks to Looker’s time filters. However our teams may also require custom date ranges. We release a new feature or launch a new marketing campaign at irregular intervals. We want to confirm their impact as soon as possible, and we keep track daily of the statistical significance of variations observed. This may result in comparing the 11 days before the change with the 18 days following. In Looker an end user with no access to Developer mode can easily apply some types of filters: filter 1 period through a Date range filter. compare several values a dimension can take to the rest of population: after Data team developers have created the filter and dimension as specified by the Looker team here, any end user can compare measures for 2 values of one dimension, eg ‘Brand’, in one step. They simply select ‘Brand’

13841
lloydtabb
Looker Staff
7 years ago
D
Looker Staff
Dillon_MorrisonLooker Staff
published in LookML

[Analytic Block] Sequencing Transactions

##About This Block Nearly all businesses want customers to come back. It’s often much easier to increase your profitability by selling more to your existing customers than to acquire new customers. An interesting field to create for this is ORDERS Order Sequence Number. ORDERS means its an attribute of an order. Order Sequence Number means that we are computing an index for this order relative to the other orders a user has placed. For a buyer, their first order will be numbered ‘1’, their second order will be numbered ‘2’, and so on. Sequencing Transactions is an excellent foundation to build other analytical patterns on. Once you have ordered transaction, you gain insights into new facets of customer behavior, and answer questions such as (among others): What items are driving return purchasers? What is the first-order behavior of our customers? What percent of business comes from new customers? Returning Customers? Power Users? What type of products draw people to your company? ##

15674
segahm
7 years ago
R
RexMember
asked in LookML

Question about spinning up a new looker instance

We are spinning up a new looker instance for a new product. I am having trouble getting started server is 500 when I pop in the git url for the repo i just created. Help! 2015-03-14 02:57:49.908 +0000 [ERROR|01358|] :: non-absolute home org/jruby/RubyFile.java:754:in `expand_path' lib/helltool/models/ssh_deploy_key.rb:27:in `ssh_path' lib/helltool/models/ssh_deploy_key.rb:12:in `initialize' lib/helltool/controllers/projects/git_setup_controller.rb:29:in `Engine' org/jruby/RubyMethod.java:128:in `call' /home/looker/looker/looker.jar!/gems/sinatra-1.4.3/lib/sinatra/base.rb:1540:in `compile!' org/jruby/RubyProc.java:271:in `call' /home/looker/looker/looker.jar!/gems/sinatra-1.4.3/lib/sinatra/base.rb:950:in `route!' /home/looker/looker/looker.jar!/gems/sinatra-1.4.3/lib/sinatra/base.rb:966:in `route_eval' /home/looker/looker/looker.jar!/gems/sinatra-1.4.3/lib/sinatra/base.rb:950:in `route!' /home/looker/looker/looker.jar!/gems/sinatra-1.4.3/lib/sinatra/base.rb:987:in `process_route' org/jr

2
R
8 years ago
V
VaiteMember
published in LookML

Sessions To Order count - or Sessions until other interaction

In a Visitors table, I had created a dimension for the Visitors’ Time to Order, as well as Time to complete several interactions. The Visitors table is derived from Sessions by grouping them by user_id. Time To Order was of course useful, but I found it would be great to separate those users who purchase on 2nd visit, perhaps one month after their first session, and those users who after their first session make n visits during the first month, but only eventually purchase at the same time as the other group. My code for this is along these lines: derived_table: SELECT s.visitor_id , MIN(CASE WHEN s.order_count > 0 THEN s.run_sequence ELSE 100000 END) AS run_sequence_of_session_with_first_order FROM sessions AS s GROUP BY 1 fields: - dimension: run_sequence_of_session_with_first_order label: '._. CONVERSION - Sessions To Order: session number ... has 1st order' type: int sql: | CASE WHEN ${TABLE}.run_sequence_of

1161
lloydtabb
Looker Staff
8 years ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service