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

From MozillaWiki
Jump to navigation Jump to search
(Initial)
 
(→‎Table Schema: schema details in progress)
Line 10: Line 10:


== Table Schema ==
== Table Schema ==
==== ga_sessions Tables ====
The Analytics data is exported into a series of tables, which are named as follows:  
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_20160613''' - This table contains all event data recorded on 2016-06-13. A new table is created for each day's data.  
Line 16: Line 17:
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
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 ====
* custom 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.
** schema
 
** 7day
There are currently two primary views available:
** 30day
* AllEvents_7Day
** can further customize as necessary to help you access your data, e.g. SH-events
* AllEvents_30Day
* github
 
The schema for these tables is identical:
{| class="wikitable"
|-
! Column !! Data Type !! Description
|-
| HitTimestamp || Example || Example
|-
| HitTimestampUsec || Example || Example
|-
| EventTimestamp || Example || Example
|-
| FullVisitorId || Example || Example
|-
| VisitId || Example || Example
|-
| VisitNumber || Example || Example
|-
| HitNumber || Example || Example
|-
| AppName || Example || Example
|-
| EventCategory || Example || Example
|-
| EventAction || Example || Example
|-
| EventLabel || Example || Example
|-
| EventValue || Example || Example
|-
| EventValueFP || Example || Example
|-
| OS || Example || Example
|-
| OSVersion || Example || Example
|-
| Device || Example || Example
|-
| Arch || Example || Example
|-
| AppPlatform || Example || Example
|-
| AppBuildID || Example || Example
|}
 
(For reference, the query 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
Data/Vis

Revision as of 16:18, 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
  • AllEvents_30Day

The schema for these tables is identical:

Column Data Type Description
HitTimestamp Example Example
HitTimestampUsec Example Example
EventTimestamp Example Example
FullVisitorId Example Example
VisitId Example Example
VisitNumber Example Example
HitNumber Example Example
AppName Example Example
EventCategory Example Example
EventAction Example Example
EventLabel Example Example
EventValue Example Example
EventValueFP Example Example
OS Example Example
OSVersion Example Example
Device Example Example
Arch Example Example
AppPlatform Example Example
AppBuildID Example Example

(For reference, the query 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.allizom.org/index.php?title=Connected_Devices/Projects/Metrics/Database_and_Visualization&oldid=1136971"