LookerのDashboardをより柔軟に作る

by nagai shinya

Slide 1

Slide 1 text

1 LookerͷDashboardΛΑΓॊೈʹ࡞Δ ͋Δ͍͸Lookerͷֶशํ๏ʹ͍ͭͯ Looker User Meetup in Tokyo #3 Nagai shinya @ Mercari, Inc.

Slide 2

Slide 2 text

2 ࠓ೔ͷςʔϚ

Slide 3

Slide 3 text

3 ! 3ͭͷػೳΛ঺հɻ ○ Filter ○ Override ○ Parameter → Dashboard͕ΑΓॊೈʹѻ͑ΔΑ͏ʹͳΔɻ ! DashboardΛ࡞Δਓʹ޲͚ͯͨ಺༰ɻ ࠓ೔ͷϝΠϯςʔϚ Lookerͷಛఆͷػೳʹ͍ͭͯ۷ΓԼ͛Δ

Slide 4

Slide 4 text

4 ! Lookerʹର͢Δॴײ ○ ศར͕ͩ׳Ε͕ඞཁͳπʔϧɻ ○ Πχγϟϧͷֶशίετ͕ͪΐͬͱߴ͍ɻ  ! ͦ͜Ͱɺ ○ ň͜͏ଊ͑Δͱɺཧղ͠΍͘͢ͳΔ͔΋?ŉΛ঺հɻ ňLookerͷ֤छػೳΛ͜͏ଊ͑Δͱཧղ͠΍͍͔͢΋?ŉͷ঺հ αϒςʔϚ

Slide 5

Slide 5 text

5 ࣗݾ঺հ

Slide 6

Slide 6 text

6 ࣗݾ঺հ ! ϝϧΧϦ ○ 2019೥1݄ೖࣾɻ ○ ௚ۙ : A/Bςετͷ෼ੳɺඪ४ԽͳͲ ○ Lookerར༻΋͔͜͜Β։࢝ɻ  ! աڈ ○ डୗσʔλ෼ੳ ○ ιʔγϟϧήʔϜ੍࡞ ιϑτ΢ΣΞΤϯδχΞ ○ ੡ༀձࣾ৘ใγεςϜ෦໳ ӬҪ ৳໻ ( Nagai Shinya )

Slide 7

Slide 7 text

7 ձࣾ঺հ

Slide 8

Slide 8 text

8 ೥݄೔ ίϛϡχςΟαʔϏεͷӡӦ ෱ࢁ੣ ˟౦ژ౎ौ୩۠ઍବϲ୩  .4%Ϗϧ ձࣾ֓ཁ גࣜձࣾϝϧΧϦ ઃཱ ࢿຊۚ ࣄۀ಺༰ ୅දऀ ॴࡏ஍ ڌ఺ ೥݄೔ ඦສԁʢࢿຊ४උؚۚΉʣ εϚʔτϑΥϯ޲͚ϑϦϚΞϓϦ  ʮϝϧΧϦʯͷاըɾ։ൃɾӡӦ ࢁాਐଠ࿠ ˟౦ژ౎ߓ۠࿡ຊ໦ ࿡ຊ໦ώϧζ৿λϫʔ ౦ژɺઋ୆ɺ෱Ԭ ϝϧΧϦάϧʔϓ͸ɺגࣜձࣾϝϧΧϦͱɺͦͷ࿈݁ࢠձࣾͰߏ੒͞Ε͍ͯ·͢ɻ ઃཱ ࣄۀ಺༰ ୅දऀ ॴࡏ஍ ೥݄೔ ۚ༥ؔ࿈ͷ৽نࣄۀ ੨༄௚थ ˟౦ژ౎ߓ۠࿡ຊ໦  ࿡ຊ໦ώϧζ৿λϫʔ ઃཱ ࣄۀ಺༰ ୅දऀ ॴࡏ஍ ೥݄ 64൛ϝϧΧϦͷاըɾ։ൃɾӡӦ +PIO-BHFSMJOH 1BMP"MUP $BMJGPSOJB 1PSUMBOE 0SFHPO #PTUPO $BNCSJEHF .BTTBDIVTFUUT ઃཱ ࣄۀ಺༰ ୅දऀ ॴࡏ஍ גࣜձࣾϝϧϖΠ Mercari, Inc.  ʢUSʣ ϚΠέϧגࣜձࣾ ઃཱ ࣄۀ಺༰ ୅දऀ ॴࡏ஍ ೥݄೔ ϑοτϘʔϧΫϥϒӡӦ খઘจ໌  ҵ৓ݝཱΧγϚαοΧʔελδΞϜࢦఆ؅ཧ ҵ৓ݝࣛౢࢢ҄ੜ౦ࢁ൪஍ גࣜձࣾ  ࣛౡΞϯτϥʔζɾΤ ϑɾγʔ

