oc_frakture.sql

select
  left(d.description, 8) launch_date
, substring(substring_index(description, '-', 2), 11) campaign
, d.description
, d.source_code
, d.source_label
, program
, round(sum(COALESCE(e.sent)),0) sent
, round(sum(COALESCE(e.opened)),0) opened
, concat(round(sum(COALESCE(e.opened))/ sum(COALESCE(e.sent))*100,2), "%") open_rate
, sum(COALESCE(e.clicked)) clicked
, concat(round(sum(COALESCE(e.clicked)) / sum(COALESCE(e.sent))*100,2), "%") CTR
, COALESCE(t.revenue_first_gifts,0) + COALESCE(t.revenue_one_time,0) revenue
, (t.one_time_gifts + t.first_gifts) responses
, concat(round((t.one_time_gifts + t.first_gifts) / sum(COALESCE(e.clicked))*100,2), "%")  page_completion_rate
, concat(round((t.one_time_gifts + t.first_gifts) / sum(COALESCE(e.sent))*100,4), "%") response_rate
, t.revenue_one_time
, t.one_time_gifts
, t.revenue_first_gifts
, t.first_gifts
, t.revenue_recurring
, t.recurring_gifts
, COALESCE(t.revenue_first_gifts,0) + COALESCE(t.revenue_one_time,0) + COALESCE(t.revenue_recurring,0) total_revenue
, t.total_gifts
 from ocean_conservancy_email_stats_by_source_code e
left join frakture_ocean_conservancy_source_code_dictionary d on e.source_code = d.source_code
left join ocean_conservancy_revenue_by_source_code t on e.source_code = t.clean_source_code
-- where e.source_code like "18L%G%"
group by source_code
order by d.description desc