Events Daily
- Introduction
- Contents
- Limitations
- Accessing the Data
- Data Reference
- Scheduling
- Schema
- Code Reference
- Background and Caveats
Introduction
org_mozilla_firefox.events_daily
is designed to answer questions about events. These include:
- Funnels
- Event Counts
- User Flows
Contents
events_daily
has one row per-client per-day, much the same as clients_daily
. The table is created in a two-step process:
- An ancillary table,
event_types
, is updated with the new events seen on that day. Each event is mapped to a unique unicode character, and each event property (theextras
fields) are also mapped to a unique unicode character. - For every user, that day's events are mapped to their associated unicode characters (including
event_properties
). The strings are aggregated and comma-separated, giving a single ordered string that represents all of that user's events on that day.
For Fenix, we aggregate the events ping data only. If you're looking for events in other pings, you'll need to query them directly.
Included in this data is a set of dimensional information about the user, also derived from the events ping. The full list of fields is available in the query.
Limitations
This approach makes some queries fast and easy, but has some limits:
- Each product is limited to at most 1 Million unique event types
- Each event property is limited to at most 1 Million values. As a result, some Fenix event properties are not included in this table.
- Queries do not know the amount of time that passed between events, only that they occurred on the same day
Note: This can be alleviated by sessionizing and splitting the events string using a
session_start
event. For Fenix this could beevents.app_opened_all_startup
.
Accessing the Data
While it is possible to build queries that access this events data directly, the Data Platform instead recommends using a set of stored procedures we have available.
These procedures create BigQuery views that hide the complexity of the event representation. The mozfun
library documentation
has information about these procedures and examples of their usage.
Data Reference
Example Queries
This query gives the event-count and client-counts per-event per-day.
SELECT
submission_date,
category,
event,
COUNT(*) AS client_count,
SUM(count) AS event_count
FROM
`moz-fx-data-shared-prod`.org_mozilla_firefox.events_daily
CROSS JOIN
UNNEST(mozfun.event_analysis.extract_event_counts(events))
JOIN
`moz-fx-data-shared-prod`.org_mozilla_firefox.event_types
USING (index)
WHERE
submission_date >= DATE_SUB(current_date, INTERVAL 28 DAY)
GROUP BY
submission_date,
category,
event
Link to a dashboard using this query in STMO.
Scheduling
This dataset is scheduled on Airflow and updated daily.
Schema
As of 2020-10-01, the current version of events_daily
is v1, and has a schema as follows:
root
|- submission_date: date
|- client_id: string
|- events: string
|- android_sdk_version: string
|- app_build: string
|- app_channel: string
|- app_display_version: string
|- architecture: string
|- device_manufacturer: string
|- device_model: string
|- first_run_date: string
|- telemetry_sdk_build: string
|- locale: string
|- city: string
|- country: string
|- subdivision1: string
|- channel: string
|- os: string
|- os_version: string
|- experiments: record
| |- key: string
| |- value: string
Code Reference
The job is defined in bigquery-etl
.
The job for updating event_types
is also defined in bigquery-etl
.
Background and Caveats
See this presentation for background.