1720218 - Create and Schedule Desktop Feature Usage Table

Status: RESOLVED FIXED

(Data Platform and Tools :: General, enhancement)

Description

[Leif Oines [:loines]]

(Anna already has some context for this)

TLDR: we'd like production-ize and schedule a new desktop feature usage table that looks substantially like the query at [1].

Overview

Currently, telemetry related to desktop feature usage is spread across many pings/tables, and the correct way of aggregating these measures is not always straightforward. (The term "feature usage" here refers to telemetry designed to measure behaviors about specific browser features, e.g. the password manager, pinned tabs etc).

We'd like to create a simplified view onto the most important and frequently used metrics related to Desktop browser feature usage by creating a new "feature usage" table.

Why We Want This

There are two intended uses for this table:

1. For Data Scientists: exploratory modeling of client behavior sometimes involves training models with many features/measurements. In the past this has involved substantial work in the way of custom queries that pull from many sources. It would be more efficient to have a single table of client-day data that is likely to contain most of the variables of interest.

2. For Users of Looker: We can lower the looker learning curve for PMs and other non-data-team users by collecting together the most commonly asked-for metrics related to desktop feature usage into a single Looker explore (rather than having these measures spread across e.g. clients_daily and events tables). The existence of this pre-aggregated table will make implementing this Looker view much easier.

Table Overview

[1] links to a prototype query. the query was used to generate a small sample table at mozdata.analysis.csl_feature_set_test_v1

Important things to know about how this was constructed, in terms of assumptions and what we're trying to achieve (likely to be points for discussion):

1. The table has a wide, client-day format similar to clients_daily (one row per client per day).
2. We would like to implement this in a way such that is maximally easy to add new measures. New features/telemetry are being added all the time and we see this dataset as eventually being the place to go for metrics related to new browser features (aspirational).
3. The query linked above does not currently implement this, however we intend there to be a special handling of null vs 0 values. In particular, we intend to coalesce all nulls for numerical measures to 0 in cases where the client was on an eligible browser version (i.e. a version where the underlying telemetry was implemented); otherwise leave the measure as null for ineligible versions. This would require, for each measurement, comparing the reported app_version against a minimum version from some metadata. The spreadsheet at [2] contains the minimum version for all the measurements contained in the prototype query.
4. (There was a bit of back and forth about this) we do not intend to expose all of these measurements in looker - some of them have not been verified in detail by DS to be reliable. We intend to reflect which measurements have been verified via some metadata - e.g. the "vetted" column in the spreadsheet at [2]. We hope this adds a (manageable) layer of curation to the dataset that will increase people's trust in the vetted metrics, while not gatekeeping and still allowing quick additions of other metrics that haven't been scrutinized (i.e. caveat emptor on all metrics that have vetted = FALSE in the spreadsheet).
5. Currently, the query for [1] operates on a 1% sample. This is generally enough data for DS needs and we believe (tentatively) that scaling the results by 100 for use in looker should be OK. The main downside here is that this blocks client-level joins to other telemetry (e.g. from experiments). Assuming the cost savings are substantial, I think this will be fine. In any case, I think at least starting out on a sample makes sense, and we can evaluate later if we run into use cases for the full dataset.

I think that covers the main points for now. Generally all of the above is negotiable, so please let us know if any of it presents major problems.

[1] https://github.com/mozilla/feature_table/blob/main/query.sql
[2] https://docs.google.com/spreadsheets/d/1AwrhjWn_ujlyhpeCwBGN_5Fqfl8frYx_PEoN_t5abhY/edit#gid=1288034616

Comment 1

[Jeff Klukas [:klukas] (UTC-4)]

I was involved with discussion of feature usage last year and had a tracking doc for Daily Per-Client Aggregations strategy. That lays out improvements to the clients_daily model to account for feature usage needs and for more rapid evolution of the clients_daily and clients_last_seen schemas. The intention there was to avoid the need for a separate feature usage table that would potentially need to be joined with clients_daily.

