Preface

This article will be updated on a regular basis. Updates may be required in response to updates to DARS or changes to business processes or errors.

Ensure you are familiar with the Data Protection laws before adding data to records in DARS and when exporting data from the system. Refer to DARSWiki FurtherHelp for further information and relevant links.

Please think twice before printing this article. If a printed copy is necessary, ensure it is printed double-sided and always recycle old versions.

Introduction

This article documents the DARS part of the process the UODO: Campaign Relations team will follow when extracting donors to be listed in the following publications:

The Encaenia Brochure

The Campaign Report

A description of the Queries and Export Definitions used in the process is provided, in addition to the steps to be followed when extracting the donor listing data.

As at August 2015, the criteria for selection of donors to be listed in each publication are as follows:

Encaenia Brochure Donor Listing

Campaign Report Donor Listing

Within the chosen date range:

Donors with cumulative giving New Funds Raised total greater than or equal to £250,000

OR

Donors who have made a pledge payment greater than or equal to £1m

Within the chosen date range:

Donors with cumulative giving New Funds Raised total greater than or equal to £25,000

OR

Donors who have made a pledge payment greater than or equal to £1m

Queries

On the Queries page, enter ‘PROC%RLR%Encaenia’ into the Name field and click on the Apply button. This loads all of the available queries matching these criteria. The following list of queries will be displayed:

Obsolete queries

The two queries listed below had originally been composed in a ‘Constituent’ source view. The process of extracting the donor listing data requires that a ‘Revenue’ source view Export Definition is used. When extracting data from DARS using an export definition, the source view of the query-selection must match the source view of the export definition. As such, the below two queries were rendered unusable/obsolete and were subsequently re-built in a ‘Revenue’ source view.

PROC-REPO-RLR-Encaenia OUTPUT (Part 1) - New Funds Raised

PROC-REPO-RLR-Encaenia OUTPUT (Part 2) - Pledge payments >£1m

Active queries

The queries/query-selections which should be used in the donor listing data review and extraction process, are listed below. An asterisk (*) indicates those queries which are essential to this process, all other queries are those which may be useful to run when reviewing the donor listing data.

Donor Listing Data Extraction Process

Step 1 – Editing/Refreshing the Queries for extracting the donors

Each of the essential (*) queries will need to be updated before they are used to extract donor listing data. These queries and the updates required are detailed below.

PROC-REPO-RLR-Encaenia FILTER

This query selects those donors who:

Have a cumulate giving total, in terms of new funds raised, of greater than or equal to £250,000/£25,000* within the date range OR

Have made a pledge payment of greater than or equal to 1m within the date range

*Note - the amount will depend on the publication the donor listing data is being generated for.

Important:The summed amounts in the outputs will lead to double-counting and should NOT therefore be used to report cumulative giving totals. However, the query will not pull out donors which it should not - see below explanation.

Situations in which double-counting will occur for the summed totals: If a donor has made a new pledge within the date range of £5,000,000, for example, and has also made a payment against that pledge of £1,000,000 – the summed value will be £6,000,000. This is clearly incorrect. However, in all cases, this double-counting will not pull out donors who do not qualify for inclusion in the Encaenia brochure/Campaign Report – if the donor has made a pledge payment of >1m within the date range, then they do qualify for inclusion because the pledge payment amount takes the donor over the qualification threshold for 1.

UPDATE STEPS REQUIRED

Open the query-selection

Check that the ‘Revenue\Date is between…’ filter is correct

On the Set sort and group options tab, set both filter values to ‘greater than or equal to 250,000’ OR ‘greater than or equal to 25,000’

On the fourth tab, Set save options, change the selection type from static to dynamic and Save the query-selection

Re-open the query. On th Set save options tab, change the selection type back to static' and Save the query-selection

Re-open the query, select the Preview results tab to view the results and export the results to Excel, if required

PROC-REPO-RLR-Encaenia Pledge instalments over £1m

This selection is used as an output in the ‘PROC-REPO-RLR…..OUTPUT …’queries below. This selection will display a ‘Yes’ or ‘No’ answer in the output:

