Google Analytics API into CSV

Question

I am trying to build an API for my Google Analytics Account to export the data into a CSV. I have the Authentication code working, but I am struggling with now printing the data in the format I would like.

For the time being, I am only pulling dimension country, dimension city, and metric session. (However these will change when I get this working.) Right now, it prints:

Date Range(0)
ga:sessions: 2
ga:country:United States
ga:city:Los Angeles
...

However, I would like to have this in a line:

date Range   sessions    country     city
0            2           USA         Los Angeles
...

What code in Python do I need to use? Below is what I have.

def initialize_analyticsreporting():
  parser = argparse.ArgumentParser(
      formatter_class=argparse.RawDescriptionHelpFormatter,
      parents=[tools.argparser])
  flags = parser.parse_args([])

http = credentials.authorize(httplib2.Http())
service = build('analytics', 'v4', http=http, discoveryServiceUrl=('https://analyticsreporting.googleapis.com/$discovery/rest'))


def get_report(service):
    return service.reports().batchGet(
        body={
            'reportRequests':[
                {
                   "viewId": "ga:52783868",
                   "dimensions": [{
                         "name": "ga:country"},
                        {"name": "ga:city"}],

                    "metrics": [{
                         "expression": "ga:sessions"}],
                     "dateRanges": [{
                         "startDate": "2017-04-10",
                         "endDate": "2017-04-12"}]
                }
            ]
        }
    ).execute()

countries = []
cities = []
val = []

def print_reponse(response):

    for report in response.get('reports', []):
        columnHeader = report.get('columnHeader',{})
        dimensionHeaders=columnHeader.get('columnHeader',[])
        metricHeaders = columnHeader.get('metricHeader',{}).get('metricHeaderEntries',[])
        rows = report.get('data',{}).get('rows',[])

        for row in rows:
            dimensions = row.get('dimensions',[])
            dateRangeValues=row.get('metrics',[])

            for header, dimension in zip(dimensionHeaders,dimensions):
                print(header+':'+dimension)

            for i, values in enumerate(dateRangeValues):           
                for metricHeader, value in zip(metricHeaders, values.get('values')):
                    print(metricHeader.get('name')+':'+value)

def main():
    analytics = initialize_analyticsreporting()
    response = get_report(service)
    print_reponse(response)



if __name__ == '__main__':
    main()

I'd suggest you use this library. One you've put it into a pandas dataframe, you may do any kind of operations, and writing into csv would be something like to_csv. Cheers. — lrnzcig
– lrnzcig, Commented Apr 20, 2017 at 13:57

Jerry · Accepted Answer · 2018-03-20 02:50:31Z

5

As lrnzcig's suggestion, we could parse the data with pandas and then export to csv file.

First, import pandas and json_normalize

import pandas as pd
from pandas.io.json import json_normalize

Use this function to parse data

def parse_data(response):

  reports = response['reports'][0]
  columnHeader = reports['columnHeader']['dimensions']
  metricHeader = reports['columnHeader']['metricHeader']['metricHeaderEntries']

  columns = columnHeader
  for metric in metricHeader:
    columns.append(metric['name'])

  data = json_normalize(reports['data']['rows'])
  data_dimensions = pd.DataFrame(data['dimensions'].tolist())
  data_metrics = pd.DataFrame(data['metrics'].tolist())
  data_metrics = data_metrics.applymap(lambda x: x['values'])
  data_metrics = pd.DataFrame(data_metrics[0].tolist())
  result = pd.concat([data_dimensions, data_metrics], axis=1, ignore_index=True)

  return result

The result will look like ...

      ga:country         ga:city    ga:sessions
0      (not set)       (not set)             64
1      Argentina       (not set)              1
2      Australia        Adelaide              3
3      Australia        Brisbane              9
...

Finally, call function to_csv to save data as csv file

result.to_csv('result.csv')

edited Mar 20, 2018 at 2:50

answered Mar 19, 2018 at 9:52

Jerry

511 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodeWorld Over a year ago

can you suggest something on same for Java. @Jerry

Collectives™ on Stack Overflow

Google Analytics API into CSV

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related