Parsing Google Analytics API Python json response into python dataframe
Question: Trying to parse Google Analytics API Python json response into python
dataframe, and then ETL to MS SQL Server using python.

I get a successful output called feed import json, gdata

    
    
    data_query = gdata.analytics.client.DataFeedQuery({
    'ids': 'ga:67981229',
    'dimensions': 'ga:userType,ga:sessionCount,ga:source', ##ga:source,ga:medium
    'metrics': 'ga:pageviews',
    ##'filters': 'ga:pagePath==/my_url_comes_here/',
    ##'segment':'',
    'start-date': '2015-01-01',
    'end-date': '2015-01-03',
    'prettyprint': 'true',
    'output':'json',
    })
    feed = my_client.GetDataFeed(data_query)
    

However, when I try to parse the the data using this code it doesn't work and
I get the below error

> > > response = json.parse(feed) ## I also tried json.load(feed) and
> json.loads(feed)
>>>

>>> data = json.parse(feed) Traceback (most recent call last): File "", line
1, in data = json.parse(feed) AttributeError: 'module' object has no attribute
'parse'

>>>

>>> data = json.loads(feed) Traceback (most recent call last): File "", line
1, in data = json.loads(feed) File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/**init**.py",
line 338, in loads return _default_decoder.decode(s) File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py",
line 365, in decode obj, end = self.raw_decode(s, idx=_w(s, 0).end())
TypeError: expected string or buffer

>>>

>>> data = json.load(feed) Traceback (most recent call last): File "", line 1,
in data = json.load(feed) File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/**init**.py",
line 286, in load return loads(fp.read(), AttributeError: 'DataFeed' object
has no attribute 'read' And I have already imported all of json as seen at the
top, furthermore - my end objective is to ETL this to MS SQL Server - so any
help on effective method to do this with a JSON Python object would help a
LOT! Thanks!


Answer: Instead of parsing the json response manually into a dataframe you could try
using the Pandas library which has built in
[methods](http://pandas.pydata.org/pandas-
docs/version/0.15.2/remote_data.html#remote-data-ga) to query the Google
Analytics API. Once you get your Google Analytics Metrics into a dataframe,
you could insert records into SQL Server using the
[to_sql](http://pandas.pydata.org/pandas-
docs/version/0.15.2/generated/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql)
method.