Yes – will mean that the donor has made a pledge payment of £1m or greater within the specified date range.

No – will mean that the donor has not made a pledge payment of £1m or greater within the specified date range.

UPDATE STEPS REQUIRED

Open the query-selection

Check that the ‘Revenue\Date is between…’ filter is correct

Save the query-selection

Step 2 – Editing Queries to extract the donor data

PROC-REPO-RLR-Encaenia OUTPUT (All)

This query-selection enables you to output, for the donors identified in ‘PROC-REPO-RLR-Encaenia FILTER’ selection, the following gift data:

New Funds Raised gifts within the specified date range

Pledge payment gifts of £1m or greater made within the date range, for pledges made before the minimum date of the date range

Note:Pledge payments will not be included in the output for pledges made in the date range, only for pledges made before the minimum date of the date range. This will avoid double-counting of gifts.

UPDATE STEPS REQUIRED

Open the query-selection

Check that the ‘Revenue\Date is between…’ filter is correct

Run the query (Preview results)

Export the data to Excel, if desired

Save the query-selection

Step 3 – Exporting the data using the export definition

Go to Administration > Export

Find the Export process ‘PROC-REPO-RLR-Encaenia Export Process’

Check that the selection in the process is PROC-REPO-RLR-Encaenia OUTPUT (All)

Check that the export definition in the process is PROC-REPO-RLR-Encaenia EXPORT

Save

Start the process

Download the exported file as an XLSX file

Open the spreadsheet, make any edits

Any other actions specified by the team

Additional Queries

As previously mentioned, there are several additional queries available for the UODO:Campaign Relations team to use when extracting and reviewing the donor listing data for the Enacenia Brochure or Campaign Report. If any of these queries are used, the date filters should be checked and updated where appropriate.

Recall that the PROC-REPO-RLR-Encaenia OUTPUT query-selection pulls off the following:

This query-selection enables you to output, for the donors identified in ‘PROC-REPO-RLR-Encaenia FILTER’ selection the following:

New Funds Raised gifts within the specified date range

Pledge payment gifts of 1m or greater made within the date range, for pledges made before the minimum date of the date range

There may be occasions when it would be useful to review the results of 1) and 2), in isolation. This is what the queries PROC-REPO-RLR-Encaenia OUTPUT (Part 1) - New Funds Raised v2 and PROC-REPO-RLR-Encaenia OUTPUT (Part 2) - Pledge payments >£1m v2 enable you to do, as detailed below.

PROC-REPO-RLR-Encaenia OUTPUT (Part 1) - New Funds Raised v2

Enables you to view those New Funds Raised gifts, given within the date range, for those donors identified in ‘PROC-REPO-RLR-Encaenia FILTER’.

New Funds Raised gifts within the specified date range

PROC-REPO-RLR-Encaenia OUTPUT (Part 2) - Pledge payments >£1m v2

Enables you to view, for those donors identified in ‘PROC-REPO-RLR-Encaenia FILTER’, the following

Pledge payment gifts of 1m or greater made within the date range, for pledges made before the minimum date of the date range

Note: If you are running these additional queries, be aware that a constituent could appear in the results of both of these queries if they:

Have donated a cumulative total of greater than or equal to £25,000/£250,000, within the date range

AND

Have made a pledge payment of greater than or equal to £1m, within the date range, for a pledge commitment made before the minimum date of the date range.

Confirmation of the current criteria for the Encaenia Brochure donor listing:

Select donors where:

New Funds Raised gifts made in the date range total greater than or equal to £250,000 (this NFR total counts the overall pledge amount and so not pledge payments)

And pledge payments made in the date range of greater than or equal to £1m

Confirmation of the current criteria for the Campaign Report donor listing:

Select donors where:

New Funds Raised gifts made in the date range total greater than or equal to £25,000 (this NFR total counts the overall pledge amount and so not pledge payments)

And pledge payments made in the date range of greater than or equal to £1m

These criteria are unlikely to change. However, it may be useful to review those donors who fall outside of the above set criteria, but who have donated a significant cumulative total to the Central University within the date range.

Two queries have been created to enable the review/consideration of such donors for donor listing, as detailed below.