Connected Devices/Projects/Metrics/Database and Visualization: Difference between revisions

From MozillaWiki
Jump to navigation Jump to search
(data desc)
Line 21: Line 21:


There are currently two primary views available:
There are currently two primary views available:
* AllEvents_7Day
* '''AllEvents_7Day''' - This view contains all events recorded over the past 7 days, including today's intraday events.
* AllEvents_30Day
* '''AllEvents_30Day''' - This view contains all events recorded over the past 30 days, including today's intraday events.


The schema for these tables is identical:  
The schema for these tables is identical:  
Line 35: Line 35:
| EventTimestamp || String || The UTC datetime in which the event was recorded by the ''Metrics library'', if you are using that method to record your events. Because the library may batch up your events before sending them to GA, this can be a more accurate time of the event occurrence. If you are posting your events directly via URL, you can disregard this column and use HitTimestamp instead.
| EventTimestamp || String || The UTC datetime in which the event was recorded by the ''Metrics library'', if you are using that method to record your events. Because the library may batch up your events before sending them to GA, this can be a more accurate time of the event occurrence. If you are posting your events directly via URL, you can disregard this column and use HitTimestamp instead.
|-
|-
| FullVisitorId || String || A unique ID generated for the user, based on (but not equal to) the Client ID parameter in the API. Repeated events from the same client ID will result in the same FullVisitorId value.
| FullVisitorId || String || A unique ID generated for the user, based on (but not equal to) the 'clientId' parameter in the API. Repeated events from the same clientId will result in the same FullVisitorId value.
|-
|-
| VisitId || Integer || Example
| VisitId || Integer || A unique identifier for this visit/session. Sessions seem to time out when no new events have been seen for ~6 minutes.
|-
|-
| VisitNumber || Integer || Example
| VisitNumber || Integer || Incremented with each distinct visit/session for this clientID.
|-
|-
| HitNumber || Integer || Example
| HitNumber || Integer || Incremented for each event recorded within this visit/session.
|-
|-
| AppName || String || Example
| AppName || String || The 'app_name' parameter.
|-
|-
| EventCategory || String || Example
| EventCategory || String || The 'event_category' parameter.
|-
|-
| EventAction || String || Example
| EventAction || String || The 'event_action' parameter.
|-
|-
| EventLabel || String || Example
| EventLabel || String || The 'event_label' parameter.
|-
|-
| EventValue || Integer || Example
| EventValue || Integer || The 'event_value' parameter.
|-
|-
| EventValueFP || String || Example
| EventValueFP || String || The event value when recording a floating point value rather than an integer. This is stored as a string due to limitations in the Analytics data export, but it is generally interpreted correctly by charting tools and can be cast with FLOAT(eventValueFP) if necessary.
|-
|-
| OS || String || Example
| OS || String || The 'os' parameter.
|-
|-
| OSVersion || String || Example
| OSVersion || String || The 'os_version' parameter.
|-
|-
| Device || String || Example
| Device || String || The 'device' parameter.
|-
|-
| Arch || String || Example
| Arch || String || The 'arch' parameter.
|-
|-
| AppPlatform || String || Example
| AppPlatform || String || The 'app_platform' parameter.
|-
|-
| AppBuildID || String || Example
| AppBuildID || String || The 'app_build_id' parameter.
|}
|}


