GASで作るファネル分析ツール

社内のファネル分析の為のウェブアプリケーションをGASで作った話

Transcript

1. GASͰ࡞ΔϑΝωϧ෼ੳπʔϧ 2018.8.22 We Are JavaScripters! @23rd Naruki Chigira @ Timers-inc. Twitter: _naru_jpn GitHub: naru-jpn
2. ࣾ಺ͷϑΝωϧ෼ੳπʔϧΛGASͰ࡞ͬͨ࿩
3. ΞϓϦ಺Ͱൃੜͨ͠Πϕϯτͷ෼ੳ(Ҏલ) ɾొ࿥Ͱ͖ΔϢχʔΫͳΠϕϯτͷ্ݶ͸300 ɾΠϕϯτ͕ແ੍ݶʹੜ੒͞ΕΔ෼ੳͷ࢓ํ ( ྫ: push_100 ) ɾ্ݶΛ௒͑ͳ͍Α͏ʹΠϕϯτΛ࡟আ͠ଓ͚Δ೔ʑ ɾ෼ੳͷํ๏͸ਓʹΑͬͯόϥόϥ
4. Firebase Analytics ʹҠߦ͢Δػӡ
5. ΞϓϦ಺Ͱൃੜͨ͠Πϕϯτͷ෼ੳ(৽) ɾొ࿥Ͱ͖ΔϢχʔΫͳΠϕϯτͷ্ݶ͸500 ɾύϥϝʔλΛ͏·͘ར༻ͯ͠Πϕϯτ͕૿͑͗͢ͳ͍Α͏ʹ(໨ඪ) ɾਓʹΑͬͯ෼ੳͷ࢓ํ͕มΘΒͳ͍Α͏ʹ͢Δ(໨ඪ)
6. ɾશΠϕϯτΛεϓϨουγʔτͰ؅ཧ͢ΔΑ͏ʹ ɾΠϕϯτͱύϥϝʔλͷܗࣜΛ౷Ұ
7. ɾΠϕϯτ͸શͯBigQueryʹࣗಈͰૹ৴ ɾ͋ͱ͸ΫΤϦͰΑ͠ͳʹ෼ੳ͢Δ͚ͩʂ
8. WITH # Firebase AnalyticsͰಠࣗʹఆٛΛ͍ͯ͠ΔΠϕϯτ(t_%)ͷΈͷநग़ͱ੔ܗ EVENT_MASTER AS ( SELECT user_id, event_timestamp as timestamp, event_name as type, max(case params.key WHEN 't_event_name' THEN params.value.string_value ELSE NULL END) AS event_name, max(case params.key WHEN 't_event_id' THEN params.value.string_value ELSE NULL END) AS event_id, max(case params.key WHEN 't_object_name' THEN params.value.string_value ELSE NULL END) AS object_name, max(case params.key WHEN 't_controller' THEN params.value.string_value ELSE NULL END) AS controller FROM `***.analytics_*****.events_*`, UNNEST(event_params) AS params WHERE user_id IS NOT NULL AND event_name LIKE 't_%' AND params.key LIKE 't_%' AND _TABLE_SUFFIX BETWEEN '20180101' AND ‘20180101' AND platform = 'IOS' GROUP BY user_id, event_timestamp, type ), # ֤Πϕϯτͷநग़ Event1 AS ( SELECT * FROM Event_Master WHERE type = 't_screen' AND controller = ‘***ViewController’ ), Event2 AS ( SELECT * FROM Event_Master WHERE type = 't_screen' AND controller = ‘***ViewController’ ), # ֤ΠϕϯτؒͰͷൃੜ࣌ؒͷൺֱɺ৚݅ʹద߹͢ΔΠϕϯτͷநग़ Event1_Extracted AS ( SELECT * FROM Event1 ), Event2_Extracted AS ( SELECT Event2.user_id, Event2.timestamp AS timestamp, Event2.event_name AS event_name, Event2.event_id AS event_id, Event2.object_name AS object_name, Event2.controller AS controller FROM Event1_Extracted INNER JOIN Event2 ON Event1_Extracted.user_id = Event2.user_id AND Event1_Extracted.timestamp < Event2.timestamp ), # ֤Πϕϯτͷूܭ Event1_Count AS ( SELECT 1 as index, COUNT(*) as count ϑΝωϧ෼ੳͷҝͷΫΤϦαϯϓϧ
9. ɹ(( ॻ͖ͨ͘ͳ͍ʂ ))
10. ߟ͑ΒΕΔฐ֐ ɾ෼ੳํ๏ͷଐਓԽɺಠࣗͷ෼ੳͷఆٛͷൃੜ ɾΫΤϦΛॻ͘ҝͷແବͳ࡞ۀ࣌ؒ
11. ΫΤϦΛࣗಈੜ੒͠Α͏ʂ
12. Demo
14. ΍ͬͨ͜ͱ
15. ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ ֤Πϕϯτͷநग़ ֤Πϕϯτͷൃੜ࣌ؒͷൺֱͱநग़ ֤ΠϕϯτͷUUͰͷूܭ ݁Ռͷ੔ܗ ΫΤϦதͰඞཁͳॲཧΛ੔ཧͯ͠ฒ΂Δ
16. /** * ΫΤϦʮ֤ΠϕϯτͷUUͰͷूܭʯͷ࡞੒ * @param {number} count Πϕϯτͷ਺ * @returns {string} ΫΤϦʮ֤ΠϕϯτͷUUͰͷूܭʯ */ function query_count_each_events(count) { var result = " # ֤Πϕϯτͷूܭ\n"; for (var i = 0; i < count; i++) { let name = "Event" + (i+1) + "_Count"; let source = "Event" + (i+1) + "_Extracted"; let query = [ " ", name, " AS (\n", " SELECT\n", " ", i+1, " as index,\n", " COUNT(*) as count\n", " FROM\n", " (SELECT user_id FROM ", source, " GROUP BY user_id)\n", " ),\n", ].join(""); result += query; } return result; } ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ ֤ॲཧΛؔ਺ʹ੾Γग़ͯ͠ɺΫΤϦͷ෦෼จࣈྻΛੜ੒
17. let query = [ // Firebase AnalyticsͰಠࣗʹఆٛΛ͍ͯ͠ΔΠϕϯτͷநग़ͱ੔ܗ query_all_events(is_ios, target_start_day, target_end_day), // ֤Πϕϯτͷநग़ query_pre_extract_events(is_ios, count, event_informations), // ֤ΠϕϯτؒͰͷൃੜ࣌ؒͷൺֱɺ৚݅ʹద߹͢ΔΠϕϯτͷநग़ query_extract_events(is_ios, count, funnel_max_seconds), // ֤Πϕϯτͷूܭ query_count_each_events(count), // Πϕϯτͷूܭͷ݁߹ query_event_counts(count), // ݁Ռ query_result() ].join(""); ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ ΫΤϦͷ෦෼จࣈྻΛશͯ࿈݁͠ɺΫΤϦจࣈྻશମΛऔಘ
18. BigQueryͱͷ࿈ܞ GAS্ͰGoogleͷଞͷαʔϏεͱ࿈ܞ͢Δҝͷઃఆ͕؆୯ʹͰ͖Δ
19. BigQueryͱͷ࿈ܞ ࢖͏ͷ΋Χϯλϯʂ var resource = { query: query, timeoutMs: 10000000, useLegacySql: false }; // ࣮ߦͱ݁Ռͷऔಘ query_results = BigQuery.Jobs.query(resource, projectID);
20. άϥϑԽ άϥϑ͕͋ͬͨํ͕ීٴ͠΍ͦ͢͏ͳͷͰ௥Ճ https://www.chartjs.org
21. ࠓޙͷల๬ ɾJavaScriptͷҰൠతͳ։ൃख๏΋ͬͱ஌Γ͍ͨ ɾGAS΋ͬͱ៉ྷʹ࢖͍͍ͨ ɾଞͷ՝୊΋͍Ζ͍ΖղܾͰ͖ͨΒ͍͍ͳ
22. ͋Γ͕ͱ͏͍͟͝·ͨ͠