Slide 9

Slide 9 text

9 ϝϧΧϦͱ͸ ˔αʔϏε։࢝೔ɿ2013೥7݄ ˔ରԠOSɿAndroidɺiOS ˞8FCϒϥ΢β͔Β΋ར༻Մೳ ˔ར༻ྉɿແྉ ˞ചΕͨͱ͖ͷख਺ྉɿൢചՁ֨ͷ10% ˔ରԠ஍Ҭɾݴޠɿ೔ຊɾ೔ຊޠجຊ࢓༷ ͦΕΛඞཁͱ͢Δਓͷखʹ౉Γɺ࢖༻͞ΕΔ͜ͱʹتͼΛײ ͡ɺ·ͨߪೖऀ͸ɺଟ࠼͔ͭϢχʔΫͳ঎඼ͷத͔Βʮๅ୳ ͠ʯײ֮Ͱ۷Γग़͠෺Λݟ͚ͭΔങ͍෺ମݧΛָ͠ΜͰ͍· ͢ɻ͞ΒʹʮϝϧΧϦʯͰ͸ɺ෺ͷചങ͚ͩͰ͸ͳ͘ɺग़඼ ऀɾߪೖऀؒͷνϟοτ΍ʮ͍͍ͶʂʯػೳΛ௨ͯ͡ɺ͓٬͞ ·ؒͷίϛϡχέʔγϣϯ΋׆ൃʹߦΘΕ͍ͯ·͢ɻ ϑϦϚΞϓϦʮϝϧΧϦʯ͸ɺݸਓ͕؆୯ʹதݹ඼ͷചങΛߦ͑Δ $UP$ϚʔέοτϓϨΠεͰ͢ɻग़඼ऀɾߪೖऀ૒ํ͕ɺ҆શɾ҆৺ͳऔ ҾΛָ͠ΜͰ͍͚ͨͩΔαʔϏεΛ໨ࢦ͠ɺʮϝϧΧϦʯ͕Ұ࣌తʹߪ ೖ୅ۚΛ༬͔ΔΤεΫϩʔܾࡁΛ׆༻ͨ͠औҾ؀ڥͷ੔උ΍ɺ؆୯͔ͭ खࠒͳՁ֨ͷ഑ૹΦϓγϣϯɺࠩผԽ͞ΕͨϢχʔΫͳ͓٬͞·ମݧΛ ఏڙ͍ͯ͠·͢ɻଟ͘ͷग़඼ऀ͸ɺࣗ෼ʹͱͬͯඞཁͰͳ͘ͳͬͨϞϊ ͕ɺ

Slide 10

Slide 10 text

10 ϝϧΧϦάϧʔϓͱLooker શࣾతͳBIπʔϧͱͯ͠׆༻த https://tech.mercari.com/entry/2018/08/22/155059 https://speakerdeck.com/haseryo/20190906-looker-user-meetup-merpay

Slide 11

Slide 11 text

11 എܠ

Slide 12

Slide 12 text

12 A/Bςετ ΞϓϦʹෳ਺ͷόϦΤʔγϣϯΛ༻ҙͯ͠ɺͲͪΒ͕ྑ͍͔ൺֱ͢Δ BUTTON BUTTON γϯϓϧͳྫ ! 100໊ͷ͓٬͞· ○ 50໊ → ੺͍Ϙλϯ ○ 50໊ → ੨͍Ϙλϯ ! ͲͪΒ͕ΑΓλοϓ͞ΕΔ͔?  ࣮ࡍ ! ෳ਺ͷςετͷ࣮ࢪɻ ! ෳ਺ͷࢦඪͷൺֱɻ

Slide 13

Slide 13 text

