@utopian-io: Contribution/Score/Moderator/Vote Analysis - 6th - 12th April 2018

This is an analysis of the Contribution types, Scores, Moderators, and @utopian-io votes for the 6th - 12th April 2018 inclusive.

Contents

General / Assumptions

1. Contributions Summary

2. Categories in focus

3. All contribution Types

4. Contribution Scoring

5. Moderator/Community Manager Statistics

6. Summary Analysis

7. 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.

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

---

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 with regards to approval percentages and number of Contribution Types being submitted.

---

1. Contributions Summary

For the week, 6th - 12th April 2018 inclusive, the SteemSQL DataBase holds a total of 763 potential contributions to utopian-io. This is 118 fewer contributions than the previous weeks' total which was 881.

293 of these contributions have been approved, 54 fewer than the previous weeks' total of 347.

1 contribution has been approved but has not received a vote.

The approval percentage of 38% is a 1% fall from 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' contributions category is the largest this week with 37% of all contributions.
This is a 9% increase on the previous week, in which 'Ideas/Suggestions' were the largest contributor. This week, 'Ideas/Suggestions' falls back to 2nd place with 31% of all contributions, down 4% from the previous week.

'Development' contributions are a new entry into 3rd spot this week, forming 11% of all contributions - gaining 1% of the share since last week.

These three largest categories account for over three-quarters (79%) of all contributions offered.

---

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

The 'Bug-Hunting' category is now the largest contribution category to utopian-io and contributions rose by 33 over the previous week.

As we can see from the chart below, the contribution totals for each week have remained fairly steady for the past 4 weeks, and the rise to the most popular category is likely due to the reduction of contributions in other categories.

'Ideas/Suggestions' which held the market share for the past month fell back into 2nd place as the number of contributions to the category fell 71 from the previous week

The approval percentage for 'Bug-Hunting' tells a different story to the solid contribution figures as this % has declined quite rapidly over the past 6 weeks.

This decline is picture in the chart above which, apart from the last week in March, a consistent decline in approval rates. 24% has been sliced of the approval % in this time period, which does look quite concerning for the category.

'Development' - Approvals / Rejections

A week rarely passes without the 'Development' category setting new records, and this week is no different at the category sees an approval percentage of 91%.

This is an excellent figure and one for the contributors to the 'Development' category to be proud of. As an added bonus, this week the recently declining submission count has risen by 3 to 87.

---

3. All contribution types

The following table shows all types and totals of contributions submitted, with their approval percentage in the end column.

In the same week as the 'Development' category bettered a 90% approval percentage, 'Video-Tutorials' scored an even higher figure which is just shy of 93%. However, only 14 contributions were submitted for this category, a new low in recent times.

'Ideas/Suggestions' only achieved a 22% approval percentage, an 8% fall on the previous week. As discussed earlier, this is on the back of 71 fewer contributions this time.

With an 80% approval percentage, 'Analysis' looks to be in a good position, but this week only 5 contributions were submitted for this category, a new low since the project began in earnest late last year.

---

4. Contribution Scoring

A new section this week following recent changes to the contribution review process.

From a recent @utopian-io update:

Adding a score to a contribution can be done by either a moderator and project owner. Scoring is not mandatory, thus not every contribution may get a score. All contributions are immediately rewardable by the community as they enter the feeds immediately without moderators having to accept.

The score is used internally by the system to filter contributions in order to have a cleaner database of contents, for the benefit of contributors, project owners and curators.

It is true that contributions which have no score won't ever get an Utopian upvote thus remarking the fact the Utopian upvote is never guaranteed.

This data will be collected starting this week, and in future 'Scores' will be analysed against previous results.

From the datasheet below, we can see that the Scores range from 1 - 100.

6 categories received the highest score, whilst submissions in 'Development' and 'Ideas' received the lowest score of 1.

'Ideas' averaged the lowest Scores of the more popular categories with 25.

It is worth mentioning that this week, every contribution with a positive score (and reviewed = 'true') received a @utopian-io vote.

---

5. Moderator/Community Manager Statistics

Another new section in this analysis is a look at how the Moderators and Community Managers are doing with regards to moderating and contributing to utopian-io. Again, this data will be compiled starting this week and summary analysis will take place once a more complete set of data is gathered.

Moderator Reviews

4 Moderators approved 100% of the contributions they reviewed this week, while 2 moderators passed fewer than 10% of the submissions they looked over. One of these moderators accepted 0 from 2 reviews, but perhaps the strictest reviewer was the one accepting only 8 from a total of 88.

Moderator Contributions

18 out of 21 contributions put forward by moderators were approved this week - 86%

Community Manager Reviews

2 Community Managers approved 100% of the submissions they reviewed within this data set.

The strictest Community Manager passed 0 from 7 contributions.

Community Manager Contributions

