Steem: Is the 'recovery_account' a guide to how engaged a new account is likely to be?

This is an analysis which looks at the engagement level of accounts created through the various methods to try and assess which creation method shows the best/worst engagement, and why this might be the case.

_{Figure 1.}

---

Repository

https://github.com/steemit/steem

Introduction

Engagement and retention of Steem accounts and users is a hot topic. Is it possible to glean any new insight into how likely a new account is to engage and interact, based on the creation method of this account?

Scope / Assumptions made

The accounts used in this analysis have been created between 5/01/2018 0:00 AM and 6/30/2018 11:59 PM - May and June 2018.

Engagement has been assessed in terms of:

• Accounts that have Posted at least once
• Accounts that have Commented at least once
• Accounts that have given a positive vote (upvote) at least once
• Accounts that have given a negative vote (downvote) at least once
• Accounts that have voted for at least one Witness

Accounts have been grouped into the following creation 'routes', and the total accounts created via this route are included:

Recovery_account	Total	Description
steemcreate	44	Steem account creation with a Credit Card
steem.apps	72	Korean account creation service
utopian.signup	154	utopian-io account creation service
cnsteem	221	Chinese account creation service
anonsteem	1160	Anonymous account creation service by @someguy123
blocktrades	2985	@blocktrades account creation service
steem	91327	steemit.com account creation
user_accounts	1814	steemconnect / steem.js account creation service

user_accounts: This group holds all the accounts not created using any of the other methods listed in the table - this are accounts created by existing accounts (or accounts that have later set their recovery_account as an existing account - see below).

From the 97777 accounts created in May and June 2018, 55 of these accounts have changed their 'recovery_account'.

The assumption has been made that this number is too small to have a noticeable impact on the results and so the figure has not been subtracted from 'user_accounts'.

---

General activity / engagement by recovery_account

The following table shows the number of accounts, grouped by recovery_account, that have made at least one of the actions listed in the top row.

Recovery Acc.	Accounts	Posted	Commented	Upvote	Downvote	Wit_Vote
user_accounts	1814	725	605	1011	179	157
steemcreate	44	23	20	22	2	2
steem.apps	72	36	37	45	2	2
utopian.signup	154	24	21	77	5	7
cnsteem	221	133	113	127	9	16
anonsteem	1160	606	491	721	82	63
blocktrades	2985	1942	1472	1978	278	240
steem	91327	32240	22723	43191	3071	2759

As we can see, 'steem' is far an away the most popular Steem account creation method - and was used to create 93.4% of all new accounts in May and June 2018. 'blocktrades' is the 2nd most popular account creation route with just over 3%, and account creation via steemconnect (or steem.js routes) using an existing account is the 3rd most popular route (accounting for the 55 accounts in this data-set that had their recovery account changed).

Looking at these numbers in percentage terms may give more insight into which account creation route leads to the more engaged, or active account. The following table gives the percentage of accounts, created in May and June 2018, grouped by recovery_account, that took part in at least one of the 'engagement' activities.

The highest percentage in each engagement category is marked in green, and the lowest in red.

Before analyzing the the data presented above, I'd like to suggest the following which is of personal opinion only.

Accounts created via steemit.com ('steem') have the most potential to be 'new' people joining the Steem blockchain. This is due to steemit.com being the first gateway to the Steem blockchain, and being the most popular of the 'sites' listed above.

Both 'anonsteem' and 'blocktrades' account creations are likely to be used by existing account holders who have some knowledge of Steem, and cryptocurrency. The remaining account creation sources are also reasonably likely to be used by existing account holders.

With these assumptions in place, we can try to make sense of the percentages in the table above.

steem: The most popular account creation method contains some of the lowest engagement activity percentages, with just over one third of these accounts producing a top level post, and less than a quarter writing a single comment/reply. This account creation source also yields the lowest 'upvote' %, and a poor turnout with regards to witness voting.

utopian.signup: Less than one sixths of the accounts generated via this method have produced a single top level post or comment. This could be due to the fact that the utopian-io website is currently under redevelopment, and will come into play once the site is live. It's also possible that these accounts were created as a test of the account creation service put in place by utopian-io.

blocktrades: This account creation method shows up best as far as this engagement criteria goes. Almost two thirds of these accounts have created a top level post, with two thirds casting at least one upvote.

---

Summary

Averaging the percentages over each account creation method analysed, the following table can be produced:

The chart named 'Figure 1' at the top of this post is a reflection of the data within the table above.

We can see here that accounts created via 'blocktrades' show the highest engagement levels across the activities listed, whilst utopian.signup gives the lowest average, closely followed by 'steem'.

With reference to the personal opinions given in the previous section, the reason for the seemingly higher levels of engagement from account sources such as 'blocktrades' and 'anonsteem', could well be due to the existing/experienced user having a plan or purpose for a new account.

In the same respect, a new account generated via 'steem' (and perhaps a new person) may not arrive with a distinct goal for the account, and due to this then fail to perform any of the activities analysed. Another reason for the low percentages for a 'steem' created account, could be the turnaround time in account request to confirmation. If this time was reduced, would we see an improved set of numbers here?

In the future, it could well be the case that there are more account creation services, perhaps coming from existing or planned dapps, or via SMT sites. I would speculate that accounts created via these sources would produce relatively good engagement/activity figures due to the account holder potentially arriving with a clear purpose or goal for the account.

We shall see if both of these statements are true or not in time.

Is the 'recovery_account' a guide to how engaged a new account is likely to be?

Possibly, although not conclusive, the data does suggest that certain account creation sources are more engaged than others.

---

Tools and queries used to gather the data and produce the report

The charts used to present the data were produced using MS Excel.

The data is sourced from SteemSQL - A publicly available SQL database with the blockchain data held within.

The SQL queries to extract the data have been produced in LINQPAD 5 SQL Server Management Studio and are as follows:

-- 97777
select count(*) from accounts
where created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
and name not in (select Account_to_recover from TxAccountRecovers)

select recovery_account, count(recovery_account) from accounts
where created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by recovery_account
order by count(recovery_account) 

--'steemcreate'
--'steem.apps'
--'utopian.signup'
--'cnsteem'
--'anonsteem'
--'blocktrades'
--'steem'

--steemcreate   44
--steem.apps    72
--utopian.signup    154
--cnsteem   221
--anonsteem 1160
--blocktrades   2985
--steem 91327
--user_accounts 1814

select count(*) from accounts where recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

------------------------
-- POSTS

-- 725 of 1814 at least 1 post
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 23 of 44
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'steemcreate'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 36 of 72
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'steem.apps'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 24 of 154
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'utopian.signup'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 133 of 221
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'cnsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 606 of 1160
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'anonsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 1942 of 2985
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'blocktrades'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 32240 of 91327
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth = 0 and
recovery_account = 'steem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

---------------------------------------------

-- COMMENTS

-- 605 of 1814 at least 1 comment
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author


-- 20 of 44
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'steemcreate'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 37 of 72
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'steem.apps'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 21 of 154
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'utopian.signup'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 113 of 221
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'cnsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 491 of 1160
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'anonsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 1472 of 2985
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'blocktrades'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author

-- 22723 of 91327
select c.author, count(c.author) from comments c
inner join accounts a on c.author = a.name
where c.depth <> 0 and
recovery_account = 'steem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by c.author


------------------------------------

-- VOTES 

-- 1011 of 1814 at least 1 positive vote
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 22 of 44
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'steemcreate'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 45 of 72
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'steem.apps'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 77 of 154
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'utopian.signup'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 127 of 221
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'cnsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 721 of 1160
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'anonsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 1978 of 2985
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'blocktrades'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 43191 of 91327
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight > 0 and
recovery_account = 'steem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

----------------------------------

-- DOWN VOTES 

-- 179 of 1814 at least 1 down-vote
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 2 of 44
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'steemcreate'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 2 of 72
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'steem.apps'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 5 of 154
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'utopian.signup'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 9 of 221
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'cnsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 82 of 1160
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'anonsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 278 of 2985
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'blocktrades'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

-- 3071 of 91327
select v.voter, count(v.voter) from txvotes v
inner join accounts a on v.voter = a.name
where v.weight < 0 and
recovery_account = 'steem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'
group by v.voter

------------------------

-- WITNESS VOTES

-- 157 of 1814 at least 1 witness vote
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account not in ('steemcreate',
'steem.apps',
'utopian.signup',
'cnsteem',
'anonsteem',
'blocktrades',
'steem')
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 2 of 44
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'steemcreate'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 2 of 72
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'steem.apps'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 7 of 154
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'utopian.signup'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- of 221
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'cnsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 63 of 1160
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'anonsteem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 240 of 2985
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'blocktrades'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

-- 2759 of of 91327
select a.name from accounts a
where (a.proxy <> '' or a.witnesses_voted_for > 0)
and a.recovery_account = 'steem'
and a.created between '5/01/2018 0:00 AM' and '6/30/2018 11:59 PM'

_{This data was gathered during July and compiled on the 23rd July 2018 at 10 am (UCT)}

---

Cheers

Asher