gistfile1.txt

def feed_report(chat=None):
    chat = chat or chat_id
    bot = telegram.Bot(token=my_token)
    msg = """ 📃Feed report for {date}📃
    Events: {events}
    🧑DAU: {users}({to_users_day_ago:+.2%} to day ago, {to_users_week_ago:+.2%} to week ago)
    👍Likes: {likes}({to_likes_day_ago:+.2%} to day ago, {to_likes_week_ago:+.2%} to week ago)
    👀Views: {views}({to_views_day_ago:+.2%} to day ago, {to_views_week_ago:+.2%} to week ago)
    🌈CTR: {ctr}({to_ctr_day_ago:+.2%} to day ago, {to_ctr_week_ago:+.2%} to week ago)
    """

    query = """
            select 
                toDate(time) as date
                ,count(distinct user_id) as DAU
                ,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
            group by date
            order by date
            """
    data = pd.DataFrame(ph.read_clickhouse(query=query, connection=connection))

    today = pd.Timestamp('now') - pd.DateOffset(days=1)
    day_ago = today - pd.DateOffset(days=1)
    week_ago = today - pd.DateOffset(days=7)

    data['date'] = pd.to_datetime(data['date']).dt.date
    data = data.astype({'DAU':int, 'views':int, 'likes':int, 'events':int})

    report = msg.format(date = today.date(),

                        events = data[data['date'] == today.date()]['events'].iloc[0],

                        users =data[data['date'] == today.date()]['DAU'].iloc[0],
                        to_users_day_ago = (data[data['date'] == today.date()]['DAU'].iloc[0]
                                           - data[data['date'] == day_ago.date()]['DAU'].iloc[0])
                                           /data[data['date'] == day_ago.date()]['DAU'].iloc[0],
                        to_users_week_ago = (data[data['date'] == today.date()]['DAU'].iloc[0]
                                           - data[data['date'] == week_ago.date()]['DAU'].iloc[0])
                                           /data[data['date'] == week_ago.date()]['DAU'].iloc[0],

                        likes=data[data['date'] == today.date()]['likes'].iloc[0],
                        to_likes_day_ago=(data[data['date'] == today.date()]['likes'].iloc[0]
                                           - data[data['date'] == day_ago.date()]['likes'].iloc[0])
                                           /data[data['date'] == day_ago.date()]['likes'].iloc[0],
                        to_likes_week_ago=(data[data['date'] == today.date()]['likes'].iloc[0]
                                           - data[data['date'] == week_ago.date()]['likes'].iloc[0])
                                           /data[data['date'] == week_ago.date()]['likes'].iloc[0],

                        views=data[data['date'] == today.date()]['views'].iloc[0],
                        to_views_day_ago=(data[data['date'] == today.date()]['views'].iloc[0]
                                           - data[data['date'] == day_ago.date()]['views'].iloc[0])
                                           /data[data['date'] == day_ago.date()]['views'].iloc[0],
                        to_views_week_ago=(data[data['date'] == today.date()]['views'].iloc[0]
                                           - data[data['date'] == week_ago.date()]['views'].iloc[0])
                                           /data[data['date'] == week_ago.date()]['views'].iloc[0],

                        ctr=data[data['date'] == today.date()]['CTR'].iloc[0],
                        to_ctr_day_ago=(data[data['date'] == today.date()]['CTR'].iloc[0]
                                           - data[data['date'] == day_ago.date()]['CTR'].iloc[0])
                                           /data[data['date'] == day_ago.date()]['CTR'].iloc[0],
                        to_ctr_week_ago=(data[data['date'] == today.date()]['CTR'].iloc[0]
                                           - data[data['date'] == week_ago.date()]['CTR'].iloc[0])
                                           /data[data['date'] == week_ago.date()]['CTR'].iloc[0]
                        )
    bot.sendMessage(chat_id=chat_id, text=report)