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

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

naru-jpn
August 21, 2018

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

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

naru-jpn

August 21, 2018
Tweet

More Decks by naru-jpn

Other Decks in Technology

Transcript

  1. 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 ϑΝωϧ෼ੳͷҝͷΫΤϦαϯϓϧ
  2. /** * ΫΤϦʮ֤Πϕϯτͷ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; } ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ ֤ॲཧΛؔ਺ʹ੾Γग़ͯ͠ɺΫΤϦͷ෦෼จࣈྻΛੜ੒
  3. 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(""); ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ ΫΤϦͷ෦෼จࣈྻΛશͯ࿈݁͠ɺΫΤϦจࣈྻશମΛऔಘ
  4. BigQueryͱͷ࿈ܞ ࢖͏ͷ΋Χϯλϯʂ var resource = { query: query, timeoutMs: 10000000,

    useLegacySql: false }; // ࣮ߦͱ݁Ռͷऔಘ query_results = BigQuery.Jobs.query(resource, projectID);