ANALYZING 1,129,657 STEEMIT ACCOUNTS

Repository

https://github.com/steemit/steem

Outline

• Scope
• Results
• Findings and Conclusion
• Tools and Scripts
• Relevant Links and Resources

Scope

Date of the analysis: 5th September 2018
Time frame of the analyzed data: All accounts created on Steemit from inception till 2:23am 5th September 2018 WAT
Components of the analysis: Analyzing accounts on the steemit platform with a view to looking at account activities and user retention.

Results

Steemit was launched in March 2016 and in May 2018 it had it's 1,000,000th registered user. It came with mixed feelings, one was that we were excited that the platform was growing while on the other hand the problem was how many of this accounts were active and contributing.

This analysis seeks to look closer whether was there really an effective progress. As the saying goes, the numbers don't lie

SEPARATING THE FISHES
As at 5th September 2018, we had a total of 1,129,657 accounts on Steemit and this is how the accounts fall under according to accumulated vesting shares. The formula used to get this is:

=IF(vesting_shares>=1000000000,"Whale",IF(vesting_shares>=100000000,"Orca",IF(vesting_shares>=10000000,"Dolphin",IF(vesting_shares>=1000000,"Minnow",IF(vesting_shares>=1,"Red Fish","Dead Fish")))))

The data above is not suprising as it follows the normal distribution curve or "bell curve". It follows a pattern where the numbers are high in the average region and low in the maximum and minimum region. If i stop here it would make no sense or meaning to data analysis, but looking further deeper we might be able to see some interesting data.

THE DEAD FISH

• The dead fish as the name implies are the dead steemit accounts, they hold a share of the total vest of 0.0000001% and account for 3.05% of all accounts
• 56.52% of dead fish accounts were created in 2016, 38.38% of accounts were created in 2017 and 5.10% in 2018.
• 53.03% of dead fish accounts have never posted while 26.57% last posted in 2018.
• They have made a total of 743,788 post with an average of 45 posts for accounts that have made at least one post
• Average voting power stood at 92.2%
• 66.41% of dead fish accounts have the default reputation of 25 while 15.95% have a reputation 24 and less
• They hold a total steem power of 92K

THE RED FISH
This is the largest group of accounts. Most steemit accounts fall under this category.

• The Red fishes make up 95% of accounts but only hold a total of 3.44% of vesting shares on the platform
• 9.28% of red fish accounts were created in 2016, 37.12% in 2017 and 53.60% in 2018
• 53.51% of red fish accounts have never post while 32.55% last posted in 2018
• They have made 40M posts with an average of 80 post per user for accounts with one post and above
• Average voting power was 96.7%
• 80.8% of accounts have a default reputation of 25 and an average reputation of 27.08
• They hold a total steem power of 16.4M with an average steem power of 15.

THE DOLPHINS

• The Dolphins make up 0.15% of total accounts and hold a 12.06% of total vesting shares
• 34.59% of accounts were created in 2016, 45.29% were created in 2017 and 17.11% were created in 2018
• 18.20% of dolphins have never posted while 74.61% last posted in 2018
• They have made a total of 5.2M post with an average of 3.6K for users who have made at least one
  post on Steemit
• Their average voting power was 88.88%
• .Only 23% of dolphin accounts have the default reputation of 25. Their average reputation is 51.2
• Their total steem power is 44.2M with an average steem power of 25.2K

THE ORCAS

• The Orcas make up a total of 0.025% of all accounts and hold an accumulated 18.24% of all vesting shares on Steemit
• 56.49% of orcas were created in 2016, 29.82% were created in 2017 and 13.68%
• 23.86% of orcas have never posted and 68.77% last posted in 2018.
• They have made a total of 953K post. The average post per account is 4.3K for users with one post and above
• Average voting power was 88.93%
• 27.37% of orca accounts have the default reputation score of 25 and average reputation of 51.49
• Total steem power stood as 39.8M with an average steem power of 139.9K

THE MINNOWS

• The Minnows make up a total of 0.77% of all accounts and hold vesting shares of 12.06% of the total vesting share
• 26.48 of minnnow accounts were created in 2016, 54.28% were created in 2017 and 19.23% were created in 2018
• 18.88% of minnow accounts have never posted and 72.01% last posted in 2018
• They have a total of 14.6M post with an average of 2.06K posts for accounts that have made at least one post on steemit
• Average voting power was 90.4%
• 23.78% of minnows have the default reputation of 25 and all accounts had an average reputation of 47.8
• Total steem power they hold is 20.8M with an average steem power of 2.3K

THE WHALES
The whales are the top on the platform, they have the steem power, they have the reputation, i am tempted to say they have everything going on well for them. They are at the top of the food chain (they are what economist call the "top 1% of the 1%").

• The Whales make up of only 0.003% of accounts but a hold a massive 59.84% of all vesting shares
• No Whale account was registered in the year 2018. 32 (88.88%) out of the 36 whale accounts were created in 2016 (the inaugural year of Steemit)
• 33.3% of whale accounts have never posted while 44.4% of whales accounts last posted in 2018.
• Whales have made a total of 25.6K with an average of 1,070 post from 24 whales that have made at least one post on steemit
• Their average voting power as the point of this analysis was 94.1%
• 36.1% of Whales have the default reputation of 25. The average reputation is 47
• Their total steem power is 70.4M steem power and their average steem power is 1.9 Million and only one whale has never voted.

Findings and Conclusion

Progress for 2018 started on high note but has gradually slowed down with the impact of steem loosing value by more than 70%. Account creation numbers has dropped, payout has dropped, posting activities are fluctuating up and down. Progress on steemit takes time as we see the must successful accounts on steemit were created in 2016. But most of the most successful accounts (whales) are official accounts not general user account.

The retention level for 2018 is high with an average of 56.6% of accounts posting at least once in 2018.

There is more to be desired but the progress so far cannot be under rated. Some areas are lacking and there is a sense of unfairness and complain from the users at the bottom of the food chain. But with the new hard-fork and Smart Media Token coming in, many of those issues will be addressed.

Tools and Scripts

Microsoft SQL Server Management Studio 17 was used to access the data from STEEMSQL ( a publicly managed database by @arcange )

Microsoft Excel 2016 was used to plot graphs and Charts.

SELECT
name,
created,
vesting_shares,
last_post,
post_count,
voting_power / 100 as VotingPower,
CONVERT (int, (SELECT MAX (v) FROM (VALUES(log10(ABS(CONVERT(bigint, reputation)-1)) -9), (0)) T(v)) * SIGN (reputation) * 9 + 25) as Reputation,
    convert(decimal(20,0), 487 * (convert(decimal(20,4), left(Accounts.vesting_shares, len(Accounts.vesting_shares)-5)) +
    convert(decimal(20,4), left(Accounts.received_vesting_shares, len(Accounts.received_vesting_shares)-5)) +
    -convert(decimal(20,4), left(Accounts.delegated_vesting_shares, len(Accounts.delegated_vesting_shares)-5))) / 1000000) as [SteemPower]
FROM ACCOUNTS (NOLOCK)

Proof of Authorship

https://github.com/jingis07