SELECT
donatewiki.utm_source,
SUM(donatewiki.count) AS donatewiki_clicks
FROM pgehres.donatewiki_counts AS donatewiki
where  donatewiki.utm_source in ('sp70704664', 'sp70821157', 'sp70821169', 'sp70845761', 'sp70830390', 'sp70850058')
GROUP BY utm_source;

SELECT
    t.utm_medium,
    CASE
        WHEN LEFT(t.utm_source,10) IN ('sp69973168','sp69973120','sp69973127','sp69973142','sp69973162','sp69973135','sp69973148')
            THEN 'C2021_PwrEmail'
        ELSE replace(t.utm_campaign, ' ', '_')
    END AS utm_campaign,
    LEFT(t.utm_source,10) AS mailing_identifier,
    SUM(c.total_amount) AS donation_amount,
    SUM(IF(c.total_amount > 50, 50, c.total_amount)) AS amount_50,
    MAX(c.total_amount) AS max_donation,
    COUNT(DISTINCT c.id) AS donation_count,
    COUNT(DISTINCT CASE WHEN comm.opt_in = 1 THEN c.contact_id END) AS opt_in,
    COUNT(DISTINCT CASE WHEN comm.opt_in = 0 THEN c.contact_id END) AS opt_out,
    COUNT(DISTINCT CASE WHEN ISNULL(comm.opt_in) THEN c.contact_id END) AS opt_in_null,
    COUNT(DISTINCT CASE WHEN pre.create_date IS NOT NULL THEN pre.id END) AS pre_payment_recurring_signups,
    COUNT(DISTINCT CASE WHEN post.create_date IS NOT NULL THEN post.id END) AS post_payment_recurring_signups
FROM civicrm.civicrm_contribution AS c
LEFT JOIN civicrm.civicrm_value_1_communication_4 AS comm ON c.contact_id = comm.entity_id
LEFT JOIN drupal.contribution_tracking AS t ON c.id = t.contribution_id
LEFT JOIN analytics_recurring_components.pre_payment_recurring AS pre ON c.id = pre.id
LEFT JOIN analytics_recurring_components.post_payment_recurring AS post ON c.id = post.id
WHERE t.utm_medium IN ('email','endowment','MGEventEmail','MGAppeal','MGAnnual','MGFE')
    AND c.financial_type_id <> 14
    AND c.contribution_status_id = 1
    and LEFT(t.utm_source,10) in ('sp70704664', 'sp70821157', 'sp70821169', 'sp70845761', 'sp70830390', 'sp70850058')
GROUP BY t.utm_medium,utm_campaign,LEFT(t.utm_source,10);