Custom Event Data Exports

There are several ways to access reporting for a single event.

1. From the Events homepage, use the gear icon by the event you want to pull reports for and choose Create Reports. This will move you directly into the reporting options for the specific event you used the gear icon for.

2. While working in the Event as an admin, choose Form Reporting from Manage Form. This will move you directly into the reporting options for the specific event you are working in.

3. From the Event Report page, admins can access data for a single event by selecting the event from the dropdown and clicking Show Reports. This control also allows you to start typing in the name of the event and the list will be filtered as you type.

To build a new custom export, click the Create New Export button under the Custom Event Data Export heading.

• Settings
• Configure Data
• Data Viewer
• Field List
• Preview
• Notification Email
• SFTP
• Finish
• Custom Event Data Export Grid
• Additional Business Rules
• Report Columns
  • E-Commerce
  • Appeal Codes
  • "Date Submitted" and "Last Updated" Columns
• Large Exports

 

Settings

The first step in the event export flow is configuring the settings of when the export should be run.  The options available are the following:

• On-Demand:  Allows the admin to generate and save an export without scheduling the export.  When this option is selected, there will not be a 'File Retrieval' option to select because the export is not scheduled.
• Once at a scheduled date:  Allows the admin to generate, save and schedule an export to be delivered via email or Secure FTP one time at the date determined by the admin.
• Daily:  Allows the admin to generate, save and schedule an export to be delivered daily via email or Secure FTP.
• Weekly:  Allows the admin to generate, save and schedule an export to be delivered weekly via email or Secure FTP.
• Biweekly:  Allows the admin to generate, save and schedule an export to be delivered every 14 days via email or Secure FTP.
• Monthly:  Allows the admin to generate, save and schedule an export to be delivered monthly via email or Secure FTP.
• Quarterly:  Allows the admin to generate, save and schedule an export to be delivered quarterly via email or Secure FTP.
• Yearly:  Allows the admin to generate, save and schedule an export to be delivered yearly via email or Secure FTP.
• Last day of month:  Allows the admin to generate, save and schedule an export to be delivered the last day of the month via email or Secure FTP.
• Last day of quarter:  Allows the admin to generate, save and schedule an export to be delivered the last day of the quarter via email or Secure FTP.

If you select to run the export On-Demand, then the details section will include the option to save the export, fields to enter the Export Name and Description, and the option to use Data Viewer to customize the export.  The option to Save this export so it can be used again later will be selected by default and will provide the admin the fields to enter the Export Name and Description.  The Export Name is a required field that is used to save the export in the Custom Event Data Export grid.  The Description is optional and will be added to the Custom Event Data Export grid if the admin chooses to enter one.  The option to use Data Viewer is unchecked by default.  If this option is checked by the admin, the progress bar across the top will be updated to include a Data Viewer step.

If you select to run the export using any of the other options, the Details section will include fields to enter the Export Name and Description, the date to start the first export and the date to end the export, the file retrieval options and the option to use date viewer to customize the export.  In addition a 'Notification Email' step is added to the progress bar across the top.  There will not be an option to 'Save the export so it can be used again later' because this will happen by default.

The Export Name is a required field that is used to save the export in the Custom Event Data Export grid.  The Description is optional.  If you enter a Description it will also be displayed in the grid. The Export scheduling options provides the admin with a calendar to select when the first export should be generated and when the last export should be generated.  For example, an admin could select to run the export daily from October 1st to October 31st and the export would be generated every day during the month of October.  The start date is a required field, but the end date is not required.  If no end date is selected, the export will continue to be generated until the export is deleted or an end date is entered.  If the admin selects the Once at a Scheduled Date option, there will only be one date filter to select the date the export to run.  The File Retrieval options allow the admin to have the export emailed or to have the file saved on the client's Secure FTP site.  The option will default to the email option.  If the option to have the file saved to the Secure FTP site is selected, the Email Notification step displayed in the progress bar across the top will change to a Secure FTP option.   

