How to set up a daily summary of Yandex.Direct work in Telegram?

The article is intended for marketers and business owners who run advertising campaigns in Yandex Direct (or any other advertising system) and want to keep a daily finger on the pulse of how their advertising works. We'll look at how you can automate data collection from an advertising system, process it, and regularly send notifications about the results to Telegram using Python.

Main goal quickly analyze advertising data for the current period, at first glance at the message, understand how things are going in advertising without going into advertising accounts. Evaluating this task, we came to the conclusion that numbers with statistics “for yesterday”, divorced from the context of the month / half a year, do not make any sense. For contextual ad analytics, we used a 7-day, 30-day, and 90-day average approach.

This way we can quickly draw a conclusion about how advertising is working now and how it has worked on average recently. If the indicators over the last 7 days show an increase relative to 30, 90 days, then things are going in the right direction; if there is a decline, then you need to start figuring out what’s going on.

Content

  1. Choosing a method for storing and retrieving source data

  2. Registering a bot to work with Telegram

  3. Connecting to a data store

  4. Generating reports and sending data to Telegram

  5. Automation of sending notifications

Step 1: Selecting a method for storing and retrieving source data

Before sending out anything in notifications, we need to prepare the data. There are two main approaches here: using a ready-made data warehouse or directly connecting to the advertising system API.

The ideal (for my taste) option is with storage

This option is considered more stable and preferable. You configure in advance the daily download of data from advertising systems into a single storage using, for example Python (the example is not ideal but the logic is correct) or language R.

For example, data from platforms such as Yandex Direct, VK Advertising, TG Ads can be automatically downloaded and updated in the database. This allows you to work with data that has already been processed and eliminate the risks associated with loss of connection or incomplete receipt of information.

Advantages:

  1. Stability: The data is already loaded into the database, which eliminates the risks associated with the unpredictability of the API. This is especially important if you need to receive large amounts of data on a regular basis.

  2. Availability: Data is always at hand and can be accessed at any time without the need to make requests to external services.

  3. Historical data: Data can be stored and processed over an extended period, which is useful for analysis and long-term trends.

Flaws:

  • Infrastructure required: The need to organize and maintain a data warehouse, which can be time-consuming and resource-intensive.

  • Initial setup: Requires setting up processes to automatically load data, which can take a significant amount of time.

  • Difficulty updating data: If data is updated frequently, there may be delays in obtaining the most current information.

Direct connection to API

This approach is less reliable, since it directly depends on the stability of the connection to the advertising system API, but requests to it can also be sent using any programming language, including Python or R.

If requests for data are too large, it is possible that the connection will be interrupted and the data may arrive incomplete or not arrive at all. Each time you request data, you connect to the API, receive the necessary information for the selected period and process it before sending it to Telegram.

Advantages:

  1. Latest data: The data is always relevant to the “now”, since queries occur in real time. This is especially useful if you need the latest data for quick decision-making, but it is not very relevant if you want to evaluate advertising in the “generalized analytics” mode for the past period.

  2. No need for storage: You can avoid setting up and maintaining a database by simply connecting to the API as needed. This is probably the key advantage of this approach.

Flaws:

  • Instability: Direct connection to the advertising systems API may result in connection interruptions, especially with large volumes of data, which can lead to data loss or delays.

  • API restrictions: Many advertising systems place limits on the number of requests or the amount of data that can be retrieved at one time, making it difficult to obtain a complete set of data.

  • Error Handling: It is necessary to implement additional logic to handle failed requests, repetitions and partial data, which increases the complexity of implementation specifically within the framework of building a notification system.

Step 2: Connect to Telegram

Once you have decided on the data source, the next step is to set up integration with Telegram to send notifications.

Creating a bot

To work with Telegram we need a bot. It can be easily created using the official @BotFather bot on Telegram:

  1. Open a conversation with @BotFather and run the command /newbot.

  2. Follow the instructions to create a bot and get a token that will be used in Python to send messages. Received token be sure to copythen we will need it to work with sending messages.

  3. Add the created bot to a chat or channel where notifications will be sent. For this step, it is necessary that the bot be added to a Telegram channel or chat and receive administrator rights there with the rights to send messages.

