Wantedly 新人研修「BigQuery データ分析ノック」を実施した話 / wantedly-newcomer-training-bigquery-training

Transcript

1. ©2019 Wantedly, Inc. Wantedly ৽ਓݚम ʮBigQuery σʔλ෼ੳϊοΫʯΛ࣮ࢪͨ͠࿩ ΤϯδχΞͷ৽ਓݚमNight 17.May 2019

   - Yuya Matsumura @yu-ya4

2. ©2019 Wantedly, Inc. ✓ Yuya Matsumura ✓ Software Engineer ✓

   Wantedly, Inc. Recommendation Team (since April 2018) ✓ Interested in Information Retrieval, Machine Learning Self-Introduction @yu-ya4 @yu__ya4

3. ©2019 Wantedly, Inc. 
   8BOUFEMZ
   ʹ͓͚Δ৽ਓݚमͷ֓ཁ঺հ
   
   ৽ਓݚमʮ#JH2VFSZ
   ෼ੳϊοΫʯͷ঺հ Agenda

4. ©2019 Wantedly, Inc. 
   8BOUFEMZ
   ʹ͓͚Δ৽ਓݚमͷ֓ཁ঺հ
   
   ৽ਓݚमʮ#JH2VFSZ
   ෼ੳϊοΫʯͷ঺հ Agenda

5. ©2019 Wantedly, Inc. ʮWantedly ݻ༗ͷ஌ࣝʢ૊৫ɾϓϩμΫτɾจԽͷ஌ࣝʣʯ΍ʮීวతͳ஌ࣝʢΤ ϯδχΞϦϯάͳͲͷઐ໳஌ࣝɺϏδωεϚφʔ΍๏཯ͷ஌ࣝʣʯΛ਎ʹ͚ͭɺ ৽ਓΤϯδχΞ͕Wantedly ͕ظ଴͢ΔΤϯδχΞ૾ʹۙͮͨ͘Ίͷֶͼͷ৔ Wantedly ʹ͓͚Δ৽ਓݚम

   ৽ਓΤϯδχΞ Wantedly ͕ظ଴͢Δ ΤϯδχΞ૾ ৽ਓݚम https://speakerdeck.com/south37/xin-zu-yan-xiu-dexing-u-isucon-falsemei-li?slide=9

6. ©2019 Wantedly, Inc. Wantedly ͕ظ଴͢ΔΤϯδχΞ૾ 
   ϓϩμΫτͰՁ஋Λಧ͚ΔͨΊʹɺٕज़Λ࢖͏
   
   ඞཁͰ͋Ε͹͋ΒΏΔٕज़ʹ௅ઓ͢Δ https://speakerdeck.com/south37/xin-zu-yan-xiu-dexing-u-isucon-falsemei-li?slide=10

7. ©2019 Wantedly, Inc. Wantedly ʹ͓͚Δ৽ਓݚम͕ఏڙ͢Δ΋ͷ 
   ϓϩμΫτͰՁ஋Λಧ͚ΔͨΊʹɺٕज़Λ࢖͏
   
   
   
   
   ⾣
   ϓϩμΫτʹՁ஋Λ༩͑Δٕज़ΛֶͿػձ
   
   ඞཁͰ͋Ε͹͋ΒΏΔٕज़ʹ௅ઓ͢Δ
   
   
   
   
   ⾣
   ٕज़తʹ޿͍ྖҬʹ৮ΕΔܦݧ https://speakerdeck.com/south37/xin-zu-yan-xiu-dexing-u-isucon-falsemei-li?slide=10

8. ©2019 Wantedly, Inc. Wantedly ʹ͓͚Δ৽ਓݚमͷྫʢ̍ʣ https://speakerdeck.com/tan_z_tan/how-to-get-involved-with- machine-learning-at-wantedly

9. ©2019 Wantedly, Inc. Wantedly ʹ͓͚Δ৽ਓݚमͷྫʢ̎ʣ https://speakerdeck.com/south37/new-grad-isucon-for-learning

10. ©2019 Wantedly, Inc. Wantedly ʹ͓͚Δ৽ਓݚमͷྫʢ̏ʣ https://speakerdeck.com/kawasy/managing-your-leader

11. ©2019 Wantedly, Inc. ଞʹ΋͍Ζ͍Ζ Wantedly ʹ͓͚Δ৽ਓݚमͷྫ https://wantedlyinc.com/ja/presentations

