Analyzing Japanese Posting Trend - Business Intelligence Steemit
English 日本語
Akihabara, Manga, Tokyo Disneyland, Ramen - these are some of the things that come to mind when I hear the word Japan.
In this edition of Business Intelligence Steemit ( #bisteemit ), let's look into another community I've joined - the Japanese community.
My Approach
My approach will be the same with my previous post but will take it two steps further:
- Since this is only my second attempt, analysis will still be simple and easy - I just want to be familiar with the data available and learn how to extract and present those data.
- I'm only interested in top-most posts; comments and replies are excluded for the meantime.
- I'm interested in the trend for the number of posts in Steemit as a whole and in a community.
- I want to know who the top contributors in a community are.
- I have provided the analysis in Japanese here first. And this is its English translation.
- I will look into the daily posts of 2017 and look into the day of the week they post most often.
- Since the original post was geared towards the Japanese community, the analysis for the overall Steemit post trend (the succeeding section) is repeated here.
Again, with these premises down, I present to you my data and analysis.
1. From 2016 to 2017, what has been the overall trend in the number of posts?
Let's look at the data.
As can be seen in the graph, there was a notable increase in the number of posts in May 2017 with 134,350 posts, then peaked in July with 513,409 posts.
Then on August, the posts slightly decreased to 499,739.
It seems that the number of posts for September will decrease. We're already on the third week, but there are only 313,446 posts. Dividing this number by 20 (the days that have passed), we will get 15,672 posts per day. Using this value to multiply the remaining days for this month, will give us around 160,000 posts.
For September, the forecast number of posts is around 470,000.
2. What's the trend of posts in the Japanese community?
Let's look at the data that contains japan (E.g., #japan, #japanese) in the tags.
Based on the graph, the number of posts peaked last month with 3,679.
What's the estimatte number of posts this month?
To estimate, let's repeat what we did to compute Steemit's post for September. Let's get the current number of posts of 2,099 and divide that by 20 and we'll get 105 posts per day. Let's multiply this value by the number of remaining 10 days for this month and we'll get around 1,000 posts.
For this month, the estimate number of posts is 3,200.
3. Who are the Top 20 Contributors?
As pointed out by Mr. @inoue, I excluded the spammers.
@yadamaniart, @feelsomoon, @kouhei-gahaku are the top three with 284, 243, and 233 posts each, respectively.
Here is the table of the top 20 contributors.
| No | Author | 2016 | 2017 | Total |
|---|---|---|---|---|
| 1 | @yadamaniart | 23 | 261 | 284 |
| 2 | @feelsomoon | 0 | 243 | 243 |
| 3 | @kouhei-gahaku | 0 | 233 | 233 |
| 4 | @soi-green | 0 | 229 | 229 |
| 5 | @kinakomochi | 0 | 212 | 212 |
| 6 | @aqeelmalik | 0 | 171 | 171 |
| 7 | @olga2772 | 0 | 169 | 169 |
| 8 | @boxcarblue | 71 | 79 | 150 |
| 9 | @kafkanarchy84 | 31 | 117 | 148 |
| 10 | @kumada90 | 3 | 135 | 138 |
| 11 | @ultraseven | 0 | 135 | 135 |
| 12 | @asim | 3 | 116 | 119 |
| 13 | @exhige | 0 | 115 | 115 |
| 14 | @steemitjp | 0 | 115 | 115 |
| 15 | @yoshiko | 0 | 114 | 114 |
| 16 | @noopu | 7 | 106 | 113 |
| 17 | @steemito | 0 | 109 | 109 |
| 18 | @yukihiro3005 | 0 | 106 | 106 |
| 19 | @miho | 0 | 106 | 106 |
| 20 | @kamada3 | 0 | 99 | 99 |
4. Which day of the week do they post most often?
I prepared two graphs. The graph above shows the number of posts per day. It's the detailed view of the graph shown in "2. What's the trend of posts in the Japanese community?"
Another graph is the number of posts per day of the week.
As can be seen from this, many posts during Tuesday with around 2,100 posts in total. For the other days of the week, it's almost the same ranging between 1,800 to 2,000 posts.
Conclusion
The Japanese community contributes to the Steemit community. Comparing the data with the Philippine community, the post trend for July and August are almost the same, but is different for September.
In order to grow the Steemit community as a whole, each community's contribution is important.
Let's do our best everyone!
This is the translation of my #bisteemit entry to @paulag's second Business Intelligence Steemit Weekly Contest.
Steemit Japanese Community
You can catch them at their Steemit chat channel #japan
I used Microsoft SQL Server Management Studio to connect to steemsql and then used Microsoft Excel for the analysis.
I am part of a Steemit Business Intelligence community. We all post under the tag #bisteemit. If you have an analysis you would like carried out on Steemit data, please do contact me or any of the #bisteemit team and we will do our best to help you...
You can find #bisteemit on discord - https://discordapp.com/invite/JN7Yv7j
Credits
Credits to @arcange for his support. All of the data taken in this report was from the superb steemsql managed by the same.
Nice post finally I can read! One small question,
What is the criteria for exclusion? Asking so that I can use the same in my queries.
Good question, I too would like to know what criteria you are using
Yes, I've been searching for that holy grail all along. Would be nice to know.
No holy grail. :D I had to check their accounts one-by-one and then maintain them on a list. Then exclude them in the query.
Want to see sample spammers? Look at the account 'jeicko19' (just add the @ sign).
In one of her posts, @cheetah commented that this account was on its blacklist.
For kameko, @inoue said that it was the same account with 'ayako'、'amisa'、'shantariq'、'monickyra'、'imako'、'jindass'. So it's a joint effort when joining with them since they are more familiar with the accounts proliferating in the community.
So to re-iterate and make it short, I have to do this manually for now until we get a better, more automatic way of doing it (like, filtering if they have at least five posts marked as spam maybe? just a thought)
So we have to wait for another Indiana Jones to find the grail.
Lol. Yes, we do. I love those movies. We watched them when I was a kid.
But my favourite grail movie is the Monty Python one. Jones was cheesy, Monty was cheeky
I fart in your general direction!
I'll have to watch this.
You could have used added a join in your query to check if @cheetah had visited the author in the comments table.
Yes we could do that. I'll share my sql with you in discord.
Hello @dbdecoy. I had to do this manually. I visited each of the accounts in the top 20 and checked their posts, comments, and replies.
The first version of the top 20 had two accounts that were spammers. I updated the list and removed those two accounts (since having them in the list meant publicity for them).
I wouldn't have known that they were spam accounts if it wasn't for @inoue pointing them out.
True. Every contribution is important. :)
Yes it is. Big things come from small things.
Congratulations! This post has been upvoted from the communal account, @minnowsupport, by eastmael from the Minnow Support Project. It's a witness project run by aggroed, ausbitbank, teamsteem, theprophet0, someguy123, neoxian, followbtcnews/crimsonclad, and netuoso. The goal is to help Steemit grow by supporting Minnows and creating a social network. Please find us in the Peace, Abundance, and Liberty Network (PALnet) Discord Channel. It's a completely public and open space to all members of the Steemit community who voluntarily choose to be there.
This post has received a 2.14 % upvote from @booster thanks to: @eastmael.