3 from 3 contributions submitted by Community Managers were approved this week.

---

6. Summary Analysis

This week, the number of contributions has fallen yet again. This marks another year low in totals, both submitted and approved.

The number of approved contributions this week is down almost 90% from the total accepted in the final week of January. From 2747 accepted contributions, down to 292 in 11 weeks - wow!

Previously:

Over the past week there has been some hope that the price of the token is stabilizing, and if this is the case we could expect the contribution totals to level off at around 800 - 100 for the coming weeks.

The recent stabilization in STEEM price has not been enough to keep the contribution totals above 800, and this analyst continues to scratch his head and wonder when these numbers are going to stop falling.

This weeks' approval percentage is only slightly lower than the previous week, which gained 3.5% on the week prior to that.

The past 3 weeks' percentages have been closely tied, but on average, are 30% lower than those achieved at the beginning of March.

---

Summary

The contribution total, accepted contributions, and the approval percentage all fell this week.

'Development' performed excellently along with 'Video-Tutorials' which both gained approval percentages of over 90%.

---

7. 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
-------------------------------

-- TAGS and Authors
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 int
Declare @influence int
Declare @staffpick Varchar(50)
Declare @utopian_vote Varchar(50)
Declare @sql_vote Varchar(50)
Declare @weight int
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/06/2018 00:00:00' AND '04/12/2018 23:59:59' 
and parent_author = '' and depth = 0 and category = 'utopian-io' and JSON_VALUE([json_metadata],'$.type') IS NOT NULL
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_april_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_april_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_april_2018_week2]
SET WEIGHT = (select TOP 1 [weight] FROM [SQL.STEEMSQL.COM].[DBSteem].[dbo].[Txvotes] 
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_april_2018_week2]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)

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

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

-- TO DO


-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_april_2018_week2] -- 763 (881)
select * from [dbo].[utopian_authors_april_2018_week2] where SQL_VOTE = 'Yes' -- 293 (347)
select * from [dbo].[utopian_authors_april_2018_week2] where SQL_VOTE = 'No' and reviewed_approved = 'false'  -- 469 (534)
select * from [dbo].[utopian_authors_april_2018_week2] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved not voted)
select * from [dbo].[utopian_authors_april_2018_week2] where SQL_VOTE = 'Yes' and reviewed_approved = 'false' -- 2! (Approved but later rejected)


-- RAW DATA FOR EXCEL
select contribution_type as Category, reviewed_approved as Approved from [dbo].[utopian_authors_april_2018_week2]
where contribution_type is not NULL
order by [contribution_type] asc


-- COUNT WHEN APPROVED
select contribution_type, count(contribution_type) from [dbo].[utopian_authors_april_2018_week2]
where reviewed_approved = 'true'
group by [contribution_type]
order by [contribution_type] asc



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

--SCORES

select contribution_type, 
max(cast(score as INT)) as max_score,
min(cast(score as INT)) as min_score, 
avg(cast(score as INT)) as avg_score 
from [dbo].[utopian_authors_april_2018_week2]
where reviewed_approved = 'true'
group by contribution_type


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


-- MODERATOR CONTRIBUTIONS

 SELECT author,
    --  count(author) as total,
        approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week2] uto1     
        WHERE uto1.author = uto.author and reviewed_approved = 'true'),
        rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week2] uto1 
        WHERE uto1.author = uto.author and reviewed_approved <> 'true')
  FROM [dbo].[utopian_authors_april_2018_week2] uto
  where author in (select name from [dbo].[mods])
  group by author


-- MODERATOR MODS

 SELECT moderator,
    --  count(moderator) as review_total,
        approved = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week2] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
        rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week2] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
  FROM [dbo].[utopian_authors_april_2018_week2] uto
  where moderator in (select name from [dbo].[mods])
  group by moderator

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

  -- CM CONTRIBUTIONS

 SELECT author,
    --  count(author) as total,
        approved = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week2] uto1     
        WHERE uto1.author = uto.author and reviewed_approved = 'true'),
        rejected = (SELECT COUNT(author) FROM [dbo].[utopian_authors_april_2018_week2] uto1 
        WHERE uto1.author = uto.author and reviewed_approved <> 'true')
  FROM [dbo].[utopian_authors_april_2018_week2] 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_april_2018_week2] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved = 'true'),
        rejected = (SELECT COUNT(moderator) FROM [dbo].[utopian_authors_april_2018_week2] uto1 WHERE uto1.moderator = uto.moderator and reviewed_approved <> 'true')
  FROM [dbo].[utopian_authors_april_2018_week2] uto
  where moderator in (select name from [dbo].[cm])
  group by moderator

---

_{This data was compiled on the 17th April 2018 at 10:30am (UCT)}

---

Thanks

Asher @abh12345

---

Posted on Utopian.io - Rewarding Open Source Contributors

---