FirebaseとBigQueryによるアプリのKPI分析（CMの効果検証編）

iOSDC 2017での発表資料です。

Transcript

1. 'JSFCBTFͱ#JH2VFSZʹΑΔ
   ΞϓϦͷ,1*෼ੳ
   ʢ$.ͷޮՌݕূฤʣ
   

   View Slide

2. ࣗݾ঺հ
   2
   

   View Slide

3. 3
   ࣗݾ঺հ
   shoby @shobyshoby
   w 'BCMJD JOD
   w 4IPUB*[BLB
   w J04$.෼ੳ
   

   View Slide

4. ϑϦϧ '3*-
   ຬ଍౓/PͷϑϦϚΞϓϦ
   
   

   View Slide

5. 5
   ೥Ͱ
   %-਺Λສ͔Βສ·Ͱ

   ૿΍ͨ͠࿩Λ͠·͢ʂ
   ೥݄ ೥݄
   

   View Slide

6. ֓ཁ
   6
   

   View Slide

7. 7
   ֓ཁ
   w ϚʔέςΟϯά෼ੳͷॏཁੑ
   w 'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ
   w $.ͷඅ༻ରޮՌվળ
   

   View Slide

8. ϚʔέςΟϯά෼ੳͷॏཁੑ
   8
   

   View Slide

9. 9
   ϚʔέςΟϯά෼ੳͷॏཁੑ
   w ੒ख़ࢢ৔Ͱ͸ϚʔέςΟϯάͷॏཁ౓͕૿͢
   w ΤϯδχΞͱͯ͠ͷεΩϧΛ׆͔ͤΔ
   

   View Slide

10. ϑϦϧΛऔΓר͘ঢ়گ
    10
    

    View Slide

11. 11
    ϚʔέςΟϯά෼ੳͷॏཁੑϑϦϧΛऔΓר͘ঢ়گ
    w ϓϩμΫτͱࢢ৔͕੒ख़
    w ݱঢ়ͷػೳͰϢʔβʔ͕΄΅ຬ଍
    w ن໛ͰαʔϏε͕બ͹ΕΔΑ͏ʹͳΔ
    w αʔϏεن໛͕֦େͯ͠Ϣʔβʔଐੑ͕ଟ༷Խ
    w ϢʔβʔΠϯλϏϡʔ͚ͩͰ͸ҙࢥܾఆ͕ࠔ೉
    

    View Slide

12. 12
    ྑ͍ΞϓϦΛ࡞Δ͚ͩͰ͸
    αʔϏε͕੒௕͠ͳ͍ʂ
    

    View Slide

13. ੒ख़ࢢ৔Ͱ

    αʔϏεΛ੒௕ͤ͞Δʹ͸
    13
    

    View Slide

14. 14
    ϚʔέςΟϯά෼ੳͷॏཁੑ੒ख़ࢢ৔ͰαʔϏεΛ੒௕ͤ͞Δʹ͸
    w ৽نϢʔβʔͷ֫ಘޮ཰Λվળ
    w ϚʔέςΟϯάͷඅ༻ରޮՌΛվળ
    w Ϣʔβʔͷܧଓ཰Λվળ
    w ఆੑʴఆྔ෼ੳͰΞϓϦͷطଘػೳΛվળ
    

    View Slide

15. 15
    վળͷΠϝʔδਤ
    ΞϓϦͷܧଓ཰վળ
    ϚʔέςΟϯάͷվળ
    

    View Slide

16. 16
    ͲͪΒ΋

    ,1*෼ੳʹΑΔվળ͕ॏཁʂ
    

    View Slide

17. ։ൃͱϚʔέςΟϯάͷྨࣅੑ
    17
    

    View Slide

18. 18
    ϚʔέςΟϯά෼ੳͷॏཁੑ։ൃͱϚʔέςΟϯάͷྨࣅੑͱΤϯδχΞͷ਌࿨ੑ
    w ͲͪΒ΋αʔϏεͷ੒௕ʹߩݙ
    w ͲͪΒ΋,1*Λ෼ੳ͠ͳ͕Β1%$"Λճ͢
    w ͲͪΒ΋౤ࢿͰ͋Γɺඅ༻ରޮՌ͕େ੾
    w ։ൃɿਓతࢿݯΛ౤ࢿ
    w ϚʔέςΟϯάɿࢿຊΛ౤ࢿ
    

    View Slide

19. 19
    ͲͪΒ΋ࣅ͍ͯΔ
    

    View Slide

20. ΤϯδχΞͱϚʔέςΟϯάͷ

    ਌࿨ੑ
    20
    

    View Slide

21. 21
    ϚʔέςΟϯά෼ੳͷॏཁੑ։ൃͱϚʔέςΟϯάͷྨࣅ఺
    w ΤϯδχΞͷಘҙͳ͜ͱ
    w σʔλۦಈͷαʔϏεվળ
    w ΞΫηεϩά΍Πϕϯτϩάͷ෼ੳ
    w ౷ܭֶͳͲͷ਺ֶ
    w ࿦ཧతͳҙࢥܾఆ
    

    View Slide

22. 22
    ΤϯδχΞ͸ϚʔέςΟϯάͰ΋

    αʔϏεͷ੒௕ʹߩݙͰ͖Δʂ
    

    View Slide

23. 'JSFCBTFͱ#JH2VFSZʹΑΔ
    ,1*෼ੳ
    23
    

    View Slide

24. ,1*෼ੳʹॏཁͳ͜ͱ
    24
    

    View Slide

25. 25
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ,1*෼ੳʹॏཁͳ͜ͱ
    w ՄࢹԽ
    w ཁҼ෼ੳ
    

    View Slide

26. 26
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ,1*෼ੳʹॏཁͳ͜ͱ
    w ՄࢹԽ
    w σʔλΛ༻͍ͨٞ࿦͕ՄೳʹͳΔ
    w ϑϦϧͰ͸3FEBTIΛ࢖༻
    

    View Slide

27. 27
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ,1*෼ੳʹॏཁͳ͜ͱ
    w ཁҼ෼ੳ
    w ,1*Λ෼ղͯ͠ɺ૿ݮͷཁҼΛಛఆ
    w ֎෦ཁҼͰ͸ͳ͘ࢪࡦ͕ώοτͨ͜͠ͱΛ֬ೝ
    

    View Slide

28. 28
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ,1*෼ੳʹॏཁͳ͜ͱ
    w $.ظؒதͷΠϯετʔϧ਺͕૿Ճ
    ˠΦʔΨχοΫΠϯετʔϧ਺͕૿Ճ
    ˠ์ૹ஍ҬͷΦʔΨχοΫΠϯετʔϧ਺͕૿Ճ
    ˠ์ૹ஍Ҭͷλʔήοτࢹௌऀ૚ͷΦʔΨχοΫ
    Πϯετʔϧ਺͕૿Ճ
    ˠ$.ޮ͍ͯΔ
    

    View Slide

29. 29
    $.͸Πϯετʔϧʹؔͯ͠

    ৄࡉͳ෼ੳ͕ඞཁ
    

    View Slide

30. 'JSFCBTFͱ#JH2VFSZʹΑΔ

    σʔλूܭ
    30
    

    View Slide

31. 31
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ'JSFCBTFͱ#JH2VFSZʹΑΔσʔλूܭ
    w 'JSFCBTFͱ#JH2VFSZͱ͸
    w 'JSFCBTFɿΞϓϦ޲͚૯߹ϓϥοτϑΥʔϜ
    w #JH2VFSZɿ҆Ձͳσʔλ΢ΣΞϋ΢ε
    

    View Slide

32. 32
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ'JSFCBTFͱ#JH2VFSZʹΑΔσʔλूܭ
    w 'JSFCBTFͱ#JH2VFSZͷ࿈ܞ
    w #JH2VFSZʹσʔλΛࣗಈͰ&YQPSU
    w 'JSFCBTF"OBMZUJDTͷੜσʔλ͕ࢀরՄೳ
    w શϢʔβʔɺશΠϕϯτͷσʔλʂ
    

    View Slide

33. 33
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ'JSFCBTFͱ#JH2VFSZʹΑΔσʔλूܭ
    w ࢀরͰ͖ΔࣗಈܭଌΠϕϯτ
    w ॳճىಈ
    w ηογϣϯ։࢝
    w ΞϓϦͷόʔδϣϯΞοϓ
    w ը໘ભҠʢࣗಈεΫϦʔϯτϥοΩϯάʣ
    IUUQTTVQQPSUHPPHMFDPNGJSFCBTFBOTXFS IMKB
    

    View Slide

34. 34
    'JSFCBTFͱ#JH2VFSZʹΑΔ,1*෼ੳ'JSFCBTFͱ#JH2VFSZʹΑΔσʔλूܭ
    w ࢀরͰ͖ΔࣗಈܭଌϢʔβʔ৘ใ
    w 04ͱΞϓϦͷόʔδϣϯ
    w ݴޠ
    w ࠃɺ஍Ҭʢ౎ಓ෎ݝʣɺࢢ۠ொଜ
    w *%'"
    IUUQTTVQQPSUHPPHMFDPNGJSFCBTFBOTXFS IMKB
    

    View Slide

35. 35
    ͜ΕΒͷσʔλΛ࢖ͬͯ

    ,1*෼ੳΛߦ͏
    

    View Slide

36. $.ͷඅ༻ରޮՌվળ
    36
    

    View Slide

37. 37
    $.ͷඅ༻ରޮՌվળ
    w Πϯετʔϧ਺͸ॏཁͳ,1*ͷҰͭ
    w ಉ͡අ༻Ͱଟ͘ͷΠϯετʔϧΛ֫ಘ͍ͨ͠
    

    View Slide

38. 38
    $.ͷඅ༻ରޮՌվળ
    w $.ͷඅ༻ରޮՌվળϙΠϯτ
    w ౤ԼՕॴ
    w ޿ࠂ಺༰
    

    View Slide

39. 39
    ౤ԼՕॴͷ࠷దԽ͸

    ਺஋ͷΈͰߦ͑Δ
    

    View Slide

40. ౤ԼՕॴͷ࠷దԽ
    40
    

    View Slide

41. 41
    $.ͷඅ༻ରޮՌվળ౤ԼՕॴͷ࠷దԽ
    w ౤ԼՕॴͷ࠷దԽ
    w අ༻ରޮՌͷߴ͍Օॴͷ౤ԼྔΛ૿΍͢
    w ਪఆ஋Λग़ͯ͠૬ରൺֱͰ൑அ
    

    View Slide

42. 42
    ࠓճ͸

    ౎ಓ෎ݝผ౤Լྔͷ

    ࠷దԽͷ࿩
    

    View Slide

43. ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    43
    

    View Slide

44. 44
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w ౎ಓ෎ݝ͝ͱͷΠϯετʔϧ਺Λऔಘ
    w ϕʔεϥΠϯ͔Βͷ૿෼Λग़͢
    w අ༻ରޮՌʢ$1*ʣΛग़ͯ͠૬ରൺֱ
    

    View Slide

45. 45
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w ౎ಓ෎ݝผͷΠϯετʔϧ਺Λऔಘ
    w 'JSFCBTFͷ౎ಓ෎ݝ৘ใΛར༻
    

    View Slide

46. 46
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    SELECT
    STRFTIME_UTC_USEC(DATE_ADD(event_dim.timestamp_micros, 9, 'HOUR'),
    '%Y-%m-%d') AS install_date,
    user_dim.geo_info.region AS region,
    EXACT_COUNT_DISTINCT(user_dim.device_info.resettable_device_id) as
    install_count
    FROM
    your_table_name
    WHERE
    user_dim.geo_info.country = 'Japan'
    AND event_dim.name = 'first_open'
    GROUP BY install_date, region
    ORDER BY install_date ASC, install_count DESC
    ౎ಓ෎ݝผͷ%BJMZΠϯετʔϧ਺Λऔಘ
    

    View Slide

47. 47
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    ౎ಓ෎ݝผͷ%BJMZΠϯετʔϧ਺Λऔಘ
    

    View Slide

48. 48
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w ϕʔεϥΠϯ͔Βͷ૿෼Λऔಘ
    w $.Λߦͳ͍ͬͯͳ͍࣌ظͱൺֱ
    IUUQXXXJOUFSOFUSJYDPNBVCMPHNFBTVSJOHUIFJNQBDUPGUWBEWFSUJTFNFOUPOXFCUSBGGJDΑΓҾ༻
    

    View Slide

49. 49
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w අ༻ରޮՌʢ$1*ʣΛग़ͯ͠૬ରൺֱ
    w ౎ಓ෎ݝผʹਪఆ$1*Λग़͢
    w ૬ରൺֱͯ͠ޮՌͷྑ͠ѱ͠Λ൑ఆ
    

    View Slide

50. 50
    අ༻ରޮՌͷྑ͔ͬͨ

    ౎ಓ෎ݝͷ౤ԼྔΛ૿΍͢
    

    View Slide

51. 51
    1%$"͕ճΔ
    $.౤Լ

    ܭը
    $.์ૹ
    ,1*෼ੳ
    අ༻ରޮՌ

    վળԾઆ
    

    View Slide

52. ·ͱΊ
    52
    

    View Slide

53. 53
    $.ͷඅ༻ରޮՌվળ౎ಓ෎ݝผ౤Լྔͷ࠷దԽ
    w ੒ख़ࢢ৔Ͱ͸ϚʔέςΟϯά෼ੳ͕ॏཁ
    w ,1*Λ௥ͬͯվળ͢Δͷ͸։ൃͱಉ͡
    w 'JSFCBTFʴ#JH2VFSZͰৄࡉʹ,1*Λ෼ੳ
    w શϢʔβʔશΠϕϯτͷੜσʔλΛूܭՄೳ
    w ,1*෼ੳΛ༻͍ͯ$.ͷඅ༻ରޮՌΛվળ
    w ౎ಓ෎ݝ୯ҐͳͲ౤ԼՕॴΛ࠷దԽ
    

    View Slide