It’s been a while since we visited the data retaining to Retention on Steemit. After bumper months of user registration in December, January and February, we are set to have another record month in March. But with all of these new users – how many are sticking around?
In addition to having bumper months for user registration, we have also experienced a pump on both the price of STEEM and the price of SBD. Now that this is subsiding could we see a decrease in retention?
Aim of Analysis
The Aim of this analysis is to carry out an in-depth study of retention on Steemit
We will analyse overall retention, author retention and voter retention.
By the end of this analysis and report we should have an accurate calculation of the retention rates on Steemit.
To see if the reduce price of both SBD and STEEM has effected the retention.
For more details on the data and the query please see the end of this post.
The Analysis
The top chart above shows the number of new users registering on Steemit for each month in each year. Below this we can see the age of the accounts on Steemit. From these we can see that around 300 days ago, so May – June 17 there was an increase in the number of accounts. We can also see a further increase around 120 days ago, in Dec 17.
Overall Activity
Overall Activity includes posting, commenting and also voting.
There are 845K accounts on Steemit, all of which have been active at some stage. Active by way of votes or posts or comments.
Almost 28% of accounts have been active in the last 30 days. 16% in the last 31-60 days and 56% have not been active in the last 60 day.
As the above data also include accounts create both this and last month, these accounts distort the level of retention. To adjust for this I have further filtered the data to exclude any accounts set up after 1 Feb 2018.
With this further filter added to the data we can now see that only 16% of account registered before Feb 18 have been active in the last 30 day and this would be a true reflection of 30 day retention on Steemit.
Author Retention
Author retention is based on both post and comments.
Of the 854K accounts, 55% have never posted. Of the 45% that have posted 57% have not posted in the last 60 days and only 26% of the 45% have posted in the last 30 days. That’s 12% of total accounts that have posted in the last 30 day.
However these values include accounts registered this month and last month and due to this, do not give an accurate reflection of retention.
By removing accounts registered before Feb 18 we can now see that 53% of accounts have never posted and 47% of account have posted. Of this 47% less than 20% have posted in the last 30 days. In fact only 9% of accounts registered before Feb 18 have posted or commented in the last 30 days. This is a true reflection of author retention on Steemit.
Author Drop off
The chart below plots the number of days since last post against the days registered. The deeper the color the greater the number of accounts. This chart shows the high drop off on registration in purple on the right of the chart, these are the accounts that never posted. We can also see a dark cluster on accounts less than 100 days old and this dark patch fades as we move up towards 300 days.
Voting Retention
We can see from the visualizations above that all accounts have voted at some time. 19.6% have voted in the last 30 days. 16.5% have voted between 31 and 60 days ago and 64% have not vote in the last 60 days.
These values include accounts registered this month and last month, which would distort the voter retention rate. By excluding accounts set up after 31 Jan 18 we can get a more accurate reflection of voter retention.
We can see now that the voter retention for 30 days is 10.2% and 74% of accounts registered before 1 Feb 18 have not voted in the last 60 days.
10.2% is an accurate reflection of Steemits 30 day voter retention.
Finally I was interested in looking at accounts that registered between Nov 17 and 31 Jan 18. These accounts are of particular interest because the price of both STEEM and SBD had a nice pump. However in more recient times we have seen the price of both STEEM and SBD reduce.
Below we can see the data for accounts set up during Nov and Jan
Almost 50% of accounts have posted and to my surprise 35% of accounts have posted in the last 30 days. That’s a retention of 17.5% on accounts that registered on Steemit while the price of STEEM was high. I was expecting to see this lower than the overall retention. The reason for my thinking was that many people come to Steemit to make a few quick $$$. Now that the price is down, posting might not seem so worthwhile. I am happy to see my thoughts are not correct.
Conclusion
Author retention on Steemit is only 9% and voting retention of 10%. Overall retention is 16%. There also appears to be an increase in retention in the last 4 months, despite the recent downturn in the price of STEEM and SBD.
Are you surprise with these figures? Do they disappoint you? How do you think we can increase the retention on Steemit? All comments welcome below
Data and query
The data was collected for this query on 13 March 18. Power BI was used to connect with SteemSQL
The full M query used to get and transform the data was
let
Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="Select *#(lf)From Accounts (NOLOCK)"]),
#"Removed Other Columns" = Table.SelectColumns(Source,{"id", "name", "created", "last_vote_time", "last_post", "last_root_post"}),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Other Columns", {{"created", type text}}, "en-IE"), "created", Splitter.SplitTextByPositions({0, 8}, true), {"created.1", "created.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"created.1", type date}, {"created.2", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"created.1", "created date"}, {"created.2", "created time"}}),
#"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns", {{"last_vote_time", type text}}, "en-IE"), "last_vote_time", Splitter.SplitTextByPositions({0, 8}, true), {"last_vote_time.1", "last_vote_time.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"last_vote_time.1", type date}, {"last_vote_time.2", type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"last_vote_time.1", "last_vote_date"}, {"last_vote_time.2", "last_vote_time"}}),
#"Split Column by Position2" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns1", {{"last_post", type text}}, "en-IE"), "last_post", Splitter.SplitTextByPositions({0, 8}, true), {"last_post.1", "last_post.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position2",{{"last_post.1", type date}, {"last_post.2", type time}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"last_post.1", "last_post"}, {"last_post.2", "last_post.time"}}),
#"Split Column by Position3" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns2", {{"last_root_post", type text}}, "en-IE"), "last_root_post", Splitter.SplitTextByPositions({0, 8}, true), {"last_root_post.1", "last_root_post.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position3",{{"last_root_post.1", type date}, {"last_root_post.2", type time}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"last_root_post.1", "last_root_post"}})
in
#"Renamed Columns3"
After this a series of DAX calculations were used to compute days different between today, account creation date, last post-date and last vote date.
I am part of a Blockchain Business Intelligence community. We all post under the tag #BlockchainBI. If you have an analysis you would like carried out on Steemit or Blockchain data, please do contact me or any of the#BlockchainBI team and we will do our best to help you...
You can find #BlockchainBI on discord https://discordapp.com/invite/JN7Yv7j
Posted on Utopian.io - Rewarding Open Source Contributors