Accounts backend

Views for accessing Mozilla Accounts backend derived data

accounts

Accounts table from production FxA database

accounts_backend.accounts

An authorized view on top of the accounts_backend_external.accounts_v1 table that only includes non-sensitive fields. Some fields in this table are converted to a more user-friendly, BigQuery-native format: - uid is converted from bytes to a hex string - boolean integer columns are converted to BOOL - timestamp columns are converted to TIMESTAMP

See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa

Schema
Column	Description	Type	Nullable
uid	Account ID in hexadecimal format. FxA stores this as bytes, for purposes of logging or integration with other systems we convert it to a hex string.	String	Yes
emailVerified		Boolean	Yes
verifierVersion		Integer	Yes
verifierSetAt		Timestamp	Yes
createdAt		Timestamp	Yes
locale		String	Yes
lockedAt		Timestamp	Yes
profileChangedAt		Timestamp	Yes
keysChangedAt		Timestamp	Yes
ecosystemAnonId		String	Yes
disabledAt		Timestamp	Yes
metricsOptOutAt		Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_external	accounts_v1

Source Directory | View Definition | Metadata File

accounts_events

Historical Pings for accounts-backend/accounts-events

accounts_backend.accounts_events

A historical view of pings sent for the accounts-backend/accounts-events document type.

This view is guaranteed to contain only complete days (per submission_timestamp) and to contain only one row per distinct document_id within a given date.

Clustering fields: normalized_channel, sample_id

Schema
Column	Description	Type	Nullable
additional_properties	A JSON string containing any payload properties not present in the schema	String	Yes
client_info		Record	Yes
document_id	The document ID specified in the URI when the client sent this message	String	Yes
events		Record	No
metadata		Record	Yes
metrics		Record	Yes
normalized_app_name	Set to "Other" if this message contained an unrecognized app name	String	Yes
normalized_channel	Set to "Other" if this message contained an unrecognized channel name	String	Yes
normalized_country_code	An ISO 3166-1 alpha-2 country code	String	Yes
normalized_os	Set to "Other" if this message contained an unrecognized OS name	String	Yes
normalized_os_version		String	Yes
ping_info		Record	Yes
sample_id	Hashed version of client_id (if present) useful for partitioning; ranges from 0 to 99	Integer	Yes
submission_timestamp	Time when the ingestion edge server accepted this message	Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_stable	accounts_events_v1

Source Directory | View Definition | Metadata File

baseline

Historical Pings for accounts-backend/baseline

accounts_backend.baseline

A historical view of pings sent for the accounts-backend/baseline document type.

This view is guaranteed to contain only complete days (per submission_timestamp) and to contain only one row per distinct document_id within a given date.

Clustering fields: normalized_channel, sample_id

Schema
Column	Description	Type	Nullable
additional_properties	A JSON string containing any payload properties not present in the schema	String	Yes
client_info		Record	Yes
document_id	The document ID specified in the URI when the client sent this message	String	Yes
events		Record	No
metadata		Record	Yes
metrics		Record	Yes
normalized_app_name	Set to "Other" if this message contained an unrecognized app name	String	Yes
normalized_channel	Set to "Other" if this message contained an unrecognized channel name	String	Yes
normalized_country_code	An ISO 3166-1 alpha-2 country code	String	Yes
normalized_os	Set to "Other" if this message contained an unrecognized OS name	String	Yes
normalized_os_version		String	Yes
ping_info		Record	Yes
sample_id	Hashed version of client_id (if present) useful for partitioning; ranges from 0 to 99	Integer	Yes
submission_timestamp	Time when the ingestion edge server accepted this message	Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_stable	baseline_v1

Source Directory | View Definition | Metadata File

baseline_clients_daily

Baseline Clients Daily

accounts_backend.baseline_clients_daily

A daily aggregate of baseline pings per client_id.

Clustering fields: normalized_channel, sample_id

baseline_clients_first_seen

Baseline Clients First Seen

accounts_backend.baseline_clients_first_seen

Captures the earliest server date that we observe a particular client in the baseline table. A client may have a first_seen_date that is earlier than the epoch of the baseline table; this is the result of a migration from a legacy data collection system. These clients are therefore not captured in this table.

Clustering fields: sample_id

See also: baseline_clients_daily and baseline_clients_last_seen.

Owners:
- ascholtz@mozilla.com

Schema
Column	Type	Nullable
submission_date	Date	Yes
client_id	String	Yes
sample_id	Integer	Yes
first_run_date	Date	Yes
durations	Integer	Yes
days_seen_session_start_bits	Integer	Yes
days_seen_session_end_bits	Integer	Yes
normalized_channel	String	Yes
normalized_os	String	Yes
normalized_os_version	String	Yes
android_sdk_version	String	Yes
locale	String	Yes
city	String	Yes
country	String	Yes
isp	String	Yes
app_build	String	Yes
app_channel	String	Yes
app_display_version	String	Yes
architecture	String	Yes
device_manufacturer	String	Yes
device_model	String	Yes
telemetry_sdk_build	String	Yes
first_seen_date	Date	Yes
is_new_profile	Boolean	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_derived	baseline_clients_daily_v1

Source Directory | View Definition | Metadata File

baseline_clients_last_seen

Baseline Clients Last Seen

accounts_backend.baseline_clients_last_seen

Captures activity history of each client in 28-day windows for each submission date based on baseline pings.

Clustering fields: normalized_channel, sample_id

deletion_request

Historical Pings for accounts-backend/deletion-request

accounts_backend.deletion_request

A historical view of pings sent for the accounts-backend/deletion-request document type.

This view is guaranteed to contain only complete days (per submission_timestamp) and to contain only one row per distinct document_id within a given date.

Clustering fields: normalized_channel, sample_id

Schema
Column	Description	Type	Nullable
additional_properties	A JSON string containing any payload properties not present in the schema	String	Yes
client_info		Record	Yes
document_id	The document ID specified in the URI when the client sent this message	String	Yes
events		Record	No
metadata		Record	Yes
metrics		Record	Yes
normalized_app_name	Set to "Other" if this message contained an unrecognized app name	String	Yes
normalized_channel	Set to "Other" if this message contained an unrecognized channel name	String	Yes
normalized_country_code	An ISO 3166-1 alpha-2 country code	String	Yes
normalized_os	Set to "Other" if this message contained an unrecognized OS name	String	Yes
normalized_os_version		String	Yes
ping_info		Record	Yes
sample_id	Hashed version of client_id (if present) useful for partitioning; ranges from 0 to 99	Integer	Yes
submission_timestamp	Time when the ingestion edge server accepted this message	Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_stable	deletion_request_v1

Source Directory | View Definition | Metadata File

events

Historical Pings for accounts-backend/events

accounts_backend.events

A historical view of pings sent for the accounts-backend/events document type.

This view is guaranteed to contain only complete days (per submission_timestamp) and to contain only one row per distinct document_id within a given date.

Clustering fields: normalized_channel, sample_id

Schema
Column	Description	Type	Nullable
additional_properties	A JSON string containing any payload properties not present in the schema	String	Yes
client_info		Record	Yes
document_id	The document ID specified in the URI when the client sent this message	String	Yes
events		Record	No
metadata		Record	Yes
metrics		Record	Yes
normalized_app_name	Set to "Other" if this message contained an unrecognized app name	String	Yes
normalized_channel	Set to "Other" if this message contained an unrecognized channel name	String	Yes
normalized_country_code	An ISO 3166-1 alpha-2 country code	String	Yes
normalized_os	Set to "Other" if this message contained an unrecognized OS name	String	Yes
normalized_os_version		String	Yes
ping_info		Record	Yes
sample_id	Hashed version of client_id (if present) useful for partitioning; ranges from 0 to 99	Integer	Yes
submission_timestamp	Time when the ingestion edge server accepted this message	Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_stable	events_v1

Source Directory | View Definition | Metadata File

events_unnested

Events Unnested

accounts_backend.events_unnested

Please provide a description for the query

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend	events

