1年分のデータが見たいと言われてやったこと/yearly_data_with_note

note tech meetup #1 エンジニアLT大会で発表したきた内容です。
Redashだとデータ容量の制限があり1年見たいな大規模なデータは扱えないのですが別の手段を試してみました。
https://pieceofcake.connpass.com/event/240575/

Transcript

1. !GSPNLL
   ೥෼ͷσʔλ͕ݟ͍ͨͱݴΘΕͯ
   ΍ͬͨ͜ͱ
   OPUFUFDINFFUVQ
   

   View Slide

2. View Slide

3. View Slide

4. View Slide

5. ΞϓϦνʔϜͷ՝୊
   w ઐ೚ͷ1E.͕͓ΒͣλεΫ͸ࣗ෼ͨͪͰߟ͑ͳ͕Β࡞͍ͬͯΔ
   w ͜Ε·Ͱ͸΢Σϒʹ͋ΔػೳΛΠϯϙʔτ͢Δ͜ͱ͕ଟ͔ͬͨͷͰେ͖ͳ
   ໰୊ʹ͸ͳͬͯ͜ͳ͔͕ͬͨɺ͋Δఔ౓͕ࠩຒ·Γ࢝ΊΔͱग़͖ͯͨ՝୊
   w ݉೚ͷ1E.΍1.ܦݧͷ͋ΔσβΠφʔ͕ೖͬͯ͘ΕͨͷͰલਐ͸͍ͯ͠Δ
   w ୹ظతʹ͸਺஋ΛάϥϑԽ͢Δ͜ͱͰՄࢹԽͰ͖͍͕ͯͨɺ௕ظతͳ੒௕ͷ
   ՄࢹԽ͕Ͱ͖͍ͯͳ͔ͬͨ
   w ࡉ͔͍ΧΠθϯΛଓ͚͍ͯΔͱɺࣗ෼ͨͪͷ΍͍ͬͯΔ͜ͱ͕ਖ਼͍͔͠Ͳ
   ͏͔͢Β෼͔Βͳ͍
   

   View Slide

6. 1E.
   ೥෼ͷσʔλΛऔ͖ͬͯͯൺֱ͍ͨ͠
   Πϝʔδਤ
   

   View Slide

7. ͕͠ͳ͍J04ΤϯδχΞ
   ͸ɺ͸͍ʜ
   

   View Slide

8. OPUFΞϓϦνʔϜͷσʔλͷ׆༻
   w 'JSFCBTF"OBMZUJDT#JH2VFSZ
   w ΢Σϒͱͷڞ௨ϩάج൫
   w ্هͷσʔλΛՄࢹԽ͢Δͷʹ3FEBTIΛར༻
   

   View Slide

9. 3FEBTI
   w ෳ਺ͷσʔλϕʔεΛแׅͯ͠Ӿཡ͢Δ͜ͱ͕Ͱ͖ΔμογϡϘʔυαʔϏε
   w "84΍(PPHMF#JH2VFSZɺ.Z42-΍1PTUHSF42-ͳͲ༷ʑͳσʔλϕʔε
   ʹରԠʢIUUQTSFEBTIJPJOUFHSBUJPOTʣ
   w ෳ਺ͷΫΤϦΛ·ͱΊͯӾཡͨ͠Γɺάϥϑͷඳը΋Մೳ
   w ϗεςΟϯά͕ՄೳʢΉ͠ΖSFEBTIJPͰͷར༻͸೥݄຤ʹதࢭʣ
   

   View Slide

10. ࠓճ3FEBTI͸ෆ࢖༻
    w ઃఆ࣍ୈ͕ͩϝϞϦ࢖༻ྔΛ௒͑Δ͜ͱ͕͋ΔʢίετͱͷτϨʔυΦϑʣ
    w
    

    w 3FEBTI͸ετοΫʹ޲͔ͳ͍ʢΠϝʔδʣ
    

    ೔෇ͷݕࡧΛݻఆ͢Δ͔ݱࡏ࣌ࠁΛج఺ʹܭࢉ͢Δ͜ͱ͕ଟ͍
    

    View Slide

11. #JH2VFSZͷஷ஝σʔλΛ
    

    ετοΫͯ͠ൺֱͯ͠Ӿཡ͍ͨ͠🤔
    

    View Slide

12. View Slide

13. εϓγ͔Β#JH2VFSZݺ΂͹ྑ͘ͳ͍ʁ
    

    View Slide

14. ࣄલ४උ
    'JSFCBTF"OBMZUJDTͱ#JH2VFSZΛϦϯΫ͓ͯ͘͠
    

    View Slide

15. View Slide

