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

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

Transcript

1. GASͰ࡞ΔϑΝωϧ෼ੳπʔϧ
   2018.8.22 We Are JavaScripters! @23rd
   Naruki Chigira @ Timers-inc.
   Twitter: _naru_jpn GitHub: naru-jpn
   

   View Slide

2. ࣾ಺ͷϑΝωϧ෼ੳπʔϧΛGASͰ࡞ͬͨ࿩
   

   View Slide

3. ΞϓϦ಺Ͱൃੜͨ͠Πϕϯτͷ෼ੳ(Ҏલ)
   ɾొ࿥Ͱ͖ΔϢχʔΫͳΠϕϯτͷ্ݶ͸300
   ɾΠϕϯτ͕ແ੍ݶʹੜ੒͞ΕΔ෼ੳͷ࢓ํ ( ྫ: push_100 )
   ɾ্ݶΛ௒͑ͳ͍Α͏ʹΠϕϯτΛ࡟আ͠ଓ͚Δ೔ʑ
   ɾ෼ੳͷํ๏͸ਓʹΑͬͯόϥόϥ
   

   View Slide

4. Firebase Analytics ʹҠߦ͢Δػӡ
   

   View Slide

5. ΞϓϦ಺Ͱൃੜͨ͠Πϕϯτͷ෼ੳ(৽)
   ɾొ࿥Ͱ͖ΔϢχʔΫͳΠϕϯτͷ্ݶ͸500
   ɾύϥϝʔλΛ͏·͘ར༻ͯ͠Πϕϯτ͕૿͑͗͢ͳ͍Α͏ʹ(໨ඪ)
   ɾਓʹΑͬͯ෼ੳͷ࢓ํ͕มΘΒͳ͍Α͏ʹ͢Δ(໨ඪ)
   

   View Slide

6. ɾશΠϕϯτΛεϓϨουγʔτͰ؅ཧ͢ΔΑ͏ʹ
   ɾΠϕϯτͱύϥϝʔλͷܗࣜΛ౷Ұ
   

   View Slide

7. ɾΠϕϯτ͸શͯBigQueryʹࣗಈͰૹ৴
   ɾ͋ͱ͸ΫΤϦͰΑ͠ͳʹ෼ੳ͢Δ͚ͩʂ
   

   View Slide

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
   ϑΝωϧ෼ੳͷҝͷΫΤϦαϯϓϧ
   

   View Slide

9. ɹ(( ॻ͖ͨ͘ͳ͍ʂ ))
   

   View Slide

10. ߟ͑ΒΕΔฐ֐
    ɾ෼ੳํ๏ͷଐਓԽɺಠࣗͷ෼ੳͷఆٛͷൃੜ
    ɾΫΤϦΛॻ͘ҝͷແବͳ࡞ۀ࣌ؒ
    

    View Slide

11. ΫΤϦΛࣗಈੜ੒͠Α͏ʂ
    

    View Slide

12. Demo
    

    View Slide

13. View Slide

14. ΍ͬͨ͜ͱ
    

    View Slide

15. ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ
    ֤Πϕϯτͷநग़
    ֤Πϕϯτͷൃੜ࣌ؒͷൺֱͱநग़
    ֤ΠϕϯτͷUUͰͷूܭ
    ݁Ռͷ੔ܗ
    ΫΤϦதͰඞཁͳॲཧΛ੔ཧͯ͠ฒ΂Δ
    

    View Slide

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;
    }
    ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ
    ֤ॲཧΛؔ਺ʹ੾Γग़ͯ͠ɺΫΤϦͷ෦෼จࣈྻΛੜ੒
    

    View Slide

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("");
    ΫΤϦΛ෼ׂͯ͠੔ཧ͢Δ
    ΫΤϦͷ෦෼จࣈྻΛશͯ࿈݁͠ɺΫΤϦจࣈྻશମΛऔಘ
    

    View Slide

18. BigQueryͱͷ࿈ܞ
    GAS্ͰGoogleͷଞͷαʔϏεͱ࿈ܞ͢Δҝͷઃఆ͕؆୯ʹͰ͖Δ
    

    View Slide

19. BigQueryͱͷ࿈ܞ
    ࢖͏ͷ΋Χϯλϯʂ
    var resource = {
    query: query,
    timeoutMs: 10000000,
    useLegacySql: false
    };
    // ࣮ߦͱ݁Ռͷऔಘ
    query_results = BigQuery.Jobs.query(resource, projectID);
    

    View Slide

20. άϥϑԽ
    άϥϑ͕͋ͬͨํ͕ීٴ͠΍ͦ͢͏ͳͷͰ௥Ճ
    https://www.chartjs.org
    

    View Slide

21. ࠓޙͷల๬
    ɾJavaScriptͷҰൠతͳ։ൃख๏΋ͬͱ஌Γ͍ͨ
    ɾGAS΋ͬͱ៉ྷʹ࢖͍͍ͨ
    ɾଞͷ՝୊΋͍Ζ͍ΖղܾͰ͖ͨΒ͍͍ͳ
    

    View Slide

22. ͋Γ͕ͱ͏͍͟͝·ͨ͠
    

    View Slide