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

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:

Stacked AE population. The number of monthly active editors is stacked by four classes 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.

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]