Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Firebase & BigQuery 實戰分享
Steven Hsieh
February 24, 2017
Technology
0
390
Firebase & BigQuery 實戰分享
2017.02.24 @ iChef
Steven Hsieh
February 24, 2017
Tweet
Share
More Decks by Steven Hsieh
See All by Steven Hsieh
Intro to Firebase Analytics
peikangh
0
4.4k
Data-driven User Experience Design
peikangh
1
630
Other Decks in Technology
See All in Technology
JAWS-UG re:Habilitaion 報告 / JAWS-UG OITA rehabilitation
hiranofumio
0
120
データエンジニアリングの潮流を俯瞰する
tetsuroito
1
720
サイボウズの アジャイル・クオリティ / Agile Quality at Cybozu
cybozuinsideout
PRO
4
2.2k
雑な攻撃からELBを守る一工夫 +おまけ / Know-how to protect servers from miscellaneous attacks
hiroga
0
480
2022年度新卒技術研修「良いコードの書き方」講義
excitejp
PRO
0
290
Power AutomateでのAdaptive Cards-基本編
miyakemito
1
270
セキュリティ 開運研修2022 / security 2022
cybozuinsideout
PRO
3
3.6k
多様な成熟度のデータ活用を総合支援するKADOKAWA Connectedのデータ組織について
kadokawaconnected
PRO
0
200
ノーメンテナンス運用実現のためのノウハウ/ColoplTech-05-02
colopl
0
160
チームとチームのチーム
eiel
1
950
ソフトウェアライセンス 2022 / Software License 2022
cybozuinsideout
PRO
1
1k
現状のFedCMの動作解説と OIDCとの親和性について- OpenID TechNight vol.19
ritou
2
450
Featured
See All Featured
Become a Pro
speakerdeck
PRO
3
840
Music & Morning Musume
bryan
35
4.2k
Designing the Hi-DPI Web
ddemaree
272
32k
Large-scale JavaScript Application Architecture
addyosmani
499
110k
Adopting Sorbet at Scale
ufuk
63
7.6k
Clear Off the Table
cherdarchuk
79
280k
How New CSS Is Changing Everything About Graphic Design on the Web
jensimmons
213
11k
Agile that works and the tools we love
rasmusluckow
319
19k
What’s in a name? Adding method to the madness
productmarketing
11
1.6k
Writing Fast Ruby
sferik
612
57k
Streamline your AJAX requests with AmplifyJS and jQuery
dougneiner
126
8.5k
Optimizing for Happiness
mojombo
365
63k
Transcript
Intro to Firebase / BigQuery BigQuery + Steven Hsieh shared@iChef
2017.02.24
Gogolook Sr. Product Planner, Growth Team HTC Sr. Engineer, Data
Insight Management StorySense Web Developer & Project Manager Hi, I’m Steven 2
• 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
Firebase Introduction 4
5 Firebase Analytics is now Google’s recommended solution for mobile
app analytics (from Google‘s official blog)
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
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
Case: Uninstall Detection Mar 8, 2013 #stackoverflow “It’s not possible”
Workaround: 自己定義「用戶流失」,例如:7天無任何活動 如果 user 出國 / 都沒電話? 8
Case: Uninstall Detection Device Not Registered è Uninstalled Mar 6,
2014 #hackfindings “A quick answer is GCM” Workaround: 每天發送數千萬個 silent push 若要深入到每小時? 9 • As-is: 利用 GCM server 的回覆狀態判定
Case: Uninstall Detection • 只要整合 SDK,自動記錄 • Android only •
想算每微秒的用戶流失率也可以唷 10 • To-be: Firebase auto collected events
Firebase 11
Firebase Analytics 與它的快樂夥伴們! 12
Firebase Analytics Analysis Action 與它的快樂夥伴們! 13
Firebase Analytics Backend As A Service 與它的快樂夥伴們! 14
Firebase Analytics • 切分目標受眾 (Audience Segmentation) • By user properties
(國家、地區、語言、版本、型號…) • By events 15
Remote Config • App 不需改版即可從後台快速更改內容/ 設定 • 根據目標受眾賦予不同值 • 傳遞不同訊息
• A/B testing • 精準的 percentile 抽樣 16
Case: Suggest Update • 只能同時對所有 user 設定 (要做彈性 targeting,要自己整合資料、設定規則) •
要修改 DB 內的資料值 • As-is: 在自己的 DB 內設定,透過 server API 回給 app Developer API Show 17
Case: Suggest Update • To-be: Firebase Remote Config • 彈性的
user group 設定 • Web 後台修改 18 Show PM/Marketer
Game over 不想等? 買金幣 看廣告 邀請朋友 不願意花錢的人 應用:依據行為模式 傳遞最適合的訊息 19
Firebase Cloud Messaging (FCM) • GCM è FCM: Android &
iOS • 鎖定 目標受眾 • 追蹤 轉換事件 • 自定義參數 • 在地化 • 語言 / 國家 • 當地時間 20
Dynamic Links • Dynamic Link: 根據平台,導至不同頁面 • iOS - 開
app 特定頁面 or App Store (未安裝) • Android - 開 app 特定頁面 or Google Play (未安裝) • Desktop - 官網 • Deep Link: 安裝前後不中斷體驗 • 個人化初次開啟流程 • Attribution Analysis • 從哪個渠道下載的用戶黏度最高? • 從哪個渠道下載的用戶邀請最多朋友安裝? • 從哪個渠道下載的用戶消費最高? 21
Firebase Analytics 與它的快樂夥伴們! 22
Firebase Analytics • 免費,無總數上限 • App 專屬的自動事件記錄 • 支援原始資料匯出 •
分析 çè 行動 (Actionable metrics) • 推播訊息 • A/B testing • 分眾內容 • Deep links 23
Access Raw Data - BigQuery 24
Firebase Analytics BigQuery 匯出原始資料 只要一個開關 25
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 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 user_dim event_dim: <array>
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 Use Standard SQL to query
31 Array in SQL???
32 Handle array – use UNNEST()
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
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
How to save money? • 縮小時間範圍計算 FROM `xxxxproject.app_events_2017*` WHERE _TABLE_SUFFIX
BETWEEN '0220' AND '0220' • 要操作很多次的 è 匯出小 table 35
How to save money? • 善用 Common Table Expression (CTE)
à 合併一個 Query 36 Select 3 times (about 2TB processed) WITH xxx AS (SELECT xxx) (about 500G processed)
Application Case 37
Test – Debug view (real-time)
Test – Debug view (real-time)
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 Case 2: Monitor performance • 算平均 à 算分布 (percentile)
PERCENT_RANK() OVER (PARTITION BY app_ver ORDER BY response_time DESC) • 暫存到新 table • Query 快 / 省錢 • 避免 BQ 運算資源不足 • 隨時查某個百分位的值
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 What’s next? (Google I/O 2017) • A/B test suite
• 現有架構,需自己記錄 (可能有欄位不足問題) • 新版 console • Compare with Facebook App Analytics
stevenhsieh@gogolook.com Thank you J