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 18th - 24th May 2018 inclusive.
Contents
General / Assumptions
Contributions Summary
Everyone is 'Bug-Hunting'
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 18th - 24th 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 - Week 3
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, 18th - 24th May 2018 inclusive, the SteemSQL DataBase holds a total of 767 potential contributions to utopian-io. This is 43 more contributions than the previous weeks' total which was 724.
This is the 3rd week of analysis in which the utopian.io application was not used to submit approvals.
319 of these contributions have been up-voted, 7 fewer than the previous weeks total of 326.
The voted on/approval percentage of 42% is a 3% fall on the previous analysis.
The Contributions offered can be any one of the following types:
Note: 'Translation' contributions are currently on hold (18-24th May 2018)
This weeks data, split into Contribution types is shown in the pie chart below:
Key ordered by largest to smallest contribution category
for the 7th week in a row, 'Bug-hunting' is the largest contribution category (492 submissions) with 64% of all contributions.
The 2nd and 3rd most popular categories are still 'Graphics' and 'Development' which have swapped places this week. Both categories have gained 1% of the overall contribution share.
These three largest categories again account for 85% of all contributions - a tie with the last analysis as the largest percentage since this statistic started being tracked.
2 Everyone is 'Bug-Hunting'
It is hard to ignore the impressive rise in contributions to this category over the past 5 months. In January and February the weekly total of submissions for 'Bug Hunting' sat comfortably below 200. In the 2nd week of March, the 'Translations' category was put on hold, and it is here we see contributions for 'Bug-Hunting' cross over 200 for the first time.
As we progressed further into March and April, with the hold on 'Translations' looking to remain that way for a while, the submissions to 'Bug-Hunting' started to rise sharply, and this can be seen in the chart below. The final week of April and the first week of March are particularly noticeable. And perhaps, if it wasn't for the downtime and switch to alternative interfaces, March week 2 (orange on the chart) would have continued this upward trend.
How have the approval percentages for 'Bug-Hunting' fared over the past 5 months? Over the first couple of months of 2018, approval %'s for the category often landed above 50%, 67% and 59% figures were achieved in the first and last week of January.
As we move further into the year, the approval percentages have gradually fallen and for the 1st and 4th week of may (this week), the approval percentage has been lower than 30%.
The increasing number of contributions has had the opposite effect with regards to the approval percentage.
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, a number of categories scored well in approval percentage. 'Development', 'Video-tutorials', 'Task-development', 'Analysis', and 'Blog' all achieved an approval % that saw more than 4 of every 5 contributions in these categories being approved.
'Ideas' continued to suffer as a category with just 3 approvals out of 16.
Despite solid approval percentages for the 'Analysis' category over the past month, the total submissions have been fairly static in number. 8, 8, 7, and 7 are the figures for the past four weeks. Considering the healthy vote weight offered for contributions of this type, these figures are 80-90% lower than 'Development', the category with the closest vote weight. Can we see an increase in contributions to this category over the coming months?
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' just pips 'Analysis' as the category receiving the highest vote weight, but the categories are closely matched.
The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions' at 3.05%, up 1.65% from the previous week, which is a noticeable rise for the 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 6 weeks. This week shows a high for the 'Documentation' category, but this % is for just one approval, distorting the figures slightly. After rising for 5 weeks in a row, the average vote weight for 'Blog' has dipped slightly this week.
5. Summary Analysis
The number of contributions has risen slightly again this week as contributors are seemingly getting used to the alternative submission applications following the downtime suffered at the start of the month.
This weeks' approval percentage is 3% lower than last week. However, as shown above, many of the contribution categories have had a good week in this regard. 'Bug-Hunting', with almost 2/3rd of all contributions and a low approval rate is, weighing down the overall approval percentage.
Summary
Contributions are up again, but the approval percentage falls 3%.
An excellent week for 5 categories though as 'Ideas' and 'Bug-Hunting' in particular weigh the overall approval % down.
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/18/2018 00:00:00' AND '05/24/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_week4
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_week4 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_week4]
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_week4]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)
UPDATE [SteemSQL].[dbo].[utopian_authors_may_2018_week4]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)
-------------------------------
-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_may_2018_week4] order by created asc -- 767 (724)
select * from [dbo].[utopian_authors_may_2018_week4] where SQL_VOTE = 'Yes' -- 319 (326)
select * from [dbo].[utopian_authors_may_2018_week4] where reviewed_approved is null --
select * from [dbo].[utopian_authors_may_2018_week4] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL) -- 548 (435)
select * from [dbo].[utopian_authors_may_2018_week4] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_may_2018_week4] 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_week4]
where contribution_type is not NULL --and reviewed_approved = 'true'
order by [contribution_type] asc
-- RAW DATA FOR EXCEL - AFTER THE CRASH - USED FOR 4th WEEK MAY
select contribution_type as Category, SQL_VOTE as Approved from [dbo].[utopian_authors_may_2018_week4]
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_week4]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc
--select * from [utopian_authors_may_2018_week4] where [contribution_type] = 'ideas'
-------------------------------
-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_may_2018_week4]
group by contribution_type
This data was compiled on the 9th June 2018 at 11 am (UCT)
Thanks
Asher @abh12345