12. ©2019 Wantedly, Inc. 
    8BOUFEMZ
    ʹ͓͚Δ৽ਓݚमͷ֓ཁ঺հ
    
    ৽ਓݚमʮ#JH2VFSZ
    ෼ੳϊοΫʯͷ঺հ Agenda

13. ©2019 Wantedly, Inc. BigQuery σʔλ෼ੳϊοΫ ୲౰ͨ͠ݚम

14. ©2019 Wantedly, Inc. ʮBigQuery Λ༻͍ͯ Wantedly ͷαʔϏεʹ͍ͭͯͷ ඞཁͳ৘ใΛద੾ʹऔಘͰ͖ΔΑ͏ʹͳΔʯ ͜ͷݚमͷ໨త

15. ©2019 Wantedly, Inc. Why Wantedly Ͱ͸ΞϓϦέʔγϣϯΤϯδχΞ͕ࣗ෼ࣗ਎Ͱσʔλ ෼ੳΛߦͬͯ՝୊Λ೺Ѳͯ͠ࢪࡦΛ࣮ࢪ͠ɼͦͷ݁ՌͷධՁ·Ͱ Λߦ͍ͬͯΔ ࣗ཯తʹಈ͍ͯαʔϏεվળΛਐΊΒΕΔମ੍ εϐʔυײɼαʔϏεѪ…

16. ©2019 Wantedly, Inc. ✓ ໰͍΍ԾઆΛཱͯΔ ✓ ݕূํ๏Λߟ͑Δ ✓ ࣮૷͢Δ ✓

    ϩάΛऔΔ ✓ ूܭ͢Δ ✓ ධՁ͢Δ https://speakerdeck.com/ngtk/lets-be-able-to-propose-measures-for-product-growth + ͋Δఔ౓ج൫͸੔͍ͬͯΔͷͰ͋·Γߟ͑Δ͜ͱ͸ͳ͍ ࢪࡦ࣮ࢪͷྲྀΕ ίί internal only ͜ͷݚमͷҐஔ͚ͮ

17. ©2019 Wantedly, Inc. ͜ͷݚमͷҐஔ͚ͮ ✓ SQL ͷ஌ࣝ • ͜Ε͕ͳ͍ͱ࢝·Βͳ͍ɻࢥͬͨ௨ΓͷσʔλΛਖ਼͘͠औಘͰ͖ΔΑ͏ʹʂ ✓

    υϝΠϯ஌ࣝ • ݻ༗ͷσʔλߏ଄ͳͲʢͲ͜ͷςʔϒϧʹͲͷσʔλ͕͋Δͷ͔ʣ • ൺֱతҰൠతͳ΋ͷʢbot ͔ΒͷΞΫηε΍ࣗࣾͷࣾһͷσʔλʹؾΛ͚ͭΑ͏ʣ ✓ ౷ܭͳͲͷ஌ࣝ • ਖ਼͘͠਺ࣈΛूܭ͠Α͏ • ਖ਼͘͠ࢪࡦͷ݁ՌΛධՁ͠Α͏ ✓ όΠΞεʹؔ͢Δ஌ࣝ • ֬ূόΠΞεͳͲʹؾΛ͚ͭΑ͏ σʔλΛਖ਼͘͠ूܭɾධՁ͢ΔͨΊʹඞཁͳ஌ࣝ

18. ©2019 Wantedly, Inc. ͜ͷݚमͷҐஔ͚ͮ ✓ SQL ͷ஌ࣝ • ͜Ε͕ͳ͍ͱ࢝·Βͳ͍ɻࢥͬͨ௨ΓͷσʔλΛਖ਼͘͠औಘͰ͖ΔΑ͏ʹʂ ✓

    υϝΠϯ஌ࣝ • ݻ༗ͷσʔλߏ଄ͳͲʢͲ͜ͷςʔϒϧʹͲͷσʔλ͕͋Δͷ͔ʣ • ൺֱతҰൠతͳ΋ͷʢbot ͔ΒͷΞΫηε΍ࣗࣾͷࣾһͷσʔλʹؾΛ͚ͭΑ͏ʣ ✓ ౷ܭͳͲͷ஌ࣝ • ਖ਼͘͠਺ࣈΛूܭ͠Α͏ • ਖ਼͘͠ࢪࡦͷ݁ՌΛධՁ͠Α͏ ✓ όΠΞεʹؔ͢Δ஌ࣝ • ֬ূόΠΞεͳͲʹؾΛ͚ͭΑ͏ σʔλΛਖ਼͘͠ूܭɾධՁ͢ΔͨΊʹඞཁͳ஌ࣝ ίί

