It's true that we're hoping to re-focus on Limn. It hasn't seen any work since last March. However, I think this issue has little to do with Limn and more with how we're generating data for it. I would love to talk about how we're currently generating this data and how we can improve, I've been thinking a lot about the problem and I think Toby is making solutions to it an upcoming priority. On Wed, Nov 13, 2013 at 8:00 PM, Toby Negrin &lt;tnegrin(a)wikimedia.org <mailto:tnegrin@wikimedia.org>> wrote: Hi all -- Limn is indeed the child of the Analytics team and we are happy to work with you to resolve these issues. Always feel free to reach out to this list if you have any questions/issues. A couple of specific comments -- right now Limn will graph what you tell it to so while we can certainly help optimize the processing, the scripts themselves are custom to the data being graphed. We have hired an engineer to focus on Limn specifically who will start in December. We're in the process of putting together some requirements and use cases for her to work on and I will circulate this broadly once I have incorporated feedback from our quarterly reviews. We'll also reach out to Mobile directly. -Toby On Wed, Nov 13, 2013 at 4:14 PM, Arthur Richards &lt;arichards(a)wikimedia.org <mailto:arichards@wikimedia.org>> wrote: +analytics On Wed, Nov 13, 2013 at 5:09 PM, Jon Robson &lt;jrobson(a)wikimedia.org <mailto:jrobson@wikimedia.org>> wrote: Thanks so much Juliusz for exploring this and great work fixing the schema (apologies for me not predicting that might be an issue) and sorry for all the pain this must have caused you. We can't be the only teams using Limn in the Foundation. It might be worth pulling everyone together. Am I right in thinking that Limn is a child of the analytics team? Maybe we should at least spend some with them getting our use case resolved.. I guess this is why we have an analytics department? I can raise this issue in the next Scrum of Scrums if it is not resolved by then. On Wed, Nov 13, 2013 at 3:54 PM, Juliusz Gonera &lt;jgonera(a)wikimedia.org <mailto:jgonera@wikimedia.org>> wrote: The dashboard and backend scripts data. The issue was addressed in the thing I had to disable edits on enwiki timing out and causing an we'll be using get updated soon. run every hour and example, even change, they are still solution for generating more graphs and performing. on the general working on this. We framework for other teams in the results of this spike the mobile team. http://ganglia.wikimedia.org/latest/?r=month&cs=&ce=&tab=v&… -- Arthur Richards Software Engineer, Mobile [[User:Awjrichards]] IRC: awjr +1-415-839-6885 x6687 <tel:%2B1-415-839-6885%20x6687> _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org <mailto:Analytics@lists.wikimedia.org> https://lists.wikimedia.org/mailman/listinfo/analytics _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org <mailto:Analytics@lists.wikimedia.org> https://lists.wikimedia.org/mailman/listinfo/analytics

Our approaches are hard-wired into our legacy code. So we do not use a common, solid framework for it. I haven't done any research on whether or not such frameworks exist. But if you find some good framework, please let us know, it would certainly be interesting.

Analytics folks, what would you say to setting aside some time to collaborate on this? Maybe pair a mobile web engineer with an analytics engineer for a sprint or two? It would be great if we can get this figured out sooner rather than later since we rely so heavily on the data and its presentation. On Thu, Nov 14, 2013 at 6:40 AM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt;wrote;wrote: -- Arthur Richards Software Engineer, Mobile [[User:Awjrichards]] IRC: awjr +1-415-839-6885 x6687

Waddaya say Toby? Almost the entire mobile web team is traveling this week and next; however Jon Robson is around and may have some time - what do you think Jon? On Thu, Nov 14, 2013 at 1:48 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: -- Arthur Richards Software Engineer, Mobile [[User:Awjrichards]] IRC: awjr +1-415-839-6885 x6687