16. #JH2VFSZ"1*Λ༗ޮԽ
    (PPHMF$MPVE1MBUGPSNΑΓ
    

    View Slide

17. ΞϓϦ͔ΒϩάΛૹ৴
    Analytics.logEvent(event.name, parameters: event.params)
    

    View Slide

18. ϓϩδΣΫτ൪߸Λ߇͓͑ͯ͘
    (PPHMF$MPVE1MBUGPSNͷ*".ͷઃఆը໘ΑΓ
    ͜Ε
    

    View Slide

19. εϓϨουγʔτͷϕʔεΛ࡞੒
    

    View Slide

20. "QQT4DSJQUΛىಈ
    

    View Slide

21. αʔϏεΛ௥ՃΑΓ#JH2VFSZ"1*Λ௥Ճ
    

    View Slide

22. ΫΤϦͷ࣮ߦ
    const request = {
    
    
    query: query,
    
    
    useLegacySql: false
    
    
    }
    
    
    const result = BigQuery.Jobs.query(request, projectId)
    
    
    ઌ΄Ͳ߇͓͍͑ͯͨϓϩδΣΫτ൪߸
    

    View Slide

23. ࣮ߦ͢Δࡍʹݖݶͷ෇༩ΛٻΊΒΕΔ
    ಛʹ໰୊ͳ͍ͷͰ෇༩͓ͯ͘͠
    

    View Slide

24. ݁Ռͷऔಘ
    IUUQTDMPVEHPPHMFDPNCJHRVFSZEPDTSFGFSFODFSFTUWKPCTRVFSZ
    SPXT஋ͷҰཡ͕഑ྻͰೖ͍ͬͯΔ
    GϑΟʔϧυͷҰཡ͕4&-&$5۟ͷॱ൪ʹ഑ྻͰೖ͍ͬͯΔ
    W࣮ࡍͷ஋
    ࠷௿ݶ͚ͩ঺հʢৄ͘͠͸υΩϡϝϯτΛࢀরʣ
    

    View Slide

25. ݄ؒͷ17Λूܭ͢Δαϯϓϧίʔυ
    function countMonthlyUser(year, month) {
    
    
    const from = `${zeroFill(year, 4)}${zeroFill(month, 2)}01`
    
    
    const toDate = new Date(year, month, 0)
    
    
    const to = `${zeroFill(year, 4)}${zeroFill(month, 2)}${zeroFill(toDate.getDate(), 2)}`
    
    
    const query = `SELECT
    
    
    FORMAT_TIMESTAMP("%Y-%m", TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS date
    
    
    , COUNT(distinct if(user_id IS NOT NULL, user_id, user_pseudo_id)) AS count_user
    
    
    FROM \`${tableName}.events_*\`
    
    
    WHERE _TABLE_SUFFIX BETWEEN '${from}' AND '${to}'
    
    
    GROUP BY
    
    
    date
    
    
    `
    
    
    const request = {
    
    
    query: query,
    
    
    useLegacySql: false
    
    
    }
    
    
    const result = BigQuery.Jobs.query(request, projectId)
    
    
    if (!result || !result.rows) {
    
    
    return 0
    
    
    } else if (result.rows.length == 0) {
    
    
    return 0
    
    
    } else {
    
    
    if (result.rows[0].f.length == 0) {
    
    
    return 0
    
    
    } else {
    
    
    return result.rows[0].f[1].v
    
    
    }
    
    
    }
    
    
    }
    
    
    IUUQTHJUIVCDPNGSPNLL4BNQMF#JH2VFSZ8JUI4QSFBETIFFU
    

    View Slide

26. ಈ͔͍ͯ͠Δ༷ࢠ
    

    View Slide

27. ϋϚͬͨ఺
    w Կ΋ߟ͑ͣʹ42-Λୟ͘ͱ4UBOEBSE42-Ͱ͸ͳ͘ϨΨγʔ42-͕࣮ߦ͞Ε
    ͯ͠·ͬͨ
    w VTF-FHBDZ4RMGBMTFΛͪΌΜͱࢦఆ͢Δඞཁ͕͋ͬͨ
    w QSPKFDUJE͕Ͳ͜ʹ͋Δͷ͔෼͔ΓͮΒ͔ͬͨ
    w *".ͷઃఆը໘Λ୳͢ͷ͕஍ຯʹେมͩͬͨ
    

    View Slide

28. 5JQT
    w ͔ͤͬ͘࡞ͬͯ΋࢖ΘΕͳ͍ͱ໪ମແ͍
    w ࢖͍੾ΓͰ͸ͳ͘ܧଓͯ͠ར༻͍ͯͨ͘͠ΊʹɺஷΊͨσʔλͷޙΖʹ଍ͯ͠
    ͍͚ΔΑ͏ͳ࢓૊ΈΛ࡞͓ͬͯ͘ͱࠓޙ΋҆৺
    

    View Slide

29. ࠓޙ
    w ࠓ͸खಈͰ࣮ߦ͠ͳ͍ͱूܭ͞Εͳ͍
    w ͍͔ͭࣗಈͰݺͼग़͞Εͯɺूܭͯ͠4MBDLʹ௨஌͞ΕΔΑ͏ʹ͍ͨ͠
    

    View Slide

30. ·ͱΊ
    w 3FEBTIͩͱઃఆʹΑͬͯ͸੍ݶ͕͋ΓେྔͷσʔλΛूܭ͠Αͱ͢Δͱ͏·
    ͘ߦ͔ͳ͍͜ͱ͕͋Δ
    w 4QSFBETIFFU͔Β#JH2VFSZ"1*Λୟ͘͜ͱͰখ·Ίʹूܭͯ͠ετοΫͰ͖
    ΔΑ͏ʹͯ͠Έͨ
    w Ұͭ࡞Ε͹ଞͷFWFOU@OBNFͰूܭ͢Δ͜ͱ΋༰қͳͷͰస༻΋Մೳ
    w શͯΛ4QSFBETIFFUͰ΍Δ΂͖ͱ͸ࢥΘͳ͍͕ɺ༻్ʹΑͬͯ͸બ୒ࢶͷҰ
    ͭʹ͋ͬͯ΋Αͦ͞͏
    

    View Slide

31. View Slide