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

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

2bfb16e53b154afc9f141f831b4aaaf2?s=47 naru-jpn
August 21, 2018

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

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

2bfb16e53b154afc9f141f831b4aaaf2?s=128

naru-jpn

August 21, 2018
Tweet

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

  13. None
  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. ͋Γ͕ͱ͏͍͟͝·ͨ͠