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)
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.
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.
account | Property_1 | View_1_1 | View_1_2 | ・ ・ ・ | Property_2 | ・ ・ ・ |
---|---|---|---|---|---|---|
A | Property 1 associated with A | View 1 associated with property 1 | View 2 associated with property 1 | ・ ・ ・ | Property 2 associated with A | ・ ・ ・ |
What I originally wanted was the following vertical data.
account | Property | View |
---|---|---|
A | Property 1 associated with A | View 1 associated with property 1 |
A | Property 1 associated with A | View 2 associated with property 1 |
A | ・ ・ ・ | ・ ・ ・ |
A | A Linked property 2 | View 1 associated with property 2 |
A | ・ ・ ・ | ・ ・ ・ |
B | Property 1 associated with B | View 1 associated with property 1 |
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
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.
account_name | account_id | account_number | property_name | property_id | view_name | view_id |
---|---|---|---|---|---|---|
A | 10 | 0 | property_1 | 100 | view_1 | 1000 |
A | 10 | 0 | property_1 | 100 | view_2 | 1001 |
A | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ |
A | 10 | 0 | property_2 | 101 | view_1 | 1100 |
A | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ | ・ ・ ・ |
B | 20 | 1 | property_1 | 200 | view_1 | 2000 |
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.
Recommended Posts