This study looks to derive statistics on Steem blockchain users in the United Kingdom, the country in which I reside. 

The aims of the study are:
* To build a picture of the geographical spread of Steem accounts across the United Kingdom. This information could be useful to organisations such as @promo-steem for their marketing operations.
* To look at the level of activity of Steem users across the United Kingdom. These statistics could help UK Steem users aiming to build their local networks or organise events. 
* To look at the geographical diversity of new users across the United Kingdom. This also has value as marketing information and could additionally be used to help established Steem users welcome new joiners in their area. 
##

![cover uk analysis.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516226285/yj5ixseeb5uzzrg89t45.png)

The approach I have applied does not use any particular post tag but instead relies upon the account location information that the user enters in their profile. It should be noted that this data relies upon user input and a large proportion of accounts leave this information blank. However, having looked at the data, there are sufficient entries to draw statistically reliable results. Readers of this analysis should bear in mind this limitation and that actual United Kingdom user numbers will be higher than those stated.


There are three parts to the analysis:
* **Geographical spread of all United Kingdom accounts**: 
A geographical breakdown of all United Kingdom Steem accounts by city, county or country. All accounts over all time are included in this first study. This will aim to illustrate where steem adoption has been highest to date and could provide useful information for marketing research;  

* **Geographical spread and activity statistics of active United Kingdom accounts**: 
A geographical breakdown of all *active* United Kingdom Steem accounts. Active has been defined as having posted an article or comments in the last month. This will illustrate where current Steem activity is highest in the UK and could be useful to Steem users looking to understand whether they could build a local network. 

* **Geographical spread of new United Kingdom accounts**: 
A geographical breakdown of *new* United Kingdom Steem accounts. New has been defined as an account created within the last two weeks. This will illustrate current hotspots for growth.
##  
---

## Outline

1. Scope of Analysis
2. Tools Used
3. Geographical spread - All accounts
4. Geographical spread and activity statistics - Active accounts
5. Geographical spread - New accounts
6. Scripts

##

---

## 1 Scope of Analysis

The analysis is based on the data for UK accounts obtained through SQL queries of SteemSQL, a publicly available Microsoft SQL database containing all the Steem blockchain data. 

The UK account data has been filtered from the overall Steem blockchain data by use of the `location` label information in the `json_metadata` column of the `Accounts` table. Due to the huge number of different account locations, the filtering took place using an iterative process in a spreadsheet, rather than through the SQL query.

The first part of the analysis includes data on accounts over all time. The second and third parts of the analysis focus on the last month and the last two weeks respectively. The data has been filtered by date using the timestamps in the `last_post` and `created` columns of the `Accounts` table.

---

## 2 Tools Used

Valentina Studio, a free data management tool, was used to run the SQL queries. The raw data was then verified and analysed in the spreadsheet application of the LibreOffice office suite. 

Graphs and charts were produced using Numbers, the Mac spreadsheet tool, or using RAWGraphs, an open source data visualisation framework.

SQL scripts are included at the end of this analysis. 

---

##

## 3 Geographical spread - All accounts

The first map below shows the geographical spread of all Steem accounts created over all time in the UK *(those with location information listed).*

The map illustrates that the geographical distribution is quite wide, with over 150 different cities and towns represented.

![Screen Shot 2018-01-17 at 12.52.25.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516227377/nskqfowvo9iu942trkfx.png)

The largest locations can be better seen on this second map:

![Screen Shot 2018-01-17 at 13.00.45.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516227446/wgxcgltujfajormkfafi.png)

It is clear that London has far and away the largest number of registered accounts, followed by Manchester, Birmingham, Leeds, and Edinburgh. It is perhaps unsurprising that the largest cities have the largest numbers of accounts. However the dominance of London is quite stark. We will see how active each of these areas is in the next section. 

##

---

## 4 Geographical spread and activity statistics - Active accounts

For activity statistics I have analysed the number of active users and the payouts from those active users over the last month.

##### Number of users

![active accounts numbers - cropped.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516233900/twrf2zpsrlpvri9ikej2.png)

##

The pattern is not dissimilar to overall accounts with London again dominating followed by other major cities. There are easily enough active accounts in London to create a sizeable network. Smaller networks and connections could also be possible for the other main cities and university towns *(particularly given that there will be more users without a location referenced).*

##### Post payouts over last month

![Active accounts2.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516234170/lcxmocynnlxioco2qhue.png)

