Firebase & BigQuery 實戰分享

2017.02.24 @ iChef

Transcript

1. Intro to Firebase / BigQuery
   BigQuery
   +
   Steven Hsieh
   shared@iChef
   2017.02.24
   

   View Slide

2. Gogolook
   Sr. Product Planner, Growth Team
   HTC
   Sr. Engineer, Data Insight Management
   StorySense
   Web Developer & Project Manager
   Hi, I’m Steven
   2
   

   View Slide

3. • Firebase Introduction
   • Access raw data à BigQuery
   • Export/Pricing/Structure
   • 操作語法 (SQL)
   • How to save money?
   • Application Case
   • Debug View
   • Calculate churn retention (with A/B test)
   • Monitor performance
   • Last action before removing app
   • What’s next?
   3
   Agenda
   

   View Slide

4. Firebase Introduction
   4
   

   View Slide

5. 5
   Firebase Analytics is now Google’s recommended solution for mobile app analytics
   (from Google‘s official blog)
   

   View Slide

6. Firebase Analytics
   • 免費 & 事件記錄總數量無上限 (500 個 unique 事件)
   • 專為 App 設計的自動事件 (Android & iOS)
   • first open, in app purchase
   • app update, app remove, os update, app
   exception
   • notification receive, notification open,
   notification dismiss
   6
   

   View Slide

7. Firebase Analytics
   • 自動記錄的 user properties
   • first open time
   • brand, model, OS version, app version
   • language, country, city
   • acquisition source, acquisition campaign
   (Google UTM), install store
   7
   

   View Slide

8. Case: Uninstall Detection
   Mar 8, 2013 #stackoverflow
   “It’s not possible”
   Workaround:
   自己定義「用戶流失」，例如：7天無任何活動
   如果 user 出國 / 都沒電話?
   8
   

   View Slide

9. Case: Uninstall Detection
   Device Not
   Registered
   è Uninstalled
   Mar 6, 2014 #hackfindings
   “A quick answer is GCM”
   Workaround:
   每天發送數千萬個 silent push
   若要深入到每小時？
   9
   • As-is: 利用 GCM server 的回覆狀態判定
   

   View Slide

10. Case: Uninstall Detection
    • 只要整合 SDK，自動記錄
    • Android only
    • 想算每微秒的用戶流失率也可以唷
    10
    • To-be: Firebase auto collected events
    

    View Slide

11. Firebase
    11
    

    View Slide

12. Firebase Analytics 與它的快樂夥伴們！
    12
    

    View Slide

13. Firebase Analytics
    Analysis
    Action
    與它的快樂夥伴們！
    13
    

    View Slide

14. Firebase Analytics
    Backend
    As
    A Service
    與它的快樂夥伴們！
    14
    

    View Slide

15. Firebase Analytics
    • 切分目標受眾 (Audience Segmentation)
    • By user properties (國家、地區、語言、版本、型號…)
    • By events
    15
    

    View Slide

16. Remote Config
    • App 不需改版即可從後台快速更改內容/ 設定
    • 根據目標受眾賦予不同值
    • 傳遞不同訊息
    • A/B testing
    • 精準的 percentile 抽樣
    16
    

    View Slide

17. Case: Suggest Update
    • 只能同時對所有 user 設定
    （要做彈性 targeting，要自己整合資料、設定規則）
    • 要修改 DB 內的資料值
    • As-is: 在自己的 DB 內設定，透過 server API 回給 app
    Developer
    API
    Show
    17
    

    View Slide

18. Case: Suggest Update
    • To-be: Firebase Remote Config
    • 彈性的 user group 設定
    • Web 後台修改
    18
    Show
    PM/Marketer
    

    View Slide

19. Game over 不想等？
    買金幣
    看廣告
    邀請朋友
    不願意花錢的人
    應用：依據行為模式
    傳遞最適合的訊息
    19
    

    View Slide

20. Firebase Cloud Messaging (FCM)
    • GCM è FCM: Android & iOS
    • 鎖定 目標受眾
    • 追蹤 轉換事件
    • 自定義參數
    • 在地化
    • 語言 / 國家
    • 當地時間
    20
    

    View Slide

21. Dynamic Links
    • Dynamic Link: 根據平台，導至不同頁面
    • iOS - 開 app 特定頁面 or App Store (未安裝)
    • Android - 開 app 特定頁面 or Google Play (未安裝)
    • Desktop - 官網
    • Deep Link: 安裝前後不中斷體驗
    • 個人化初次開啟流程
    • Attribution Analysis
    • 從哪個渠道下載的用戶黏度最高?
    • 從哪個渠道下載的用戶邀請最多朋友安裝?
    • 從哪個渠道下載的用戶消費最高?
    21
    

    View Slide

22. Firebase Analytics 與它的快樂夥伴們！
    22
    

    View Slide

