# 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:** [January 4, 2016, 5:13pm 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:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![DavidGNavas](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/davidgnavas/32/68313_2.png) [@DavidGNavas](https://meta.discourse.org/u/DavidGNavas)\
**Post date:** [January 4, 2016, 5:13pm UTC](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426/1 "2016-01-04T17:13:02Z")

</div>

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.

[![](https://global.discourse-cdn.com/meta/original/3X/3/9/39b31332ba376814335437f8ca011aad6ecc4995.png)](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

 ![](https://global.discourse-cdn.com/meta/original/3X/1/2/12df474cbc670fc78d3acc80b8795bea172ede88.png)

### 6 - Add a network chart

 ![](https://global.discourse-cdn.com/meta/original/3X/9/4/94e5da4c27d5baa782f4bc6ac250abef940d86eb.png)

 ![](https://global.discourse-cdn.com/meta/original/3X/b/3/b37881fb7bf97acd3442a4aa8d9e08a15e8b78d3.png)

**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.

 ![](https://global.discourse-cdn.com/meta/original/3X/9/a/9a88185065e5f931da94ae09201dcdaf5829cb8c.png)

### 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! 😄

---

_[View the full topic](https://meta.discourse.org/t/create-a-network-chart-of-your-forum-data-visualization/37426)._