@utopian-io: Contribution/Score/Moderator/Vote Analysis - 20th - 26th April 2018

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 20th - 26th April 2018 inclusive.

Contents

General / Assumptions

1. Contributions Summary

2. Categories in focus

3. All contribution Types

4. Contribution Scoring

5. Category Vote Weighing

6. Moderator/Community Manager Statistics

7. Summary Analysis

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

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

---

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, 20th - 26th April 2018 inclusive, the SteemSQL DataBase holds a total of 877 potential contributions to utopian-io. This is 196 more contributions than the previous weeks' total which was 691.

331 of these contributions have been approved, 75 more than the previous weeks' total of 256.

1 contribution was approved but did not receive a vote.

The approval percentage of 38% is a 1% improvement on 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 and controls over half of all contributions at 51%.

'Suggestions/Ideas' holds 2nd place but loses 9% of the total contribution percentage from last week with 13% this time.

'Development' contributions regains 3rd spot and this category accounts for 10% of all contributions this week.

These three largest categories account for 74% of all contributions - the same percentage as the previous week.

---

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

This week, the 'Bug-Hunting' category accounts for more than half of all the contributions to utopian-io.

The chart below shows a sizable increase in contributions when compared to recent weeks - a 40 - 50% rise on the average over this period.

However, and as is often the case, the increase in contributions for a given category does not always mean and improvement in the approval percentage.

The chart above covers the previous 8 weeks' analysis and shows this week to be amongst the lowest as far as approved contributions.

'Development' - Approvals / Rejections

The 'Development' contribution category is back in the top 3 most popular this week, and has long been one of the best as far as approved contribution percentages.

The chart above shows that this week is no different, as the approval % is up 3% on the previous week to 84% and ties in nicely with the average over the past 3 months which is also 84%.

---

3. All contribution types

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

The 'Analysis' category achieved a 100% approval rate in this weeks analysis, although the overall contribution total was lower than average at just 6 contributions.

'Graphics' contribution success rate more than doubled against the previous week, which contained 24 more contributions.

The approval percentage of 'Video-Tutorials' fell 25% this week - 8 additional contributions were submitted than the previous week.

---

4. Contribution Scoring

Contribution scoring took place this week and the table below shows the min, max, and average across the contribution categories.

Four categories achieved the maximum score of 100, and the lowest score across all categories was 26.

'Documentation' received the best average score across all categories, with 'Tutorials' averaging out as the lowest.

---

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.

Development tops the vote weight chart this week and with Analysis not far behind.

The average vote weight of the 'general', non-task related categories was the lowest for 'Ideals/Suggestions' at 2.6% as this category is considered on of the easiest to contribute to.

A 20% vote from @utopian-io in this week of analysis is estimated to be worth around $150.

---

6. Moderator/Community Manager Statistics

This section has appeared in 3 weeks' analysis, a wider data range will be available for assessment in the near future.

Moderator Reviews

Five Moderators approved 100% of the contributions they reviewed this week (two previously), and one of these moderators passed a total of 10 contributions.

Eight moderators passed fewer than 10% of the submissions they looked over (three previously) and the strictest moderator passed 0 from 19 contributions - all of which were 'Ideas'.

Moderator Contributions

15 out of 19 contributions put forward by moderators were approved this week - 79%

Community Manager Reviews

Two Community Managers approved 100% of the submissions they reviewed within this data set - 5 contributions between the pair.

The strictest Community Manager passed 0 from 12 contributions.

Community Manager Contributions

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

---

7. Summary Analysis

This week, the number of contributions has risen against the previous weeks and sparks the end of a long downward trend.

As the chart above displays, this is the first rise in contribution totals in 8 weeks, and perhaps, encouraged by the price in the payment token (STEEM), this is the beginning of a revival in submissions to utopian.

This weeks' approval percentage is up 1% of the previous week, and is firmly in line with percentages over the past month.

---

Summary

An increase in both the approval and contribution figures, a welcome change for utopian-io connections.

A sharp rise in 'Bug-hunting' contributions appeared, but the quality did not increase with the growing numbers submitted.

Overall, a fairly average weeks' data, contributions remain strictly moderated to ensure quality is upheld.

---

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:

-------------------------------
-- 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],'$.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/20/2018 00:00:00' AND '04/26/2018 23:59:59' 
and parent_author = '' and depth = 0 and category = 'utopian-io' and JSON_VALUE([json_metadata],'$.type') IS NOT NULL
and json_metadata <> ''
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_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_april_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_april_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_april_2018_week4]
SET SQL_VOTE = 'Yes' WHERE (WEIGHT <> NULL or WEIGHT > 0)

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


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


-- CHECK THE NUMBERS
select * from [dbo].[utopian_authors_april_2018_week4] -- 877 (691)
select * from [dbo].[utopian_authors_april_2018_week4] where SQL_VOTE = 'Yes' -- 330 (256)
select * from [dbo].[utopian_authors_april_2018_week4] where reviewed_approved is null -- 
select * from [dbo].[utopian_authors_april_2018_week4] where SQL_VOTE = 'No' or (reviewed_approved = 'false' or reviewed_approved is NULL)  -- 548 (435)
select * from [dbo].[utopian_authors_april_2018_week4] where SQL_VOTE = 'No' and reviewed_approved = 'true' -- 1 (Approved then unvoted) 2 Approved not voted)
select * from [dbo].[utopian_authors_april_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_april_2018_week4]
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_week4]
--where reviewed_approved = 'true'
where SQL_VOTE = 'Yes'
group by [contribution_type]
order by [contribution_type] asc

select * from [utopian_authors_april_2018_week4] where [contribution_type] = 'ideas'



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

--SCORES

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

select * from [dbo].[utopian_authors_april_2018_week4] where ISNUMERIC(score) = 1
-------------------------------

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


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

-- NEW MODS OR CMS?
Select distinct moderator from [dbo].[utopian_authors_april_2018_week4] where (moderator not in (select name from mods) and moderator not in (select name from cm))



-- MODERATOR REVIEWS

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



  -- MODERATOR CONTRIBUTIONS

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

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

  -- CM CONTRIBUTIONS

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

---

_{This data was compiled on the 8th May 2018 at 9:30am (UCT)}

---

Thanks

Asher @abh12345