[PYTHON] Get a list of GA accounts, properties, and views as vertical data using API

This is the first post of a Qiita article. I think there are some points that cannot be reached, including the first time using the API, but thank you.

-[Overview / What you want to do](#Overview / What you want to do) -[Try this API (for non-engineers)](Try using # try-this-api for non-engineers) -[Development environment / module version](#Development environment module version) -[Preliminary work contents](# Preliminary work contents) -[Implementation code](#Implementation code) -[Future outlook](#Future outlook)

Overview / What you want to do

My company uses Google Analytics (GA) to measure the numbers on the site. There were some employees and I had been using it for more than a few years, but I couldn't handle the operation and maintenance properly. Various accounts and properties have been messed up.

On the GA management screen, it is possible to scroll through the list of accounts and properties. The goal of this time is to make the results available for viewing on spreadsheets.

Try this API (for non-engineers)

By using the API below, you can get the GA account data associated with the logged-in Chrome account in JSON format. Management API> Reference> Account: list

Even if you format the execution result, you will be able to do what you want to do to some extent. Convert JSON file to Excel I want to convert a JSON file to a CSV file

The first method could not be executed due to the version of Excel, and when I actually tried the second method, a problem occurred.

1. The format of the output is not what you intended The structure of GA is Account> Properties> View. The files after csv conversion are output for the number of lines in the account. Also, because the number of properties varies from account to account, personally unintended data was generated.

What I originally wanted was the following vertical data.

2. Is there a limit to the number of accounts that can be acquired? There were 59 accounts at runtime in the company, but only 49 were acquired. I messed with the numerical values of the parameters, but I tried to write the code to get the data, so I have not investigated the cause deeply. If you have any idea, I would appreciate it if you could comment.

Development environment / module version

• python 3.8.2
• google-api-python-client 1.8.0
• build 1.0.2
• pandas 0.24.2
• numpy 1.16.4

Work contents in advance

I am working after adding the issued service account to the GA account I want to acquire. Originally I wanted to get a GA account linked to an in-house administrator account, but I don't know how to deal with it. ..

I had a lot of accounts and it was hard, so I worked with one hand while singing and survived. Lol

Implementation code

Below is the implementation code for the main subject.

Most of the implementation code is quoted from below. First Analytics API: Python Quickstart for Service Accounts

We use API to create df for each account, property and view, and append to empty df. Management API> Reference> Web Properties: list Management API> Reference> View (old profile): list

qiita.python

from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import numpy as np

def get_service(api_name, api_version, scopes, key_file_location):

    credentials = ServiceAccountCredentials.from_json_keyfile_name(
            key_file_location, scopes=scopes)
    service = build(api_name, api_version, credentials=credentials)
    return service

def get_account_propaty_view_info(service):
    accounts = service.management().accounts().list().execute()

    #Store the list of accounts in df
    account_df = pd.DataFrame(columns=['account_name', 'account_id'])

    for account in accounts.get('items', []):  
        account_array = np.array([[account.get('name'), account.get('id')]])
        df_account = pd.DataFrame(data=account_array,columns=account_df.columns)
        account_df = account_df.append(df_account, ignore_index=False)

    #Store a list of properties in df
    property_df = pd.DataFrame(columns=['account_number','account_id', 'property_name', 'property_id'])

    #Get the target property list from the df of the account
    for i in range(len(account_df)):
        properties = service.management().webproperties().list(accountId=account_df['account_id'].iloc[i]).execute()
        for property in properties.get('items', []):
            property_array = np.array([[i, account_df['account_id'].iloc[i], property.get('name'), property.get('id')]])
            df_property = pd.DataFrame(data=property_array,columns=property_df.columns)
            property_df = property_df.append(df_property, ignore_index=False)
        
    #Store the list of views in df
    view_df = pd.DataFrame(columns=['account_id', 'property_id', 'view_name', 'view_id'])

    #Get the target view list from the property df
    for i in range(len(property_df)):
        views  = service.management().profiles().list(accountId=property_df['account_id'].iloc[i],
        webPropertyId=property_df['property_id'].iloc[i] ).execute()
        for view in views.get('items', []):
            view_array = np.array([[property_df['account_id'].iloc[i], property_df['property_id'].iloc[i], view.get('name'), view.get('id')]])
            df_view = pd.DataFrame(data=view_array,columns=view_df.columns)

            view_df = view_df.append(df_view, ignore_index=False)
            
    #Try to combine the list of accounts, the list of properties, and the list of views into one df
    #Account due to processing convenience at the time of joining_If you are concerned about the position of number, please change the order of the columns.
    ga_account_list = pd.merge(pd.merge(account_df, property_df, on='account_id', how='inner'), view_df, on=['account_id', 'property_id'], how='inner')
    ga_account_list.to_csv('Csv file name to create', encoding='utf_8_sig')

def main():
    scope = 'https://www.googleapis.com/auth/analytics.readonly'
    key_file_location = 'JSON key file for the issued service account'

    service = get_service(
            api_name='analytics',
            api_version='v3',
            scopes=[scope],
            key_file_location=key_file_location)

    get_account_propaty_view_info(service)

if __name__ == '__main__':
    main()

Current output example The account ID is not a neat number, so I have an account_number instead.

Future outlook

First of all, we plan to use this result to sort out unnecessary accounts and properties.

Based on that, I would like to create a group for each department in the company. Currently, GA authority is granted to individuals, but it is possible to grant authority for each group.

Mr. A, who is newly joining the company, is in the XX department, so the image is that he should raise the authority of □□. I want to aim for a future that is automated with the touch of a button.

I hope it will be helpful for those who have similar problems.