#!/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) '''