Difference between revisions of "Orgs & People Extract"

From Charitylog Manual
Jump to: navigation, search
Line 1: Line 1:
 
[[File:helpheader_small.png|right]]
 
[[File:helpheader_small.png|right]]
−
''Location in standard build:'' '''''Reports > Data Extraction/Mail Merge'''''
+
__NOTOC__
 +
''Location in standard build:'' '''''[[Reports]] tab > Data Extraction/Mail Merge'''''
  
  
−
[[File:DE_1.png|border]]
+
[[File:cl_DE_1.png|border]]
  
  
Line 11: Line 12:
 
* which types of organisation/person you want information about
 
* which types of organisation/person you want information about
 
* whether to filter these people (for example, only display people who live in a certain district)
 
* whether to filter these people (for example, only display people who live in a certain district)
−
* whether to output the data directly to a spreadsheet, or to a [[Blue Folder Lookup]]
+
* which fields should be displayed on the output (if applicable)
  
 +
Within the Data Extraction feature, you can create extractions that send the relevant people to a [[Mail Merge]].
  
−
__TOC__
+
Click on the menu item to see a list of the Data Extractions currently set up on your system, if there are any. Click "Create New Export" to create a new one, or click on the name of an existing one to edit it.
  
  
 +
[[File:cl_DE_2.png|border]]
  
−
==Creating a Data Extraction which produces a spreadsheet==
 
  
−
Click on the menu item to see a list of the Data Extractions currently set up on your system, if there are any. Click "Create New Export" to create a new one.
+
=="Who To Extract?" tab==
  
−
[[File:DE_2.png|border]]
+
By default, Data Extractions include Clients and ignore other organisation/person Types. If you want to look at other organisation/person Types, click on the "Who To Extract?" tab.
  
 +
If you choose to Exclude a certain Type, this will override the Included ones. For example, if you Include Clients and Exclude Volunteers, Clients will be Included, but anyone who is both a Client and a Volunteer will be Excluded.
  
−
===Choose who to extract===
 
  
−
Click on the "Who To Extract?" tab.
+
[[File:cl_DE_10.png|border]]
  
−
[[File:DE_3.png|border]]
 
  
 +
If you select '''Not Relevant''', this simply means that that Type will not affect the output; organisations/people with only that Type will not be shown, but organisations/people with that Type as well as another one will not be excluded either.
  
−
You can now choose the different Types of organisations/people to show details for. For each Type, there are three choices:
 
  
−
# '''Include'''. If a Type is included, then details about any organisation/person with that Type will be shown on the output spreadsheet. For example, if you set "Include Clients?" to "Include", the output will show details about all the Clients.
 
−
# '''Exclude'''. If a Type is excluded, they will not be shown, and this will override any includes. For example, if you set "Include Clients?" to "Include" and set "Include Volunteers" to "Exclude", the output will show details about all the Clients ''unless they are also Volunteers'', in which case their details will not be shown.
 
−
# '''Not Relevant'''. This setting simply means that that Type will not affect the output; organisations/people with only that Type will not be shown, but organisations/people with that Type as well as another one will not be excluded either.
 
  
 +
==Selection Criteria tab==
  
−
[[File:DE_4.png|border]]
+
On the "Selection Criteria" tab, there are various settings that relate to the way this data extraction will behave.
−
 
 
−
 
 
−
===Name and other basic details for the data extraction===
 
−
 
 
−
Click on the "Selection Criteria" tab.
 
−
 
 
−
[[File:DE_5.png|border]]
 
−
 
 
−
 
 
−
You can now enter the following details:
 
−
 
 
−
* Definition Name
 
−
* Description - once you have set up a Data Extraction, you will be able to save it and then return to it and run it again by just clicking a "run" button. This description will be displayed on the list of Data Extractions, so it's helpful to put a quick description of what the data extraction will produce.
 
−
* Name for "Save As" - when the system produces a spreadsheet for you to download, this will be the name of the file (appended with the date and time that the file was created). If you do not enter anything, the file will be named "excel_output" followed by the date and time of creation.
 
−
* Destination - select "Export to Spreadsheet" (the other options will be covered later).
 
−
* Send letters or emails (based on 'preferred method of communication')? - only relevant for letter creation, not spreadsheets
 
−
* Suppress Letters When Client Requests It?  - setting this to "No" will override the field on the [[Contact and Consent Rules]] page.
 
