Media performance

1-- core.daisycon.media_performance
2-- Affiliate results per media, Daisycon's statistics grouped by media: clicks,
3-- transactions by status, commission, revenue, and the rates affiliate managers
4-- steer on (conversion rate, approval rate, cost of sale, commission per click).
5--
6-- Grain: advertiser_id x media_id, totalled over the sync window. Currency: per row.
7-- Depends on: staging.daisycon.statistics_by_media, .advertiser
8--
9-- No date column: Daisycon returns these statistics as totals for a date range,
10-- and the window is set by the sync (12 months back on a full sync by default).
11-- Sync the stream in full mode so every row covers the same window. Rates are
12-- ratios of sums; re-sum and divide again when you roll rows up.
13--
14-- approval_rate only counts transactions that have been judged: open ones are left
15-- out of both sides. cost_of_sale divides program commission by the revenue your
16-- tracking reported for the same transactions.
17
18SELECT
19    s.advertiser_id,
20    a.advertiser_name,
21    s.media_id,
22    s.media_name,
23    s.media_location,
24    s.media_type_id,
25    s.currency,
26
27    s.clicks,
28    s.unique_clicks,
29    s.transactions,
30    s.transaction_parts,
31    s.open_transactions,
32    s.approved_transactions,
33    s.disapproved_transactions,
34    s.revenue,
35    s.commission,
36    s.program_commission,
37    s.open_program_commission,
38    s.approved_program_commission,
39    s.disapproved_program_commission,
40    s.cpc_clicks,