From a quick study it becomes clear that the payout levels over the last month can be easily skewed by a single user. One active user in Newbury for example is sufficient to elevate the town to a high ranking on the chart, despite it not appearing on the chart by user numbers. Other than these occasional distortions the pattern is not overly dissimilar to that for user numbers.

---

## 5 Geographical spread - New accounts

Here is the map of new accounts created over the last two weeks:

![Screen Shot 2018-01-17 at 18.54.58.png](https://res.cloudinary.com/hpiynhbhq/image/upload/v1516227532/g9trmq5fsy4yqbvteezu.png)

As for overall accounts and activity statistics, London dominates the numbers of new accounts. Given the overall account numbers and population size it is likely that London generates the largest number of new accounts in most weeks. However there has also been recent marketing by @promo-steem in London which may have additionally significantly increased these numbers over January.

Here are the overall statistics for the top cities with the remaining cities and wider UK locations (counties and countries) grouped by country.

|Place|New Accounts|
|:-|:-|
|London|91|
|Manchester|9|
|Bristol|5|
|Birmingham|2|
|Cambridge|2|
|Newcastle|2|
|Yorkshire|5|
|England|31|
|Glasgow|3|
|Aberdeen|1|
|Scotland|12|
|Belfast|2|
|Northern Ireland|2|
|Swansea|1|
|Wales|8|
|UK|38|
|Total|214|

##
Manchester, Bristol, Glasgow and Belfast are the next four cities ranked by numbers of new users.
*England, Wales, Scotland , Northern Ireland and UK relate to new accounts in those countries not included in the individual cities in the table.*

If anyone is interested in the account names for a particular city or country I would be happy to provide these. 

---

## 6 Scripts

This was the script used for the second and third parts of the analysis - active accounts and new accounts.


~~~~

/* LOCATION ANALYSIS - ACTIVE */
SELECT
    Comments.author,
    IIF(CHARINDEX('\"location\":\"', accounts.json_metadata) > 0, SUBSTRING(accounts.json_metadata, CHARINDEX('\"location\":\"', accounts.json_metadata) + 15, CHARINDEX('\"', accounts.json_metadata, CHARINDEX('\"location\":\"', accounts.json_metadata) + 15) - CHARINDEX('\"location\":\"', accounts.json_metadata) - 15), 'Unspecified') as [Location], 
    convert(date, Accounts.last_post) as [LastPost],
    convert(date, Accounts.created) as [AccountCreated],
    Count(Comments.author) AS [Posts],
    Count(distinct Comments.author) AS [DistinctCommentAuthor],
    count(Comments.parent_author) AS [ParentAuthor],
    count(distinct Comments.parent_author) AS [DistinctParentAuthor],
    sum(CONVERT(REAL,Comments.pending_payout_value)) AS [PendingPayoutValue],
    sum(CONVERT(REAL,Comments.curator_payout_value)) AS [CuratorPayoutValue],
    sum(CONVERT(REAL,Comments.total_payout_value)) AS [TotalPayoutValue]

~~~~


~~~~

FROM
      Comments (NOLOCK)
        inner join Accounts (NOLOCK)
            on Comments.author = Accounts.name

~~~~

~~~~

WHERE
    convert(date, Accounts.last_post) >= '2017-12-17' and
    convert(date, Comments.created) >= '2017-12-17' 

~~~~


~~~~

GROUP BY
    Comments.author,
   IIF(CHARINDEX('\"location\":\"', accounts.json_metadata) > 0, SUBSTRING(accounts.json_metadata, CHARINDEX('\"location\":\"', accounts.json_metadata) + 15, CHARINDEX('\"', accounts.json_metadata, CHARINDEX('\"location\":\"', accounts.json_metadata) + 15) - CHARINDEX('\"location\":\"', accounts.json_metadata) - 15), 'Unspecified'),
   Accounts.last_post,
   convert(date, Accounts.created)

~~~~

A simplified versions of this script was used for the analysis of all accounts. The query can be obtained from the above simply by removing all the elements relating to the Comments table (including the inner join) and grouping instead by `name` from the Accounts table.  


##

That's all for today. Thanks for reading!


<br /><hr/><em>Posted on <a href="https://utopian.io/utopian-io/@miniature-tiger/geographical-analysis-of-uk-steem-accounts">Utopian.io -  Rewarding Open Source Contributors</a></em><hr/>