Automation of reporting Part 1

I continue my publications on analytics:

• A Data Analysis in business — BI system and visualisation
• Analyses of Product Metrics
• А/B-tests — Part 1/3(AA-test)
• А/B-tests — Part 2/3(AB-test)
• A/B-tests — Part 3/3 (relationship metrics)
• Building an ETL-Pipeline (Airflow)

In this series of articles, we will deal with the automation of basic reporting. We will set up automatic sending of the report in a telegram.

How to create a bot:

1. First, let’s create our own telegram bot using @BotFather.
2. Click on start and send the command /newbot.
3. Next, we will enter the name of the future bot (my neme — Zmey56_test). The name may not be unique, it can be changed.
4. Then enter the nickname of the bot. The nickname must necessarily end with the word “bot” and be unique. It cannot be changed.

Now we need to find the chat_id to which the bot will send information. It is important to note that telegram is configured so that chat_id has a positive value for personal chats, and a negative value for groups. Start a dialogue with the bot to allow him to write in private messages. To do this, follow the link t.me /<nickname of the bot> and launch the bot. To allow the bot to write to the chat/channel, first add the bot there and give it the rights to send messages in the channel settings. To get the chat_id, use the link: https://api.telegram.org/bot <your_bot token>/getUpdates. As a result, you will receive the following response, where you can find the necessary chat_id:

Now let’s start managing the bot via python. To do this, you need to install the telegram and python-telegram-bot libraries.

After that, we will check our bot how it works.

Getting access to the bot

Sending a text message

Sending an image

Sending a file

It is important to note that I take the data to form a file from the database that was provided to us. You can generate data from other sources.

Result in my group:

Now let’s go directly to the task. It included setting up automatic sending of a report in a telegram every morning. The report should contain:

• a text with information about the values of key metrics (DAU, Views, Likes, CTR) for the previous day
• a graph with the values of metrics for the previous 7 days

Let’s write the main function that will collect the report:

After executing this code, the following report comes to our group:

Now let’s build graphs for our report:

Next, I will add the following code to the end of the feed_report function:

After a test check of my code, I get the following graph in my group:

We have combined four graphs into one in order to exclude separate messages for each graph and not annoy users.

For automation this time we will use GitLab CI/CD. It is better to read about how to work with this system in the official documentation. You also need to know what dockers are.

In order for the token of our bot not to be visible in the public domain, it is desirable to hide it.

To hide the bot token, you need to act according to the following algorithm:

• In GitLab, go to Settings -> CI/CD -> Variables
• Add a secret variable by specifying its name and value
• In the code, import the os library and replace the value of the secret variable with the string os.environ.get(“REPORT_BOT_TOKEN”)

After that, upload the file to our repository and go to CI/CD -> Editor, where we press the Configure pipeline key. After that, we will be offered a template. We will clean it up and create our own.

I use the docker image from the course, the stages will be init and run. Let’s call the job feed_report_job:

Next, go to the schedule and create a schedule for sending reports. I chose 11 am time in Tbilisi (Georgia).

After that, you can force start by clicking on the play(triangle) button and see the report in your group.

This concludes this article, in the next part we will continue to deal with the automation of reports.

I remind you that all my works you can look at my site: https://alex.gladkikh.org.