629159 - Up & Coming Formula

Status: RESOLVED FIXED

(addons.mozilla.org Graveyard :: Administration, defect, P3)

Description

[Justin Scott [:fligtar]]

As discussed in meatspace, we need a formula for ranking up and coming add-ons, aka "hot this week" to replace Top Add-ons in the discovery pane.

The most obvious calculation of percent increase in ADU this week heavily favors brand new add-ons that go from 1 ADU to 10, so we need something better that identifies the rising stars in the last 7 days.

Comment 1

[Dave Dash [:davedash, :dd] (assign all bugs to mbrandt)]

You can make a "normalized" top 10 list, but taking the top downloads this week - the top downloads of all time.

That would get you the top downloads this week that aren't always popular or expected to be there like ABP.

Comment 2

[Justin Scott [:fligtar]]

I asked metrics what we do for Personas:
---
Here's the MDX query we use (don't worry, I'll translate :)  ):


week as '([Dates].CurrentMember.Lag(6.0) : [Dates].CurrentMember)'
set lastweek as '([Dates].CurrentMember.Lag(27.0) : [Dates].CurrentMember.Lag(7.0))'
member [Measures].[ThisWeek] as 'Avg([thisweek], [Measures].[Requests])'
member [Measures].[Last3Weeks] as 'Avg([lastweek], [Measures].[Requests])'
member [Measures].[Evolution] as '( ( [Measures].[ThisWeek] - [Measures].[Last3Weeks] ) / [Measures].[Last3Weeks])', FORMAT_STRING = "##.00%"
member [Measures].[NAME]  as [Personas].CurrentMember.Name
select {[Measures].[Evolution],[Measures].[NAME]} ON COLUMNS,
Filter([Personas].Children, (([Measures].[ThisWeek] > 100.0) AND ([Measures].[Last3Weeks] > 1.0)) AND [Personas].CurrentMember.Name <> '#null')
 ON ROWS
from [Lightweight Themes]
where ([Dates].[Date].[${TODAY}])

So basically we see personas with:

- Last week's average over 100 users (to filter out residual users)
- In the 3 weeks before, had to have an average >1 (to filter recently created personas)

We then divide this 2 numbers and order them.


Hope that's what you're looking for, just ping me for any questions.


(this is almost impossible to do with a single sql query, btw, if we have the data in our DW we can do this mdx query for you and update the numbers) 


----

I took a crack at a query and it gives some interesting results. We might be able to use this with a few tweaks.

