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

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

ࣗݾ঺հ 2

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

5 ೥Ͱ %-਺Λສ͔Βສ·Ͱ  ૿΍ͨ͠࿩Λ͠·͢ʂ ೥݄ ೥݄

Slide 6

Slide 6 text

֓ཁ 6

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

ϑϦϧΛऔΓר͘ঢ়گ 10

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

੒ख़ࢢ৔Ͱ  αʔϏεΛ੒௕ͤ͞Δʹ͸ 13

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

16 ͲͪΒ΋  ,1*෼ੳʹΑΔվળ͕ॏཁʂ

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

19 ͲͪΒ΋ࣅ͍ͯΔ

Slide 20

Slide 20 text

ΤϯδχΞͱϚʔέςΟϯάͷ  ਌࿨ੑ 20

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

22 ΤϯδχΞ͸ϚʔέςΟϯάͰ΋  αʔϏεͷ੒௕ʹߩݙͰ͖Δʂ

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

,1*෼ੳʹॏཁͳ͜ͱ 24

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

29 $.͸Πϯετʔϧʹؔͯ͠  ৄࡉͳ෼ੳ͕ඞཁ

Slide 30

Slide 30 text

'JSFCBTFͱ#JH2VFSZʹΑΔ  σʔλूܭ 30

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

35 ͜ΕΒͷσʔλΛ࢖ͬͯ  ,1*෼ੳΛߦ͏

Slide 36

Slide 36 text

$.ͷඅ༻ରޮՌվળ 36

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

39 ౤ԼՕॴͷ࠷దԽ͸  ਺஋ͷΈͰߦ͑Δ

Slide 40

Slide 40 text

౤ԼՕॴͷ࠷దԽ 40

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

42 ࠓճ͸  ౎ಓ෎ݝผ౤Լྔͷ  ࠷దԽͷ࿩

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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Πϯετʔϧ਺Λऔಘ

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

50 අ༻ରޮՌͷྑ͔ͬͨ  ౎ಓ෎ݝͷ౤ԼྔΛ૿΍͢

Slide 51

Slide 51 text

51 1%$"͕ճΔ $.౤Լ  ܭը $.์ૹ ,1*෼ੳ අ༻ରޮՌ  վળԾઆ

Slide 52

Slide 52 text

·ͱΊ 52

Slide 53

Slide 53 text

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