Engaged Red Fish Analysis

Repository

Introduction
Scope
Red Fish who were once bigger fish
Red Fish yet to grow larger
Removing the disengaged
Scoring engagement activity
Conclusions
Tools and Scripts

Introduction

There is currently a project under way to try to make 250 new Minnows in one month. This is just one of many initiatives underway to engage, retain, and support the smaller Steem accounts as they find their way on the platform.

It is hoped that the output of this analysis will find Red Fish that are presently engaging and growing which could then be eligible for additional support in contests and growth projects.

This analysis takes a look at:

General Red Fish numbers
Accounts who are Red Fish who used to own more SP
Current Red Fish and their activities
A set of metrics to define who could be most appropriate to push to Minnow status

source

Scope

Red Fish in terms of this section of the analysis are accounts holding between 100 and 500 Steem Power

Looking at the total number of accounts with various SP ranges, the Red Fish are the row highlighted in green.

Including 'Plankton' accounts, the range of account sizes on Steem looks rather bleak:

With Plankton excluded, it is perhaps easier to see why focusing on Red Fish sized accounts as a growth area is a solid approach for the network:

Red Fish who were once bigger fish

Much of the focus is towards the support of smaller accounts, but maybe there is something to learn by looking present Red Fish accounts that used to own more Steem Power than they do now. As it stands, there are 1907 Red Fish sized accounts that have earned over 500 SP.

If we group these account by the month/year that they were created, there are three distinct peaks on the chart.

Another chart immediately springs to mind and it is the price history of STEEM. This chart also contains peaks at around the same time (August 2016, June 2017, January 2018) as the above which could suggest that accounts arrived during these price moves quickly left following the later dips in price. But are they still here?

_source

Of the 1907 accounts currently holding 100-500 SP that were once larger, 800 have either submitted a top level post or a comment to the Steem blockchain in the opening 10 days of March 2019.

This figure of 800 equates to just under 42% of the once larger Red Fish and suggests that a good number of these accounts are still active. If January and February's numbers are included also (allowing for a 70 day maximum break without posting), the percentages rises to almost 64%. These numbers seem to go against popular thinking that many accounts that power-down leave and don't return - it could well be the case that they just sell most of their stake (hopefully at the highs) and then try to build this back up again ready for the next bull run.

It is open to debate as to whether these accounts are the ideal accounts to support via initiatives like the one mentioned in the introduction, and for the scope of the latter stages of this analysis, they have been excluded.

Red Fish yet to grow larger

This section takes a look at the current Red Fish accounts which have yet to earn 500 SP in post rewards.

As a comparison to the previous section, this chart shows the current Red Fish, yet to earn 500 SP in post rewards, grouped by account creation date.

As with the previous chart of this type, there is a strong correlation between the price of STEEM and the number of accounts created (holding 100-500 SP) at a particular date. How about the Red Fish yet to reach higher levels, are they still working towards this goal?

There are currently 12121 Red Fish in this subset, and it is interesting to note that 1996 of these accounts have never written a comment of top level post - 16.5%. The accounts that have are grouped by last post date in the chart below.

During the first 10 days of March, just over 18% of the Red Fish in this subset have made a top level post or a comment. Again, if January and February are included with March, the percentage rises to 31.5%. These percentages are lower than the Red Fish who were once larger, and so one more look at this criteria, but with the exclusion of the Red Fish that have never posted.

With the 'no posts' excluded, the percentages still fall some way short of those held by the Red Fish who were once larger. This is encouraging as a potential investor as these accounts could potentially see value in sticking around as they have experienced holding more Steem Power in the past and a much larger percentage are still engaged.

Looking back on the chart above it is also worth noting that Red Fish were dropping off at a fairly consistent rate through January 2018 - December 2018, at around 450 a month on average. The rapid decline on the STEEM price chart from January - March 2018 seemingly not correlating with this steady drop-off.

Removing the disengaged

The next part of the analysis furthers the criteria assessed above to find a group of Red Fish who are engaged and actively growing their account. Using the base criteria in the analysis scope, there are currently 14126 Red Fish (holding more than 99 Steem Power (SP) and less than 500 SP). In addition to this, the following criteria has been added to narrow down this figure, with a goal to find the most active/engaged.

Account has posted in the past 7 days
Account has made a comment in the past 7 days
Account has posting rewards of less than 500000 (500 SP)
Account has voted for at least 1 witness directly or via proxy
Account has Steem Power owned plus all balances and savings balance that does not exceed 500

This first data-set returns 1645 Red Fish, and they are grouped by account creation date below.

It is surprising to see that there is an account created as far back as May 2016, which matches the criteria above - slow and steady wins the race?

