Churn Rates on STEEM 2018 Review

in utopian-io •  8 months ago 



Churn is a fact of life in business.  You will win new users and you will lose users. Churn measures how many users you are losing. Whereas retention measures how many users you are keeping.  Churn can be calculated by the following formula

Churn = Number of Active users at the beginning of the period + New registrations – Number of Active users at the end of the period.

Churn rate can be calculated using 

Churn rate = Churn / Number of Active users at the beginning of the period.

A high churn rate on a social media/networking site would indicate low engagement lack of loyalty and high dissatisfaction.   To give us some sort of base to use as a benchmark for STEEM, I have taken this chart from


Aim of Analysis

The aim of this analysis is to calculate the churn rate on STEEM as a social networking type blockchain* for Q4 of 2018 and compare this to the rest of the year.  You can view the Q3 Churn report here

*STEEM is not only used a as a social networking type blockchain.  Some people are investors and wallet holders only.  Therefore, I have taken figures for all new registered accounts and I have also taken figures for those that have either poster or voted or both and excluded those that have not.

All data for this analysis was taken from Steemsql held and managed by @arcange.  As to not distract from the data, I will first present the findings and then the queries used.  

Monthly Churn 2018


The table and chart above show the monthly churn and churn rates including ALL registered accounts as new accounts, no matter what their actively level on the block.

It is clear to see from this that the churn has been reducing for the most part of this year, with a period of increase from August to October.

The average monthly churn rate for 2018 was 39% and the median is 35%

Not all accounts became active in a social sense.  Some accounts are just wallet holders.  To reflect this the table and chart below have an adjusted in the new account’s column.  This column now only includes accounts that have either voted or posted.  Churn and churn rates are calculated the same as above.


From this we can see churn reduced from Feb to August, increasing again in September and then reducing again for October and November and an increase in December.  All in all, monthly churn rates when you only include accounts that either/or post/vote show a lower churn than that including all accounts.

The average monthly churn rate calculated this way was 21% and the median was 23.5%

Quarterly Churn


The table and chart above shows quarterly churn where New accounts includes ALL new registered accounts.  We can see that the churn rate levels off at 52% for both Q3 and Q4.  The general trend for the year seen a reduction in churn.

The average quarterly churn rate was 75% and the median 63%

Below shows churn where new accounts only include accounts that because actively socially by either/or voting/ commenting


Although the churn rates are considerably lower using only accounts that because social as new accounts, This shows a different trend where by the trend for the churn rate over the year is an increase.

The average churn was 37% and the median is 38%.


Looking at STEEM as a social type platform, it would be my opinion that only including new accounts that became active on the social side is a fair representation of churn on steem as a social platform.  Some accounts are wallet holders (accounts with active transfers in or out of the wallet and no social actions), some accounts never had any activity at all.  Therefore, there is justification for excluding these accounts from the calculations when looking at churn from a social aspect.

However, it is worth pointing out, accounts that have ONLY JSON transactions (such as steemmonster players) would be included in accounts that never posted or voted.

It is also worth pointing out the HF20 happened late September, so was in full effect for Q4.  In the next churn report I will have enough data to compare the churn rates with different sign up routes such as via Steemit Inc or Utopian.

The Data and the queries

As mentioned I used Steemsql and PowerBI to gather and model the data.  The query used to get the unique voters was

SELECT voter, timestamp FROM Txvotes (NOLOCK) where timestamp >= CONVERT(DATE,'2018-10-01')             and         timestamp< CONVERT(DATE,'2019-01-01')

With the data returned from this query, a distinct count of voter (for both month and quarter) was calculated.

The query used to get the unique authors was

SELECT author, timestamp FROM Txcomments (NOLOCK) where timestamp >= CONVERT(DATE,'2018-10-01')             and         timestamp< CONVERT(DATE,'2019-01-01')

With the data returned from this query, a distinct count of author (for both month and quarter) was calculated.

To get the total unique users, I combined both of the above queries and then ran distinct counts on the name.

The query used to get the number of new accounts was

Select name, created FROM Accounts (NOLOCK)

However, this query only gives ALL new accounts.  To get the distinct counts of new accounts that posted and or voted I used data from a different report.  This is the monthly new user report that is published on steem under my account.

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Hi @paulag, thanks for the follow-up to the Q3 report! I like the concept of additionally filtering out accounts that haven't posted or voted. I agree that this probably gives a more realistic picture on the retention for Steem's social media aspects. A couple of apps rely on Steem features other than votes or comments. You've mentioned SteemMonsters already, using mainly customJSONs and maybe a few transfers. SteemDice brings only wallet activity and seems to be pretty popular at the moment. Finding a metric for an "active" user becomes harder with those. Looking forward to the Q1/2019 report with less HF20 influence!

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.

Need help? Chat with us on Discord.


  ·  8 months ago (edited)

I didnt even consider Steemdice. @lextenebris mentions in a comment a new class of filterable items to account for different uses of the block and I agree with that for sure.

Thanks for the review

