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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  4. Firebase Analytics ʹҠߦ͢Δػӡ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  13. /**
    * ΫΤϦʮ֤Πϕϯτͷ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 full-size slide

  14. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide