Repository
https://github.com/utopian-io/api.utopian.io
This is an analysis of the Contribution types, Scores, Moderators, and @utopian-io votes for the 11th - 17th May 2018 inclusive.
Contents
General / Assumptions
Contributions Summary
The first full week following downtime
All contribution Types
Category Vote Weighing
Summary Analysis
Tools used to gather data and compile report
General
@utopian-io is the open source project for open source projects, housed on the Steem Blockchain.
Currently, @utopian.io holds 3.6 million Steem power through delegations from its kind sponsors.
To become a sponsor and view the current standings visit https://utopian.io/sponsors
This report is an analysis of the number and type of contributions sent for review, and the success/fail rate of each contribution type. Also included is a review of the Vote Weight for each category and a look at the effect the new methods of contributing is having on submission totals and approval percentages.
NOTE: During the data collection week of 11th - 17th May, contributions were made using alternative interfaces than utopian.io. An issue caused the loss of 'review' data, and this weeks 'scored/un-scored' are based on Votes given by @utopian-io.
If a contribution has been positively voted, it is assumed approved.
Recent reports of this type are listed here.
OCT 2017: October 2017
NOV 2017: Week 1 - Week 2 - Week 3 - Week 4
DEC 2017: Week 1 - Week 2 - Week 3 - Week 4
JAN 2018: Week 1 - Week 2 - Week 3 - Week 4 - Week 5
FEB 2018: Week 1 - Week 2 - Week 3 - Week 4
MAR 2018: Week 1 - Week 2 - Week 3 - Week 4
APRIL 2018: Week 1 - Week 2 - Week 3 - Week 4
MAY 2018: Week 1 - Week 2
I will aim to provide this report on a weekly basis, with a look at the previous weeks data and more historical data to compare and contrast.
This will allow contributors and employees of utopian.io to keep in touch with how the platform is progressing, such as the approval percentages and number of Contribution Types being submitted. It is hoped that this analysis will provide information that can add value to decision making processes at utopian-io.
1. Contributions Summary
For the week, 11th - 17th May 2018 inclusive, the SteemSQL DataBase holds a total of 724 potential contributions to utopian-io. This is 286 more contributions than the previous weeks' total which was 438.
This is the 2nd week of analysis in which the utopian.io application was not used to submit approvals.
326 of these contributions have been up-voted, 128 more than the previous weeks total of 198.
The voted on/approval percentage of 45% is the same as the previous weeks analysis.
The Contributions offered can be any one of the following types:
Note: 'Translation' contributions are currently on hold.
This weeks data, split into Contribution types is shown in the pie chart below:
Key ordered by largest to smallest contribution category
The 'Bug-hunting' is again the largest contribution category (475 submissions), and this week totals two-thirds of all contributions to utopian-io - up to 66% from 53% previously. This rapid growth is a potential warning sign and I expect the category to come under scrutiny if this continues.
The 2nd and 3rd most popular categories are still 'Development' and 'Graphics' respectively. Development has lost 3% of it's share since last week, and Graphics has maintained an 8% hold.
These three largest categories account for 85% of all contributions this week, which is the highest percentage since this statistic was compiled. The 'Bug-hunting' is likely the key driver for this, and not the drop in contributions to other categories.
2 The first full week following downtime
This weeks and last weeks contributions were made from alternative sources than the utopian.io website. The downtime and switch to other condensers produced a low week as far as total contributions. Has this continued or are contributors finding their feet again?
The table above shows the difference in total contributions from this week to the previous. The downtime last week caused a 56% reduction in contributions, which has turned around week this past week with a 65% increase.
The major gainers are the 'Tutorials' and 'Bug-hunting' categories, with over 100% more contributions than the previous week. This is way above the average and may suggest that these categories include more 'ad-hoc' contributions than categories such as 'Development' and 'Analysis', which could potentially be tied to on-going projects.
3. All contribution types
The following table shows all types and totals of contributions submitted, with their approval percentage in the end column.
This week, 'Analysis' fared best out of the standard contribution categories with 8 from 9 accepted contributions.
A solid week for 'Development', 'Graphics', 'Tutorials', and 'Video-tutorials' which all gained an approval percentage of over 70%. This is important to note as the overall approval percentage is 25% lower than this at 45%, dragged down by the sheer volume of 'Bug-hunting' contributions which were approved less than one in three submissions.
'Ideas' continued to suffer as a category with just one approval from eleven. A look at a previous chart for this category, covering twelve weeks earlier in the year shows just how much the contributions and approvals have fallen here.
The additional rules and monitoring put in place to guard against low quality contributions to this category have clearly had a major impact, perhaps even too much so!
4. Category Vote Weighting
Each approved contribution is likely (but not guaranteed) to receive a vote from utopian-io to support the work carried out. Each category has a range of weight that can be attributed to the vote depending on the answers provided during the review process.
'Development', as expected, holds the highest category vote weight average. 'Analysis' and 'Graphics' are in 2nd and 3rd and remain among the highest valued contributions
The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions' at 1.4%, down 1.2% from the previous week. This vote weight, whilst only taking into account a single approval, is perhaps another reason for the sharp decline in submissions to this category.
With vote weight data now being collected each week, we can look back at previous analysis and summarize these values.
The chart above covers the past 5 weeks and week can see that in this time, most categories have averaged similar results to the previous week, or declined in vote weight slightly. The standout category is 'Blog', and submissions to this category have more than doubled in average vote weight value over the time-frame shown.
5. Summary Analysis
This weeks' approval percentage is exactly the same as the previous weeks analysis and potential shows a marked improvement in the quality of contributions over the past two weeks.
The contribution totals were seriously affected by downtime last week, but show a good recovery - down 56% last time, and up 65% from last week.
Although contributions are not quite at the same levels prior to the down time, the numbers are much improved, and the approval % is noticeably better.
Summary
'Bug-hunting' contributions continue to rise, and without the approval percentage doing the same.
A sustained improvement in approval percentages following on from last week - 45% in both cases.
Contributions rose markedly with a full 7 days available to submit. This combined with the sustained approval % is a promising sign for @utopian-io and it's contributors.
6. Tools used to gather this data and compile report
The charts used to present the data were produced using MS Excel.
The data is sourced from SteemSQL - A publicly available SQL database with all the blockchain data held within.
The SQL queries to extra to the data have been produced in both SQL Server Personal Edition and LINQPAD 5. Some of the code used for these results is as follows:
-------------------------------
-- MAIN INSERT START
-------------------------------
SET NOCOUNT ON
Declare @permlink Varchar(2000)
Declare @author Varchar(50)
Declare @contributionType Varchar(50)
Declare @moderator Varchar(50)
Declare @reviewed_approved Varchar(50)
Declare @flagged Varchar(50)
Declare @score Varchar(50)
Declare @influence Varchar(50)
Declare @staffpick Varchar(50)
Declare @utopian_vote Varchar(50)
Declare @sql_vote Varchar(50)
Declare @weight Varchar(50)
Declare @created datetime
DECLARE APPROVALS_CURSOR CURSOR FOR
SELECT [permlink]
,[author]
,JSON_VALUE(json_metadata,'$.tags[1]') as contribution_type
,JSON_VALUE([json_metadata],'$.moderator.account') as moderator
,JSON_VALUE([json_metadata],'$.moderator.reviewed') as reviewed_approved
,JSON_VALUE([json_metadata],'$.moderator.flagged') as flagged
,JSON_VALUE([json_metadata],'$.score') as score
,JSON_VALUE([json_metadata],'$.total_influence') as influence
,JSON_VALUE([json_metadata],'$.staff_pick') as staff_pick
,"Utopian_vote" =
CASE
WHEN JSON_QUERY([active_votes],'$') like '%utopian-io%' THEN 'Yes'
ELSE 'No'
END
, null as SQL_VOTE
, null as [WEIGHT]
,[created]
FROM [SQL.STEEMSQL.COM].[DBSteem].[dbo].[Comments] WITH (NOLOCK)
WHERE
created BETWEEN '05/11/2018 00:00:00' AND '05/17/2018 23:59:59'
and depth = 0
and category = 'utopian-io'
and ISJSON([active_votes])>0
and ISJSON(json_metadata) > 0
and JSON_VALUE(json_metadata,'$.tags[1]') in ('bug-hunting','development','analysis','visibility','tutorials','video-tutorials',
'graphics','copywriting','ideas','documentation','task-development','blog','task-graphics','task-documentation''task-ideas','task-social')
order by contribution_type, [created] asc
OPEN APPROVALS_CURSOR
FETCH NEXT FROM APPROVALS_CURSOR
INTO @permlink, @author, @contributionType, @moderator, @reviewed_approved, @flagged, @score, @influence, @staffpick, @utopian_vote, @sql_vote, @weight, @created
WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert into utopian_authors_may_2018_week3
SELECT @permlink, @author, @contributionType, @moderator, @reviewed_approved, @flagged, @score, @influence, @staffpick, @utopian_vote, NULL, NULL, @created
FETCH NEXT FROM APPROVALS_CURSOR
INTO @permlink, @author, @contributionType, @moderator, @reviewed_approved, @flagged, @score, @influence, @staffpick, @utopian_vote, @sql_vote, @weight, @created
END
CLOSE APPROVALS_CURSOR
DEALLOCATE APPROVALS_CURSOR
SET NOCOUNT OFF
-------------------------------
-- MAIN INSERT COMPLETE
-------------------------------
-- First UPDATE CURSOR
SET NOCOUNT ON
Declare @permlink Varchar(2000)
Declare @author varchar(50)
Declare @weight int
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT permlink, author, WEIGHT from utopian_authors_may_2018_week3 FOR UPDATE OF WEIGHT
OPEN UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
INTO @permlink, @author, @weight
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE [dbo].[utopian_authors_may_2018_week3]
SET WEIGHT = (select TOP 1 [weight] FROM [SQL.STEEMSQL.COM].[DBSteem].[dbo].[Txvotes] with (nolock)
WHERE [voter] = 'utopian-io'
and [permlink]+[author] = @permlink+@author
ORDER BY [timestamp] desc)
WHERE CURRENT OF UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
INTO @permlink, @author, @weight
END
CLOSE UPDATE_CURSOR
DEALLOCATE UPDATE_CURSOR
SET NOCOUNT OFF
-------------------------------
-- SECOND UPDATES
UPDATE [SteemSQL].[dbo].[utopian_authors_may_2018_week3]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)
UPDATE [SteemSQL].[dbo].[utopian_authors_may_2018_week3]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)
-------------------------------
-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_may_2018_week3] order by created asc -- 724 (438)
select * from [dbo].[utopian_authors_may_2018_week3] where SQL_VOTE = 'Yes' -- 326 (198)
select * from [dbo].[utopian_authors_may_2018_week3] where reviewed_approved is null --
select * from [dbo].[utopian_authors_may_2018_week3] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL) -- 548 (435)
select * from [dbo].[utopian_authors_may_2018_week3] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_may_2018_week3] where SQL_VOTE = 'Yes' and reviewed_approved = 'false' -- 0 (Approved but later rejected)
-- RAW DATA FOR EXCEL
select contribution_type as Category, reviewed_approved as Approved from [dbo].[utopian_authors_may_2018_week3]
where contribution_type is not NULL --and reviewed_approved = 'true'
order by [contribution_type] asc
-- RAW DATA FOR EXCEL - AFTER THE CRASH - USED FOR 2nd WEEK MAY
select contribution_type as Category, SQL_VOTE as Approved from [dbo].[utopian_authors_may_2018_week3]
where contribution_type is not NULL --and SQL_VOTE = 'Yes'
order by [contribution_type] asc
---------------------------
-- COUNT WHEN APPROVED
select contribution_type, count(contribution_type) from [dbo].[utopian_authors_may_2018_week3]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc
select * from [utopian_authors_may_2018_week3] where [contribution_type] = 'ideas'
-----------------------------
--SCORES
select contribution_type,
max(cast(score as float)) as max_score,
min(cast(score as float)) as min_score,
avg(cast(score as float)) as avg_score
from [dbo].[utopian_authors_may_2018_week3]
where reviewed_approved = 'true'
group by contribution_type
select * from [dbo].[utopian_authors_may_2018_week3] where ISNUMERIC(score) = 1
-------------------------------
-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_may_2018_week3]
group by contribution_type
-------------------------------
-- NEW MODS OR CMS?
Select distinct moderator from [dbo].[utopian_authors_may_2018_week3] where (moderator not in (select name from mods) and moderator not in (select name from cm))
-- MODERATOR REVIEWS
SELECT moderator,
-- count(moderator) as review_total,
approved = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week3] uto
where moderator in (select name from [dbo].[mods])
group by moderator
-- MODERATOR CONTRIBUTIONS
SELECT author,
-- count(author) as total,
approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week3] uto
where author in (select name from [dbo].[mods])
group by author
-------------------
-- CM CONTRIBUTIONS
SELECT author,
-- count(author) as total,
approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week3] uto
where author in (select name from [dbo].[cm])
group by author
-- CM MODS
SELECT moderator as Community_manager,
approved = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week3] uto
where moderator in (select name from [dbo].[cm])
group by moderator
This data was compiled on the 2nd June 2018 at 11 am (UCT)
Thanks
Asher @abh12345