report_automation_part_2.py

### SERP Engagement ###
def serp_engagement():
    account = searchconsole.authenticate(client_config='C:/Users/My.Computer/search_console_credentials.json', 
                                         credentials='C:/Users/My.Computer/search_console_serialized_creds.json')
    webproperty = account['https://www.clientsite.com/']    
    frames = []
    report = webproperty.query.range(search_console_date, days=day).dimension('query').get()
    sc_data = []
    for kw_row in report.rows:
        keyword = kw_row[0]
        click = int(kw_row[1])
        impressions = int(kw_row[2])
        ctr = kw_row[3]
        position_avg = kw_row[4]
        sc_data.append((keyword, click, impressions, ctr, position_avg))
    
    sc_report_data = pd.DataFrame(sc_data, columns=['Keyword', 'Clicks', 'Impressions', 'CTR', 'Position Average'])
    sc_report_data['Month'] = date_main
    engagement_pivot = sc_report_data.pivot_table(index=['Month'], values=['Clicks', 'Impressions'], aggfunc='sum')
    engagement_pivot['CTR'] = (engagement_pivot['Clicks']/engagement_pivot['Impressions'])*100
    return engagement_pivot
    
serp_results = serp_engagement()