mysql> select addons.id, addons.slug, (thisweek - threeweek) / threeweek as evolution, thisweek, threeweek from addons inner join (select addon_id, avg(count) as thisweek from update_counts where date_sub(curdate(), interval 7 day) <= date group by addon_id) as a on a.addon_id=addons.id inner join (select addon_id, avg(count) as threeweek from update_counts where date_sub(curdate(), interval 28 day) <= date and date_sub(curdate(), interval 7 day) > date group by addon_id) as b on b.addon_id=addons.id where threeweek > 1 and thisweek > 100 and addons.addontype_id=1 order by evolution desc limit 100;
+--------+--------------------------------+--------------+------------+------------+
| id     | slug                           | evolution    | thisweek   | threeweek  |
+--------+--------------------------------+--------------+------------+------------+
| 277025 | antiprotetor                   | 894.37500000 |  3581.5000 |     4.0000 | 
| 276760 | video-myxa                     |  34.06048387 |   217.3750 |     6.2000 | 
| 276302 | tabmyposition                  |  10.91666667 |   143.0000 |    12.0000 | 
| 266859 | facebookgamer                  |   6.58776565 |   452.3750 |    59.6190 | 
|  10578 | bw-checker                     |   6.36318671 |   111.5000 |    15.1429 | 
| 270899 | auctionsensor-ebay-deal-search |   5.17562494 |   106.7500 |    17.2857 | 
| 274097 | google-shortener               |   3.53789856 |   179.6250 |    39.5833 | 
| 265038 | tapsure                        |   3.36287478 |   539.7500 |   123.7143 | 
| 269246 | https-facebook                 |   3.11666667 |   185.2500 |    45.0000 | 
| 262817 | youtube-mp3-podcaster          |   3.06534012 |  3382.7500 |   832.0952 | 
| 271182 | opendownload-271182            |   2.86195904 |   204.5000 |    52.9524 | 
| 274096 | change-panorama-shortcut       |   2.84153208 |   147.3750 |    38.3636 | 
| 271045 | home-links                     |   2.74737395 |   713.5000 |   190.4000 | 
|  74382 | vividas-video-plugin           |   2.64578418 |  2059.0000 |   564.7619 | 
| 244867 | utorrent-status-tool           |   2.35315338 |  1079.8750 |   322.0476 | 
| 274934 | imagefit                       |   2.30203396 |   208.5000 |    63.1429 | 
| 268255 | tabcloud                       |   2.14078177 |   315.1250 |   100.3333 | 
| 270435 | personas                       |   1.93280483 |  2307.0000 |   786.6190 | 
| 270427 | piccsycom-save-images-to-piccs |   1.89869813 |   178.1250 |    61.4500 | 
| 270426 | shotpix-image-upload           |   1.79897906 |   136.7500 |    48.8571 | 
| 268878 | adban                          |   1.79821872 |   432.1250 |   154.4286 | 
| 262534 | leftsidestar                   |   1.67379157 |   137.0000 |    51.2381 | 
| 271055 | page-eaters                    |   1.57134348 |   213.2500 |    82.9333 | 
| 275536 | easy-dragtogo-1                |   1.50986842 |   286.1250 |   114.0000 | 
| 271830 | savefromnet-helper             |   1.49203147 |  9353.2500 |  3753.2632 | 
| 264087 | friendly-bug                   |   1.48927568 |  1043.1250 |   419.0476 | 
| 256990 | play-your-piano                |   1.48730095 |  1354.7500 |   544.6667 | 
| 277001 | prospector-home-dash           |   1.35188146 |  5515.7500 |  2345.2500 | 
| 204931 | paste-and-go-204931            |   1.17325949 |   343.3750 |   158.0000 | 
| 226339 | eve-status-quick-view          |   1.16932647 |   375.5000 |   173.0952 | 
| 200751 | urim                           |   1.00576062 |   296.3750 |   147.7619 | 
| 269705 | facebook-runner                |   1.00171233 |   292.2500 |   146.0000 | 
| 269534 | good-website-inspector         |   0.96104650 |   159.1250 |    81.1429 | 
| 262804 | autofill-262804                |   0.96047833 |  1844.2500 |   940.7143 | 
| 161860 | clip-it                        |   0.93068896 |   209.2500 |   108.3810 | 
| 261467 | cookie-monster-261467          |   0.91396901 |   374.5000 |   195.6667 | 
| 262806 | random-video-chat              |   0.91152825 |   133.6250 |    69.9048 | 
| 274103 | image-download-?               |   0.87513383 |   319.6250 |   170.4545 | 
| 214784 | fb-plus                        |   0.86132434 |  2795.0000 |  1501.6190 | 
| 266856 | wonderpage-extension           |   0.83463971 |   192.3750 |   104.8571 | 
| 265063 | easy-app-tabs                  |   0.81854641 |   239.8750 |   131.9048 | 
|  67650 | youtube-enabler                |   0.81165479 |   178.7500 |    98.6667 | 
| 262835 | none-262835-1                  |   0.78431346 |   169.0000 |    94.7143 | 
|  12765 | universal-behavioral-advertisi |   0.78108962 |  1090.8750 |   612.4762 | 
| 264490 | people-lookup                  |   0.77120079 |   550.0000 |   310.5238 | 
| 262812 | tabble-keep-tabs-on-people-and |   0.75635144 |   253.5000 |   144.3333 | 
| 212319 | fastesttube-youtube-video-down |   0.73239314 | 13496.2500 |  7790.5238 | 
| 265058 | thundersync                    |   0.70849520 |   209.2500 |   122.4762 | 
|  10023 | google-reader-new-style-minima |   0.70617710 |   144.3750 |    84.6190 | 
|  10420 | gmail-favicon-notification     |   0.70037394 |   752.3750 |   442.4762 | 
| 249361 | memory-restart                 |   0.67870941 |  2907.1250 |  1731.7619 | 
| 269960 | magazinify                     |   0.67836956 |   113.2500 |    67.4762 | 
| 247253 | loudtronix-audio-video-downloa |   0.66750479 |  1413.2500 |   847.5238 | 
|   8743 | url-fixer-plus-ru-and-ua       |   0.66600310 |  9275.7500 |  5567.6667 | 
| 126519 | suppress-security-warning      |   0.64613699 |   210.0000 |   127.5714 | 
| 155487 | add-to-fashiolista             |   0.64223939 |  1494.1250 |   909.8095 | 
| 195789 | censureblock                   |   0.63208284 | 11684.6250 |  7159.3333 | 
| 265062 | tunetunenet-converter          |   0.62200660 |   148.3750 |    91.4762 | 
| 161772 | vkshare                        |   0.60085462 |   359.1250 |   224.3333 | 
| 265083 | noface                         |   0.58567786 |   111.3750 |    70.2381 | 
| 269704 | die2nite-map-tool-updater      |   0.57719357 |   737.0000 |   467.2857 | 
|  12651 | obpwd-object-based-password-pa |   0.54730973 |   588.1250 |   380.0952 | 
| 266838 | who-stole-my-pictures          |   0.48396136 |  1639.0000 |  1104.4762 | 
| 215989 | proxy-selector                 |   0.45579638 |   462.2500 |   317.5238 | 
| 259364 | rss-icon-in-awesombar          |   0.44124816 |   691.2500 |   479.6190 | 
| 262821 | opacus-sugarcrm-thunderbird-ex |   0.43640151 |   210.1250 |   146.2857 | 
| 264678 | mozrepl                        |   0.43618634 |   302.6250 |   210.7143 | 
| 268716 | selenium-expert-selenium-ide   |   0.42462335 |   243.0000 |   170.5714 | 
|  12737 | listit                         |   0.41456327 |  1518.5000 |  1073.4762 | 
|  13930 | background-tabs                |   0.41421420 |   162.5000 |   114.9048 | 
| 268258 | statusus                       |   0.41089372 |   200.7500 |   142.2857 | 
| 264676 | webnotes                       |   0.41067891 |   341.2500 |   241.9048 | 
|  47136 | readability-47136              |   0.39573469 |  1979.7500 |  1418.4286 | 
| 262828 | flashresizer                   |   0.38614938 |  1484.5000 |  1070.9524 | 
| 258239 | bookmarks-deiconizer           |   0.38281278 |   228.6250 |   165.3333 | 
| 247486 | illuminations-for-developers   |   0.37879754 |  5759.5000 |  4177.1905 | 
|  11638 | myanmar-converter              |   0.35681911 |   417.1250 |   307.4286 | 
| 255245 | power-debugger-selenium-ide    |   0.35078254 |  1085.0000 |   803.2381 | 
| 264091 | deletebutton                   |   0.34137715 |   206.1250 |   153.6667 | 
| 253996 | ebay-show-missing-images-show- |   0.33515065 |   202.6250 |   151.7619 | 
| 162076 | extension-options-menu         |   0.33424430 |   180.2500 |   135.0952 | 
| 251546 | fantapper                      |   0.32970536 | 18224.6250 | 13705.7619 | 
|  15093 | system-monitor                 |   0.32647845 |   332.1250 |   250.3810 | 
| 265085 | sugestron-speed-dial           |   0.32554055 |  1118.1250 |   843.5238 | 
| 258718 | facebook-friend-request-notifi |   0.32382690 |  4141.7500 |  3128.6190 | 
| 244876 | booly-shopping-price-compariso |   0.32076861 |   229.6250 |   173.8571 | 
| 259585 | eazypaper-zotero-integration   |   0.31865104 |   134.0000 |   101.6190 | 
| 195280 | speechutil                     |   0.31616088 |   359.5000 |   273.1429 | 
| 265081 | tabfind                        |   0.31560737 |   158.7500 |   120.6667 | 
| 161617 | cookie-importer                |   0.31335395 |  3556.6250 |  2708.0476 | 
|  15095 | dictcc-translation             |   0.31301975 |   473.7500 |   360.8095 | 
| 258052 | bypassrdto-v-06                |   0.30001062 |  1014.8750 |   780.6667 | 
|  56938 | free-games                     |   0.29894374 |  2048.1250 |  1576.7619 | 
|   3756 | pokereval                      |   0.29673930 |  3568.7500 |  2752.0952 | 
| 258229 | new-tab-from-location-bar      |   0.29506632 |   162.5000 |   125.4762 | 
| 256994 | img2tab                        |   0.29267163 |   444.1250 |   343.5714 | 
| 257702 | tab-history-redux              |   0.28685260 |   144.2500 |   112.0952 | 
| 261958 | password-categories            |   0.28542324 |   225.5000 |   175.4286 | 
| 259879 | barlesque                      |   0.28482813 |  1091.1250 |   849.2381 | 
| 223377 | search-ip-with-honey-and-utrac |   0.28301291 |   133.2500 |   103.8571 | 
+--------+--------------------------------+--------------+------------+------------+
100 rows in set (8.78 sec)

