Firebase & # 8594; BigQuery how to activate users for this month, week, day

Question

I have absolutely no idea where to start, I already searched Google for information and came up with nothing. I have many applications from Firebase loaded in BigQuery. I want to be able to get active users for this month from bigquery. There must be a way you can just do this. Any help would be great. Thank.

Answer 1

It should be possible to count the number of different ones fullVisitorIdgrouped 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;

( , ). + :

#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;