If you do not have Secure FTP set up for your site, then the file retrieval option will not display. It will default to having the file emailed, and the Notification Email step will automatically be added to the progress bar. 

The option to use Data Viewer is unchecked by default.  If this option is checked by the admin, the progress bar across the top will be updated to include a Data Viewer step. 

NOTE:  The Data Viewer option is only available for Super Admins.  If the admin creating the export is an Events Admin only, the Use Data Viewer to customize this export option will not be displayed.

 

Configure Data

The second step in the event export flow is configuring the data to be returned.  Configuring the data is broken out into two main sections: Event Data and Commerce Data. 

The Event Data can be filtered to include the following:

• Event data with all related activities and categories - Selected by default.  This will return registrants who are registered for the event or any of the activities, and will include by default fields for the main event, fields for the activities, and fields for the categories.  If this is an RSVP event, the records returned will include the Yes and No responses for the event or any activity.
• Main event only - This option will return registrants who are registered for the main event category and will include by default fields just from the main event and categories.  There will be no activity data.  If this is an RSVP event, then the records returned will include the Yes and No responses for the main event.
  
• Specific Activities - The Specific Activities option displays a list box with all the activities on an event and allows you to select from one activity to all the activities.  This will return registrants who are registered for any of the activities selected and will include by default fields for the activities selected.  If this is an RSVP activity, the records returned will include only the Yes registrations for any of the activities selected. If there are no activities on the event, then a No available activities message will appear when the Specific Activities option is selected.
• Specific Categories - This option displays a list box with all the categories on an event and allows the admin to select from one category to all the categories where the main event is considered a category.  This will return registrants who have a value in any of the category fields selected and will include by default the fields for the categories selected.  NOTE:  The option to export data for a donation category will be included in the Campaign export.  The Events admin will not be able to select a donation category in the event export flow.  The main event category will always be listed as a category, so there should not be a time when there are no categories to select.

You can also Filter data based on when the users submitted the form. This option is unchecked by default.  When the option is checked (enabled) a From date box and a To date box are displayed allowing you to enter the dates to filter the submissions.  The admin is required to enter either the From date or the To date.  If a From date is not entered it will pull data since the first submission.  If a To date is not entered it will include data through the last submission.

Limit export to only use data updated since last run.  This option is unchecked by default.  When the option is checked (enabled), the output will only include records that have been added or updated since the last time the export was generated.  This option is not available for On-Demand exports and Once at a scheduled date exports.

 

Commerce Data can be configured to do the following:

• Include billing information - This option checked (enabled) by default and will include the billing and transactional data.  The admin can uncheck the Include billing information option to not have the below fields included in the export. The specific fields that are included are those to the right of the commerce_instance_uid and includes the following fields in the export:
  • Order Total
  • Transaction ID
  • Purchase Date
  • Authorization Code
  • Customer Trans Number
  • Retrieval Code
  • Appeal Code
  • Billing Name
  • Credit Card Type
  • Billing Email
  • Billing Phone
  • Billing Street 1
  • Billing Street 2
  • Billing City
  • Billing State
  • Billing Zip
  • Billing Country
• Include incomplete e-commerce transactions -This option is disabled (unchecked) by default and allows you to include any form submissions that were started but not completed by the user.  You can use this information to follow up with the user or research issues with your form.
  
• Include voided transactions - This option is checked (enabled) by default and allows the admin to included all fully-adjusted (voided) commerce items.  The admin can uncheck the box to exclude these fully adjusted transactions from the export.  When this option is unchecked (disabled), the original transaction row and the row showing the full adjustment are excluded from the export.

*If the event does not include any commerce, the commerce section will not appear on the Configure Data step.

 

Data Viewer

