1253751 - Write a notebook to generate Firefox cohort reports with Unified Telemetry data for desktop usage hours analysis

Status: RESOLVED FIXED

(Cloud Services Graveyard :: Metrics: Pipeline, defect, P1)

Description

[Jean Collings]

Somewhat similar to https://bugzilla.mozilla.org/show_bug.cgi?id=1198537 AND https://bugzilla.mozilla.org/show_bug.cgi?id=1226379#c27 we are looking to create Firefox cohort reports based on usage hours.

Mark Reid mentioned that we could potentially use the same data source as we do for churn/retention dashboards and that it may be as simple as adding a few more fields to the output.

The user stories are as follows: 

*As a Product Owner, I want to be able to on a regular basis calculate usage hours of new user cohorts acquired on a weekly basis over time, so I can understand if there any statistical difference in each cohort.

*As a Product Owner, I want to be able to filter usage hours cohorts data by geo, sync, channel, start version, acquisition period and week, so I can narrow down a cohort to a specific segment. 

*As a Product Owner, I want the data to be refreshed weekly, so that I have the latest data.

*As a Product Owner, I want the data to be able to be viewed and explored within a dashboard, so I can easily develop insights.


Note:

Similar to Unified Telemetry retention data, we want to create a tableau report to be able to explore usage data as it is better suited to filtering and handling larger data sets. 


Y axis = hours
X axis = time

The main filters on the report will be:

* Channel
* Country / Geo
* Type (Usage Hours)
* Start week
* End Week


Chris - please let me know if we are missing anything else here. Thanks.

Comment 1

[Chris More [:cmore]]

:mreid: what is your rough estimate on when you'll be able to start to tackle the usage hours?

Comment 2

[Thomas Huelbert]

Alessio, based your capacity can you take this?

Comment 3

[Benjamin Smedberg]

I'm concerned about this. The closest thing we have to usage hours is subsessionLength, but that has some known inconsistencies between OSes: sleep time is counted on some OSes and not others. Also it's got some outlier data at bug 1250640 which needs to be cleaned up before this metric can be used successfully.

So far we've been waiting on product/rweiss to decide what exact hours metric we're going to use as the standard for usage comparison metrics. I don't think I've seen that come through yet.

Comment 4

[Alessio Placitelli [:Dexter]]

