>

Encaenia Brochure and Campaign Report - Donor Listing Extraction Process

From DARSwiki
Revision as of 13:55, 20 August 2015 by Ouit0224 (Talk | contribs) (PROC-REPO-RLR-Encaenia OUTPUT (Part 2) - Pledge payments >£1m v2)

Jump to: navigation, search

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:

  1. The Encaenia Brochure
  2. 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:

”Screenshot of the queries returned when searching for PROC%RLR%Encaenia.”


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.


*PROC-REPO-RLR-Encaenia OUTPUT (All)
*PROC-REPO-RLR-Encaenia FILTER
*PROC-REPO-RLR-Encaenia Pledge Instalments over £1m


PROC-REPO-RLR-Encaenia OUTPUT (Part 1) - New Funds Raised v2
PROC-REPO-RLR-Encaenia OUTPUT (Part 2) - Pledge payments >£1m v2
PROC-REPO-RLR-Encaenia FILTER ADDITIONAL - Pledge payments <£1m but Total payments >£1m
PROC-REPO-RLR-Encaenia OUTPUT ADDITIONAL - Pledge payments <£1m but Total payments >£1m


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:


  1. 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
  2. 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.


ImportantInfo.jpg 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:


  1. Yes – will mean that the donor has made a pledge payment of £1m or greater within the specified date range.
  2. 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:


  1. New Funds Raised gifts within the specified date range
  2. Pledge payment gifts of £1m or greater made within the date range, for pledges made before the minimum date of the date range


UsefulInfo.jpg 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.

”Screenshot of the query PROC-REPO-RLR-Encaenia OUTPUIT (All), showing the query filters.”


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’
      • Ensure that the selection in the process is PROC-REPO-RLR-Encaenia OUTPUT (All)
      • Ensure that the export definition in the process is PROC-REPO-RLR-Encaenia EXPORT
  • Save the process
  • Start the process
  • Download the exported file as an XLSX file
  • Open the spreadsheet, make any edits
  • Any other actions specified by the team to finalise/verify/complete the donor list for the publication


Additional Queries

As mentioned previously, several additional queries are available for the UODO:Campaign Relations team to use when extracting and reviewing the donor listing data for the Enacenia Brochure/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:


  1. New Funds Raised gifts within the specified date range
  2. 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’.


  1. New Funds Raised gifts within the specified date range



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


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


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



UsefulInfo.jpg Note: If you are running these additional queries, be aware that a constituent could appear in the results of both 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.


PROC-REPO-RLR-Encaenia FILTER ADDITIONAL - Pledge payments <£1m but Total payments >£1m

Enables the selection of donors where:


  • A pledge(s) was set up before the minimum date of the date range
  • Where payments against those pledges have been received within the date range but of less than £1m
  • Where the total of those payments is greater than or equal to £1m


Note – Do not use the summed amounts in the outputs of this query to report cumulative giving – this query is merely used to select the donors.



UPDATE STEPS REQUIRED

This selection is used as a filter in the '...OUTPUT...' query-selection below. The below steps ensure that the dates are correct and the selections include new donors and donations since the last time the query was processed.

  • Ensure that the 'Revenue\Date is between..' filter is correct
  • Save as a dynamic selection
  • Re-save as a static selection


PROC-REPO-RLR-Encaenia OUTPUT ADDITIONAL - Pledge payments <£1m but Total payments >£1m

Enables you to output, for the donors identified in the query 'PROC-REPO-RLR-Encaenia FILTER ADDITIONAL - Pledge payments <£1m but Total payments >£1m', the following:


  • Pledge payment gifts of <£1m in the date range, for pledges made before the minimum date of the date range


UsefulInfo.jpg 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
Personal tools
Namespaces
  • Page
  • Discussion
  • Variants
    Actions
    Navigation
    DARS User Support
    DARS Support Centre
    Advancing Oxford
    Tools