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 1st - 7th June 2018 inclusive.

Contents

General / Assumptions

1. Contributions Summary

2. 'Graphics' - The new most popular category

3. All contribution Types

4. Category Vote Weighing

5. Summary Analysis

6. 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 over 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 1st - 7th June, contributions were made using alternative interfaces than utopian.io. Due to this, 'review' data including 'scores' are not included and approvals 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 - Week 4 - Week 5

---

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, 1st - 7th June 2018 inclusive, the SteemSQL DataBase holds a total of 410 potential contributions to utopian-io. This is 203 fewer contributions than the previous weeks' total which was 613.

This is the 5th week of analysis in which the utopian.io application was not used to submit approvals.

242 of these contributions have been up-voted, 70 fewer than the previous week's total of 312.

The voted on/approval percentage of 59% is a 10% rise on the previous analysis.

---

The Contributions offered can be any one of the following types:

Note: 'Translation' contributions are currently on hold (1st - 7th June 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 first time in two months, we have a new lead category as far as contribution totals stand. 'Graphics' has taken the top spot, over 'Bug-hunting' in 2nd, and 'Development' in 3rd.

These three largest categories again account for 72% of all contributions - A 10% fall on the previous weeks analysis.

Midway through this analysis week, the 'Bug-hunting' category underwent a rule change:

Bug Hunting on Request Only - In order to provide a better service for Open Source Project Owners, the Bug Hunting category is now accepting contributions on request only. This means, contributions in this category will be rewarded only if they are linked to a task request or are being submitted to projects present in our Whitelist. source

And so it is for this reason that the category has seen much fewer contributions this week, and it is expected that the number will fall again next time.

---

2 'Graphics' - The new most popular category

For the past couple of months, 'Bug-hunting' has dominated the contribution numbers, but due to a rule change during this week's analysis, the contributions to this category were drastically reduced, and 'Graphics' benefited from this.

For the first time since the beginning of April, the number of 'Graphics' contributions has totaled over 100. Normally when there is a reasonable increase in the number of contributions in a category, the approval percentage suffers. However, this has not been the case for 'Graphics' and the 80% approval percentage for this week of analysis is in fact the highest approval percent of the year so far.

This leads the analyst to believe that the rules in place are both fair and clear for this category, allowing for an increase in contributions, whilst maintaining quality of work.

---

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, there were no categories that fell below one in five contributions receiving approval. 'Bug-hunting' fared the worst, and seemingly the rule change was missed by some contributors as they submitted bugs that were unrelated to a Task Request.

The 'Graphics' category had an excellent week with a good rise in contributions and an approval percentage better than four out of every five.

Of the main contribution categories, 'Analysis' (7), 'Video-tutorials' (9), and 'Documentation' (3) all obtained 100% approval. Whilst the number of submissions is fairly low for each category, this is still an excellent effort from the contributors.

'Task-development' and 'Task-graphics' also achieved a 100% approval rate which is a good indication of the clear process involved when detailing and executing Task requests.

---

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.

This week, 'Analysis' is again the category receiving the highest vote weight. The gap between this category and 'Development' in 2nd has grown over 5% since the previous week's analysis, and this is a reasonable move. In the utopian-io scoring system, it is generally assumed that 'Development' would top the score charts/vote weight %, but this is clearly not the case at present.

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 8 weeks. It's clear that 'Analysis' has made a substantial move this week, and perhaps the 'Staff pick' and low number of contributions (in comparison to 'Development') has had a role to play here. 'Graphics' has overtaken 'Blog' and 'Video-tutorials' and moved into the the 3rd with regards to vote weight average.

---

5. Summary Analysis

The number of contributions has seen a huge drop this week. The beginning of the summer for the northern hemisphere, decline in the price of STEEM, and probably the largest factor - the adjustment of the 'Bug-hunting' rules have all likely had a say in this decline.

A 10% rise in the overall contribution approval percentage an excellent result, and is the best overall approval % since the beginning of March this year.

---

Summary

Contributions fall to a new low for the project, mainly due to a rule change in the 'Bug-hunting' category.

The approval percentage is the highest since the first week of March 2018.

'Analysis' tops the average vote weight for the 2nd week in a row.

Three main categories received a 100% approval rate this week, along with three sub-categories (Task Requests).

---

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 '06/01/2018 00:00:00' AND '06/07/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_june_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_june_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_june_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_june_2018_week1]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)

UPDATE  [SteemSQL].[dbo].[utopian_authors_june_2018_week1]
SET SQL_VOTE = 'No' WHERE (WEIGHT IS NULL or WEIGHT = 0)

-------------------------------

-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_june_2018_week1]  order by created asc -- 410 (631)
select * from [dbo].[utopian_authors_june_2018_week1] where SQL_VOTE = 'Yes' -- 242 - 312 (319) (326)
select * from [dbo].[utopian_authors_june_2018_week1] where reviewed_approved is null -- 
select * from [dbo].[utopian_authors_june_2018_week1] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL)  -- 548 (435)
select * from [dbo].[utopian_authors_june_2018_week1] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_june_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_june_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 2nd WEEK MAY
select contribution_type as Category, SQL_VOTE as Approved from [dbo].[utopian_authors_june_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_june_2018_week1]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc

-----------------------------

-- VOTE WEIGHT
select contribution_type, avg(cast(WEIGHT as INT)) from [dbo].[utopian_authors_june_2018_week1]
group by contribution_type

---

_{This data was compiled on the 21st June 2018 at 8 am (UCT)}

---

Thanks

Asher @abh12345