1059964 - Calculate popularity for light weight themes

Reporter

Description

•

10 years ago

Right now popularity is calculated in pentaho and injected into our database.  I think we can reduce some complexity and calculate this ourselves.  We should probably use celery to keep this in a low priority queue.

Wil Clouser [:clouserw]

Reporter

Updated

•

10 years ago

Blocks: 1059966

Ricardo Fradinho [:rfradinho]

Comment 1

•

10 years ago

This is how popularity is calculated by Pentaho. There are two metrics calculated and pushed to AMO, popularity and movers based on LWT ADI (Requests):


* update personas popularity
calculate:
[7DayAverage] = Agv(Reguests, over last 7 days)

-- update popularity
update personas
set popularity = [7DayAverage]
where addon_id = [addon_id]

* update_movers

calculate:
[ThisWeek] = Avg(Requests, over last 7 days)
[Last3Weeks] = Avg(Requests, over last 21 days)
[Evolution] = ([ThisWeek] - [Last3Weeks]) / [Last3Weeks]

-- reset movers:
update personas set movers=0;

-- update movers:
update personas
set movers = [Evolution]
where addon_id = [addon_id]

Mathieu Agopian [:magopian]

Comment 2

•

10 years ago

PR: https://github.com/mozilla/olympia/pull/254

Mathieu Agopian [:magopian]

Comment 3

•

10 years ago

Fixed in https://github.com/mozilla/olympia/commit/b2515d8d3d74516311c367fffae315f716965e4c, let's see the results in a few days.

Mathieu Agopian [:magopian]

Comment 4

•

10 years ago

Oups: https://github.com/mozilla/olympia/pull/255 (we don't want to update the popularity and movers fields yet, use a popularity_tmp and movers_tmp fields for now).

Mathieu Agopian [:magopian]

Comment 5

•

10 years ago

Fixed (again) in https://github.com/mozilla/olympia/commit/53bd03b2ff1fb50d3b3cfc24e446916a92df8650

Mathieu Agopian [:magopian]

Comment 6

•

10 years ago

Following a discussion on IRC with Ricardo:

> for movers we only update if: [ThisWeek] > 100.0 AND [Last3Weeks] > 1.0

Mathieu Agopian [:magopian]

Comment 7

•

10 years ago

PR to speed things up by using bulk queries: https://github.com/mozilla/olympia/pull/260

Mathieu Agopian [:magopian]

Comment 8

•

10 years ago

Attached file Script to backfill the last 21 days of LWT update counts — Details

Uploaded a small python script to backfill the LWT updatecheck counts for the last 21 days.

To run:

DJANGO_SETTINGS_MODULE=settings_local python backfill_theme_users.py

Mathieu Agopian [:magopian]

Comment 9

•

10 years ago

Last PR merged in https://github.com/mozilla/olympia/commit/59ca1e378144c8a91c5222c85f06d29ddfc9d677

Mathieu Agopian [:magopian]

Comment 10

•

10 years ago

First numbers came through, after the backfill:

POPULARITY

mysql> select id, addon_id, persona_id, popularity, popularity_tmp, popularity*100.0/popularity_tmp-100 as diff from personas order by popularity desc limit 100;


+--------+----------+------------+------------+----------------+-----------+
| id     | addon_id | persona_id | popularity | popularity_tmp | diff      |
+--------+----------+------------+------------+----------------+-----------+
| 240805 |   263347 |     340931 |    1446095 |         723325 |  99.92327 |
|   2962 |    18066 |       5918 |     203458 |         126998 |  60.20567 |
|  31481 |    46852 |      64769 |      95303 |          45353 | 110.13604 |
|  43420 |    60179 |      80276 |      88374 |          45147 |  95.74723 |
|   3677 |    18781 |       7610 |      70810 |          35092 | 101.78388 |
| 270331 |   294291 |     381047 |      66608 |          52927 |  25.84881 |
|  12349 |    27453 |      25991 |      66276 |          22954 | 188.73399 |
|  77982 |    95961 |     122757 |      57511 |          31924 |  80.14973 |
|     10 |    15114 |         16 |      53152 |          23288 | 128.23772 |
|     27 |    15131 |         33 |      52987 |          33150 |  59.84012 |
| 145414 |   165163 |     209631 |      51928 |          21845 | 137.71115 |
|   8324 |    23428 |      17926 |      46233 |          38333 |  20.60887 |
|  58180 |    75387 |      98983 |      39792 |          13700 | 190.45255 |
|   9396 |    24500 |      20213 |      34600 |           5875 | 488.93617 |
| 262040 |   285667 |     370177 |      34481 |          15608 | 120.91876 |
| 183586 |   204056 |     262107 |      33582 |          15056 | 123.04729 |
| 134717 |   153659 |     195562 |      31219 |          14807 | 110.83947 |
|   3750 |    18854 |       7765 |      30628 |          16395 |  86.81305 |
|  93178 |   111435 |     141882 |      30229 |          14473 | 108.86478 |
|  31282 |    46627 |      64323 |      28868 |          16677 |  73.10068 |
|  75264 |    93135 |     120312 |      28313 |          15043 |  88.21379 |
| 252555 |   275623 |     357511 |      28224 |           8435 | 234.60581 |
| 284244 |   308922 |     398309 |      26162 |          11474 | 128.01116 |
| 151794 |   171543 |     218552 |      24624 |           9095 | 170.74217 |
|  33728 |    49331 |      67532 |      23900 |          13989 |  70.84852 |
| 263303 |   286995 |     371773 |      23828 |           9566 | 149.09053 |
|  58671 |    75896 |      99584 |      23700 |          10621 | 123.14283 |
| 114756 |   133345 |     172700 |      23570 |           9575 | 146.16188 |
| 244588 |   267270 |     346435 |      22014 |          11083 |  98.62853 |
|     74 |    15178 |         89 |      21481 |           8912 | 141.03456 |
| 378658 |   418418 |     492572 |      21338 |           7988 | 167.12569 |
| 124980 |   143671 |     183166 |      20029 |          10268 |  95.06233 |
| 164256 |   184305 |     235694 |      19170 |          10072 |  90.32963 |
| 198899 |   219810 |     282733 |      18889 |           9447 |  99.94707 |
| 200545 |   221554 |     286736 |      18270 |           6919 | 164.05550 |
| 248976 |   271900 |     352536 |      17108 |           8530 | 100.56272 |
| 147951 |   167700 |     213107 |      16878 |              0 |      NULL |
| 302321 |   328285 |     420088 |      15707 |           8283 |  89.62936 |
|  31650 |    47073 |      65250 |      15158 |              0 |      NULL |
|    329 |    15433 |        473 |      14634 |           7292 | 100.68568 |
|   1545 |    16649 |       2635 |      14510 |           7219 | 100.99737 |
| 168218 |   188332 |     241274 |      14236 |           6071 | 134.49185 |
| 175364 |   195622 |     250496 |      14234 |          10394 |  36.94439 |
| 340431 |   370431 |     465057 |      13906 |           4426 | 214.18888 |
| 328329 |   356664 |     451083 |      12536 |           6447 |  94.44703 |
|   1883 |    16987 |       3469 |      11905 |           6394 |  86.19018 |
|   7572 |    22676 |      16182 |      11627 |           6724 |  72.91791 |
|  97920 |   116211 |     154970 |      11356 |           5633 | 101.59773 |
| 155488 |   175237 |     223748 |      11262 |           4348 | 159.01564 |
| 349665 |   381711 |     474692 |      11240 |           2447 | 359.33796 |
| 135253 |   154200 |     196674 |      11028 |           4690 | 135.13859 |
|  27560 |    42664 |      56392 |      10938 |           4102 | 166.65041 |
| 252097 |   275128 |     356904 |      10935 |           5842 |  87.17905 |
| 254136 |   277332 |     360804 |      10749 |           6143 |  74.97965 |
| 251043 |   274030 |     355553 |      10693 |              0 |      NULL |
| 349657 |   381703 |     474687 |      10601 |           6804 |  55.80541 |
|   1252 |    16356 |       2050 |      10524 |           4626 | 127.49676 |
| 350239 |   382385 |     475013 |      10406 |           5195 | 100.30799 |
| 353029 |   385629 |     476651 |      10149 |           4667 | 117.46304 |
| 276139 |   300364 |     388199 |      10106 |           5133 |  96.88291 |
|  17853 |    32957 |      36939 |      10021 |           4156 | 141.12127 |
| 352081 |   384569 |     476081 |       9802 |           5800 |  69.00000 |
| 382638 |   424038 |     495112 |       9758 |           4243 | 129.97879 |
|  10621 |    25725 |      22702 |       9658 |           4835 |  99.75181 |
|   5675 |    20779 |      11944 |       9461 |           7138 |  32.54413 |
| 188744 |   209355 |     268730 |       9179 |           4878 |  88.17138 |
| 335158 |   364307 |     459632 |       8710 |           6612 |  31.73019 |
| 259263 |   282712 |     366455 |       8698 |           5014 |  73.47427 |
| 259061 |   282470 |     366157 |       8523 |           3013 | 182.87421 |
|  42834 |    59531 |      79567 |       8399 |           4740 |  77.19409 |
| 230145 |   252176 |     326042 |       7900 |           4227 |  86.89378 |
| 286185 |   310978 |     400611 |       7762 |           3784 | 105.12685 |
| 349645 |   381691 |     474681 |       7683 |           2967 | 158.94843 |
|  11875 |    26979 |      25085 |       7415 |           3871 |  91.55257 |
|  75450 |    93321 |     120549 |       7363 |           2367 | 211.06886 |
|  22912 |    38016 |      46920 |       7127 |           4366 |  63.23866 |
|  10438 |    25542 |      22309 |       6922 |           3118 | 122.00128 |
| 384945 |   427287 |     496695 |       6748 |           1130 | 497.16814 |
|  73640 |    91426 |     118801 |       6674 |           3206 | 108.17218 |
| 269880 |   293821 |     380284 |       6431 |           4851 |  32.57060 |
| 244776 |   267458 |     346720 |       6387 |              0 |      NULL |
|  21501 |    36605 |      44334 |       6126 |           3060 | 100.19608 |
|  29278 |    44382 |      59764 |       6113 |           3191 |  91.57004 |
|   5163 |    20267 |      10897 |       6083 |           3233 |  88.15342 |
| 310028 |   336610 |     429015 |       6044 |           2984 | 102.54692 |
| 336168 |   365570 |     460800 |       5995 |           2828 | 111.98727 |
|  61264 |    78621 |     102875 |       5925 |           2451 | 141.73807 |
| 253831 |   276962 |     359200 |       5788 |           2864 | 102.09497 |
| 272623 |   296690 |     383666 |       5555 |           3543 |  56.78803 |
| 254693 |   277889 |     359544 |       5532 |           3363 |  64.49599 |
| 257519 |   280842 |     364132 |       5493 |           2730 | 101.20879 |
| 353213 |   385827 |     476777 |       5419 |           4036 |  34.26660 |
| 106975 |   125478 |     164049 |       5380 |           3431 |  56.80560 |
| 238354 |   260788 |     337483 |       5361 |           2855 |  87.77583 |
|  25700 |    40804 |      52817 |       5332 |           2181 | 144.47501 |
| 443328 |   502266 |          0 |       5312 |           5293 |   0.35896 |
| 167630 |   187744 |     242722 |       5241 |           2858 |  83.37999 |
| 409726 |   459460 |          0 |       5235 |           5222 |   0.24895 |
| 473344 |   535190 |          0 |       5164 |           5145 |   0.36929 |
|  74803 |    92653 |     119762 |       5042 |           2577 |  95.65386 |
+--------+----------+------------+------------+----------------+-----------+



MOVERS

mysql> select id, addon_id, persona_id, movers, movers_tmp, movers*100.0/movers_tmp-100 as diff from personas order by movers desc limit 100;

+--------+----------+------------+---------------------+---------------------+---------------------+
| id     | addon_id | persona_id | movers              | movers_tmp          | diff                |
+--------+----------+------------+---------------------+---------------------+---------------------+
|  79207 |    97220 |     127966 |  224.72916666666669 |    2.30969300911854 |   9629.828413535843 |
| 476606 |   539210 |          0 |  136.80357142857142 |                NULL |                NULL |
| 468022 |   529154 |          0 |   67.66666666666667 |    2.04318488529015 |   3211.822985468954 |
| 476420 |   538970 |          0 |   27.09090909090909 |    0.15037996742671 |  17914.972043475314 |
| 473180 |   534992 |          0 |  19.669158878504675 |     1.9867328358209 |   890.0253584109892 |
| 430574 |   486394 |          0 |  19.065349544072948 |    1.99127544661404 |   857.4441133441194 |
| 475726 |   538126 |          0 |  4.0655737704918025 |   0.130448669986602 |  3016.6080657697507 |
| 475958 |   538398 |          0 |  3.8588156574105055 |   0.125473217279217 |   2975.409829353015 |
| 475732 |   538132 |          0 |  3.6344980587909044 |   0.131439780743085 |   2665.143123522834 |
| 476042 |   538484 |          0 |  3.0063861758076635 |   0.126193568292649 |  2282.3608575959342 |
| 475802 |   538210 |          0 |   2.879062736205593 |   0.115561187874084 |  2391.3751659792847 |
| 474410 |   536440 |          0 |   2.132626899225785 |   0.710187156785834 |  200.29082881160946 |
| 473968 |   535904 |          0 |  1.7711223648569072 |   0.435983817972729 |   306.2358032214149 |
| 474140 |   536112 |          0 |   1.721619090883806 |   0.464215940421486 |   270.8660002757893 |
| 474700 |   536784 |          0 |  1.6823812801101174 |   0.664665196186555 |  153.11710162689414 |
| 476014 |   538454 |          0 |  1.6601700186605843 |   0.122290716580763 |  1257.5601362709965 |
| 473570 |   535470 |          0 |  1.6129032258064517 |           0.6015625 |   168.1189777963972 |
| 473984 |   535922 |          0 |   1.576897514289512 |   0.444718273266272 |   254.5834765699757 |
| 320653 |   348231 |     441928 |  1.5408060574112235 |   0.585838686008638 |  163.00858823592694 |
| 476064 |   538510 |          0 |  1.4233203177595262 |    0.12587796482718 |   1030.714434185226 |
| 336015 |   365401 |     460628 |  1.2181695373184733 |   0.191745070172322 |   535.3068353849723 |
| 474608 |   536672 |          0 |  1.1853229624619548 |   0.510349288486416 |  132.25719898177235 |
|  16709 |    31813 |      34674 |  0.9916218581968239 |   0.459400146305779 |  115.85144588456345 |
| 476084 |   538534 |          0 |   0.864198958935801 |   0.126568436263818 |   582.7918432479273 |
| 474774 |   536924 |          0 |  0.7048989808689432 |     0.3366953426283 |   109.3581025999303 |
| 409418 |   459011 |          0 |  0.6602143104044246 |   0.397473469387755 |   66.10273672388251 |
| 145374 |   165123 |     209572 |   0.640272679601468 |  -0.107242718446602 |  -697.0313778648485 |
| 139324 |   158377 |     201492 |  0.5800586510263929 |  -0.491065268065268 | -218.12251624142488 |
|  16390 |    31494 |      34078 |  0.5588072887907233 |   0.454486556527373 |  22.953535316960952 |
| 473344 |   535190 |          0 |  0.5250805079382936 |  0.0897890812814403 |   484.7932737973448 |
| 304657 |   330819 |     422723 | 0.49382180793409947 |  0.0886280857952246 |   457.1843321484747 |
|  27048 |    42152 |      55427 | 0.46897190490892254 |   0.264822134387352 |   77.08939095814523 |
|  26256 |    41360 |      53921 |  0.4583333333333333 |   0.165088958990536 |  177.62809586776063 |
| 474808 |   536962 |          0 |  0.4315253959136784 |   0.203994052125013 |  111.53822448177465 |
| 459354 |   519478 |          0 | 0.41459854014598524 |   0.233709817549957 |   77.39885491004762 |
|  32144 |    47645 |      66280 | 0.40367473167182105 |   0.126920507268791 |   218.0531975159244 |
| 468452 |   529642 |          0 |  0.4035517452541335 |   0.160969017094017 |  150.70150302180915 |
|  57404 |    74584 |      98549 |  0.4025974025974026 |    0.13075050985724 |  207.91268273980637 |
| 461822 |   522252 |          0 |  0.3974838490309417 |   0.122716174779609 |  223.90501883292825 |
|  29352 |    44456 |      59916 |  0.3860824742268041 |    0.35678391959799 |   8.211848410047892 |
| 231003 |   253115 |     328407 |  0.3634272570442784 |   0.119326555760936 |  204.56527863955478 |
| 181285 |   201737 |     258463 | 0.34739726027397255 | 0.00212764528048369 |  16227.780925728215 |
|  49582 |    66526 |      87969 |  0.3243128964059196 |   0.142483137254902 |  127.61493230298862 |
|  15989 |    31093 |      33217 |  0.3208218095614382 |  0.0615855103689738 |  420.93716141884113 |
|  40133 |    56385 |      76371 |  0.3205174272367949 |   0.192185406698565 |   66.77511198314525 |
| 222762 |   244553 |     314963 |  0.3177339901477832 |  -0.667765238879736 | -147.58169063738984 |
|  45699 |    62544 |      81807 |  0.3162480779087649 |  0.0986672000000001 |   220.5199680428396 |
| 222044 |   243812 |     314709 |  0.3103591446958687 | -0.0123456790123456 |  -2613.909072036553 |
|  32996 |    48513 |      65509 | 0.30278194620801113 |   0.101085523734798 |  199.53047184320076 |
| 425012 |   479202 |          0 | 0.30183609493954316 |   0.176879533138808 |   70.64500882794295 |
| 180047 |   200467 |     256856 |  0.2995594713656387 |   0.230678915135608 |  29.859927245426064 |
| 118912 |   137536 |     174910 | 0.28259756222472604 |   0.248419721871049 |   13.75810265636568 |
| 203893 |   225034 |     290482 |  0.2730304293455605 |   0.160738626964433 |   69.85987407119919 |
| 272876 |   296959 |     383982 | 0.27059683821993175 |  -0.118932979135536 | -327.52044066058386 |
|  96905 |   115191 |     145895 |  0.2589390962671907 |  0.0117232415902141 |  2108.7670400253323 |
| 475046 |   537200 |          0 | 0.25535798796565473 |  0.0855977618843639 |  198.32320652334818 |
| 276174 |   300399 |     388243 |  0.2512926577042398 |   0.756653324994788 |  -66.78893101989993 |
| 340231 |   370200 |     464833 | 0.24282899058462878 |   0.221265086206897 |   9.745732933919882 |
| 176389 |   196695 |     251925 | 0.23975073794686771 |  0.0106605166051662 |  2148.9598471305035 |
| 123738 |   142407 |     181548 | 0.23425283965186616 |  0.0587170731707317 |   298.9518329204334 |
| 150433 |   170182 |     216643 |  0.2284424379232506 | -0.0496760259179266 |  -559.8645598194129 |
| 342188 |   372581 |     467157 | 0.22220156191892906 |  0.0292731687575881 |   659.0622107192638 |
| 467942 |   529070 |          0 | 0.22039003668661908 |  0.0330631458094144 |   566.5731021391958 |
|   9400 |    24504 |      20217 | 0.21987559670186596 | -0.0364422483014207 |  -703.3535441700344 |
|  63426 |    80872 |     105617 |  0.2198275862068965 |  0.0732984293193717 |  199.90763546798036 |
| 193657 |   214404 |     276609 |  0.2178606476938173 |   0.246913580246914 | -11.766437684004146 |
|   3216 |    18320 |       6452 |  0.2103709949409782 |   0.073706197398623 |   185.4183262273525 |
| 176441 |   196747 |     251998 | 0.20834800986262758 |   0.127145293538865 |   63.86608112941357 |
| 343665 |   374486 |     469034 |  0.2083248936601174 |  0.0163307852675469 |  1175.6575403272727 |
| 133171 |   152084 |     193667 | 0.20578420467185776 |  0.0424065387348969 |  385.26526995827464 |
| 471290 |   532848 |          0 |  0.2033495012354717 |  0.0320258026229897 |   534.9552066791838 |
|  23310 |    38414 |      47732 | 0.19946965232763703 |  0.0934747474747475 |   113.3941601516756 |
| 242799 |   265438 |     344609 | 0.19623233908948198 |   0.178247734138973 |  10.089668201048255 |
|  16208 |    31312 |      33677 |  0.1960392078415684 |  0.0995260663507109 |   96.97272787890918 |
|  34874 |    50579 |      69254 |  0.1959511077158136 |   0.131921824104235 |   48.53577794754213 |
|  39954 |    56201 |      76167 | 0.19378357636224106 | -0.0686979016958896 | -382.08077914821627 |
| 366348 |   401420 |     484640 | 0.18988242185865517 | 0.00634540299716483 |  2892.4406998814093 |
| 321128 |   348725 |     442523 | 0.18903688524590168 | -0.0426021624618797 |  -543.7260324873212 |
| 445910 |   505214 |          0 | 0.18760299150716178 |  0.0862489829129374 |  117.51327977575633 |
|  40540 |    56865 |      76853 | 0.18755052546483425 | 0.00244903581267218 |   7558.137316505595 |
| 106968 |   125471 |     163980 | 0.18701109696197932 |   0.224436498632278 |  -16.67527425279313 |
|   1513 |    16617 |       2538 | 0.18601398601398603 |   0.344612644701692 | -46.022298115321384 |
|  51689 |    68682 |      90062 | 0.18245390972663694 |  0.0576619505753631 |    216.419940543241 |
| 404972 |   453068 |          0 | 0.17986230636833048 |   0.099627374301676 |   80.53502627069108 |
| 350243 |   382389 |     475015 | 0.17886165595117662 |  -0.305948833121766 | -158.46129698425443 |
| 385164 |   427644 |     496853 | 0.17774566473988437 |   0.512027491408935 |  -65.28591379912328 |
|  24856 |    39960 |      51175 | 0.17678018575851404 |  -0.038872886297376 |  -554.7647540400083 |
| 476390 |   538930 |          0 |  0.1736453201970443 |                NULL |                NULL |
| 410251 |   460116 |          0 | 0.17177214078820321 |  0.0930225344871906 |    84.6564832221988 |
| 350295 |   382461 |     475044 | 0.17168674698795172 |   -0.13826212193951 |   -224.174824297189 |
| 294982 |   320292 |     411112 |  0.1714601769911504 |   0.220677671589922 | -22.302888300466947 |
|      6 |    15110 |         11 | 0.17096203029706877 | -0.0873189995612111 |  -295.7901844457385 |
| 165730 |   185802 |     237419 | 0.16931818181818187 |   0.518555669050051 |  -67.34811864493582 |
| 114814 |   133403 |     175128 | 0.16873661670235546 |   0.134309652928416 |  25.632531261389104 |
|  39162 |    55292 |      75207 | 0.16866359447004606 |  0.0605616797900264 |  178.49887099370454 |
| 334754 |   363761 |     459148 | 0.16796008869179596 |   0.283790934320074 |  -40.81555526281826 |
| 201425 |   222460 |     286110 | 0.16709606183290396 |   0.138040042149631 |  21.048979144600068 |
|   1011 |    16115 |       1589 | 0.16674840608141245 |  0.0938020100502512 |   77.76634636302862 |
| 469588 |   530958 |          0 | 0.16581560854006666 |  0.0312841718932642 |  430.03035882106394 |
| 380124 |   420440 |     493492 | 0.16521197007481297 |   0.120409112040911 |    37.2088600891596 |
+--------+----------+------------+---------------------+---------------------+---------------------+


Seems the numbers aren't making much sense for the moment...

Ricardo Fradinho [:rfradinho]

Comment 11

•

10 years ago

The values for popularity_tmp are incorrect. The Hive results for the top lwt (yahoo persona, addonid=263347) reports 1589387 counts:

ds		id	src	requests
----------	------  ------  ---------
2014-09-08	263347	<null>	1198690
2014-09-08	340931	gp	390695
2014-09-08	340931	<null>	2

This is inline with the popularity value (1446095) posted before. The popularity is a 7 day average ,so it should be inline with the daily counts.

Can you check where popularity_tmp=723325 is coming from? From Hive results, Hive import process or popularity calculation ?

Flags: needinfo?(mathieu)

Mathieu Agopian [:magopian]

Comment 12

•

10 years ago

PR: https://github.com/mozilla/olympia/pull/272

Ricardo, good catch, i wasn't adding the update count numbers for the old and new queries, but overwriting with one or another (so each day had either the count for the old queries with the "gp" sources or the count for the new queries, and thus the popularity was based on those numbers).

Flags: needinfo?(mathieu)

Mathieu Agopian [:magopian]

Comment 13

•

10 years ago

Fixed in https://github.com/mozilla/olympia/commit/e58be8520e26f8a37d78be4fa23b0699493eb609

Mathieu Agopian [:magopian]

Comment 14

•

10 years ago

Updated numbers:

+--------+----------+------------+------------+----------------+----------+
| id     | addon_id | persona_id | popularity | popularity_tmp | diff     |
+--------+----------+------------+------------+----------------+----------+
| 240805 |   263347 |     340931 |    1478519 |        1456911 |  1.48314 |
|   2962 |    18066 |       5918 |     206666 |         204938 |  0.84318 |
|  43420 |    60179 |      80276 |     114404 |         117515 | -2.64732 |
|  31481 |    46852 |      64769 |      96284 |          95094 |  1.25139 |
|   3677 |    18781 |       7610 |      71723 |          70588 |  1.60792 |
| 270331 |   294291 |     381047 |      67272 |          66265 |  1.51966 |
|  12349 |    27453 |      25991 |      66768 |          65726 |  1.58537 |
|  77982 |    95961 |     122757 |      57624 |          56841 |  1.37753 |
|     10 |    15114 |         16 |      53479 |          52745 |  1.39160 |
|     27 |    15131 |         33 |      52909 |          52362 |  1.04465 |
| 145414 |   165163 |     209631 |      52658 |          52251 |  0.77893 |
|   8324 |    23428 |      17926 |      46846 |          46078 |  1.66674 |
|  58180 |    75387 |      98983 |      39862 |          39314 |  1.39391 |
|   9396 |    24500 |      20213 |      35204 |          34789 |  1.19291 |
| 262040 |   285667 |     370177 |      34763 |          34448 |  0.91442 |
| 183586 |   204056 |     262107 |      33876 |          33417 |  1.37355 |
| 134717 |   153659 |     195562 |      31945 |          31437 |  1.61593 |
|  93178 |   111435 |     141882 |      31003 |          30589 |  1.35343 |
|   3750 |    18854 |       7765 |      30925 |          30454 |  1.54659 |
|  31282 |    46627 |      64323 |      29159 |          28921 |  0.82293 |
|  75264 |    93135 |     120312 |      28608 |          28297 |  1.09906 |
| 252555 |   275623 |     357511 |      28331 |          28068 |  0.93701 |
| 284244 |   308922 |     398309 |      26127 |          25808 |  1.23605 |
| 151794 |   171543 |     218552 |      25052 |          24820 |  0.93473 |
|  33728 |    49331 |      67532 |      24317 |          24087 |  0.95487 |
| 263303 |   286995 |     371773 |      24282 |          23722 |  2.36068 |
| 114756 |   133345 |     172700 |      24053 |          23844 |  0.87653 |
|  58671 |    75896 |      99584 |      23856 |          23558 |  1.26496 |
| 244588 |   267270 |     346435 |      22093 |          21772 |  1.47437 |
|     74 |    15178 |         89 |      22076 |          21930 |  0.66575 |
| 378658 |   418418 |     492572 |      21643 |          21298 |  1.61987 |
| 124980 |   143671 |     183166 |      20336 |          20155 |  0.89804 |
| 164256 |   184305 |     235694 |      19379 |          19085 |  1.54048 |
| 198899 |   219810 |     282733 |      18986 |          18758 |  1.21548 |
| 200545 |   221554 |     286736 |      18262 |          18067 |  1.07932 |
| 248976 |   271900 |     352536 |      17248 |          17124 |  0.72413 |
| 147951 |   167700 |     213107 |      17022 |              0 |     NULL |
| 302321 |   328285 |     420088 |      16013 |          15845 |  1.06027 |
|  31650 |    47073 |      65250 |      15259 |              0 |     NULL |
|    329 |    15433 |        473 |      14890 |          14770 |  0.81246 |
|   1545 |    16649 |       2635 |      14594 |          14415 |  1.24176 |
| 175364 |   195622 |     250496 |      14320 |          14123 |  1.39489 |
| 168218 |   188332 |     241274 |      14081 |          13907 |  1.25117 |
| 340431 |   370431 |     465057 |      13735 |          13570 |  1.21592 |
| 328329 |   356664 |     451083 |      12701 |          12566 |  1.07433 |
|   1883 |    16987 |       3469 |      11950 |          11763 |  1.58973 |
|   7572 |    22676 |      16182 |      11676 |          11508 |  1.45985 |
|  97920 |   116211 |     154970 |      11468 |          11297 |  1.51368 |
| 155488 |   175237 |     223748 |      11448 |          11145 |  2.71871 |
| 349665 |   381711 |     474692 |      11362 |          11218 |  1.28365 |
| 252097 |   275128 |     356904 |      11228 |          11001 |  2.06345 |
| 135253 |   154200 |     196674 |      11198 |          11075 |  1.11061 |
|  27560 |    42664 |      56392 |      11024 |          10888 |  1.24908 |
| 254136 |   277332 |     360804 |      10899 |          10749 |  1.39548 |
|  17853 |    32957 |      36939 |      10656 |          10550 |  1.00474 |
| 251043 |   274030 |     355553 |      10618 |              0 |     NULL |
|   1252 |    16356 |       2050 |      10575 |          10441 |  1.28340 |
| 349657 |   381703 |     474687 |      10470 |          10319 |  1.46332 |
| 350239 |   382385 |     475013 |      10300 |          10167 |  1.30815 |
| 353029 |   385629 |     476651 |      10198 |          10075 |  1.22084 |
| 276139 |   300364 |     388199 |      10144 |          10005 |  1.38931 |
| 382638 |   424038 |     495112 |       9872 |           9568 |  3.17726 |
|  10621 |    25725 |      22702 |       9822 |           9758 |  0.65587 |
| 352081 |   384569 |     476081 |       9672 |           9531 |  1.47938 |
|   5675 |    20779 |      11944 |       9551 |           9445 |  1.12229 |
| 188744 |   209355 |     268730 |       9203 |           9067 |  1.49994 |
| 335158 |   364307 |     459632 |       9068 |           8993 |  0.83398 |
| 259263 |   282712 |     366455 |       8715 |           8601 |  1.32543 |
| 259061 |   282470 |     366157 |       8504 |           8339 |  1.97865 |
|  42834 |    59531 |      79567 |       8379 |           8328 |  0.61239 |
| 230145 |   252176 |     326042 |       7946 |           7837 |  1.39084 |
| 286185 |   310978 |     400611 |       7774 |           7693 |  1.05291 |
|  75450 |    93321 |     120549 |       7600 |           7555 |  0.59563 |
| 349645 |   381691 |     474681 |       7513 |           7364 |  2.02336 |
|  11875 |    26979 |      25085 |       7442 |           7329 |  1.54182 |
|  22912 |    38016 |      46920 |       7188 |           7109 |  1.11127 |
|  10438 |    25542 |      22309 |       6990 |           6889 |  1.46611 |
| 384945 |   427287 |     496695 |       6727 |           6644 |  1.24925 |
|  73640 |    91426 |     118801 |       6703 |           6587 |  1.76104 |
| 310028 |   336610 |     429015 |       6646 |           6653 | -0.10522 |
| 269880 |   293821 |     380284 |       6470 |           6341 |  2.03438 |
| 244776 |   267458 |     346720 |       6316 |              0 |     NULL |
|   5163 |    20267 |      10897 |       6169 |           6079 |  1.48051 |
|  29278 |    44382 |      59764 |       6141 |           6074 |  1.10306 |
|  21501 |    36605 |      44334 |       6102 |           6037 |  1.07669 |
| 336168 |   365570 |     460800 |       6081 |           5980 |  1.68896 |
|  61264 |    78621 |     102875 |       6027 |           5975 |  0.87029 |
| 253831 |   276962 |     359200 |       5825 |           5782 |  0.74369 |
| 254693 |   277889 |     359544 |       5557 |           5470 |  1.59049 |
| 272623 |   296690 |     383666 |       5507 |           5412 |  1.75536 |
| 257519 |   280842 |     364132 |       5494 |           5417 |  1.42145 |
| 106975 |   125478 |     164049 |       5442 |           5393 |  0.90859 |
| 353213 |   385827 |     476777 |       5419 |           5292 |  2.39985 |
| 409726 |   459460 |          0 |       5407 |           5363 |  0.82044 |
|  25700 |    40804 |      52817 |       5389 |           5312 |  1.44955 |
| 238354 |   260788 |     337483 |       5333 |           5262 |  1.34930 |
| 167630 |   187744 |     242722 |       5324 |           5169 |  2.99865 |
| 443328 |   502266 |          0 |       5223 |           5164 |  1.14253 |
|  74803 |    92653 |     119762 |       5026 |           4986 |  0.80225 |
| 156560 |   176347 |     225714 |       4911 |           4848 |  1.29950 |
+--------+----------+------------+------------+----------------+----------+

Mathieu Agopian [:magopian]

Comment 15

•

10 years ago

movers:

+--------+----------+------------+---------------------+---------------------+---------------------+
| id     | addon_id | persona_id | movers              | movers_tmp          | diff                |
+--------+----------+------------+---------------------+---------------------+---------------------+
|  79207 |    97220 |     127966 |  176.78061224489795 |   0.729401586211533 |  24136.389882709966 |
| 477252 |   539982 |          0 |  27.258503401360546 |   0.508971543751482 |   5255.604598332944 |
| 477164 |   539882 |          0 |  17.338345864661655 |   0.433627003205128 |   3898.446991655573 |
| 477148 |   539866 |          0 |  17.305418719211822 |   0.475539189900053 |   3539.115153232483 |
| 477474 |   540226 |          0 |   14.75531914893617 |   0.250095602294455 |   5799.871494566988 |
| 477528 |   540286 |          0 |  14.029900332225914 |   0.208670899934311 |   6623.458008108694 |
| 477476 |   540228 |          0 |  12.997252747252748 |   0.219588029537505 |   5818.925897111734 |
| 477480 |   540232 |          0 |  10.398126463700235 |   0.206735646175695 |   4929.672751675998 |
| 468022 |   529154 |          0 |  10.313040470425458 |     1.4998126263761 |   587.6219261698169 |
|  40537 |    56862 |      76850 |   9.739010989010989 |    1.79005072819506 |   444.0634075678406 |
| 477450 |   540202 |          0 |   8.377289377289378 |   0.173723479599692 |   4722.197550148673 |
| 430574 |   486394 |          0 |  7.9080459770114935 |    1.26336716578499 |    525.949936897232 |
| 477460 |   540212 |          0 |   7.717857142857143 |   0.171033293027005 |   4412.488186518484 |
|  40546 |    56871 |      76860 |   6.723348017621145 |     1.6266815920398 |  313.31678249277473 |
| 473180 |   534992 |          0 |   6.296192384769539 |    1.12121212121212 |  461.55229377674334 |
| 476606 |   539210 |          0 |  3.1147599885024437 |                NULL |                NULL |
|  40540 |    56865 |      76853 |   3.006865592817533 |    1.17554690645551 |  155.78439927027546 |
| 477506 |   540260 |          0 |  2.7489177489177488 |   0.165739710789766 |  1558.5752055550756 |
| 475726 |   538126 |          0 |  2.3333333333333335 |   0.298738457536741 |   681.0622551153674 |
| 476420 |   538970 |          0 |  1.9306295399515743 |   0.376818484369651 |   412.3500093635713 |
| 475958 |   538398 |          0 |  1.9178791983001775 |   0.264374816924329 |   625.4394426111791 |
| 473570 |   535470 |          0 |  1.8514257128564282 |   0.626206493126645 |   195.6573803015474 |
|  42013 |    58544 |      78593 |   1.842105263157895 |   0.889917194349732 |   106.9973785037306 |
| 475732 |   538132 |          0 |  1.7827490216645825 |   0.235848445066869 |   655.8875451390523 |
| 409418 |   459011 |          0 |  1.5953271028037384 |   0.679502634779084 |   134.7786485511424 |
|  43190 |    59922 |      80002 |  1.5834038950042335 |   0.803155001416832 |   97.14798416382612 |
| 476042 |   538484 |          0 |  1.5701708771208478 |   0.257942588619879 |  508.72882044102994 |
| 476832 |   539486 |          0 |   1.530154542027893 |   0.392232293510496 |   290.1143703219701 |
|  41999 |    58530 |      78575 |  1.4730538922155687 |   0.768754301445285 |   91.61569430521246 |
| 476646 |   539254 |          0 |  1.3830829871098327 |   0.357438016528926 |  286.94344841685455 |
| 474410 |   536440 |          0 |  1.2336116053099766 |     0.2491785453858 |  395.07135672535185 |
| 476570 |   539170 |          0 |   1.152073732718894 |   0.235600551470588 |   388.9944974779556 |
|  16709 |    31813 |      34674 |    1.06542332741267 |   0.400802434975097 |   165.8225685377555 |
| 476586 |   539186 |          0 |  1.0647525962125841 |   0.282953572163169 |   276.2994006658379 |
|  26256 |    41360 |      53921 |  1.0326398852223815 |   0.498066900458592 |  107.32955437745107 |
| 476064 |   538510 |          0 |  1.0029777317452098 |   0.219566724286161 |  356.79860416281946 |
|  16390 |    31494 |      34078 |  0.9749958874814935 |   0.448488587291795 |  117.39591934078425 |
| 474140 |   536112 |          0 |  0.9397476934663902 |   0.160197297876903 |   486.6189417180435 |
| 468452 |   529642 |          0 |  0.9367088607594939 |   0.449453278688525 |  108.41073036395426 |
| 473968 |   535904 |          0 |  0.9282068730860837 |   0.158196407185629 |   486.7433335555579 |
| 476758 |   539374 |          0 |  0.8388592424109875 |   0.242631956314733 |  245.73320643833534 |
|  29352 |    44456 |      59916 |  0.8274497388000632 |   0.421347291875627 |   96.38188134939037 |
| 201852 |   222931 |     288141 |  0.8071999999999999 |   0.388711821086262 |  107.66026557779114 |
| 476014 |   538454 |          0 |   0.784202249909278 |   0.141594020704864 |  453.83853499284055 |
| 474700 |   536784 |          0 |  0.7692528250969809 |   0.139688924149892 |  450.68992032005394 |
| 425012 |   479202 |          0 |  0.7552388935456831 |   0.408642395372576 |   84.81657852878942 |
| 145374 |   165123 |     209572 |  0.7427385892116184 |    0.31026281903886 |  139.39013753323485 |
|  27048 |    42152 |      55427 |  0.7048346055979646 |   0.320536540240518 |  119.89212370891775 |
| 139324 |   158377 |     201492 |  0.6457399103139013 |   0.264233695652174 |   144.3821211825784 |
| 231003 |   253115 |     328407 |  0.6112491128459899 |   0.291097163016316 |  109.98113018769936 |
|  43641 |    60418 |      80544 |  0.6023271731690623 |   0.390261711800751 |   54.33929462098547 |
| 203893 |   225034 |     290482 |  0.6000393468424159 |   0.306538116591928 |   95.74705864106448 |
| 304657 |   330819 |     422723 |    0.58664363845711 |   0.257723217090795 |  127.62545225036419 |
| 475802 |   538210 |          0 |  0.5475709086707874 |  -0.052387047865138 |  -1145.241010870512 |
| 459354 |   519478 |          0 |  0.5446976336546888 |   0.265260821309656 |  105.34417067902697 |
| 195250 |   216064 |     278676 |  0.5382932166301968 |   0.174128955223881 |  209.13481100148033 |
|  23310 |    38414 |      47732 |  0.5196652719665272 |   0.285413793103448 |   82.07433716357741 |
| 476390 |   538930 |          0 |  0.5093399750933997 |                NULL |                NULL |
|  57404 |    74584 |      98549 |  0.4856230031948882 |   0.182591205211727 |  165.96188060195732 |
|  32144 |    47645 |      66280 |   0.477810650887574 |   0.201234914397979 |  137.43923976463432 |
|  15989 |    31093 |      33217 |  0.4510151663405089 |   0.166527725661872 |  170.83488022665819 |
| 181285 |   201737 |     258463 | 0.44499751119960185 |   0.197499140893471 |   125.3161756483938 |
| 132181 |   151056 |     192363 | 0.43618513323983155 |   0.261185402684564 |   67.00210990222001 |
|  63426 |    80872 |     105617 |  0.4339878131347327 |   0.234307321482374 |   85.22161850899735 |
|  15347 |    30451 |      31916 |  0.4236943251186976 |   0.305561693457098 |  38.660811937863485 |
| 476084 |   538534 |          0 |  0.4188103188873802 |   0.103019208010924 |  306.53614697073795 |
| 474608 |   536672 |          0 |  0.4073757127543621 | -0.0180755437850042 |   -2353.73973585419 |
| 404972 |   453068 |          0 | 0.40689655172413797 |   0.226282376336068 |   79.81804783587168 |
| 222044 |   243812 |     314709 |  0.3920509357514953 |   0.179941116405718 |  117.87734987012513 |
|  45699 |    62544 |      81807 |  0.3902791145332052 |   0.140864435146444 |    177.060078455905 |
| 473984 |   535922 |          0 |  0.3901709729356744 | -0.0767186981069412 |  -608.5735062810891 |
| 114814 |   133403 |     175128 |  0.3671510830819852 |   0.208180785254387 |    76.3616573130628 |
|  23417 |    38521 |      47964 | 0.36713735558408217 |   0.144419683257919 |  154.21559395640816 |
| 316990 |   344241 |     437556 |  0.3653444676409186 |   0.216685979142526 |   68.60549495941865 |
| 221690 |   243436 |     314192 |  0.3627726952850104 |   0.211631663974152 |   71.41702166520707 |
|  43420 |    60179 |      80276 | 0.34943466807461354 |   0.227797794741838 |   53.39686166436596 |
| 431450 |   487502 |          0 |  0.3365098272781419 |   0.149794322442421 |  124.64791841993338 |
|   9400 |    24504 |      20217 |  0.3355371900826446 |    0.15940208516518 |  110.49736566177603 |
| 133171 |   152084 |     193667 |  0.3350895679662802 |   0.169217474489796 |   98.02302863613917 |
| 380124 |   420440 |     493492 |  0.3213222156045265 |   0.173281697905182 |   85.43344132070473 |
| 336015 |   365401 |     460628 |  0.3188215045054168 | -0.0889256695756847 | -458.52584076869687 |
| 151804 |   171553 |     218566 |  0.3111111111111111 |   0.112997170345218 |   175.3264618579692 |
|  32996 |    48513 |      65509 | 0.30422505751934753 |   0.141687297657589 |  114.71583024651804 |
| 180047 |   200467 |     256856 |  0.3037447988904299 |    0.10468347107438 |   190.1554522151947 |
| 476008 |   538448 |          0 | 0.29976580796252916 |   0.156139432816689 |   91.98597212432597 |
| 123738 |   142407 |     181548 | 0.29338327091136085 |   0.134751858497821 |   117.7211314054752 |
| 222762 |   244553 |     314963 | 0.29063735177865613 |   0.122661971830986 |  136.94169222969998 |
| 201425 |   222460 |     286110 |  0.2892781316348194 |   0.153613520197857 |   88.31554101632716 |
|  30507 |    45671 |      62655 |   0.283720930232558 |   0.150224250325945 |   88.86493333597085 |
|  40133 |    56385 |      76371 |  0.2814021421616358 |   0.088098016336056 |  219.41938520863823 |
| 342188 |   372581 |     467157 |  0.2793962793962796 |   0.130519165453663 |  114.06532781997527 |
|  39954 |    56201 |      76167 |  0.2728276877761413 |  0.0977089212562586 |   179.2249512821898 |
|  29582 |    44686 |      60440 | 0.26875331564986743 |   0.142480552635531 |   88.62456010915784 |
|  26737 |    41841 |      54863 |  0.2671355498721226 |  0.0935812837432514 |   185.4583087415565 |
| 415156 |   466578 |          0 |   0.261738578680203 |   0.137943718191797 |   89.74302136490303 |
|   3216 |    18320 |       6452 | 0.25925925925925936 |    0.12184249628529 |  112.78229448961119 |
|  59732 |    77026 |     100922 |  0.2591847005535983 |   0.150078672985782 |   72.69922194618064 |
| 419954 |   472923 |          0 |  0.2501128668171557 |    0.13399141999142 |   86.66334518521515 |
| 474774 |   536924 |          0 |  0.2489394213072578 | -0.0402156386127892 |  -719.0114838263222 |
| 410251 |   460116 |          0 | 0.24852270808711815 |   0.119546108358638 |  107.88858081565539 |
+--------+----------+------------+---------------------+---------------------+---------------------+

Those numbers are still way off, but maybe that's because of some missing data not exported? Ricardo, what do you think?

Flags: needinfo?(rfradinho)

Mathieu Agopian [:magopian]

Comment 16

•

10 years ago

Regarding the entries with a popularity of 0, I started investigating on the first occurence:

mysql> select * from personas where id=147951;
+--------+----------+------------+---------+-------------+--------+------------+-------------+-----------+--------+------------------------------+---------------------+---------------------+----------------------+------------+---------+----------+-----------------+------------+----------------+
| id     | addon_id | persona_id | name    | description | header | footer     | accentcolor | textcolor | author | display_username             | submit              | approve             | movers               | popularity | license | checksum | dupe_persona_id | movers_tmp | popularity_tmp |
+--------+----------+------------+---------+-------------+--------+------------+-------------+-----------+--------+------------------------------+---------------------+---------------------+----------------------+------------+---------+----------+-----------------+------------+----------------+
| 147951 |   167700 |     213107 | 1107411 |     1107412 | 15.jpg | fekete.jpg | 7d7d7d      | ffffff    | Bátja  | Bátja ⃰ ⃰ ⃰ ® 叔父           | 2010-05-16 00:50:55 | 2010-05-16 05:16:55 | 0.026208322563969953 |      17016 |       7 |          |            NULL |       NULL |              0 |
+--------+----------+------------+---------+-------------+--------+------------+-------------+-----------+--------+------------------------------+---------------------+---------------------+----------------------+------------+---------+----------+-----------------+------------+----------------+

So that's a persona with addon_id=167700 and persona_id=213107. I ran the hive query for 2014-09-04, and I couldn't find a line with either "167700" and a type different than "gp", nor a line with "213107" and a type of "gp".

Ricardo, could you please have a look on your side and check why there's no such entries for this persona in particular?

Mathieu Agopian [:magopian]

Comment 17

•

10 years ago

Ricardo said that it might be caused by the current hive query filtering out the requests that are 404. I've checked this addon, and didn't see anything strange with it: https://addons.allizom.org/en-US/firefox/addon/katana006/

Ricardo, could you please provide me a few of those requests from the logs, so I can replay them and investigate why we would reveive 404s?

Another "fix" would be to simply remove this filter at the hive query level, and see if this fixes the numbers.

Ricardo Fradinho [:rfradinho]

Comment 18

•

10 years ago

Here's an example of a user that checks "Two Little Birds" addon_id=47073.
The user has the theme installed, but the update-check returns 404.
Currently, Pentaho is ignoring http response code, so they are exported. The Hive query is only accepting http response=200.

We need to decide if we should include these as ADI or not.

Flags: needinfo?(rfradinho) → needinfo?(clouserw)

Ricardo Fradinho [:rfradinho]

Comment 19

•

10 years ago

Attached file two_little_birds.example.txt — Details

Mathieu Agopian [:magopian]

Comment 20

•

10 years ago

Don't filter 404s: https://github.com/mozilla/olympia/pull/278
Fixed in https://github.com/mozilla/olympia/commit/5cf25dfabb9a6959ab8cf83996a28975b83e6188

Wil Clouser [:clouserw]

Reporter

Comment 21

•

10 years ago

Thanks Mathieu - I think that's the right choice.

Flags: needinfo?(clouserw)

Ricardo Fradinho [:rfradinho]

Comment 22

•

10 years ago

I found the cause of the movers mismatch. The formula I wrote at the beginning of this bug is incorrect.
The actual formula should be:

calculate:
[ThisWeek] = Avg(Requests, over last 7 days)
[Previous3Weeks] = Avg(Requests, over previous 21 days)
[Evolution] = ([ThisWeek] - [Previous3Weeks]) / [Previous3Weeks]

ThisWeek uses the range [day-6:day] and Previous3Weeks the 21 days before, [day-27:day-6].

Then movers are reset (as mentioned initially) and movers updated only when we meet these requirements:
[ThisWeek] > 100
[Previous3Weeks] > 1

This last should not impact much, but it may explain why there are still some mismatch between "movers" and "movers_tmp"

Ricardo Fradinho [:rfradinho]

Comment 23

•

10 years ago

I'm posting here the sanity checks that are done on Pentaho regarding the version-check pings. The fields must follow these rules, or else Pentaho discards the log record:

max_app_version: len<=50, discard if match .*\\.*
addon_status: len<=50, discard if match .*\\.*
product_version: len<=15, discard if match .*\\.*
product_os: len<=10, discard if match .*\\.*
product_build_target: len<=20, discard if match .*\\.*
addon_version: len<=25, discard if match .*\\.*
addon_guid: len<=255, discard if match .*\\.*
update_type: must be numeric

Mathieu Agopian [:magopian]

Comment 24

•

10 years ago

Fix the movers computation: https://github.com/mozilla/olympia/pull/279

Mathieu Agopian [:magopian]

Comment 25

•

10 years ago

Fixed in https://github.com/mozilla/olympia/commit/172e9a70f85e5fec2331ebda7dad3c518ea7f4a3

Mathieu Agopian [:magopian]

Comment 26

•

10 years ago

Attached file Script to backfill the last 28 days of LWT update counts — Details

Uploaded a new version of the small python script to backfill the LWT updatecheck counts (for the last 28 days this time).

To run:

DJANGO_SETTINGS_MODULE=settings_local python backfill_theme_users.py

Mathieu Agopian [:magopian]

Comment 27

•

10 years ago

Previous fix was incomplete, new PR: https://github.com/mozilla/olympia/pull/282

Mathieu Agopian [:magopian]

Comment 28

•

10 years ago

Fixed in https://github.com/mozilla/olympia/commit/9514524394523de1caedb9dfeb8bfd8c2f94fe82

Mathieu Agopian [:magopian]

Comment 29

•

10 years ago

Popularity:

mysql> select id, addon_id, persona_id, popularity, popularity_tmp, popularity*100.0/popularity_tmp-100 as diff from personas order by popularity desc limit 100;
+--------+----------+------------+------------+----------------+----------+
| id     | addon_id | persona_id | popularity | popularity_tmp | diff     |
+--------+----------+------------+------------+----------------+----------+
| 240805 |   263347 |     340931 |    1479932 |        1479926 |  0.00041 |
|   2962 |    18066 |       5918 |     209496 |         209499 | -0.00143 |
|  43420 |    60179 |      80276 |     144061 |         144059 |  0.00139 |
|  31481 |    46852 |      64769 |      97378 |          97380 | -0.00205 |
|   3677 |    18781 |       7610 |      78503 |          78511 | -0.01019 |
| 270331 |   294291 |     381047 |      68026 |          68025 |  0.00147 |
|  12349 |    27453 |      25991 |      67220 |          67220 |  0.00000 |
|  77982 |    95961 |     122757 |      57660 |          57663 | -0.00520 |
|     10 |    15114 |         16 |      53971 |          53972 | -0.00185 |
| 145414 |   165163 |     209631 |      53373 |          53374 | -0.00187 |
|     27 |    15131 |         33 |      53156 |          53158 | -0.00376 |
|   8324 |    23428 |      17926 |      46800 |          46800 |  0.00000 |
|  58180 |    75387 |      98983 |      39903 |          39901 |  0.00501 |
|   9396 |    24500 |      20213 |      35505 |          35507 | -0.00563 |
| 262040 |   285667 |     370177 |      35099 |          35098 |  0.00285 |
| 183586 |   204056 |     262107 |      33993 |          33993 |  0.00000 |
| 134717 |   153659 |     195562 |      32534 |          32538 | -0.01229 |
|  93178 |   111435 |     141882 |      31251 |          31251 |  0.00000 |
|   3750 |    18854 |       7765 |      31222 |          31222 |  0.00000 |
|  31282 |    46627 |      64323 |      29466 |          29467 | -0.00339 |
|  75264 |    93135 |     120312 |      28806 |          28807 | -0.00347 |
| 252555 |   275623 |     357511 |      28382 |          28381 |  0.00352 |
| 284244 |   308922 |     398309 |      25967 |          25968 | -0.00385 |
| 151794 |   171543 |     218552 |      25459 |          25460 | -0.00393 |
| 114756 |   133345 |     172700 |      24660 |          24660 |  0.00000 |
| 263303 |   286995 |     371773 |      24633 |          24639 | -0.02435 |
|  33728 |    49331 |      67532 |      24573 |          24574 | -0.00407 |
|  58671 |    75896 |      99584 |      24163 |          24164 | -0.00414 |
|     74 |    15178 |         89 |      22495 |          22495 |  0.00000 |
| 378658 |   418418 |     492572 |      22320 |          22319 |  0.00448 |
| 244588 |   267270 |     346435 |      22266 |          22266 |  0.00000 |
| 124980 |   143671 |     183166 |      20460 |          20459 |  0.00489 |
| 164256 |   184305 |     235694 |      19673 |          19671 |  0.01017 |
| 198899 |   219810 |     282733 |      19023 |          19022 |  0.00526 |
| 200545 |   221554 |     286736 |      18108 |          18109 | -0.00552 |
| 248976 |   271900 |     352536 |      17461 |          17461 |  0.00000 |
| 147951 |   167700 |     213107 |      17290 |          17290 |  0.00000 |
| 302321 |   328285 |     420088 |      16240 |          16241 | -0.00616 |
|  31650 |    47073 |      65250 |      15310 |          15310 |  0.00000 |
|    329 |    15433 |        473 |      14907 |          14908 | -0.00671 |
|   1545 |    16649 |       2635 |      14532 |          14532 |  0.00000 |
| 175364 |   195622 |     250496 |      14343 |          14342 |  0.00697 |
| 168218 |   188332 |     241274 |      13963 |          13962 |  0.00716 |
| 340431 |   370431 |     465057 |      13522 |          13523 | -0.00739 |
| 328329 |   356664 |     451083 |      12887 |          12887 |  0.00000 |
|   1883 |    16987 |       3469 |      11952 |          11951 |  0.00837 |
|   7572 |    22676 |      16182 |      11766 |          11767 | -0.00850 |
| 155488 |   175237 |     223748 |      11536 |          11543 | -0.06064 |
|  97920 |   116211 |     154970 |      11480 |          11480 |  0.00000 |
| 349665 |   381711 |     474692 |      11421 |          11420 |  0.00876 |
| 135253 |   154200 |     196674 |      11319 |          11319 |  0.00000 |
| 254136 |   277332 |     360804 |      11021 |          11019 |  0.01815 |
|  27560 |    42664 |      56392 |      11012 |          11011 |  0.00908 |
| 252097 |   275128 |     356904 |      10776 |          10776 |  0.00000 |
|  17853 |    32957 |      36939 |      10716 |          10715 |  0.00933 |
| 251043 |   274030 |     355553 |      10706 |          10705 |  0.00934 |
|   1252 |    16356 |       2050 |      10539 |          10539 |  0.00000 |
| 349657 |   381703 |     474687 |      10443 |          10443 |  0.00000 |
| 276139 |   300364 |     388199 |      10226 |          10225 |  0.00978 |
| 353029 |   385629 |     476651 |      10163 |          10162 |  0.00984 |
| 350239 |   382385 |     475013 |      10140 |          10139 |  0.00986 |
|  10621 |    25725 |      22702 |       9941 |           9941 |  0.00000 |
| 382638 |   424038 |     495112 |       9934 |           9934 |  0.00000 |
|   5675 |    20779 |      11944 |       9561 |           9562 | -0.01046 |
| 352081 |   384569 |     476081 |       9490 |           9490 |  0.00000 |
| 335158 |   364307 |     459632 |       9403 |           9403 |  0.00000 |
| 188744 |   209355 |     268730 |       9221 |           9221 |  0.00000 |
| 259263 |   282712 |     366455 |       8744 |           8745 | -0.01144 |
| 259061 |   282470 |     366157 |       8539 |           8538 |  0.01171 |
|  42834 |    59531 |      79567 |       8383 |           8383 |  0.00000 |
| 230145 |   252176 |     326042 |       8031 |           8031 |  0.00000 |
|  75450 |    93321 |     120549 |       7850 |           7850 |  0.00000 |
| 286185 |   310978 |     400611 |       7786 |           7785 |  0.01285 |
|  11875 |    26979 |      25085 |       7474 |           7473 |  0.01338 |
| 310028 |   336610 |     429015 |       7446 |           7445 |  0.01343 |
| 349645 |   381691 |     474681 |       7237 |           7237 |  0.00000 |
|  22912 |    38016 |      46920 |       7147 |           7146 |  0.01399 |
|  10438 |    25542 |      22309 |       7031 |           7031 |  0.00000 |
| 384945 |   427287 |     496695 |       6732 |           6733 | -0.01485 |
|  73640 |    91426 |     118801 |       6697 |           6696 |  0.01493 |
| 269880 |   293821 |     380284 |       6496 |           6496 |  0.00000 |
|  29278 |    44382 |      59764 |       6248 |           6247 |  0.01601 |
|   5163 |    20267 |      10897 |       6242 |           6243 | -0.01602 |
| 244776 |   267458 |     346720 |       6168 |           6168 |  0.00000 |
|  21501 |    36605 |      44334 |       6146 |           6145 |  0.01627 |
| 336168 |   365570 |     460800 |       6101 |           6101 |  0.00000 |
|  61264 |    78621 |     102875 |       6080 |           6080 |  0.00000 |
| 253831 |   276962 |     359200 |       5894 |           5894 |  0.00000 |
| 409726 |   459460 |          0 |       5582 |           5582 |  0.00000 |
| 254693 |   277889 |     359544 |       5569 |           5568 |  0.01796 |
| 272623 |   296690 |     383666 |       5566 |           5566 |  0.00000 |
| 257519 |   280842 |     364132 |       5564 |           5563 |  0.01798 |
| 106975 |   125478 |     164049 |       5536 |           5536 |  0.00000 |
|  25700 |    40804 |      52817 |       5411 |           5411 |  0.00000 |
| 167630 |   187744 |     242722 |       5373 |           5378 | -0.09297 |
| 238354 |   260788 |     337483 |       5348 |           5348 |  0.00000 |
| 353213 |   385827 |     476777 |       5230 |           5230 |  0.00000 |
| 443328 |   502266 |          0 |       5139 |           5139 |  0.00000 |
|  74803 |    92653 |     119762 |       5111 |           5112 | -0.01956 |
| 156560 |   176347 |     225714 |       4929 |           4929 |  0.00000 |
+--------+----------+------------+------------+----------------+----------+


