398606 - Need an automated way to collect Weekly Metrics

Status: RESOLVED FIXED

(support.mozilla.org :: General, defect)

Description

[David Tenser [:djst]]

On a weekly/monthly basis, we will collect a number of metrics that measures the quality, status, and load of the site. 

This bug is about the TikiWiki parts (KB/forums). It should ideally be a page/module only accessible by administrators, which lets you select a start and end date for the measurements, and then it would run a number of SQL statements on the database to fetch the relevant info and present it on a page.

Comment 1

[David Tenser [:djst]]

This depends on bug 397703 to get the "Answered thread ratio" for forums.

Comment 2

[Ray Kiddy]

Hey there. Just wanted to offer an assist. Hablo SQL, long time.

If you want help with this, ping. If you want to just export the MySQL schema, I can work with that from outside. I might not even need to set up a Twiki myself if the data fetch code is abstracted, as it should be. My semester ends this week, so cycles may be available.

Comment 3

[Nelson Ko [:nkoth]]

David, Can you log in as admin and look at tiki-admin_actionlog.php, and see if it generates the info you need?

Let me know what is missing.

Ray, thanks for your offer. I'm inclined to improve the Action Log than doing something outside. One hybrid way is just to take the csv content churned out by the Action Log which you can then work on externally. We'll see how much is missing first and see what needs to be done.

Comment 4

[David Tenser [:djst]]

Here's the list of weekly metrics associated with TikiWiki. I have checked the ones that are fixed so far using tiki-admin_actionlog.php. Note that all of them still require manual counting. For example, in order to see the active KB contributors, I need to count the number of users with "created wiki page" or "modified wiki page" counts > 0.

Participation
[x] Active KB contributors
[x] Active forum contributors
[ ] New contributors
[x] Number of new KB articles
[x] Number of modified KB articles

Performance
[] Average article quality ratio
[] Avg. article “problem solved” ratio
[] Answered forum thread ratio
[] Avg. forum thread answer time

Traffic
[x]New forum threads

Highlights
[] Top 5 rated problems
[] Top 5 rated quality articles
[] Bottom 5 rated quality articles

Comment 5

[Nelson Ko [:nkoth]]

I have updated the version on support-stage. Enhancements:

1) Categories have now been turned on. It has just started to collect data, though. The setting for this is actually in the "settings" panel right in front of us.

2) I verified that the numbers in the tables are actually number of actions, not the number of pages. To overcome this, I have created a table that shows breakdown of number of actions by object.

3) The table of actions broken down by object, and the one broken down by users now total up the number of users or objects so you don't have to count.

4) In order to affect what goes in to the tables, you can go to the "settings" panel, change the checkboxes for "viewed" (do NOT change the "recorded" setting), and then re-run the query. Using this method, you should be able to easily generate counts of the "participation" items above.

Note, for highlights, you can try the solution for bug 398764

Comment 6

[David Tenser [:djst]]

Here's what I can get today:

Participation
[x] Active KB contributors
[x] Active forum contributors
[ ] New contributors
[x] Number of new KB articles
[x] Number of modified KB articles

