Firebase & BigQuery 實戰分享

2017.02.24 @ iChef

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