Google2Pandas で、Google Analytics のデータを pandas Dataframe に変換する

!python3 -m pip install Google2Pandas

    Successfully built Google2Pandas
    [31mmezzanine 4.2.3 has requirement django<1.11,>=1.8, but you'll have django 1.11.11 which is incompatible.[0m
    [31mdf2gspread 0.2.5 has requirement google-api-python-client==1.6.2, but you'll have google-api-python-client 1.6.4 which is incompatible.[0m
    [31mpywebpush 1.5.0 has requirement requests>=2.13.0, but you'll have requests 2.12.1 which is incompatible.[0m
    Installing collected packages: Google2Pandas
    Successfully installed Google2Pandas-0.1.1

from google2pandas import *
view_id = 'your_view_id'
query = {
    'reportRequests': [{
        'viewId' : view_id,

        'dateRanges': [{
            'startDate' : '8daysAgo',
            'endDate'   : 'today'}],

        'dimensions' : [
            {'name' : 'ga:date'}, 
            {'name' : 'ga:pagePath'},
            {'name' : 'ga:browser'}],

        'metrics'   : [
            {'expression' : 'ga:pageviews'}],
    }]
}
# Assume we have placed our client_secrets_v4.json file in the current
# working directory.
conn = GoogleAnalyticsQueryV4(secrets='ga_client.json')
df = conn.execute_query(query)
# --------------------------------------------------------
# pageviewsで降順ソート、上位20件を抽出
# --------------------------------------------------
df.sort_values(by=["pageviews"], ascending=[False]).head(20)

from google2pandas import *

view_id = 'your_view_id'
query = {
    'reportRequests': [{
        'viewId' : view_id,
        'dateRanges': [{
            'startDate' : '8daysAgo',
            'endDate'   : 'today'}],

        'dimensions' : [
            {'name' : 'ga:userAgeBracket'}, 
            {'name' : 'ga:userGender'},
            {'name' : 'ga:interestOtherCategory'},
        ],
        'metrics'   : [
            {'expression' : 'ga:percentNewSessions'}],
    }]
}
# Assume we have placed our client_secrets_v4.json file in the current
# working directory.

conn = GoogleAnalyticsQueryV4(secrets='ga_client.json')
df = conn.execute_query(query)
# --------------------------------------------------------
# pageviewsで降順ソート、上位20件を抽出
# --------------------------------------------------
df.sort_values(by=["percentNewSessions"], ascending=[True]).head(20)

from google2pandas import *

view_id = 'your_view_id'
query = {
    'reportRequests': [{
        'viewId' : view_id,

        'dateRanges': [{
            'startDate' : '8daysAgo',
            'endDate'   : 'today'}],

        'dimensions' : [
            {'name' : 'ga:date'}, 
            {'name' : 'ga:pagePath'},
            {'name' : 'ga:browser'}],

        'metrics'   : [
            {'expression' : 'ga:pageviews'}],
    }]
}
# Assume we have placed our client_secrets_v4.json file in the current
# working directory.

conn = GoogleAnalyticsQueryV4(secrets='ga_client.json')
df = conn.execute_query(query)

import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成する
df.to_sql("ga_report",  conn, index=False, if_exists="replace")
# コネクションを閉じる
conn.close()

# 結果の確認
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'select * from ga_report'
for row in c.execute(sql):
    print(row)
conn.close()

('Chrome', '20180507', '/', '2')
('Firefox', '20180507', '/', '1')
('Internet Explorer', '20180507', '/', '2')
('Chrome', '20180507', '/?page=1', '2')
('Firefox', '20180507', '/about/', '1')
('Safari', '20180507', '/amp/blog/centos-74-apache-python-連携時につまづいたところ/', '1')
('Chrome', '20180507', '/amp/blog/django-18からsettingspyのtemplatesが非推奨になって警告が出力される/', '1')
('Safari', '20180507', '/amp/blog/django-dumpdata-loaddata-でデータを移行する/', '1')
('Chrome', '20180507', '/amp/blog/Error-Cannot-find-module-core-util-is/', '1')
('Safari', '20180507', '/amp/blog/google-amp-cache-api-を使ってみる/', '1')
('Safari', '20180507', '/amp/blog/google-app-script-の-urlfetchapp-の-例外ハンドリングについて/', '2')
('Safari', '20180507', '/amp/blog/google-apps-script-で電子署名をする/', '2')
('Safari', '20180507', '/amp/blog/google-search-console-の-キーワードを-python-sklearn-randomforestclassifier-でクラス分類してカテゴリ分けする/', '1')
('Safari', '20180507', '/amp/blog/java-easybatchを使ってみる１/', '1')