Store listing conversion

1-- core.google_play.store_listing_conversion
2-- Store listing visitors, acquisitions and conversion rate by traffic source: the
3-- Store performance report in Play Console.
4--
5-- Grain: one row per day x package x traffic source.
6-- Depends on: staging.google_play.store_performance_by_source
7--
8-- Google ships a conversion rate per row; it is not reused here, because a rate
9-- cannot be summed. conversion_rate is recomputed from summed acquisitions and
10-- visitors, so it stays right when rolled up to a week or across sources. Search
11-- terms and UTM tags are summed away in this model; they are in the staging model
12-- if you need them.
13
14SELECT
15    date_day,
16    package_name,
17    traffic_source,
18    SUM(store_listing_visitors)                                         AS store_listing_visitors,
19    SUM(store_listing_acquisitions)                                     AS store_listing_acquisitions,
20    ROUND(SAFE_DIVIDE(SUM(store_listing_acquisitions),
21                      NULLIF(SUM(store_listing_visitors), 0)), 4)       AS conversion_rate
22FROM {{staging.google_play.store_performance_by_source}}
23GROUP BY date_day, package_name, traffic_source