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