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

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

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

Zmey56

query_feed_q = """
                select 
                    toDate(time) as date
                    ,count(distinct user_id) as users_feed
                    ,countIf(action='like') as likes
                    ,countIf(action='view') as views
                    ,countIf(action='like') / countIf(action='view') as CTR
                    ,likes+views as events
                from simulator_20220620.feed_actions
                where toDate(time) between today() - 8 and today() - 1

Zmey56

msg = """ 📃Application report for {date}📃
    Events: {events}
    🧑DAU: {users}({to_users_day_ago:+.2%} to day ago, {to_users_week_ago:+.2%} to week ago)
    🧑DAU by platform: 
        📱IOS: {users_ios}({to_users_ios_day_ago:+.2%} to day ago, {to_users_ios_week_ago:+.2%} to week ago)
        📞Android: {users_and}({to_users_and_day_ago:+.2%} to day ago, {to_users_and_week_ago:+.2%} to week ago)
    👫DAU by gender: 
        🙎Male: {users_male}({to_users_male_day_ago:+.2%} to day ago, {to_users_male_week_ago:+.2%} to week ago)
        👰‍Female: {users_female}({to_users_female_day_ago:+.2%} to day ago, {to_users_female_week_ago:+.2%} to week ago)
    🧑New users: {new_users}({to_new_users_day_ago:+.2%} to day ago, {to_new_users_week_ago:+.2%} to week ago)

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))