# Create a Network chart of your forum - data visualization
**URL:** https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426
**Category:** Administrators
**Tags:** how-to, reporting
**Created:** [1월 4, 2016, 5:13오후 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426 "2016-01-04T17:13:02Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@DavidGNavas](https://meta.discourse.org/u/DavidGNavas)
#### Post date: [1월 4, 2016, 5:13오후 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/1 "2016-01-04T17:13:02Z")
Thanks to the [Data Explorer Plugin](https://meta.discourse.org/t/data-explorer-plugin/32566) and the query that @riking created, we have a great visualization of the interaction in our :discourse: instance.
[](http://bit.ly/1O6vDyv)
You can also [play with it](http://bit.ly/1O6vDyv).
It’s easy to do it:
### 1 - Install [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin
### 2 - Run the riking query or an modified version of it.
> **We use a version that only extracts from a specific group**
>
> ```plaintext
> WITH pairs AS (
> SELECT p.user_id liked, pa.user_id liker
> FROM post_actions pa
> LEFT JOIN posts p ON p.id = pa.post_id
> WHERE post_action_type_id = 2
> )
> SELECT liker liker_user_id, liked liked_user_id, count(*)
> FROM pairs, group_users AS a, groups AS b
> where ( liker = a.user_id
> and a.group_id = b.id
> and b.name ilike 'group1' )
> and
> liked in (select c.user_id from group_users AS c, groups AS d
> where
> c.group_id = d.id
> and d.name ilike 'group1' )
> GROUP BY liked, liker
> ORDER BY count DESC
>
> ```
> **You could do it another modified version of the SQL query that filter by period**
>
> ```plaintext
> WITH pairs AS (
> SELECT p.user_id liked, pa.user_id liker
> FROM post_actions pa
> LEFT JOIN posts p ON p.id = pa.post_id
> WHERE post_action_type_id = 2 AND
> p.created_at >= CURRENT_DATE - INTERVAL '1 month'
> )
> SELECT liker liker_user_id, liked liked_user_id, count(*)
> FROM pairs, group_users AS a, groups AS b
> where ( liker = a.user_id
> and a.group_id = b.id
> and b.name ilike 'group1' )
> and
> liked in (select c.user_id from group_users AS c, groups AS d
> where
> c.group_id = d.id
> and d.name ilike 'group1' )
> GROUP BY liked, liker
> ORDER BY count DESC
>
> ```
### 3 - Download the .csv
### 4 - Create a [Fusion Table](https://www.google.com/fusiontables/data?dsrcid=implicit)
### 5 - Upload the .csv
### 6 - Add a network chart
**Options** : check “link is directional” to see who gives or receives likes from who and “color by columns” to see who receives more than gives or vice versa.
### 7 - Visualization!
In order to visualize names instead of ids, you only [have to change the query to show usernames too](https://meta.discourse.org/t/network-chart-of-your-forum-data-visualization/37426/3), but be careful with data privacy! 😄
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [1월 4, 2016, 10:08오후 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/2 "2016-01-04T22:08:46Z")
I tried this recipe and I like it! Well done and many thanks for posting it.
> [@DavidGNavas](#):
>
> pd. in order to visualize names instead of ids, you only have to change the query to show usernames too, but be careful with data privacy! 😄
I’d like to do this but the query to show usernames is a bit beyond my sql capabilities. I’d be grateful for assistance.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [1월 4, 2016, 11:21오후 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/3 "2016-01-04T23:21:20Z")
> [@DavidGNavas](#):
>
> SELECT liker liker\_user\_id, liked liked\_user\_id, count(\*)
> FROM pairs, group\_users AS a, groups AS b
Replace these two lines with this to have the usernames instead:
```plaintext
SELECT u1.username_lower liker_username, u2.username_lower liked_username, count(*)
FROM pairs, group_users AS a, groups AS b
LEFT JOIN users u1 ON u1.id = liker
LEFT JOIN users u2 ON u2.id = liked
```
---
### Author:  [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [6월 14, 2017, 6:12오전 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/4 "2017-06-14T06:12:05Z")
Bumping an old topic, as I’ve been experimenting with this a lot over the last few days… the network graphs have been very useful for identifying which members to look at in more detail, even just viewing `user_id` (though `username` would be far more valuable).
@riking I’ve tried your suggested change to the query in order include usernames (for example, using the first example query from this thread, looking at a specific user group):
```
WITH pairs AS (
SELECT p.user_id liked, pa.user_id liker
FROM post_actions pa
LEFT JOIN posts p ON p.id = pa.post_id
WHERE post_action_type_id = 2
)
SELECT u1.username_lower liker_username, u2.username_lower liked_username, count(*)
FROM pairs, group_users AS a, groups AS b
LEFT JOIN users u1 ON u1.id = liker
LEFT JOIN users u2 ON u2.id = liked
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs, group_users AS a, groups AS b
where ( liker = a.user_id
and a.group_id = b.id
and b.name ilike 'group1' )
and
liked in (select c.user_id from group_users AS c, groups AS d
where
c.group_id = d.id
and d.name ilike 'group1' )
GROUP BY liked, liker
ORDER BY count DESC
```
That runs into this problem though:
> PG::UndefinedColumn: ERROR: column “liker” does not exist
> LINE 16: LEFT JOIN users u1 ON u1.id = liker
> ^
> HINT: There is a column named “liker” in table “pairs”, but it cannot be referenced from this part of the query.
This is beyond my (extremely basic!) SQL knowledge to troubleshoot ☹ Is there something obvious that I’m missing with this query?
---
### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [3월 26, 2026, 7:49오후 UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/5 "2026-03-26T19:49:25Z")
> [@커뮤니티 네트워크 시각화](https://meta.discourse.org/t/community-network-visualisation/184189):
>
> 요약: 사용자 네트워크 시각화 link GitHub: https://github.com/merefield/discourse-user-network-vis [GitHub - merefield/discourse-user-network-vis: A plugin that creates a User Network Visualisation to show social links between users · GitHub](https://github.com/merefield/discourse-user-network-vis)arrow_right 설치: [플러그인 설치 가이드](https://meta.discourse.org/t/install-a-plugin/19157)를 따르세요. 기능 커뮤니티를 네트워크 시각화로 표현합니다 자동으로 추가되는 메뉴 항목을 통해 접근할 수 있습니다 (비활성화 가능): 사이드바/드롭다운 메뉴에서 User Network를 클릭하거나 yoursite.com/usernetworkvis로 이동하여 접근할 수 있습니다 - 참고: 시각화를 보려면 등록된 사용자로 로그인되어 있어야 합니다. …
오늘 이 주제를 다시 살렸습니다.