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 25th - 31st May 2018 inclusive.

Contents

General / Assumptions

1. Contributions Summary

2. 'Video Tutorials' make good progress

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 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 25th - 31st May, 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

---

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

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

312 of these contributions have been up-voted, 7 fewer than the previous weeks total of 319.

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 (25th - 31st 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 8th week in a row, 'Bug-hunting' is the largest contribution category (379 submissions) with 60% of all contributions - 113 fewer contributions and a 4% less overall share than the previous week

The 2nd and 3rd most popular categories are still 'Development' and 'Graphics' which have swapped places again this week. Both categories combined have gained 1% of the overall contribution share, as they did previously also.

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

---

2 'Video Tutorials' make good progress

In recent months, the overall approval percentage of contributions to utopian-io have fallen. One category that has not followed this trend though is 'Video Tutorials'.

Over the past 12 weeks, the average total of contributions for this category each week has been 24, and on average, 15 of those 24 have been approved.

In percentage terms, this looks better each month moving forward. In March, 49.5% of contributions to 'Video-Tutorials' were approved, and in April this rose to 76.75%. May is the best month so far for the category with 78.25% of all contributions being approved.

The total contributions do not match the figures of old, but the approval percentages show encouraging signs that the quality of contributions to 'Video-Tutorials' is of a high standard.

---

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 four contributions receiving approval. 'Development' fared best of the popular contribution categories, and 'task-development' supported projects with all 7 submissions being approved.

Contributions to 'Ideas' stepped up markedly on last week with a 44% approval rate, up 26% on the previous week.

'Analysis' submissions fell to just 4, with 2 approvals. Work is needed here to encourage more contributions to this category.

---

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.

'Analysis' turns the tables with 'Development' as the category receiving the highest vote weight. This is likely to be due to the huge difference in the number of contributions and approvals in each category.

'Bug-hunting' has replaced 'Ideas' as the category with the lowest vote percentage average of the more popular contribution categories. The vote weight average for 'Ideas' as tripled over the last three weeks, which is a good sign for contributors to this 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 7 weeks. This week shows a high for the 'Video-tutorials' category which has steadily risen to surpass 'Graphics' as the 3rd highest average vote weight. 'Graphics' has also been overtaken by 'Blog' for which the average vote weight percentage has grown almost 500% in the past 7 weeks - impressive.

---

5. Summary Analysis

The number of contributions has fallen quite sharply this week. The only conclusion is that the price of STEEM has had an effect on the submissions as it has continued to fall over the past few months.

An 8% upswing in the overall contribution approval percentage is a welcome relief, and 49% is the best approval percentage total since the the 1st week of March.

---

Summary

Contributions fall but the approval percentage is the best in months.

'Blog' becomes the 3rd highest average vote weight category, behind 'Development', and 'Analysis' - which tops the list.

However, just 4 contributions and 2 approvals to the 'Analysis' category - a new low.

No categories fared particularly badly this week as far as approval % went, as they all achieved a better strike rate of 1 in 3.

---

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/25/2018 00:00:00' AND '05/31/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_week5
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_week5 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_week5]
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_week5]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)

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


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


-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_may_2018_week5]  order by created asc -- 631 (767)
select * from [dbo].[utopian_authors_may_2018_week5] where SQL_VOTE = 'Yes' -- 312 (319) (326)
select * from [dbo].[utopian_authors_may_2018_week5] where reviewed_approved is null -- 
select * from [dbo].[utopian_authors_may_2018_week5] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL)  -- 548 (435)
select * from [dbo].[utopian_authors_may_2018_week5] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_may_2018_week5] 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_week5]
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_week5]
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_week5]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc

--select * from [utopian_authors_may_2018_week5] where [contribution_type] = 'ideas'


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


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

---

_{This data was compiled on the 15th June 2018 at 7 am (UCT)}

---

Thanks

Asher @abh12345