ãã¼ã¿ã®è©³ç´°
Firebase Analytics ããã¨ã¯ã¹ãã¼ãããã表ã®ã¹ãã¼ãã¯åä¸ã§ã。ãµã³ãã«ã®ã¯ã¨ãªãå®è¡ãã¦ã¿ããã¨ãã§ããããã«、ãµã³ãã«ã®ã¦ã¼ã¶ã¼ãã¼ã¿ãå
¥ãã 2 ã¤ã®ãã¼ã¿ã»ãã(
iOS ã¨
Android )ã使ãã¾ãã。ãã®ãã¼ã¿ã»ããã¯、iOS 㨠Android ã®ã¯ãã¹ãã©ãããã©ã¼ã ã²ã¼ã ã¢ããªã®ãµã³ãã«ã§ã。åãã¼ã¿ã»ããã«ã¯、1 é±éåã®ã¢ããªãã£ã¯ã¹ ãã¼ã¿ãå
¥ã£ã¦ãã 7 ã¤ã®è¡¨ãå«ã¾ãã¦ãã¾ã。
次ã®ã¯ã¨ãªã¯、iOS çã¢ããªã® 1 æ¥åã®ä½¿ç¨ç¶æ³ãã、ããã¤ãã®åºæ¬çãªã¦ã¼ã¶ã¼å±æ§ã¨ç«¯æ«ãã¼ã¿ãè¿ãã¾ã。
SELECT
user_dim.app_info.app_instance_id,
user_dim.device_info.device_category,
user_dim.device_info.user_default_language,
user_dim.device_info.platform_version,
user_dim.device_info.device_model,
user_dim.geo_info.country,
user_dim.geo_info.city,
user_dim.app_info.app_version,
user_dim.app_info.app_store,
user_dim.app_info.app_platform
FROM
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
Firebase Analytics ããã¨ã¯ã¹ãã¼ãããããã¹ã¦ã® BigQuery ãã¼ãã«ã®ã¹ãã¼ãã¯åä¸ãªã®ã§、æ¬æç¨¿ã®ã¯ã¨ãªã¯ãã¹ã¦çæ§ã® Firebase Analytics ãã¼ã¿ã«å¯¾ãã¦å®è¡ã§ãã¾ã。ãã®å ´å、ãã¼ã¿ã»ããã¨è¡¨ã®ååãèªåã®ããã¸ã§ã¯ãã®ååã§ç½®ãæãã¾ã。
ã¹ãã¼ãã«ã¯、ã¦ã¼ã¶ã¼ãã¼ã¿ã¨ã¤ãã³ããã¼ã¿ãããã¾ã。ãã¹ã¦ã®ã¦ã¼ã¶ã¼ãã¼ã¿ã¯ Firebase Analytics ã§èªåçã«åå¾ããã¾ãã、ã¤ãã³ããã¼ã¿ã¯ã¢ããªã«è¿½å ããã«ã¹ã¿ã ã¤ãã³ãããåå¾ããã¾ã。ã§ã¯、ã¦ã¼ã¶ã¼ãã¼ã¿ã¨ã¤ãã³ããã¼ã¿ã®ã¬ã³ã¼ããè¦ã¦ã¿ã¾ããã。
ã¦ã¼ã¶ã¼ãã¼ã¿
ã¦ã¼ã¶ã¼ ã¬ã³ã¼ãã«ã¯、åã¦ã¼ã¶ã¼ã®ä¸æã®ã¢ã㪠ã¤ã³ã¹ã¿ã³ã¹ ID(ã¹ãã¼ãã®
user_dim.app_info.app_instance_id)ã«å ãã¦、å ´æ、端æ«、ã¢ããªã®ãã¼ã¸ã§ã³ãªã©ã®ãã¼ã¿ãå«ã¾ãã¦ãã¾ã。Firebase ã³ã³ã½ã¼ã«ã§ã¯、ã¢ããªã® Android 㨠iOS ã®ã¢ããªãã£ã¯ã¹ã§å¥ã
ã®ããã·ã¥ãã¼ãã«ãªã£ã¦ãã¾ã。BigQuery ã§ã¯、ãã©ãããã©ã¼ã ãåããã«、ã¯ã¨ãªãå®è¡ãã¦ã¦ã¼ã¶ã¼ãä¸çã®ã©ãããã¢ããªã«ã¢ã¯ã»ã¹ãã¦ããããæ¢ããã¨ãã§ãã¾ã。次ã®ã¯ã¨ãªã¯、BigQuery ã®
ã¦ããªã³æ©è½ ãæ´»ç¨ãã¦ãã¾ã。
UNION ALL æ¼ç®åã¨ãã¦ã³ã³ãã使ããã¨ãã§ãã¾ã。ãã®è¡¨ã§ã¯、ã¦ã¼ã¶ã¼ã«ãã£ã¦ããªã¬ã¼ãããã²ã¨ã¾ã¨ã¾ãã®ã¤ãã³ãã«å¯¾ã㦠1 è¡ã使ããã¾ã。ãã®ãã、åã¦ã¼ã¶ã¼ã 1 度ã ãæ°ããããã«
EXACT_COUNT_DISTINCT ã使ã£ã¦ãã¾ã。
SELECT
user_dim.geo_info.country as country,
EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
GROUP BY
country
ORDER BY
users DESC
ã¦ã¼ã¶ã¼ãã¼ã¿ã«ã¯、ã¦ã¼ã¶ã¼ãã¼ã¹ã®åã»ã°ã¡ã³ãã表ãããã«å®ç¾©ãã屿§ãå«ã
user_properties ã¬ã³ã¼ããå«ã¾ãã¦ãã¾ã。ãã¨ãã°、好ã¿ã®è¨èªãæå¨å°ãªã©ãããã«ãããã¾ã。Firebase Analytics ã¯、ããã©ã«ãã§
ããã¤ãã®ã¦ã¼ã¶ã¼ ãããã㣠ãåå¾ãã¾ã。ã¾ã、25 åã¾ã§ã®ç¬èªã®å±æ§ã追å ãããã¨ãã§ãã¾ã。
ã¦ã¼ã¶ã¼ã®å¥½ã¿ã®è¨èªã¯、ããã©ã«ãã®ã¦ã¼ã¶ã¼ ããããã£ã® 1 ã¤ã§ã。次ã®ã¯ã¨ãªãå®è¡ããã¨、両æ¹ã®ãã©ãããã©ã¼ã ã§ã¦ã¼ã¶ã¼ãã©ã®è¨èªã使ã£ã¦ããããè¦ããã¨ãã§ãã¾ã。
SELECT
user_dim.user_properties.value.value.string_value as language_code,
EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users,
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
WHERE
user_dim.user_properties.key = "language"
GROUP BY
language_code
ORDER BY
users DESC
ã¤ãã³ããã¼ã¿
Firebase Analytics ã使ãã¨、ã¢ã¤ãã ã®è³¼å
¥ããã¿ã³ã®ã¯ãªãã¯ãªã©ã®ã¢ããªå
ã®ã«ã¹ã¿ã ã¤ãã³ããç°¡åã«è¨é²ã§ãã¾ã。ã¤ãã³ããè¨é²ããéã«ã¯、ã¤ãã³ãåã¨æå¤§ 25 åã¾ã§ã®ãã©ã¡ã¼ã¿ã Firebase Analytics ã«æ¸¡ãã¾ã。ããã¨、ã¤ãã³ããçºçããåæ°ãèªåçã«ãã©ããã³ã°ã§ãã¾ã。次ã®ã¯ã¨ãªã¯、Android ã§ç¹å®ã®æ¥ä»ã«çºçããã¢ããªå
ã®åã¤ãã³ãã®åæ°ã表示ãã¾ã。
SELECT
event_dim.name,
COUNT(event_dim.name) as event_count
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
GROUP BY
event_dim.name
ORDER BY
event_count DESC
ã¤ãã³ãã«å¥ã®ã¿ã¤ãã®å¤(ã¢ã¤ãã ã®ä¾¡æ ¼ãªã©)ãé¢é£ä»ãããã¦ããå ´å、ããããªãã·ã§ã³ã®å¤ãã©ã¡ã¼ã¿ã¨ãã¦æ¸¡ã、BigQuery ã§ãã®å¤ã«ãã£ã¦ãã£ã«ã¿ãããã¨ãã§ãã¾ã。ãµã³ãã«è¡¨ã«ã¯、spend_virtual_currency ã¤ãã³ããããã¾ã。次ã®ã¯ã¨ãªã§ã¯、ãã¬ã¤ã¤ã¼ãä¸åº¦ã«ä½¿ã£ãä»®æ³é貨ã®é¡ã表示ããã¾ã。
SELECT
event_dim.params.value.int_value as virtual_currency_amt,
COUNT(*) as num_times_spent
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
WHERE
event_dim.name = "spend_virtual_currency"
AND
event_dim.params.key = "value"
GROUP BY
1
ORDER BY
num_times_spent DESC
è¤éãªã¯ã¨ãªã®ä½æ
ã¢ããªã®ä¸¡æ¹ã®ãã©ãããã©ã¼ã ã®ç¹å®ã®æéã«å¯¾ãã¦ã¯ã¨ãªãå®è¡ãããå ´åã¯、ã©ãããã°ããã§ãããã。Firebase Analytics ãã¼ã¿ã®è¡¨ã¯æ¥ä»ãã¨ã«åå²ããã¦ãããã、BigQuery ã®
TABLE_DATE_RANGE 颿°ã使ãã¾ã。次ã®ã¯ã¨ãªã¯、1 é±éã®éã«ã¢ã¯ã»ã¹ãã¦ããã¦ã¼ã¶ã¼ã®æå¨é½å¸ãã«ã¦ã³ããããã®ã§ã。
SELECT
user_dim.geo_info.city,
COUNT(user_dim.geo_info.city) as city_count
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
user_dim.geo_info.city
ORDER BY
city_count DESC
ããã«、1 é±éã®éã®ãã©ãããã©ã¼ã å
¨ä½ã§ã®ã¢ãã¤ã«ã¨ã¿ãã¬ããã®ä½¿ç¨çãæ¯è¼ããã¯ã¨ãªã使ãããã¨ãã§ãã¾ã。
SELECT
user_dim.app_info.app_platform as appPlatform,
user_dim.device_info.device_category as deviceType,
COUNT(user_dim.device_info.device_category) AS device_type_count FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
1,2
ORDER BY
device_type_count DESC
ããå°ãè¤éãªã¯ã¨ãªã使ãããã¨ãã§ãã¾ã。ãã©ãããã©ã¼ã ãã¾ããã ç´è¿ 2 é±éã®ä¸æã®ã¦ã¼ã¶ã¼ ã¤ãã³ãã®ã¬ãã¼ããçæãã¦ã¿ã¾ããã。ããã§ã¯、
PARTITION BY ã¨
EXACT_COUNT_DISTINCT ã使ã£ã¦ã¦ã¼ã¶ã¼ãã¨ã«ã¤ãã³ãã®éè¤ãæé¤ããã¨ã¨ãã«、ã¦ã¼ã¶ã¼ ããããã£ã¨
user_dim.user_id é
ç®ãæ´»ç¨ãã¦ãã¾ã。
SELECT
STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date,
appPlatform,
eventName,
COUNT(*) totalEvents,
EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users
FROM (
SELECT
fullVisitorid,
openTimestamp,
FORMAT_UTC_USEC(openTimestamp) firstOpenedTime,
userIdSet,
MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId,
appPlatform,
eventTimestamp,
FORMAT_UTC_USEC(eventTimestamp) as eventTime,
eventName
FROM FLATTEN(
(
SELECT
user_dim.app_info.app_instance_id as fullVisitorid,
user_dim.first_open_timestamp_micros as openTimestamp,
user_dim.user_properties.value.value.string_value,
IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet,
user_dim.app_info.app_platform as appPlatform,
event_dim.timestamp_micros as eventTimestamp,
event_dim.name AS eventName,
event_dim.params.key,
event_dim.params.value.string_value
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
), user_dim.user_properties)
)
GROUP BY
date, appPlatform, eventName
Google Analytics ã«åãã¢ããªã®ãã¼ã¿ãããã°、BigQuery ã«
Google Analytics ãã¼ã¿ãã¨ã¯ã¹ãã¼ã ã、Firebase Analytics BigQuery 表㨠JOIN ãããã¨ãã§ãã¾ã。
ã¢ããªãã£ã¯ã¹ ãã¼ã¿ã®è¦è¦å
çãã¼ã¿ã® BigQuery ã¨ã¯ã¹ãã¼ãã«ãã£ã¦、ã¢ãã¤ã«ã¢ããªã®ãã¼ã¿ã使ã£ãæ´å¯ãè¡ããããã«ãªãã¾ãã。次ã¯、
Google Data Studio ã使ã£ã¦ãã¼ã¿ãè¦è¦åãã¦ã¿ã¾ããã。Data Studio ã¯、BigQuery 表ããç´æ¥èªã¿åºããã¨ãã§ãã¾ã。ããã«、åè¿°ã®ãããªã«ã¹ã¿ã ã¯ã¨ãªã渡ããã¨ãå¯è½ã§ã。Data Studio ã¯、ãã¼ã¿æ§é ã«å¿ãã¦、æç³»åã°ã©ã、æ£ã°ã©ã、åã°ã©ã、å°å³ãªã©、ãã¾ãã¾ãªã¿ã¤ãã®ã°ã©ããçæã§ãã¾ã。
æåã®è¦è¦åã®ä¾ã¨ãã¦、åãã©ãããã©ã¼ã ã§ã¦ã¼ã¶ã¼ãã¢ããªã«ã¢ã¯ã»ã¹ããéã«ä½¿ã£ã¦ãã端æ«ã®ã¿ã¤ããæ¯è¼ããæ£ã°ã©ãã使ãã¦ã¿ã¾ããã。ã¢ãã¤ã«ã¨ã¿ãã¬ãããæ¯è¼ããåè¿°ã®ã¯ã¨ãªãç´æ¥ Data Studio ã«è²¼ãä»ããã¨、次ã®ã°ã©ããçæã§ãã¾ã。
ãã®ã°ã©ããã、iOS ã¦ã¼ã¶ã¼ã¯ã¿ãã¬ããã§ã²ã¼ã ãããã¦ã¼ã¶ã¼ã®æ¹ãå¤ããã¨ãç°¡åã«ãããã¾ã。ããã«è¤éãªä¾ã¨ãã¦、両æ¹ã®ãã©ãããã©ã¼ã ã®ã¤ãã³ãæ°ãæ¯è¼ããåè¿°ã®ã¤ãã³ã ã¬ãã¼ãã®ã¯ã¨ãªã使ã£ã¦æ£ã°ã©ãã使ãã¦ã¿ã¾ã。
BigQuery ããã¸ã§ã¯ãã Data Studio ã«æ¥ç¶ãã詳ããæé ã«ã¤ãã¦ã¯、
ãã¡ãã®æç¨¿ ãã覧ãã ãã。
次ã®ãããã¯
Firebase ã«ã¤ãã¦ããç¥ããªãã¨ããå ´åã¯、ã¾ã
ãã¡ã ãã覧ãã ãã。æ¢ã« Firebase ä¸ã«ã¢ãã¤ã«ã¢ããªãæ§ç¯ãã¦ããæ¹ã¯、Firebase ããã¸ã§ã¯ãã BigQuery ã«ãªã³ã¯ããããã®
詳ããã¬ã¤ã ãã覧ãã ãã。質åãããå ´åã¯、
BigQuery ãªãã¡ã¬ã³ã¹ ããã¥ã¡ã³ã ãåç
§ã、Stack Overflow ã§
firebase-analytics ã¿ã°ã
google-bigquery ã¿ã°ãã¤ãã¦è³ªåãã¦ãã ãã。ã¾ã、ä»å¾ã®æç¨¿ã§åãä¸ãã¦ã»ãããããã¯ãããã¾ããã、ãã²
ç§ã«ãç¥ãã ãã ãã。
Posted by
Kaz Sato - Developer Relations Team
[ãã®è¨äºã¯ Sara Robinson 、ãããããã¼ ã¢ããã±ã¼ãã«ãã Google Cloud Platform Blog ã®è¨äº "Using BigQuery and Firebase Analytics to understand your mobile app " ãå
ã«ç¿»è¨³・å çãããã®ã§ã。詳ããã¯å
è¨äºãã覧ãã ãã。]
5 æã® Google I/O ã§、Firebase ã¯ãããããã¼ã®ã¢ãã¤ã«ã¢ããªæ§ç¯ãæ¯æ´ãããã¾ãã¾ãªè£½åãçºè¡¨ ãã¾ãã。æ°ãã Firebase ãã©ãããã©ã¼ã ã®ä¸é¨ã§ãã Firebase Analytics ã¯、ã¦ã¼ã¶ã¼ã iOS ã¢ããªã Android ã¢ããªãã©ã®ããã«ä½¿ã£ã¦ãããã«ã¤ãã¦ã®ãã¼ã¿ãèªåçã«åå¾ãããã¼ã«ã§、ã«ã¹ã¿ã ã®ã¢ããªã¤ãã³ããå®ç¾©ãããã¨ãã§ãã¾ã。åå¾ãããã¼ã¿ã¯、Firebase ã³ã³ã½ã¼ã«ã®ããã·ã¥ãã¼ãããåç
§ã§ãã¾ã。æ°ãã Firebase ãã©ãããã©ã¼ã ã®ã¯ã©ã¦ãçµ±åæ©è½ã®ãã¡、ç§ãæ°ã«å
¥ã£ã¦ããã®ã¯、ã«ã¹ã¿ã ã®åæãè¡ãããã« Firebase Analytics ã®çãã¼ã¿ã Google BigQuery ã«ã¨ã¯ã¹ãã¼ãã§ããæ©è½ã§ã。ãã®ã«ã¹ã¿ã åææ©è½ã¯、iOS ç㨠Android çã®ã¢ããªã®ãã¼ã¿ãéè¨ããã、Firebase Analytics ã¤ãã³ãã§æ¸¡ãããã«ã¹ã¿ã ãã©ã¡ã¼ã¿ã«ã¢ã¯ã»ã¹ããéã«ç¹ã«ä¾¿å©ã§ã。ãã®å¼·åãªè£½åã®çµã¿åããã§ä½ãã§ããã®ã、è¦ã¦ã¿ã¾ããã。
BigQuery ã¨ã¯ã¹ãã¼ãã®ä»çµã¿
Firebase ããã¸ã§ã¯ãã BigQuery ã«ãªã³ã¯ãã ã¨、Firebase ã¯æ¯æ¥、èªåçã«é¢é£ä»ãããã BigQuery ãã¼ã¿ã»ããã«æ°ãã表ãã¨ã¯ã¹ãã¼ããã¾ã。iOS ç㨠Android çã®ä¸¡æ¹ã®ã¢ããªãããå ´å、Firebase ã¯åãã©ãããã©ã¼ã ã®ãã¼ã¿ãå¥ã
ã®ãã¼ã¿ã»ããã«ã¨ã¯ã¹ãã¼ããã¾ã。å表ã«ã¯、Firebase Analytics ãåå¾ããã¦ã¼ã¶ã¼ã®ã¢ã¯ãã£ããã£ã屿§ãã¼ã¿ã¨ã¨ãã«、ã¢ããªã§åå¾ãã¦ããã«ã¹ã¿ã ã¤ãã³ããå«ã¾ãã¦ãã¾ã。ãã®ãã、ã¯ãã¹ãã©ãããã©ã¼ã ã¢ããªã® 1 é±éåã®ãã¼ã¿ãã¨ã¯ã¹ãã¼ãããã¨、BigQuery ããã¸ã§ã¯ãã«ã¯ 2 ã¤ã®ãã¼ã¿ã»ãããã§ã、ããããã« 7 ã¤ã®è¡¨ãã§ãããã¨ã«ãªãã¾ã。
ãã¼ã¿ã®è©³ç´°
Firebase Analytics ããã¨ã¯ã¹ãã¼ãããã表ã®ã¹ãã¼ãã¯åä¸ã§ã。ãµã³ãã«ã®ã¯ã¨ãªãå®è¡ãã¦ã¿ããã¨ãã§ããããã«、ãµã³ãã«ã®ã¦ã¼ã¶ã¼ãã¼ã¿ãå
¥ãã 2 ã¤ã®ãã¼ã¿ã»ãã(iOS 㨠Android )ã使ãã¾ãã。ãã®ãã¼ã¿ã»ããã¯、iOS 㨠Android ã®ã¯ãã¹ãã©ãããã©ã¼ã ã²ã¼ã ã¢ããªã®ãµã³ãã«ã§ã。åãã¼ã¿ã»ããã«ã¯、1 é±éåã®ã¢ããªãã£ã¯ã¹ ãã¼ã¿ãå
¥ã£ã¦ãã 7 ã¤ã®è¡¨ãå«ã¾ãã¦ãã¾ã。
次ã®ã¯ã¨ãªã¯、iOS çã¢ããªã® 1 æ¥åã®ä½¿ç¨ç¶æ³ãã、ããã¤ãã®åºæ¬çãªã¦ã¼ã¶ã¼å±æ§ã¨ç«¯æ«ãã¼ã¿ãè¿ãã¾ã。
SELECT
user_dim.app_info.app_instance_id,
user_dim.device_info.device_category,
user_dim.device_info.user_default_language,
user_dim.device_info.platform_version,
user_dim.device_info.device_model,
user_dim.geo_info.country,
user_dim.geo_info.city,
user_dim.app_info.app_version,
user_dim.app_info.app_store,
user_dim.app_info.app_platform
FROM
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
Firebase Analytics ããã¨ã¯ã¹ãã¼ãããããã¹ã¦ã® BigQuery ãã¼ãã«ã®ã¹ãã¼ãã¯åä¸ãªã®ã§、æ¬æç¨¿ã®ã¯ã¨ãªã¯ãã¹ã¦çæ§ã® Firebase Analytics ãã¼ã¿ã«å¯¾ãã¦å®è¡ã§ãã¾ã。ãã®å ´å、ãã¼ã¿ã»ããã¨è¡¨ã®ååãèªåã®ããã¸ã§ã¯ãã®ååã§ç½®ãæãã¾ã。
ã¹ãã¼ãã«ã¯、ã¦ã¼ã¶ã¼ãã¼ã¿ã¨ã¤ãã³ããã¼ã¿ãããã¾ã。ãã¹ã¦ã®ã¦ã¼ã¶ã¼ãã¼ã¿ã¯ Firebase Analytics ã§èªåçã«åå¾ããã¾ãã、ã¤ãã³ããã¼ã¿ã¯ã¢ããªã«è¿½å ããã«ã¹ã¿ã ã¤ãã³ãããåå¾ããã¾ã。ã§ã¯、ã¦ã¼ã¶ã¼ãã¼ã¿ã¨ã¤ãã³ããã¼ã¿ã®ã¬ã³ã¼ããè¦ã¦ã¿ã¾ããã。
ã¦ã¼ã¶ã¼ãã¼ã¿
ã¦ã¼ã¶ã¼ ã¬ã³ã¼ãã«ã¯、åã¦ã¼ã¶ã¼ã®ä¸æã®ã¢ã㪠ã¤ã³ã¹ã¿ã³ã¹ ID(ã¹ãã¼ãã® user_dim.app_info.app_instance_id)ã«å ãã¦、å ´æ、端æ«、ã¢ããªã®ãã¼ã¸ã§ã³ãªã©ã®ãã¼ã¿ãå«ã¾ãã¦ãã¾ã。Firebase ã³ã³ã½ã¼ã«ã§ã¯、ã¢ããªã® Android 㨠iOS ã®ã¢ããªãã£ã¯ã¹ã§å¥ã
ã®ããã·ã¥ãã¼ãã«ãªã£ã¦ãã¾ã。BigQuery ã§ã¯、ãã©ãããã©ã¼ã ãåããã«、ã¯ã¨ãªãå®è¡ãã¦ã¦ã¼ã¶ã¼ãä¸çã®ã©ãããã¢ããªã«ã¢ã¯ã»ã¹ãã¦ããããæ¢ããã¨ãã§ãã¾ã。次ã®ã¯ã¨ãªã¯、BigQuery ã®ã¦ããªã³æ©è½ ãæ´»ç¨ãã¦ãã¾ã。UNION ALL æ¼ç®åã¨ãã¦ã³ã³ãã使ããã¨ãã§ãã¾ã。ãã®è¡¨ã§ã¯、ã¦ã¼ã¶ã¼ã«ãã£ã¦ããªã¬ã¼ãããã²ã¨ã¾ã¨ã¾ãã®ã¤ãã³ãã«å¯¾ã㦠1 è¡ã使ããã¾ã。ãã®ãã、åã¦ã¼ã¶ã¼ã 1 度ã ãæ°ããããã« EXACT_COUNT_DISTINCT ã使ã£ã¦ãã¾ã。
SELECT
user_dim.geo_info.country as country,
EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
GROUP BY
country
ORDER BY
users DESC
ã¦ã¼ã¶ã¼ãã¼ã¿ã«ã¯、ã¦ã¼ã¶ã¼ãã¼ã¹ã®åã»ã°ã¡ã³ãã表ãããã«å®ç¾©ãã屿§ãå«ã user_properties ã¬ã³ã¼ããå«ã¾ãã¦ãã¾ã。ãã¨ãã°、好ã¿ã®è¨èªãæå¨å°ãªã©ãããã«ãããã¾ã。Firebase Analytics ã¯、ããã©ã«ãã§ããã¤ãã®ã¦ã¼ã¶ã¼ ãããã㣠ãåå¾ãã¾ã。ã¾ã、25 åã¾ã§ã®ç¬èªã®å±æ§ã追å ãããã¨ãã§ãã¾ã。
ã¦ã¼ã¶ã¼ã®å¥½ã¿ã®è¨èªã¯、ããã©ã«ãã®ã¦ã¼ã¶ã¼ ããããã£ã® 1 ã¤ã§ã。次ã®ã¯ã¨ãªãå®è¡ããã¨、両æ¹ã®ãã©ãããã©ã¼ã ã§ã¦ã¼ã¶ã¼ãã©ã®è¨èªã使ã£ã¦ããããè¦ããã¨ãã§ãã¾ã。
SELECT
user_dim.user_properties.value.value.string_value as language_code,
EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users,
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601],
[firebase-analytics-sample-data:ios_dataset.app_events_20160601]
WHERE
user_dim.user_properties.key = "language"
GROUP BY
language_code
ORDER BY
users DESC
ã¤ãã³ããã¼ã¿
Firebase Analytics ã使ãã¨、ã¢ã¤ãã ã®è³¼å
¥ããã¿ã³ã®ã¯ãªãã¯ãªã©ã®ã¢ããªå
ã®ã«ã¹ã¿ã ã¤ãã³ããç°¡åã«è¨é²ã§ãã¾ã。ã¤ãã³ããè¨é²ããéã«ã¯、ã¤ãã³ãåã¨æå¤§ 25 åã¾ã§ã®ãã©ã¡ã¼ã¿ã Firebase Analytics ã«æ¸¡ãã¾ã。ããã¨、ã¤ãã³ããçºçããåæ°ãèªåçã«ãã©ããã³ã°ã§ãã¾ã。次ã®ã¯ã¨ãªã¯、Android ã§ç¹å®ã®æ¥ä»ã«çºçããã¢ããªå
ã®åã¤ãã³ãã®åæ°ã表示ãã¾ã。
SELECT
event_dim.name,
COUNT(event_dim.name) as event_count
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
GROUP BY
event_dim.name
ORDER BY
event_count DESC
ã¤ãã³ãã«å¥ã®ã¿ã¤ãã®å¤(ã¢ã¤ãã ã®ä¾¡æ ¼ãªã©)ãé¢é£ä»ãããã¦ããå ´å、ããããªãã·ã§ã³ã®å¤ãã©ã¡ã¼ã¿ã¨ãã¦æ¸¡ã、BigQuery ã§ãã®å¤ã«ãã£ã¦ãã£ã«ã¿ãããã¨ãã§ãã¾ã。ãµã³ãã«è¡¨ã«ã¯、spend_virtual_currency ã¤ãã³ããããã¾ã。次ã®ã¯ã¨ãªã§ã¯、ãã¬ã¤ã¤ã¼ãä¸åº¦ã«ä½¿ã£ãä»®æ³é貨ã®é¡ã表示ããã¾ã。
SELECT
event_dim.params.value.int_value as virtual_currency_amt,
COUNT(*) as num_times_spent
FROM
[firebase-analytics-sample-data:android_dataset.app_events_20160601]
WHERE
event_dim.name = "spend_virtual_currency"
AND
event_dim.params.key = "value"
GROUP BY
1
ORDER BY
num_times_spent DESC
è¤éãªã¯ã¨ãªã®ä½æ
ã¢ããªã®ä¸¡æ¹ã®ãã©ãããã©ã¼ã ã®ç¹å®ã®æéã«å¯¾ãã¦ã¯ã¨ãªãå®è¡ãããå ´åã¯、ã©ãããã°ããã§ãããã。Firebase Analytics ãã¼ã¿ã®è¡¨ã¯æ¥ä»ãã¨ã«åå²ããã¦ãããã、BigQuery ã® TABLE_DATE_RANGE 颿°ã使ãã¾ã。次ã®ã¯ã¨ãªã¯、1 é±éã®éã«ã¢ã¯ã»ã¹ãã¦ããã¦ã¼ã¶ã¼ã®æå¨é½å¸ãã«ã¦ã³ããããã®ã§ã。
SELECT
user_dim.geo_info.city,
COUNT(user_dim.geo_info.city) as city_count
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
user_dim.geo_info.city
ORDER BY
city_count DESC
ããã«、1 é±éã®éã®ãã©ãããã©ã¼ã å
¨ä½ã§ã®ã¢ãã¤ã«ã¨ã¿ãã¬ããã®ä½¿ç¨çãæ¯è¼ããã¯ã¨ãªã使ãããã¨ãã§ãã¾ã。
SELECT
user_dim.app_info.app_platform as appPlatform,
user_dim.device_info.device_category as deviceType,
COUNT(user_dim.device_info.device_category) AS device_type_count FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
1,2
ORDER BY
device_type_count DESC
ããå°ãè¤éãªã¯ã¨ãªã使ãããã¨ãã§ãã¾ã。ãã©ãããã©ã¼ã ãã¾ããã ç´è¿ 2 é±éã®ä¸æã®ã¦ã¼ã¶ã¼ ã¤ãã³ãã®ã¬ãã¼ããçæãã¦ã¿ã¾ããã。ããã§ã¯、PARTITION BY 㨠EXACT_COUNT_DISTINCT ã使ã£ã¦ã¦ã¼ã¶ã¼ãã¨ã«ã¤ãã³ãã®éè¤ãæé¤ããã¨ã¨ãã«、ã¦ã¼ã¶ã¼ ããããã£ã¨ user_dim.user_id é
ç®ãæ´»ç¨ãã¦ãã¾ã。
SELECT
STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date,
appPlatform,
eventName,
COUNT(*) totalEvents,
EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users
FROM (
SELECT
fullVisitorid,
openTimestamp,
FORMAT_UTC_USEC(openTimestamp) firstOpenedTime,
userIdSet,
MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId,
appPlatform,
eventTimestamp,
FORMAT_UTC_USEC(eventTimestamp) as eventTime,
eventName
FROM FLATTEN(
(
SELECT
user_dim.app_info.app_instance_id as fullVisitorid,
user_dim.first_open_timestamp_micros as openTimestamp,
user_dim.user_properties.value.value.string_value,
IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet,
user_dim.app_info.app_platform as appPlatform,
event_dim.timestamp_micros as eventTimestamp,
event_dim.name AS eventName,
event_dim.params.key,
event_dim.params.value.string_value
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
), user_dim.user_properties)
)
GROUP BY
date, appPlatform, eventName
Google Analytics ã«åãã¢ããªã®ãã¼ã¿ãããã°、BigQuery ã« Google Analytics ãã¼ã¿ãã¨ã¯ã¹ãã¼ã ã、Firebase Analytics BigQuery 表㨠JOIN ãããã¨ãã§ãã¾ã。
ã¢ããªãã£ã¯ã¹ ãã¼ã¿ã®è¦è¦å
çãã¼ã¿ã® BigQuery ã¨ã¯ã¹ãã¼ãã«ãã£ã¦、ã¢ãã¤ã«ã¢ããªã®ãã¼ã¿ã使ã£ãæ´å¯ãè¡ããããã«ãªãã¾ãã。次ã¯、Google Data Studio ã使ã£ã¦ãã¼ã¿ãè¦è¦åãã¦ã¿ã¾ããã。Data Studio ã¯、BigQuery 表ããç´æ¥èªã¿åºããã¨ãã§ãã¾ã。ããã«、åè¿°ã®ãããªã«ã¹ã¿ã ã¯ã¨ãªã渡ããã¨ãå¯è½ã§ã。Data Studio ã¯、ãã¼ã¿æ§é ã«å¿ãã¦、æç³»åã°ã©ã、æ£ã°ã©ã、åã°ã©ã、å°å³ãªã©、ãã¾ãã¾ãªã¿ã¤ãã®ã°ã©ããçæã§ãã¾ã。
æåã®è¦è¦åã®ä¾ã¨ãã¦、åãã©ãããã©ã¼ã ã§ã¦ã¼ã¶ã¼ãã¢ããªã«ã¢ã¯ã»ã¹ããéã«ä½¿ã£ã¦ãã端æ«ã®ã¿ã¤ããæ¯è¼ããæ£ã°ã©ãã使ãã¦ã¿ã¾ããã。ã¢ãã¤ã«ã¨ã¿ãã¬ãããæ¯è¼ããåè¿°ã®ã¯ã¨ãªãç´æ¥ Data Studio ã«è²¼ãä»ããã¨、次ã®ã°ã©ããçæã§ãã¾ã。
ãã®ã°ã©ããã、iOS ã¦ã¼ã¶ã¼ã¯ã¿ãã¬ããã§ã²ã¼ã ãããã¦ã¼ã¶ã¼ã®æ¹ãå¤ããã¨ãç°¡åã«ãããã¾ã。ããã«è¤éãªä¾ã¨ãã¦、両æ¹ã®ãã©ãããã©ã¼ã ã®ã¤ãã³ãæ°ãæ¯è¼ããåè¿°ã®ã¤ãã³ã ã¬ãã¼ãã®ã¯ã¨ãªã使ã£ã¦æ£ã°ã©ãã使ãã¦ã¿ã¾ã。
BigQuery ããã¸ã§ã¯ãã Data Studio ã«æ¥ç¶ãã詳ããæé ã«ã¤ãã¦ã¯、ãã¡ãã®æç¨¿ ãã覧ãã ãã。
次ã®ãããã¯
Firebase ã«ã¤ãã¦ããç¥ããªãã¨ããå ´åã¯、ã¾ããã¡ã ãã覧ãã ãã。æ¢ã« Firebase ä¸ã«ã¢ãã¤ã«ã¢ããªãæ§ç¯ãã¦ããæ¹ã¯、Firebase ããã¸ã§ã¯ãã BigQuery ã«ãªã³ã¯ããããã®è©³ããã¬ã¤ã ãã覧ãã ãã。質åãããå ´åã¯、BigQuery ãªãã¡ã¬ã³ã¹ ããã¥ã¡ã³ã ãåç
§ã、Stack Overflow ã§ firebase-analytics ã¿ã°ã google-bigquery ã¿ã°ãã¤ãã¦è³ªåãã¦ãã ãã。ã¾ã、ä»å¾ã®æç¨¿ã§åãä¸ãã¦ã»ãããããã¯ãããã¾ããã、ãã²ç§ã«ãç¥ãã ãã ãã。
Posted by Kaz Sato - Developer Relations Team