Looking at engagement outside ones own posts - thoughts, method, and a couple of tables to show who's been doing this the most.
source
The following comment by @kristyglas on my post the other day forms the basis for today's effort:
Orcas and dolphins get way more comments on their posts. Replying to comments is way faster (not necessarily easier) than finding interesting posts to comment on source
I think so, and although responding to the people who have visited your content and chosen to make a comment is important (repeat visitors/retention?), the commentary elsewhere is also important (new eyes?), and should perhaps be looked at separately.
Yesterday I didn't post and spent most of my day replying to comments on my on blog, and whilst this makes my numbers look good in the table below, I do feel that my engagement on the posts of others has not been anything special.
Total comments on Hive since 3 pm - 20th March 2020
| Account | Comments | Length |
|---|---|---|
| galenkp | 4119 | 1070482 |
| tattoodjay | 3115 | 582747 |
| melinda010100 | 2960 | 337529 |
| johannpiber | 2675 | 907985 |
| russellstockley | 2660 | 822739 |
| tarazkp | 2636 | 492735 |
| flaxz | 2520 | 343921 |
| chekohler | 2364 | 434206 |
| abh12345 | 2231 | 408976 |
| trincowski | 2047 | 237592 |
| wakeupkitty | 1884 | 236339 |
| artemislives | 1867 | 512427 |
| brittandjosie | 1834 | 449073 |
| silvertop | 1796 | 292852 |
SQL to the rescue
And so this morning, I have been looking at a method to count:
- How many comments one has made outside their own posts
- How many different people one has spoken to outside of replying to those commenting on their own posts
You can skip to the tables if code makes you cross-eyed 🤓
Getting the number of comments an account has made from a specific date is quite easy:
select count(*) from comments where
author = 'abh12345' -- ME
and created > '03/20/2020 15:00:00' -- Approx new chain start-time
and depth > 0 -- Comments only (Posts are depth 0)
However, because posts and comments reside in the same table on @hiveSQL and there is no obvious method to join the table back on itself, I have taken this rather fiddly approach to finding out if a comment that was made, was made in reply to a comment on ones own post.
select url,
left(substring(url,CHARINDEX('@',url,1)+1,20), CHARINDEX('/',substring(url,CHARINDEX('@',url,1)+1,20))-1) as post_author
from comments
where depth > 0 and author = 'abh12345'
and author <> left(substring(url,CHARINDEX('@',url,1)+1,20), CHARINDEX('/',substring(url,CHARINDEX('@',url,1)+1,20))-1)
and created > '03/20/2020 15:00:00'
The screenshot is shows some of the data returned from the above query. Each post and comment has a url, and the first username in the URL will be the original post author. So, it's just a matter of ensuring that this author is not the same as 'me', which is what this line does:
and author <> left(substring(url,CHARINDEX('@',url,1)+1,20), CHARINDEX('/',substring(url,CHARINDEX('@',url,1)+1,20))-1)
In English, the process is something like:
- Find the first '@' and tell me what position it is in the string
- Start at this position +1 (we don't want the @), and give me the next 20 characters (should be enough to account for all length of username)
- Use these 20 characters and give me everything to the left of the first '/'
And so:
'/hive-139358/@galenkp/your-limousine-has-arrived-sir#@abh12345/q8wibb'
becomes
'galenkp/your-limousi'
and then
'galenkp'
And then you can compare the comment author to what is returned from the string query, simples! 🙈
If anyone has a better method, I'm all ears!
So, who has been commentating a lot outside of their own posts, and who's been speaking to many different people?
Total comments on Hive since 3pm - 20th March 2020, excluding replies to comments on own posts
| Account | Comments | Length |
|---|---|---|
| galenkp | 1959 | 476231 |
| tattoodjay | 1842 | 317958 |
| flaxz | 1735 | 239697 |
| melinda010100 | 1734 | 220625 |
| rockor | 1721 | 138017 |
| trincowski | 1660 | 209792 |
| silvertop | 1654 | 264934 |
| ryivhnn | 1634 | 666635 |
| chekohler | 1609 | 276496 |
| johannpiber | 1525 | 517684 |
| artemislives | 1457 | 407770 |
There is some overlap in this list and the one further up, and I think it might be interesting to look at the percentages of comments in/out of own posts, when looking at account 'class'.
Galen is a new orca, and with 1959 of 4119 comments made outside his own posts, a fairly even 47/53% split. He is also the only orca in the 2nd list.
chekohler (11k HP) has 1609 of 2364 comments outside of own posts - 68/32%
trincowski (3.4k HP) has 1660 of 2047 comments outside of own posts - 81/19%
And where am I in this list? Much further down with 721 comments (out of 2231) outside of my own posts - 32/68%
A really small dataset, but it does seem that smaller accounts are commenting more outside their own posts.
The final list shows the accounts who have commented on the most unique accounts' posts on Hive (since 3 pm 20th March 2020):
| Account | No. of different people visited |
|---|---|
| joeyarnoldvn | 696 |
| johnolusegun | 468 |
| brittandjosie | 466 |
| creativemary | 365 |
| mattsanthonyit | 349 |
| steemsupporter | 343 |
| fredkese | 340 |
| silvertop | 328 |
| naturalmedicine | 326 |
| ewkaw | 325 |
| alokkumar121 | 324 |
| melinda010100 | 321 |
| chekohler | 320 |
| codingdefined | 319 |
| jeffjagoe | 312 |
| artemislives | 310 |
| mayvileros | 301 |
| sidwrites | 301 |
Notice that the orcas in the first list are nowhere to be found here, although engagement master/knucklehead Galen is not far behind with 293 different account-posts commented on.
182 for me, which compared to the top of the list doesn't look that good. However, the average number of unique people visited for all the accounts that have made at least one 'outreach' comment on Hive is only 16.8.
Summary
Well I think I have new criteria to be included in the Engagement League at some point. I do look at number of different people spoken to each week, but that includes people visiting your own posts.
Do you think it is worth looking at comments made outside of own posts, and number of different people spoken to outside of your own posts?
And if so, do you think this type of 'outreach' engagement, should be scored higher than engaging on your own posts?
Tomorrow I think I'm going to show you my sports watch, my brain is ded from this weeks data grind.
Have a good friday!
Asher
Good trick to SUBSTRINGify the url to find the post author.
But when dealing with dates, you should use ANSI date format (YYYY-MM-DD) because you do not know the locale of the server. Would it be a different date format than US (MM/DD/YYYY), or change to a different format, your query will fail.
By replacing
created > '03/20/2020 15:00:00'withcreated > '2020-03-20 15:00:00', your query will never fail, whatever the locale of the server.Add to this that the latest format is more "universal" and is less confusing.
It does the job :)
Thanks for the tip on dates. I usually take the day month year out of timestamp/created and build my own date - much easier when exporting to excel I find.
If you plan to export a date part to Excel, better (and faster) to use
CONVERT(DATE,timestamp)That works, cheers :D
I think the 'outreach' engagement is a good idea. While I tend to view and comment on several post a day, I do realize that most of my comments go to the same grouping of people, so I know I need to reach out more.
It has been quite some time since the last League update, and this could be a nice metric to add.
Thanks for the feedback, I think they will both be added :)
Ok, so you lost me when talking about queries and code and .. lol..
It's great that you can pull out this info though :)
Yeah, I think that will be the case for many. The reason I add it is partly for future reference, and to pad the work out :D
Well, keep up the good work, I'd say :) and have a wonderful weekend ;)
I think engagement is meant to be both ways. Your post and other people's post. This will help in discovering people with good content too.
New users have to comment on other posts to get seen unlike the big boys who know themselves already. You might then notice that there are constant people on most of the posts done by the orcas ...
Well this criterion will make things tougher on the engagement league.
Hey mate, some interesting stats. Showing commenting statistics outside of ones own posts would be a good addition to the EL I think. It will show who is spreading the engagement around and who is commenting with a small select group.
I was pleased to see my nearly 50/50 split although a little disappointed to see my unique users commented on being a little lower. I considered why this may be.
I think, for me, it is because I focus on supporting people through engagement and that process takes time to develop. Hence my commenting is less widespread, but penetrates much deeper.
Me spreading 620 comments a week between 620 accounts would provide no value to those 620 accounts...Follow up commenting and engagement is required, not simply drop and run commenting.
Engaging to me doesn't mean dropping as many comments as possible, but getting involved, conversing. That's my idea of it, and I think the comment count, split between within and without my posts and character count demonstrates that.
This is a good post, and interesting statistics, although the code did my head in. (I'm a dumb bastard so don't understand it.) I hope people can see patterns here, and that they can see the benefit of engaging for effect, not just engaging to make it up the list on the EL.
Yes, I think I will include these metrics in the EL, probably starting a week on Sunday.
Very true, and that is an insane number of accounts to keep up with. Seems you are doing a solid job sir, hats off :)
It will be interesting to see if it changes the results any, the new metrics I mean.
~ I didn't post and spent most of my day replying to comments on my on blog, and whilst this makes my numbers look good in the table below, I do feel that my engagement on the posts of others has not been anything special.~
I have thought about doing one day of only reading and commenting. but i am here to post, i am the girl with the ideas and love to make a blog.
I think if someone spent a moment or even two to comment on a masterpiece blog you did the least you can do is be nice and answer back just as in real life.
That said, it will take away time to spend to comment outside your own blog.
~ Do you think it is worth looking at comments made outside of own posts, and number of different people spoken to outside of your own posts? ~
well yes, your are the list guy, and this could provide a whole different look on the list and might be interesting. Or a separate league. In the past there were tow leagues aswell remember. And there are sponsors enough.
That ‘outreach' engagement, could be scored higher than engaging on your own posts, and make total engagement better in numbers i guess.
But i am a fan of the list on sunday but maybe it need a few new rows, as i said could be interesting ! More work for you less biking though
We could also tie our sweet orca at the number 1 place to a chair ( Janton style) without his computer and see what happens to the numbers hahahahah, they will be different i tell ya,
Oh and dont tell him i want to tie him to a chair please,
B
It all points to more work for me and less time on my bike!
I think the EL is due a revamp though, and these metrics could help :)
I say revamp away , biking can be done tomorrow
Beer today, biking tomorrow... no revamp this week! :D
I would find it helpful to separate replies to comments on the author's own posts from comments made on other authors' posts.
They're both important so I wouldn't rank either one higher than the other.
I guess a minimum five word/20 character threshold could eliminate lots of the "nice picture" comments, but on the other hand it could also knock out the little riffs of repartee which are such a joy to read :)
I found it useful, when I was new, to get the feedback that the Engagment League provides: I'm always more or less in the shallows, so I felt that my engagement was about right for the time I had available and what I wanted to do.
Is there still a redfish league? I thought this was really good for smaller accounts, as there may be too much competition for them to appear in the Engagement League, but the redfish league would still provide them with some feedback.
Thank you for this - all really interesting :)
I will look adding these metrics into the EL, and probably remove a couple of less important ones (like witness votes).
Exactly, and why i'm not all in on the idea to chop off short comments.
Sadly not, paulag did the legwork with that one and I'm worried about doing something similar because of the time it takes.
Thanks for stopping by, have a nice weekend.
Less important ones WITNESS VOTES , maybe people who havent votes 30 witnesses are not allowed to participate, witnesses are important
Harsh! :)
I had a week off in this period. That must really have dented my stats!!
I do try to comment on other posts, I am not a fan of those who only ever reply but there are a lot of those kind of authors.
I have joined a table to itself before, usually when you make the table as an alias and nested selects. It isn't the most graceful.
select * from comments com1
where blah in (select * from blah)
And blah in/not in etc
But fuck it, yours works!! :0D
Yep I think your week off cost you here :)
Love me some nested selects for readability!
The issue is seemingly a lack of key to main post from the comments within.
Also, a comment has a depth > 1 and a parent author, but nothin to say it is a comment of this post which was written by this author.
Anyway, string query saves the day!
Good old string query, almost sounds like a sitcom!! :OD
I must try harder in the other commenting!
I know I am super appreciative of the comments given to me and I do make an effort to leave at least a few comments a day on the posts of others but I have to admit that it is among my circle of friends. Although, I upvote a wider circle. Time is always the factor. Another factor that might be interesting to study is the length and original content in each comment. I get a lot of 'nice picture(s)' on posts that were more than just a picture. Should we consider qualitative measures of comments as well as quantitative?
Nothing wrong with this at all, I guess it depends how many friends you want :)
We should, but it is easier said than done.
Any ideas on how to do this, (other than just discounting 'nice post', and 'upvote plz sir') would be appreciated :)
Word count would give you an idea, and I think (don't know) spam detector software would weed out posts that were too similar. Also whether the comment was responded too and how many times. Quality comments usually get responded to and a thread is created with more than two entries.
I think the above would definitely help guide, but there are always anomalies. I think quality is in the eye of the reader, and that's really tough to assess with code.
Absolutely:):):)
That's a very good point, we need to check the combination of quality along with the quantity. But how do you define quality, just by removing common words or there should be a length of the comments or it can be no two comments by an author should be exact copy.
Aww thank you for the shoutout!
Not at all surprised to see trincowski and ryivhnn :D I've got a lot to learn from them xD
That's a really good addition :D
Unfortunately I don't understand any of the code part, but your compilations and stats are easy to understand (I think haha) Actually most of those posting a lot are also diversifying :D
My pleasure :) Thanks for the encouragement to look further into this data!
Those are some impressive numbers and the number of different people visited is truly remarkable.
Yeah I think some people (no names brittandjosie) have taken it upon themselves to welcome every new account to Hive :)
Wow you took your time to do all these? This is huge and really helpful. Well its good to post but interacting with others is also a key part to be relevant on the blockchain.
I'm not sure how helpful but it is something I was interested to see, cheers :)
So with 319 comments on different author is actually good for me, can be better. Interesting case study.
That is a huge number of different people spoken to outside of your own posts!
Very insightful, having more metrics to work with definitely paints a more complete picture of how the platform is performing and how people are behaving. With this kind of data we could discover areas of improvement in both behaviour and user experience.
Cheers :)
Yeah I think my messing around could well end up as part of a larger project, within an app, at some point.
Oh my it's so cool you can make all of these statistics and show it to us. There are some serious heavy weight champions in here haha
Yes there are, and you are one of them - so many people you have spoken to of late :)
I consider it only fair to give back to a community which offered me such a place to express freely and with members who can appreciate value when they see it. I have to put in the extra effort and hope that people will engage more in here if they realise their posts are being read and appreciated. I must admit it is quite a challenge and I have to set up a schedule in order to give Hive the deserved time
Yep, I am guilty of not commenting off of my own blog that much these days, but I do make the effort to sometimes, as well as read and vote.
You are quite busy here and in real life, but I think the numbers would be well above average.
I have been adding a few names to my follow list lately from those who engage with me. I figure that I am not going to search for new authors that much as I am not a great consumer of random in real life. However, if they take the time and engage well with me, I will give them a follow and see what they are up to.
Been doing exactly the same :)
This shows another picture very different from your last statistics, could be good info for the engagement league, sincerely I didn't expect so drastic changes in the data, some of the users on the list are veterans from the engagement league also but including this possibly they will be better ranked, I'm happy for trinkowski and brittandjosie two users that I appreciate a lot because they do a fantastic job in Hive engaging with others.
Excellent work abh12345.
Well thats do nice and highly appreciated @palasatenea and indeed i will add veteran to my info
Lol I should've written remarkable members, I understand that the veteran word isn't the preferred one for any woman 😄.
No I love veteran
Thanks very much :)
Yeah the 'outreach' commentating is definitely worth highlighting in a different way to engaging on your own blog. Both are essential I think, but for different reasons. Cheers!
I would bet my "engagement" outside of my own posts must be way higher. Not because I'm not replying and engaging enough with the commenters of my posts. But because there don't seem to be many kamikazes here who dare to comment on my articles. LoL
haha :)
Yeah I reckon so, you could soar up the EL!
Thank you for the recognition!! };D
A huge hug 🤗 and a little bit of !BEER 🍻 from @amico!
Un caro abbraccio 🤗 e un po' di BEER 🍻 da @amico!
would love to join this esteemed group
Me too :D
I'm quite thrilled to see that I made it on your sample lists! A great way for me to reach out is by using Communities! If I comment on all the posts in #FeatheredFriends, #ShadowHunters and then go to @barbara-orenya's #FeelGood Community it provides me with more posts to comment on than I can manage in a day! I really hope that we see communities being supported and utilized more and more! A lot of the Redfish that post into Shadowhunters could use more encouragement!