kw_tools/SEO/seo_monitor.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pandas as pd

from datetime import datetime
import pymysql
pymysql.install_as_MySQLdb()
import dataset
import requests
import gspread
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import smtplib
from userdata import *
from email.message import EmailMessage

db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/seo?charset=utf8mb4')

data = pd.DataFrame()

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name("choozmointernal-2e314f3d4e12.json", scope)
gc = gspread.authorize(credentials)

def notify_group(msg):
    gid='WekCRfnAirSiSxALiD6gcm0B56EejsoK89zFbIaiZQD'
    headers = {"Authorization": "Bearer " + gid,"Content-Type": "application/x-www-form-urlencoded"}
    r = requests.post("https://notify-api.line.me/api/notify",headers=headers, params={"message": msg})

def send_email(message):
    server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
    server.ehlo()
    server.login(gmail_username, gmail_password)

    emails = {"Jared": "jared@choozmo.com", "Morrison": "morrison@choozmo.com", "Ginia": "ginia@choozmo.com", "Doris": "doris@choozmo.com"}
    
    for name in emails:
        msg = EmailMessage()
        msg['Subject'] = 'SEO亮點字績效通知' + str(datetime.date.today())
        msg['From'] = "service@choozmo.com"
        msg['To'] = emails[name]

        
        msg.set_content(name + "您好，\n\n" + message)
        
        print ('Sending email to ' + msg['To'])
        server.send_message(msg)
        print ('Email sent to ' + msg['To'])
        fname = name+'.txt'
        with open(fname, 'w', encoding="UTF+8") as f:
            f.write(name + "您好，\n\n" + message)

    server.close()

def download(client, date, newest=True):
    """關鍵字清單"""
    lst_kw = []
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/seo?charset=utf8mb4')
    cursor_kw = db.query(f"SELECT term FROM seo.selected_kw where client='{client}'")
    for c in cursor_kw:
        for i in c.values():
            lst_kw.append(i)
    print(len(lst_kw))
    
    """抓取general_log關鍵字"""
    last_check_lst = []
    for i in lst_kw:
        if newest:
            cursor_last_check = db.query(f"select kw,ranking from seo.general_log where kw='{i}' order by dt desc limit 1")
        else:
            cursor_last_check = db.query(f"select kw,ranking from seo.general_log where kw='{i}' order by dt limit 1")
        group = []
        n = 0
        for c in cursor_last_check:
            for j in c.values():
                if 'site' in str(j):
                    n+=1
                if n==1 and j==1:
                    group.append(101)
                else:
                    group.append(j)
            n = 0
        last_check_lst.append(group)
    # df2 = pd.DataFrame(not_match_kw_lst,columns=['關鍵字組','搜尋排名'])
    df3 = pd.DataFrame(last_check_lst, columns=['關鍵字組', '搜尋排名'])
    # df_all = pd.concat([df, df3])
    df3.to_csv(f"./排名CSV/{client}當日排名{date}.csv", index=False, encoding='utf_8_sig')
    db.close()


def combine_df(client, date1, date2):
    df = pd.read_csv(f'./排名CSV/{client}當日排名{date1}.csv')
    df2 = pd.read_csv(f'./排名CSV/{client}當日排名{date2}.csv')
    df_result = pd.merge(df, df2, on='關鍵字組', how='outer').fillna(0)
    date1 = arrange_date(date1)
    date2 = arrange_date(date2)
    df_result.columns = ['kw', 'starting_rank', 'current_rank']
        
    p = df_result['starting_rank'].tolist()
    l = df_result['current_rank'].tolist()

    c = [u"\u2197 "+str(abs(int(i)-int(j))) if int(i)-int(j)>0 \
        else u"\u2198 "+str(abs(int(i)-int(j))) if int(i)-int(j)<0 \
        else u"\u2192 "+str(abs(int(i)-int(j))) for (i, j) in zip(p, l)]

    df_result['client'] = client
    df_result['change'] = c
    df_result['kw'] = df_result['kw'].str.split('site',0).str[0]
    df_result.to_csv(f"./排名CSV/combine/{client}排名變化.csv", index=False, encoding='utf_8_sig')
    return df_result, date1, date2

def arrange_date(date):
    if date[0] == 0 :
        date = date[1]+'/'+date[2:]
    else:
        date = date[:2]+'/'+date[2:]
    return date

def checkrank(data, targetcust, targetkw):
    '''
    msg = "以下是排名下降的關鍵字：\n"
    msg2 = "以下是目前排名於百名外的關鍵字：\n"
    '''
    msg = "以下是亮點字的績效：\n"
    flag1=0
    flag2=0
    for row in data.index:
            #print(row)
            delta = int(data['starting_rank'][row])-int(data['current_rank'][row])

            for t in targetkw:
                try:
                    w = data['kw'][row]
                    if ((t in w) and data['client'][row] in targetcust):
                        msg += "[" + data['client'][row] + "] " + data['kw'][row] + ": " + str(int(data['starting_rank'][row])) + " -> " + str(int(data['current_rank'][row])) + " (" + str(int(delta)) + ")\n"
                except:
                    pass

            '''
            if (delta < 0):
                msg += "[" + data['client'][row] + "] " + data['kw'][row] + ": " + str(int(data['starting_rank'][row])) + " -> " + str(int(data['current_rank'][row])) + " (" + str(int(delta)) + ")\n"
                flag1=1
            if (int(data['current_rank'][row]) == 101):  #out of range...
                msg2 += "[" + data['client'][row] + "] " + data['kw'][row] + "\n"
                flag2=1
            '''
    print(msg)
    #print(msg2)
    return msg
    #return msg, msg2, flag1, flag2

if __name__ == "__main__":
    date1 = '0707'
    date2 = '0717'
    lst = ['清原', '神助物流', '班尼斯', '有夠讚', '仁本', '火柴星人', '呷茶', 'ChoozMo']

    sheet = gc.open('SEO 亮點字')
    sheet_instance = sheet.get_worksheet(0)
    records_data = sheet_instance.get_all_records()
    #print(records_data[0])

    targetkw = []
    for rd in records_data:
        targetkw.append(rd["亮點字"])

    print(targetkw)

    for client in lst:
        date1 = '0707'
        date2 = '0718'
        download(client, date1, newest=False)
        download(client, date2, newest=True)
        res, d1, d2 = combine_df(client, date1, date2)
        data = data.append(res, ignore_index=True)
    data.to_csv(f"./排名CSV/combine/__排名變化.csv", index=False, encoding='utf_8_sig')

    
    spread = Spread("kw_ranking_tracker",creds=credentials)
    spread.df_to_sheet(data, index=False, sheet="main", start="A1", replace=False)
    time_stamp = datetime.now()
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
    
    spread.sheet.update('G1', '最後更新:' + time_stamp)
    
    #msg, msg2, flag1, flag2=checkrank(data, lst, targetkw)
    msg = checkrank(data, lst, targetkw)

    msg += "\n完整資料請至https://cmm.ai/seo1"
    #msg2 += "\n完整資料請至https://cmm.ai/seo1"

    msg01 = [msg[i:i+1000] for i in range(0, len(msg), 1000)]
    for m1 in msg01:
        notify_group(m1)
        print(m1)

    send_email(msg)
    '''
    if flag1 == 1:
        msg01 = [msg[i:i+1000] for i in range(0, len(msg), 1000)]
        for m1 in msg01:
            notify_group(m1)
            print(m1)
    
    if flag2 == 1:
        msg02 = [msg2[i:i+1000] for i in range(0, len(msg2), 1000)]
        for m2 in msg02:
            notify_group(m2)
            print(m2)
    '''