Implement a python script to dump alexa top sites to db

[MDTsai]

This is a part of #1533 , I decide to cache alexa top sites in a sqlite db, not query when new issue opened.
Assume the ranking doesn't change that frequently, we manually launch the script to create a new db, then deploy to production server.
The plan is to have a top site table with 4 columns: url (primary key), priority (from 1 to 3), country_code and ranking. We use url and priority for label. Country_code and ranking are used to left a comment when a new issue created, let contributor know it's important because it's top N in which country.
I use the alexa top slite to query:

1. global top 10000 sites
2. tier 1 top 1000 sites (11 regions, total 11000 sites)

Priority is defined in #1533 (comment) . If the url doesn't exist, then add a record. If the url exists but priority is higher (like global 9000 but top 52 in Taiwan), update the priority.

[MDTsai]

WIP implementation https://github.com/MDTsai/webcompat.com/tree/Issue_1579

[MDTsai]

We query 21000 sites with 52.5 USD, only 13221 unique URL we have finally.
As @softvision-sergiulogigan question, we have update 366 URLs' priority to higher (low in global but high in country)
topsites.zip

I will try to use the data and map to current issues we have to and give use a idea about how many issues in what priority. @zoepage Do you think this is ok?

[miketaylr]

will try to use the data and map to current issues we have to and give use a idea about how many issues in what priority. @zoepage Do you think this is ok?

👍

[MDTsai]

I try to use github3.py and form+helpers from webcompat to get domain_name from issue body (**URL:**).
Now I need to solve the problem that domain name from URL doesn't match we have from alexa. Two ideas:

1. Query from alexa, give "www.bing.com" receives 301 redirect to "bing.com". alexa site info API provides the same result.
2. Reduce the domain name length, but at least a second-level domain+top-level domain. Use reduced name to lookup in DB. I prefer this idea because we have limited URL in DB.

[zoepage]

will try to use the data and map to current issues we have to and give use a idea about how many issues in what priority. @zoepage Do you think this is ok?

👍

[karlcow]

@MDTsai just an idea for checking matching of domain names. Still early here so maybe it's a bad idea. :)

Trying to match in reverse order.

>>> domain1 = 'www.mozilla.org'
>>> alexa_list = ['mozilla.org', 'mozilla.com']
>>> domain1.split('.')
['www', 'mozilla', 'org']
>>> domain1.split('.')[-1]
'org'
>>> last_part = domain1.split('.')[-1]
>>> # this filter extract all the domain from the alexa_list 
>>> # which are matching a string finishing with last_part
>>> filter(lambda x: x.endswith(last_part), alexa_list)
['mozilla.org']

Trying to match org, then mozilla, etc.

[karlcow]

Ah useful too.
https://en.wikipedia.org/wiki/Reverse_domain_name_notation#Python

[MDTsai]

Using the DB to analysis current web-bugs issues (7348), we have:

• Critical: 1325
• Important: 981
• Normal: 905

If we use the ranking, that means:

• 43.7% of issues are important in global or tier 1 countries.
• Global top 100 sites occupy ~18% (but I guess Oana and Serigu contribute a lot google issues). That make sense, huge website has more problem then small one.

You can visit here to see the result and give me some feedback. Then I can adjust then priority from ranking or do some more thing?
@karlcow @zoepage @softvision-sergiulogigan @softvision-oana-arbuzov @miketaylr @adamopenweb

[miketaylr]

Very cool, @MDTsai!

The only thing that might feel a bit odd is a site like fox.com (ranking 1125 in the US) sitting in the same "normal" bucket (I guess rank 3) as makeuseof.com (872 in US).

But any system won't be perfect, and it's better than what we have right now. 👍

[MDTsai]

@miketaylr:
fox.com is 5067th in global and 1129th in US. So we only use the global ranking to priority 3.
makeuseof.com is 1267th in global and 862th in US. These 2 are the same priority also 3. So I will keep on next steps. :)

[zoepage]

@MDTsai The list looks really good! Thanks! :)