Rights are added in the channel or chat administration section when going to resource administrators.

Step 3: Connect to the data store

Now that the Telegram bot is configured, you need to connect to the data storage where the results of advertising campaigns are stored. To do this, you can use Python libraries such as pymysql or sqlalchemy.

Linked Libraries

from sqlalchemy import create_engine
import pandas as pd
from datetime import  datetime
import requests

Here we include four necessary packages:

Connecting to the database

db_url = f"mysql+mysqlconnector://{user}:{password}@{host}:{port}/{dbname}?auth_plugin=mysql_native_password"
engine = create_engine(db_url)

This is where you configure and create a connection to the database:

SQL query data for last 7 days

Here we are creating an SQL query to retrieve data for the last 7 days. The request performs the following operations:

SQL queries for last 30 and 90 days

query_last_30_days = query.replace("7 DAY", "30 DAY")
query_last_90_days = query.replace("7 DAY", "90 DAY")

These lines modify the original SQL query to fetch data for the last 30 and 90 days, respectively:

This allows you to reuse the original request, replacing only the time slots.

Executing SQL Queries and Retrieving Data

last_7_days_data = pd.read_sql(query, con=engine)
last_30_days_data = pd.read_sql(query_last_30_days, con=engine)
last_90_days_data = pd.read_sql(query_last_90_days, con=engine)

Here the queries are executed and the results are loaded into a DataFrame using pandas:

Returning results

return last_7_days_data, last_30_days_data, last_90_days_data

As a result, the function returns three DataFrame objects containing data for the last 7, 30 and 90 days. This data can be used to analyze advertising campaign performance metrics – General request code.

Step 4: Generating reports and sending data to Telegram

After receiving summary data from the database, it is important to process it and generate understandable reports. Use the Pandas library to work with data.

Since we had the task of generating average values ​​for 7, 30, 90 days, we will write functions that can access the data collected on Step 3 and form them into average values ​​for all key indicators – Averaging functions.

After we have received the averaged data, we can form a message from it to send to Telegram – Function for generating a message to be sent to the TG

Sending data to Telegram:

def sent_msg(msg):
    TOKEN = f'777********43:AAHOR***********cEj4lbJo'  # Указываем Токен, который получили на Шаге 2 от BotFather
    CHATS_ID = [f'-35*****8']   # Указываем ID чата в который планируем отправлять сообщения ботом

    for CHAT_ID in CHATS_ID:  
      url = f"https://api.telegram.org/bot{TOKEN}/sendMessage?chat_id={CHAT_ID}&text={msg}&parse_mode=HTML"
      response = requests.get(url)

      if response.status_code == 200:
          print(f"Message sent successfully to {CHAT_ID}")
      else:
          print(f"Failed to send message to {CHAT_ID}: {response.text}")

To get CHATS_ID you can use the Telegram bot –@getmyid_bot adding him to your group/chat and he will send you your chat ID in response.

Already at this stage, by executing the request:

message = build_msg(merged_data)
sent_msg(message)

Your message with all the advertising data should go to the chat for which we received CHATS_ID above.

Step 5: Automate notification sending

The last stage is process automation. One way is to set up a task to send reports daily using Task Scheduler on Windows.

Setting up a task in Task Scheduler

  1. Open Task Scheduler.

  2. Create a new task by selecting New Task.

  3. Set the trigger to run at a specified time every day.

  4. In the “Actions” section, specify the launch of the Python script:

  5. Save the task.

Now your script will automatically run every day at the specified time and send reports to Telegram.

Alternative solutions

For Linux users, you can use cron to configure the script to run automatically. For example:

0 9 * * * /usr/bin/python3 /path/to/script.py

This job will run the script daily at 9:00 am.

Conclusion

We looked at how we can automate the process of generating reports on advertising campaigns and sending them to Telegram. Now, with the help of Python and a small script, you can receive up-to-date dynamic data on a daily basis and monitor the performance of your advertising campaigns. Don't miss important changes, respond to fluctuations in time and optimize your advertising strategies to achieve better results.

P.S. The article does not cover in detail the topic of how data is collected and loaded into the database; I plan to talk about this later if the topic is relevant.

Thank you for reading, I wish you successful implementation =)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *