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:

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

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