Gloo Church Analytics Export

This script generates an export of financial data suitable to supply to Gloo for their Church Analytics.

The comment at the top of the script places this report on the Totals by Fund page, where it will appear in the Other Reports drop down list.

The headers for the first columns include the following information about the transaction:

  • Fund

  • Transaction Amount

  • Transaction Date

  • Transaction Type (ACH, Check, Bankcard)

  • First Name

  • Last Name

  • Email

  • Phone Number

  • Address

  • City

  • State

  • Zip

  • Date of Birth

  • Individual ID

  • Transaction ID

  • Family ID

  • Delete Transaction

Create a Custom Transaction Report

Step 1

Create a new SQL Report named GlooChurchAnalyticsExport.

Step 2

Copy the code below and paste it into the new SQL file you created. Save the script.

For more detailed instructions on creating SQL Report click the link below.

See also

How to create a SQL Script

Run the Custom Transaction Report

Step 1

Go to Administration > Contributions > Totals by Fund Report.

Step 2

Select the Date Range you want for the report and click Run.

Step 3

Select Gloo Church Analytics Export from Other Reports. The report opens on-screen. If you want it in Excel just select the Download to Excel button.

Use the following code to Create the SQL Script. See How to create a SQL Script.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
--class=TotalsByFund

SELECT
   f.FundName [Fund],
   c.ContributionAmount [Transaction Amount],
   CAST(c.ContributionDate AS DATE) [Transaction Date],
   (CASE
        WHEN COALESCE(t.PaymentType,'')='C' THEN 'Bank Card'
        WHEN COALESCE(t.PaymentType,'')='B' THEN 'ACH'
        WHEN UPPER(c.CheckNo)='ONLINE' THEN 'Online'
        WHEN COALESCE(c.TranId, 0) > 0 THEN 'Online'
        WHEN ISNUMERIC(c.CheckNo)=1 THEN 'Check'
        ELSE 'Cash' END) [Transaction Type],
    p.FirstName [First Name],
    p.LastName [Last Name],
    p.EmailAddress [Email],
    (CASE
        WHEN COALESCE(fa.HomePhone,'')<>'' THEN fa.HomePhone
        WHEN COALESCE(p.CellPhone,'')<>'' THEN p.CellPhone
        WHEN COALESCE(p.WorkPhone,'')<>'' THEN p.WorkPhone
        ELSE '' END) [Phone Number],
    p.PrimaryAddress [Address],
    p.PrimaryCity [City],
    p.PrimaryState [State],
    p.PrimaryZip [Zip],
    p.BDate [Date of Birth],
    p.PeopleId [Individual id],
    c.ContributionId [Transaction id],
    p.FamilyId [Family id],
    'FALSE' [Delete Transaction]
FROM dbo.ContributionSearch(NULL, NULL, NULL, NULL, @StartDate, @EndDate, @CampusId, NULL, @Online, NULL, @TaxNonTax, NULL, NULL, NULL, @IncludeUnclosedBundles, NULL, NULL, @ActiveTagFilter, NULL, NULL) cs
-- If you use custom fund sets, immediately after @ActiveTagFilter in the parameter list on the line above, replace the next NULL with @fundset
JOIN dbo.Contribution c ON c.ContributionId=cs.ContributionId
LEFT JOIN dbo.People p ON p.PeopleId = c.PeopleId
LEFT JOIN dbo.ContributionFund f ON f.FundId = c.FundId
LEFT JOIN dbo.[Transaction] t ON t.Id = c.TranId
LEFT JOIN dbo.Families fa ON fa.FamilyId=p.FamilyId
ORDER BY f.FundName, c.ContributionDate


Latest Update

03/24/2020

Added NULL to end of parameter list for dbo.ContributionSearch() function on line 31 of script. Also added commented line 32 for instructions on the change necessary to accommodate custom fund sets.