Using out-of-the-box queries: Membership
Using out-of-the-box queries: Membership
The following are out-of-the-box queries related to membership, organized by category. Use these predefined queries to easily retrieve membership data.
AUTOPAY
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| Total active auto-renew members | Displays the total number of members with active automatic renewal enrollments. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Recurring members | Lists all active recurring members, displaying their ID, member type, and start date. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Total cancelled auto-pay membership | Displays the total number of cancelled recurring membership renewals. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Cancelled membership enrollments | Lists all members who have cancelled their recurring membership renewals with their start date, cancellation date, and cancellation reason. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Total membership credit cards expired | Displays the total number of AutoPay membership enrollments associated with expired credit cards. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Expired payment methods | Returns members with expired payment methods with details like expiration date, credit card summary, and member type. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Recurring memberships by member type | Displays the number of members enrolled in recurring memberships by their member type. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Auto-pay members by region | Displays the number of members enrolled in recurring memberships by region. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Membership credit cards recently expired or expiring | Lists members with credit cards that are expired or about to expire, including expiration dates, card summaries, and member types. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| Membership pending invoices | Returns pending membership invoices with payment attempt details, including members, expiration dates, invoice numbers, descriptions, amounts, and failure messages. | $/Samples/Dashboards/AutoPay/Membership | AutoPay Membership dashboard |
| All failures | Shows members with three payment failures. | $/Samples/Dashboards/AutoPay/Membership/Failed payments | AutoPay Membership dashboard |
| Failed 3 times | Lists members with upcoming credit card expirations, showing the expiration date, card summary, and member type. | $/Samples/Dashboards/AutoPay/Membership/Failed payments | AutoPay Membership dashboard |
| Automatic payment enrollments | Allows users to search for active membership automatic payment enrollments, showing their membership type, status, and payment period end date. | $/Membership/DefaultSystem/Queries/AutoPay | Membership > Automatic payments > Enrollments |
| AutoPay processing tasks | List of AutoPay tasks for membership and their status. | $/Membership/DefaultSystem/Queries/AutoPay | Process payments (AutoPay task status tab) |
| Find renewal invoices due for automatic payment | Returns renewal invoices due for automatic payment processing on or before a specified date. | $/Membership/DefaultSystem/Queries/AutoPay | Process payments (Submit invoices tab) |
| Find renewal invoices due for automatic payment ALL | Returns all AutoPay membership invoices with an existing balance. | $/Membership/DefaultSystem/Queries/AutoPay | Process payments (Review invoices tab) |
| Find renewal invoices due for automatic payment TODAY | Returns all AutoPay membership invoices due for payment on or before today's date. | $/Membership/DefaultSystem/Queries/AutoPay | Process payments (Review invoices tab) |
| Payment history for automatic renewals | Allows users to search for processed automatic renewal payments within a specified date range, displaying member information, invoice details, amount, and payment status. | $/Membership/DefaultSystem/Queries/AutoPay | Reviewing payments |
| Payment history for automatic renewals (archived) | Allows users to search for successfully processed automatic renewal payments that are older than 90 days. | $/Membership/DefaultSystem/Queries/AutoPay | Reviewing payments |
| Processed automatic payments for renewals | Displays details of completed and failed processed payments, including member information, invoice details, amount, result, and transaction notes. | $/Membership/DefaultSystem/Queries/AutoPay | Retrieving processed payments |
| Submitted automatic payments for renewals | Shows payments submitted for processing, with details on member name, invoice, amount, and status. | $/Membership/DefaultSystem/Queries/AutoPay | Retrieving processed payments |
| Recently cancelled AutoPay Memberships | Returns automatic membership cancellations from the past seven days. | $/Membership/DefaultSystem/Queries/AutoPay | Staff recurring membership cancellation alert |
| Automatic payment membership enrollments | Lists members enrolled with AutoPay, with member type, status, start date, and payment method. | $/Common/Query Sources/Membership | Common membership query sources |
BILLING AND RENEWALS
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| Renewals | Allows users to search for renewals this year by name, ID, and member type. | $/Samples/Dashboards/Membership | Membership dashboard |
| Progress Tracker - Renewals This Year | Displays the number of members who have renewed this year. | $/Samples/Dashboards/Membership | Membership dashboard |
| Revenue last 12 months | Returns the membership revenue within the last 12 months. | $/Samples/Dashboards/Membership | Membership dashboard |
| Available billing products | Lists all billing products available for selection, showing the name, type, and product code. | $/Membership/DefaultSystem/Queries | Billing products |
| Billing log by contact | Shows the billing history for each contact, including membership type, billing cycle, bill dates, amount, and balance. | $/Membership/DefaultSystem/Queries | |
| Join billing cycles | Lists membership billing cycles available for joining. | $/Membership/DefaultSystem/Queries | Join Now |
| Recent renewals summary | Returns the total number of members who have renewed in the past seven days. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff recent renewals summary alert |
| Recent renewals | Lists members who have renewed in the past seven days. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff recent renewals list alert |
| BillingFeesQuery | Displays billing details for a specific ID, including billing date, cycle, amount, and balance. | $/Common/Queries/Billing | |
| BillingIssueNotificationByBillRunQuery | Lists billing runs for membership fees with details on the billing cycle, creation and start dates, unpaid bills, and links to issue notifications. | $/Common/Queries/Billing | Issue notifications |
| BillingIssueNotificationLogDetailQuery | Returns detailed records of billing notifications sent to recipients, including bill date, billing cycle, term dates, and notification status. | $/Common/Queries/Billing | Issue notifications (Logs tab > select Billing Cycle) |
| BillingIssueNotificationLogQuery | Displays a log of issued notifications, including billing cycle, communication type, status, and the number of notifications sent. | $/Common/Queries/Billing | Issue notifications (Logs tab) |
| BillingLogByBillCycleDataQuery | Shows a log of generated renewal billings with details on the billing cycle, accounting method, billing date, count, amount, status, and batch information. | $/Common/Queries/Billing | Generate renewals (Logs tab) |
| BillingLogByParty | Displays billing details for a specific billing run, including bill dates, member type, and amount for each subscriber. | $/Common/Queries/Billing | Generate renewals (Logs tab > select Billing Run) |
| WebRenewableBillingCycles | Lists membership billing cycles with details on joining and renewal eligibility, member types, and associated billing product codes. | $/Common/Queries/Commerce/Billing | |
| Subscriptions | List of non-membership subscriptions with details like ID, name, product title, status, beginning date, and paid through date. | $/Common/Query Sources/Membership | Common membership query sources |
| Invalid products in subscriptions | Lists subscriptions with invalid products, showing member details, organization, membership type, product code, product type, and start date. | $/Samples/Dashboards/Data Integrity/Membership | Data integrity dashboard (Membership tab) |
INVOICES
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| Renewal invoices | Returns all renewal invoices, including those with or without a balance. | $/Common/Query Sources/Membership | Common membership query sources |
| Organization open renewal invoices | Returns results to organization administrators if the organization they administer has open membership renewal invoices. | $/Common/Tasks/Queries | Organization administrator renewal due alert |
| OpenDuesInvoices | Returns all open dues invoices. | $/Accounting/DefaultSystem/Queries/OpenDuesInvoices | |
| OpenDuesInvoicesByBillingRun | Displays open dues invoices by most recent billing, including invoice date, sold to and bill to names, description, accounting method, and balance. | $/Accounting/DefaultSystem/Queries/OpenDuesInvoices | |
| Membership billing batch detail | Displays renewal transaction details for a selected batch, such as bill to customer, bill to ID, ship to customer, product, term dates, and amount. | $/Accounting/DefaultSystem/Queries/Batches/Renewal Batch Details | Batch reports window |
MEMBERS
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| Progress Tracker - Total Active Members | Displays the total number of active members in the Progress Tracker content item. | $/Samples/Dashboards/Membership | Membership dashboard |
| Progress Tracker - New members | Displays the total number of members who have joined in the past year in the Progress Tracker content item. | $/Samples/Dashboards/Membership | Membership dashboard |
| Progress Tracker - Lapsed members | Displays the number of lapsed members this year in the Progress Tracker content item. | $/Samples/Dashboards/Membership | Membership dashboard |
| Active members by type | Returns the number of active members by member type. | $/Samples/Dashboards/Membership | Membership dashboard |
| All members | Lists all active members. | $/Samples/Dashboards/Membership | Membership dashboard |
| New members | Lists members who have joined this year along with their membership type and join date. | $/Samples/Dashboards/Membership | Membership dashboard |
| New members this year | Allows users to search for new members from this year by name, ID, or member type. | $/Samples/Dashboards/Membership | Membership dashboard |
| Newest Members | Lists members who have joined in the last 100 days along with their membership type and join date. | $/Samples/Dashboards/Membership | Membership dashboard |
| ExpiredMemberUpdateDetailLog | Displays records of actions taken to manage expired members, including user, status, timestamps, number of members updated, invoices reversed, errors, and a details link. | $/Membership/DefaultSystem/Queries | Manage expired members (Logs tab) |
| ManageExpiredMembers | Lists expired members with their expiration date and status. | $/Membership/DefaultSystem/Queries | Manage expired members |
| Members with empty Join Dates | Lists members with missing join dates, including their IDs, names, organizations, and member types. | $/Samples/Dashboards/Data Integrity/Membership | Data integrity dashboard (Membership tab) |
| Orders by member type this year | Compares the number of product orders placed by various member types within the current calendar year. | $/Samples/Dashboards/Commerce/Product Sales | Commerce dashboard |
| Total event registrations by member type | Lists this year's event registrations broken down by registrant member types. | $/Samples/Dashboards/Events | Events dashboard |
| Billing History | Displays the billing history for the selected contact. | $/ContactManagement/DefaultSystem/Queries/Advanced/Contact/Financial | Membership tab of account pages |
| Non-membership subscriptions | Displays non-membership subscriptions for the selected contact. | $/ContactManagement/DefaultSystem/Queries/Advanced/Contact/Financial | Membership tab of account pages |
| Subscriptions | Shows payments submitted for processing, with details on member name, invoice, amount, and status. | $/ContactManagement/DefaultSystem/Queries/Advanced/Contact/Financial | Membership tab of account pages |
| Lapsing members | Lists members who expire within seven days. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff lapsing members list alert |
| Lapsing members summary | Displays the total number of members who expire within seven days. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff lapsing members summary alert |
| Joins today | Lists members who joined today. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff new joins alert |
| Member dropped or reinstated | Lists members whose status has changed recently to dropped or reinstated. | $/Common/Tasks/Queries/Staff dashboard alerts | Staff dropped or reinstated members alert |
| New members in the last 7 days | Used by an automated task to send a communication to new members who joined within the past seven days. | $/Common/Tasks/Queries | New member welcome email |
| Expired members | Indicates if the selected contact's membership is expired. | $/Common/Tasks/Queries | Expired membership |
| Expired organizations | Used to alert organization administrators that the organization is expired. | $/Common/Tasks/Queries | Expired organizations alert |
| Members expiring within 60 days | Returns a list of members expiring within 60 days. | $/Common/Tasks/Queries | Membership expires soon alert |
| All active members | Lists all active members with details like member type, join date, and paid-through date. | $/Common/Query Sources/Membership | Common membership query sources |
| All non-members | Lists all non-members with details like name, ID, member type, and company designation. | $/Common/Query Sources/Membership | Common membership query sources |
| Inactive members | Lists all inactive members with details like member type, status, and paid-through date. | $/Common/Query Sources/Membership | Common membership query sources |
CHAPTERS
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| Members by chapter | Returns the number of active members by chapter. | $/Samples/Dashboards/Membership/ | Membership dashboard |
| Top chapters | Lists all chapters sorted by the number of members in each. | $/Samples/Dashboards/Membership/ | |
| Chart - Active members by type | Returns the number of active chapter members by member type. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Chart - Members by engagement category | Returns the number of chapter members per engagement category. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Progress Tracker - Active members | Displays the number of active members in the chapter in the Progress Tracker content item. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Progress Tracker - New members | Displays the number of chapter members who have joined in the past year in the Progress Tracker content item. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| New members this year | Lists new chapter members for the year with filters for name, ID, and date range. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Progress Tracker - Lapsed members | Displays the number of chapter members who have lapsed this year. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Lapsed members this year | Lists lapsed chapter members for this year with filters for name, ID, and expiration date range. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Progress Tracker - Event registrations | Displays the number of event registrations by chapter members this year. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| Registrations for events this year | Lists chapter member event registrations this year, including event name, dates, registrant, and registration link. | $/Samples/Dashboards/Membership/Specific chapter | Chapter dashboard |
| All engaged members | Lists all chapter members who are actively engaged, with details on their engagement percentile, score, and last engagement month. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| At risk engagement (no recent activity) | Lists chapter members at risk due to lack of recent engagement, including their last engagement month, percentile, score, and category. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Recently engaged | Lists chapter members who have recently engaged, showing their percentile, score, and most recent engagement month. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Active members | Lists active chapter members with their ID, effective date, and expiration date. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Chapter administrators | Lists all administrators for the chapter. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Expired members | Lists expired members with names, IDs, effective and expiration dates, and date/name filters. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Future members | Lists future chapter members with names, IDs, effective and expiration dates, and filters for the date range and member name. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Search members | Allows users to search chapter members by name, date range, and active status. | $/Samples/Dashboards/Membership/Specific chapter/Member engagement | Chapter dashboard |
| Manage chapter subscriptions | Allows users to search chapter members based on their activity status, with results listing members' names, effective dates, expiration dates, and active status. | $/ContactManagement/DefaultSystem/Queries/Groups/Chapters | Chapter dashboard |
| Find chapters | Lists all chapters with links to each. | $/ContactManagement/DefaultSystem/Queries/Groups/Chapters | Managing chapters |
| Selected chapter lapsing members | Lists chapter members who expire this month. | $/Common/Tasks/Queries/Specific chapter alerts | Specific chapter lapsing member alert |
| Selected chapter recent event registrations | Lists event registrations for chapter members within the past seven days (top 10 results). | $/Common/Tasks/Queries/Specific chapter alerts | Specific chapter recent event registrations |
| Selected chapter recent gifts | Returns gifts received from chapter members within the last 15 days. | $/Common/Tasks/Queries/Specific chapter alerts | Specific chapter recent gifts alert |
| Selected chapter recent joins | Lists chapter members who joined this month. | $/Common/Tasks/Queries/Specific chapter alerts | Specific chapter new joins alert |
| Selected chapter recent orders | Displays orders placed for chapter members in the last seven days. | $/Common/Tasks/Queries/Specific chapter alerts | Specific chapter recent orders |
| Chapter memberships | Lists all chapter memberships with name, ID, organization, member type, chapter join date, and status. | $/Common/Query Sources/Membership | Common membership query sources |