Firebase - > BigQuery如何获得当月，周，日的活跃用户 [英] Firebase -> BigQuery how to get active users for that month, week, day

问题描述

我完全不知道从哪里开始，我已经搜索了谷歌的信息，什么都没有提出。我有很多来自Firebase的应用程序都投入BigQuery。我希望能够从bigquery获得当月的活跃用户。必须有一种方法可以简单地做到这一点。任何帮助将是伟大的。感谢。

解决方案

应该可以计算不同的 fullVisitorId ，按月分组：

  #standardSQL 
 SELECT 
 EXTRACT（MONTH FROM 
 TIMESTAMP_MICROS （user_dim.first_open_timestamp_micros））AS month，
 COUNT（DISTINCT user_dim.app_info.app_instance_id）AS monthly_visitors 
 FROM`your_dataset.your_table` 
 GROUP BY month; （注意，今年1月份和去年1月份的这个组别不一样）。您可以按年份或月份分组： 
 
 
  #standardSQL 
 SELECT 
 FORMAT_TIMESTAMP（
 '％Y-％m'，
 TIMESTAMP_MICROS（user_dim.first_open_timestamp_micros））AS year_and_month，
 COUNT（DISTINCT user_dim.app_info.app_instance_id）AS monthly_visitors 
 FROM`your_dataset.ga_sessions` 
 GROUP BY year_and_month; 
  
 
I have absolutely no idea where to start on this, I've already searched google for information and came up with nothing. I have many apps from Firebase feeding into BigQuery. I want to be able to get the active users for that month from bigquery. There has got to be a way that you can simply do this. Any help would be great. Thanks.
解决方案
It should be possible to count the number of distinct fullVisitorId, grouped by month:
#standardSQL
SELECT
  EXTRACT(MONTH FROM
      TIMESTAMP_MICROS(user_dim.first_open_timestamp_micros)) AS month,
  COUNT(DISTINCT user_dim.app_info.app_instance_id) AS monthly_visitors
FROM `your_dataset.your_table`
GROUP BY month;
(Note that this groups January of this year with January of last year, however). You could alternatively group by year + month:
#standardSQL
SELECT
  FORMAT_TIMESTAMP(
      '%Y-%m',
      TIMESTAMP_MICROS(user_dim.first_open_timestamp_micros)) AS year_and_month,
  COUNT(DISTINCT user_dim.app_info.app_instance_id) AS monthly_visitors
FROM `your_dataset.ga_sessions`
GROUP BY year_and_month;


                        
这篇关于Firebase  - > BigQuery如何获得当月，周，日的活跃用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！