Reconciliation Report
Reconciliation Reports allow you to obtain a list of anyone who has purchased a Membership within the date range you enter. Name, ID, Campaign, Amount, and Billing Information are included by default.
Reconciliation Reports in sub-communities: Reconciliation reports can be pulled from the top level (GID=1) to also include reconciliation data for all sub-communities.
1. Choose Reconciliation Report.
2. Date Range: Enter the date range. If left blank, all information, regardless of date, will be pulled into the form.
NOTE: Enter a date in the first range box only to search for a range since a certain date up to the present. Enter a date range in the second date range box only to search for a range from the start of the community up to that date.
Choose the community data you want to include in the report:
- Main community (This includes regular sub-communities)
- Main community and all sealed sub-communities
- Exclude certain communities - You have the option to select the communities you want to exclude from the report and use the arrows to move them to the right.
- Main community and selected sealed sub-communities - A list box will appear displaying all of the sealed sub-communities. Multiple sub-communities can be selected by using the CTRL or shift key.
- Choose the additional communities you want to include in the report and use the arrows to move them to the right.
Include Voided Transactions: This option is checked by default. The report will show all transactions, including all fully-adjusted (voided) commerce items. Uncheck the box to exclude voided transactions from the report. NOTE: Voided Transactions are not the same as adjustments. Adjustments will not display in Reconciliation Reporting. This checkbox includes/excludes voided transactions (completely adjusted in one adjustment).
Click Next.
3. The system will indicate to you how many rows of member data will be included in the report. There are additional fields you can add to the report as well. As a best practice, do not include any additional fields until you view the default fields included in the report. You can view the defaults by clicking Preview.
4. The preview will show up to 10 rows of data and will contain the main items needed for a bookkeeping type report. If you want to add other fields, you can use the Back button. If you are happy with the information provided, click Generate Report.
5. There will be a link for you to Download File. By default, it will export to Excel as a CSV file. There are other options shown on the screen as well.
- I do not want to Save this Export
- Save this Export for Future Use
- Save and Schedule this Export - You can save and schedule the report to be pulled and sent to you at regular intervals. With a scheduled report, you can set up who will receive the email notification so the report can be downloaded.
- As a best practice, you should set an End Date for the scheduled report for a single Campaign so that it does not continue to run after the Campaign has ended.
- Data Output - select either
- All Available Data - Produces a cumulative report that includes all data requested in the query, across the lifespan of the form, event, or campaign.
- Data Updated Since Last Run - The report will export data per the configured query from the last time the report was run. For example, a weekly report will only show you the data updated within the last week.
Reporting for a Specific Campaign
When a report is run from the membership form, it will provide ALL the transaction history for a person. (Which means, for example, if you run a report from the membership form and filter for "Lapsed", you will get ALL transactions that are lapsed, even if someone now has a current membership.) To find the most recent status of a person, it is easier to use Data Viewer (see Data Viewer: Building a Query) which will only query on and return the most recent status.
Reporting for a Specific Campaign – Run Immediately
1. There are two ways to pull a report on a specific Membership campaign.
- Click
for the Campaign for which you want to pull reporting information.
- While working in the Campaign, choose Form Reporting from Manage Forms.
2. Choose Run the Report Immediately. You will then need to make a choice between the following options.
- Include all members who submitted this form.
- Include all members who submitted this form between [start date] and [end date].
For this example, we selected Include all members who submitted this form.
Include incomplete e-commerce transactions: Select this option to include any purchases 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.
Customize query: Indicate here if you would like to use the Data Viewer to further refine your report based on member data fields. Use this option to refine a particular report to include only certain types of members, or members from a particular region, etc.
Include Voided Transactions: This option is checked by default. The report will show all transactions, including all fully-adjusted (voided) commerce items. Uncheck the box to exclude voided transactions from the report.
3. Reporting Filters: There are additional filters that can be applied such as by Level, Expiration Date or Auto-renewal status. If you want to apply additional filters, select them here. Click Next to proceed.
For example, you can filter to find memberships that expired in the last x number of days or find memberships that will expire in x number of days so you can send them an email.
4. Export File Format: Select either CSV or Fixed Data Length depending on your preference.
5. The fields from the form will be in the Selected box. You may select as many fields as you like to create your query.
6. Select Additional Data Fields: Add additional data fields by using these two methods.
- Select from quick fields – Choose from an alphabetical list of fields to add to your form.
- Select from a tree view - Select from a list of existing fields by searching the tree view of previously created forms to find the desired field. Click on the plus signs (+) to drill down in the hierarchy.
7. Once you have all of the fields in the Selected box, click Add Selected Fields.
8. Sort items in the Export Fields List: Sort items in the list using the “Move” buttons to put the report columns in the desired order.
9. Save the Export Fields List: Here is your opportunity to save the Export Field List to reuse in the future.
NOTE: Saving the Export Field List is not the same as saving the actual export/report.
10. Click Next.
11. Report Preview: A preview of the report will be shown with up to 10 rows of data. If there were no results for your form, a message will display instead of displaying a preview. Click Next.
12. There will be a link for you to Download File. By default, it will export to Excel as a CSV file. There are other options shown on the screen as well.
- I do not want to Save this Export
- Save this Export for Future Use
- Save and Schedule this Export - You can save and schedule the report to be pulled and sent to you at regular intervals. With a scheduled report, you can set up who will receive the email notification so the report can be downloaded.
- As a best practice, you should set an End Date for the scheduled report for a single Campaign so that it does not continue to run after the Campaign has ended.
Reporting for a Specific Campaign – Schedule for a Later Date
1. There are two ways to pull a report on a specific campaign.
- Click
for the Campaign for which you want to pull reporting information.
- While working in the Campaign, choose Form Reporting from Manage Forms.
2. Choose Schedule the Report for a Later Date.
Include incomplete e-commerce transactions: Select this option to include any purchases 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.
Customize query: Indicate here if you would like to use the Data Viewer to further refine your report based on member data fields. Use this option to refine a particular report to include only certain types of members, or members from a particular region, etc.
Include Voided Transactions: This option is checked by default. The report will show all transactions, including all fully-adjusted (voided) commerce items. Uncheck the box to exclude voided transactions from the report.
3. Reporting Filters: There are additional filters that can be applied as well such as by Level, Expiration Date or Auto-renewal status . If you want to apply additional filters, select them. Click Next to proceed.
4. Export File Format: Select either CSV or Fixed Data Length depending on your preference.
5. The fields from the form will be in the Selected box. You may select as many fields as you like to create your query.
6. Select Additional Data Fields: Add additional data fields by using these two methods.
- Select from quick fields – Choose from an alphabetical list of fields to add to your form.
- Select from a tree view - Select from a list of existing fields by searching the tree view of previously created forms to find the desired field. Click on the plus signs (+) to drill down in the hierarchy.
7. Once you have all of the fields in the Selected box, click Add Selected Fields.
8. Sort items in the Export Fields List: Sort items in the list using the “Move” buttons to put the report columns in the desired order.
9. Save the Export Fields List: Here is your opportunity to save the Export Field List to reuse in the future.
NOTE: Saving the Export Field List is not the same as saving the actual export/report.
10. Click Next.
11. Report Preview: A preview of the report will be shown with up to 10 rows of data. If there were no results for your form, a message will display instead of displaying a preview. Click Next.
12. Schedule Report: You are now ready to save and schedule the report. Enter a report name, description, start date, and a frequency for how often the report should run (i.e. daily, monthly, yearly). As a best practice, you should set an End Date for the scheduled report for a single Campaign so that it does not continue to run after the Campaign has ended.
Data Output - select either
- All Available Data - Produces a cumulative report that includes all data requested in the query, across the lifespan of the form, event, or campaign.
- Data Updated Since Last Run - The report will export data per the configured query from the last time the report was run. For example, a weekly report will only show you the data updated within the last week.
Click to save your information.
13. Email Notification: The next screen prompts you for the admins who should receive the notification that the report has run. Fill in the following data: admins, From Name, Subject, and body text. Click Save Email Notification when you are done.
If your campaign contains Designations you will have links to two files available in the email: the normal Campaign report and a separate Designations report. The Designations file will contain only the Designations information. Each Designation will have its own row of data and will correlate to 1 row of date in the Campaign report file.
NOTE: Do NOT delete ##File Link## and ##File URL## from the message. These create the link to your report file and allow you to access it from the email.
14. Click Close.
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.
Membership Status
For a person's Membership status you will see the following:
- Active - Membership is current.
- Lapsed (Expired) - Membership is in a lapsed state if the expiration date passes without the membership being auto renewed or the constituent repurchasing a membership.
- Canceled - Membership has been canceled.
- Past - Previous membership level. A membership level/transaction is designated as 'Past' after a member purchases another membership. (The previous membership will show as "Lapsed" if it expired before the new membership was purchased.)
Reconciliation Report Columns
A column called “Community Name” will be added to all reconciliation reports. It is in reports from both the top level and those generated from sub-communities for standardization purposes.
- **LedgerFields** are the Commerce ID fields, they only show if they exist.
- **CustomFields** will only show if they are added to the report field list.
- **ID** is the institution’s constituent ID field, specific to the institution’s naming for that field
- **VolunteerAgentFields** will only show for Donations if Volunteer Fundraising is enabled.
- ACH columns will no longer appear. No payment gateway integrations currently support ACH so these columns were extraneous. If enabled in the future for a particular gateway, the the ACH columns will appear
- "From Facebook" will only show if the site has forms of that type enabled on the Social Applications Manager.
All Reconciliation Reports across three types of report options – running a new reconciliation report, running a saved reconciliation report, and scheduling a reconciliation report – now have the same consistent columns across all three options. (This was not previously the case, with variance in the columns / ordering across report types and methods.) ***As a result, any automated processes that you have in place for scheduled Reconciliation Reports will be affected by this new column and sort ordering. You will need to update your automated procedures to consume the files with their new sort order.***
Click here to download an Excel file showing the Reconciliation Report Column Ordering.
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.