Dimension and measure names can get confusing, especially as models grow. Making the proper field references while writing LookML, and choosing the correct field in the field picker, requires thoughtful naming. One approach that will help with this is a consistent naming methodology. The following conventions are those often used internally at Looker.
“Count” for Count Measures
type: count measures are called [Filter] Count. For example:
- view: users
fields:
- measure: count
type: count
- measure: active_count
type: count
filters:
status: 'active'
This will give names in the Looker UI like:
- USERS Count
- USERS Active Count
These field names read naturally, although business users may not initially know to search for “count” as opposed to “number of” or “total”. However, with some very simple training, this seems to be the best option.
We don’t use “number of” or “total” for counts, because we want to reserve those words for other cases.
“Unique Count” for Count Distinct Measures
type: count_distinct measures are called Unique [Filter] [Entity] Count. For example:
- view: users
fields:
- measure: unique_user_count
type: count_distinct
sql: ${id}
- measure: unique_active_user_count
type: count_distinct
sql: ${id}
filters:
status: 'active'
- measure: unique_name_count
type: count_distinct
sql: ${name}
This will give names in the Looker UI like:
- USERS Unique User Count
- USERS Unique Active User Count
- USERS Unique Name Count
“Total” for Sum Measures
type: sum measures are called Total [Filter] [Entity]. For example:
- view: orders
fields:
- measure: total_revenue
type: sum
sql: ${TABLE}.revenue
- measure: total_chicago_revenue
type: sum
sql: ${TABLE}.revenue
filters:
city: 'Chicago'
This will give names in the Looker UI like:
- ORDERS Total Revenue
- ORDERS Total Chicago Revenue
“Number of” for Quantity Dimensions
type: number dimensions that represent a quantity are called Number of [Entity]. For example:
- view: orders
fields:
- dimension: number_of_items
type: number
sql: ${TABLE}.items
This will give names in the Looker UI like:
- ORDERS Number of Items
Using the word “count” or “total” in this case can be quite confusing, because you will not know if you are working with a dimension or measure.
“Average” for Average Measures
type: average measures are called Avg [Filter] [Entity]. For example:
- view: orders
fields:
- measure: avg_revenue
type: average
sql: ${TABLE}.revenue
- measure: avg_chicago_revenue
type: average
sql: ${TABLE}.revenue
filters:
city: 'Chicago'
This will give names in the Looker UI like:
- ORDERS Avg Revenue
- ORDERS Avg Chicago Revenue
“Over” or “Per” For Ratios
Measures and dimensions that are based on ratios between two other fields are called [Numerator] over [Denominator] or [Numerator] per [Denominator]. For example:
- view: orders
fields:
- dimension: revenue_per_number_of_items
type: number
sql: ${revenue} / ${number_of_items}
- measure: total_revenue_per_unique_customer
type: number
sql: ${total_revenue} / ${unique_customer_count}
This will give names in the Looker UI like:
- ORDERS Revenue Per Number Of Items
- ORDERS Total Revenue Per Unique Customer
If you were to call the first dimension something like Avg Revenue Per Item it would be less clear that you were using a type: number dimension instead of a type: average measure.