Skip to content

Instantly share code, notes, and snippets.

@awavering
Created March 3, 2015 20:42
Show Gist options
  • Save awavering/7103ae5feef3bcce3a30 to your computer and use it in GitHub Desktop.
Save awavering/7103ae5feef3bcce3a30 to your computer and use it in GitHub Desktop.
/* 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment