Pipe line income Major gifts

PHOTO

Wed Jun 01 2022 12:42:37 GMT+0000 (Coordinated Universal Time)

Saved by @Matt_Stone #globalkpi

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)themonth,
  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)theyear, sum(Transaction_Allocation__c.Amount__c) amount

from Transaction_Allocation__c
 
where 
 (Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','50141')
AND

((Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged')
AND
Opportunity__r.Pledge_Date__c > 2022-03-31)))

Group by  CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.Country_of_Ownership__c,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

Number of new monthly gifts recruited / converted

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)themonth,
  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)theyear, COUNT_DISTINCT(Opportunity__r.Id) The_Count, sum(Opportunity__r.Ask_Amount__c) amount

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')
AND
((
Opportunity__r.RecordTypeId IN ('012w00000006jGy')
 AND
Opportunity__r.Date_Recurring_Donation_Established__c  > 2022-03-31
 
)
OR
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = False))
 
AND
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')
)
 

Group by 
Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),
  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Gross income from Major gifts

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode

from Transaction_Allocation__c

where
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
AND
Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 

AND Transaction_Allocation__c.GL_Code__c IN ('50141','50140','50180','50181','50270')

Group by CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c), Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

Gross income from Legacies

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode

 
from Transaction_Allocation__c
 
where
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
AND
Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Transaction_Allocation__c.GL_Code__c IN ('50010')
 

Group by CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c), Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

Number of new monthly gifts recruited / converted

select 
Opportunity__r.Country_of_Ownership__c, 
CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Vmonth, 
 COUNT_DISTINCT(Opportunity__r.Id) Volume 
 
from Transaction_Allocation__c
 
where 
(
  Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')
 
AND
  
     (Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND                Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
 
AND
   
 Opportunity__r.RecordTypeId IN ('012w00000006jGy','0122X000000ie1A')

AND Opportunity__r.Date_Recurring_Donation_Established__c > 2022-03-31)
 
  
Group by 
Opportunity__r.Country_of_Ownership__c ,
  CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)

GBP Income - International team

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode 

 
from Transaction_Allocation__c
 
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
 
AND Transaction_Allocation__c.GL_Code__c IN ('50030','50020','50050','50060','50200','51020','51040','50110','50120','50130','50240','51030','51060','50170','50171','51050')) 
 
Group by 
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

Fundraising income

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode
 
from Transaction_Allocation__c
 
where
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
AND
Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Transaction_Allocation__c.GL_Code__c IN ('51050','51040','51020','50171','50130','51060')
 
Group by CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c), Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

Average global monthly gift (DD / PP / CC)

select  Opportunity__r.Country_of_Ownership__c,  CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years, 
 sum(Transaction_Allocation__c.Amount__c) amount,
  AVG(Transaction_Allocation__c.Amount__c) averages,
  COUNT_DISTINCT(Opportunity__r.Id) The_Count
  
from Transaction_Allocation__c
 
where 
((Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1)
AND
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')
AND
Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal'))

AND
((
Opportunity__r.RecordTypeId IN ('012w00000006jGy')

 AND
 Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly'
)
OR
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND Opportunity__r.CiWF_Payment_Method__c IN ('Paypal')

))
 


 
 
Group by CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c), Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Opportunity__r.Ask_Date__c)themonth,
  CALENDAR_YEAR(Opportunity__r.Ask_Date__c)theyear, COUNT_DISTINCT(Opportunity__r.Id) The_CountThe_Count, sum(Opportunity__r.Ask_Amount__c) amount
from Transaction_Allocation__c
 
where 

Transaction_Allocation__c.Stage__c IN ('Ask Made','Pledged','Posted','Closed Lost')
  AND
   Opportunity__r.Ask_Date__c >2022-03-31
AND
Opportunity__r.Ask_Date__c< NEXT_N_DAYS:1

AND Transaction_Allocation__c.GL_Code__c IN ('50141','50140','50180','50181','50270')
 
Group by  CALENDAR_MONTH(Opportunity__r.Ask_Date__c),Opportunity__r.Country_of_Ownership__c,CALENDAR_YEAR(Opportunity__r.Ask_Date__c)

Pipe line income Major gifts

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)themonth,
  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)theyear, sum(Transaction_Allocation__c.Amount__c) amount

from Transaction_Allocation__c
 
where 
 (Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','50141')
AND

((Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged')
AND
Opportunity__r.Pledge_Date__c > 2022-03-31)))

Group by  CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.Country_of_Ownership__c,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Browse more snippets >>