On Tue, Nov 19, 2013 at 2:07 AM, Matthew Flaschen &lt;mflaschen(a)wikimedia.org Yeah, we refer to that as deletion drift. Dario is heading an effort to make these metrics more standard and intuitive ( https://meta.wikimedia.org/wiki/Research:Refining_the_definition_of_monthly…). We'll have to see what we need for these dashboards and if the new definitions would help.

K, so a quick follow-up on this. Jon and I worked today and identified two short term problems. 1. http://mobile-reportcard.wmflabs.org/graphs/edits-monthly-5plus-editors no longer updates because the query used for it takes too long to finish 2. the scripts run hourly even for graphs that only need to be updated daily For 1, I fiddled with the SQL until it performed a little better. It was also not correct, as I believe it was getting "the number of people who created an account in month X and made >= 5 edits anytime". I changed it to what I assumed we wanted, which is "the number of people who created an account and made >= 5 edits in month X". This new query ( https://gist.github.com/milimetric/7554108) takes 4 minutes to run 3 months' worth. Juliusz, any idea what the timeout is on that job? I'm running the query now for 13 months and if it's < timeout, we can just deploy it. Otherwise, we can maybe run one month at a time and concat results. Let me know what you think and I'll make a Change

This is actually not correct - the query is supposed to be who created an account in month X and made >= 5 edits ever. But, if we can't make that kind of query performant enough... are you ok with this change, Kenan?

That would be good. It is suppose to be in line with the active editor metric. On Tue, Nov 19, 2013 at 7:11 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt;wrote;wrote: -- Kenan Wang Product Manager, Mobile Wikimedia Foundation

On Wed, Nov 20, 2013 at 11:34 AM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: Wait, so it *is* supposed to be like the active editor metric? To be very clear, we have two possible questions we can be answering here, and only one of them is like the active editor metric. Both of these questions will show results for X <- {each of the last 12 months} 1. How many people, of those that created their account on mobile, made at least 5 edits in month X? [THIS IS LIKE ACTIVE EDITOR] 2. How many people created their account on mobile in month X, and made at least 5 edits since then? I've implemented both, but there is a pretty big difference so we need to figure that out before moving on. Dan _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

2) if instead we want to restore data for the mobile dashboards ASAP based on an interim definition (with the understanding that this may change once there is consensus on the canonical one), I recommend we go for Dan’s definition #1 and follow the assumptions described in the Mobile active editor page above (option C). Someone correct me if I am wrong, but I don’t think Dan’s definition #2 is a use case I’ve heard of from the Mobile team (cumulative lifetime 5+ editors for cohorts defined by the month of registration).

Dario and I chatted about this. The original purpose of this dashboard was to be more of an acquisition dashboard. Thus to fit that requirement Dario suggested that we use an activation metric. i.e. How many users in month X reached 5 edits within 30 days. Is that possible? On Wed, Nov 20, 2013 at 12:05 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt;wrote;wrote: -- Kenan Wang Product Manager, Mobile Wikimedia Foundation _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

The metric in WikiMetrics could be used for this, but the cohort would have to be re-generated all the time. I implemented the new logic that Kenan described. The problem is, this query runs crazy slow on the slaves: https://gist.github.com/milimetric/7554108#file-active-editors-from-account… I'm gonna try generating a huge cohort, upload it to wikimetrics and see where I get. On Thu, Nov 21, 2013 at 5:28 PM, Toby Negrin &lt;tnegrin(a)wikimedia.org&gt;wrote;wrote:

Heh, besides the fact that we don't have threshold, this ran on wikimetrics in like 10 seconds: https://metrics.wmflabs.org/reports/result/c06d3de0-060f-4434-9bd1-ba5606e1… Which confirms my suspicion that it's not the mediawiki tables that are problematic, but the cross-db join. On Thu, Nov 21, 2013 at 6:10 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt;wrote;wrote:

Dan here is what I'm looking for: How many users registered on enwiki in month X and reached 5 edits within 30 days I talked with Dario and we're hoping that restricting it to enwiki solves the cross-db join issue that you were facing. On Mon, Nov 25, 2013 at 2:31 PM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: OK let me talk to Dario to make sure but I think we should do: How many people created their account on mobile in month X, and made at least 5 edits in month X + the month after? On Thu, Nov 21, 2013 at 3:14 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: Heh, besides the fact that we don't have threshold, this ran on wikimetrics in like 10 seconds: https://metrics.wmflabs.org/reports/result/c06d3de0-060f-4434-9bd1-ba5606e1… Which confirms my suspicion that it's not the mediawiki tables that are problematic, but the cross-db join. On Thu, Nov 21, 2013 at 6:10 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: OK, so to sum this up: * wikimetrics won't help us yet because timeseries isn't implemented for threshold * the query is just friggin slow, and I don't think there's anything we can do about it * SILVER LINING: with this new metric definition, we don't have to re-compute the entire history every day. So my proposal is to do this daily: 1. dump that day's worth of data into a temp table in enwiki_p 2. join that to revision_userindex and run the query I wrote 3. concatenate the results manually, maybe with some simple python It's a bitch, but it's all we can do until we tackle this problem the right way (big data, data warehouse, etc.) On Thu, Nov 21, 2013 at 5:48 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: The metric in WikiMetrics could be used for this, but the cohort would have to be re-generated all the time. I implemented the new logic that Kenan described. The problem is, this query runs crazy slow on the slaves: https://gist.github.com/milimetric/7554108#file-active-editors-from-account… I'm gonna try generating a huge cohort, upload it to wikimetrics and see where I get. On Thu, Nov 21, 2013 at 5:28 PM, Toby Negrin &lt;tnegrin(a)wikimedia.org&gt; wrote: I confirmed with Kenan that the metric is similar to the activation metric used in WikiMetrics. -Toby On Thu, Nov 21, 2013 at 2:07 PM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: Dario and I chatted about this. The original purpose of this dashboard was to be more of an acquisition dashboard. Thus to fit that requirement Dario suggested that we use an activation metric. i.e. How many users in month X reached 5 edits within 30 days. Is that possible? On Wed, Nov 20, 2013 at 12:05 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: On Wed, Nov 20, 2013 at 11:34 AM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: That would be good. It is suppose to be in line with the active editor metric. Wait, so it *is* supposed to be like the active editor metric? To be very clear, we have two possible questions we can be answering here, and only one of them is like the active editor metric. Both of these questions will show results for X <- {each of the last 12 months} 1. How many people, of those that created their account on mobile, made at least 5 edits in month X? [THIS IS LIKE ACTIVE EDITOR] 2. How many people created their account on mobile in month X, and made at least 5 edits since then? I've implemented both, but there is a pretty big difference so we need to figure that out before moving on. Dan -- Kenan Wang Product Manager, Mobile Wikimedia Foundation _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics -- Kenan Wang Product Manager, Mobile Wikimedia Foundation -- Kenan Wang Product Manager, Mobile Wikimedia Foundation _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