Thank you for your review, @crokkon! Keep up the good work!

Nice, is there data for Value per Steem user? How many daily users do we have on a daily basis? 50.000?

statistia facebook.JPG


source 1 2

Analyzing the historical context of what happened in the last four months, I believe that the churn rate is relatively small.
The decrease in the price of Steem and also the problems related to the HF20 distort this value upwards, and consequently can show a rate that despite being high, even thought being smaller than expected.

If you think about though when the HF20 hit people were closing their accounts and being done with Steemit period. There is another portion that simple went inactive to see how thing would work out. Then the came back saw that Steem had dropped in price and more left. Same thing in December. The only reasonable expectation is most people are on some sort of holiday or the holiday season and not being online so much, plus much of the bulk left. Then there is the usual I joined, I have no clue, too afraid to talk, quit. I could be completely off base too LOL. Anyway around it. It's not as bad as I thought it was. Have a good day

Totally agree with you about that isn't so bad as I thought.

Onboarding could be better but for those that take the first step in becoming active, well these figures are good

Posted using Partiko Android

I agree. These figures are good. I love it when you post the breakdowns like this. I know it interests me and it's gibberish to some. To look at the numbers puts a different light on things. At least from my point of view :D

Great analysis, churn rate is a kind of new way to differentiate the extent of activeness of users on a social platform, thanks for your information .😀

Posted using Partiko Android

Glad you like it 😀

Posted using Partiko Android

It's very interesting to see that when you measure it only by accounts which used the social functions, churn was never all that bad. A worst case of 35% monthly and much more comparable to other social networks.

Its really interesting. So many accounts never preform any action and these accounts distort the figures.

Good to hear from ya, hope all is good

Posted using Partiko Android

Nice work. Looking forward to the next report.

Posted using Partiko Android

Thank you @bluerobo

Posted using Partiko Android

As the daily active users and transactions continue to stabilize, metrics will get even better to compare as those that remain are mostly here for the social aspects. However, it will be interesting to see these metrics for certain Dapps like Steem Monsters and @actifit to determine if these innovative forms of engagement are better which I speculate they are.

Posted using Partiko iOS

Yes im looking forward to delving further into individual apps

Posted using Partiko Android

This post has been included in the latest edition of SOS Daily News - a digest of all you need to know about the State of Steem.

I think it's interesting to try and separate historical accounts which have social media activity from those which are purely wallet-activity focused, and that is an absolutely useful metric to go by.

On calculating churn, are we filtering out the new accounts which have had no social media activity? Just for clarity. I only ask because to be active even a little via a posting and voting, an account must be staked, which is much more expensive right now than creating the account in a real sense.

yes I have filtered out new accounts which have no social media activity. some of these may have wallet activity, some also might have steemmonster activity (json) and some may have never logged in.

I'm not fully sure how to treat steemmonster activity. I'm not so convinced I should have excluded these?

Its looks a lot better filtering out accounts this way and I feel its a little more accurate.

I suppose that depends entirely on how you think of Steem Monster activity.

Myself, I am hesitant to class it in with the rest of social media activity on the blockchain because it is only good and only really readable for Steem Monsters. Which is sort of the opposite of how I envision a digital application which can really grow a distributed database solution. From the perspective of someone who doesn't play and who only engages with the steem blockchain via the social media sides of things, it increases the traffic and the load on the servers that I use without providing me even incidental use or interest. From the perspective of someone like me, it's a pure cost.

Compare to one of the other major digital applications on the platform like a video solution which might not particularly carry a video that I want to see you right this moment but I could, at any time, just by going to look and it doesn't require that I play a specific game or get involved at a specific level.

At some point were probably going to have to create a new class of "filterable item" which involves digital applications which encrypt their data payload or otherwise create content which is not intended to involve user interaction outside the specific provenance of that singular application. Steem Monsters won't be the last to play around in that space.

that steemdice thiny is another one, it only uses wallet transactions. Steemmonsters uses json and I have seen many other projects also looking to use JSON. I agree, a new class of filterable items will be the way forwards as the blockchain develops more

Hi @paulag!

Your post was upvoted by @steem-ua, new Steem dApp, using UserAuthority for algorithmic post curation!
Your UA account score is currently 7.021 which ranks you at #89 across all Steem accounts.
Your rank has not changed in the last three days.

In our last Algorithmic Curation Round, consisting of 233 contributions, your post is ranked at #10.

Evaluation of your UA score:
  • Your follower network is great!
  • The readers appreciate your great work!
  • Good user engagement!

Feel free to join our @steem-ua Discord server

Congratulations @paulag! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You received more than 60000 upvotes. Your next target is to reach 65000 upvotes.

Click here to view your Board
If you no longer want to receive notifications, reply to this comment with the word STOP

Support SteemitBoard's project! Vote for its witness and get one more award!

Hey, @paulag!

Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Get higher incentives and support!
Simply set as a 5% (or higher) payout beneficiary on your contribution post (via SteemPlus or Steeditor).

Want to chat? Join us on Discord

Vote for Utopian Witness!