Performance
[] Average article quality ratio
[] Avg. article “problem solved” ratio
[] Answered forum thread ratio (# posts with answers / tot # posts)
[] Avg. forum thread answer time

Traffic
[x]New forum threads


----

"New contributors" would be nice to have here, but the Participation stats can be a separate bug. The Performans stats work, but as long as we're not showing both polls, they're pretty useless.

Nelson, can "New contributors" be added? If so, we can mark this as FIXED once we have that.

Comment 7

[David Tenser [:djst]]

I still can't use this, or I'm not understanding how to.

* Number of modified KB articles - The page lists multiple edits per article, effectively listing _number of article modifications_. There seem to be no way to get the number of _modified articles_.

* Number of new KB articles - there seem to be no way to get this info.

* Under "Number of actions per user," you can manually count the number of modified articles, but not new articles.

* "Number of actions per object" mixes kb articles and forum posts, making this data very hard to use. Also, it lists number of updates, but what about new articles?

* It generally is a lot of work to gather all this data. 

I'd really like to see a much simpler SQL query based page that will just take the start and end date, and then generate all data we want from the SUMO Weekly Metrics doc and present it on one page. This query would automatically select the right categories when determining e.g. number of new KB articles, forum threads answer ratio, etc. Could this simply be a separate module that we develop independently of Tiki?

Comment 8

[Nelson Ko [:nkoth]]

separate module is a neat solution. But for now, you probably need to generate data. But even now, you don't need to manually count anything if you generate the correct queries. I can walk you through the steps on IRC....

Comment 9

[Laura Thomson :laura]

Most of this is covered by Omniture, but we do need reporting on CSAT.

Comment 10

[David Tenser [:djst]]

This is one of the primary Q2 goals.

Comment 11

[Laura Thomson :laura]

This is the main bug for metrics dashboard, may need to dupe.

Comment 12

[Paul Craciunoiu [:paulc]]

Attachment: jquery ui patch

This is the patch for jquery UI. library files have been placed in appropriate folders: 
* js files in webroot/js
* the default ui theme in styles/jui

For a final version, we may want to remove all the extra JS stuff and keep only the tabs interface, to ease the server load. See http://jqueryui.com/download
As a first implementation, I simply included all the features.

Comment 13

[Paul Craciunoiu [:paulc]]

Attachment: jquery ui images go in webroot/styles/jui/smoothness

Images for the jquery ui theme.

Just as with the library, quite a few of them are optional and could be removed to optimize page load time.

Comment 14

[Paul Craciunoiu [:paulc]]

Attachment: metrics patch, v1

This is the entire metrics patch. Still on the to-do list:
* Connecting to the data warehouse (currently this patch connects to the default sumo master database)
* List-based metrics (probably won't happen until the metrics team supplies the data for the warehouse)
* Code cleanup :)

Comment 15

[Laura Thomson :laura]

I suggest you commit the jquery bits (not the other bits yet obviously).  I'm still working my way through the main patch.

Comment 16

[Paul Craciunoiu [:paulc]]

I'll commit those soon. After a meeting with Cheng and QA, a lot of the syntax for the Tab (the smarty {metric} function) will be updated.
Laura: if you can get the connection to external DB set up, that'd be great. I'd like to help as much as possible to get that done.

Comment 17

[Paul Craciunoiu [:paulc]]

r27949 - committed the JQuery UI library files.

Comment 18

[Laura Thomson :laura]

Comment on attachment 383334 [details] [diff] [review]
metrics patch, v1

This is a reserved r+.

DB stuff still needs doing (but paulc and I are in communication about how to).

We need a bit more error handling,have also talked about that.

Otherwise, it's awesome :)

Comment 19

[Paul Craciunoiu [:paulc]]

Attachment: SQL patch creating the dashboard tables

This patch creates the required dashboard tables, with indexes and foreign keys where best used :)

Comment 20

[Paul Craciunoiu [:paulc]]

Attachment: metrics patch, v2

Second patch (and hopefully final before commit to stage). Yay!

Comment 21

[Paul Craciunoiu [:paulc]]

Laura: should I file a separate bug for the sql patch? attachment (id=384006)

Comment 22

[Laura Thomson :laura]

