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 4th - 10th May 2018 inclusive.
Contents
General / Assumptions
Contributions Summary
The effect of downtime on contribution totals
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 the Scoring, Vote Weight, and Moderator/Community Manager review statistics.
NOTE: During the data collection week of 4th - 10th May, contributions were made using alternative interfaces than utopian.io. An issue caused the loss of 'review' data, and 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
MAY 2018: Week 1
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, 4th - 10th May 2018 inclusive, the SteemSQL DataBase holds a total of 438 potential contributions to utopian-io. This is 568 fewer contributions than the previous weeks' total which was 1006.
However, during this analysis period utopian-io stopped accepting contributions while they worked on a severe issue, and so the number of contributions compared to recent weeks should not be considered negative.
198 of these contributions have been approved, 159 less than the previous weeks total of 357.
The approval percentage of 45% is 10% more 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, still commanding over 50% of all contributions, but this percentage has fallen 8% this week to 53%.
'Development' is the 2nd most popular submission category, gaining an additional 6% share this time.
And 'Graphics' takes 3rd place, just ahead of 'Copywriting' with an 8.2% share, falling 1% from the previous week.
These three largest categories account for over 75% of all contributions this week - down 4% from the previous weeks analysis.
2 The effect of downtime on contribution totals
As stated at the beginning of the report, utopian-io suffered an issue which meant the service needed to be taken offline. This caused a large reduction in the contribution totals for the week analysed.
The table above shows the difference in total contributions from this week to the previous.
Of the main contribution categories, 'Analysis' and 'Copywriting' actually increased in contribution totals, gaining 14% and 32% respectively.
All of the other main contribution categories suffered a considerable reduction in figures. Popular categories, 'Ideas' and 'Bug-hunting' fared the worst, losing 80% and 62% respectively.
An 80% reduction in the contribution of 'Ideas' really stands out, and it is possible that many of the contributors to this category are not regularly submitting to utopian-io. The confusion of the utopian-io site not being available may have been enough to put these people off, as they failed to realise that contributions could be made through other front ends.
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, 'Tutorials' and 'Video-Tutorials fared particularly well in terms of approval percentages. 10 from 12 'Video-Tutorial' submissions were approved, and the 1 contribution and approval of the 'Documentation' category (although well done there!), 83% is the best score this week.
6 from 8 'Analysis' contributions were approved, giving a solid approval percentage of 75%. The worst performer this week was 'Ideas' with 24% of submissions being approved. This compounds the poor performance with regards to the fall in total submissions to the category over the last analysis.
4. Contribution Scoring
This data is unavailable for the current weeks analysis due to reasons explained earlier.
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 'Development' has overtaken 'Analysis' with the highest category vote weight average. This is more like the expected result given the rules in place with regards to category scoring!
The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions' at 2.6%, down 0.3% from the previous week.
A 20% vote from @utopian-io in this week of analysis is estimated to be worth around $130.
6. Moderator/Community Manager Statistics
This data is unavailable for the current weeks analysis due to reasons explained earlier.
7. Summary Analysis
This weeks' approval percentage is up 10% on the previous weeks analysis.
This is the best outcome in the previous 2 months of data analysis. It is interesting that the a sharp drop in contributions has caused this marked improvement in accepted contributions. Is it a case of the less frequent, more adhoc contributors (scoring fewer approvals) not submitting as much this week?
Summary
'Copywriting' contributions rose 32% (7 addition submissions totaling 32) from the previous week, despite downtime to the service.
'Development' regains top spot in the vote weight percentage chart.
A tough week for utopian-io and the analyst, as scripts required adjustments to compensate for the change in applications gathering contributions for approval. The utopian.io website was taken offline and the downtime, from the 4/5th May is expected to be 1 month.
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:
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/04/2018 00:00:00' AND '05/10/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_week2
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_week2 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_week2]
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_week2]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)
UPDATE [SteemSQL].[dbo].[utopian_authors_may_2018_week2]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)
-------------------------------
-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_may_2018_week2] order by created asc -- 438 (1006)
select * from [dbo].[utopian_authors_may_2018_week2] where SQL_VOTE = 'Yes' -- 198 (357)
select * from [dbo].[utopian_authors_may_2018_week2] where reviewed_approved is null --
select * from [dbo].[utopian_authors_may_2018_week2] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL) -- 548 (435)
select * from [dbo].[utopian_authors_may_2018_week2] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_may_2018_week2] 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_week2]
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_week2]
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_week2]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc
select * from [utopian_authors_may_2018_week2] where [contribution_type] = 'ideas'
-------------------------------
-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_may_2018_week2]
group by contribution_type
This data was compiled on the 28th May 2018 at 6pm (UCT)
Thanks
Asher @abh12345