Table of Contents

Analytics - Data Model

About

A periodic process will process the raw data event to load them into a SQL database.

When data is loaded:

Data are broken down into tables and views where:

Example:

Platform

Segment

Google Analytics

Google Analytics works with a one table format exported each day where:

See the full definition at BigQuery Export schema (Column Definition)

Extract of the Example that can be found in the guide 

SELECT
  (SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
  SELECT
    fullVisitorId,
    SUM (totals.transactions) AS total_transactions_per_user
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20170701'
    AND '20170731'
    AND totals.transactions IS NOT NULL
  GROUP BY
    fullVisitorId )

SELECT
  device.browser,
  SUM ( totals.transactions ) AS total_transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
  device.browser
ORDER BY
  total_transactions DESC

Documentation / Reference