People often go to great lengths to manually pull together certain information. A common case we see is wanting to build lists of entities based on a number of composite criteria: “I want to find users who have purchased shorts and sandals (tssk tssk)”… or more realistically “I want to find users who have purchased my product X but not my complementary upsell product Y”
This pattern provides one way to enable these types of requests in a self-service way, without requiring lots of copying and pasting and Excel sheets. The pattern is very open-ended, so you can really customize it a lot, but is clear enough that it should be relatively easy to adapt to your dataset
Here’s some example LookML referencing our “thelook” dataset:
explore: users {
hidden: yes
join: users_having_bought_X {
sql_on: ${users_having_bought_X.user_id}=${users.id} ;;
type: left_outer
relationship: one_to_one
}
join: users_having_bought_Y {
sql_on: ${users_having_bought_Y.user_id}=${users.id} ;;
type: left_outer
relationship: one_to_one
}
}
view: users_having_bought_Y {
extends: [users_having_bought_X]
}
view: users_having_bought_X {
derived_table: {
sql: SELECT users.id as user_id, COUNT(products.id) as product_count
FROM users
LEFT JOIN orders
ON orders.user_id = users.id
LEFT JOIN order_items
ON order_items.order_id = orders.id
LEFT JOIN inventory_items
ON inventory_items.id=order_items.inventory_item_id
LEFT JOIN products
ON products.id=inventory_items.product_id
WHERE {% condition select_product %}products.item_name{% endcondition %}
GROUP BY 1;;
}
dimension: user_id {
hidden: yes
type: number
sql: ${TABLE}.user_id ;;
}
dimension: purchase_count {
sql: COALESCE(${TABLE}.product_count,0) ;;
type: number
}
filter: select_product {
suggest_explore: products
suggest_dimension: products.name
}
}
Here’s how users can now build a “users that have X but not Y” list
