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 27th April - 3rd May 2018 inclusive.
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
@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 the Scoring, Vote Weight, and Moderator/Community Manager review statistics.
NOTE: During the data collection week of 27th April -3rd May, an issue caused the loss of 'review' data and so this weeks approvals/rejections 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
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, 27th April - 3rd May 2018 inclusive, the SteemSQL DataBase holds a total of 1006 potential contributions to utopian-io. This is 129 more contributions than the previous weeks' total which was 877.
357 of these contributions have been approved, 26 more than the previous weeks' total of 331.
The approval percentage of 35% is 3% less than 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, adding another 10% of the share from the previous analysis and commands 61% of all contributions.
'Graphics' takes 2nd place with a 9.3% share, gaining 2% from the previous week.
And 'Ideas/Suggestions' just beat 'Development' into 3rd spot, accounting for 8.6% of all contributions this week.
These three largest categories account for over 79% of all contributions this week - up over 5% from the previous weeks analysis.
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.
'Bug-hunting' - Approvals / Rejections
This week, the 'Bug-Hunting' category has dominated the approved contributions to @utopian-io.
The last two weeks have seen a sharp rise in contributions to this category, and this week of analysis is a record high for 'Bug-Hunting' category contributions.
With a sharp rise in contributions to a category, it is not always met with a similar rise in approval rates, and yet again we can see this has taken place again.
The highest number of contributions to the category has been met with the lowest approval rate of the year - A sure sign that quantity does not mean quality.
The analyst expects further measures to be taken with regards to this category as there are signs that contributors are try to exploit 'Bug Hunting'.
'Graphics' - Approvals / Rejections
The 'Graphics' contribution category is back in the top 3 most popular this week and has seen 30 additional contributions from the previous week of analysis.
Interestingly, this category has bucked the trend with regards to contributions going up, and approval percentages going down, and has gained a 60% approval rate. This percentage is 25% better than the average across all categories, and is the highest approval percentage for the category since the analysis covering the 2nd week of January 2018.
3. All contribution types
The following table shows all types and totals of contributions submitted, with their approval percentage in the end column.
The top category in terms of approval percentage is again 'Analysis', however, with just 7 contributions (1 more than the previous week), this perhaps gives a slightly biased look to this figure.
'Development' achieved another solid week with regards to contribution numbers and approval percentage, 81 contributions is 10 fewer than last time, and a 78% approval rate, 5% lower than previously
'Ideas/Suggestions' gained 9% in its approval percentages over the previous week, but still holds the unwanted prize of the lowest approval % of the week with just 17%.
The approval percentage of 'Video-Tutorials' climbed 12% this week to over 71%, but the contributions numbers fell sharply from 39 previously to just 21 this time.
4. Contribution Scoring
Contribution scoring took place this week and the table below shows the min, max, and average across the contribution categories.
Three categories achieved the maximum score of 100, and the lowest score across all categories was 23 (Graphics).
'Video Tutorials' received the best average score across all categories, with 'Copywriting' averaging out as the lowest.
5. 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.
This week, Analysis has overtaken Development with the highest category vote weight average.
The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions' at 2.9%, up 0.3% from the previous week.
A 20% vote from @utopian-io in this week of analysis is estimated to be worth around $150.
6. Moderator/Community Manager Statistics
Unfortunately, as per the note at the beginning of this analysis, a database error caused the loss of a large set of review data.
A total of 297 contributions were left without review data following the incident, and so we cannot gain accurate data for this weeks analysis.
7. Summary Analysis
For the second consecutive week, the total number of contributions has risen. The long downward trend has been broken and this looks positive on the chart below.
However, this change in direction should be treated with caution as this move is mainly due to the sharp rise in contributions for just a single category - 'Bug Hunting'.
This weeks' approval percentage is down 3% on the previous week.
Unfortunately, 35% marks a new low to the success rate percent of contributions to utopian-io. This can be considered tough on the contributors to the project, but is a confirmation that the desire for quality open source work is of utmost importance to utopian-io.
Summary
Another rise in contribution numbers, but a decline in the success rate of the submissions to utopian-io.
Another sharp rise in 'Bug-hunting' contributions, but the lowest approval % of the year so far.
'Analysis' topped the vote weight percentage charts for the first time.
A 35% contribution approval percentage is the lowest of any weeks analysis thus far.
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 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],'$.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/27/2018 00:00:00' AND '05/03/2018 23:59:59'
and parent_author = '' and depth = 0 and category = 'utopian-io' and JSON_VALUE([json_metadata],'$.type') IS NOT NULL
and json_metadata <> ''
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_week1
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_week1 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_week1]
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_week1]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)
UPDATE [SteemSQL].[dbo].[utopian_authors_may_2018_week1]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)
-------------------------------
-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_may_2018_week1] -- 1006 (887)
select * from [dbo].[utopian_authors_may_2018_week1] where SQL_VOTE = 'Yes' -- 357 (330)
select * from [dbo].[utopian_authors_may_2018_week1] where reviewed_approved is null --
select * from [dbo].[utopian_authors_may_2018_week1] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL) -- 548 (435)
select * from [dbo].[utopian_authors_may_2018_week1] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_may_2018_week1] 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_week1]
where contribution_type is not NULL --and reviewed_approved = 'true'
order by [contribution_type] asc
-- RAW DATA FOR EXCEL - AFTER THE CRASH - USED FOR 1ST WEEK MAY
select contribution_type as Category, SQL_VOTE as Approved from [dbo].[utopian_authors_may_2018_week1]
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_week1]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc
select * from [utopian_authors_may_2018_week1] 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_week1]
where reviewed_approved = 'true'
group by contribution_type
select * from [dbo].[utopian_authors_may_2018_week1] where ISNUMERIC(score) = 1
-------------------------------
-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_may_2018_week1]
group by contribution_type
-------------------------------
-- NEW MODS OR CMS?
Select distinct moderator from [dbo].[utopian_authors_may_2018_week1] 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_week1] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week1] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week1] 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_week1] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week1] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week1] 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_week1] uto1
WHERE uto1.author = uto.author and reviewed_approved = 'true'),
rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_may_2018_week1] uto1
WHERE uto1.author = uto.author and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week1] 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_week1] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_may_2018_week1] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
FROM [dbo].[utopian_authors_may_2018_week1] uto
where moderator in (select name from [dbo].[cm])
group by moderator
This data was compiled on the 21st May 2018 at 4:30pm (UCT)
Thanks
Asher @abh12345