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

Firebase & BigQuery 實戰分享

Steven Hsieh
February 24, 2017

Firebase & BigQuery 實戰分享

2017.02.24 @ iChef

Steven Hsieh

February 24, 2017
Tweet

More Decks by Steven Hsieh

Other Decks in Technology

Transcript

  1. Intro to Firebase / BigQuery
    BigQuery
    +
    Steven Hsieh
    [email protected]
    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. [email protected]
    Thank you J

    View Slide