In this second study of the 2017 full year statistics for the steem blockchain platforms I will look at the figures for eSteem. 

![esteem title.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515719766/pxkjvtzmsxbvloznqvbe.png)

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.
---

## Outline

1. Scope of Analysis
2. Tools Used
3. Overview of 2017
4. Location Analysis
5. Top 50 users
6. Scripts

##

---

## 1 Scope of Analysis

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.

---

## 2 Tools Used

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. 

---

## 3 Overview of 2017

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.

![esteem daily user numbers 2017.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515706620/ybbisufptigdzs0w37ae.png)

<center><sup>Individual authors per day - "alternative" platforms</sup></center>

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:
* *The light blue line is the number of articles per day and the dark blue line is number of distinct authors. These are plotted against the left hand axis.*
* *The silver columns are author rewards and the gold columns are curator rewards. These are plotted against the right hand axis.*

![Screen Shot 2018-01-11 at 20.56.19.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515706746/fry1s4uutrfpdqgedgtt.png)

<center><sup>2017 main metrics - eSteem</sup></center>

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. 

---

## 4 Location Analysis

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. 

<center>![Screen Shot 2018-01-11 at 22.34.49.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515710348/sd9v93vsqqocbhqf59bi.png)</center>

<center><sup>location analysis over 2017 - eSteem</sup></center>

The largest number of posters over 2017 *(of those analysed)* were located in Indonesia, the USA, Nigeria, and India. 

---

## 5 Top 50 Users

Finally, a sunburst chart of the top 50 users by author payout for the eSteem platform over 2017 *(excluding @good-karma the developer of eSteem who would otherwise be the number 1 rated user).* 

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:*
* *Some author payouts will have been augmented by votes from upvote bots. As such the net earnings will be lower than those seen here.*
* *The amounts are expressed as per the payout values shown on steem platform sites - no conversion has been made to US$ or to Steem. The value of payouts can actually differ quite substantially from one month to the next, depending on the value of the underlying currencies.*

![esteem top 50 users.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1515711985/hky8sixnyvnaubeqqqm4.png)

<center><sup>top 50 users over 2017 - eSteem</sup></center>

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. 

---

## 6 Scripts

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:
* Changing from grouping by the month of the comment creation date to the exact date of comment creation allows the extraction by individual date. The author information is not required for this run *(and the results would be very large by author and date)*
* An inner join to the Accounts table allowed the additional location data to be extracted.

##

That's all for today. Thanks for reading!







<br /><hr/><em>Posted on <a href="https://utopian.io/utopian-io/@miniature-tiger/esteem-full-year-2017-statistics">Utopian.io -  Rewarding Open Source Contributors</a></em><hr/>