The largest group (shoal?) of Red Fish matching the above criteria arrived in January 2018. This was when the price of STEEM reached it's all time high, and was one of the biggest sign-up months to date. Irrespective of the account creation date, this group of Red Fish are seemingly the mostly suitable to be offered encouragement to grow to the next level.

However, this is still a large number of accounts, and so it would be nice to apply other filters, or indeed score their activities on Steem. One method to do this is detailed in the final part of the report.

Scoring engagement activity

The list of metrics below could be considered as some of the main activities an account would be part-taking to show that they are active and engaged.

Posts (P)
Comments - number of (C)
Number of people spoken to (PS)
Comments - replies to replies - depth > 2 (C R)
Comments - length in characters (C L)
Witness votes (W)
Up-votes to others (V)
Up-votes to different authors (U V)
Self-votes - a small minus score (S V)

The 1645 Red Fish retrieved using the final criteria have been processed against the metrics above and the activity range is the past 7 days. If an account failed to meet any of the above criteria, they would not receive a score for this metric and be passed over.

Following processing, 874 accounts remained. These accounts were then scored using the above metrics as one method to find the most active and engaged Red Fish. The end result looks like this:

Well done to the Red Fish appearing on this list :)

Conclusions

Excluding 'Plankton' accounts, Red Fish account for 56% of the account sizes on Steem.

Red Fish arrived during various STEEM price booms, but it is the Red Fish that were once previously larger that show higher recent activity levels.

Encouraging smaller accounts, particularly those showing good recent activity, could be a good method to grow a larger, more long-standing 'middle-class' of Steem accounts.

Tools / Script

This analysis was produced by gathering data from SteemSQL, a copy of the Steem blockchain data held in a SQL Server database which is managed by @arcange. The scripts were written in Linqpad 5 and the charts produced using MS Excel.


-- Red Fish
select '''', name, ''',', * from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500


-- once bigger fish
select '''', name, ''',', * from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and posting_rewards > 500000

-- created
select cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20)), count(*) from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and posting_rewards > 500000
group by cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20))

-- last post 
select cast(datepart(month,convert(date,[last_post])) as varchar(20))+'/'+cast(datepart(year,convert(date,[last_post])) as varchar(20)), count(*) from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and posting_rewards > 500000
group by cast(datepart(month,convert(date,[last_post])) as varchar(20))+'/'+cast(datepart(year,convert(date,[last_post])) as varchar(20))

-- created < 500 rewards
select cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20)), count(*) from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and posting_rewards < 500000
group by cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20))

-- last post < 500 rewards
select cast(datepart(month,convert(date,[last_post])) as varchar(20))+'/'+cast(datepart(year,convert(date,[last_post])) as varchar(20)), count(*) from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and posting_rewards < 500000
group by cast(datepart(month,convert(date,[last_post])) as varchar(20))+'/'+cast(datepart(year,convert(date,[last_post])) as varchar(20))


-- Red Fish new critera
select '''', name, ''',', * from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and last_post > getdate()-7
and last_root_post > getdate()-7
and posting_rewards < 500000
and (witnesses_voted_for > 0 or proxy <> '' or proxy <> ' ' or proxy is not null)
and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 + cast(left(sbd_balance, len(sbd_balance)-6) as float) + cast(left(balance, len(balance)-6) as float) + cast(left(savings_balance, len(savings_balance)-6) as float) + cast(left(savings_sbd_balance, len(savings_sbd_balance)-6) as float) < 500


-- Red Fish new critera - created
select cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20)), count(*) from accounts
where cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 >= 100 and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 < 500
and last_post > getdate()-7
and last_root_post > getdate()-7
and posting_rewards < 500000
and (witnesses_voted_for > 0 or proxy <> '' or proxy <> ' ' or proxy is not null)
and cast(left(vesting_shares, len(vesting_shares)-6) as float)*499.429/1000000 + cast(left(sbd_balance, len(sbd_balance)-6) as float) + cast(left(balance, len(balance)-6) as float) + cast(left(savings_balance, len(savings_balance)-6) as float) + cast(left(savings_sbd_balance, len(savings_sbd_balance)-6) as float) < 500
group by cast(datepart(month,convert(date,[created])) as varchar(20))+'/'+cast(datepart(year,convert(date,[created])) as varchar(20))


-- Scoring Query 

SET NOCOUNT ON

