LookerのDashboardをより柔軟に作る

Looker User Meetup in Tokyo #3 発表資料。
https://looker-japan-user-group.connpass.com/event/159056/

Lookerでdashboardを作るときに便利な3つの機能を紹介します。

---
Slide内に登場するLink

1. プロダクトのリリース前から新ダッシュボード「Looker」の導入に踏み切ったわけ
https://tech.mercari.com/entry/2018/08/22/155059

2. メルカリにおけるDashboard Replacementの事例
https://speakerdeck.com/haseryo/20190906-looker-user-meetup-merpay

3. Override Look Filters in the URL
https://help.looker.com/hc/en-us/articles/360023571954-Override-Look-Filters-in-the-URL

Transcript

1. 1 LookerͷDashboardΛΑΓॊೈʹ࡞Δ ͋Δ͍͸Lookerͷֶशํ๏ʹ͍ͭͯ Looker User Meetup in Tokyo #3 Nagai shinya @ Mercari, Inc.
2. 2 ࠓ೔ͷςʔϚ
3. 3 ! 3ͭͷػೳΛ঺հɻ ◦ Filter ◦ Override ◦ Parameter → Dashboard͕ΑΓॊೈʹѻ͑ΔΑ͏ʹͳΔɻ ! DashboardΛ࡞Δਓʹ޲͚ͯͨ಺༰ɻ ࠓ೔ͷϝΠϯςʔϚ Lookerͷಛఆͷػೳʹ͍ͭͯ۷ΓԼ͛Δ
4. 4 ! Lookerʹର͢Δॴײ ◦ ศར͕ͩ׳Ε͕ඞཁͳπʔϧɻ ◦ Πχγϟϧͷֶशίετ͕ͪΐͬͱߴ͍ɻ
 ! ͦ͜Ͱɺ ◦ ň͜͏ଊ͑Δͱɺཧղ͠΍͘͢ͳΔ͔΋?ʼnΛ঺հɻ ňLookerͷ֤छػೳΛ͜͏ଊ͑Δͱཧղ͠΍͍͔͢΋?ʼnͷ঺հ αϒςʔϚ
5. 5 ࣗݾ঺հ
6. 6 ࣗݾ঺հ ! ϝϧΧϦ ◦ 2019೥1݄ೖࣾɻ ◦ ௚ۙ : A/Bςετͷ෼ੳɺඪ४ԽͳͲ ◦ Lookerར༻΋͔͜͜Β։࢝ɻ
 ! աڈ ◦ डୗσʔλ෼ੳ ◦ ιʔγϟϧήʔϜ੍࡞ ιϑτ΢ΣΞΤϯδχΞ ◦ ੡ༀձࣾ৘ใγεςϜ෦໳ ӬҪ ৳໻ ( Nagai Shinya )
7. 7 ձࣾ঺հ
8. 8 ೥݄೔
   ίϛϡχςΟαʔϏεͷӡӦ
   ෱ࢁ੣
   ˟
   ౦ژ౎ौ୩۠ઍବϲ୩
 .4%Ϗϧ ձࣾ֓ཁ גࣜձࣾϝϧΧϦ ઃཱ
   ࢿຊۚ
   ࣄۀ಺༰
   ୅දऀ
   ॴࡏ஍
   ڌ఺ ೥݄೔
    ඦສԁʢࢿຊ४උؚۚΉʣ
   εϚʔτϑΥϯ޲͚ϑϦϚΞϓϦ
 ʮϝϧΧϦʯͷاըɾ։ൃɾӡӦ
   
   ࢁాਐଠ࿠
   ˟
   ౦ژ౎ߓ۠࿡ຊ໦ 
   
   ࿡ຊ໦ώϧζ৿λϫʔ
   ౦ژɺઋ୆ɺ෱Ԭ ϝϧΧϦάϧʔϓ͸ɺגࣜձࣾϝϧΧϦͱɺͦͷ࿈݁ࢠձࣾͰߏ੒͞Ε͍ͯ·͢ɻ ઃཱ
   ࣄۀ಺༰
   ୅දऀ
   ॴࡏ஍ ೥݄೔
   ۚ༥ؔ࿈ͷ৽نࣄۀ
   ੨༄௚थ
   ˟
   ౦ژ౎ߓ۠࿡ຊ໦
 ࿡ຊ໦ώϧζ৿λϫʔ ઃཱ
   ࣄۀ಺༰
   ୅දऀ
   ॴࡏ஍ ೥݄
   64൛ϝϧΧϦͷاըɾ։ൃɾӡӦ
   +PIO
   -BHFSMJOH
   
   1BMP
   "MUP
   $BMJGPSOJB
   
   1PSUMBOE
   0SFHPO
   
   #PTUPO
   $BNCSJEHF
   .BTTBDIVTFUUT ઃཱ
   ࣄۀ಺༰
   ୅දऀ
   ॴࡏ஍ גࣜձࣾϝϧϖΠ Mercari, Inc.
 ʢUSʣ ϚΠέϧגࣜձࣾ ઃཱ
   ࣄۀ಺༰
   ୅දऀ
   ॴࡏ஍ ೥݄೔
   ϑοτϘʔϧΫϥϒӡӦ
   খઘจ໌
 ҵ৓ݝཱΧγϚαοΧʔελδΞϜࢦఆ؅ཧ
   ҵ৓ݝࣛౢࢢ҄ੜ౦ࢁ൪஍ גࣜձࣾ
 ࣛౡΞϯτϥʔζɾΤ ϑɾγʔ
