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.
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| AUTOPAY | |||
| 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. | ||
| Total cancelled auto-pay membership | Displays the total number of cancelled recurring membership renewals. | ||
| Cancelled membership enrollments | Lists all members who have cancelled their recurring membership renewals with their start date, cancellation date, and cancellation reason. | ||
| Total membership credit cards expired | Displays the total number of AutoPay membership enrollments associated with expired credit cards. | ||
| Expired payment methods | Returns members with expired payment methods with details like expiration date, credit card summary, and member type. | ||
| Recurring memberships by member type | Displays the number of members enrolled in recurring memberships by their member type. | ||
| Auto-pay members by region | Displays the number of members enrolled in recurring memberships by region. | ||
| 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. | ||
| Membership pending invoices | Returns pending membership invoices with payment attempt details, including members, expiration dates, invoice numbers, descriptions, amounts, and failure messages. | ||
| All failures | Shows members with three payment failures. | $/Samples/Dashboards/ AutoPay/Membership/Failed payments |
|
| Failed 3 times | Lists members with upcoming credit card expirations, showing the expiration date, card summary, and member type. | ||
| 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. | 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. | Process payments (Submit invoices tab) | |
| Find renewal invoices due for automatic payment ALL | Returns all AutoPay membership invoices with an existing balance. | 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. | ||
| 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. | 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. | Retrieving processed payments | |
| Submitted automatic payments for renewals | Shows payments submitted for processing, with details on member name, invoice, amount, and status. | ||
| Recently cancelled AutoPay Memberships | Returns automatic membership cancellations from the past seven days. | 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 | |||
| 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. | ||
| Revenue last 12 months | Returns the membership revenue within the last 12 months. | ||
| 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. | ||
| Join billing cycles | Lists membership billing cycles available for joining. | 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. | 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. | Issue notifications | |
| BillingIssueNotificationLogDetailQuery | Returns detailed records of billing notifications sent to recipients, including bill date, billing cycle, term dates, and notification status. | 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. | 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. | Generate renewals (Logs tab) | |
| BillingLogByParty | Displays billing details for a specific billing run, including bill dates, member type, and amount for each subscriber. | 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 | |||
| 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. | ||
| 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 | |||
| 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. | ||
| Progress Tracker - Lapsed members | Displays the number of lapsed members this year in the Progress Tracker content item. | ||
| Active members by type | Returns the number of active members by member type. | ||
| All members | Lists all active members. | ||
| New members | Lists members who have joined this year along with their membership type and join date. | ||
| New members this year | Allows users to search for new members from this year by name, ID, or member type. | ||
| Newest Members | Lists members who have joined in the last 100 days along with their membership type and join date. | ||
| 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. | 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. | ||
| Subscriptions | Shows payments submitted for processing, with details on member name, invoice, amount, and status. | ||
| 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. | Staff lapsing members summary alert | |
| Joins today | Lists members who joined today. | Staff new joins alert | |
| Member dropped or reinstated | Lists members whose status has changed recently to dropped or reinstated. | 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. | Expired membership | |
| Expired organizations | Used to alert organization administrators that the organization is expired. | Expired organizations alert | |
| Members expiring within 60 days | Returns a list of members expiring within 60 days. | 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. | ||
| Inactive members | Lists all inactive members with details like member type, status, and paid-through date. | ||
| CHAPTERS | |||
| 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. | ||
| 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. | ||
| Progress Tracker - Active members | Displays the number of active members in the chapter in the Progress Tracker content item. | ||
| Progress Tracker - New members | Displays the number of chapter members who have joined in the past year in the Progress Tracker content item. | ||
| New members this year | Lists new chapter members for the year with filters for name, ID, and date range. | ||
| Progress Tracker - Lapsed members | Displays the number of chapter members who have lapsed this year. | ||
| Lapsed members this year | Lists lapsed chapter members for this year with filters for name, ID, and expiration date range. | ||
| Progress Tracker - Event registrations | Displays the number of event registrations by chapter members this year. | ||
| Registrations for events this year | Lists chapter member event registrations this year, including event name, dates, registrant, and registration link. | ||
| 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 |
|
| 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. | ||
| Recently engaged | Lists chapter members who have recently engaged, showing their percentile, score, and most recent engagement month. | ||
| Active members | Lists active chapter members with their ID, effective date, and expiration date. | ||
| Chapter administrators | Lists all administrators for the chapter. | ||
| Expired members | Lists expired members with names, IDs, effective and expiration dates, and date/name filters. | ||
| Future members | Lists future chapter members with names, IDs, effective and expiration dates, and filters for the date range and member name. | ||
| Search members | Allows users to search chapter members by name, date range, and active status. | ||
| 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 |
|
| Find chapters | Lists all chapters with links to each. | 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). | Specific chapter recent event registrations | |
| Selected chapter recent gifts | Returns gifts received from chapter members within the last 15 days. | Specific chapter recent gifts alert | |
| Selected chapter recent joins | Lists chapter members who joined this month. | Specific chapter new joins alert | |
| Selected chapter recent orders | Displays orders placed for chapter members in the last seven days. | 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 |