User Numbers and User Retention Analysis - The Story of 2017
We're fast approaching the end of the year. The Christmas lights are up. Snow has started to fall. And despite the almost constant drama on the trending page, really it's been a very good year for the world of Steem. Not as mindblowing as for some other cryptocurrencies, mind you. But from a user point of view, the Steem network has really taken off.
Let's have a look at the numbers:
- Main blog post numbers are in light blue; Individual Author numbers are in dark blue (both measured against the left hand axis).
- Comment numbers are in orange (measured against the right hand axis) - Total Poster numbers (articles and comments) are in yellow (left hand axis).
The April - June 2017 surge in the cryptocurrency markets coincided with exponential growth in Steem blockchain user numbers, as well as in the amount of articles posted and the level (although not necessarily the quality) of comments. Since the summer the number of individual authors posting has levelled off but remains 10x higher than the position at the start of the year; no mean feat. We now appear to have entered a period of relative stability, if there can be such a thing in the crypto world.
So did increasing user numbers push demand for Steem and drive the price up? Or did the rising Steem price attract new users? From the chart below it certainly looks like the latter; the price moved first and the increase in Steem network activity followed. In all likelihood there will be some influence in each direction.
(here I've used comment numbers rather than user numbers - the overall picture is the same but there's just a more interesting fit between comment numbers and the Steem price).
New users continue to sign up to Steem but the biggest issue for growth remains user retention. The following chart shows new account numbers in green (only those that actually make a post or comment) and accounts deactivating in red (captured by the last date on which an account makes a post). It's broadly "one in - one out" since the surge of activity ending in June. Like all the best clubs then!
From a user retention perspective we appear to lose around half of posters within 30 days of their first entry (the purple line below) and another 10-30% within 90 days (the blue line - the 90 day retention is actually worsening for newer accounts). Only one in ten posters seem to sign up for the long haul (the gold line). But if you're reading this then you're one of them. And I think you've made the right choice.
Methodology and Tools for Analysis
- Raw data was obtained through sql queries of the Comments and Accounts tables in steemsql (thank you as always @arcange!) using Valentina Studio.
- Data was analysed in Numbers, the mac spreadsheet tool.
- Data was obtained across all of 2017.
- Data for the Steem price was obtained from coinmarketcap.com
The following query formed the starting point for the user retention analysis. It resulted in thirty-nine thousand rows of data. It took 4 seconds to run.
SELECT
CONVERT(date, Accounts.created) AS [AccountStart],
CONVERT(date, Accounts.last_post) AS [LastCommentorPost],
datediff (day, CONVERT(date, Accounts.created), CONVERT(date, Accounts.last_post)) AS [stayDuration],
Count(Accounts.name) AS [Accounts]
FROM
Accounts (NOLOCK)
WHERE
Accounts.post_count > 0
GROUP BY
CONVERT(date, Accounts.created),
CONVERT(date, Accounts.last_post),
datediff (day, CONVERT(date, Accounts.created), CONVERT(date, Accounts.last_post))
Full Results
The detailed results for the user retention study can be found below.
| Account Creation Date | %age of accounts still posting after 30 days | %age of accounts still posting after 90 days | %age of accounts still posting (posted within last 30 days) |
|---|---|---|---|
| 2017-01-01 | 64% | 57% | 19% |
| 2017-01-02 | 55% | 44% | 17% |
| 2017-01-03 | 40% | 36% | 10% |
| 2017-01-04 | 35% | 29% | 5% |
| 2017-01-05 | 50% | 46% | 18% |
| 2017-01-06 | 53% | 50% | 8% |
| 2017-01-07 | 41% | 35% | 11% |
| 2017-01-08 | 49% | 41% | 10% |
| 2017-01-09 | 41% | 30% | 6% |
| 2017-01-10 | 46% | 36% | 14% |
| 2017-01-11 | 48% | 41% | 8% |
| 2017-01-12 | 50% | 43% | 14% |
| 2017-01-13 | 48% | 38% | 11% |
| 2017-01-14 | 48% | 42% | 12% |
| 2017-01-15 | 44% | 32% | 8% |
| 2017-01-16 | 51% | 39% | 11% |
| 2017-01-17 | 57% | 46% | 10% |
| 2017-01-18 | 48% | 46% | 14% |
| 2017-01-19 | 44% | 38% | 7% |
| 2017-01-20 | 51% | 46% | 11% |
| 2017-01-21 | 46% | 40% | 7% |
| 2017-01-22 | 42% | 32% | 17% |
| 2017-01-23 | 49% | 45% | 18% |
| 2017-01-24 | 41% | 33% | 8% |
| 2017-01-25 | 44% | 38% | 13% |
| 2017-01-26 | 55% | 52% | 7% |
| 2017-01-27 | 46% | 40% | 2% |
| 2017-01-28 | 50% | 38% | 13% |
| 2017-01-29 | 51% | 40% | 11% |
| 2017-01-30 | 48% | 41% | 7% |
| 2017-01-31 | 48% | 42% | 4% |
| 2017-02-01 | 52% | 38% | 8% |
| 2017-02-02 | 58% | 46% | 19% |
| 2017-02-03 | 47% | 38% | 8% |
| 2017-02-04 | 58% | 54% | 12% |
| 2017-02-05 | 55% | 49% | 17% |
| 2017-02-06 | 60% | 44% | 14% |
| 2017-02-07 | 60% | 55% | 18% |
| 2017-02-08 | 45% | 45% | 16% |
| 2017-02-09 | 51% | 41% | 16% |
| 2017-02-10 | 61% | 56% | 21% |
| 2017-02-11 | 51% | 46% | 18% |
| 2017-02-12 | 57% | 52% | 14% |
| 2017-02-13 | 34% | 32% | 4% |
| 2017-02-14 | 50% | 48% | 10% |
| 2017-02-15 | 38% | 32% | 6% |
| 2017-02-16 | 51% | 48% | 16% |
| 2017-02-17 | 100% | 60% | 0% |
| 2017-02-18 | 58% | 50% | 25% |
| 2017-02-19 | 49% | 43% | 13% |
| 2017-02-20 | 51% | 39% | 16% |
| 2017-02-21 | 51% | 38% | 9% |
| 2017-02-22 | 50% | 47% | 11% |
| 2017-02-23 | 65% | 59% | 16% |
| 2017-02-24 | 51% | 47% | 13% |
| 2017-02-25 | 48% | 45% | 17% |
| 2017-02-26 | 45% | 42% | 16% |
| 2017-02-27 | 45% | 38% | 8% |
| 2017-02-28 | 53% | 44% | 12% |
| 2017-03-01 | 43% | 35% | 8% |
| 2017-03-02 | 42% | 38% | 9% |
| 2017-03-03 | 71% | 60% | 9% |
| 2017-03-04 | 58% | 56% | 19% |
| 2017-03-05 | 62% | 60% | 18% |
| 2017-03-06 | 44% | 33% | 11% |
| 2017-03-07 | 59% | 49% | 12% |
| 2017-03-08 | 58% | 53% | 14% |
| 2017-03-09 | 61% | 55% | 23% |
| 2017-03-10 | 53% | 45% | 15% |
| 2017-03-11 | 57% | 54% | 11% |
| 2017-03-12 | 51% | 41% | 12% |
| 2017-03-13 | 58% | 51% | 4% |
| 2017-03-14 | 56% | 43% | 12% |
| 2017-03-15 | 66% | 55% | 9% |
| 2017-03-16 | 62% | 51% | 9% |
| 2017-03-17 | 66% | 53% | 14% |
| 2017-03-18 | 59% | 48% | 13% |
| 2017-03-19 | 60% | 50% | 15% |
| 2017-03-20 | 63% | 47% | 12% |
| 2017-03-21 | 65% | 59% | 17% |
| 2017-03-22 | 55% | 43% | 16% |
| 2017-03-23 | 58% | 47% | 12% |
| 2017-03-24 | 59% | 47% | 17% |
| 2017-03-25 | 66% | 52% | 14% |
| 2017-03-26 | 71% | 58% | 10% |
| 2017-03-27 | 59% | 48% | 17% |
| 2017-03-28 | 62% | 47% | 13% |
| 2017-03-29 | 59% | 44% | 11% |
| 2017-03-30 | 62% | 50% | 12% |
| 2017-03-31 | 82% | 73% | 6% |
| 2017-04-01 | 66% | 51% | 16% |
| 2017-04-02 | 78% | 70% | 20% |
| 2017-04-03 | 60% | 50% | 5% |
| 2017-04-04 | 57% | 48% | 13% |
| 2017-04-05 | 68% | 52% | 12% |
| 2017-04-06 | 68% | 57% | 15% |
| 2017-04-07 | 77% | 69% | 4% |
| 2017-04-08 | 82% | 78% | 7% |
| 2017-04-09 | 87% | 72% | 4% |
| 2017-04-10 | 80% | 74% | 4% |
| 2017-04-11 | 78% | 71% | 4% |
| 2017-04-12 | 56% | 39% | 13% |
| 2017-04-13 | 70% | 54% | 11% |
| 2017-04-14 | 53% | 45% | 18% |
| 2017-04-15 | 88% | 83% | 6% |
| 2017-04-16 | 84% | 73% | 6% |
| 2017-04-17 | 44% | 19% | 13% |
| 2017-04-18 | 84% | 77% | 5% |
| 2017-04-19 | 74% | 50% | 11% |
| 2017-04-20 | 65% | 53% | 24% |
| 2017-04-21 | 70% | 44% | 14% |
| 2017-04-22 | 72% | 50% | 7% |
| 2017-04-23 | 56% | 38% | 15% |
| 2017-04-24 | 70% | 55% | 12% |
| 2017-04-25 | 77% | 66% | 7% |
| 2017-04-26 | 82% | 76% | 3% |
| 2017-04-27 | 63% | 46% | 15% |
| 2017-04-28 | 60% | 44% | 14% |
| 2017-04-29 | 0% | 0% | 0% |
| 2017-04-30 | 100% | 100% | 100% |
| 2017-05-01 | 0% | 0% | 0% |
| 2017-05-02 | 0% | 0% | 0% |
| 2017-05-03 | 100% | 0% | 0% |
| 2017-05-04 | 50% | 50% | 50% |
| 2017-05-05 | 87% | 42% | 23% |
| 2017-05-06 | 71% | 49% | 19% |
| 2017-05-07 | 67% | 41% | 14% |
| 2017-05-08 | 66% | 42% | 14% |
| 2017-05-09 | 66% | 41% | 15% |
| 2017-05-10 | 59% | 37% | 11% |
| 2017-05-11 | 63% | 38% | 13% |
| 2017-05-12 | 64% | 44% | 17% |
| 2017-05-13 | 64% | 41% | 14% |
| 2017-05-14 | 62% | 36% | 11% |
| 2017-05-15 | 57% | 35% | 13% |
| 2017-05-16 | 60% | 36% | 13% |
| 2017-05-17 | 56% | 33% | 14% |
| 2017-05-18 | 59% | 36% | 15% |
| 2017-05-19 | 60% | 38% | 13% |
| 2017-05-20 | 58% | 36% | 12% |
| 2017-05-21 | 57% | 32% | 14% |
| 2017-05-22 | 57% | 36% | 16% |
| 2017-05-23 | 60% | 36% | 12% |
| 2017-05-24 | 57% | 34% | 13% |
| 2017-05-25 | 57% | 34% | 15% |
| 2017-05-26 | 66% | 36% | 16% |
| 2017-05-27 | 63% | 40% | 17% |
| 2017-05-28 | 71% | 51% | 18% |
| 2017-05-29 | 56% | 24% | 14% |
| 2017-05-30 | 58% | 31% | 20% |
| 2017-05-31 | 53% | 31% | 15% |
| 2017-06-01 | 64% | 40% | 13% |
| 2017-06-02 | 58% | 33% | 14% |
| 2017-06-03 | 59% | 34% | 15% |
| 2017-06-04 | 55% | 33% | 13% |
| 2017-06-05 | 53% | 31% | 12% |
| 2017-06-06 | 55% | 31% | 11% |
| 2017-06-07 | 46% | 25% | 10% |
| 2017-06-08 | 55% | 32% | 15% |
| 2017-06-09 | 51% | 29% | 17% |
| 2017-06-10 | 56% | 28% | 10% |
| 2017-06-11 | 52% | 33% | 22% |
| 2017-06-12 | 52% | 28% | 12% |
| 2017-06-13 | 51% | 29% | 13% |
| 2017-06-14 | 50% | 29% | 10% |
| 2017-06-15 | 47% | 26% | 12% |
| 2017-06-16 | 48% | 27% | 9% |
| 2017-06-17 | 47% | 25% | 12% |
| 2017-06-18 | 46% | 25% | 11% |
| 2017-06-19 | 47% | 27% | 11% |
| 2017-06-20 | 46% | 24% | 12% |
| 2017-06-21 | 43% | 22% | 10% |
| 2017-06-22 | 45% | 22% | 10% |
| 2017-06-23 | 42% | 22% | 9% |
| 2017-06-24 | 41% | 22% | 10% |
| 2017-06-25 | 41% | 21% | 8% |
| 2017-06-26 | 39% | 19% | 9% |
| 2017-06-27 | 40% | 19% | 10% |
| 2017-06-28 | 42% | 22% | 11% |
| 2017-06-29 | 42% | 20% | 10% |
| 2017-06-30 | 44% | 22% | 11% |
| 2017-07-01 | 41% | 19% | 11% |
| 2017-07-02 | 41% | 18% | 10% |
| 2017-07-03 | 41% | 19% | 10% |
| 2017-07-04 | 41% | 21% | 11% |
| 2017-07-05 | 43% | 21% | 10% |
| 2017-07-06 | 47% | 21% | 11% |
| 2017-07-07 | 54% | 28% | 16% |
| 2017-07-08 | 42% | 21% | 12% |
| 2017-07-09 | 42% | 22% | 13% |
| 2017-07-10 | 44% | 22% | 9% |
| 2017-07-11 | 43% | 22% | 12% |
| 2017-07-12 | 47% | 22% | 14% |
| 2017-07-13 | 43% | 19% | 11% |
| 2017-07-14 | 44% | 21% | 12% |
| 2017-07-15 | 43% | 21% | 12% |
| 2017-07-16 | 44% | 19% | 10% |
| 2017-07-17 | 47% | 24% | 12% |
| 2017-07-18 | 44% | 20% | 13% |
| 2017-07-19 | 41% | 19% | 11% |
| 2017-07-20 | 45% | 23% | 17% |
| 2017-07-21 | 44% | 18% | 12% |
| 2017-07-22 | 52% | 24% | 16% |
| 2017-07-23 | 49% | 21% | 15% |
| 2017-07-24 | 48% | 19% | 12% |
| 2017-07-25 | 49% | 16% | 8% |
| 2017-07-26 | 39% | 13% | 7% |
| 2017-07-27 | 27% | 8% | 4% |
| 2017-07-28 | 44% | 16% | 9% |
| 2017-07-29 | 42% | 20% | 13% |
| 2017-07-30 | 42% | 20% | 12% |
| 2017-07-31 | 43% | 16% | 13% |
| 2017-08-01 | 45% | 16% | 13% |
| 2017-08-02 | 46% | 18% | 15% |
| 2017-08-03 | 44% | 16% | 13% |
| 2017-08-04 | 41% | 13% | 11% |
| 2017-08-05 | 53% | 14% | 12% |
| 2017-08-06 | 47% | 14% | 13% |
| 2017-08-07 | 43% | 17% | 14% |
| 2017-08-08 | 40% | 14% | 12% |
| 2017-08-09 | 40% | 15% | 13% |
| 2017-08-10 | 36% | 14% | 13% |
| 2017-08-11 | 44% | 14% | 14% |
| 2017-08-12 | 54% | 12% | 12% |
| 2017-08-13 | 48% | 14% | 15% |
| 2017-08-14 | 36% | 13% | 14% |
| 2017-08-15 | 41% | 15% | 17% |
| 2017-08-16 | 46% | 12% | 13% |
| 2017-08-17 | 46% | 12% | 14% |
| 2017-08-18 | 39% | 9% | 12% |
| 2017-08-19 | 41% | 13% | 15% |
| 2017-08-20 | 42% | 10% | 13% |
| 2017-08-21 | 43% | 18% | |
| 2017-08-22 | 36% | 13% | |
| 2017-08-23 | 42% | 15% | |
| 2017-08-24 | 36% | 12% | |
| 2017-08-25 | 47% | 13% | |
| 2017-08-26 | 39% | 14% | |
| 2017-08-27 | 38% | 13% | |
| 2017-08-28 | 43% | 14% | |
| 2017-08-29 | 40% | 16% | |
| 2017-08-30 | 43% | 17% | |
| 2017-08-31 | 44% | 15% |
Questions
As always, if you have any questions on this analysis, please drop me a note in the comments. Alternatively if there is an analysis from the steem blockchain you would like to see carried out, please let me know.
That's all for today. Thanks for reading!
Posted on Utopian.io - Rewarding Open Source Contributors
Thanks Asher!
There has been a big rise in articles - a lot of it coming in through steemjs - automated stuff.
But comments is way more still. It's just on that chart I've put comments on the right hand axis so it fits on the chart! The scale is about 3x as large - sorry, it's probably not as clear as it should be!
I think it's good to see the whole year in context. Gives the realisation that things have come a long way. A period of stability and then up again hopefully!
hehe, you replied to yourself :D
ahh, I get it now - it's 2 am here so I have excuses!
the automated stuff is what I'm seeing when i curate with SQL, i've got so many accounts 'blacklisted' but there are more every day :(
Hopefully we can battle through to next year and rise like a bird!
I am the 1 in 10 :-)
really good post and analysis.
Thanks also for sharing your query as I am learning a lot from reading these. I'm new to sql so it really helps me and gives me ideas
Thanks also for entering the bicontest. Results will be out later today :0)
Thanks @paulag!
Yes I've always been a bit reticent to share the queries. Mainly because I'm also new to sql and I'm sure my coding is horrible! But I've seen a few posted up and I also found it helpful to see the different approaches. It's all in the spirit of open-source!
Brilliant work sir!
The graphs tell a sad tale of the rise of the auto bloggers - Are we now at more articles than comments?
YIKES
Steem has leveled off its seems (outside of the internal market) and comments are starting to rise, this looks promising at least.
Cheers!
Congratulations! Your post has been manually selected by @flauwy for the daily @utopian-1up curation trail. 10 Steemians/Utopians are following my trail and have upvoted your post for $1.31 in total. Join the discussion for how to make Utopian 1UP a community effort to select the best Utopian contributions of the day. You can follow the @utopian-1up trail on SteemAuto (tutorial).
Disclaimer: 1UP is in not affiliated or organized by the official Utopian.io project!
Yay! Thank you! I agree with this decision!
Very interesting stats. We really have to work on increasing the motivation of newcomers to not loose so many so quickly again.
Thanks @flauwy!
Yes, there's a difficult line to be trodden. It does not look like lots of marketing to newcomers is resulting in growth (although it could be argued it's preventing the numbers from contracting). However I think that new user numbers and the steem price need to be managed in tandem. Some kind of smaller marketing but targeted to groups more likely to succeed might be called for. For instance only through personal recommendations, or sponsored marketing to particular groups: coders sponsored by utopian or artists, sponsored by an equivalent set up for artists. At least until SMTs arrive.
And thanks for the approval. You're everywhere at the moment!
Thank you for the contribution. It has been approved.
For future contributions please stay a bit more formal with your introduction text.
You can contact us on Discord.
[utopian-moderator]
Hey @miniature-tiger I am @utopian-io. I have just upvoted you at 7% Power!
Achievements
Suggestions
Human Curation
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x