Repository
https://github.com/steemit/condenser
Overview
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 vertoanalytics.com
Aim of Analysis
The aim of this analysis is to calculated the churn rate on STEEM as a social networking type blockchain* for Q3 of 2018 and compare this to the rest of the year. You can view the Q2 Churn report here
https://steemit.com/steem/@paulag/steemit-monthy-churn-rate-drops-to-35
*STEEM is not only used a as a social networking type blockchain. Some people are investors and wallet holders only. Previous analysis have shown this makes up less than 0.5% of accounts on STEEM and so no adjustment has been made as I have deemed it immaterial.
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.
Weekly Churn Q3
The table above shows the number of new accounts registered per week, the distinct count of votes, the discount of authors, the distinct user count (either voted or posted/commented), the weekly churn and the weekly churn rate.
The churn rate varies considerably from week to week as it is dependent on two variables. The distinct users and The number of new accounts. As both of these variables vary from week to week it is expected to see this variation reflected in the churn rates.
Below shows how this compares to previous reporting periods. We can see that the average weekly churn halved from Q2 to Q3.
Monthly Churn 2018
As with the weekly churn rates you would expect to see a variance on the monthly churn rates as the variable also change.
The monthly churn rate peaks in March at 71% and reduced to a low of 20% in August before rising again in September to 35%. It is worth noting here that HF20 took place in September and very well had an impact as the resource credits problem hit the last week of the month.
Quarterly Churn Rates
I think the chart speaks for its self.
Using this as a comparative figure against the social media sites in the image above. Facebook was reporting a churn rate Q3 to Q4 of only 2.8%, Snapchat 23.5%, Instagram 19.1%, Twitter 25.3% and Kick 33%.
Conclusion
Churn is a fact of any business. What is an acceptable level of churn is also a business choice but the reality is, the lower this number the better. We had a Q1 churn rate of 121% was very disappointing to see. 52% qtr churn is such a massive improvement.
The drop in new users from Q2 to Q3 of 49% had the biggest impact on this reduction in churn as the drop in distinct new users was only 18% for the same period. However Q3 figures could be slightly skewed due to HF 20 as previously mentioned.
What are your thoughts on the churn rates above? What reflection do they give about Steem? Please do comment below
Personal Opinion
In my last report I shared my personal thoughts on this data. Something I don't normally do. However retention and churn are close to my heart and something I have been working towards improving with different initiatives and communities. From a witness standpoint 
I have shared data before proving increased retention amount users of Ashers league and often feature data posts on communities showing the same. There is a massive amount of people to thank for this reduction in churn. People like 
The work all of these projects and people have been doing has gone relatively unnoticed and thanked. I have even see some people say who gives a fuck about the retention and I am here to strongly defend the need to have improved retention and churn.
We all know Steemit.com was a beta UI for the STEEM blockchain. A launchpad for greater things. We all know that Steemit Incs main focus is the blockchain and not the UI. I also fully understand and agree that the first few million users on something that could have a few hundred million are not really a big deal. However for Steemit.com to be a successful launchpad for greater things it needs to prove the concept works. Extremely high churn by no means helps this and makes steemit.com useless as a marking tool for the STEEM blockchain and SMT's.
It is also well known that the initial distribution of STEEM makes the blockchain rather centralized. Due to this centralization, when Steemit had a wave of new users for the second half of 2017, there were too few active accounts spreading to spread out the distribution of new coins to a wider base.
Now new account creation is rather low compared to 9 months ago. In Sept we had 109K unique active accounts of which a vast majority is bots. Now is the time to focus on and grow the humans here. Because these will be the base for the next wave, a larger base to spread the rewards. A large base to promote a positive sentiment of STEEM, a larger base to help provide initial traffic and launchpad for DApps to succeed.
Personally I feel the current churn rates are perfect. If we can maintain these levels and grow the accounts we have the STEEM blockchain and SMT's will have an improved chance of success. The next question is, how can we accelerate the growth of accounts while we have this lull to take advantage of?????
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-07-01') and timestamp< CONVERT(DATE,'2018-10-01')The query used to get the unique authors was
SELECT author, timestamp FROM Txcomments (NOLOCK) where timestamp >= CONVERT(DATE,'2018-07-01') and timestamp< CONVERT(DATE,'2018-10-01')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)