Firebase & BigQuery 實戰分享

Transcript

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

   2017.02.24

2. Gogolook Sr. Product Planner, Growth Team HTC Sr. Engineer, Data

   Insight Management StorySense Web Developer & Project Manager Hi, I’m Steven 2

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

4. Firebase Introduction 4

5. 5 Firebase Analytics is now Google’s recommended solution for mobile

   app analytics (from Google‘s official blog)

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

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

8. Case: Uninstall Detection Mar 8, 2013 #stackoverflow “It’s not possible”

   Workaround: 自己定義「用戶流失」，例如：7天無任何活動 如果 user 出國 / 都沒電話? 8

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 的回覆狀態判定

10. Case: Uninstall Detection • 只要整合 SDK，自動記錄 • Android only •

    想算每微秒的用戶流失率也可以唷 10 • To-be: Firebase auto collected events

11. Firebase 11

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

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

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

15. Firebase Analytics • 切分目標受眾 (Audience Segmentation) • By user properties

    (國家、地區、語言、版本、型號…) • By events 15

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

    • A/B testing • 精準的 percentile 抽樣 16

17. Case: Suggest Update • 只能同時對所有 user 設定 （要做彈性 targeting，要自己整合資料、設定規則） •

    要修改 DB 內的資料值 • As-is: 在自己的 DB 內設定，透過 server API 回給 app Developer API Show 17

18. Case: Suggest Update • To-be: Firebase Remote Config • 彈性的

    user group 設定 • Web 後台修改 18 Show PM/Marketer

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

20. Firebase Cloud Messaging (FCM) • GCM è FCM: Android &

    iOS • 鎖定 目標受眾 • 追蹤 轉換事件 • 自定義參數 • 在地化 • 語言 / 國家 • 當地時間 20

21. Dynamic Links • Dynamic Link: 根據平台，導至不同頁面 • iOS - 開

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

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

23. Firebase Analytics • 免費，無總數上限 • App 專屬的自動事件記錄 • 支援原始資料匯出 •

    分析 çè 行動 (Actionable metrics) • 推播訊息 • A/B testing • 分眾內容 • Deep links 23

24. Access Raw Data - BigQuery 24

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

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

27. 27 Schema app_events_YYYYMMDD user_dim event_dim: <array> device_info: model, brand, os_version…

    geo_info: country, region, city… app_info: version, store… traffic_source: channel, campaign... user_properties: <Array> key, int_value, float_value, str_value key, int_value, float_value, str_value . . . date, name, time… params: <Array> key, int_value, float_value, str_value key, int_value, float_value, str_value . . . date, name, time… params: <Array> key, int_value, float_value, str_value key, int_value, float_value, str_value . . . . . .

28. 28 user_dim event_dim: <array>

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

30. 30 Use Standard SQL to query

31. 31 Array in SQL???

32. 32 Handle array – use UNNEST()

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

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

35. How to save money? • 縮小時間範圍計算 FROM `xxxxproject.app_events_2017*` WHERE _TABLE_SUFFIX

    BETWEEN '0220' AND '0220' • 要操作很多次的 è 匯出小 table 35

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)

37. Application Case 37

38. Test – Debug view (real-time)

39. Test – Debug view (real-time)

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

41. 41 Case 2: Monitor performance • 算平均 à 算分布 (percentile)

    PERCENT_RANK() OVER (PARTITION BY app_ver ORDER BY response_time DESC) • 暫存到新 table • Query 快 / 省錢 • 避免 BQ 運算資源不足 • 隨時查某個百分位的值

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)

43. 43 What’s next? (Google I/O 2017) • A/B test suite

    • 現有架構，需自己記錄 (可能有欄位不足問題) • 新版 console • Compare with Facebook App Analytics

44. stevenhsieh@gogolook.com Thank you J