(In reply to thuelbert from comment #2)
> Alessio, based your capacity can you take this?

I'm afraid I can't right now.

Comment 5

[Mark Reid [:mreid]]

(In reply to Benjamin Smedberg  [:bsmedberg] from comment #3)
> I'm concerned about this. The closest thing we have to usage hours is
> subsessionLength, but that has some known inconsistencies between OSes:
> sleep time is counted on some OSes and not others. Also it's got some
> outlier data at bug 1250640 which needs to be cleaned up before this metric
> can be used successfully.
> 
> So far we've been waiting on product/rweiss to decide what exact hours
> metric we're going to use as the standard for usage comparison metrics. I
> don't think I've seen that come through yet.

Is there a bug for defining the standard for usage? Since we don't really have anything else to use at the moment, it seems like we should still proceed with this bug (but ideally add some big shiny caveats on the viz side). I'll make sure to sanitize the values in that field (I'll add something to the "standards" library[1] so we can share and iterate).

[1] https://github.com/mozilla/python_moztelemetry/blob/master/moztelemetry/standards.py

Comment 6

[Georg Fritzsche [:gfritzsche]]

Is there a need to have this bug employee confidential?
I don't see any sensitive numbers in here.

Comment 7

[Alessio Placitelli [:Dexter]]

I'm taking this bug, as I'm actively working on it.

Comment 8

[Alessio Placitelli [:Dexter]]

My current implementation discards the subsession lengths with negative values or greater than 180 days. As suggested by :mreid, 180 days is a bit too high and could lower it (to about 7-days, so any subsession longer than 7 days gets discarded).

Chris, how should we treat subsession length outliers?

Comment 9

[Anthony Zhang [:azhang] (last day at Mozilla: 2016-04-29)]

The crash stats aggregate data [1] now has usage hours (computed from subsession lengths, with some cleaning and validation). Perhaps the data would be useful for implementing this?

It groups population based on the following:

* Channel
* Application
* Version
* Architecture
* Submission-date
* build-id
* OS and version
* geoCountry
* E10S enabled/disabled
* Experiment ID
* Experiment branch

See [2] for an example of a dashboard that can be made with this data.

[1]: https://github.com/mozilla/moz-crash-rate-aggregates
[2]: https://sql.telemetry.mozilla.org/queries/66

Comment 10

[Alessio Placitelli [:Dexter]]

Thanks Anthony, but I guess this has more in common with the churn data (as stated in comment 1).

In [1] from comment 9 the subsession length is capped to 25 hours [3], which seems to be fine for all the platforms except Windows (due to the sleep time inconsistencies).

Given the analysis in [4], I'd suggest 48h as the exclusion margin for a single subsession on Windows, if this is fine for :cmore.

[3] - https://github.com/mozilla/moz-crash-rate-aggregates/blob/master/crash_rate_aggregates/fill_database.py#L95
[4] - https://sql.telemetry.mozilla.org/queries/89/source#table

Comment 11

[Alessio Placitelli [:Dexter]]

As suggested by :gfritzsche, we should be clamping the subsession length in (0, threshold) rather than discarding the broken values.

Comment 12

[Chris More [:cmore]]

(In reply to Alessio Placitelli [:Dexter] from comment #10)
> Thanks Anthony, but I guess this has more in common with the churn data (as
> stated in comment 1).
> 
> In [1] from comment 9 the subsession length is capped to 25 hours [3], which
> seems to be fine for all the platforms except Windows (due to the sleep time
> inconsistencies).
> 
> Given the analysis in [4], I'd suggest 48h as the exclusion margin for a
> single subsession on Windows, if this is fine for :cmore.
> 
> [3] -
> https://github.com/mozilla/moz-crash-rate-aggregates/blob/master/
> crash_rate_aggregates/fill_database.py#L95
> [4] - https://sql.telemetry.mozilla.org/queries/89/source#table

I'm fine with your suggestion of 48h. This doesn't need to be perfect in all cases. Even if there is some error, as long as the error is consistent, we can use the data to compare various cohorts. We'll still have to calculate statistical significance if we notice any difference in the cohrts to ensure the sample size is large enough.

Comment 13

[Alessio Placitelli [:Dexter]]

Thanks :cmore. The code for this is ready, it adds a few lines to :mreid's churn report (PR: https://github.com/mozilla/churn-analysis/pull/1 ).

Until that PR is merged, I'm not sure if it makes sense to open another PR, since my work is extends that code.

Comment 14

[Alessio Placitelli [:Dexter]]

I've generated a new PR, based off :mreid's PR in comment 13: https://github.com/mozilla/churn-analysis/pull/2

Comment 15

[Roberto Agostino Vitillo (:rvitillo)]

(In reply to Jean Collings from comment #0)
> *As a Product Owner, I want to be able to on a regular basis calculate usage
> hours of new user cohorts acquired on a weekly basis over time, so I can
> understand if there any statistical difference in each cohort.

I had a quick look at the analysis; I think it would be good to add the sum of squares of usage hours as well so that we can compute the variance of a segment to make meaningful statistical comparisons.

Comment 16

[Verdi [:verdi] Best to slack me]

Any updates on this? This info will help us move forward on bug 1143116. Thanks!

Comment 17

[Alessio Placitelli [:Dexter]]

I've added the sum of squares of usage hours, as suggested by Roberto, and updated the PR from comment 14. That PR is pending review.

Comment 18

[Mark Reid [:mreid]]

Jean, will your team be taking care of the Tableau reporting side of things once the dataset is available?

Comment 19

[[:josephine]]

As this is a change to Tableau (additional field), I will work with Jean on the Tableau reporting side of things once the dataset is available.

Comment 20

[Jean Collings]

(In reply to Josephine from comment #19)
> As this is a change to Tableau (additional field), I will work with Jean on
> the Tableau reporting side of things once the dataset is available.

Thanks Josephine! Please work with Cmore as I've been put on a different project now.

Comment 21

[Alessio Placitelli [:Dexter]]

The PR was reviewed and we're backfilling the data.

Comment 22

[Alessio Placitelli [:Dexter]]

Josephine, I've uploaded the data to gdrive, in the "churn bug1253751" folder.

Comment 23

[[:josephine]]

Alessio, do you have the definition of the two additional fields:
1. usage_hours
2. sum_squared_usage_hours

Also, What is the logic behind getting the two fields?

Comment 24

[[:josephine]]

Chris, how do you want to capture the usage hours in tableau? I'll send you an invite for this for next week? My schedule is crazy this week. I'll send you one week data file for you to see the data more closely.

Comment 25

[Alessio Placitelli [:Dexter]]

(In reply to Josephine from comment #23)
> Alessio, do you have the definition of the two additional fields:
> 1. usage_hours
> 2. sum_squared_usage_hours

- usage_hours: sum of the per-client subsession lengths, clamped in the [0, 48h] range;
- sum_squared_usage_hours: the sum of squares of the usage hours (see comment 15, where it was requested);

> Also, What is the logic behind getting the two fields?

I'm not sure what you mean here.

Comment 26

[Chris More [:cmore]]

(In reply to Josephine from comment #24)
> Chris, how do you want to capture the usage hours in tableau? I'll send you
> an invite for this for next week? My schedule is crazy this week. I'll send
> you one week data file for you to see the data more closely.

Let's talk through it next week. We gave an example of how it can be done in comment 0, but maybe we need to draw it out on a white board to see if it makes sense.

Comment 27

[[:josephine]]

Chris,

As discussed, usage hours by channels here:
https://dataviz.mozilla.org/views/FirefoxDesktopCohortAnalysis-UT_0/UsageHoursbyChannel

The graph shows sum of usage hours and I use the % of total hours in the summary table. I think it's more useful than showing the sum of hours. Please have a look and let me know which one works best for you.

PS. data is updated up to April 16th.

Thanks,
Josephine

Comment 28

[Alessio Placitelli [:Dexter]]

Let's move the validation/visualization to bug 1270474. I've moved the ni? and the last comment there.
Let's make this bug about writing the notebook: it was reviewed and merged to the churn repository (https://github.com/mozilla/churn-analysis).