Looking at the numbers from Steepshot // January - May 2018
Repository
https://github.com/Chainers/steepshot.io
Introduction
According to steepshot.io
Steepshot is the blockchain-powered censorship-resistant platform that you can use to share cool pictures with your friends, curate and comment their photos and get crypto rewards for your activity.
On Steepshot, you get rewarded for sharing pictures of moments in your life.
Outline
- Scope
- Results
- Findings
- Conclusion
- Tools and Scripts
- Relevant Links and Resources
Scope
Date of the analysis: 6th June 2018
Timeframe of the analysed data: January to May 2018
Components of the analysis: Analyzing users and post made from Steepshot
Results
ACTIVE USERS
Between January and May 2018, there were 215,841 posts from the steepshot app. These posts were made by a total of 13,311 users. That is an average of 16 post per user.
In this post the term "Active" is defined as a user who makes at least one post a month via Steepshot.
The numbers from Steepshot are not encouraging as 53.15% users made post in only one month between January and May 2018. While a meager 3.76% of users can be termed as "active" as they posted at least once in every month between January and May 2018
POST INTERACTION/ENGAGEMENT
The data for Post and comments excluded post and comments from the most popular bots on steemit. The bots used are listed on https://steembottracker.com
This code snippet was used to filter them out.
author not in ('kittybot', 'isotonic', 'getboost', 'booster', 'lightningbolt', 'nado.bot', 'shares', 'peace-bot', 'postdoctor', 'spydo', 'singing.beauty', 'minnowhelper', 'jerrybanfield', 'sunrawhale', 'sleeplesswhale', 'zapzap', 'cryptoempire', 'pwrup', 'redwhale', 'lovejuice', 'foxyd', 'noicebot', 'minnowfairy', 'honestbot', 'seakraken', 'upboater', 'whalebuilder', 'mrswhale', 'upgoater', 'smartsteem', 'dailyupvotes', 'pushup', 'allaz', 'sneaky-ninja', 'upmyvote', 'dolphinbot', 'minnowvotes', 'chronocrypto', 'thebot', 'inciter', 'oceanwhale', 'promobot', 'mitsuko', 'bearwards', 'voterunner', 'brupvoter', 'payforplay', 'edensgarden', 'redlambo', 'mercurybot', 'appreciator', 'slimwhale', 'moneymatchgaming', 'boomerang', 'childfund', 'buildawhale', 'youtake', 'megabot', 'authors.league', 'upme', 'alphaprime', 'steembloggers', 'msp-bidbot', 'lost-ninja', 'estream.studios', 'upyou', 'postpromoter', 'rocky1', 'bluebot', 'flymehigh', 'aksdwi', 'puppybot', 'onlyprofitbot', 'boostbot', 'discordia', 'canalcrypto', 'therising', 'ebargains', 'fishbaitbot', 'luckyvotes', 'brandonfrye', 'estabond', 'upmewhale', 'hotbot', 'adriatik', 'steembidbot')
depth = 0
and depth > 0
was used to filter comments and posts
The number of comments on a post can give us an idea on the level of interaction in the community. The lower the number of comments on a post the less likely there is a good interaction on a post. The idea of Steepshot is to share moments of life via pictures and pictures ignite our inquisitive minds.
Between January and May 2018 there were a total of 47,398 comments made via Steepshot on posts.
Number of authors posting via steepshot didn't vary much between January and May 2018. The comment to post ratio averaged 0.2 for January to May 2018.
Interaction is between two or more persons and majortiy of post have 0-1 comments pointing to a low level of interaction between users.
It was also observed that comments on steepshot post were more from Steemit than Steepshot itself. Few comments were made from Steepshot app itself.
EARNINGS/FINANCIAL BREAKDOWN
For post made via steepshot, there was a total earning of about $226,582.53 from January to May 2018. This payout was nothing close to an even distributions as only few post had a reasonable payout value.
Monthly Payout volume was in an undulating pattern, going up and down with no definite pattern of growth or drop
MONTH | PAYOUT | AVERAGE STEEM PRICE | UPVOTES |
---|---|---|---|
Jan | 66,382.61 | $5.45 | 338,834 |
Feb | 46,747.75 | $4.03 | 400,916 |
Mar | 27,131.35 | $2.37 | 465,468 |
Apr | 48,072.24 | $2.70 | 539,721 |
May | 38,248.58 | $3.10 | 550,805 |
STEEM price was evaluated from https://coinmarketcap.com/
The number of upvotes has grown consistently from January to May 2018, Janaury had the lowest number of upvotes within the 5 months under review but the highest payout value. Payout and price of STEEM has a correlation. With high price of steem, there was a higher payout.
The analysis however has a limitation as another determining factor is STEEM POWER of individual voters. In this analysis, the STEEM POWER of individual voters was not put into consideration
94,418 Posts have a payout of $0 which is about 43% of all post and 90% of all post earned less than $2 in payout
Findings
- The retention numbers / active users on Steepshot is poor.
- Post engagement is also on the low
- For now i would say Steepshot is not "stablized" as its numbers move in a rather almost unpredictable pattern.
Conclusion
With the emergence of other application for posting, The competition is rising and Steepshot has to find a way to increase user retention and with the low price of SBD minnows are finding it difficult to earn much on the blockchain as more payout go to more established few.
Engagement is necessary in order to retain users
Tools and Scripts
Microsoft SQL Server Management Studio 17 was used to access the data from STEEMSQL ( a publicly managed database by @arcange )
Microsoft Excel was used to plot graphs and Charts.
SCRIPTS
SELECT
author,
created,
json_metadata,
(JSON_VALUE (json_metadata,'$.app')) AS App,
children,
total_payout_value,
net_votes
FROM COMMENTS (NOLOCK)
WHERE
depth = 0
AND
created>= CONVERT(datetime,'1/01/2018') AND created< CONVERT(datetime,'6/01/2018')
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) = 'steepshot'
The script was adjusted to test different parameters/indices
Relevant Links and Resources
https://steepshot.io/faq
https://steemsql.com/
Go here https://steemit.com/@a-a-a to get your post resteemed to over 72,000 followers.
A better query would be using a CONTAINS to get all the steepshot tag and then followed by LIKE, which makes the query much more faster and efficient. In addition to that, the created column doesn't need to have CONVERT.
A sample query:
Thank you. But I'm trying to get data of post made from steepshot app and not steepshot tag analysis
if you read closely, the first line will do a full text search on json_metadata containing 'steepshot', and then compare with the next line. I suppose this will be quicker.
tagging @arcange to verify this
your code above returns about 10 rows of data only. and the * picks all info from the comments table and i don't need a lot of them
hmm, you are right. I was reading the doc on CONTAINS, but I presume it's a full text search.
https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017
try this
I tested, the query takes less than 30 seconds to get data, which is much faster then ur query
The benchmark test (not completed):
took some time to load and didnt load complete
Hi @jingis07, glad we could figure this out, well done! The analysis of the number of active users over a longer period of time is a very interesting metric. It shows that the number of recurring users is significantly lower than the number of active users. This approach is something to keep in mind also for other analyses. I'm curious, how did you filter out the bot comments? I think this needs a more complex query than what you've sketched with the
author not in (...)
, or am I missing something?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? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]
It is not complex as you think.
First of all
filters post, leaving only comments
Then
author not in (......)
removes comments from the authors in the bracketREFERENCE
https://www.w3schools.com/sql/sql_in.asp
https://steemit.com/utopian-io/@paulag/analysis-of-steemit-bidbot-business-steemit-business-intelligence
Hi @jingis07, thanks for your explanation. Now I understand: your query gets all comments done via steepshot, but does not include comments done via steemit, busy, etc,. Filtering out the bot authors on those comments probably makes no difference, since the bots don't post via steepshot anyway but use other/custom
app
settings injson_metadata
. So the actual engagement level on steepshot posts is likely to be higher than the numbers you've shown.I think it could work to get all replies to a steepshot post by joining
Comments as posts
withComments as replies
onposts.permlink = replies.parent_permlink
etc... In that case your author exclude list could be used to filter out the bot replies.Hey @jingis07
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!
Contributing on Utopian
Learn how to contribute on our website or by watching this tutorial on Youtube.
Want to chat? Join us on Discord https://discord.gg/h52nFrV.
Vote for Utopian Witness!