9. 9 ϝϧΧϦͱ͸ ˔αʔϏε։࢝೔ɿ2013೥7݄
   ˔ରԠOSɿAndroidɺiOS ˞8FCϒϥ΢β͔Β΋ར༻Մೳ
   ˔ར༻ྉɿແྉ
   ˞ചΕͨͱ͖ͷख਺ྉɿൢചՁ֨ͷ10% ˔ରԠ஍Ҭɾݴޠɿ೔ຊɾ೔ຊޠجຊ࢓༷ ͦΕΛඞཁͱ͢Δਓͷखʹ౉Γɺ࢖༻͞ΕΔ͜ͱʹتͼΛײ ͡ɺ·ͨߪೖऀ͸ɺଟ࠼͔ͭϢχʔΫͳ঎඼ͷத͔Βʮๅ୳ ͠ʯײ֮Ͱ۷Γग़͠෺Λݟ͚ͭΔങ͍෺ମݧΛָ͠ΜͰ͍· ͢ɻ͞ΒʹʮϝϧΧϦʯͰ͸ɺ෺ͷചങ͚ͩͰ͸ͳ͘ɺग़඼ ऀɾߪೖऀؒͷνϟοτ΍ʮ͍͍ͶʂʯػೳΛ௨ͯ͡ɺ͓٬͞ ·ؒͷίϛϡχέʔγϣϯ΋׆ൃʹߦΘΕ͍ͯ·͢ɻ ϑϦϚΞϓϦʮϝϧΧϦʯ͸ɺݸਓ͕؆୯ʹதݹ඼ͷചങΛߦ͑Δ $UP$ϚʔέοτϓϨΠεͰ͢ɻग़඼ऀɾߪೖऀ૒ํ͕ɺ҆શɾ҆৺ͳऔ ҾΛָ͠ΜͰ͍͚ͨͩΔαʔϏεΛ໨ࢦ͠ɺʮϝϧΧϦʯ͕Ұ࣌తʹߪ ೖ୅ۚΛ༬͔ΔΤεΫϩʔܾࡁΛ׆༻ͨ͠औҾ؀ڥͷ੔උ΍ɺ؆୯͔ͭ खࠒͳՁ֨ͷ഑ૹΦϓγϣϯɺࠩผԽ͞ΕͨϢχʔΫͳ͓٬͞·ମݧΛ ఏڙ͍ͯ͠·͢ɻଟ͘ͷग़඼ऀ͸ɺࣗ෼ʹͱͬͯඞཁͰͳ͘ͳͬͨϞϊ ͕ɺ
10. 10 ϝϧΧϦάϧʔϓͱLooker શࣾతͳBIπʔϧͱͯ͠׆༻த https://tech.mercari.com/entry/2018/08/22/155059 https://speakerdeck.com/haseryo/20190906-looker-user-meetup-merpay
11. 11 എܠ
12. 12 A/Bςετ ΞϓϦʹෳ਺ͷόϦΤʔγϣϯΛ༻ҙͯ͠ɺͲͪΒ͕ྑ͍͔ൺֱ͢Δ BUTTON BUTTON γϯϓϧͳྫ ! 100໊ͷ͓٬͞· ◦ 50໊ → ੺͍Ϙλϯ ◦ 50໊ → ੨͍Ϙλϯ ! ͲͪΒ͕ΑΓλοϓ͞ΕΔ͔?
 ࣮ࡍ ! ෳ਺ͷςετͷ࣮ࢪɻ ! ෳ਺ͷࢦඪͷൺֱɻ