The third step in the event export flow is Data Viewer (if the option was enabled on Step 1), where you can define the criteria, write the query logic and preview the query results.  The fields that drive registration for the main event or activity will be included in the Define Criteria section by default.  If a category is selected as the export type on the previous step, then the Define Criteria section will display a categories filter with the category pre-selected.  The admin has the option to add additional fields or remove the default fields.  They can also define the criteria for each field in addition to writing the query logic.  This step allows you to filter the data that will be included in the export.

NOTE:  The default for Write Query Logic will be 'OR' on the Event Export Data Viewer step.  The Data Viewer usually defaults to 'AND' in other areas of Encompass, but when coming from the event it will default to 'OR' because the queries used for the event export types use 'OR'.  For example, if you selected the Specific Activities export type and selected two activities, we will return records of users who are registered for Activity 1 OR Activity 2.  The 'OR' will remain on the query when it goes into the Data Viewer step.

NOTE:  The Data Viewer option is only available for Super Admins.  If the admin creating the export is an Events Admin only, the Use Data Viewer to customize this export option will not be displayed.

Field List

The next step in the event export flow is the Field Selector step.  This step allows you to select the fields to include in the export using the field selector that includes the Forms column, the Available Fields column and the Selected Fields column.

The Forms column will include the following folders:

• Quick Field List: The quick field list folder includes existing fields from previously created forms.
  
• Profile:  The profile folder includes all profile forms on the site and allows the admin to drill down and add certain fields from the profile form to be included in the event export results.
  
• Event: The event folder is expanded to show only the event for which the export is being created.  If the Event data with all related activities and categories or the Main event only option is selected as the export type then the folders for the main event category, all activities and all other categories will display.  This will allow the admin to select fields from any of the areas on the event.  If the Specific Activities or Specific Categories option is selected, the admin will only be able to select fields from the activity / category selected.  

The Available Fields column displays fields from the categories on the event.  Drill down in the event using the + sign to see the available fields.
NOTE:  The Available Fields will not automatically populate at the event level.  To see the fields on the event, you will need to drill down into each category before the fields will display in the Available Fields column.

 

 

The Selected Fields column displays fields that are also based on the selection that was made on the Configure Data step in the Export Type option.  The same general rules apply.  The fields that are in the Selected Fields column are the fields that will be included in the export.  The admin has the ability to remove these fields by selecting the icon to the left of the field.  If a field is removed from the Selected Fields column it will no longer have the gray highlight in the Available Fields column.  This gives the admin a clear understanding of the fields that are available and that will not be included in the export.  It also allows the admin to easily add the field back to the export by clicking on the field (that is now white) in the Available Fields column.  Fields that are added to the Selected Fields column will be added to the bottom of the list.  These fields can be re-ordered by dragging and dropping the fields.  By default all fields that are in the Available Fields column will be included in the Selected Fields column in the order they appear on the form.  In addition, Constituent ID, First Name, Last Name and Primary E-mail address fields will be added by default to the top of the selected fields column if the fields are not already included in the export.  First Name and Last Name will be auto-included in the file export at the beginning of the file.  If First Name and Last Name are included as Selected Fields they will be included in the export twice.

NOTE:  If the admin removes the ID field by mistake and would like to add it back to the selected fields, the admin can go to the Quick Field List folder to find the ID and add it back.  If the admin removes the First Name or Last Name field, and has selected to export an event or category only, the admin will have to go to the Quick Field List folder to find the First Name or Last Name field to add it back.

NOTE:  The three column field selector can be collapsed to only show one or two of the columns, but clicking the arrows between the columns.  The same arrows can be used to expand and show all of the columns.

The admin can also load a Saved Fields List by selecting the button above the left hand corner of the field selector.  This will open a list box allowing you to select the Saved Fields List to load.  The saved fields will be added to the bottom of the Selected Fields column and fields will not be duplicated.  This means that if the Saved Fields List has First Name (Mr Homecoming Event), and that field is already in the Selected Fields column, the field from the Saved Fields List will not be added. 

