Downloads and deletions

1-- core.app_store_connect.downloads_and_deletions
2-- Installs and deletions by type, the Installations and Deletions view of App
3-- Analytics: first-time downloads, redownloads, updates, restores and deletions.
4--
5-- Grain: one row per day x app x territory x source type x device.
6-- Depends on: staging.app_store_connect.installs_deletions
7--
8-- Opted-in users only, with rows under five users suppressed by Apple, so totals
9-- sit below the units in sales_and_proceeds. Use this model for mix and trend
10-- (which source, which territory, how many deletions per install), and
11-- sales_and_proceeds for absolute download counts.
12--
13-- INSTALLS, NOT DOWNLOADS. Every column counts install or delete events on a
14-- device. Apple labels each install with the download that caused it: a
15-- first-time download is the first Get or Buy on an Apple ID, a redownload is the
16-- same account installing again. App Store Connect's own download figures come
17-- from a separate Downloads report, counted per account and including pre-orders,
18-- which is not synced. Expect these to run close to, not equal to,
19-- the downloads shown in App Analytics.
20--
21-- Apple documents four install types: first-time download, redownload, manual
22-- update and restore. Anything else Apple sends lands in other_installs, so the
23-- type columns always add up to total_installs.
24
25SELECT
26    date_day,
27    app_apple_id,
28    ANY_VALUE(app_name)                                                         AS app_name,
29    territory,
30    source_type,
31    device,
32
33    SUM(IF(event = 'Install' AND download_type = 'First-time download', event_count, 0)) AS first_time_downloads,
34    SUM(IF(event = 'Install' AND download_type = 'Redownload', event_count, 0))          AS redownloads,
35    SUM(IF(event = 'Install' AND download_type = 'Manual update', event_count, 0))       AS manual_updates,
36    SUM(IF(event = 'Install' AND download_type = 'Restore', event_count, 0))             AS restores,
37    SUM(IF(event = 'Install' AND COALESCE(download_type, '') NOT IN
38           ('First-time download', 'Redownload', 'Manual update', 'Restore'), event_count, 0))
39                                                                                         AS other_installs,
40    SUM(IF(event = 'Install', event_count, 0))                                           AS total_installs,