13. 13 χʔζ ! ӈਤ ◦ A/Bςετ༻ͷDashboardɻ ◦ 20ʙ30ݸఔ౓ͷࢦඪɻ ◦ ಉ਺ͷάϥϑ( Tile )
 ! χʔζ ◦ 10݅ͷA/BςετΛ࣮ࢪͨ͠৔߹ɺ ◦ 10ݸ ӈਤͷDashboard͕ཉ͍͠ɻ
 → ίϐϖͰ࡞Δͷ͸େม͗͢Δ...
 ! ͦΜͳ࣌ɺ໾ʹཱͭػೳ ňࣅͨΑ͏ͳDashboardΛ୔ࢁ࡞Γ͍ͨʼnͱ͍͏χʔζ
14. 14 લఏ
15. 15 ςʔϒϧߏ੒ͷΠϝʔδ A/Bςετ໊ɺuser_idɺgroup_id͕ೖ͍ͬͯΔΠϝʔδ name user_id group_id test01 10001 1 test01 10002 2 test01 10003 1 test01 10004 2 test02 10001 1 test02 10002 2 Test01ʹ͍ͭͯूܭ͍ͨ͠৔߹: ! test01ͰߜΓࠐΈ (WHERE) ! user_idͰKPIΛ݁߹ (JOIN) ! group_idผʹूܭ (GROUP BY)
16. 16 filterػೳͷجຊ
17. 17 Filterͷجຊ ᶃ 1ͭͷTileʹରͯ͠ɺSQLͷ “WHERE۟” ʹ૬౰͢ΔߜΓࠐΈΛ௥Ճ ྫ ! 1ͭͷάϥϑʹରͯ͠ɺ ! ಛఆͷA/Bςετʹߜͬͯूܭ ‏ tileͷfilter
18. 18 Filterͷجຊ ᶄ ྫ ! ෳ਺ͷάϥϑʹରͯ͠ɺ ! ಛఆͷA/Bςετʹߜͬͯूܭ ෳ਺ͷTileʹରͯ͠ɺSQLͷ “WHERE۟” ʹ૬౰͢ΔߜΓࠐΈΛ௥Ճ ‑dashboardͷfilter
19. 19 filterͷجຊతͳػೳͰͰ͖Δ͜ͱ ! ͜Ε͚ͩͰ΋ʮUIͰࢦఆͨ͠A/BςετΛूܭͯ͠දࣔʯ͕࣮ݱͰ͖Δɻ ! ͔͠͠ɺ͜Ε͚ͩͰ͸ෆे෼ …. Filterͷجຊ : “WHERE”૬౰ͷ৚݅ΛUI(dashboard্)ͰࢦఆͰ͖Δ
20. 20 filterͷجຊతͳػೳͰࠔΔ͜ͱ ! ࢦఆ͕໘౗ ◦ Dashboard։͘ → filterʹೖྗɻ ◦ ຖճඞཁɻ ◦ ࢦఆ͍ͨ͠parameter͕5ͭ͘Β͍͋ͬͨͱͨ͠Β...?
 ! ϦϯΫͱͯ͠ڞ༗Ͱ͖ͳ͍ ◦ Filterʹಉ͡஋Λೖྗ͠ͳ͍ͱಉ͡ը໘͕ग़ͯ͜ͳ͍ɻ ◦ slackͰڞ༗͍ͨ͠৔߹ͳͲʹෆศɻ ຖճfilterΛࢦఆ͢Δͷ͕໘౗ɾϦϯΫͱͯ͠ڞ༗Ͱ͖ͳ͍
21. 21 Override
22. Filterͷઃఆ஋ΛURLͰࢦఆ͢Δػೳ 22 લఏ
    ˔ %BTICPBSE
    ˓ https://looker.example.com/
 dashboards/42 ˔ 'JMUFS
    ˓ /BNFͱ͍͏GJMUFS͕͋ͬͨ৔߹ ࢖༻ྫ
    ˔ %BTICPBSE
    ˓ https://looker.example.com/
 dashboards/42 OBNFUFTU
    ˔ 'JMUFS
    ˓ /BNF
    GJMUFSʹUFTU͕ઃఆ͞Ε ͨঢ়ଶͰ%BTICPSE͕දࣔ Override Look Filters ࢀߟ: https://help.looker.com/hc/en-us/articles/360023571954-Override-Look-Filters-in-the-URL
