Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

shoby
September 17, 2017

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

iOSDC 2017での発表資料です。

shoby

September 17, 2017
Tweet

More Decks by shoby

Other Decks in Technology

Transcript

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  11. ੒ख़ࢢ৔Ͱ

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  14. 16
    ͲͪΒ΋

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  17. 19
    ͲͪΒ΋ࣅ͍ͯΔ

    View full-size slide

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

    ਌࿨ੑ
    20

    View full-size slide

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

    View full-size slide

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

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    ৄࡉͳ෼ੳ͕ඞཁ

    View full-size slide

  28. 'JSFCBTFͱ#JH2VFSZʹΑΔ

    σʔλूܭ
    30

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    ,1*෼ੳΛߦ͏

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    ਺஋ͷΈͰߦ͑Δ

    View full-size slide

  38. ౤ԼՕॴͷ࠷దԽ
    40

    View full-size slide

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

    View full-size slide

  40. 42
    ࠓճ͸

    ౎ಓ෎ݝผ౤Լྔͷ

    ࠷దԽͷ࿩

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  44. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

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

    View full-size slide

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

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

    վળԾઆ

    View full-size slide

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

    View full-size slide