hhh_flow_wk_report/gatest.py

import sys
import codecs
import traceback
import requests
import re
import pandas as pd
import random
import urllib
import dataset
import json
import gspread
import datetime
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import os
import threading
from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import dataset


# GA: https://developers.google.com/analytics/devguides/reporting/core/v4/rest/v4/reports/batchGet?apix_params=%7B%22resource%22%3A%7B%7D%7D#%E8%AF%B7%E6%B1%82%E6%AD%A3%E6%96%87

db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
db.query('delete from hhh_weekly_report')           # delete DB content

table = db['hhh_weekly_report']                     # DB table

SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']     # Oauth範圍
KEY_FILE_LOCATION = 'choozmo-ga-beee24b7a4c1.json'
VIEW_ID = '188916214'


def initialize_analyticsreporting():
  """Initializes an Analytics Reporting API V4 service object.

  Returns:
    An authorized Analytics Reporting API V4 service object.
  """
  # GA金鑰
  credentials = ServiceAccountCredentials.from_json_keyfile_name(
      KEY_FILE_LOCATION, SCOPES)

  # Build the service object. 建立GA物件
  analytics = build('analyticsreporting', 'v4', credentials=credentials)

  return analytics


def get_report(analytics,body):
  """Queries the Analytics Reporting API V4.

  Args:
    analytics: An authorized Analytics Reporting API V4 service object.
  Returns:
    The Analytics Reporting API V4 response.
  """
  return analytics.reports().batchGet(      #返回 Google Analytics（分析）数据。
      body={
        'reportRequests':body
        # [
        #{
#          'viewId': VIEW_ID,
#          'dateRanges': [{'startDate': '14daysAgo', 'endDate': 'today'}],
#          'dateRanges': [{'startDate': '2021-05-30', 'endDate': '2021-06-05'}],

#          'metrics': [{'expression': 'ga:users'}],
#          'dimensions': [{'name': 'ga:sourceMedium'}]
#          'dimensions': [{'name': 'ga:date'},{'name': 'ga:sourceMedium'}]

#        }]
      }
  ).execute()


def print_response(response):
  """Parses and prints the Analytics Reporting API V4 response.

  Args:
    response: An Analytics Reporting API V4 response.
  """
  result=[]
  for report in response.get('reports', []):            # 取得報表s
    columnHeader = report.get('columnHeader', {})
    dimensionHeaders = columnHeader.get('dimensions', [])
    metricHeaders = columnHeader.get('metricHeader', {}).get('metricHeaderEntries', [])

    for row in report.get('data', {}).get('rows', []):
      dimensions = row.get('dimensions', [])
      dateRangeValues = row.get('metrics', [])
      ga_dict={}

      for header, dimension in zip(dimensionHeaders, dimensions):
        print(header + ': ', dimension)
        ga_dict[header]=dimension
      
      for i, values in enumerate(dateRangeValues):
        print('Date range:', str(i))
        for metricHeader, value in zip(metricHeaders, values.get('values')):
          ga_dict[metricHeader.get('name')]=value
#          print(metricHeader.get('name') + ':', value)
        result.append(ga_dict)
    return result
#        print(ga_dict)

def main():
  analytics = initialize_analyticsreporting()
#(FB_|facebook|IG_|LINE_|LINEMP_|qsear.ch)

  body=[{ 'viewId': VIEW_ID,                                                # 从中提取数据的 Google Analytics（分析）数据视图 ID。
#  'dateRanges': [{'startDate': '2021-05-31', 'endDate': '2021-06-18'}],    # 请求中的日期范围。请求最多可以有 2 个日期范围。
  'dateRanges': [{'startDate': '2021-05-31', 'endDate': '2021-08-05'}],

#  'filtersExpression': 'ga:sourceMedium=~(FB_|facebook|IG_|LINE_|LINEMP_|qsear.ch)',
  'filtersExpression': 'ga:sourceMedium=~(FB_|facebook|IG_|LINE_|LINEMP_|qsear.ch)',    # 限制针对您的请求所返回数据的维度或指标过滤条件。
   # 请求的指标。请求必须指定至少一个指标。请求总共可有 10 个指标。
  'metrics': [{'expression': 'ga:users'},{'expression': 'ga:newusers'},{'expression': 'ga:sessions'},{'expression': 'ga:pageviews'},{'expression': 'ga:bounceRate'},{'expression': 'ga:pageviewsPerSession'}],
   # 请求的维度。请求总共可有 7 个维度。
  'dimensions': [{'name': 'ga:date'},{'name': 'ga:isoWeek'}]
  }]

  response = get_report(analytics,body)
  ga_dict = print_response(response)
  for elmt in ga_dict:
      elmt['category']='social'
      table.insert(elmt)

  body=[{ 'viewId': VIEW_ID,
#  'dateRanges': [{'startDate': '2021-05-24', 'endDate': '2021-06-10'}],
#  'dateRanges': [{'startDate': '2021-05-31', 'endDate': '2021-06-18'}],
#   'dateRanges': [{'startDate': '2021-05-31', 'endDate': '2021-06-25'}],
    'dateRanges': [{'startDate': '2021-05-31', 'endDate': '2021-08-05'}],

#  'filtersExpression': 'ga:sourceMedium=~(FB_|facebook|IG_|LINE_|LINEMP_|qsear.ch)',
  'filtersExpression': 'ga:sourceMedium!~(FB_|facebook|IG_|LINE_|LINEMP_|qsear.ch)',
  'metrics': [{'expression': 'ga:users'},{'expression': 'ga:newusers'},{'expression': 'ga:sessions'},{'expression': 'ga:pageviews'},{'expression': 'ga:bounceRate'},{'expression': 'ga:pageviewsPerSession'}],
  'dimensions': [{'name': 'ga:date'},{'name': 'ga:isoWeek'}]
  }]

  response = get_report(analytics,body)
  ga_dict=print_response(response)
  for elmt in ga_dict:
      elmt['category']='organic'
      table.insert(elmt)


if __name__ == '__main__':
  main()