Movers:

mysql> select id, addon_id, persona_id, movers, movers_tmp, movers*100.0/movers_tmp-100 as diff from personas order by movers desc limit 100;
+--------+----------+------------+---------------------+-------------------+----------------------------+
| id     | addon_id | persona_id | movers              | movers_tmp        | diff                       |
+--------+----------+------------+---------------------+-------------------+----------------------------+
| 197696 |   218581 |     280945 |  240.85034013605443 |  241.285714285714 |        -0.1804392568156885 |
| 478652 |   541534 |          0 |  110.57142857142857 |          111.2857 |        -0.6418357691701999 |
| 478458 |   541322 |          0 |  15.622119815668205 |  15.6451612903226 |       -0.14727540500747693 |
| 478520 |   541392 |          0 |   11.19047619047619 |  11.2721095238095 |        -0.7242063533971077 |
|  40537 |    56862 |      76850 |   8.012860817376392 |  8.01114079777184 |        0.02147034545978954 |
|  40546 |    56871 |      76860 |    7.00796812749004 |  7.00797052004529 |     -0.0000341404868038353 |
|  79207 |    97220 |     127966 |   4.915546218487394 |  4.92563029411765 |       -0.20472660407133958 |
| 477824 |   540612 |          0 |   4.600191754554171 |   4.6155322147651 |        -0.3323660088831133 |
|  40540 |    56865 |      76853 |   4.159718462371413 |  4.16026061076242 |       -0.01303159685730293 |
| 478132 |   540974 |          0 |  4.1542124542124546 |   4.1582417948718 |       -0.09690010485476819 |
| 468022 |   529154 |          0 |     4.0257328990228 |  4.02573217821952 |     0.00001790489899633485 |
|  42013 |    58544 |      78593 |  3.6464949071300174 |  3.64649417058188 |     0.00002019880200521129 |
| 478014 |   540840 |          0 |   3.410446306762814 |  3.42369904502046 |       -0.38708829495165276 |
| 430574 |   486394 |          0 |  3.0866171003717473 |  3.09219338632689 |        -0.1803343212555859 |
|  43190 |    59922 |      80002 |   2.881161377447671 |  2.88116210414073 |   -0.000025222220500609183 |
| 477988 |   540810 |          0 |  2.6700680272108843 |  2.67006785714286 |    0.000006369427040908704 |
| 477252 |   539982 |          0 |   2.637865748709122 |  2.64612722891566 |       -0.31221024130134367 |
| 478170 |   541014 |          0 |  2.5213219616204694 |  2.52131901394103 |     0.00011691021337867369 |
| 478080 |   540912 |          0 |   2.463203463203463 |  2.47902411126887 |         -0.638180483743227 |
| 478060 |   540892 |          0 |  2.4113424264178036 |  2.42857185929648 |        -0.7094471103551143 |
|  41999 |    58530 |      78575 |   2.400325467860049 |  2.40032602120847 |   -0.000023053052629506965 |
| 478110 |   540946 |          0 |  2.3016759776536317 |   2.3074226041813 |       -0.24904958967007929 |
| 478154 |   540998 |          0 |  2.1331025802391443 |  2.14348725627473 |        -0.4844757534800408 |
| 473180 |   534992 |          0 |  2.0862904486478038 |  2.09049659841394 |       -0.20120337767244223 |
| 478064 |   540896 |          0 |   2.071638039379975 |  2.07980792959061 |        -0.3928194567583603 |
| 476390 |   538930 |          0 |  2.0149616944569626 |  2.12810903435991 |         -5.316801821527889 |
| 409418 |   459011 |          0 |  1.9866946778711487 |  1.98669460784314 |   0.0000035248502001650195 |
| 161352 |   181306 |     231763 |  1.9641802067946825 |  1.96418057210272 |    -0.00001859849561469673 |
| 478206 |   541054 |          0 |  1.9395393474088296 |  1.94329511569558 |       -0.19326803512321078 |
| 477474 |   540226 |          0 |  1.9236251113507434 |  1.92587548651541 |       -0.11684946303242327 |
| 476606 |   539210 |          0 |  1.7679901548027723 |  1.76908056897966 |       -0.06163733840097052 |
| 473570 |   535470 |          0 |  1.7278266756939744 |  1.72588784508753 |         0.1123381575438458 |
| 477148 |   539866 |          0 |  1.7135267674703718 |  1.72146661217818 |       -0.46122559982514133 |
|  26256 |    41360 |      53921 |  1.4417989417989416 |   1.4417987654321 |    0.000012232417304858245 |
| 425012 |   479202 |          0 |  1.4153005464480874 |  1.41530039177045 |    0.000010928961685863214 |
| 468452 |   529642 |          0 |  1.3696915511846222 |  1.36729272581574 |        0.17544343823311692 |
| 477506 |   540260 |          0 |   1.292895614260067 |   1.2928959759052 |    -0.00002797171154611533 |
|  29352 |    44456 |      59916 |   1.186049649217485 |  1.18604945029415 |    0.000016771925899661255 |
| 475726 |   538126 |          0 |  1.1851001891162534 |  1.18571425200085 |      -0.051788437522816366 |
| 477480 |   540232 |          0 |  1.1558777357096688 |    1.159889444904 |        -0.3458699630345592 |
| 477164 |   539882 |          0 |  1.1320006892986385 |   1.1444082026538 |        -1.0841859859436056 |
|  43641 |    60418 |      80544 |   1.098993288590604 |  1.09899333884172 |    -0.00000457246774487885 |
| 201852 |   222931 |     288141 |  1.0927903871829105 |  1.09279116485568 |     -0.0000711638961377048 |
| 477476 |   540228 |          0 |  1.0747239045244033 |  1.08289199644076 |        -0.7542850019395786 |
|  16390 |    31494 |      34078 |  1.0736114802019663 |  1.07293726002892 |        0.06283873234377779 |
| 404972 |   453068 |          0 |  1.0574886535552193 |  1.05748896917299 |   -0.000029845963410934928 |
| 476420 |   538970 |          0 |  1.0437294664098786 |  1.23092599049128 |        -15.207780608051706 |
|  30445 |    45600 |      62433 |  1.0373269114990966 |  1.03732843459527 |     -0.0001468287306778393 |
|  63426 |    80872 |     105617 |  1.0255941499085923 |  1.02559415224809 | -0.00000022811144617662649 |
| 476832 |   539486 |          0 |  0.9923178004649753 | 0.993936331480546 |       -0.16284051244608122 |
| 477528 |   540286 |          0 |  0.9658385093167703 | 0.969355625657203 |       -0.36283034289382954 |
|  23310 |    38414 |      47732 |  0.9563591022443888 | 0.956358757470448 |     0.00003605069102263769 |
| 431450 |   487502 |          0 |  0.9422150882825041 | 0.942214606741573 |     0.00005110735150992696 |
| 475958 |   538398 |          0 |  0.9143387553041018 | 0.915767490428124 |       -0.15601505174139163 |
|  16709 |    31813 |      34674 |  0.8937443879078119 | 0.893968707425269 |       -0.02509254693077878 |
| 475732 |   538132 |          0 |  0.8925634160904484 | 0.893853682903817 |       -0.14434877184562822 |
| 476646 |   539254 |          0 |  0.8598702376717318 | 0.861630942501482 |        -0.2043455896138795 |
| 317018 |   344269 |     437588 |  0.8296025582457743 | 0.829602611712992 |  -0.0000064449191654603055 |
| 203893 |   225034 |     290482 |  0.8160880027500861 | 0.816088282302391 |    -0.00003425515487265329 |
| 316990 |   344241 |     437556 |  0.8016336056009336 | 0.801632951777608 |     0.00008156143333337695 |
| 476042 |   538484 |          0 |  0.7083076990085642 |  0.70881426655515 |        -0.0714668948535433 |
| 145374 |   165123 |     209572 |  0.7058680892337537 | 0.705868109915957 |  -0.0000029300379225105644 |
| 139324 |   158377 |     201492 |  0.6639175257731957 | 0.663917759676886 |   -0.000035230822916787474 |
| 231003 |   253115 |     328407 |   0.651468788249694 | 0.650757166166001 |        0.10935293849864536 |
| 195250 |   216064 |     278676 |  0.6428571428571428 | 0.642857692307692 |    -0.00008547001237957375 |
|  27048 |    42152 |      55427 |  0.6387096774193548 | 0.638709651520405 |    0.000004054886233006982 |
|  43420 |    60179 |      80276 |  0.6013642912873588 | 0.601349999650572 |       0.002376592133543909 |
| 459354 |   519478 |          0 |  0.5951107715813598 | 0.595110001307486 |     0.00012943386468577955 |
| 477460 |   540212 |          0 |  0.5744305138619105 | 0.584495601422052 |        -1.7220125413525267 |
|  32144 |    47645 |      66280 |  0.5708795900939369 | 0.570879638845401 |    -0.00000853970975356333 |
| 132181 |   151056 |     192363 |  0.5686440677966101 | 0.568643275998612 |     0.00013924335900128426 |
| 304657 |   330819 |     422723 |  0.5679595827900913 | 0.567959601698207 |   -0.000003329130393581181 |
| 476570 |   539170 |          0 |  0.5658163265306123 | 0.568935447753705 |        -0.5482381587239189 |
| 221690 |   243436 |     314192 |  0.5624187256176852 | 0.562418331442241 |     0.00007008581019363191 |
| 114814 |   133403 |     175128 |  0.5505230926256697 | 0.550523111118613 |  -0.0000033591583985526086 |
| 477450 |   540202 |          0 |  0.5281010151027481 | 0.534538301559792 |        -1.2042703840416635 |
| 474140 |   536112 |          0 |  0.5203830010493179 | 0.520343106322052 |         0.0076670040942588 |
| 181285 |   201737 |     258463 |  0.4790514983997674 | 0.479051779944173 |    -0.00005877118451280694 |
| 476586 |   539186 |          0 | 0.47203169347209095 | 0.480014279801325 |        -1.6629893453457214 |
| 476008 |   538448 |          0 | 0.45544016423137323 | 0.455440290667855 |   -0.000027761373857515537 |
| 133171 |   152084 |     193667 | 0.43838254172015395 | 0.438382272662942 |     0.00006137502101921655 |
| 476064 |   538510 |          0 | 0.41969887076536994 | 0.420237108578993 |        -0.1280795538126256 |
| 222044 |   243812 |     314709 | 0.41958283054645756 | 0.419582961220773 |    -0.00003114385651770135 |
| 380124 |   420440 |     493492 | 0.41137675185490535 | 0.411376703328665 |     0.00001179605942525086 |
|  15989 |    31093 |      33217 |  0.4039321303528144 | 0.403932209080145 |   -0.000019490233455599082 |
|   9400 |    24504 |      20217 |  0.3946007316765485 | 0.394600772181133 |   -0.000010264699739082062 |
|  57404 |    74584 |      98549 | 0.39028475711892785 | 0.390285241253809 |    -0.00012404642296814927 |
|  45699 |    62544 |      81807 |  0.3850085178875639 | 0.385008429516275 |    0.000022953078982368424 |
| 474410 |   536440 |          0 | 0.37606504260170426 | 0.376155033072858 |       -0.02392377164773052 |
| 125576 |   144281 |     184387 | 0.37137625877326835 |   0.3713764261674 |   -0.000045073978824916594 |
|  29582 |    44686 |      60440 | 0.36248698497694487 | 0.362270715297266 |         0.0596983610727051 |
|  40133 |    56385 |      76371 |  0.3613224107665302 | 0.361322275053314 |     0.00003756015767919507 |
| 415156 |   466578 |          0 |  0.3594771241830065 | 0.359477275057392 |    -0.00004197049327103741 |
|  32996 |    48513 |      65509 | 0.35291873330789775 | 0.352918777633358 |    -0.00001255967747226805 |
| 419954 |   472923 |          0 |  0.3439999999999999 | 0.344000452715942 |     -0.0001316032983424975 |
|  30507 |    45671 |      62655 |  0.3428943937418514 | 0.342895135044364 |    -0.00021618927679867284 |
|  15347 |    30451 |      31916 | 0.33753148614609574 | 0.337531864773022 |    -0.00011217516501460523 |
|  60427 |    77766 |     102206 |  0.3362369337979094 | 0.336236991869919 |    -0.00001727115427740955 |
|  56262 |    73394 |      96602 | 0.33491364713850325 | 0.334914200783678 |     -0.0001653095549443151 |
|  29094 |    44198 |      59422 |  0.3281853281853281 | 0.328185585585585 |    -0.00007843131089657618 |
+--------+----------+------------+---------------------+-------------------+----------------------------+

