0

Does anyone know how to best import Google Analytics data into a mySQL database (or any other SQL database)? I am asking about both the method of importing the data as well as how to best store it. I do not believe Google Analytics offers any information about what their data schema is. Is there a best practice for what the table structure should be?

I was able to find some information about how to import the data on this thread: how to push GA data to mysql tables. I do have the tool Analytics Canvas which facilitates the process of making API calls and exporting the data, however I do not know how to best organize the data into different tables.

Thanks for the help.

Community
  • 1
  • 1
heyydrien
  • 971
  • 1
  • 11
  • 28
  • You would need to use the Core Reporting API (https://developers.google.com/analytics/devguides/reporting/core/v3/). I have created a 3rd party reporting service [Embeddedanalytics](http://www.embeddedanalytics.com/) similar to Analytics Canvas. I have always thought about offering ETL (Extract, Transform, Load) services to MySql, Access, And MS Sql Server. If you would like reach out to us with your needs. Perhaps we could help you out. – M Schenkel Aug 25 '15 at 19:20

2 Answers2

0

I don't think GA offers an API for this purpose but I am pretty sure you can build one yourself. For example, if you are using PHP, the global variable $_SERVER provides you with everything you need to build a service that simulates what google does with GA. If you are using Wordpress you may be able to find nice plugins to get information about your visitors. Now looking twice to what you want, if you have a spreadsheet, export the data to a csv file and create a PHP script that will read the file line by line and import it to a SQL table. You may need to create such table(s) and their relationship for this to work. Let me know if you need further assistance.

Abraham
  • 311
  • 2
  • 8
  • Thanks for the tip. Any advice on the database schema? – heyydrien Aug 26 '15 at 13:57
  • For this task I think you should have a single table. You don't want this to be sucking your production server's memory, and processing power. Some of the columns you should not miss are: ID, IP address, user agent, time of access, and others that may be of your interest... Play around with the $_SERVER variable and whatever you need from there make it a column. – Abraham Aug 27 '15 at 12:51
0

I built a powershell script to query the GA Core Reporting API and save the data to a csv file, from which our warehouse software can ingest it.

See my github for the tutorial.

Rochelle Smits
  • 41
  • 4