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 ಓݝ୯ҐͳͲԼՕॴΛ࠷దԽ