This is an analysis of the Contribution types, Scores, Moderators, and 
Contents
General / Assumptions
Contributions Summary
Categories in focus
All contribution Types
Contribution Scoring
Category Vote Weighing
Moderator/Community Manager Statistics
Summary Analysis
Tools used to gather data and compile report
General
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 the Scoring, Vote Weight, and Moderator/Community Manager review statistics.
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
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, 13th - 19th April 2018 inclusive, the SteemSQL DataBase holds a total of 691 potential contributions to utopian-io. This is 72 fewer contributions than the previous weeks' total which was 763.
256 of these contributions have been approved, 54 fewer than the previous weeks' total of 293.
2 contributions were approved but did not receive a vote.
The approval percentage of 37% is a 1% fall from the previous week.
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:
The 'Bug-hunting' category remains the largest this week and retains 37% of all contributions.
'Suggestions/Ideas' holds 2nd place but loses 9% of the contributions share from the previous week with 22% of all contributions this time.
'Graphics' contributions regains 3rd spot after 'Development' took its place last week, and holds 15% of all contributions.
These three largest categories account for 74% of all contributions offered- Down 5% from the previous week.
2 Categories in focus
In this section we take a look a few of the individual contribution types of interest.
A full list of contribution types is listed further below with associated figures and analysis.
'Graphics' - Approvals / Rejections
'Graphics' has become the 3rd largest contribution category this week with 88 contributions produced for review.
As we can see from the chart above, the category has been met with a steady decline in contributions over the past 3 months and has lost over 80% of the numbers seen contribution wise, since the beginning of February.
This week however, the number has improved over the previous week and could mark a change in fortune for this category. However, the approval percentage suggests there is still plenty of room for improvement with regards to the quality of the contribution submitted.
'Ideas/Suggestions' - Approvals / Rejections
'Ideas/Suggestions' has held its position as the 2nd most contributed to category this week, but as the chart below suggests, not everything is rosy here.
The total contributions have fallen around 85% since the end of January, and it seems this category is quickly falling out of favor. Perhaps the reduction in contributions is down to stricter moderation of the category as the approval % has also fallen dramatically over this period. Less than 10% of the contributions to the category were approved this week- only 14 successes from 153 submissions.
3. All contribution types
The following table shows all types and totals of contributions submitted, with their approval percentage in the end column.
The 'Development' category received fewer contributions and fell with regards to approval percentage. However, it still stood out with an 81% approval rate.
'Video-Tutorials' came out with the best approval percentage again this week with 84%, as only 5 from 31 contributions were rejected.
4. Contribution Scoring
Following an issue with the utopian-io central databases during this week of data collection, only 114 contributions hold a score (including 0) from the 256 approved and so it has been decided not to analyse the data collected for this period.
5. Category Vote Weighting
A new chart this week to assess what size of vote is given to each category. Once additional collections have been made over coming weeks, the averages will be viewed over a larger range.
Development tops the vote weight chart, with Analysis, and Graphics close behind. These weights are almost double those of the 'Tutorials', 'Video-Tutorial', and 'Copywriting' categories,
The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions', which may be a clue to the falling number of contributions to this category, as detailed above.
A 20% vote from
6. Moderator/Community Manager Statistics
This section first appeared in the previous weeks' analysis and in future, a wider data range will be available for assessment.
Moderator Reviews
Two Moderators approved 100% of the contributions they reviewed this week (four previously), and one of these moderators passed a total of 10 contributions.
Three moderators passed fewer than 10% of the submissions they looked over (two previously) and the strictest moderator passed 0 from 23 contributions - all of which were 'Ideas'.
Moderator Contributions
18 out of 23 contributions put forward by moderators were approved this week - 78%
Community Manager Reviews
No Community Managers approved 100% of the submissions they reviewed within this data set, and the highest approval percentage by a Community Manager was 83% - 5 from 6 contributions.
The strictest Community Manager passed 0 from 6 contributions.
Community Manager Contributions
The 2 contributions submitted by Community Managers this week were approved.
7. Summary Analysis
This week, the number of contributions has again fallen against the previous week and continues the downward trend in recent months. This marks yet another year low in totals, both submitted and approved for 2018.
As the chart above displays, the figures are nothing like those of earlier in the year, and perhaps this level of contributions and approval percentages is the new norm for utopian-io.
Over the past couple of weeks, the price of STEEM has more than doubled from its recent lows, but this has still not been enough to spark a revival in contributions to the platform.
This weeks' approval percentage is only slightly lower than the previous week, and is the 2nd lowest, at 37%, since the beginning of the project.
It has been a month since the approval percentage for each week analysed has topped 40% and it is clear that the review procedure has become more strict. While this may be off-putting to some contributors, the off-shoot is that utopian-io is likely to be gaining respect in the Open Source area, as quality levels increase.
Summary
Another decrease in contributions and a slight decrease in the approval percentage this week.
'Development' performed excellently along with 'Video-Tutorials' which both gained approval percentages of over 90%.
'Ideas/Suggestions' Showed a further noticeable fall in contributions and the rate of approval was less than 1 in 10 contributions.
The upside to this is that approved contributions (bar the two that did not receive a vote) will be rewarded well.
8. 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 int
Declare @influence int
Declare @staffpick Varchar(50)
Declare @utopian_vote Varchar(50)
Declare @sql_vote Varchar(50)
Declare @weight int
Declare @created datetime
DECLARE APPROVALS_CURSOR CURSOR FOR
SELECT [permlink]
,[author]
,JSON_VALUE([json_metadata],'$.type') 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 '04/13/2018 00:00:00' AND '04/19/2018 23:59:59'
and parent_author = '' and depth = 0 and category = 'utopian-io' and JSON_VALUE([json_metadata],'$.type') IS NOT NULL
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_april_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_april_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_april_2018_week3]
SET WEIGHT = (select TOP 1 [weight] FROM [SQL.STEEMSQL.COM].[DBSteem].[dbo].[Txvotes]
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_april_2018_week3]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)
UPDATE [SteemSQL].[dbo].[utopian_authors_april_2018_week3]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)
-------------------------------
-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_april_2018_week3] -- 691 (763)
select * from [dbo].[utopian_authors_april_2018_week3] where SQL_VOTE = 'Yes' -- 256 (293)
select * from [dbo].[utopian_authors_april_2018_week3] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL) -- 435 (469)
select * from [dbo].[utopian_authors_april_2018_week3] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_april_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_april_2018_week3]
where contribution_type is not NULL
order by [contribution_type] asc
-- COUNT WHEN APPROVED
select contribution_type, count(contribution_type) from [dbo].[utopian_authors_april_2018_week3]
where reviewed_approved = 'true'
group by [contribution_type]
order by [contribution_type] asc
-----------------------------
--SCORES
select contribution_type,
max(cast(score as INT)) as max_score,
min(cast(score as INT)) as min_score,
avg(cast(score as INT)) as avg_score
from [dbo].[utopian_authors_april_2018_week3]
where reviewed_approved = 'true'
group by contribution_type
select * from [dbo].[utopian_authors_april_2018_week3] where ISNUMERIC(score) = 1
-------------------------------
-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_april_2018_week3]
group by contribution_type
-------------------------------
-- NEW MODS OR CMS?
Select distinct moderator from [dbo].[utopian_authors_april_2018_week3] where (moderator not in (select name from mods) and moderator not in (select name from cm))
-- MODERATOR CONTRIBUTIONS
SELECT author,
-- count(author) as total,
approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_april_2018_week3] uto
where author in (select name from [dbo].[mods])
group by author
-- MODERATOR REVIEWS
SELECT moderator,
-- count(moderator) as review_total,
approved = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_april_2018_week3] uto
where moderator in (select name from [dbo].[mods])
group by moderator
-------------------
-- CM CONTRIBUTIONS
SELECT author,
-- count(author) as total,
approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week3] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_april_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_april_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week3] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_april_2018_week3] uto
where moderator in (select name from [dbo].[cm])
group by moderator
This data was compiled on the 25th April 2018 at 9:30am (UCT)
Thanks
Posted on Utopian.io - Rewarding Open Source Contributors