In this second study of the 2017 full year statistics for the steem blockchain platforms I will look at the figures for eSteem.
eSteem is an open source blogging application built on top of the steem blockchain. It is a mobile based application, allowing users to access the blockchain whilst on the move. It is available to all steem accounts but in particular provides accessibility to users whose only method to connect to the internet is through their mobile device.
There are three parts to the analysis:
Overview of 2017: An overview of the figures for the full year 2017 (daily individual author numbers, post numbers and reward payouts) illustrating the growth in the platform over the second half of the year;
Location analysis: A breakdown of the 2017 users by location to understand the main locations for the user base; and
Top 50 users: A summary of the payouts for the 50 users over 2017 showing those users that have had most financial success over 2017 through the platform.
The analysis is based on the data for the eSteem application obtained through SQL queries of SteemSQL, a publicly available Microsoft SQL database containing all the Steem blockchain data.
The eSteem data has been filtered from the overall steem blockchain data by filtering on the app label information in the json_metadata column of the Comments table. One limitation of this approach is that modifying an article in another application causes the app label information in the json_metadata column to change to that of the modifying platform. However it is expected that this limitation has minimal effect.
The analysis focuses on articles posted in 2017. The data has been filtered by date using the timestamps in the created column of the Comments table.
Location information has been obtained from the location label information in the json_metadata column of the Accounts table.
Valentina Studio, a free data management tool, was used to run the SQL queries. The raw data was then verified and analysed in the spreadsheet application of the LibreOffice office suite.
Graphs and charts were produced using Numbers, the Mac spreadsheet tool, or using RAWGraphs, an open source data visualisation framework.
SQL scripts are included at the end of this analysis.
eSteem has consistently been the second largest of the steem blockchain applications when measured by numbers of posters and number of posts. This chart illustrates the relative size of the “alternative” steem apps (i.e. excluding steemit.com) measured by the number of individual authors per day. The time period is the second half of 2017.
Individual authors per day - "alternative" platforms
eSteem also managed to grow substantially throughout 2017 under all three metrics: articles, authors, and payouts. The chart below covers the full year 2017 for the eSteem application:
2017 main metrics - eSteem
Key growth spurts can be seen in June and December 2017, both periods when the price of the underlying steem currency was increasing significantly. It will be interesting to see how the statistics react over January given the rapid rise and fall in the steem price that has already occurred in the month.
Finally the table of monthly figures for eSteem shows continual growth in article numbers and poster numbers over 2017.
| Month | All Posters | Regular Posters | All Posts | Total Payouts |
|---|---|---|---|---|
| Jan | 154 | 48 | 809 | 2,095 |
| Feb | 122 | 51 | 712 | 1,928 |
| Mar | 156 | 66 | 1,070 | 1,797 |
| Apr | 137 | 58 | 801 | 2,364 |
| May | 401 | 155 | 2,434 | 24,872 |
| Jun | 2,205 | 1,093 | 21,939 | 132,169 |
| Jul | 4,259 | 2,339 | 54,723 | 71,428 |
| Aug | 4,056 | 2,222 | 46,211 | 61,099 |
| Sep | 4,206 | 2,393 | 49,038 | 63,413 |
| Oct | 4,366 | 2,378 | 47,203 | 49,543 |
| Nov | 3,932 | 2,232 | 51,260 | 47,889 |
| Dec | 6,964 | 3,995 | 87,538 | 145,961 |
| Total | 363,738 | 604,558 |
The December figures showed a 200% increase in total payouts generated and over 70% increases in poster numbers and post numbers.
User retention was also strong over December, with 70% of November posters also posting in December (this only considers posts, not comments). New users of the platform in December were roughly equally split between new December steem blockchain accounts and existing accounts diversifying into eSteem.
The aim of this second part of the analysis is to understand the main locations for the eSteem user base over 2017. It is one of the aims of eSteem to reach users whose only method to connect to the internet is through their mobile device.
The location analysis relies on steem users filling in the location information in their profile. Unfortunately a majority of users do not complete this information. Of the 15,465 users who posted (not including comments) over 2017 I was only able to analyse 36%. As such the results of the analysis can only be considered indicative.
location analysis over 2017 - eSteem
The largest number of posters over 2017 (of those analysed) were located in Indonesia, the USA, Nigeria, and India.
Finally, a sunburst chart of the top 50 users by author payout for the eSteem platform over 2017 (excluding 
A further layer of breakdown - the main tags under which they post - has also been added (“other” is typically seen where posters use a wide variety of main tags).
It is worth noting that there are a couple of limitations to this analysis:
top 50 users over 2017 - eSteem
It is interesting that the author payout rewards are really quite evenly split over the top 50 users. This may be a function of the way the application is used - perhaps a mobile app is used for more frequent and shorter posts with less differentiation in the rewards obtained.
This was the main script used for the analysis.
/* SINGLE APP ANALYSIS by AUTHOR and MONTH - 2017 Full year */
SELECT
Comments.author,
Month(Comments.created) as [CommentMonth],
IIF(isjson(comments.json_metadata) = 1, IIF(CHARINDEX('/', json_value(comments.json_metadata, '$.app')) > 0, SUBSTRING(json_value(comments.json_metadata, '$.app'), 1, CHARINDEX('/', json_value(comments.json_metadata, '$.app'))-1),json_value(comments.json_metadata, '$.app')), null) as [Application],
Count(Comments.author) AS [Posts],
Count(distinct Comments.author) AS [DistinctCommentAuthor],
count(Comments.parent_author) AS [ParentAuthor],
count(distinct Comments.parent_author) AS [DistinctParentAuthor],
sum(CONVERT(REAL,Comments.pending_payout_value)) AS [PendingPayoutValue],
sum(CONVERT(REAL,Comments.curator_payout_value)) AS [CuratorPayoutValue],
sum(CONVERT(REAL,Comments.total_payout_value)) AS [TotalPayoutValue]
FROM
Comments (NOLOCK)
WHERE
YEAR(Comments.created) = 2017 AND
depth = 0 AND
IIF(isjson(comments.json_metadata) = 1, IIF(CHARINDEX('/', json_value(comments.json_metadata, '$.app')) > 0, SUBSTRING(json_value(comments.json_metadata, '$.app'), 1, CHARINDEX('/', json_value(comments.json_metadata, '$.app'))-1),json_value(comments.json_metadata, '$.app')), null) = 'busy'
GROUP BY
Comments.author,
Month(Comments.created) as [CommentMonth]
IIF(isjson(comments.json_metadata) = 1, IIF(CHARINDEX('/', json_value(comments.json_metadata, '$.app')) > 0, SUBSTRING(json_value(comments.json_metadata, '$.app'), 1, CHARINDEX('/', json_value(comments.json_metadata, '$.app'))-1),json_value(comments.json_metadata, '$.app')), null)
Very similar scripts were used for the location analysis and the extraction of data by day. Briefly the small differences are:
That's all for today. Thanks for reading!