Below the field selector is a Share this field list within the event option. This provides the ability to save the field list that has been created to be used when creating another export for this event.   This is unchecked (disabled) by default.  When it is checked (enabled), a Field List Name text box will appear prompting the admin to enter a name to save the list. 

NOTE:  Currently if the admin goes to the Preview step and then goes back to the Field List, the fields in the Available column will no longer be displayed.  The admin can access these fields by going into the folders.  We have a ticket opened to address this item.  ENC-13930

Preview

The next step in the event export flow is the Preview step.  This step allows the admin to see a preview of what the export will include.  All of the fields that were in the Selected Fields column in the previous Field List step will be included as columns.  iModules auto-includes Last Name and First Name.  This is to ensure that an export is not generated without this information.  In order to eliminate the duplicate columns in the export, the admin can remove the Last Name and a First Name fields from the Selected Fields column on the previous step. 

Above the preview will be verbiage letting you know the number of rows that will be returned in the full export and the number of rows being displayed in the preview.   The preview will only show up to the first 10 rows of data.

Notification Email

The Notification Email step allows you to configure the email that is sent with the scheduled export.  This step is not shown if the admin selects to run an On-Demand export or if the admin selects to have the export saved to the Secure FTP.  The email notification step allows you to add recipients to receive the email and to set the name that will appear on the From line.  These will default to the email address and name of the admin creating the export.  The From Email will not be configurable and will be set to support@imodules.com.  The subject will default to "Site Name - Scheduled Export Export Name is ready", but can be changed.  The message will also have default verbiage that can be edited.

SFTP

If the admin selects Save file on this community's Secure FTP, then the Email Notification step is replaced with the Secure FTP step. This step gives you the naming convention for the file and allows the admin to enter an email address to receive a message when the file is available. By default the email address will include the email of the admin creating the export.

Finish

The last step of the flow, Finish, gives you details about the created export: Export Name, the export schedule (if applicable), how the email will be retrieved, and a link to run the export now. 

Every step in the event export flow will have the Back button, which will take you back to the previous step.  The Next button will move you to the next step, and the Return to Event Reporting link above the progress bar will take you back to the reporting for the event.  If data has been saved on the step, you will be warned that the data will be lost if you continue.

Custom Event Data Export Grid

When you complete the steps in the export, all exports that were saved to be used again will be added to the Custom Event Data Export grid with the details that were defined when creating the export.  The following columns are included in the Custom Event Data Export grid:

• Report Name
• Description
• Date Added
• Frequency
• Start Date
• End Date
• Data Output
• Last Run
• Last Updated By

The options available under the wheel  icon for the export are the following:

• Run Export - Running the export opens a new window with the following details:
  • Export Name - The name of the export the admin is attempting to run.
  • Export Description - The description of the export, if one is provided.
  • A radio button option that is selected by default to Use existing settings.  This will run the export with the settings that were configured during the initial creation of the export. 
  • Another radio button option to Filter data based on when users submitted the form.  Selecting this option will display a From date box and a To date box to allow the admin to enter a new date range to run the export.  A From date or a To date is required to generate the export if this option is selected.  Both dates do not need to be entered.
• Edit Export - Editing the export will open the new Create Export flow with the settings configured to the initial creation of the export.  The admin will be able to go in and making changes to the export.  Admins will be required to go all the way through the flow in order for the changes to be applied. Changes made on steps can change the configurations on later steps.  For example, if the first time the admin created the export the admin selected the Event and all activities option, and then when editing the export, changed the export type to the Main event only, the fields in the Field list would be updated and the activity fields will be removed.
  
• Delete Export - Deleting the export will display a message asking the admin to confirm the action to delete the export.  If they select the OK button, the export will be removed from the grid.

Additional Business Rules