Source Directory | View Definition | Metadata File

metrics

Historical Pings for accounts-backend/metrics

accounts_backend.metrics

A historical view of pings sent for the accounts-backend/metrics document type.

This view is guaranteed to contain only complete days (per submission_timestamp) and to contain only one row per distinct document_id within a given date.

Clustering fields: normalized_channel, sample_id

Schema
Column	Description	Type	Nullable
additional_properties	A JSON string containing any payload properties not present in the schema	String	Yes
client_info		Record	Yes
document_id	The document ID specified in the URI when the client sent this message	String	Yes
events		Record	No
metadata		Record	Yes
metrics		Record	Yes
normalized_app_name	Set to "Other" if this message contained an unrecognized app name	String	Yes
normalized_channel	Set to "Other" if this message contained an unrecognized channel name	String	Yes
normalized_country_code	An ISO 3166-1 alpha-2 country code	String	Yes
normalized_os	Set to "Other" if this message contained an unrecognized OS name	String	Yes
normalized_os_version		String	Yes
ping_info		Record	Yes
sample_id	Hashed version of client_id (if present) useful for partitioning; ranges from 0 to 99	Integer	Yes
submission_timestamp	Time when the ingestion edge server accepted this message	Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_stable	metrics_v1

Source Directory | View Definition | Metadata File

metrics_clients_daily

Metrics Clients Daily

accounts_backend.metrics_clients_daily

Please provide a description for the query

Schema
Column	Type	Nullable
submission_date	Date	Yes
client_id	String	Yes
sample_id	Integer	Yes
normalized_channel	String	Yes
n_metrics_ping	Integer	Yes
days_sent_metrics_ping_bits	Integer	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_derived	metrics_clients_daily_v1

Source Directory | View Definition | Metadata File

nonprod_accounts

Accounts table from FxA database in stage environment

accounts_backend.nonprod_accounts

An authorized view on top of the accounts_backend_external.nonprod_accounts_v1 table that only includes non-sensitive fields. Some fields in this table are converted to a more user-friendly, BigQuery-native format: - uid is converted from bytes to a hex string - boolean integer columns are converted to BOOL - timestamp columns are converted to TIMESTAMP

See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa

Schema
Column	Description	Type	Nullable
uid	Account ID in hexadecimal format. FxA stores this as bytes, for purposes of logging or integration with other systems we convert it to a hex string.	String	Yes
emailVerified		Boolean	Yes
verifierVersion		Integer	Yes
verifierSetAt		Timestamp	Yes
createdAt		Timestamp	Yes
locale		String	Yes
lockedAt		Timestamp	Yes
profileChangedAt		Timestamp	Yes
keysChangedAt		Timestamp	Yes
ecosystemAnonId		String	Yes
disabledAt		Timestamp	Yes
metricsOptOutAt		Timestamp	Yes

Referenced Tables
Project	Dataset	Table
moz-fx-data-shared-prod	accounts_backend_external	nonprod_accounts_v1

Source Directory | View Definition | Metadata File

Column	Type	Nullable
days_since_seen	Integer	Yes
days_since_created_profile	Integer	Yes
days_since_seen_session_start	Integer	Yes
days_since_seen_session_end	Integer	Yes
days_seen_bits	Integer	Yes
days_created_profile_bits	Integer	Yes
submission_date	Date	Yes
client_id	String	Yes
sample_id	Integer	Yes
first_run_date	Date	Yes
durations	Integer	Yes
days_seen_session_start_bits	Integer	Yes
days_seen_session_end_bits	Integer	Yes
normalized_channel	String	Yes
normalized_os	String	Yes
normalized_os_version	String	Yes
android_sdk_version	String	Yes
locale	String	Yes
city	String	Yes
country	String	Yes
isp	String	Yes
app_build	String	Yes
app_channel	String	Yes
app_display_version	String	Yes
architecture	String	Yes
device_manufacturer	String	Yes
device_model	String	Yes
telemetry_sdk_build	String	Yes
first_seen_date	Date	Yes
is_new_profile	Boolean	Yes