Comment 3

[Justin Scott [:fligtar]]

Each add-on will have a hotness score that reflects its number of users in the last 7 days compared to the previous 3 weeks. The score for each add-on is calculated by dividing the difference in average active users of last week and previous 3 weeks into the average active users of the previous 3 weeks, like so:

(avg(active users last 7 days) - avg(active users 21 days starting 7 days ago)) / avg(21 days starting 7 days ago)

For example:
Jan. 1 - 7 average ADU: 20
Jan. 8 - 14 average ADU: 25
Jan. 15 - 21 average ADU: 30
Jan. 22 - 28 average ADU: 300
On Jan. 28, the hotness score is (300 - 25 / 300) = .9166 (pretend I'm not taking the average of an average)

BUT:
If the last 7 days average ADU is less than 1000, the score is 0.
If the average of the previous 3 weeks is not at least 1, the score is 0.

Once the score is saved in the addons table, we will order and filter (e.g. only reviewed add-ons) to get Up & Coming in various places.

Comment 4

[Jeff Balogh (:jbalogh)]

https://github.com/jbalogh/zamboni/commit/5cc87ec 

The cron job will run tonight.

Add-ons can be removed from the hotness index by adding them to https://addons.allizom.org/z/en-US/admin/models/addons/frozenaddon/. Their hotness will not immediately be changed, which is probably worth another bug.

Comment 5

[Justin Scott [:fligtar]]

thanks! I changed the minimum ADU from 100 to 1000 in comment #3 and I think you took 100 from the earlier formula.

Comment 6

[Jeff Balogh (:jbalogh)]

(In reply to comment #5)
> thanks! I changed the minimum ADU from 100 to 1000 in comment #3 and I think
> you took 100 from the earlier formula.

https://github.com/jbalogh/zamboni/commit/f56841f