In bug 1255472, we added Telemetry data collection to Firefox 47+ to measure the breakdown of Windows 10 usage by version (so we can differentiate between RTM, November Update, etc and so we can see the monthly patch level). Anthony put together a quick and dirty dashboard for visualizing the data at: http://nbviewer.jupyter.org/urls/s3-us-west-2.amazonaws.com/telemetry-public-analysis-2/windows-distribution/data/windows-build-distribution.ipynb However, this dashboard is a bit limited in functionality and it would be easier to improve upon it by rebuilding it on top of re:dash instead. Quick wish list: * Coalescing by major version number (i.e. allowing me to see version 10586.* as one number instead of broken down by monthly patch level) or a range of version numbers (i.e. 14xxx so I can lump all Insider builds together easily). * Breakdowns by monthly patch level so we can easily track uptake of monthly security updates. * Breakdown by Firefox version number and/or update channel (since presumably our pre-release users are going to be more likely to be on an Insider build of Windows 10 as well).

Started trying to reproduce this analysis in re:dash today. I notice the main_summary OS_SERVICE_PACK_MAJOR field is always 'null'. Is this expected? Test query [1] It looks like I can stratify by OS_VERSION. Is OS_VERSION a human readable product of the major/minor version codes? Where is the process to populate the presto DB specified? [1] https://sql.telemetry.mozilla.org/queries/669/source