Of note, we implemented a clients_daily_joined and clients_daily_last_seen_joined that include a join to event ping data, and that model can be extended to additional joins as well, although we have not added any real event-based fields there as we were waiting on DS to decide on a vetting strategy. If DS has decided not to pursue adding new feature usage data to clients_daily, then DE needs to know this and we should likely tear down clients_daily_joined and clients_daily_last_seen_joined as they are currently unused.

I do realize that point (2) is important, and I would love to see a situation where DS feels empowered to quickly add new fields and make use of them and that clients_daily has not historically allowed this. If we move forward with this, I think we need a tight explanation of how this is different from clients_daily/clients_last_seen and what the ownership model is. My read of this is that a major piece of the intention behind this is giving DS more agency to evolve the table; do we understand the workflows around this table enough to know if that will be successful? Do you already have a working process for experimenting with this table, adding new fields, etc?

Comment 2

[Leif Oines [:loines]]

Hi Jeff, yes I think a large part of the motivation here is to create something that can be iterated on quickly, where the cost of time and compute is low. It can be thought of as a way to speculate cheaply on new metrics without having to commit the time and money to codify them in CD. To illustrate:

• The prototype query pulls not only from the events dataset but also the new tab / activity stream dataset. TBH these metrics are not absolutely critical, but we include them because they do measure some potentially interesting new tab behaviors. This is a case where it probably isn't worth the cost of implementing a full CD-activity stream join (and backfilling) but it is worth picking out the handful of events that are interesting for some modeling applications and that could be (speculative, see below) interesting to stakeholders (new tab telemetry is also very messy and new events are frequently added IIUC).
• The query also pulls many metrics from telemetry.main that are not currently aggregated in CD. Some of them probably would be worth adding to CD, and maybe even backfilling. Some of them might be worth adding without a backfill. And some of them probably aren't even worth the bother. There are two reasons why it can be unclear which one of these categories a metric falls in:
  1. The (e.g. predictive) value of a metric is sometimes only clear when it is put into a model to "compete" with many other metrics/features. In other words, it may not tell you much when aggregated and plotted on its own, but it does have marginal value, controlling for other metrics (and its hard to know this until you try).
  2. (and this might be more important) It can be hard to anticipate what stakeholders might find interesting to explore on their own. I have a feeling that some of these metrics might be useful to PMs, though I'm not sure. My hope is that this table would allow us to cheaply put some things in front of folks, and see what they find useful.

On ownership and development: I can only speak for myself, but I would be OK with DS mostly owning this, if we can get it into a shape where we can easily file PRs to add metrics as needed. I think what we're looking for here is help with scheduling and consultation on ways we can make the iteration process easy.

RE: clients_daily_joined, you're right that we haven't used this (at least I haven't). But I could still see it having some use if we find that it would be useful to "graduate" some metrics from the feature table we're proposing - this would allow us to compute statistics on the full population and potentially define new bit patterns based on them, which isn't covered by this workflow. In a sense, the table we're proposing here could create an implicit vetting process: someone finds a metric from this table useful, and would like to have it available in CD. That would be a proof of value that would make us less hesitant about spending the money to e.g. backfill it into CD.

Comment 3

[Leif Oines [:loines]]

Quick follow up, I should have answered more directly your question about how we've developed this. This is like the ~4th version of a table that Corey and I have used to do model development, cleaned up and run by Su for review. We've got quite a bit of mileage out of it (the DS intern is also currently using a version of it). But the development process has been messy - I or Corey find problems with some metrics, discover others that we want to add, want to widen the time window, aggregate on a 28 day rolling basis etc - the result is that we've forked the underlying query many times just to make small additions. We feel its time to (a) make the query incremental and (b) find a way to add metrics that is more efficient and doesn't require us to rebuild new versions of the table every time.

Comment 4

[Anna Scholtz [:ascholtz]]

Attachment: GitHub Pull Request

Comment 5

[Anna Scholtz [:ascholtz]]

I pushed a WIP PR for adding this feature usage table: https://github.com/mozilla/bigquery-etl/pull/2195

One of the main questions I have is how we want to store the metadata. I left a comment in the PR to discuss some of the options I could think of, but I'm also open to other alternatives

Comment 6

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/2202

Comment 7

[Anna Scholtz [:ascholtz]]

Changes have been merged into bigquery-etl and data has been backfilled to 2021-01-01.