Source: Pixabay.com
Repository
https://github.com/steemit/steem
Introduction
In this analysis, i would be breaking down the activities of the steemit accounts created in the month of June 2017. Looking at their progress one year after joining the platform.
Looking out for those who have been able to swim through the tides of the steemit ocean, those who gave up along the line and who didn't even make an attempt at trying.
Outline
- Scope
- Results
- Findings 1
- Findings 2
- Findings 3
- Conclusion
- Tools and Scripts
Scope
Date of the analysis: 5th July 2018
Time frame of the analyzed data: June 2017 to June 2018
Components of the analysis: Analyzing user account activities over a one year period
Results
USER RETENTION
In the month of June 2017 a total of 64,802 accounts were created, that corresponded with increase in the price of steem which caused a boom in steemit activities. This accounts have posted a total of 7,762,451 times
The previous month of May 2017 had ~20,000 accounts created thus accounting for a more than 200% increase in accounts created. After filtering the data in Microsoft Excel using pivot tables, this is what i discovered
29,030 accounts have never posted anything on steemit
11,503 accounts last posted one year ago in June 2017 (their month of registration)
2,028 accounts last posted in January 2018
In the last 90 days (April 2018 to June 2018) only 4,213 accounts representing 6.50% have made a post out of the ~64k that registered in June 2017
In the last 90 days (April 2018 to June 2018) only 4,448 accounts representing 6.86% have voted on a post out of the ~64k that registered in June 2017
ACCOUNT CATEGORIZATION
To get the value of vesting_shares and remove the text VESTS at the end, I used the
REPLACEfunction in ExcelTo categorize by vesting_shares, I used the formula:
=IF(G:G>=1000000000,"Whale",IF(G:G>=100000000,"Orca",IF(G:G>=10000000,"Dolphin",IF(G:G>=1000000,"Minnow",IF(G:G>=1,"Red Fish","Dead Fish")))))
WHAT ARE VESTS?
Vests are shares in the Steem Power Fund. If a user takes no action on the Steemit Platform, his/her shares will remain static in quantity, just like shares in a financial fund.
Vest can give us idea into the volume of activities in a steemit account. So categorizing accounts by VESTS by tell us how far a user has come.
Is there a direct proportional correlation between vest and reputation? NO
ANALYZING REPUTATION
- To get the simplified reputation score
CONVERT (int, (SELECT MAX (v) FROM (VALUES(log10(ABS(CONVERT(bigint, reputation)-1)) -9), (0)) T(v)) * SIGN (reputation) * 9 + 25) AS Reputation
47,035 representing 72% of accounts remain with the default reputation of 25, this shouldn't be surprising considering the high number of accounts that have never posted
Only 2,777 accounts have reputation of 50 and above
There is no direct relationship between age of account and reputation.
Most competition/contest on steemit, they mostly require accounts with reputation 40 and above to weed out Bot accounts and automated posting services. Going by that criteria, only 7,431 representing 11.46% of accounts under review make that requirement
POSTING ACTIVITIES
29,030 of accounts representing 44.78% of accounts created in June 2017 have made 0 post
While 6,368 representing 9.82% of accounts created in June 2017 have only 1 post
This translates that more more than 50% of accounts created in June 2017 have made 1 or less post
Only 17,783 accounts created 10 post or more
Findings 1
The retention/active users is just above ~6% from the large pool of registered users.
Findings 2
Bots or automated posting accounts contributed more to the high number of post. Accounts such as 
Findings 3
User growth in rank is quiet slow as most are still stagnant and below reputation 40, but reputation should not be mistaken for retention as an upvote of about $40 can push one from default reputation 25 to 40 but it can give us an idea over time as from reputation 50 increasing is difficult so it would take quiet some rewards to increase
Conclusion
Many people run multiple accounts for different reasons and that can contribute to the large volume of inactive accounts. I read a comment where someone said he open accounts for his children because he wanted them to be in the first MILLION while some others run multiple account so as to hide their activities (mostly financial) as a proxy account.
There are more "abandoned" accounts than accounts opened for reasons listed above and others. I think steem/steemit can be complex for most people to understand. I sometimes talk at seminars on Steemit and people just tend to not understand the whole concept of crypto and blockchain. Others get super excited immediately you give drop the idea to them and after few days, they get bored and less interested.
The retention numbers from steemit is not good for a social media platform, greater support should be channeled to new users to support them answer their numerous questions and things bothering them. You might say there is already a steemit white paper to do just that, but from my experience people barely read that and prefer to talk to a "human being" directly. When i joined steemit i had to find my way by myself and it wasn't funny
Tools and Scripts
Microsoft SQL Server Management Studio 17 was used to access the data from STEEMSQL ( a publicly managed database by 
Microsoft Excel used to plot graphs and Charts.
SCRIPTS
SELECT
name,
created,
post_count,
last_vote_time,
last_post,
vesting_shares,
CONVERT (int, (SELECT MAX (v) FROM (VALUES(log10(ABS(CONVERT(bigint, reputation)-1)) -9), (0)) T(v)) * SIGN (reputation) * 9 + 25) as Reputation
FROM ACCOUNTS (NOLOCK)
WHERE accounts.created BETWEEN '2017-06-01' AND '2017-07-01'