7

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.

Joe Scotto
  • 10,936
  • 14
  • 66
  • 136

1 Answers1

2

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;
Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • None of my tables have `fullVisitorId` – Joe Scotto Jan 05 '17 at 18:43
  • Sorry, I wish that I had a decent sample table to play around with. Does it have `user_dim.app_info.app_instance_id`? From past posts, it sounds like that can be used to identify users. You would use `COUNT(DISTINCT user_dim.app_info.app_instance_id)` in the above examples instead. – Elliott Brossard Jan 05 '17 at 18:50
  • My timestamps are stored in microseconds too. I'm very new to "advanced" SQL so any help is greatly appreciated. – Joe Scotto Jan 05 '17 at 19:12
  • What is the name of the timestamp column? To get from microseconds to a timestamps, you can use the `TIMESTAMP_MICROS` function, so it would be `FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP_MICROS(timestamp_col))`. – Elliott Brossard Jan 05 '17 at 19:13
  • `user_dim.first_open_timestamp_micros` – Joe Scotto Jan 05 '17 at 19:14
  • Okay, I edited my answer. Hopefully it uses the right columns now. – Elliott Brossard Jan 05 '17 at 19:18
  • Seems to be working but what does each column refer to? I have both `month` and `monthly_visitors` – Joe Scotto Jan 05 '17 at 19:19
  • `month` is the integer month (1 through 12) and `monthly_visitors` is the number of distinct visitors for that month. Or you can use the second query, which returns year and month instead. – Elliott Brossard Jan 05 '17 at 19:21
  • When I compare this to my data within Firebase Analytics console, it's way off. I have nearly 4.4k visitors for the past month in firebase but on here it's only pulling around 300. I am querying the table from today also and just setup bigquery yesterday. Is there a place I can find out how it gets the data into bigquery because I'm very confused now. – Joe Scotto Jan 05 '17 at 19:24
  • You will only see data since you linked the two. See http://stackoverflow.com/questions/40802963/google-firebase-bigquery-no-previous-data-available, for instance. – Elliott Brossard Jan 05 '17 at 19:29
  • On Firebase Analytics Console it's telling me my daily is 999. Are these numbers averaged? If so I think this did what I need. – Joe Scotto Jan 05 '17 at 19:30
  • That sounds likely, but I haven't used the Firebase Analytics Console to be able to confirm. – Elliott Brossard Jan 05 '17 at 19:32
  • One final question if you know. How often is data inserted into BigQuery? – Joe Scotto Jan 05 '17 at 19:33
  • From [this Firebase documentation](https://cloud.google.com/solutions/mobile/mobile-firebase-analytics-big-query#linking_firebase_analytics_data_to_bigquery), it sounds like it's daily: "After you link a Firebase app to a BigQuery project, Firebase Analytics exports your event data to a corresponding BigQuery dataset on a daily basis." – Elliott Brossard Jan 05 '17 at 19:37
  • Hi Guys, a little late to the party with this thread, but I also worked on this question that might shine further light on MAUs, WAUs and DAUs. Would love your feedback there: https://stackoverflow.com/questions/46302556/calculating-active-user-metrics-for-firebase-analytics-in-bigquery – d_- Sep 19 '17 at 14:09
  • I think the reason why the numbers are off is that you're using first_open_timestamp_micros. This means users are only counted in the month where they FIRST used the app. If they visit again in the future they won't be counted until they uninstall and reinstall the app (which causes first_open_timestamp_micros to change). I think you need to use event_dim.timestamp_micros instead. – NaxyMatt Nov 21 '17 at 03:06