Jump to content

Research talk:Modeling monthly active editors/Work log/2014-06-24

Add topic
From Meta, a Wikimedia project coordination wiki

Tuesday, June 24th

[edit]

I just replicated my analysis for enwiki, but this time I filtered for users who registered using the mobile interface.

First, the monthly editor counts that go into my MAE script:

SELECT
    em.*,
    lui.attached_method
FROM editor_month em
INNER JOIN local_user_info lui USING (wiki, user_id)
LEFT JOIN log.ServerSideAccountCreation_5487345 ssac ON
    em.wiki = ssac.wiki AND
    event_userId = user_id
WHERE 
    em.wiki IN ("enwiki") AND 
    event_displayMobile = 1
ORDER BY em.wiki, month;

Now, to gather the count of mobile registrations:

SELECT
    lui.wiki,
    LEFT(user_registration, 6) AS month,
    COUNT(*) AS registrations
FROM local_user_info lui
LEFT JOIN log.ServerSideAccountCreation_5487345 ssac ON
    lui.wiki = ssac.wiki AND
    event_userId = user_id
WHERE 
    lui.wiki IN ('enwiki') AND
    user_registration IS NOT NULL AND
    attached_method != "login" AND
    event_displayMobile = 1
GROUP BY 1,2;

Now, the baselines. Note that I'm only working with 11 months since mobile was only partially active in June, 2013

> summary(
+ merged_mae[month_date >= "2013-07-01" & month_date < "2014-05-01",
+ list(
+ mae = total_active_editors,
+ nae = new_active_editors,
+ nae_rate = new_editor_activation_rate,
+ snae = surviving_new_active_editors,
+ snae_rate = new_active_survival_rate,
+ roae = old_active_editors,
+ roae_rate = old_active_survival_rate,
+ rae = reactivated_editors,
+ inactivated = inactivated_editors,
+ inactivated_rate = inactivation_rate
+ ),
+ ]
+ )
      mae              nae            nae_rate            snae      
 Min.   : 231.0   Min.   : 212.0   Min.   :0.01560   Min.   : 4.00  
 1st Qu.: 887.8   1st Qu.: 730.0   1st Qu.:0.02385   1st Qu.:52.75  
 Median :1041.0   Median : 796.5   Median :0.02547   Median :64.50  
 Mean   :1039.5   Mean   : 786.0   Mean   :0.02591   Mean   :57.50  
 3rd Qu.:1322.8   3rd Qu.: 945.2   3rd Qu.:0.02962   3rd Qu.:76.00  
 Max.   :1407.0   Max.   :1043.0   Max.   :0.03137   Max.   :80.00  
                                                                    
   snae_rate            roae         roae_rate           rae        
 Min.   :0.06732   Min.   :  0.0   Min.   :0.2000   Min.   : 15.00  
 1st Qu.:0.07713   1st Qu.: 24.0   1st Qu.:0.2647   1st Qu.: 88.25  
 Median :0.08408   Median : 73.5   Median :0.2772   Median :137.50  
 Mean   :0.08641   Mean   : 61.9   Mean   :0.2740   Mean   :134.10  
 3rd Qu.:0.09190   3rd Qu.: 83.5   3rd Qu.:0.2955   3rd Qu.:192.25  
 Max.   :0.12736   Max.   :124.0   Max.   :0.3361   Max.   :228.00  
                                   NA's   :1                        
  inactivated     inactivated_rate
 Min.   :  47.0   Min.   :0.8460  
 1st Qu.: 756.8   1st Qu.:0.8573  
 Median : 835.0   Median :0.8762  
 Mean   : 784.5   Mean   :0.8739  
 3rd Qu.:1039.2   3rd Qu.:0.8789  
 Max.   :1186.0   Max.   :0.9216  
                                  
> summary(
+ merged_mae[month_date >= "2013-07-01" & month_date < "2014-05-01",
+ list(
+ registrations,
+ nae_prop = new_active_editors / total_active_editors,
+ snae_prop = surviving_new_active_editors / total_active_editors,
+ roae_prop = old_active_editors / total_active_editors,
+ rae_prop = reactivated_editors / total_active_editors
+ )
+ ]
+ )
 registrations      nae_prop        snae_prop         roae_prop      
 Min.   :13587   Min.   :0.6967   Min.   :0.01732   Min.   :0.00000  
 1st Qu.:29674   1st Qu.:0.7185   1st Qu.:0.04408   1st Qu.:0.02646  
 Median :31238   Median :0.7603   Median :0.05546   Median :0.06398  
 Mean   :29589   Mean   :0.7763   Mean   :0.05272   Mean   :0.05113  
 3rd Qu.:31893   3rd Qu.:0.7953   3rd Qu.:0.06292   3rd Qu.:0.07199  
 Max.   :35098   Max.   :0.9177   Max.   :0.08498   Max.   :0.08813  
    rae_prop      
 Min.   :0.06272  
 1st Qu.:0.09903  
 Median :0.12726  
 Mean   :0.11988  
 3rd Qu.:0.14036  
 Max.   :0.16680

Alright, let's check these out over time.

month_date registrations mae nae nae_rate snae snae_rate roae roae_rate rae inactivated inactivated_rate
2013-06-01 4341 51 51 0.01174845 0 NA 0 NA 0 0 NA
2013-07-01 13587 231 212 0.01560315 4 0.07843137 0 NA 15 47 0.9215686
2013-08-01 29564 861 776 0.02624814 27 0.1273585 4 0.2105263 54 200 0.8658009
2013-09-01 27894 859 689 0.02470065 73 0.09407216 17 0.2 80 771 0.8954704
2013-10-01 31535 968 748 0.02371968 62 0.08998549 45 0.2647059 113 752 0.8754366
2013-11-01 31558 968 724 0.02294188 52 0.06951872 65 0.2954545 127 851 0.8791322
2013-12-01 33707 1114 817 0.02423829 67 0.09254144 82 0.3360656 148 819 0.8460744
2014-01-01 35098 1346 1043 0.02971679 55 0.06731946 82 0.2760943 166 977 0.8770197
2014-02-01 30002 1253 880 0.02933138 80 0.07670182 84 0.2772277 209 1182 0.8781575
2014-03-01 30940 1388 967 0.03125404 77 0.0875 116 0.310992 228 1060 0.8459697
2014-04-01 32005 1407 1004 0.0313701 78 0.08066184 124 0.2945368 201 1186 0.8544669
2014-05-01 36624 2092 1555 0.0424585 86 0.08565737 134 0.3325062 317 1187 0.8436389

That's all, folks. --Halfak (WMF) (talk) 00:24, 25 June 2014 (UTC)Reply


J/k Posting a little more from the next morning:

The number of monthly active editors is stacked by four classes over time for mobile editors on English Wikipedia.
Stacked AE population. The number of monthly active editors is stacked by four classes over time for mobile editors on English Wikipedia.
The survival and activation rates of three classes of editors is plotted over time for mobile editors on English Wikipedia.
AE rates. The survival and activation rates of three classes of editors is plotted over time for mobile editors on English Wikipedia.
Monthly active editors are plotted by four editor classes for enwiki with loess fits overlaid over time for mobile editors on English Wikipedia.
AE population trends. Monthly active editors are plotted by four editor classes for enwiki with loess fits overlaid over time for mobile editors on English Wikipedia.

--Halfak (WMF) (talk) 14:27, 25 June 2014 (UTC)Reply