行レベルのアクセス権限管理をかけつつ、全体に対するシェアも計算できるようにする

やりたいこと（具体例）

状況

ECプラットフォームを提供している

各販売業者は、このプラットフォーム上で商品を販売する

各販売ブランド向けに、売上実績のデータを公開している

行レベルアクセス権限管理で、それぞれのブランドの売上だけが見えるように制限している

やりたいこと

各ブランドが、市場でどれだけのシェアの売上を持っているのかを開示したい

商品カテゴリ別や顧客のグループ別にシェアを知れるようにしたい

https://scrapbox.io/files/67ede332f4bfa9610cb00515.png

作成方法

売上明細テーブルをベースにして、シェア分析用の派生テーブルを作成する

シェア計算に使いたいディメンションを含める

明細IDや商品名、単価といった粒度のディメンションは削る

上記の粒度で合計売上を計算しておく

要するに、以下の形のフィールドを持つ派生テーブルを定義する

https://scrapbox.io/files/67ee140bd63befdb0ae0d75f.png

上記派生テーブルに対し、フィールドを作成する

ディメンション

ブランドが自社か否かのYes / No

メジャー

売上の総合計 (①)

自社ブランドだけの売上合計 (②)

シェア (② / ①)

上記派生テーブル上で、ブランド名のメジャーを消す

→これにより、他社ブランドのデータが見えなくなる

上記派生テーブルに対してexploreを定義する

具体的な実装例

派生テーブルの定義

下の例はJOINが含まれて複雑になっていますが、本質的にはいくつかのディメンションに対して合計売上を事前集計しただけのものです。

code: derived_table.js

view: share_analysis {

derived_table: {

sql: SELECT

(EXTRACT(YEAR FROM order_items.created_at AT TIME ZONE 'Japan')) AS order_items_created_year,

(FORMAT_TIMESTAMP('%Y-%m', order_items.created_at , 'Japan')) AS order_items_created_month,

TRIM(products.category) AS products_category,

TRIM(products.brand) AS products_brand,

TRIM(products.department) AS products_department,

CASE WHEN users.country = 'UK' THEN 'United Kingdom'

ELSE users.country

END

AS users_country,

users.state AS users_state,

CASE

WHEN users.age < 0 THEN '0'

WHEN users.age >= 0 AND users.age < 10 THEN '1'

WHEN users.age >= 10 AND users.age < 20 THEN '2'

WHEN users.age >= 20 AND users.age < 30 THEN '3'

WHEN users.age >= 30 AND users.age < 40 THEN '4'

WHEN users.age >= 40 AND users.age < 50 THEN '5'

WHEN users.age >= 50 AND users.age < 60 THEN '6'

WHEN users.age >= 60 AND users.age < 70 THEN '7'

WHEN users.age >= 70 THEN '8'

ELSE '9'

END AS users_age_tier__sort_,

CASE

WHEN users.age < 0 THEN 'Below 0'

WHEN users.age >= 0 AND users.age < 10 THEN '0 to 9'

WHEN users.age >= 10 AND users.age < 20 THEN '10 to 19'

WHEN users.age >= 20 AND users.age < 30 THEN '20 to 29'

WHEN users.age >= 30 AND users.age < 40 THEN '30 to 39'

WHEN users.age >= 40 AND users.age < 50 THEN '40 to 49'

WHEN users.age >= 50 AND users.age < 60 THEN '50 to 59'

WHEN users.age >= 60 AND users.age < 70 THEN '60 to 69'

WHEN users.age >= 70 THEN '70 or Above'

ELSE 'Undefined'

END AS users_age_tier,

users.gender AS users_gender,

COALESCE(SUM(order_items.sale_price), 0) AS order_items_total_sale_price

FROM looker-private-demo.ecomm.order_items AS order_items

FULL OUTER JOIN looker-private-demo.ecomm.inventory_items AS inventory_items ON inventory_items.id = order_items.inventory_item_id

LEFT JOIN looker-private-demo.ecomm.users AS users ON order_items.user_id = users.id

FULL OUTER JOIN looker-private-demo.ecomm.products AS products ON products.id = inventory_items.product_id

GROUP BY

1,

2,

3,

4,

5,

6,

7,

8,

9,

10

ORDER BY

1 DESC;;

}

派生テーブル上のフィールド

code: fields.js

# 各レコードが自社ブランドか否かを判定する

dimension: is_mybrand {

type: yesno

}

measure: total_sales_mybrand {

label: "自社売上"

type: sum

sql: ${order_items_total_sale_price} ;;

}

measure: total_sales {

label: "市場全体の売上"

type: sum

sql: ${order_items_total_sale_price} ;;

}

measure: share_mybrand {

label: "自社のシェア"

sql: ${total_sales_mybrand} / nullif(${total_sales}, 0);;

value_format_name: percent_1

}

# このフィールドを消去するのが重要

# dimension: products_brand {

# type: string

# sql: ${TABLE}.products_brand ;;

# }

explore

code: explore.js

explore: share_analysis {}