(In reply to comment #21)
> Laura: should I file a separate bug for the sql patch? attachment (id=384006) [details]

You should file a bug to get the SQL run on staging.

Comment 23

[Laura Thomson :laura]

Comment on attachment 384024 [details] [diff] [review]
metrics patch, v2

Please commit ASAP.

Comment 24

[Paul Craciunoiu [:paulc]]

r28321 for stage
r28323 JQueryUI, and r28325 the dashboard code

Filed bug 500008 for the SQL

Comment 25

[E. Cooper [:ecooper]]

Attachment: metrics patch, v2.1

After shooting myself in the foot twelve times, I've modified instances where metricslib needs to use support_dw. This patch gives me no SQL errors locally using three separate databases (master, slave, support_dw) with three separate users. 

It's based on what was discussed here: https://bugzilla.mozilla.org/show_bug.cgi?id=499228#c18

Comment 26

[Laura Thomson :laura]

Comment on attachment 384977 [details] [diff] [review]
metrics patch, v2.1

This is ok for me.

Comment 27

[E. Cooper [:ecooper]]

(In reply to comment #26)
> (From update of attachment 384977 [details] [diff] [review])
> This is ok for me.

This patch is in trunk at r28408.

Comment 28

[Paul Craciunoiu [:paulc]]

(In reply to comment #27)
> This patch is in trunk at r28408.
This should be added to prod branch as well.

Comment 29

[Laura Thomson :laura]

Reopening until it's committed to prod.  Paul or Eric: can one of you take care of this?

Comment 30

[E. Cooper [:ecooper]]

(In reply to comment #29)
> Reopening until it's committed to prod.  Paul or Eric: can one of you take care
> of this?

In prod at r28510

Comment 31

[Milos Dinic [:Milos]]

How can I verify this?

Comment 32

[Louis-Philippe Huberdeau]

Metrics configuration data helped in understanding the feature. I created a branch on tiki svn to upstream this feature as it's a larger chunk. The branch shouldn't last very long, but I need to make several changes to the implementation.

My concerns/questions are the following:
* Database connectivity is currently made in db/tiki-db.php. Wouldn't it be better to use preferences for these as they are data driven? It may be a little out of scope, but I would even go for allowing multiple sources, and a single point of configuration for data sources, which could also be used by plugins like SQL and REPORT.
* I will need to document usage for this one. Is there any documentation I can start from?
* Tabs have a content section. It appears to contain smarty syntax. What is it used for?

Comment 33

[Paul Craciunoiu [:paulc]]

(In reply to comment #32)
> * Database connectivity is currently made in db/tiki-db.php. Wouldn't it be
> better to use preferences for these as they are data driven?
Not sure I understand what you mean by using preferences.
> * I will need to document usage for this one. Is there any documentation I can
> start from?
Check out: https://wiki.mozilla.org/Support/MetricsDashboardPRD/implementation
(along with the spec: https://wiki.mozilla.org/Support/MetricsDashboardPRD)
> * Tabs have a content section. It appears to contain smarty syntax. What is it
> used for?
In the admin, you can add in content that shows up above the automatically-generated data. See the actual dashboard to get a sense of it:
http://support.mozilla.com/tiki-metrics.php (expand one of the results)
I don't think we're actually doing this. If you some sample data to play around with, Cheng may be able to help.

Comment 34

[Paul Craciunoiu [:paulc]]

s/you some/you need some

Comment 35

[Louis-Philippe Huberdeau]

I did a bit more poking around and tiki already has a tiki_dsn table, primarily used by the SQL plugin. Of course, it needs some work as (surprise surprise) the abstraction to accessing the database connection is terrible.

Anyway, the objective would be to have a DNS dropdown for each metric. This way, you can select which database to obtain the data from. The same dashboard could use multiple data sources, which would not be hardcoded in a file.

As for the smarty syntax, I figured out what it was for. The metrics are not displayed at all by default. A special plugin has to be used.

I am still a bit mystified by the queries that must be entered and their configuration. I started a documentation page on doc.tw.o for it. Help would be needed to make it accurate. I just wrote down the things I figured out.

http://doc.tikiwiki.org/Metrics+Dashboard

Among others, I'm not certain how the range affects the query and exactly what the output of the query should be anyway.

Comment 36

[Louis-Philippe Huberdeau]

A dynamic content block with the label MetricsEmpty was always displayed above the metrics. I found no occurence of it in the dump so I removed the portion of code.

I can imagine what it could be used for, but it was not used in production and the condition was buggy (empty or count > 0).

Comment 37

[Louis-Philippe Huberdeau]

Except for the documentation, the  upstream is mostly completed. I merged back in trunk this morning.