Product page conversion

1-- core.app_store_connect.product_page_conversion
2-- The App Store funnel: impressions, product page views, taps on Get, and the
3-- downloads that followed, by where people found the app. App Analytics' Sources
4-- view, with conversion recomputed at any grain.
5--
6-- Grain: one row per day x app x territory x source type x device.
7-- Depends on: staging.app_store_connect.store_engagement,
8--             core.app_store_connect.downloads_and_deletions
9--
10-- APPLE'S CONVERSION RATE USES UNIQUE IMPRESSIONS; THIS ONE USES IMPRESSIONS.
11-- Unique counts are distinct users within one row of Apple's file and cannot be
12-- added up across devices, territories or days, so a unique-based rate can only be
13-- shown at exactly Apple's grain. `downloads_per_impression` divides summed counts
14-- instead. It is lower than the App Store Connect figure, consistently so, and it
15-- stays correct when you roll it up. Compare trends, not the level.
16--
17-- Impressions are counted on event = 'Impression' across all engagement types;
18-- page views on event = 'Page view'. Apple counts impressions and page views
19-- separately: a page view is not also an impression. get_taps is Apple's 'Get'
20-- engagement type, which covers taps on Get, Buy and Pre-order alike.
21--
22-- Downloads here are install events of the first-time and redownload types (see
23-- downloads_and_deletions), not Apple's per-account download count.
24
25WITH engagement AS (
26    SELECT
27        date_day,
28        app_apple_id,
29        ANY_VALUE(app_name)                                                     AS app_name,
30        territory,
31        source_type,
32        device,
33        SUM(IF(event = 'Impression', event_count, 0))                           AS impressions,
34        SUM(IF(event = 'Page view', event_count, 0))                            AS product_page_views,
35        SUM(IF(event = 'Tap' AND engagement_type = 'Get', event_count, 0))      AS get_taps
36    FROM {{staging.app_store_connect.store_engagement}}
37    GROUP BY date_day, app_apple_id, territory, source_type, device
38)
39
40SELECT