Zmey56

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import telegram
import pandahouse
from datetime import date
import io
import sys
import os

Zmey56

send_telegram_report(chat_id)

Zmey56

plt_obj = get_plot(data_feed, data_msg, data_dau_all, data_new_users)
bot.sendMessage(chat_id=chat_id, text=report)
for pl in plt_obj:
    bot.sendPhoto(chat_id=chat_id, photo=pl)

Zmey56

image: cr.yandex/crp742p3qacifd2hcon2/practice-da:latest

stages:
  - init
  - run


feed_report_job:
    stage: run
    script:

Zmey56

def get_plot(data_feed, data_msg, data_dau_all, data_new_users):
    data = pd.merge(data_feed, data_msg, on='date')
    data = pd.merge(data, data_dau_all, on='date')
    data = pd.merge(data, data_new_users, on='date')

    data['events_app'] = data['events']+data['msg']

    plt_obj_all = []

    fig, axes = plt.subplots(3, figsize = (10, 14))

Zmey56

report = msg.format(date=today.date(),
                        events=data_msg[data_msg['date'] == today.date()]['msg'].iloc[0]
                        + data_feed[data_feed['date'] == today.date()]['events'].iloc[0],
                        users=data_dau_all[data_dau_all['date'] == today.date()]['users'].iloc[0],
                        to_users_day_ago = (data_dau_all[data_dau_all['date'] == today.date()]['users'].iloc[0]
                                           - data_dau_all[data_dau_all['date'] == day_ago.date()]['users'].iloc[0])
                                           /data_dau_all[data_dau_all['date'] == day_ago.date()]['users'].iloc[0],
                        to_users_week_ago = (data_dau_all[data_dau_all['date'] == today.date()]['users'].iloc[0]
                                           - data_dau_all[data_dau_all['date'] == week_ago.date()]['users'].iloc[0])
                                           /data_dau_all[data_dau_all['date'] == week_ago.date()]['users'].iloc[0],

Zmey56

data_feed['date'] = pd.to_datetime(data_feed['date']).dt.date
data_msq['date'] = pd.to_datetime(data_msq['date']).dt.date
data_dau_all['date'] = pd.to_datetime(data_dau_all['date']).dt.date
data_new_users['date'] = pd.to_datetime(data_new_users['date']).dt.date

data_feed = data_feed.astype({'users_feed': int, 'likes': int, 'views': int, 'events': int})
data_msq = data_msq.astype({'users_msg': int, 'msg': int})
data_dau_all = data_dau_all.astype({'users': int, 'users_ios': int, 'users_and': int, 'users_male': int, 'users_female': int})
data_new_users = data_new_users.astype({'new_user': int, 'new_user_ads': int, 'new_user_organic': int})

Zmey56

data_new_users_q = """
select date, 
        uniqExact(user_id) as new_user, 
        uniqExactIf(user_id, source='ads') as new_user_ads,
        uniqExactIf(user_id, source='organic') as new_user_organic
from(
    select user_id,
            source, 
            min(dt_reg) as date 
    from

Zmey56

query_dau_all_q = """select date, 
                                uniqExact(user_id) as users, 
                                uniqExactIf(user_id, os='iOS') as users_ios, 
                                uniqExactIf(user_id, os='Android') as users_and, 
                                uniqExactIf(user_id, gender=1) as users_male,
                                uniqExactIf(user_id, gender=0) as users_female
                        from
                            (select distinct toDate(time) as date, user_id, os, gender
                            from simulator_20220620.feed_actions
                            where toDate(time) between today() - 8 and today() - 1

Zmey56

query_msg_q = """
                  select 
                      toDate(time) as date
                      ,count(distinct user_id) as users_msg
                      ,count(user_id) as msg
                      , msg / users_msg as mpu
                  from simulator_20220620.message_actions
                  where toDate(time) between today() - 8 and today() - 1
                  group by date
                  order by date