''(For reference, the query used to create these views can be found at: https://github.com/dylano/metrics/tree/master/sql)''
''(For reference, the queries used to create these views can be found at: https://github.com/dylano/metrics/tree/master/sql)''


If these views are insufficient for your project or you'd like to discuss other ways to view or use the data, feel free to contact the CD Metrics team. (#cd-metrics, :doliver)
If these views are insufficient for your project or you'd like to discuss other ways to view or use the data, feel free to contact the CD Metrics team. (#cd-metrics, :doliver)

Revision as of 23:46, 15 June 2016

Summary

We are using a combination of Google Analytics, Google BigQuery database, and Re:Dash to enable teams to view & analyze project data. This page will describe what data we have and how to access it.

BigQuery

BigQuery is Google's cloud-based big data solution. We are using it as a store for all of the data sent to Google Analytics so that teams can view the full detail of all events sent by their application, rather than the aggregated views that are presented in the Google Analytics dashboards.

Data is imported in batches from Analytics several times per day. Note that there may be a delay of up to 8 hours before event data is exported, though it usually seems to arrive within 2 hours.

BigQuery supports a subset of SQL, covering most common clauses and operators. For a full reference, see https://cloud.google.com/bigquery/query-reference.

Table Schema

ga_sessions Tables

The Analytics data is exported into a series of tables, which are named as follows:

  • ga_sessions_20160613 - This table contains all event data recorded on 2016-06-13. A new table is created for each day's data.
  • ga_sessions_intraday_20160614 - This table contains all event data that has been exported for the current day, and batches of data will be appended to it several times during the day.

If you are familiar with Google Analytics data and/or working with multi-dimensional data, you are welcome to query these tables directly. You can find the schema reference here: https://support.google.com/analytics/answer/3437719

Simplified Views

With the Connected Devices metrics solution, we are only using a relatively small subset of the data available in Google Analytics. To simplify the picture, we have created a couple of views to allow easier access to the information. These views are flattened and can be queried with simple SQL.

There are currently two primary views available:

  • AllEvents_7Day - This view contains all events recorded over the past 7 days, including today's intraday events.
  • AllEvents_30Day - This view contains all events recorded over the past 30 days, including today's intraday events.

The schema for these tables is identical:

Column Data Type Description
HitTimestamp String The UTC datetime in which the event was received by Google Analytics. This is a timestamp with the format "YYYY-MM-DD 24:MM:SS", e.g. "2016-06-11 21:00:54".
HitTimestampUsec Integer The UTC time (in microseconds) in which the event was received by Google Analytics, e.g. 1465678854000000.
EventTimestamp String The UTC datetime in which the event was recorded by the Metrics library, if you are using that method to record your events. Because the library may batch up your events before sending them to GA, this can be a more accurate time of the event occurrence. If you are posting your events directly via URL, you can disregard this column and use HitTimestamp instead.
FullVisitorId String A unique ID generated for the user, based on (but not equal to) the 'clientId' parameter in the API. Repeated events from the same clientId will result in the same FullVisitorId value.
VisitId Integer A unique identifier for this visit/session. Sessions seem to time out when no new events have been seen for ~6 minutes.
VisitNumber Integer Incremented with each distinct visit/session for this clientID.
HitNumber Integer Incremented for each event recorded within this visit/session.
AppName String The 'app_name' parameter.
EventCategory String The 'event_category' parameter.
EventAction String The 'event_action' parameter.
EventLabel String The 'event_label' parameter.
EventValue Integer The 'event_value' parameter.
EventValueFP String The event value when recording a floating point value rather than an integer. This is stored as a string due to limitations in the Analytics data export, but it is generally interpreted correctly by charting tools and can be cast with FLOAT(eventValueFP) if necessary.
OS String The 'os' parameter.
OSVersion String The 'os_version' parameter.
Device String The 'device' parameter.
Arch String The 'arch' parameter.
AppPlatform String The 'app_platform' parameter.
AppBuildID String The 'app_build_id' parameter.

(For reference, the queries used to create these views can be found at: https://github.com/dylano/metrics/tree/master/sql)

If these views are insufficient for your project or you'd like to discuss other ways to view or use the data, feel free to contact the CD Metrics team. (#cd-metrics, :doliver)


Data/Vis

  • G analytics
    • file bug to request access
  • re:dash
** soon to be available at sql.telemetry.m.o
  • periscope
** contact doliver for access
  • other tools may be able to access
** contact doliver
Retrieved from "https://wiki.mozilla.org/index.php?title=Connected_Devices/Projects/Metrics/Database_and_Visualization&oldid=1136990"