13 χʔζ ! ӈਤ ○ A/Bςετ༻ͷDashboardɻ ○ 20ʙ30ݸఔ౓ͷࢦඪɻ ○ ಉ਺ͷάϥϑ( Tile )  ! χʔζ ○ 10݅ͷA/BςετΛ࣮ࢪͨ͠৔߹ɺ ○ 10ݸ ӈਤͷDashboard͕ཉ͍͠ɻ  → ίϐϖͰ࡞Δͷ͸େม͗͢Δ...  ! ͦΜͳ࣌ɺ໾ʹཱͭػೳ ňࣅͨΑ͏ͳDashboardΛ୔ࢁ࡞Γ͍ͨŉͱ͍͏χʔζ

Slide 14

Slide 14 text

14 લఏ

Slide 15

Slide 15 text

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)

Slide 16

Slide 16 text

16 filterػೳͷجຊ

Slide 17

Slide 17 text

17 Filterͷجຊ ᶃ 1ͭͷTileʹରͯ͠ɺSQLͷ “WHERE۟” ʹ૬౰͢ΔߜΓࠐΈΛ௥Ճ ྫ ! 1ͭͷάϥϑʹରͯ͠ɺ ! ಛఆͷA/Bςετʹߜͬͯूܭ ‏ tileͷfilter

Slide 18

Slide 18 text

18 Filterͷجຊ ᶄ ྫ ! ෳ਺ͷάϥϑʹରͯ͠ɺ ! ಛఆͷA/Bςετʹߜͬͯूܭ ෳ਺ͷTileʹରͯ͠ɺSQLͷ “WHERE۟” ʹ૬౰͢ΔߜΓࠐΈΛ௥Ճ ‑dashboardͷfilter

Slide 19

Slide 19 text

19 filterͷجຊతͳػೳͰͰ͖Δ͜ͱ ! ͜Ε͚ͩͰ΋ʮUIͰࢦఆͨ͠A/BςετΛूܭͯ͠දࣔʯ͕࣮ݱͰ͖Δɻ ! ͔͠͠ɺ͜Ε͚ͩͰ͸ෆे෼ …. Filterͷجຊ : “WHERE”૬౰ͷ৚݅ΛUI(dashboard্)ͰࢦఆͰ͖Δ

Slide 20

Slide 20 text

20 filterͷجຊతͳػೳͰࠔΔ͜ͱ ! ࢦఆ͕໘౗ ○ Dashboard։͘ → filterʹೖྗɻ ○ ຖճඞཁɻ ○ ࢦఆ͍ͨ͠parameter͕5ͭ͘Β͍͋ͬͨͱͨ͠Β...?  ! ϦϯΫͱͯ͠ڞ༗Ͱ͖ͳ͍ ○ Filterʹಉ͡஋Λೖྗ͠ͳ͍ͱಉ͡ը໘͕ग़ͯ͜ͳ͍ɻ ○ slackͰڞ༗͍ͨ͠৔߹ͳͲʹෆศɻ ຖճfilterΛࢦఆ͢Δͷ͕໘౗ɾϦϯΫͱͯ͠ڞ༗Ͱ͖ͳ͍

Slide 21

Slide 21 text

21 Override

Slide 22

Slide 22 text

Filterͷઃఆ஋ΛURLͰࢦఆ͢Δػೳ 22 લఏ ˔ %BTICPBSE ˓ https://looker.example.com/  dashboards/42 ˔ 'JMUFS ˓ /BNFͱ͍͏GJMUFS͕͋ͬͨ৔߹ ࢖༻ྫ ˔ %BTICPBSE ˓ https://looker.example.com/  dashboards/42 OBNFUFTU ˔ 'JMUFS ˓ /BNFGJMUFSʹUFTU͕ઃఆ͞Ε ͨঢ়ଶͰ%BTICPSE͕දࣔ Override Look Filters ࢀߟ: https://help.looker.com/hc/en-us/articles/360023571954-Override-Look-Filters-in-the-URL

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

24 ϦϯΫͷ༻ҙ ۃ࿦ͱͯ͠͸จࣈྻΛ࿈݁͢Δ͚ͩͰग़དྷΔ

Slide 25

Slide 25 text