(In reply to Ryan Harter [:harter] from comment #1) > Started trying to reproduce this analysis in re:dash today. I notice the > main_summary OS_SERVICE_PACK_MAJOR field is always 'null'. Is this expected? > Test query [1] That field shouldn't always be null. Mark, could you comment on that? That said, the longitudinal dataset (1% of clients) might be a better fit for this type of analysis. > It looks like I can stratify by OS_VERSION. Is OS_VERSION a human readable > product of the major/minor version codes? Where is the process to populate > the presto DB specified? You can find the code that creates the main_summary and the longitudinal view at [1]. Both views are based on the raw data [2]. Please file a bug if a field that you need is missing from a view (but present in the raw data). [1] https://github.com/mozilla/telemetry-batch-view/tree/master/src/main/scala/com/mozilla/telemetry/views [2] http://gecko.readthedocs.io/en/latest/toolkit/components/telemetry/telemetry/main-ping.html

Based on a survey of one windows user, it looks like the osServicePackMajor (and minor) fields are Integers and not Strings. Per IRC conversation, we may also need to add integer fields for windowsBuildNumber and windowsUBR. Pull request to fix and add these fields incoming presently.

Hey All, I have a sample query for this dashboard here: https://sql.telemetry.mozilla.org/queries Ryan, a few questions on how you'll use this dashboard: /763/source#table(In reply to Ryan VanderMeulen [:RyanVM] from comment #0) > Anthony put together a quick and dirty dashboard for visualizing the data at: > http://nbviewer.jupyter.org/urls/s3-us-west-2.amazonaws.com/telemetry-public- > analysis-2/windows-distribution/data/windows-build-distribution.ipynb First investigation suggests ~40% of Windows 10 clients do not report build numbers. Is this of interest? > Quick wish list: > * Coalescing by major version number (i.e. allowing me to see version > 10586.* as one number instead of broken down by monthly patch level) or a > range of version numbers (i.e. 14xxx so I can lump all Insider builds > together easily). We're basing this off of the longitudinal set, so we will need to present percentages instead of counts - is that OK? Otherwise, this should be easy. > * Breakdowns by monthly patch level so we can easily track uptake of monthly > security updates. Can you expand on what you want to track in this part of the dashboard? Are you looking for a time-series with % of users on a monthly patch for a given build number? This is doable but more complicated. > * Breakdown by Firefox version number and/or update channel (since > presumably our pre-release users are going to be more likely to be on an > Insider build of Windows 10 as well). Do you want all of these charts in a preconfigured dashboard or do you want to be able to answer questions on the fly with re:dash's interactive frontend? Stratifying the charts listed above by Firefox version and update channel will create a lot of charts which may drown out the signal.

(In reply to Ryan Harter [:harter] from comment #5) > First investigation suggests ~40% of Windows 10 clients do not report build > numbers. Is this of interest? That sounds mildly surprising, yes. The reporting was only added in Fx47, but that's been shipping for nearly two months now. Can we try filtering out any older versions? > We're basing this off of the longitudinal set, so we will need to present > percentages instead of counts - is that OK? Otherwise, this should be easy. Percentages are fine. > Can you expand on what you want to track in this part of the dashboard? Are > you looking for a time-series with % of users on a monthly patch for a given > build number? This is doable but more complicated. In the long run, I had something like this in mind: http://cdn.arstechnica.net/wp-content/uploads/2014/12/ie-2014-11.png In the short run, I can live with a histogram distribution for a given date and build number (i.e. distribution of windowsUBR for version 10586). > Do you want all of these charts in a preconfigured dashboard or do you want > to be able to answer questions on the fly with re:dash's interactive > frontend? Stratifying the charts listed above by Firefox version and update > channel will create a lot of charts which may drown out the signal. Some of these certainly seem like the kinds of things that could be changed on the fly instead having all the charts displayed in one shot, yes.

Here's a sample dashboard for review: https://sql.telemetry.mozilla.org/dashboard/windows-10-user-strata So far, this dashboard provides Win10 client breakdowns by Build&UBR, Build, and Build Prefix (e.g. 14xxx,10xxx). Note a few differences from the Anthony's previous analysis: * New dashboard uses the most recent ping from a client so long as it was submitted within the last 28 days. The previous analysis only looked at one day of data * Previous analysis restricted to "Nightly", while the dashboard does not restrict. My final todo for this dashboard is adding an example query to breakdown by Firefox version number and update channel. This query will produce a chart similar to the build prefix charts conditioned on a given version or channel. ETA tomorrow. (In reply to Ryan VanderMeulen [:RyanVM] from comment #6) > The reporting was only added in Fx47, > but that's been shipping for nearly two months now. Can we try filtering out > any older versions? Filtering to Fx47+ brings this down to a more reasonable level (~0.02%). > In the long run, I had something like this in mind: > http://cdn.arstechnica.net/wp-content/uploads/2014/12/ie-2014-11.png Thanks, the chart helps a lot. We briefly discussed this offline, but this looks difficult at first glance. We'd have to normalize the longitudinal dataset into a clean time series with at most one observation per client-day. > In the short run, I can live with a histogram distribution for a given date > and build number (i.e. distribution of windowsUBR for version 10586). This is now included in the draft dashboard. Look for "Patch Adoption" > Some of these certainly seem like the kinds of things that could be changed > on the fly instead having all the charts displayed in one shot, yes. SG. For now, the patch adoption distributions require the user to manually change a query value to change the build number.

Ryan, the dashboard is now ready for review. The only revision since comment #7 is the addition of the build number breakdown for firefox nightly. https://sql.telemetry.mozilla.org/dashboard/windows-10-user-strata A few notes and caveats: The percentages reported in the dashboard should be accurate to +/- 1 percentage point Note that the firefox nightly breakdown reports sampled counts and not percentages. This is definitely suboptimal but it should do for now. I have a write up coming later this week to document the issue. In short, all of the denominators of the percentages reported in this dashboard are approximate. They rely on a presto view, which means sequential reads of this table are not guaranteed to be consistent. For the larger breakdowns, this isn't a problem. However, since the nightly breakdown deals with a narrow slice of data, the error becomes large. Usually, we could use the ROLLUP functionality of Presto to deal with this, but it is apparently unsupported in re:dash. I'm looking into fixing this by creating a new table instead of using a view (or logical table), which would give us a performance win to boot.

As discussed offline, we've agreed to the following improvements: * Remove the Build-UBR plots * Partition the build groups into "RTM" (build <= 10240), "November Release" (build <=10586), "Anniversary" (build <= 14393), and "Insider" (build > 14393) * Remove reporting for NULL values * Add additional patch adoption charts for all Build Groups * Add build group distribution charts for all release channels and remove the aggregated build group distribution Expect these updates to happen by EOD Monday, so Tuesday morning EST.

The above updates are complete. Waiting on these to refresh this evening to confirm everything is working properly.

The following fixes were implemented today, as per my offline convo with RyanVM: > * Let's drop the Windows Build Distribution charts as I think they don't add much value. If someone wants finer-grained breakdowns in the future, we can worry about it then. > > * The Build Number Prefix Distribution tables are a bit hard to draw conclusions from as-is because they span all channels and will be heavily weighted by the Release population. I think we should drop them in favor of a chart for # of pings by channel (or percentages) so we can get a rough feeling for the size of each population. > > * If possible, let's try use percentages for the build distribution by release channel charts. Sounds like we may want to create a new table for this. > > * For the Example Patch Adoption Breakdown, let's get rid of the prefixes and only show numerical values. Also, let's only look at the major release versions to avoid random Insider noise (i.e. 10240, 10586, and 14393 only).

After fixing the following requests from RyanVM, I believe this is complete for now. Ryan, please confirm: * What do "Other" and "Insider" correspond to currently on the "Release Channel Distribution" chart? * For all build distribution charts, can we please order them chronologically by release? i.e. RTM, November, Anniversary, Insider, Other (I guess)

Looks good, thanks!