Export Google Analytics Data to a Database, BigQuery or FTP Storage

With SkyGlue, you can export Google Analytics user data, including individual visitor data, to a database, BigQuery or text files on a daily basis. The data includes, but is not limited to the following:

Table Name Contains
User Table registered user IDs mapped to anonymous user IDs
Visitor Table individual visitor information, such as the browser type, OS and device type, etc.
Session Table information on each visit session, such as the key word, visit date and referral path, etc.
Pageview Table detailed pageview information for each visitor, such as the page path, average time on page and date, etc.
Event Table detailed event information for each visitor, such as the event category, event action and event label, etc.
Transaction Table detailed transaction data, such as the transaction ID, transaction amount, etc.

SkyGlue can provide customized data ( e.g. custom dimensions) in each table that fits your need.

With such detailed information on your website visitors, and in such a flexible format, you can analyze visitors and create highly customized reports on your KPIs. You can use the customer data intelligence to direct your personalized marketing campaigns to produce the greatest return.

Database schema and relationships

Below are the relationships of the tables:

  • 1 UserID may have 1 or more Visitor IDs (Visitor Id is the cookie ID for each browser. A registered user may log in using different browsers or devices.)
  • 1 Visitor (browser cookie ID) may have 1 or more Sessions (See here for details on how GA sessions are defined.)
  • 1 Session may have 1 or more Event entries in the Event table.
  • 1 Session may have 1 or more Pageview entries in the Pageview table.
  • 1 Session may have 1 or more Transaction entries in the Transaction table.

skyglue_gadata_erd

Google BigQuery data schema

SkyGlue can also export GA raw data to BigQuery (can be combined with a database export). In BigQuery, the data format is one big flat table, instead of different relational tables in the database. This is because BigQuery does not like join operations. Every pageview and event data will also have session data and visitor data associated with them in each row. Pageview and event data are also combined in the table with a “hitType” attribute indicating if it is a pageview hit or event hit. So, it is a long row with all attributes about a pageview/event. The following are part of the fields in BigQuery export:

Data export to CSV files

SkyGlue can export data to a CSV file on an FTP storage.

Other data export options

Depending on your need, SkyGlue can customize data export to other data formats and destinations.

Avoid or minimize data sampling

Since SkyGlue retrieves and exports GA data on a daily basis, Google Analytics data sampling can be avoided or minimized to the best extend.

  • Avoid data sampling if the total daily sessions is less than 500K.
  • Minimize data sampling if total daily sessions is over 500K.

    Note: The 500K is the GA sampling threshold as described here.

Why you should export raw data in Google Analytics

  • Integrate the web data with other data sources.
  • Build powerful data dashboard and reports for your custom KPIs, spot trends and patterns.
  • Conduct personalized marketing based on user profiles.
  • Segment your customers based on various characteristics. Knowing the differences between customer groups will help you make better strategic decisions regarding product growth and marketing. You can spot new business opportunities and areas for optimization with customer segmentation.
  • Grow your dataset for machine learning. Machine learning models need large datasets to train. Artificial intelligence models are powerful tools for decision makers and they can precisely segment customers, which is harder to do manually with conventional analytical methods.

Use cases for raw data export

There are many use cases for exported raw data. See use cases for using exported raw data. We provide full services to help you set up, maintain or integrate the data that fits your need.

Reach out to us for your data export need.

Contact Us