Account Rentention: Looking at accounts from June 2017

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 REPLACE function in Excel

• To 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?

A definition from @hisnameisolllie

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 @minnowbooster and @cryptomonitor . This finding is limited as tracking of such accounts is difficult as they are used for different purposes

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 @arcange )

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'

Proof of Authorship

https://github.com/jingis07