Mobile/Metrics/Redash

This page explains how to use Mozilla's Re:dash query and visualization system.

Overview

Re:dash is a data collaboration and visualization platform. The system can connect to a variety of datasources. Mozilla already connects several datasources (Presto, Crash-DB and Sync) to our instance. Telemetry data (UT and UI) are extracted via Spark, stored as Parquet files and exposed to Presto DB into Re:dash. If you want to query and analyze Mobile event data, make sure you are using "Presto" (the default) when making an SQL query.

Presto is a distributed SQL query engine designed to query large data sets. Browse the documentation to learn more about the SQL syntax in Presto.

Data Tables / Schemas

If you want to explore the exposed data tables in Re:dash, use this query:

show tables

If you want to examine the structure of a specific table, use this query:

describe <table-name>

Mobile telemetry data is split into two tables: android_clients_v1 and android_events_v1.

The android_clients_v1 table holds data about common, mostly stable, information about individual Firefox installs. Each install has a unique clientid UUID.

clientid	varchar		unique identifier
profiledate	timestamp	when the installation was created, based on the profile
submissiondate	timestamp	when the telemetry record was received by the server
creationdate	timestamp	when the telemetry record was generated by the client
appversion	varchar		version of Firefox
osversion	bigint		version of Android SDK
locale		varchar		locale code
defaultsearch	varchar		current default search engine name
device		varchar		device identifier string
arch		varchar		CPU architecture
channel		varchar		channel name (partition)
submission	varchar		submission as a string 'YYYYMMDD' (partition)

Some of these fields can change during the lifetime of a Firefox installation. Some can't. A record is created for each telemetry ping, so it is possible to track the history of a client over time. You can see the default search engine change or the OS get updated, for a given client.

The android_events_v1 table holds event data (UI Telemetry). Each event is a record.

clientid	varchar		unique identifier
submissiondate	timestamp	when the telemetry record was received by the server
ts		bigint		relative timestamp associated with the event in milliseconds
action		varchar		name of the event action
method		varchar		name of the event method
extras		varchar		extra contextual hint associated with the event
sessions	varchar		sessions associated with the event (JSON array)
experiments	varchar		a/b experiments associated with the event (JSON array)
channel		varchar		channel name (partition)
submission	varchar		submission as a string 'YYYYMMDD' (partition)

Understanding Event Data

Firefox collects event data using the UI Telemetry system. The documentation explains some of the details of using the system, but doesn't really cover how it all comes together. The best way to know if a probe exists for an event is to look in the source.

Probes are added, and removed, all the time, so keeping an updated list is harder than you think. Let's cover a few groups of probes to give you an idea of how this all works.

Loading URLs

Any event that involves loading a URL, should use the loadurl.1 event action. Since we can load URLs many different ways, there will be different methods and different extra properties, which provide more context.

action	method	extras	description
loadurl.1	griditem	0	loaded from top sites 0-indexed tile
loadurl.1	listitem	bookmarks	loaded from a list of bookmarks
loadurl.1	listitem	history	loaded from a list of history items
loadurl.1	suggestion	engine.0	loaded from the first search engine suggestion

UI Actions

Any button, menu or context menu event should use the action.1 event type. Most UI action use the widget ID as the event extras property.

action	method	extras	description
action.1	menu	reload	choose reload from main menu
action.1	contextmenu	home_remove	remove an item from a home panel, probably a listitem
action.1	actionbar	select_all	select all text from the text edit actionbar
action.1	button	find_next	find next button

Useful Queries

This guide doesn't try to teach SQL, but assumes you have a basic understanding.

Looking at Clients

Find the profile creation date for clients:

select distinct
  profiledate,
  clientid
from android_clients_v1
where
  profiledate is not null

We use distinct here to force a recordset with one clientid and profiledate per row, without duplicates.

Looking at Events

You want to look at basic UI Telemetry over the last 7 days, like the UI Telemetry dashboard:

select
  submissiondate as date,
  channel,
  action,
  method,
  extras,
  count(*) as count
from android_events_v1
where
  submissiondate > current_date - interval '7' day
group by 1, 2, 3, 4, 5

Same, but only those events that happened during a firstrun session:

select
  submissiondate as date,
  channel,
  action,
  method,
  extras,
  count(*) as count
from android_events_v1
where
  submissiondate > current_date - interval '7' day and sessions like '%firstrun%'
group by 1, 2, 3, 4, 5

Let's breakdown loadurl.1 events into some subgroups over the last 7 days:

select
  submissiondate as date,
  count(action) as allloads,
  sum(case when extras = 'bookmarks' then 1 else 0 end) as bookmarks,
  sum(case when extras = 'reading_list' then 1 else 0 end) as readinglist,
  sum(case when extras = 'top_sites' then 1 else 0 end) as topsites,
  sum(case when extras = 'history' then 1 else 0 end) as history,
  sum(case when extras = 'frecency' then 1 else 0 end) as frecency,
  sum(case when method = 'actionbar' and extras = 'user' then 1 else 0 end) as user_typed,
  count(distinct clientid) as users
from
  android_events_v1
where
  submissiondate > current_date - interval '7' day and action = 'loadurl.1'
group by 1

Resources

Turns out that many other companies use event data and SQL, so we can benefit from what they have already done. Here are some blogs and posts that might help you build some SQL queries.

• Periscope Data has a blog with tons of posts on using SQL and event data for analyzing retention, churn and active users. They also cover some general SQL topics too.
• Mode Analytics covers using SQL on event data, but has a lot of general analysis posts too.
• Segment doesn't cover as much SQL as others, but does have posts on the process of data collection and analysis. Worth taking a look.
• Treasure Data has a FAQ post on techniques for optimizing Presto queries.