Mathieu Agopian [:magopian]

Comment 30

•

10 years ago

It's hard to read from the previous comment, but numbers are almost perfect on those two queries: numbers are within less than 1% of difference but for one exception for the movers:

The biggest difference (for movers) is:

+--------+----------+------------+---------------------+-------------------+--------------------+
| id     | addon_id | persona_id | movers              | movers_tmp        | diff               |
+--------+----------+------------+---------------------+-------------------+--------------------+
| 476390 |   538930 |          0 |  2.0149616944569626 |  2.12810903435991 | -5.316801821527889

Sean Rich [:srich]

Comment 31

•

10 years ago

And this one? Or no?
+--------+----------+------------+---------------------+-------------------+--------------------+
| id     | addon_id | persona_id | movers              | movers_tmp        | diff               |
+--------+----------+------------+---------------------+-------------------+--------------------+
| 476420 |   538970 |          0 |  1.0437294664098786 |  1.23092599049128 | -15.207780608051706

Mathieu Agopian [:magopian]

Comment 32

•

10 years ago

Ah, yes indeed, very well spotted, missed that one. Ricardo, any thoughts?

Flags: needinfo?(rfradinho)

Ricardo Fradinho [:rfradinho]

Comment 33

•

10 years ago

@Mathieu, the numbers are perfect :)
The numbers for "diff" for the 2 addons mentioned above is ok and happen because we're comparing low values (eg 2.01% vs 2.12%).
Let's leave the scripts running for a few days and check every other day. If nothing comes up, we can close this.

Flags: needinfo?(rfradinho)

Wil Clouser [:clouserw]

Reporter

Comment 34

•

10 years ago

It's been a few days.  Can we close this?

Mathieu Agopian [:magopian]

Comment 35

•

10 years ago

I believe we can

Status: NEW → RESOLVED

Closed: 10 years ago

Resolution: --- → FIXED

Nobody; OK to take it and work on it

Assignee

Updated

•

8 years ago

Product: addons.mozilla.org → addons.mozilla.org Graveyard

Script to backfill the last 21 days of LWT update counts 10 years ago Mathieu Agopian [:magopian] 423 bytes, text/x-python		Details
two_little_birds.example.txt 10 years ago Ricardo Fradinho [:rfradinho] 1.03 KB, text/plain		Details
Script to backfill the last 28 days of LWT update counts 10 years ago Mathieu Agopian [:magopian] 423 bytes, text/x-python		Details