usoc_mothlystats.sql

/* Monthly First Read Data */
SET @year = 2015;

/* $ this Month, Donations this Month, Average Gift This Month */
CREATE TEMPORARY TABLE {{tmp("monthly_stats")}}
select MONTH(cac.transaction_dt) as "month", SUM(cac.transaction_amt) as "revenue", COUNT(cac.transaction_amt) as "count", AVG(cac.transaction_amt) as "average"
FROM cons_action_contribution cac
WHERE YEAR(cac.transaction_dt)=@year AND cac.contribution_type=1 AND cac.transaction_amt>1 AND cac.transaction_amt<9999
GROUP BY MONTH(cac.transaction_dt);

/* donations YTD, Donors YTD */
/* create new table with monthdate and transaction amount */
CREATE TEMPORARY TABLE {{tmp("ytd_stats_temp")}} 
SELECT cons_id, cac.transaction_dt, cac.transaction_amt
FROM cons_action_contribution cac
WHERE YEAR(cac.transaction_dt)=@year AND cac.contribution_type=1 AND cac.contribution_type=1 AND cac.transaction_amt>1 AND cac.transaction_amt<9999;

CREATE TEMPORARY TABLE {{tmp("ytd_stats")}} 
SELECT MONTH(transaction_dt) as "month", 
      (SELECT COUNT(transaction_dt)
        FROM {{tmp("ytd_stats_temp")}}
        WHERE transaction_dt<=MAX(cac.transaction_dt)) donations_ytd
FROM cons_action_contribution cac
GROUP BY MONTH(transaction_dt);

/* New Signups */
CREATE TEMPORARY TABLE {{tmp("new_signups")}} 
SELECT MONTH(rs.create_dt) as "month", COUNT(rs.stg_signup_id) as "count" 
FROM r_signups rs 
WHERE YEAR(rs.create_dt)=@year
GROUP BY MONTH(rs.create_dt);

/* New Donors (FTA) */
CREATE TEMPORARY TABLE {{tmp("new_donors")}}
SELECT MONTH(scr.charge_dt) as "month", count(distinct cac.cons_id) as "count"
FROM stg_contribution_reporting scr
JOIN cons_action_contribution cac USING(stg_contribution_id)
WHERE YEAR(scr.charge_dt)=@year AND scr.contribution_type=1 AND is_first_time=1 AND cac.transaction_amt>1 AND cac.transaction_amt<9999
GROUP BY MONTH(scr.charge_dt);

/* Subscribed Emails */
{{ use_data_mart() }} CREATE TEMPORARY TABLE datamart
SELECT MONTH(sample_date) as "month", total_cons_count, subscribed_cons_count as "subscribed", subscribed_email_count
FROM subscribed_cons
WHERE client_id={{ client_id() }} AND YEAR(sample_date)=@year
GROUP BY MONTH(sample_date);

/* JOIN THEM ALL TOGETHER */
SELECT ms.revenue, ms.count, ms.average, dm.subscribed, ns.count, nd.count
FROM monthly_stats ms
JOIN ytd_stats ys USING(month)
JOIN datamart dm USING(month)
JOIN new_signups ns USING(month)
JOIN new_donors nd USING(month);