−
 
 
  
 +
* '''Definition name''' - the name of the data extraction, which will be displayed on various screens.
 +
* '''Description''' - displayed on the main list of data extractions.
 +
* '''Name for "Save As"''' - this will be used if the data extraction is used to create a spreadsheet.
 +
* '''Export to a Spreadsheet or Create Mail-merged Letters/Emails?''' - this is where you choose the destination for this extraction. The choices are:
 +
** ''Export to Spreadsheet'' - produces an Excel spreadsheet which you can save to your computer for further use
 +
** ''Send to Blue Folder Lookup'' - this will take the data from the data extraction straight to Charitylog's Blue Folder Lookup page, where you can manipulate the data and also add other data - for example, you could use this method to add information from another Data Extraction to the output of this one.
 +
** ''Batch Send SMS Messages'' - creates a batch of SMS text messages to the people identified by the data extraction. You will need to have [[Textanywhere]] set up to use this feature.
 +
** ''Merge With'' - [[Standard Letter Templates]] will be named here. You can create batches of letters or emails (depending on the way the template in question is set up).
 
<div class="toccolours mw-collapsible mw-collapsed" style="width:1024px">
 
<div class="toccolours mw-collapsible mw-collapsed" style="width:1024px">
−
===Step 1: Choose Selection Criteria===
+
* '''Send letters or emails (based on 'preferred method of communication')?''' - click "Expand" to show this section.
−
 
 
−
'''Choosing Selection Criteria''' is optional - click "Expand" to show this section.
 
 
<div class="mw-collapsible-content">
 
<div class="mw-collapsible-content">
 +
This option only applies to quite a specific scenario. For the option to be applicable, you need to be using the "Preferred Method of Communication" field on the [[Details Screen]] to keep a record of peoples' communication preferences, and you need to be merging letters/emails using a [[Standard Letter Templates|Template]] that is set to produce ''Letters or Emails'' based on that preference. The options are:
 +
** ''Create letters for all (ignore preferences)''
 +
** ''Create emails for all (ignore preferences)''
 +
** ''Create letters (only for non-email recipients)'' - i.e. anyone who has an non-email method named as their Preferred Method of Communication will not be included in the merge
 +
** ''Create emails (only for non-letter recipients)'' - the opposite; anyone who has an email method named as their Preferred Method of Communication will not be included in the merge
 +
** ''Create letters and emails (use preferences)''
  
−
Click the "Choose Selection Criteria" button.
+
For each [[Communication Methods|Method]] there is a field which can be set - "Is this an email method?". This field governs whether someone using that method as their "Preferred Method Of Communication" should be included in the merges or not. The option of "create emails (only for non-letter recipients)" should really be called '''"Create emails (only for email recipients"'''.
−
 
+
</div></div>
−
 
+
* '''Suppress Letters When Client Requests It?''' - this relates to a setting in [[Contact and Consent Rules]]. If set to "No", mail merges will create letters for everyone even if they have the "Suppress Letters on Mailmerge Extract" option set to "Yes" in their [[Contact and Consent Rules]] area.
−
[[File:reports_20.png|border]]
+
* '''Suppress Extract of People Whose Preferred Contact Method is Email?''' - this setting is purely included for our customers who want to export data to Excel for mail merging. The assumption is that you might want to create emails from Charitylog but letters from Excel.
−
 
+
* '''Include This Extract in Mail-Merge Audit?''' - if set to "No", mail merges created with this configuration will not be shown in the [[Letter History Report]].
−
 
+
* '''Hide 'Anonymous Client' records''' - this refers to organisation/person records created using the "Record a Contact (for an anonymous client)" feature. These are created with a flag that marks them as "Anonymous", so even if they have been given some level of identifiable data - like a name or a postcode - they are still marked as "Anonymous". This setting lets you decide whether those people should be included in a data extraction/mail merge. or left out.
−
You can now set up rules for the data extraction.
+
* '''Merge "Related and Partnered" Clients and Send Single Letter to Both''' - people who are linked using the [[Relationships Screen]] have a preference which tells the system to only send one letter rather than two if they are both present in a mail merge. This is useful for partners etc.
−
 
+
* '''Active Records Only?''' - defaults to "Yes". Refers to the [[Active Field]].
  
−
[[File:reports_22.png|border]]
 
  
 +
===1. Choose Selection Criteria (optional)===
  
