Engagement away from home

avatar

Looking at engagement outside ones own posts - thoughts, method, and a couple of tables to show who's been doing this the most.


people-2557396_1920.jpg
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
AccountCommentsLength
galenkp41191070482
tattoodjay3115582747
melinda0101002960337529
johannpiber2675907985
russellstockley2660822739
tarazkp2636492735
flaxz2520343921
chekohler2364434206
abh123452231408976
trincowski2047237592
wakeupkitty1884236339
artemislives1867512427
brittandjosie1834449073
silvertop1796292852

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'


image.png

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:

  1. Find the first '@' and tell me what position it is in the string
  2. 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)
  3. 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
AccountCommentsLength
galenkp1959476231
tattoodjay1842317958
flaxz1735239697
melinda0101001734220625
rockor1721138017
trincowski1660209792
silvertop1654264934
ryivhnn1634666635
chekohler1609276496
johannpiber1525517684
artemislives1457407770

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):

AccountNo. of different people visited
joeyarnoldvn696
johnolusegun468
brittandjosie466
creativemary365
mattsanthonyit349
steemsupporter343
fredkese340
silvertop328
naturalmedicine326
ewkaw325
alokkumar121324
melinda010100321
chekohler320
codingdefined319
jeffjagoe312
artemislives310
mayvileros301
sidwrites301

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



0
0
0.000
60 comments
avatar

A huge hug 🤗 and a little bit of !BEER 🍻 from @amico!


Un caro abbraccio 🤗 e un po' di BEER 🍻 da @amico!

0
0
0.000
avatar

Those are some impressive numbers and the number of different people visited is truly remarkable.

0
0
0.000
avatar

Yeah I think some people (no names brittandjosie) have taken it upon themselves to welcome every new account to Hive :)

0
0
0.000
avatar

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.

0
0
0.000
avatar

I'm not sure how helpful but it is something I was interested to see, cheers :)

0
0
0.000
avatar

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?

0
0
0.000
avatar

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

Nothing wrong with this at all, I guess it depends how many friends you want :)

Should we consider qualitative measures of comments as well as quantitative?

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 :)

0
0
0.000
avatar

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.

0
0
0.000
avatar

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.

0
0
0.000
avatar

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.

0
0
0.000
avatar

So with 319 comments on different author is actually good for me, can be better. Interesting case study.

0
0
0.000
avatar

That is a huge number of different people spoken to outside of your own posts!

0
0
0.000
avatar

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.

0
0
0.000
avatar

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!

0
0
0.000
avatar

Well thats do nice and highly appreciated @palasatenea and indeed i will add veteran to my info

0
0
0.000
avatar

Lol I should've written remarkable members, I understand that the veteran word isn't the preferred one for any woman 😄.

0
0
0.000
avatar

Ok, so you lost me when talking about queries and code and .. lol..
It's great that you can pull out this info though :)

0
0
0.000
avatar

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

0
0
0.000
avatar

Well, keep up the good work, I'd say :) and have a wonderful weekend ;)

0
0
0.000
avatar

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

0
0
0.000
avatar

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!

0
0
0.000
avatar

Good old string query, almost sounds like a sitcom!! :OD

I must try harder in the other commenting!

0
0
0.000
avatar

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

The final list shows the accounts who have commented on the most unique accounts' posts on Hive (since 3 pm 20th March 2020)

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

0
0
0.000
avatar

My pleasure :) Thanks for the encouragement to look further into this data!

0
0
0.000
avatar

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 :)

0
0
0.000
avatar

I will look adding these metrics into the EL, and probably remove a couple of less important ones (like witness votes).

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 :)

Exactly, and why i'm not all in on the idea to chop off short comments.

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.

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.

0
0
0.000
avatar

Less important ones WITNESS VOTES , maybe people who havent votes 30 witnesses are not allowed to participate, witnesses are important

0
0
0.000
avatar

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.

0
0
0.000
avatar

Cheers :)

Yeah I think my messing around could well end up as part of a larger project, within an app, at some point.

0
0
0.000
avatar

~ 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

0
0
0.000
avatar

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 :)

0
0
0.000
avatar

I say revamp away , biking can be done tomorrow

0
0
0.000
avatar

Beer today, biking tomorrow... no revamp this week! :D

0
0
0.000
avatar

And if so, do you think this type of 'outreach' engagement, should be scored higher than engaging on your own posts?

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.

0
0
0.000
avatar

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

0
0
0.000
avatar

Yes there are, and you are one of them - so many people you have spoken to of late :)

0
0
0.000
avatar

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

0
0
0.000
avatar

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.

0
0
0.000
avatar

Thanks for the feedback, I think they will both be added :)

0
0
0.000
avatar

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.

0
0
0.000
avatar

You are quite busy here and in real life, but I think the numbers would be well above average.

0
0
0.000
avatar

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.

0
0
0.000
avatar

Been doing exactly the same :)

0
0
0.000
avatar

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.

0
0
0.000
avatar

Yes, I think I will include these metrics in the EL, probably starting a week on Sunday.

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.

Very true, and that is an insane number of accounts to keep up with. Seems you are doing a solid job sir, hats off :)

0
0
0.000
avatar

It will be interesting to see if it changes the results any, the new metrics I mean.

0
0
0.000
avatar

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

0
0
0.000
avatar

haha :)

Yeah I reckon so, you could soar up the EL!

0
0
0.000
avatar

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' with created > '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.

0
0
0.000
avatar
(Edited)

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.

select
cast(day(timestamp)as varchar(20)) + '/' + cast(month(timestamp)as varchar(20)) + '/' + cast(year(timestamp)as varchar(20))

0
0
0.000
avatar

If you plan to export a date part to Excel, better (and faster) to use CONVERT(DATE,timestamp)

0
0
0.000
avatar

That works, cheers :D

0
0
0.000
avatar

yoda.png Your SQL skills, improved they are now!

0
0
0.000
avatar

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!

0
0
0.000