データガバナンスツールとしてのLooker / 20200124 Looker User Meet Up in Tokyo #3

●TECHBLOG「データ集計基盤の改善でLooker導入に至ったワケ」
https://techblog.zozo.com/entry/looker-data-processing-improvement

●Looker Customer Stories「ケーススタディ：株式会社ZOZOテクノロジーズ」
https://info.looker.com/looker-japan/zozotechnologies-casestudy

●Looker Advent Calendar 2019 | 23日目「LookerにおけるGAデータ管理」
https://qiita.com/tsucasa/items/216ef43a269accb7ed1e

●ZOZOテクノロジーズ #2 Looker Advent Calendar 2019 | 4日目「データ集計やりにくい感の原因～OLAP～」
https://qiita.com/tsucasa/items/c9ada44308aff2a115cf

※PLAZMA 2019「lookerを用いたZOZOTOWNのデータ処理基盤」に関する記事は近日公開予定です。

Transcript

1. Copyright © ZOZO Technologies, Inc.
2. © ZOZO Technologies, Inc. 2
3. © ZOZO Technologies, Inc. 3
4. © ZOZO Technologies, Inc. 4
5. © ZOZO Technologies, Inc. 5
6. © ZOZO Technologies, Inc. 6
7. © ZOZO Technologies, Inc. 7
8. © ZOZO Technologies, Inc. 8 ZOZOTOWN
9. © ZOZO Technologies, Inc. 9 ZOZOTOWN
10. © ZOZO Technologies, Inc. 10
11. © ZOZO Technologies, Inc. 11
12. © ZOZO Technologies, Inc. 12
13. © ZOZO Technologies, Inc. 13
14. © ZOZO Technologies, Inc. 14
15. © ZOZO Technologies, Inc. 15
16. © ZOZO Technologies, Inc. 16
17. © ZOZO Technologies, Inc. 17
18. © ZOZO Technologies, Inc. 18
19. © ZOZO Technologies, Inc. 19
20. © ZOZO Technologies, Inc. 20
21. © ZOZO Technologies, Inc. 21
22. © ZOZO Technologies, Inc. 22
23. © ZOZO Technologies, Inc. 23
24. © ZOZO Technologies, Inc. 24 explore: view_1 { join: view_2 { … } join: view_3 { … } join: view_4 { … } view: direct_derived_view { derived_table: { explore_source: view_1 { column: column_a {} } SELECT view_1.column_a AS view_1_column_a FROM `〇〇〇〇〇` AS view_1
25. © ZOZO Technologies, Inc. explore: view_1 { join: view_2 { … } join: view_3 { … } join: view_4 { … } 25 view: big_view { derived_table: { explore_source: view_1 { column: column_a {} column: column_b {} column: column_c { field: view_2.column_c } column: column_d { field: view_3.column_d } column: column_e { field: view_4.column_e } } } dimension: column_a {…} dimension: column_b {…} dimension: column_c {…} view: indirect_derived_view { derived_table: { explore_source: big_view { column: column_a {} } WITH big_view AS (SELECT view_1.column_a AS column_a, view_1.column_b AS column_b, view_2.column_c AS column_c, view_3.column_d AS column_d, view_4.column_e AS column_e FROM `〇〇〇〇〇` AS view_1 LEFT JOIN `▲▲▲▲▲` AS view_2 ON ... LEFT JOIN `□□□□□` AS view_3 ON ... LEFT JOIN `◆◆◆◆◆` AS view_4 ON ... ) SELECT big_view.column_a AS big_view_column_a FROM big_view
26. © ZOZO Technologies, Inc. 26 filters: { field: timestamp value: “{{ _user_attributes[‘start_dt’] }} to {{ _user_attributes[‘end_dt’] }}, {{ _user_attributes[‘end_dt’] }} for 1 days” } start_dt: 2020-01-01 end_dt: 2020-01-21 explore: pppp explore: qqqq explore: rrrr explore: ssss
27. © ZOZO Technologies, Inc. 27
28. © ZOZO Technologies, Inc. 28
29. © ZOZO Technologies, Inc. 29
30. © ZOZO Technologies, Inc. 30
31. © ZOZO Technologies, Inc. 31
32. © ZOZO Technologies, Inc. 32
33. © ZOZO Technologies, Inc. 33 view: users { sql_table_name: `base_sys.users` ;; dimension: userid { label: “会員ID” view: member { sql_table_name: `marketing.member` ;; dimension: id { label: “会員ID” view: ga_sessions { sql_table_name: `9999999.ga_sessions` ;; dimension: custom_dimension3 { label: “会員ID”
34. © ZOZO Technologies, Inc. 34 view: users { sql_table_name: `base_sys.users` ;; extends: [user_id] view: member { sql_table_name: `marketing.member` ;; extends: [user_id] view: ga_sessions { sql_table_name: `9999999.ga_sessions` ;; extends: [user_id] view: user_id { dimension: user_id { label: “会員ID” sql: {% if data_source._parameter_value == ‘BASE’ %} ${users.userid} {% elsif data_source._parameter_value == ‘MA’ %} ${member.id} {% elsif data_source._parameter_value == ‘GA’ %} ${ga_sessions.custom_dimension_3} {% endif %} ;; } parameter: data_source { type: unquoted allowed_value: { label: “基幹DBシステム” value: “BASE” } allowed_value : { label : “マーケティングシステム” value: “MA” } allowed_value : { label: “Google Analytics” value: “GA”
35. © ZOZO Technologies, Inc. 35 意味付け
36. © ZOZO Technologies, Inc. 36
37. © ZOZO Technologies, Inc. 37