23. 23 ϦϯΫूΛ༻ҙ͢Δ͚ͩͰɺෳ਺ͷDashboard͕͋Δ͔ͷΑ͏ʹ࢖͑Δ ! (lookML) ! (SQL) OverrideͷԿ͕خ͍͠ͷ͔? ← Looker dashboard΁ͷURLΛੜ੒͢ΔWebΞϓϦ ! ϦϯΫઌͷdashboard͸ಉ͡ɻ ! Override͕ม͑ͯ͋Δɻ https://looker.example.com/dashboards/42?name=test01 https://looker.example.com/dashboards/42?name=test02 https://looker.example.com/dashboards/42?name=test03
24. 24 ϦϯΫͷ༻ҙ ۃ࿦ͱͯ͠͸จࣈྻΛ࿈݁͢Δ͚ͩͰग़དྷΔ
25. 25 Filter + OverrideͰग़དྷΔ͜ͱ “WHERE”૬౰ͷ৚݅ΛUI / URLͰࢦఆͰ͖Δ ! UIͰࢦఆͨ͠A/BςετΛूܭͯ͠ද͕࣮ࣔݱͰ͖Δɻ ! ຖճfilterΛઃఆ͠ͳͯ͘ɺϦϯΫू͔Βඈ΂͹OKɻ ! ϦϯΫͱͯ͠ී௨ʹڞ༗Մೳɻ
26. 26 ! ࠔΔέʔε ◦ WHEREҎ֎΋੍ޚ͍ͨ͠ɻ
 ! ྫ ◦ Test01Ͱ͸ɺ ▪ group_id = 1 ͱ group_id = 2Ͱൺֱ͍ͨ͠ ◦ Test02Ͱ͸ɺ ▪ group_id = 1 ͱ group_id = 3Ͱൺֱ͍ͨ͠ Filterͷݶք ී௨ͷFilter͸”WHERE”ʹ૬౰͢Δ෦෼੍͔͠ޚͰ͖ͳ͍
27. 27 Parameter
28. 28 ParameterͰग़དྷΔ͜ͱ QueryͷWHEREҎ֎ͷ෦෼Λಈతʹมߋ Look MLʹParameterΛఆٛ FilterͰ஋Λࢦఆ ࢦఆͨ͠ఆ਺Λ࢖ͬͨQuery͕ൃߦͰ͖Δ
29. 29 ! 1ຕͷDashboard + URLΛੜ੒͢Δ WebΞϓϦͰࡁΉɻ ! ςετ਺෼DashboardΛίϐϖͤ ͣʹࡁΉ.... Filter + Parameter + OverrideͰग़དྷΔ͜ͱ “WHERE”΍ͦͷଞͷ෦෼ΛಈతʹࢦఆͰ͖ΔɻͦΕΛURLͰࢦఆͰ͖Δɻ
30. 30 LookerΛֶश͢Δ࣌ͷίπ
31. 31 ! ༷ʑͳઃఆཁૉ͕͋Δ ◦ View, Model ◦ Filter, Parameter …
 ! ಥ͖٧ΊΔͱ͜ΕΒͷઃఆ͸ɺ ◦ ňͲΜͳQueryΛൃߦ͍ͨ͠ͷ͔?ʼn ◦ ňͲΜͳมߋΛՄೳʹ͍ͨ͠ͷ͔?ʼn
 ͜ͷ2఺ͷࢦఆ LookerͰDashboardΛ࡞Δͱ͖ͷίπ ňൃߦ͍ͨ͠SQL QueryΛΠϝʔδͯ͠࡞Δʼn BigQuery SQL Query Response
32. 32 filterɾoverrideɾparameterͷ৔߹ Query͕Ͳ͏ͳΔ?ɾͲ͏มߋՄೳʹͳΔ? ͷࢹ఺Ͱ੔ཧՄೳ filter parameter override Query͕Ͳ͏ͳΔ? WHEREΛ௥Ճ Query্Ͱఆ਺͕ ࢖͑Δ - Ͳ͏มߋՄೳʹͳΔ? UIͰ৚݅ΛࢦఆՄೳʹ UIͰ৚݅ΛࢦఆՄೳʹ filter΍parameter͕ URLͰࢦఆՄೳʹ
33. 33 ! 3ͭͷػೳΛ۷ΓԼ͛ ◦ Filter → WHEREͷࢦఆ ◦ Parameter → QueryͰ࢖͑Δఆ਺ͷࢦఆ ◦ Override → ͦͷ2ͭΛURLͰࢦఆͰ͖ΔΑ͏ʹ͢Δ → ͜ΕΒʹΑΓDashboardΛΑΓॊೈʹͳΔɻ ! LookerΛֶश͢Δίπ ◦ ňͲΜͳQueryΛൃߦͰ͖Δ? ʼnňͲΜͳมߋ͕ՄೳʹͳΔ?ʼn ·ͱΊ Lookerͷಛఆͷػೳʹ͍࣮ͭͯྫΛަ͑ͯ঺հ
34. 34 Q&A