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

2 ࠓ೔ͷςʔϚ

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

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

5 ࣗݾ঺հ

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

7 ձࣾ঺հ

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

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

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

11 എܠ

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

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

14 લఏ

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 filterػೳͷجຊ

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

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

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

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

21 Override

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

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 ϦϯΫͷ༻ҙ ۃ࿦ͱͯ͠͸จࣈྻΛ࿈݁͢Δ͚ͩͰग़དྷΔ

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

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

27 Parameter

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

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

30 LookerΛֶश͢Δ࣌ͷίπ

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

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

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

34 Q&A