Looking at engagement outside ones own posts - thoughts, method, and a couple of tables to show who's been doing this the most.
The following comment by 
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