23. Firebase Analytics
    • 免費，無總數上限
    • App 專屬的自動事件記錄
    • 支援原始資料匯出
    • 分析 çè 行動 (Actionable metrics)
    • 推播訊息
    • A/B testing
    • 分眾內容
    • Deep links
    23
    

    View Slide

24. Access Raw Data - BigQuery
    24
    

    View Slide

25. Firebase Analytics
    BigQuery
    匯出原始資料
    只要一個開關
    25
    

    View Slide

26. BigQuery , which is part of the growing serverless computing trend, is
    described as a "fully managed, petabyte scale, low-cost analytics data
    warehouse" with a pay-as-you-go pricing model. The new standard-
    based SQL dialect may open it up to a new universe of developers.
    26
    

    View Slide

27. 27
    Schema
    app_events_YYYYMMDD
    user_dim event_dim:
    device_info: model, brand, os_version…
    geo_info: country, region, city…
    app_info: version, store…
    traffic_source: channel, campaign...
    user_properties:
    key, int_value, float_value, str_value
    key, int_value, float_value, str_value
    .
    .
    .
    date, name, time… params:
    key, int_value, float_value, str_value
    key, int_value, float_value, str_value
    .
    .
    .
    date, name, time… params:
    key, int_value, float_value, str_value
    key, int_value, float_value, str_value
    .
    .
    .
    .
    .
    .
    

    View Slide

28. 28
    user_dim
    event_dim:
    

    View Slide

29. Standard SQL
    • From Jun. 2016，取代 Legacy SQL
    • 和 SQL 2011 (ISO standard) 更一致
    • 可以寫更多更複雜的子查詢
    • 更好的最佳化 (more advanced query optimization)
    • 提供更多資料型態 date, time, array, structs
    • Theta (θ) JOIN: 連結的條件可以不只是 ‘=’
    • 例如：列出在至少兩個不同日期有借閱的人
    SELECT DISTINCT Firsts.borrower_id
    FROM Checkouts Firsts, Checkouts Seconds
    WHERE Firsts.date > Seconds.date AND Firsts.borrower_id = Seconds.borrower_id;
    29
    

    View Slide

30. 30
    Use Standard SQL to query
    

    View Slide

31. 31
    Array in SQL???
    

    View Slide

32. 32
    Handle array – use UNNEST()
    

    View Slide

33. Pricing
    33
    Product Daily
    count
    Size Cost for
    1-day data
    Cost for
    1-year data
    小熊來電 150萬 1.5 GB $0.9 NTD/m $330 NTD/m
    Whoscall 2.5億 150GB $90 NTD/ m $33000 NTD/ m
    

    View Slide

34. Pricing
    34
    Query Product Data processed Query cost
    每日反安裝數 小熊來電 150MB $0.02 NTD
    Whoscall 13GB $1.95 NTD
    一週的 retention 小熊來電 500MB $0.075 NTD
    Whoscall 500GB $75 NTD
    

    View Slide

35. How to save money?
    • 縮小時間範圍計算
    FROM `xxxxproject.app_events_2017*`
    WHERE _TABLE_SUFFIX BETWEEN '0220' AND '0220'
    • 要操作很多次的 è 匯出小 table
    35
    

    View Slide

36. How to save money?
    • 善用 Common Table Expression (CTE) à 合併一個 Query
    36
    Select 3 times
    (about 2TB processed)
    WITH xxx AS (SELECT xxx)
    (about 500G processed)
    

    View Slide

37. Application Case
    37
    

    View Slide

38. Test – Debug view (real-time)
    

    View Slide

39. Test – Debug view (real-time)
    

    View Slide

40. 40
    Case 1: Calculate A/B test churn retention
    找出參與實驗的
    device id 以及組別
    找出這些 device id
    的反安裝時間
    (若有反安裝)
    device_id,
    exp_group,
    remove_time
    GROUP BY
    exp_group,
    remove_time
    JOIN
    

    View Slide

41. 41
    Case 2: Monitor performance
    • 算平均 à 算分布 (percentile)
    PERCENT_RANK() OVER (PARTITION BY app_ver ORDER BY response_time DESC)
    • 暫存到新 table
    • Query 快 / 省錢
    • 避免 BQ 運算資源不足
    • 隨時查某個百分位的值
    

    View Slide

42. 42
    Case 3: Last action before removing app
    • LAG()
    LAG(event_dim.name, 1) OVER (PARTITION BY user_dim.device_info.resettable_device_id
    ORDER BY event_dim.timestamp_micros ASC)
    

    View Slide

43. 43
    What’s next? (Google I/O 2017)
    • A/B test suite
    • 現有架構，需自己記錄 (可能有欄位不足問題)
    • 新版 console
    • Compare with Facebook App Analytics
    

    View Slide

44. stevenhsieh@gogolook.com
    Thank you J
    

    View Slide