get_ga_ad_performance/main.py

import pandas as pd
import datetime as dt
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

def process_worksheet(ws, n):
    data = []
    r1 = ws.row_values(7)
    r2 = ws.row_values(8)
    data.append(r1)
    data.append(r2)
    f = pd.DataFrame(data)
    f.columns = f.iloc[0] 
    f = f[1:]

    f.to_excel(c[0] + str(n) + " f.xlsx")
    print(f)
    return f

def notify_group(message):
    gid='WekCRfnAirSiSxALiD6gcm0B56EejsoK89zFbIaiZQD'
    headers = {"Authorization": "Bearer " + gid,"Content-Type": "application/x-www-form-urlencoded"}
    msg = [message[i:i+1000] for i in range(0, len(message), 1000)]
    print(msg)
    for m in msg:
      r = requests.post("https://notify-api.line.me/api/notify",headers=headers, params={"message": m})
      print(m)

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", "Wizer": "wizer@choozmo.com", "Doris": "doris@choozmo.com"}
    
    for name in emails:
        msg = EmailMessage()
        msg['Subject'] = '廣告績效通知' + str(dt.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()

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)

clients = [['仁本', '1BZlXtEPD0OFdRRVYdTkXhHBcds6mhB0ILkB3ZB3EW3M', 'https://docs.google.com/spreadsheets/d/1BZlXtEPD0OFdRRVYdTkXhHBcds6mhB0ILkB3ZB3EW3M'], ['元利樂樺', '1jYPBgv2U0nnxN3WqI8X8tHcUon3GTMmnC8YrLFz1c84', 'https://docs.google.com/spreadsheets/d/1jYPBgv2U0nnxN3WqI8X8tHcUon3GTMmnC8YrLFz1c84']]

fields = ["All conv.", "All conv. rate", "All conv. value", "Conversions", "Conv. rate", "Conv. value", "CTR", "Clicks", "Avg. CPC", "Impr.", "Cost", "Avg. cost"]

msg = "以下是目前廣告績效：\n\n"
for c in clients:
  msg += "[" + c[0] + "]\n詳細資料: " + c[2] + "\n\n"
  msg += "[昨日資料與前日比較: 昨日/前日/變化/變化%]\n"
  sh = gc.open_by_key(c[1])
  file = process_worksheet(sh.worksheet(c[0] + ' 昨日'), 1)
  for f in fields:
    f1 = f
    f2 = f + " (Compare to)"
    f3 = f + " (Change)"
    f4 = f + " (Change %)"
    msg += f + ": " + file[f1].values[0] + ' / ' + file[f2].values[0] + ' / ' + file[f3].values[0] + ' / ' + file[f4].values[0] + "\n"

  msg += "\n[累積資料]\n"
  file = process_worksheet(sh.worksheet(c[0] + ' 累積'), 2)
  for f in fields:
    msg += f + ": " + file[f].values[0] + "\n"
  msg += "\n"

msg = msg.replace("#NUM!", "--")

print(msg)
notify_group(msg)
send_email(msg)