Addons Daily

Introduction

The addons_daily table is a small and fast dataset with data on specific add-ons, and the users who have them installed. It contains one row per addon_id and submission_date.

Contents

Many questions about add-ons are of the form: "How many users have add-on A installed?" or "Are users with add-on Z more active than users with add-on Y?" This dataset is aimed at answering these type of questions without having to do cumbersome joins or filters.

This dataset also has detailed search aggregates by each add-on, broken out by our major search engines (google, bing, ddg, amazon, yandex, other), along with total aggregates (total). This allows us to identify strange search patterns for add-ons who change a user's search settings on their behalf, often siphoning away SAP searches and Mozilla revenue (see the second example query below).

Accessing the Data

The addons_daily table is accessible through STMO using the Telemetry (BigQuery) data source.

See STMO#71007 for an example.

Data Reference

Example Queries

DAU, WAU and MAU for uBlock Origin

SELECT
    submission_date,
    dau,
    wau,
    mau
FROM
    `moz-fx-data-shared-prod.telemetry.addons_daily`
WHERE
    submission_date >= DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)
    AND addon_id = 'uBlock0@raymondhill.net'

Add-ons with Highest Organic:SAP search ratio

SELECT
    addon_id,
    ANY_VALUE(name) as name,
    AVG(dau) as avg_dau,
    SAFE_DIVIDE(SUM(organic_searches.total), SUM(sap_searches.total)) as organic_sap_ratio
FROM
    telemetry.addons_daily
WHERE
    submission_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
    AND is_system = false
GROUP BY
    1
HAVING
    avg(dau) > 1000
ORDER BY
    4 DESC

SELECT
    addon_id,
    ANY_VALUE(name) as name,
    AVG(dau) as avg_dau,
    SAFE_DIVIDE(SUM(ad_clicks.total), SUM(sap_searches.total)) as adclick_sap_ratio
FROM
    telemetry.addons_daily
WHERE
    submission_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
    AND is_system = false
    AND addon_id in ('uBlock0@raymondhill.net', --  'Ublock user'
                     '{d10d0bf8-f5b5-c8b4-a8b2-2b9879e08c5d}', -- 'ABP user'
                     'jid1-NIfFY2CA8fy1tg@jetpack', --'Adblock user', 
                     '{73a6fe31-595d-460b-a920-fcc0f8843232}', --'NoScript user', 
                     'firefox@ghostery.com', --'Ghostery user'
                     'adblockultimate@adblockultimate.net', --'AdblockUltimate user'
                     'jid1-MnnxcxisBPnSXQ@jetpack' -- '$PrivacyBadger user'
                     )
GROUP BY
    1
HAVING
    avg(dau) > 1000
ORDER BY
    4 DESC

Scheduling

This dataset is updated daily via the telemetry-airflow infrastructure. The job runs as part of the addons_daily DAG.

Schema

The data is partitioned by submission_date.

As of 2020-04-17, the current version of the addons_daily dataset is v1.

Code Reference

This dataset is generated by bigquery-etl. Refer to this repository for information on how to run or augment the dataset.