Easily understand your organization's sustainers with byt_recurring_donation_summaries

WITH recurring_gift_summaries AS (
select
("Donation"->'Donor'->>'ConsId')::bigint as constituent_id,
("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint as original_transaction_id,
MAX(("Donation"->'Payment'->>'PaymentDate')::timestamp) as last_payment_date,
MAX(("Donation"->'RecurringPayment'->>'NumberOfPayments')::bigint) AS number_of_payments
FROM donations
WHERE
UPPER("Donation"->'RecurringPayment'->>'Type') = 'SUSTAINING'
GROUP BY
("Donation"->'Donor'->>'ConsId')::bigint,
("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint
order by ("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint
)

select
rgs.*,
c."Constituent"->'ConsName'->>'FirstName' as first_name,
c."Constituent"->'ConsName'->>'LastName' as last_name,
c."Constituent"->>'PrimaryEmail' as primary_email,
(c."Constituent"->>'AcceptsEmail')::boolean as accepts_email,
c."Constituent"->'HomeAddress'->>'Street1' as home_address_street1,
c."Constituent"->'HomeAddress'->>'Street2' as home_address_street2,
c."Constituent"->'HomeAddress'->>'City' as home_address_city,
c."Constituent"->'HomeAddress'->>'State' as home_address_state,
c."Constituent"->'HomeAddress'->>'Zip' as home_address_zip
from constituents c
join recurring_gift_summaries rgs on (c."Constituent"->>'ConsId')::bigint = rgs.constituent_id

Quickly report on fundraising objectives or finely segment audiences for retargeting with byt_donor_summaries

Note: This view requires view byt_recurring_donation_summaries to be present.

Note: This view performs numerous calculations. You should consider creating this view as a materialized view to boost performance.

WITH simple_donations AS (
SELECT
(donations."Donation"->'Payment'->>'Amount')::numeric AS donation_amount,
(donations."Donation"->'Donor'->>'ConsId')::bigint AS constituent_id,
(donations."Donation"->'Payment'->>'CreationDate')::timestamp without time zone AS donation_date
FROM donations
), simple_constituents AS (
SELECT
constituents.dmi_id,
constituents.dmi_status,
constituents.dmi_deleted,
constituents."ConsId"::bigint AS consid,
constituents."Constituent"->>'PrimaryEmail' AS email,
constituents."Constituent"->'ConsName'->>'LastName' AS lastname,
constituents."Constituent"->'ConsName'->>'FirstName' AS firstname,
constituents."Constituent"->'HomeAddress'->>'Zip' AS zipcode,
constituents."Constituent"->'HomeAddress'->>'City' AS city,
constituents."Constituent"->'HomeAddress'->>'State' AS state,
constituents."Constituent"->'HomeAddress'->>'Street1' AS street1,
constituents."Constituent"->'HomeAddress'->>'Street2' AS street2,
constituents."Constituent"->>'AcceptsEmail' AS acceptsemail,
constituents."Constituent"->>'EmailStatus' AS emailstatus
FROM constituents
), donor_giving_summaries AS (
SELECT
SUM((donations."Donation"->'Payment'->>'Amount')::numeric) AS total_amount,
MIN((donations."Donation"->'Payment'->>'PaymentDate')::date) AS first_gift_date,
MAX((donations."Donation"->'Payment'->>'PaymentDate')::date) AS last_gift_date,
(donations."Donation"->'Donor'->>'ConsId')::bigint AS constituent_id,
MAX((donations."Donation"->'Payment'->>'Amount')::numeric) AS largest_amount
FROM donations
GROUP BY
(donations."Donation"->'Donor'->>'ConsId')::bigint
), donor_consecutive_giving AS (
SELECT t2.constituent_id,
MAX(t2.yr_cnt) AS max_consecutive_years
FROM (
SELECT
t.constituent_id,
t.grp,
COUNT(t.seqnum) AS yr_cnt
FROM (
SELECT
t_1.constituent_id,
t_1.donation_date,
t_1.seqnum,
dense_rank() OVER (
PARTITION BY t_1.constituent_id
ORDER BY (t_1.donation_date + t_1.seqnum) DESC
) AS grp
FROM (
SELECT
t_2.constituent_id,
t_2.donation_date,
dense_rank() OVER (
PARTITION BY t_2.constituent_id
ORDER BY t_2.donation_date DESC
) AS seqnum
FROM (
SELECT
simple_donations.constituent_id,
(date_part('year'::text, simple_donations.donation_date))::bigint AS donation_date
FROM simple_donations
GROUP BY
simple_donations.constituent_id,
(date_part('year'::text, simple_donations.donation_date))
) t_2
) t_1
) t
GROUP BY
t.constituent_id,
t.grp
) t2
GROUP BY
t2.constituent_id
), donors_year_before_last AS (
SELECT
simple_donations.constituent_id
FROM simple_donations
WHERE
(date_part('year'::text, simple_donations.donation_date) = (((date_part('year'::text, CURRENT_DATE))::bigint - 2))::double precision)
GROUP BY
simple_donations.constituent_id
), donors_last_year AS (
SELECT
simple_donations.constituent_id
FROM simple_donations
WHERE
(date_part('year'::text, simple_donations.donation_date) = (((date_part('year'::text, CURRENT_DATE))::bigint - 1))::double precision)
GROUP BY
simple_donations.constituent_id
), donors_this_year AS (
SELECT
simple_donations.constituent_id
FROM simple_donations simple_donations
WHERE
(date_part('year'::text, simple_donations.donation_date) = date_part('year'::text, CURRENT_DATE))
GROUP BY
simple_donations.constituent_id
)
SELECT dgs.total_amount, dgs.first_gift_date, dgs.last_gift_date, dgs.constituent_id, dgs.largest_amount, sc.email, sc.acceptsemail, sc.emailstatus, sc.firstname, sc.lastname, sc.city, sc.state, sc.zipcode, sc.street1, sc.street2, dcg.max_consecutive_years AS consecutive_years, CASE WHEN (dybl.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_year_before_last, CASE WHEN (dly.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_last_year, CASE WHEN (dty.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_this_year, case WHEN (rec.constituent_id IS NOT NULL) THEN true ELSE false END AS sustainer, rec.last_recurring_gift_dateFROM donor_giving_summaries dgsJOIN simple_constituents sc ON dgs.constituent_id = sc.considJOIN donor_consecutive_giving dcg ON dcg.constituent_id = dgs.constituent_idLEFT JOIN donors_year_before_last dybl ON dgs.constituent_id = dybl.constituent_idLEFT JOIN donors_last_year dly ON dgs.constituent_id = dly.constituent_idLEFT JOIN donors_this_year dty ON dgs.constituent_id = dty.constituent_idLEFT JOIN ( SELECT constituent_id, MAX(last_payment_date) as last_recurring_gift_date FROM byt_recurring_donation_summaries GROUP BY constituent_id) AS rec ON dgs.constituent_id = rec.constituent_id
Did this answer your question?