How to understand? We do analytics for the bot from scratch. Part 2 - Making a Dashboard in Redash

In the previous article, we prepared our test application – a bot for anonymous messaging in Telegram.

It’s time to display all the data we have collected. For this we will use Redash.

What is in the base

The data in the database (MongoDB) is stored in the following form.

User:

{
  "_id": {
    "$oid": "64908ebc5a1d121b983e2406"
  },
  "user_id": 5784928745,
  "username": "nickname",
  "datetime": 1687195324.282187,
  "status": "active"
}

Message:

{
  "_id": {
    "$oid": "64908ec65a1d121b983e2407"
  },
  "user_id_from": 5784928745,
  "user_id_to": "5982379843",
  "username_from": "nickname_from",
  "username_to": "nickname_to",
  "content": "Hi",
  "message_type": "question",
  "datetime": 1687195334.6866229
}

We write requests

Querying MongoDB is an art form in my opinion. Let’s try to master them a little.

Active Users

Get the number of active users:

{
	"collection": "users",
	"query": {
		"status": "active"
	},
	"count": true
}

Immediately create a widget for this request (Counter):

Remote Users

Remember, we did not delete users, but simply marked them as such? Now this is what we need.

The request is the same, only we change the status:

{
	"collection": "users",
	"query": {
		"status": "deleted"
	},
	"count": true
}

The widget is created in exactly the same way as in the previous request.

All users

We are also interested in knowing the sum of all users of the bot? I think yes. Let’s do it:

{
	"collection": "users",
	"count": true
}

The request turned out to be even simpler, heh.

Messages

We have already learned how to count the number of elements in collections, let’s count the messages:

{
	"collection": "messages",
	"count": true
}

We may also want to see all of our messages in a table, since we are saving them (and – anonymous):

{
    "collection": "messages",
    "aggregate": [
        {
            "$addFields": {
                "datetime": {
                    "$toDate": {
                        "$multiply": [
                            "$datetime",
                            1000
                        ]
                    }
                }
            }
        }
    ]
}

Here the request is more complicated. Let’s figure it out.

$toDate expects milliseconds, which it will convert to a date. To do this, we must first convert our Unix Timestamp seconds to milliseconds. We do this with $multiply – multiply by 1000. After these manipulations $toDate will return the date. Put it in a column datetime, which we already have (overwrite). All this allows us to $addFields (same as $project, but does not erase all existing columns). We wrap in aggregate and start.

We add a table widget, where we can turn off the columns we don’t need, or swap them:

We would also like to build a graph that will show how activity changes by day. Let’s do it:

{
    "collection": "messages",
    "aggregate": [
        {
            "$addFields": {
                "datetime": {
                    "$toDate": {
                        "$multiply": [
                            "$datetime",
                            1000
                        ]
                    }
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$datetime"
                    }
                },
                "count": {
                    "$sum": 1
                }
            }
        }
    ]
}

In this query, we have added grouping by column datetime into a column _ideach group stores the number of elements thanks to count.

We create a chart where _id is the x-axis, and count -Y:

User Graph

Phew, now the most important thing is the graph of the number of users by day. We change the name of the collection from the previous request and get the result:

{
    "collection": "users",
    "aggregate": [
        {
            "$addFields": {
                "datetime": {
                    "$toDate": {
                        "$multiply": [
                            "$datetime",
                            1000
                        ]
                    }
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$datetime"
                    }
                },
                "count": {
                    "$sum": 1
                }
            }
        }
    ]
}

We figured out the requests, the most pleasant thing remains – to assemble a dashboard.

Putting together a dashboard

All queries we have made need to be saved and given sensible names. Also, the visualization that we gave them must be preserved. Then go to the dashboards tab, create a new one and import all the widgets one by one: