The data that Glean applications generates maps cleanly to structures we create in BigQuery: see the section on Glean Data in the data pipeline reference.
You can use the Glean Dictionary to access these mappings when writing queries. For example, say you wanted to get a count of top sites as measured in Firefox for Android. In this case you would:
- Go to the Glean Dictionary home page.
- Navigate to the Firefox for Android application
- Under metrics, search for "top", select
- Scroll down to the bottom. Under BigQuery, you should see an entry like: "In
metrics.counter.metrics_top_sites_count". The former corresponds to the table name whilst the latter corresponds to the column name. You can select which channel you want to view information for and the table name will update accordingly.
With this information in hand, you can now proceed to writing a query. For example, to get the average of this metric on the first of January, you could write something like this:
-- Count number of pings where Fenix is the default browser SELECT AVG(metrics.counter.metrics_top_sites_count) FROM -- We give the table an alias so that the table name `metrics` and field name -- `metrics` don't conflict. org_mozilla_fenix.metrics AS m WHERE date(submission_timestamp) = '2021-01-01'
Note that we alias the table used in the query, otherwise the BigQuery parser gets confused.
This can also happen with the tables and columns corresponding to the events ping.
Another option is to explicitly qualify the table when selecting the column (so
SELECT AVG(metrics.metrics.counter.metrics_top_sites_count) FROM org_mozilla_fenix.metrics WHERE DATE(submission_timestamp) = '2021-01-01'