The Sales by Membership Report displays the sales revenue your organization has generated specifically from members. The report is organized by membership program and level, such as Family or Individual, so you can quickly compare the percentage of income generated from each. When running the report in Detail, Altru will also display a breakdown of the quantity and amount sold for each program, special event, and merchandise item. To run the report:
- Go to Sales
- Under Reports, click Sales by Membership
- In the parameters section of the report, choose the Membership program you'd like to report on.
- Under Report type, choose either Summary or Detail. You can also check the box to display a pie chart if desired.
- Under Sales Method, choose what Sales Method you'd like to run the report for. Note: This Report does not include any back office transactions.
- Under Date, select your date range. Note: The date range is the transaction date on completed orders. If you're including Group Sales in this report, ensure all of your groups are checked in prior to running this report. Groups that are not checked in will not show on this report.
- Click View Report
Option 2: Pull a Query with Membership Information
You can also pull a sales order query to view any sales. There are a few options you can use to either filter on member sales only or output membership information from your patrons. The instructions below will cover your options:
Start your query:
- Go to Analysis > Information Library
- Click Add an ad-hoc query
- Select the Source View of Sales Orders and click OK.
- The New Ad-hoc Query window will appear. You will need to add some fields to Include Records Where to filter on the sales you need for your query report (for example: do you need to report on all transactions between a certain date? Do you only want to see sales from one program or program event? Do you only want to view merchandise sales?) We have some suggestions below:
- If you'd like to report on sales between a specific date range, highlight Sales Orders at the very top of the left column. From the middle column, drag Transaction Date to Include Records Where. Set this filter to be equal to, or between your date range.
- If you'd like to report on sales in a specific sales method (Advanced, Group, Daily or Online), highlight Sales Orders at the very top of the left column. From the middle column, drag Sales Method Type to Include Records Where. Set this to be equal to, or one of the sales methods you need in your report.
- If you'd like to report on sales with certain types items sold (for example: Tickets or Merchandise), highlight Sales Order Item in the left column, and drag Type from the Middle column to Include Records Where. Set this to be equal to or one of the items you need. For example: Sales Order Item\Type is one of Tickets, Merchandise will pull any sale with either tickets or merchandise purchased.
- If you'd like to report on sales to a specific program, from the left column, expand Sales Order Item, then expand Sales Order Item Ticket, then highlight Program. From the middle column, drag Program record to Include records where. Set this to be equal to or one of the programs you need in your report.
- If you'd like to report on sales to a specific program event, from the left column, beneath Sales Order Item and Sales Order Item Ticket, highlight Program Events. From the middle column, drag Program event record to Include records where. Set this to be equal to the program event you need in your report.
- Here is an example of a query that will pull all transactions this month for the General Admission Program:
- Option 1: Filter on Members Only: If you only need to view sales from members, in the left column, expand Constituent, then expand Member. Beneath Member, highlight Membership. From the middle column, drag the fields you need to report on member sales. For example: you may drag Status to Include Records Where and when the apply criteria box pops, set the criteria to be equal to Active to report on sales from active members. Here is an example of how this may look. Notice this query will pull all transactions this month for the General Admission Program from Constituents who are Active Members in our General Membership Program:
- Option 2: Add Membership Information to your Results: If you would like to output membership information like status, expiration date, or level, you can add these fields to Results fields to display. In the left column, expand Constituent, then expand Member. Beneath Member, drag any fields you would need to report on Member sales. For example, you might drag Membership Level or Status into Results Fields to Display. Here is an example of a query pulling all transactions this month for the General Admission program and displaying the constituent's membership status, membership level, and expiration date. ***Important Note: If constituents have multiple memberships, you may see duplication of sales data in your results.
- Option 3: Add a "Is Member Yes/No" field: You can also add a simple "Yes/No" field to indicate whether or not a patron is an active membership. This option works well if your constituents could have multiple memberships and you want to see sales from both non-members and members in one query.
- First, you will need to build a constituent to pull in everyone you want to consider a member. (Note: You may need to save the query you started and re-open later). Go back to Analysis > Information Library.
- Click Add an ad-hoc query
- Select the source view of Constituents and click OK.
- From the left column, expand Member. From the middle column, drag Is dropped? to Include records where. Set this to be equal to No. This will eliminate anyone who was once a member, but has since been removed from a membership.
- From the left column, beneath where you expanded Member, highlight Membership. From the middle column, drag the fields fields you need to include active members. For example, you may drag Membership Program or Status to Include records where. Here is an example that is pulling any member who is not dropped and Active in the General Membership Program:
- Next, go to the Set Save Options Tab. Name your query (for example: Current Active Members) and check the box to Create a selection (we recommend creating a dynamic selection so that this updates and changes as new members join and expire) and check the box to Show this selection in the Query Designer:
- Click Save and close, then find your Sales Order Query again and click Edit definition to edit the query.
- To add this Yes or No field, from the left column, expand the Constituent node. Beneath Constituent, highlight the Selections folder in the left column. In the middle column, drag the name of the selection you just created, to Results fields to display. This will display a Yes if the constituent is in the query (and hence is an active member) or a No if the constituent is not in the query (and is not an active member). Here's how that will look in Results fields to display:
Tip: You can also click the pencil icon to rename this field. In this example, we have renamed this "Is Active Member?"
- Next, you'll want to output details about the items sold:
- To add the number of tickets sold, highlight Sales Order Item and drag Quantity to Results fields to display
- To add the dollar amount of those items sold, drag Net Amount to Results fields to display. The Net Amount field will display how much was paid for the item (cost minus any discounts).
- (Optional) To add the description of the item that was purchased, drag Description to Results fields to display.
- (Optional) Here are some additional suggestions for program or event-related fields you may wish to include:
- Sales Order Item\Sales Order Item Ticket\Price Type Code for the price type purchased.
- Sales Order Item\Sales Order Item Ticket\Program\Name for the name of the program.
- Sales Order Item\Sales Order Item Ticket\Program\Category for program category.
- Sales Order Item\Sales Order Item Ticket\Program Events\Start Date for the date of the event.
- Sales Order Item\Sales Order Item Ticket\Program Events\Name for the name of the program event.
- In this example, we are displaying the Price Types purchased, the Quantity of tickets purchased, and the Net Amount for the tickets purchased in our query:
- Next, we will add information about refunds that were made. You can use this information to subtract refunds from sales totals if desired. For more information about excluding refunds in query, see our related solution: How to exclude refunds from a sales order query
- To add the number of tickets refunded, expand Sales Order Item and highlight Refunded item. Drag Quantity to Results fields to display.
- To add the dollar amount of those items refunded, drag Total to Results fields to display.
- In order for the refund fields to calculate properly, highlight each field in Results fields to display and click the Sigma sign and check the box to Sum.
- Once your query is complete, you can save your query on the Set Save Options tab.
- To analyze your results, you can also export your query to Excel or as a .CSV. Using Excel will allow you to use features such as Autosum, Pivot Tables, and OData to report on the data in different way. Please visit Microsoft Support for further assistance with Excel.