Analyzing the Data Engineering Discord Server with DuckDB

In case you didn't know, there's a fantastic Data Engineering community on Discord (invite here). I've been a member of this server since the very start, and I've seen it grow substantially over the years. If you read that last sentence again, you'll notice that it's really vague. When was the start? Was I really there at the start, or did I just join (waves hands) a while ago? How much has the server grown over time? How many members are there? How often does the average user post? What are the most popular channels? I could go on.

When I was thinking about all of this one day I realized I was uniquely placed to answer these questions for a few reasons:

1. I'm a data engineer by trade who spends all day working with databases and moving data around, and who also writes and reads a lot of code for fun.
2. I've been tinkering with Discord bots for several years, having written several bots for an online game that I used to play, as well as a fun meme-generating bot. Additionally, I recently wrote the Plumber bot for the Data Engineering Discord server to manage static content such as rules, FAQs, and recommended resources.
3. I'm an admin on the server and so have the requisite access to do a project like this.

So with that, I set out to find a way to learn more about this community, and play around some new(ish) tech along the way.

Getting the Data

The full source for the bot is at available on my Github account, but I'll walk through some of it below.

First I identified what I wanted to collect. I knew I wanted to gather information on the members of the server, but I also wanted information on messages. In order to get messages, I'd need channels. Luckily these three entities are naturally related and form a compact and simple data model. I defined the three tables as follows:

CREATE TABLE IF NOT EXISTS 
users(
    id INT64 PRIMARY KEY, 
    name VARCHAR, 
    joined_at TIMESTAMP,
    created_at TIMESTAMP
    );

CREATE TABLE IF NOT EXISTS
channels(
    id INT64 PRIMARY KEY,
    category VARCHAR,
    category_id INT64,
    created_at TIMESTAMP,
    mention VARCHAR,
    name VARCHAR,
    topic VARCHAR
    );

CREATE TABLE IF NOT EXISTS
messages(
    id INT64 PRIMARY KEY,
    channel_id INT64,
    user_id INT64,
    content VARCHAR,
    clean_content VARCHAR,
    jump_url VARCHAR,
    created_at TIMESTAMP
    );

Once that was all done, here was the structure of the tables:

Now that I had the tables defined I had to collect the data. I used boilerplate code I had written for the Plumber bot to create the connection to Discord, and used the DuckDB Python API to create and manage my database.

NOTE

I used DuckDB for a few reasons. First, it's a somewhat new piece of technology that I had read a lot about but hadn't had a good use case for yet. Second, it was supposed to have a similar API to SQLite which I had used in the past and was familiar with. And third, I knew that I'd be issuing analytical queries on my laptop against a potentially large dataset, and DuckDB was designed for exactly that use case. It was a natural fit.

The heart of the program comes down the on_ready() method, which runs once the bot has connected to the server.

async def on_ready(self) -> None:
    # Connected to the Discord server
    server = client.get_guild(self.server_id)
    print(f"Backfill: {self.backfill}")
    # Create a connection to a file called 'server.db'
    con = duckdb.connect("server.db")

    # CREATE IF NOT EXISTS
    self.create_table_users(con)
    self.create_table_channels(con)
    self.create_table_messages(con)

    # Get all users
    await self.get_users(con, server)

    # Get all channels, and for each channel get all messages
    await self.get_channels_and_messages(con, server, self.backfill)

    # Close connection to DuckDB and Discord
    con.close()
    await client.close()

At a high level this code connects to the Discord API, finds a given server by its ID, creates or connects to a DuckDB database, creates the required database tables, and then pulls and stores all users, channels, and messages from the provided server. You can run it with the --backfill flag to re-collect all data from the beginning (overwriting any existing content due to INSERT OR REPLACE), and then you can run it again without the --backfill flag to just update the existing data.

Due to the way the Discord API works, users and channels are very quick to retrieve, but getting messages can take a bit longer. I also acknowledge that I'm doing row level inserts here which is a sub-optimal method for loading data into a columnar system. Doing a bulk load via a COPY command would likely be more performant. Still, I'm able to collect and store all of the data from the Data Engineering server in ~20 minutes so no need to over-engineer.

Analysis

Even with only three tables there are a lot of questions we can answer. Let's list some out, starting with the basics, and then write some queries to solve them:

How many members are there?

select count(*) from users;

How many users have joined each month?

select
    datetrunc('month', joined_at) as month,
    count(*) as join_count 
from users 
group by 1 
order by 1;

Plotted:

Looks like we had a peak around mid-2020 which coincides with a bump in messages around that time (see below), and users joining continues at a relatively steady pace.

How many posts are there in total?

select count(*) from messages;

Which channels get the most activity?

-- Omits channels with no messages as messages is the base table
select 
    c.name as channel_name, 
    count(*) as message_count,
    round(count(*)/(select count(*) from messages)*100,2) as pct_total
from messages m 
left join channels c 
    on m.channel_id = c.id 
group by 1 
order by 2 desc;

Maybe we have too many channels 🤔

What time of day is the server the most active?

select
    datepart('hour', created_at) as hour_of_day,
    count(*) as message_count,
    round(count(*)/(select count(*) from messages)*100,2) as pct_total
from messages
group by 1
order by 1;

Here it is plotted:

So it looks like activity tends to peak around 17:00 UTC, which would be towards the end of the workday in Europe and afternoon in the US.

How has activity on the server changed over time?

select
    datetrunc('month', created_at) as month,
    count(*) as message_count
from messages
group by 1
order by 1;

I also plotted this and added a horizontal line representing the average posts per month over all time:

I'm not entirely sure what to make of this. It seems that we had a peak around the end of 2020, then again mid-2022, and right now (end of June 2023) we're in a bit of a dip.

How many members joined Discord specifically to join this server?

This is assuming that users who joined the server on the same day they joined Discord signed up specifically to join the Data Engineering server

select 
count(*) as count,
    round(count(*)/(select count(*) from users)*100,3) as pct_same_day
from users 
where datediff('day', created_at, joined_at) = 0;

Conclusion

There's a lot more analysis that could be done, but I think with even the little effort I put into this I was able to learn some interesting things about this server and its activity. It was also great to finally have a chance to get hands on with DuckDB. While the dataset was by no means large (the database file shows only 56MB on my filesystem), DuckDB still handled it like a champ and had all the functionality I was looking for. I can definitely see myself using it for other projects in the future.