EBernhardson has uploaded a new change for review.
https://gerrit.wikimedia.org/r/176789
Change subject: Initial reporting for flow
......................................................................
Initial reporting for flow
Change-Id: Ic3e0afb0f3c3e2037e4ec946c20cef89db5b1412
---
A .gitreview
M dashboards/reportcard.json
A flow/active-boards.sql
A flow/active-topics.sql
M flow/config.yaml
A flow/messages-posted.sql
A flow/moderation-actions.sql
A flow/unique-users.sql
8 files changed, 86 insertions(+), 1 deletion(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-flow-data
refs/changes/89/176789/1
diff --git a/.gitreview b/.gitreview
new file mode 100644
index 0000000..12ee97a
--- /dev/null
+++ b/.gitreview
@@ -0,0 +1,6 @@
+[gerrit]
+host=gerrit.wikimedia.org
+port=29418
+project=analytics/limn-flow-data
+defaultbranch=master
+defaultrebase=0
diff --git a/dashboards/reportcard.json b/dashboards/reportcard.json
index 87fdda5..232db6a 100644
--- a/dashboards/reportcard.json
+++ b/dashboards/reportcard.json
@@ -8,6 +8,16 @@
"graph_ids": [
"http://datasets.wikimedia.org/limn-public-data/mobile/datafiles/thanks-daily.csv";
]
- }
+ },
+ {
+ "name": "Usage data",
+ "graph_ids": [
+ "active-boards",
+ "active-topics",
+ "messages-posted",
+ "moderation-actions",
+ "unique-users"
+ ]
+ }
]
}
diff --git a/flow/active-boards.sql b/flow/active-boards.sql
new file mode 100644
index 0000000..b605302
--- /dev/null
+++ b/flow/active-boards.sql
@@ -0,0 +1,14 @@
+SELECT z.weekstart,
+ count(distinct workflow_wiki, workflow_namespace, workflow_title_text)
as num_boards
+ FROM flow_workflow
+ JOIN (
+ SELECT a.tree_ancestor_id,
+
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(a.tree_descendant_id),1,12),16,10)>>2)/1000),
interval DAYOFWEEK(
FROM_UNIXTIME((conv(substring(hex(a.tree_descendant_id),1,12),16,10)>>2)/1000))
day)) as weekstart
+ FROM flow_tree_node a
+ JOIN (
+ SELECT b.tree_descendant_id, MAX(b.tree_depth) as max
+ FROM flow_tree_node b
+ GROUP BY b.tree_descendant_id
+ ) y ON y.max = a.tree_depth AND y.tree_descendant_id =
a.tree_descendant_id
+ ) z ON z.tree_ancestor_id = workflow_id
+ GROUP BY z.weekstart;
diff --git a/flow/active-topics.sql b/flow/active-topics.sql
new file mode 100644
index 0000000..fb53661
--- /dev/null
+++ b/flow/active-topics.sql
@@ -0,0 +1,13 @@
+SELECT z.weekstart, count(distinct workflow_id) as num_topics
+ FROM flow_workflow
+ JOIN (
+ SELECT a.tree_ancestor_id,
+
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(a.tree_descendant_id),1,12),16,10)>>2)/1000),
interval DAYOFWEEK(
FROM_UNIXTIME((conv(substring(hex(a.tree_descendant_id),1,12),16,10)>>2)/1000))
day)) as weekstart
+ FROM flow_tree_node a
+ JOIN (
+ SELECT b.tree_descendant_id, MAX(b.tree_depth) as max
+ FROM flow_tree_node b
+ GROUP BY b.tree_descendant_id
+ ) y ON y.max = a.tree_depth AND y.tree_descendant_id =
a.tree_descendant_id
+ ) z ON z.tree_ancestor_id = workflow_id
+ GROUP BY z.weekstart;
diff --git a/flow/config.yaml b/flow/config.yaml
index 2b14994..7aa984a 100644
--- a/flow/config.yaml
+++ b/flow/config.yaml
@@ -14,3 +14,18 @@
db: el
# the key in the graph section is the name of the sql file and generated file
graphs:
+ active-boards:
+ title: "Active boards"
+ frequency: hourly
+ active-topics:
+ title: "Active topics"
+ frequency: hourly
+ messages-posted:
+ title: "Messages posted"
+ frequency: hourly
+ unique-users-posting-messages:
+ title: "Unique users posting messages"
+ frequency: hourly
+ moderation-actions:
+ title: "Moderation actions"
+ frequency: hourly
diff --git a/flow/messages-posted.sql b/flow/messages-posted.sql
new file mode 100644
index 0000000..c879b72
--- /dev/null
+++ b/flow/messages-posted.sql
@@ -0,0 +1,6 @@
+SELECT
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000),
interval DAYOFWEEK(
FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000)) day)) as
weekstart,
+ count( rev_id ) as num_replies
+ FROM flow_revision
+ WHERE rev_change_type = 'reply'
+ GROUP BY weekstart;
+
diff --git a/flow/moderation-actions.sql b/flow/moderation-actions.sql
new file mode 100644
index 0000000..6865253
--- /dev/null
+++ b/flow/moderation-actions.sql
@@ -0,0 +1,10 @@
+SELECT
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000),
interval DAYOFWEEK(
FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000)) day)) as
weekstart
+ rev_change_type,
+ count(rev_change_type) as num_actions,
+ FROM flow_revision
+ WHERE rev_change_type IN (
+ 'restore-post', 'hide-post', 'delete-post',
+ 'restore-topic', 'hide-topic', 'delete-topic'
+ )
+ GROUP BY weekstart, rev_change_type;
+
diff --git a/flow/unique-users.sql b/flow/unique-users.sql
new file mode 100644
index 0000000..18bbd48
--- /dev/null
+++ b/flow/unique-users.sql
@@ -0,0 +1,11 @@
+SELECT weekstart,
+ SUM(user) as unique_users
+ FROM (
+ SELECT 1 as user,
+
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000),
interval DAYOFWEEK(
FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)>>2)/1000)) day)) as
weekstart
+ FROM flow_revision
+ WHERE rev_change_type = 'reply'
+ GROUP BY rev_user_wiki, rev_user_id, rev_user_ip
+ ) x
+ GROUP BY weekstart;
+
--
To view, visit https://gerrit.wikimedia.org/r/176789
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ic3e0afb0f3c3e2037e4ec946c20cef89db5b1412
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-flow-data
Gerrit-Branch: master
Gerrit-Owner: EBernhardson <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