• If the Show Donation Amount to Event Admins option has been enabled on the source Donation Campaign, Donation category data will be available through the Event Export.
• The order total is the total amount for the entire event submission even when the export is filtered to just include an activity or category.  If the admin creates an export for a specific RSVP activity and includes the billing information, the order total with the amount for the entire event submission will display.
• A progress indicator bar has been added to the top of the export to let the admin know how many steps there are and what step they are on.
• Columns that are not included in the event will not be added to the Selected column and will therefore not be added in the export.  For example, if the event is not configured to allow someone to opt-out of the event. then the opt-out columns will not be in the export.
• The field selector now uses Display name instead of Field name.
• The date used to pull the export is the date added field, not the date completed field.
• If the export type selected is Event data with all related activities and categories, then all Yes and No responses for the event or any activity will be returned.
• If the export type selected is Main event only, then all Yes and No responses for the main event will be returned.
• If the export type selected is Specific Activities, then all Yes registrations for any of the activities selected will be returned.
• If the export type selected is Specific Categories, then all records where the category field(s) have a value will be returned.
• If the export type selected is Specific Activities or Specific Categories and the event has a donation category, the campaign name field and the donation field will not be returned in the results.

 

Report Columns

A few special columns will appear in your Forms, Events, Donations and Membership reports.

E-Commerce

The following payment gateways will have a value for the Customer Trans Number column: AuthorizeNet, SecurePay, Verisign, PaymenTech, SkipJack, Shift4, PlugNPay, TouchNet, infiNET, NEOS, VirtualMerchant, Moneris, CashNet, CommWeb hosted, Moneris US, CashNet Hosted, Official Payments, Bluefin, CyberSourceSOAP, NIC USA ePay Emulator, First Data Global Gateway, USAePaySOAP.

The following payment gateways will have a value for the Retrieval Code column: AuthorizeNet, SecurePay, Verisign, PaymenTech, SkipJack, Shift4, PlugNPay, infiNET, VirtualMerchant, Moneris US, Official Payments, TransNational Bankcard, Bluefin, CyberSourceSOAP, NIC USA ePay Emulator, First Data Global Gateway, USAePaySOAP.

Appeal Codes

Appeal Code functionality can be used to generate additional reporting information. They are useful for marketing purposes, for example, to let you track which of the marketing methods you used was the most effective.
A column for appeal codes will appear in your reports. It will be populated with the appeal code(s) that were used. If no appeal code was used, the column will be empty. Click here to find out how to use appeal codes.

"Date Submitted" and "Last Updated" Columns

"Date Submitted" and "Last Updated" Columns provide clients more descriptive and nuanced date/time stamps for their form exports - Events, Donations and Membership Campaigns, and Commerce Forms. These date stamps provide a much clearer picture for analyzing form submissions and transactions.  NOTE:  This does not apply to standard forms, but if your standard, non-commerce form has an instance field, then the columns will be populated in the online export.

These new date/time stamps appear in the exports contiguous to the existing date_added column.

Date Submitted (date_submitted) - This is the date/time stamp for when the user completed the form submission, whether by final submission of the form or by successfully completing the commerce transaction.  This Date Submitted will not change through the life of the particular form submission.

Last Updated (last_updated) - This is the date/stamp of the original date of the submission, but which then changes to reflect the date/time of any subsequent edits or adjustments by the registrant or an admin

Due to the current profile form system functionality for generating last updated values on a per field basis for update fields exports, these columns have not been added to Profile Form exports. This will be done in a future enhancement.

NOTE:  Clients who have an automated offline process that depends on specific defined column headers and/or order will need to update the process to include these columns.  This does not affect the current web services query filters for start and end dates. 

Large Exports

If your export is very large, you might receive this message after the Preview page: Your file is being processed. Please wait...

The system will time out after 60 seconds if your file is too large to be processed in that time and will give you this message and options:

Select Save and Schedule.

Enter the Export Name, select today’s date for the Start Date, and select One Time for the Frequency. (End Date is not applicable.) Your export will be run in overnight processing, and you will receive an email notifying you when it is ready.