Question

Multi-Level Peer Comparisons w/ Share of Wallet Application

  • 15 July 2015
  • 0 replies
  • 347 views

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 sells clothing items. I might want to compare one particular Calvin Klein item to other Calvin Klein items, and then to the overall population of items.


For the purpose of this example, my item of interest is the Calvin Klein Field Jacket. Let’s see how that compares to its peer set.


In this particular example, I can create a dimension which returns one of the following choices:


(1) Field Jacket
(2) Rest of Calvin Klein
(3) Rest of Population

Of course, this could easily be extended to more than 3 elements.


Right away, we can use this comparison dimension to spot useful trends, such as how this item’s gross margin compares to its peers:



Using our comparison dimension, we can build useful metrics such as share of wallet. Notice the naming convention used above – the item itself starts with 1) the specific item, then 2) the rest of the items the brand owns and 3) everything else. This convention allows us to build filtered measures to tell us the comparative share of wallet of whatever item we’ve selected.


Now, if an item has increased sales, we can figure out if the item is gaining popularity relative to the brand, or if the overall brand is driving success. In this particular example, we see that Calvin Klein has a flat market share overall, but this particular item is increasing in popularity within the brand:



Try it Yourself


How it’s Done


Step one: We’ll create a one-row derived table which tells us everything about the selected item. To accomplish this, we will use templated filters:


- view: product_selected
derived_table:
sql: |
SELECT *
FROM products p
WHERE {% condition item_name %} p.item_name {% endcondition %}

fields:

- dimension: item_name
suggest_dimension: products.item_name

Then, we can create a cross-join from our products table to this new derived table in our Explore LookML:


- explore: orders_with_share_of_wallet
joins:
## all your other joins here ##
- join: product_selected
type: cross
relationship: one_to_one

Now, we’ve essentially made a table that looks like this:


category  | brand_name   | item_name         | category_2 | brand_name_2 | item_name_2
----------+--------------+-------------------+------------+--------------+-------------
Pants | Calvin Klein | Black Dress Pants | Outerwear | Calvin Klein | Field Jacket
Outerwear | Calvin Klein | Field Jacket | Outerwear | Calvin Klein | Field Jacket
Skirts | Calvin Klein | Skirt With Trim | Outerwear | Calvin Klein | Field Jacket
Outerwear | Calvin Klein | Slim Fit Coat | Outerwear | Calvin Klein | Field Jacket
Sleepwear | Nautica | Woven Pant | Outerwear | Calvin Klein | Field Jacket
Outerwear | Nautica | Rain Coat | Outerwear | Calvin Klein | Field Jacket

Step 2: Now that we have each item side-by-side with the item of interest, we can make useful comparisons. In this example, we’ll create a dimension called ‘comparison’ which groups our items into three groups: the selected item, other items of the same brand, and the rest of the population:


  - dimension: comparison
sql: |
CASE

WHEN ${products.item_name} = ${product_selected.item_name}
THEN '(1) '||${products.item_name}

WHEN ${products.brand} = ${product_selected.brand}
THEN '(2) Rest of '||${products.brand}

ELSE '(3) Rest Of Population'

END

In this particular example, the dimension would return one of the following choices:


(1) Field Jacket
(2) Rest of Calvin Klein
(3) Rest of Population

Of course, this could easily be extended to more than 3 elements.


Step 3: Using our comparison dimension, we can build useful metrics such as share of wallet. Notice the naming convention used above – the item itself starts with 1) the specific item, then 2) the rest of the items the brand owns and 3) everything else. . We can use this convention to build dynamically filtered measures with wildcard matching:


  - measure: total_sale_price
type: sum
sql: ${sale_price}

- measure: total_sale_price_this_item
type: sum
sql: ${sale_price}
filters:
products.comparison: '(1)%'

- measure: total_sale_price_this_brand
type: sum
sql: ${sale_price}
filters:
products.comparison: '(2)%,(1)%'

- measure: share_of_wallet_within_brand
type: number
description: 'This item sales over all sales for same brand'
value_format: '#.00\%'
sql: 100.0 * ${total_sale_price_this_item}*1.0 / nullif(${total_sale_price_this_brand},0)

- measure: share_of_wallet_within_company
description: 'This item sales over all sales across website'
value_format: '#.00\%'
type: number
sql: 100.0 * ${total_sale_price_this_item}*1.0 / nullif(${total_sale_price},0)

- measure: share_of_wallet_brand_within_company
description: 'This brand''s sales over all sales across website'
value_format: '#.00\%'
type: number
sql: 100.0 * ${total_sale_price_this_brand}*1.0 / nullif(${total_sale_price},0)

0 replies

Be the first to reply!

Reply