19. ©2019 Wantedly, Inc. 1. BigQuery ͷجຊతͳ஌ࣝ • جຊతͳ SQL ͷ஌ࣝ͸͋Δલఏ

    • BigQuery ͳΒͰ͸ͷಛ௃Λ཈͑Δʢྻࢦ޲ɼARRAY ͷѻ͍ɼWindow ؔ਺…ʣ 2. Wantedly ͷαʔϏεʹ͍ͭͯͷσʔλͷυϝΠϯ஌ࣝ • Ұൠతͳͱ͜ΖʢBotɼRPAɼࣗࣾࣾһͷߦಈ …ʣ͸ผͷߨٛʢʰσʔλΛਖ਼͘͠ݟΔͨΊʹʱʣͰ • ςʔϒϧϨϕϧͷ஌ࣝʢʮ࿩Λฉ͖ʹߦ͖͍ͨʯͷσʔλͱ͸ʁɼεΧ΢τػೳͷݕࡧϩά͸Ͳ͜ʹ͋Δʁʣ 3. σʔλΛ෼ੳ͢Δࡍͷࢦඪʹ͍ͭͯͷ஌ࣝ • ҰൠతͳࢦඪʢMAUɼARPUɼRetentionɼPVɼImpression…ʣ • υϝΠϯґଘͳࢦඪʢVisit ͷCVR ͱ͸ʁɼ༗ྉاۀͷܧଓ཰ͱ͸ʁʣ ͜ͷݚमΛ௨ͯ͠ಘͯ΄͍͠΋ͷ

20. ©2019 Wantedly, Inc. ࣮ࡍతͳ໰୊Λղ͍ͯ਎ʹ͚ͭΑ͏ How …΍Βͳ͖Ό਎ʹ͔ͭͳ͍ʂ

21. ©2019 Wantedly, Inc. ✓ 2019೥4݄1೔ʹ͓͚Δ PC͔ΒืूҰཡ΁ͷϦΫΤετ਺ʢPVʣ ✓ 2019೥4݄1೔͔Β4݄7೔·Ͱͷ1िؒͷΞΫςΟϒͳϩάΠϯϢʔβ਺ʢWAUʣ ✓ εΧ΢τػೳʹ͓͚ΔΩʔݕࡧʹ༻͍ΒΕͨΩʔϫʔυ͝ͱͷݕࡧճ਺

    ✓ 2019೥4݄1೔ʹ1෼ؒ͋ͨΓ100ճҎ্ΞΫηε͖͍ͯͯ͠ΔIPΞυϨε ✓ 2018 ೥ͷ೔ຊࠃ಺ʹ͓͚Δ ARPU ਪҠ What …ଞʹ΋੝Γͩ͘͞Μʂ

22. ©2019 Wantedly, Inc. ѹ౗త੒௕ Results …ݚमΛड͚ͨ৽ਓΤϯδχΞ΋ɼݚमΛ࣮ࢪͨ͠ࢲࣗ਎΋ʂ

23. ©2019 Wantedly, Inc. Summary 㾎 8BOUFEMZ
    ʹ͓͚Δ৽ਓݚम
    w ৽ਓΤϯδχΞ͕
    8BOUFEMZ
    ͕ظ଴͢ΔΤϯδχΞ૾ʹۙͮͨ͘Ίͷֶͼͷ৔
    㾎 8BOUFEMZ
    ʹ͓͚Δ৽ਓݚम͕ఏڙ͢Δ΋ͷ
    

    w ϓϩμΫτʹՁ஋Λ༩͑Δٕज़ΛֶͿػձ
    w ٕज़తʹ޿͍ྖҬʹ৮ΕΔܦݧ
    㾎 ʮ#JH2VFSZ
    σʔλ෼ੳϊοΫʯͷ࣮ࢪ
    w #JH2VFSZ
    ͷجຊతͳ஌ࣝ
    w 8BOUFEMZ
    ͷαʔϏεʹ͍ͭͯͷσʔλͷυϝΠϯ஌ࣝ
    w σʔλΛ෼ੳ͢Δࡍͷࢦඪʹ͍ͭͯͷ஌ࣝ