DECLARE @name as nvarchar(50)
DECLARE @witness_votes as nvarchar(50)
DECLARE @owned_sp as decimal(15,6)
DECLARE @active_sp as decimal(15,6)
DECLARE @no_of_votes int
DECLARE @unique_votes int
DECLARE @self_votes int
DECLARE @posts int
DECLARE @comments int
DECLARE @comments_elsewhere int
DECLARE @removed_upvotes int
DECLARE @all_removed_upvotes int
DECLARE @downvotes int
DECLARE @differentpeople int
DECLARE @comment_length int
DECLARE @comments_partiko int
DECLARE @comments_fastreply int
DECLARE @comment_length_partiko int
DECLARE @comment_lenght_fastreply int
DECLARE @Weekdate DATETIME = DATEADD(day, -7, getutcdate())
DECLARE @steem_per_vest int
set @steem_per_vest = 493.714


DECLARE PEOPLE CURSOR FOR
 
Select a.name as name, 
Case
    WHEN cast(a.proxy as nvarchar(50)) <> '' THEN proxy 
    ELSE cast(a.witnesses_voted_for as nvarchar(50))
    END as Wit_votes
FROM accounts a
WHERE a.name in (
'Accounts HERE'
)
OPEN PEOPLE
FETCH NEXT FROM PEOPLE
INTO @name, @witness_votes--, @owned_sp, @active_sp

PRINT 'Name | Posts | Comments | People |comm_elsewhere | Com_len | Wit_v | Votes | Unique_v | Self_v | Down_v'
PRINT '-|-|-|-|-|-|-|-|-|-'

WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @comments_partiko = (select count(*) from Comments WHERE author = @name AND created > DATEADD(day, -7, getutcdate()) AND depth > 0 and IIF(isjson(json_metadata) = 1, IIF(CHARINDEX('/', json_value(json_metadata, '$.app')) > 0, SUBSTRING(json_value(json_metadata, '$.app'), 1, CHARINDEX('/', json_value(json_metadata, '$.app'))-1),json_value(json_metadata, '$.app')), null) = 'partiko')
SET @comments_fastreply = (select count(*) from Comments WHERE author = @name AND created > DATEADD(day, -7, getutcdate()) AND depth > 0 and IIF(isjson(json_metadata) = 1, IIF(CHARINDEX('/', json_value(json_metadata, '$.app')) > 0, SUBSTRING(json_value(json_metadata, '$.app'), 1, CHARINDEX('/', json_value(json_metadata, '$.app'))-1),json_value(json_metadata, '$.app')), null) = 'fast-reply')

SET @comment_length_partiko = @comments_partiko*96
SET @comment_lenght_fastreply = @comments_fastreply*199

SET @comment_length = (select sum(len(body)) from Comments WHERE author = @name AND created > DATEADD(day, -7, getutcdate()) AND depth > 0)
SET @comment_length = @comment_length-@comment_length_partiko-@comment_lenght_fastreply

SELECT 
    @all_removed_upvotes = SUM(IIF(weight = 0,1,0)),
    @removed_upvotes = SUM(IIF(weight = 0 AND author = @name,1,0)),
    @self_votes = SUM(IIF(author = @name,1,0)),
    @no_of_votes = COUNT(*),
    @unique_votes = COUNT(DISTINCT author)
FROM 
    Txvotes 
WHERE 
    timestamp > @Weekdate
    AND voter = @name 
    
SELECT 
    @posts = SUM(IIF(depth = 0,1,0)),
    @comments = SUM(IIF(depth > 0,1,0)),
    @comments_elsewhere = SUM(IIF(depth > 2,1,0))
FROM 
    comments 
WHERE 
    author = @name 
    AND created > @Weekdate
    
SET @differentpeople = (select count(distinct parent_author) from Comments WHERE parent_author <> @name and author = @name AND created > DATEADD(day, -7, getutcdate()) AND depth > 0  AND len(body)>60)


SET @no_of_votes = @no_of_votes - @all_removed_upvotes - @all_removed_upvotes
SET @self_votes = @self_votes - @removed_upvotes - @removed_upvotes

PRINT '@'+CAST(@name AS VARCHAR) + '|' + CAST(@posts AS VARCHAR) + '|' + CAST(@comments AS VARCHAR) + '|' + CAST(@differentpeople AS VARCHAR) + '|' + CAST(@comments_elsewhere AS VARCHAR) + '|' + CAST(@comment_length AS VARCHAR) + '|' + CAST(@witness_votes AS VARCHAR) + '|' + CAST(@no_of_votes AS VARCHAR) + '|' + CAST(@unique_votes AS VARCHAR) + '|' + CAST(@self_votes AS VARCHAR) /* + '|' + CAST(@downvotes AS VARCHAR) */

    FETCH NEXT FROM PEOPLE
    INTO @name, @witness_votes
END

CLOSE PEOPLE
DEALLOCATE PEOPLE

SET NOCOUNT OFF

Thanks

Asher