25 Filter + OverrideͰग़དྷΔ͜ͱ “WHERE”૬౰ͷ৚݅ΛUI / URLͰࢦఆͰ͖Δ ! UIͰࢦఆͨ͠A/BςετΛूܭͯ͠ද͕࣮ࣔݱͰ͖Δɻ ! ຖճfilterΛઃఆ͠ͳͯ͘ɺϦϯΫू͔Βඈ΂͹OKɻ ! ϦϯΫͱͯ͠ී௨ʹڞ༗Մೳɻ

Slide 26

Slide 26 text

26 ! ࠔΔέʔε ○ WHEREҎ֎΋੍ޚ͍ͨ͠ɻ  ! ྫ ○ Test01Ͱ͸ɺ ■ group_id = 1 ͱ group_id = 2Ͱൺֱ͍ͨ͠ ○ Test02Ͱ͸ɺ ■ group_id = 1 ͱ group_id = 3Ͱൺֱ͍ͨ͠ Filterͷݶք ී௨ͷFilter͸”WHERE”ʹ૬౰͢Δ෦෼੍͔͠ޚͰ͖ͳ͍

Slide 27

Slide 27 text

27 Parameter

Slide 28

Slide 28 text

28 ParameterͰग़དྷΔ͜ͱ QueryͷWHEREҎ֎ͷ෦෼Λಈతʹมߋ Look MLʹParameterΛఆٛ FilterͰ஋Λࢦఆ ࢦఆͨ͠ఆ਺Λ࢖ͬͨQuery͕ൃߦͰ͖Δ

Slide 29

Slide 29 text

29 ! 1ຕͷDashboard + URLΛੜ੒͢Δ WebΞϓϦͰࡁΉɻ ! ςετ਺෼DashboardΛίϐϖͤ ͣʹࡁΉ.... Filter + Parameter + OverrideͰग़དྷΔ͜ͱ “WHERE”΍ͦͷଞͷ෦෼ΛಈతʹࢦఆͰ͖ΔɻͦΕΛURLͰࢦఆͰ͖Δɻ

Slide 30

Slide 30 text

30 LookerΛֶश͢Δ࣌ͷίπ

Slide 31

Slide 31 text

31 ! ༷ʑͳઃఆཁૉ͕͋Δ ○ View, Model ○ Filter, Parameter …  ! ಥ͖٧ΊΔͱ͜ΕΒͷઃఆ͸ɺ ○ ňͲΜͳQueryΛൃߦ͍ͨ͠ͷ͔?ŉ ○ ňͲΜͳมߋΛՄೳʹ͍ͨ͠ͷ͔?ŉ  ͜ͷ2఺ͷࢦఆ LookerͰDashboardΛ࡞Δͱ͖ͷίπ ňൃߦ͍ͨ͠SQL QueryΛΠϝʔδͯ͠࡞Δŉ BigQuery SQL Query Response

Slide 32

Slide 32 text

32 filterɾoverrideɾparameterͷ৔߹ Query͕Ͳ͏ͳΔ?ɾͲ͏มߋՄೳʹͳΔ? ͷࢹ఺Ͱ੔ཧՄೳ filter parameter override Query͕Ͳ͏ͳΔ? WHEREΛ௥Ճ Query্Ͱఆ਺͕ ࢖͑Δ - Ͳ͏มߋՄೳʹͳΔ? UIͰ৚݅ΛࢦఆՄೳʹ UIͰ৚݅ΛࢦఆՄೳʹ filter΍parameter͕ URLͰࢦఆՄೳʹ

Slide 33

Slide 33 text

33 ! 3ͭͷػೳΛ۷ΓԼ͛ ○ Filter → WHEREͷࢦఆ ○ Parameter → QueryͰ࢖͑Δఆ਺ͷࢦఆ ○ Override → ͦͷ2ͭΛURLͰࢦఆͰ͖ΔΑ͏ʹ͢Δ → ͜ΕΒʹΑΓDashboardΛΑΓॊೈʹͳΔɻ ! LookerΛֶश͢Δίπ ○ ňͲΜͳQueryΛൃߦͰ͖Δ? ŉňͲΜͳมߋ͕ՄೳʹͳΔ?ŉ ·ͱΊ Lookerͷಛఆͷػೳʹ͍࣮ͭͯྫΛަ͑ͯ঺հ

Slide 34

Slide 34 text

34 Q&A