−
You have -
 
  
−
* '''Condition''' - choose WHERE, AND or OR (only WHERE will be available on the first line)
+
[[File:cl_DE_3.png|border]]
−
* '''Field name''' - choose the field name you want to look at
 
−
* '''Selection Criteria''' - choose criteria as shown:
 
  
−
[[File:reports_23.png|border]]
 
  
−
* '''Selection Value''' - you will be prompted for the value when you select the field name. Selecting the "Gender" field will show the following popup:
+
If you click the "Choose Selection Criteria" '''button''', you can introduce filters for the data extraction based on the standard fields in the system. For example, you could choose to only extract data for clients within a certain set of postal districts.
  
−
[[File:reports_24.png|border]]
+
* '''These filters only apply when the data extraction (or mail merge) is run from this screen. If the data extraction is applied on a Blue Folder Lookup page, these filters will not be applied.'''
  
−
So choosing to only extract females would be set up like this:
+
Clicking on the button will take you to the "Mail Merge Criteria" page.
  
  
−
[[File:reports_25.png|border]]
+
[[File:cl_DE_4.png|border]]
  
  
−
Clicking "Save Details" will save that line and give the option to add another line. Choosing to extract only females over 55 would look like this:
+
You can now set up your filters. First choose the field you want to filter by, and once you have chosen it, you will see a popup where you can pick the options to use for your filter. (options for "Postal District" shown)
  
  
−
[[File:reports_26.png|border]]
+
[[File:cl_DE_5.png|border]]
  
  
−
Using -
+
The filters default to "equals" (or "in (this,this,this)" which is the multiple version of "equals"), but you can change the criteria. The options are:
−
 
 
−
* Condition AND
 
−
* Field name "Age Range"
 
−
* Selection Criteria "IN (this, this this)"
 
−
* Selection Value "55 to 64, 65 to 74, 75 to 84, 85 to 94, Over 95"
 
−
 
 
−
''The "Field Name" will control what type of data you are allowed to enter. For example "Forename" or "Address" will accept text, whereas "Postal District" will require a selection of districts from data already held in Charitylog.''
 
−
 
 
−
You do not necessarily have to make any selection criteria. If you leave the selection criteria blank, the extraction will simply look at all possible organisations or people.
 
−
 
 
−
Once you have selected the relevant rules, if any, click "Save and Close" to save the query and proceed to step 2.
 
−
 
 
−
 
 
−
[[File:reports_27.png|border]]
 
−
</div></div>
 
  
 +
''Options intended for use on any type of field''
 +
* Equals
 +
* Not Equals ''(does not equal)''
 +
''Options intended for use on number fields''
 +
* Greater than
 +
* Greater than or equal to
 +
* Less than
 +
* Less than or equal to
 +
''Options intended for use on text fields''
 +
* Text begins with
 +
* Text contains
 +
* Text does not contain
 +
''Only for use with drop-down lists:''
 +
* In (this, this, this)
  
−
===Step 2: Choose What To Print===
+
After you "Save Details", another line will appear. You can add as many criteria as required, and each subsequent line can be an OR line (which would widen the results) or an AND line (which would narrow the results).
  
−
Click the "Choose What To Print" button.
+
The screenshot below shows rules that will extract data about people who are male, 50 or over, and live in the North or West postal districts.
  
  
−
[[File:reports_28.png|border]]
+
[[File:cl_DE_6.png|border]]
  
  
−
This will display a screen with all available fields on it. Simply tick the ones that you want to be displayed.
+
===2. Choose what to print===
  
−
* Note that you may want to extract names if you need to identify the organisations or people - if you do not select names for export, and apply the data extraction to a report without name data, you will not know who the organisations or people are.
+
You need to choose some data to print. This step is not optional - even if you are producing a mail merge, which doesn't take fields directly from whatever you specify here, you will need to choose at each one field.
−
* Alternatively, do not select names if you want the report to be anonymous! You may well already need to manually delete names and addresses from a spreadsheet to anonymise it - don't put names in the data extraction too; you will only need to delete them twice.
 
  
−
The screenshot shows name, forename and title selected.
+
If you are producing a data extraction on a spreadsheet, each option you choose here will be displayed in a column on the spreadsheet.
  
  
−
[[File:reports_29.png|border]]
+
[[File:cl_DE_7.png|border]]
  
  
−
Once you have made your selections, click the "Save and Close" button.
+
Clicking the button will show you a list of standard fields that you can choose to display. You can order them in the default order, or alphabetically, by clicking the buttons in the top right hand corner.
  
  
−
[[File:reports_30.png|border]]
+
[[File:cl_DE_8.png|border]]
  
  
−
===Step 3: Export The Results===
+
You can also filter your selection by typing in the "Filter Mail merge fields" box. In the screenshot, the filter has been used to show all the address fields, which have then been selected.
  
  
−
At this point you will be returned to the main Data Extraction screen. You can now click the "Save and Close" button, or the "Export the Results" button to run the data extract on your full database. So using the scenario above - having chosen to export females over 55, with name, forename and title data;
+
[[File:cl_DE_9.png|border]]
  
−
* Clicking "Export the Results" will create a spreadsheet like this:
 
  
 +
===3. Export The Results===
  
−
[[File:reports_31.png|border]]
+
Clicking this button will run the data extraction. Different things will happen, depending on which option is selected in the "Export to a Spreadsheet or Create Mail-merged Letters/Emails?" field.
  
  
−
From here you can drill down using Excel's features. Pivot Tables are one of the most useful features; see [[Tables (Excel)]] for more information.
+
[[File:cl_DE_11.png|border]]
  
  
 
----
 
----
 
[[File:helpheader_small.png|right]]
 
[[File:helpheader_small.png|right]]
−
[[User:Rob Kay|Rob Kay - manual author]] ([[User talk:Rob Kay|talk]]) 16:20, 1 December 2015 (GMT)
+
[[User:Rob Kay|Rob Kay - manual author]] ([[User talk:Rob Kay|talk]]) 13:48, 12 January 2017 (GMT)
 +
[[Category:Reporting]]

Revision as of 14:04, 12 January 2017

Helpheader small.png

Location in standard build: Reports tab > Data Extraction/Mail Merge


File:Cl DE 1.png


Data Extractions provide a way to export data about organisations/people stored on the system. You can choose:

  • which types of organisation/person you want information about
  • whether to filter these people (for example, only display people who live in a certain district)
  • which fields should be displayed on the output (if applicable)

Within the Data Extraction feature, you can create extractions that send the relevant people to a Mail Merge.

Click on the menu item to see a list of the Data Extractions currently set up on your system, if there are any. Click "Create New Export" to create a new one, or click on the name of an existing one to edit it.


File:Cl DE 2.png


"Who To Extract?" tab

By default, Data Extractions include Clients and ignore other organisation/person Types. If you want to look at other organisation/person Types, click on the "Who To Extract?" tab.

If you choose to Exclude a certain Type, this will override the Included ones. For example, if you Include Clients and Exclude Volunteers, Clients will be Included, but anyone who is both a Client and a Volunteer will be Excluded.


File:Cl DE 10.png


If you select Not Relevant, this simply means that that Type will not affect the output; organisations/people with only that Type will not be shown, but organisations/people with that Type as well as another one will not be excluded either.


Selection Criteria tab

On the "Selection Criteria" tab, there are various settings that relate to the way this data extraction will behave.

  • Definition name - the name of the data extraction, which will be displayed on various screens.
  • Description - displayed on the main list of data extractions.
  • Name for "Save As" - this will be used if the data extraction is used to create a spreadsheet.
  • Export to a Spreadsheet or Create Mail-merged Letters/Emails? - this is where you choose the destination for this extraction. The choices are:
    • Export to Spreadsheet - produces an Excel spreadsheet which you can save to your computer for further use
    • Send to Blue Folder Lookup - this will take the data from the data extraction straight to Charitylog's Blue Folder Lookup page, where you can manipulate the data and also add other data - for example, you could use this method to add information from another Data Extraction to the output of this one.
    • Batch Send SMS Messages - creates a batch of SMS text messages to the people identified by the data extraction. You will need to have Textanywhere set up to use this feature.
    • Merge With - Standard Letter Templates will be named here. You can create batches of letters or emails (depending on the way the template in question is set up).
  • Send letters or emails (based on 'preferred method of communication')? - click "Expand" to show this section.

This option only applies to quite a specific scenario. For the option to be applicable, you need to be using the "Preferred Method of Communication" field on the Details Screen to keep a record of peoples' communication preferences, and you need to be merging letters/emails using a Template that is set to produce Letters or Emails based on that preference. The options are:

    • Create letters for all (ignore preferences)
    • Create emails for all (ignore preferences)
    • Create letters (only for non-email recipients) - i.e. anyone who has an non-email method named as their Preferred Method of Communication will not be included in the merge
    • Create emails (only for non-letter recipients) - the opposite; anyone who has an email method named as their Preferred Method of Communication will not be included in the merge
    • Create letters and emails (use preferences)

For each Method there is a field which can be set - "Is this an email method?". This field governs whether someone using that method as their "Preferred Method Of Communication" should be included in the merges or not. The option of "create emails (only for non-letter recipients)" should really be called "Create emails (only for email recipients".

  • Suppress Letters When Client Requests It? - this relates to a setting in Contact and Consent Rules. If set to "No", mail merges will create letters for everyone even if they have the "Suppress Letters on Mailmerge Extract" option set to "Yes" in their Contact and Consent Rules area.
  • Suppress Extract of People Whose Preferred Contact Method is Email? - this setting is purely included for our customers who want to export data to Excel for mail merging. The assumption is that you might want to create emails from Charitylog but letters from Excel.
  • Include This Extract in Mail-Merge Audit? - if set to "No", mail merges created with this configuration will not be shown in the Letter History Report.
  • Hide 'Anonymous Client' records - this refers to organisation/person records created using the "Record a Contact (for an anonymous client)" feature. These are created with a flag that marks them as "Anonymous", so even if they have been given some level of identifiable data - like a name or a postcode - they are still marked as "Anonymous". This setting lets you decide whether those people should be included in a data extraction/mail merge. or left out.
  • Merge "Related and Partnered" Clients and Send Single Letter to Both - people who are linked using the Relationships Screen have a preference which tells the system to only send one letter rather than two if they are both present in a mail merge. This is useful for partners etc.
  • Active Records Only? - defaults to "Yes". Refers to the Active Field.


1. Choose Selection Criteria (optional)

File:Cl DE 3.png


If you click the "Choose Selection Criteria" button, you can introduce filters for the data extraction based on the standard fields in the system. For example, you could choose to only extract data for clients within a certain set of postal districts.

  • These filters only apply when the data extraction (or mail merge) is run from this screen. If the data extraction is applied on a Blue Folder Lookup page, these filters will not be applied.

Clicking on the button will take you to the "Mail Merge Criteria" page.


File:Cl DE 4.png


You can now set up your filters. First choose the field you want to filter by, and once you have chosen it, you will see a popup where you can pick the options to use for your filter. (options for "Postal District" shown)


File:Cl DE 5.png


The filters default to "equals" (or "in (this,this,this)" which is the multiple version of "equals"), but you can change the criteria. The options are:

Options intended for use on any type of field

  • Equals
  • Not Equals (does not equal)

Options intended for use on number fields

  • Greater than
  • Greater than or equal to
  • Less than
  • Less than or equal to

Options intended for use on text fields

  • Text begins with
  • Text contains
  • Text does not contain

Only for use with drop-down lists:

  • In (this, this, this)

After you "Save Details", another line will appear. You can add as many criteria as required, and each subsequent line can be an OR line (which would widen the results) or an AND line (which would narrow the results).

The screenshot below shows rules that will extract data about people who are male, 50 or over, and live in the North or West postal districts.


File:Cl DE 6.png


2. Choose what to print

You need to choose some data to print. This step is not optional - even if you are producing a mail merge, which doesn't take fields directly from whatever you specify here, you will need to choose at each one field.

If you are producing a data extraction on a spreadsheet, each option you choose here will be displayed in a column on the spreadsheet.


File:Cl DE 7.png


Clicking the button will show you a list of standard fields that you can choose to display. You can order them in the default order, or alphabetically, by clicking the buttons in the top right hand corner.


File:Cl DE 8.png


You can also filter your selection by typing in the "Filter Mail merge fields" box. In the screenshot, the filter has been used to show all the address fields, which have then been selected.


File:Cl DE 9.png


3. Export The Results

Clicking this button will run the data extraction. Different things will happen, depending on which option is selected in the "Export to a Spreadsheet or Create Mail-merged Letters/Emails?" field.


File:Cl DE 11.png


Helpheader small.png

Rob Kay - manual author (talk) 13:48, 12 January 2017 (GMT)

Retrieved from ‘https://wiki.dizions.co.uk/index.php?title=Orgs_%26_People_Extract&oldid=5884’