Firebase Console reports vastly different numbers to BigQuery queries

[ricky....@crocmedia.com]

I am trying to use a BigQuery query to find how many screen views occur by country/region (Firebase Console only provides country). 

The table data in BigQuery seems much more granular and I think this is why my numbers are either much smaller or larger than what Firebase reports. Is there some sort of guidance as to which rows in the tables actually represent a distinct screen view?

As an example, for one particular day, Firebase reports ~100k Australian based "screen_view" events in the Console.

My BigQuery query looks like this:

SELECT
  geo.region, COUNT(params.value.string_value) as count
FROM
  `xxx`,
  UNNEST(event_params) as params
WHERE
  geo.country = "Australia" AND geo.region > "" AND event_name = "screen_view" AND params.key = "firebase_screen"
GROUP BY
  geo.region
ORDER BY
  count DESC

I get some output which is quite a significant amount less than what the Firebase console reports for total screen views in Australia.

Row   region              count
1     Victoria            25613
2     South Australia     3557
...

[Kato Richardson]

Hi Ricky,

Keep in mind that the stats in the console are localized to your time zone, but BigQuery timestamps are UTC, so the date definitions won't match. Also keep in mind that data for the last 3 days makes it into BQ well ahead of the Firebase reports, so those numbers may differ as well.

☼, Kato

--
You received this message because you are subscribed to the Google Groups "Firebase Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.
To post to this group, send email to fireba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/97f6376f-f3ff-434e-b161-ff59cc6f0965%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Kato Richardson |

Developer Programs Eng |

kato...@google.com |

775-235-8398

[ricky....@crocmedia.com]

Hi Kato.

Thanks for your response, but this doesn't seem enough to account for the discrepancies. As an example, if I simplify my request a bit, and try to measure all "screen_view" events for one day, BigQuery tells me I have about 700,000. In Firebase for the same day, the console says 124,000.

SELECT 

  geo.region, COUNT(1) as count

FROM 

  `sen-app-43283.analytics_187572305.events_20190429`, 

  UNNEST(event_params) as params

WHERE

  geo.country = "Australia" AND geo.region > "" AND event_name = "screen_view"

GROUP BY 

  geo.region

ORDER BY

  count DESC

Surely this is some sort of bug? The lack of documentation for this stuff is just unbelievable.

To unsubscribe from this group and stop receiving emails from it, send an email to fireba...@googlegroups.com.

To post to this group, send email to fireba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/97f6376f-f3ff-434e-b161-ff59cc6f0965%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kato Richardson]

The data may very well be inconsistent for some reason and there could indeed be a bug. But you haven't shared an apples to apples comparison here, so it's hard to verify where the discrepancy is coming from.

The console is showing in your local time zone and BigQuery stores the data in UTC. The definition of "one day" in the console should overlap more than one partition in BigQuery, and you'll need to constrain the query based on event_timestamp to measure roughly the same time frame.

What sort of counts do you get if you just measure all events in Australia in BigQuery? Does that roughly equal the sum of the individual region counts or is there some unaccounted for bandwidth?

☼, Kato

To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.

To post to this group, send email to fireba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/e8ff32dc-7c69-417e-9747-e40935175d53%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[ricky....@crocmedia.com]

Hey Kato.

Thanks for your response. I see what you mean now about the time zones, which has led me to start experimenting with some larger date ranges. Unfortunately the total numbers just rise linearly. I changed tack and started just pulling screen names instead of the regions.

We can now start to see some of the issue. 1.8 million null values. Then a weird auto value. Interestingly, this auto number is the total number of screen views that Firebase shows me for this date range. So where does this leave us? What exactly is auto, and does that represent the total? Why are rows 3 and 5 different, because I know for a fact that the view controller NSMTodayViewController sets its screen name through the Firebase SDK to "Today"? This is all so confusing.

This is all muddied by the fact that there is hardly any documentation as to how the screen tracking works, and you've made it almost impossible to disable automatic screen tracking (which I suspect causes problems like this).

P.S. As helpful as you've been, this groups forum really isn't ideal for exchanging detailed information and getting specific issues resolved. You really need to implement a support mechanism, especially as you look to charge people to use services like BigQuery.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/e8ff32dc-7c69-417e-9747-e40935175d53%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kato Richardson]

Hi Ricky,

You can get paid support with SLAs from GCP support here, and Firebase offers free community support here. Please reach out and we'll have some of the analytics gurus take a look.

Feel free to CC me on the email thread once you have the case created. Happy to help.

In the interim, I'll see what I can learn about the null and auto values. Those are curious. Can you share the query you used to pull those up so I can try some similar lookups?

☼, Kato

To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.

To post to this group, send email to fireba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/e6097f26-1aad-491f-97fc-472f66d7e98b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Todd Kerpelman]

Ricky, I suspect the problem with that last set of results is a buggy use of UNNEST in your SQL query. Remember that UNNESTing your params will take _every_ parameter associated with that event and expand it out into a new row. This means you're probably combining parameters like firebase_event_origin (which has a string value of "auto") and firebase_screen_class and firebase_screen_id (which has a value of "nil") all into one query.

My guess is that you probably need to add back in a WHERE params.key = "..." statement.

Three tips that might help...

1. If you ever see results like this that you can't explain, temporarily stop aggregating your results (i.e. adding in a count statement) and look at your

resulting data rows. That can help a lot when it comes to debugging.

2. I'm more of a fan these days of using SELECT FROM UNNEST (helpful blog post). For instance, if you were interested in grabbing just the

firebase_screen parameters from your query, I would try something like this:

SELECT geo.region, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "firebase_screen_class") AS firebase_screen FROM `xxx` WHERE event_name = "screen_view" LIMIT 100

3. Also, to be honest, I don't think you need to unnest your parameters at all. If all you're interested in is view counts per region, you could do that even easier with:

SELECT geo.region, count(1)

FROM `xxx`

WHERE event_name = "screen_view" 

GROUP BY geo.region

ORDER BY 2 DESC

Hope that helps!

--T 

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/e6097f26-1aad-491f-97fc-472f66d7e98b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ricky D'Amelio]

Todd, thanks so much for your detailed response! You were spot on, the UNNEST was causing all the duplication of rows, which I didn't need. And even when I do, your SELECT FROM UNNEST syntax works better.

Your suggested query seems to be working properly and returning more consistent results.

Thanks again!

[Todd Kerpelman]

Glad I could help!

--T

You received this message because you are subscribed to a topic in the Google Groups "Firebase Google Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebase-talk/8nVjY-ZNObo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebase-tal...@googlegroups.com.

To post to this group, send email to fireba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/82078596-ea71-4ae4-b241-a37254b2cdaf%40googlegroups.com.