With SkyGlue, you can export Google Analytics user data, including individual visitor data, to databases, Big Query, or text files on a daily basis. The data includes, but not limited to the following:
||registered user IDs mapped to anonymous user IDs
||individual visitor information, such as the browser type, OS and device type, etc.
||information on each visit session, such as key word, visit date and referral path, etc.
||detailed pageview information for each visitor, such as page path, average time on page and date, etc.
||detailed event information for each visitor, such as event category, event action and event label, etc.
||detailed transaction data, such as transaction ID, transaction amount, etc.
SkyGlue can provide customized data ( e.g. custom dimensions) in each table that fits your needs.
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 and campaign efforts to produce the greatest return.
Database Schema and relationships
Following are the relationships of the tables:
- 1 UserID may have 1 or more Visitor ID (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.
Google BigQuery data schema
SkyGlue can also export the GA raw data to BigQuery (It can be combined with databases export). In BigQuery, it will be one big flat table, instead of different relational tables in the databases. This is because BigQuery doesn’t 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. Following are part of the fields in BigQuery export.