On Wed, Nov 27, 2013 at 2:41 PM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: Thank you. I'll see if I can tune the query to do this efficiently. The cross-db issue comes from joining the Event Logging table with the mediawiki table. If my tuning doesn't yield results, the only viable solution is to import the event logging stuff into a temp table in labsdb/enwiki_p. Then they'll be on the same database and the query should fly. Is that possible with the schema you're capturing for mobile registrations? In other words, can that data be shared publicly?

On Thu, Nov 28, 2013 at 3:17 AM, Ori Livneh &lt;ori(a)wikimedia.org&gt; wrote: Thanks Ori - this sounds and looks viable to me, and seems like a better solution. Kenan, Jon, Dario, Dan, et al - can we move forward with this? -- Arthur Richards Software Engineer, Mobile [[User:Awjrichards]] IRC: awjr +1-415-839-6885 x6687

On Mon, Dec 2, 2013 at 5:45 PM, Kenan Wang &lt;kwang(a)wikimedia.org&gt; wrote: I'm ok with this. I do see it as a temporary measure though. What Ori says here, "inferring that something must have happened", is sort of the whole reason SQL exists. In my opinion, the problem is that these two data sources can't be joined efficiently to do analytics work on them. But since that's a harder problem at the moment, I agree with Ori's solution. Jon/Arthur, who set up your Event Logging solution and do you need help reviewing / merging this Change? I don't know much about Event Logging but I'm happy to learn and help if you need. Dan

You can backfill the events according to Ori's new logic. Then your query is simple going forward.

OK I'll add a task! On Mon, Dec 2, 2013 at 4:34 PM, Ori Livneh &lt;ori(a)wikimedia.org&gt; wrote: -- Kenan Wang Product Manager, Mobile Wikimedia Foundation

We're planning to tackle this in our upcoming iteration starting on Monday. Dan, just to clarify - are you able to backfill the data, or will we need to do that? I can help write the script, but I don't think I have write access to the Event Logging table where the results would go. The script shouldn't be the hard part though, testing Ori's patch is what needs to be scrutinized the most here. Let me know if you guys need any help with that too. _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

On Wed, Dec 4, 2013 at 2:38 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote: We're planning to tackle this in our upcoming iteration starting on Monday. Dan, just to clarify - are you able to backfill the data, or will we need to do that? I can help write the script, but I don't think I have write access to the Event Logging table where the results would go. I can pass along the credentials, in the interest of furthering the EventLogging handover. Ping me whenever. _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

I think the plan is to change this EL instance to log the event "this user has now made X edits within their first 30 days", where X is in {1,5,10,25,50,100}. That will start happening when this patch: https://gerrit.wikimedia.org/r/#/c/98079/1/WikimediaEvents.php is merged. So my idea is to backfill these milestone events so new the query can just be:

On Thu, Dec 5, 2013 at 4:18 PM, Matthew Flaschen &lt;mflaschen(a)wikimedia.org&gt; wrote: On 12/05/2013 09:49 AM, Dan Andreescu wrote: I think the plan is to change this EL instance to log the event "this user has now made X edits within their first 30 days", where X is in {1,5,10,25,50,100}. That will start happening when this patch: https://gerrit.wikimedia.org/r/#/c/98079/1/WikimediaEvents.php is merged. So my idea is to backfill these milestone events so new the query can just be: Yeah, but if it's backfilled, it's no longer an actual EL event. I agree with Dario it's cleaner to have a separate table (holding only the backfilled data), and do a UNION. Matt Flaschen I guess it's up to the mobile web team, but I disagree on YAGNI grounds. What's an example of a situation in which you'd care that these milestone EL events are "actual" vs. back-filled? _______________________________________________ Analytics mailing list Analytics(a)lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics

Dario and I chatted about this. The original purpose of this dashboard was to be more of an acquisition dashboard. Thus to fit that requirement Dario suggested that we use an activation metric. i.e. How many users in month X reached 5 edits within 30 days. Is that possible? On Wed, Nov 20, 2013 at 12:05 PM, Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt;wrote;wrote: -- Kenan Wang Product Manager, Mobile Wikimedia Foundation