“How much do members spend in the gift shop?” sounds like a five-minute query. In Altru it usually isn’t, and the reason has less to do with query design than with what happened at the register. A merchandise sale can only be tied to a member if the transaction was tied to a constituent in the first place. Anonymous walk-up sales, which are most gift shop sales at most sites, have no member to report on.
So before building anything, it helps to know which question you can actually answer with the data you have, and which one needs a change at the counter first.
Start with how the sale was recorded
Pull up a handful of recent merchandise transactions from daily sales and look at each one. You’ll typically see one of three patterns:
- Linked to a constituent. Staff looked up the member (or scanned their card) before ringing up the sale, often to apply a member discount. These are reportable by member.
- Anonymous, but with a member discount applied. The cashier gave the discount without attaching the person. You can count these as “member” sales, but you can’t say which member.
- Anonymous with no discount. Could be a member who didn’t mention it, could be anyone. These can’t be attributed.
The mix between those three determines what your report will mean. If only a small share of merchandise sales are linked to a constituent, a “member merchandise sales” total will understate real member spending, and it’s worth saying so wherever the number gets presented.
Building the query
The general approach is to start from the sales side, narrow to merchandise, and then narrow to people who were members. Field and view names vary a bit by Altru version and by how your items are set up, so treat this as the outline rather than an exact recipe.
1. Pick a sales or revenue-based query view
You want a view where each row is a sale (or a line item on a sale) and where the purchasing constituent is available as a related field. A sales order or revenue view usually fits. If you aren’t sure which view exposes both the merchandise item and the constituent, build a quick test query with a single known transaction and check that both show up correctly before going further.
2. Filter to merchandise
Filter on the item type or on the specific merchandise items or departments you care about. If your gift shop items are organized into categories, filtering on category is easier to maintain than listing individual items, since new SKUs get picked up automatically.
3. Filter to members
This is the step people get wrong. There are two reasonable definitions, and they give different answers:
- Members as of the sale date. The constituent had an active membership on the day they bought the item. This is the accurate version for “do members shop more?” questions, but it requires comparing the sale date to membership start and expiration dates.
- Members today. The constituent currently holds an active membership, using a current-member selection or smart field. Easier to build, but it counts purchases made before someone joined and excludes people whose membership has since lapsed.
If the date comparison is awkward to express in the query designer, a workable compromise is to run the query for a fixed period (one quarter, say) and filter to constituents whose membership was active for that whole window. It’s not perfect, but it’s consistent from quarter to quarter.
4. Choose output fields
Useful columns: constituent name and lookup ID, membership level, sale date, item, quantity, amount, and any discount applied. Including membership level lets you see whether higher tiers actually spend more in the shop, which is often the real question behind the request.
5. Add a discount-based query for the anonymous sales
Build a second query on the same sales view filtered to merchandise transactions where your member discount was applied, with no constituent filter. This gives you a count and total of member-discounted sales, including the ones nobody attached to a person. Reported side by side with the first query, it gives a more honest picture than either number alone.
Check the numbers before you share them
- Compare your merchandise total (all sales, no member filter) against a standard sales report for the same dates. If they don’t match, the item filter is missing something or the view is counting refunds differently.
- Look for duplicate constituent records among buyers. A member who also has a second, non-member record will split their purchases between the two.
- Decide how to treat returns and voided sales, and note that choice on the report.
Getting more sales attached to members
The query can only be as good as the register data. A few habits improve it over time:
- Make “look up the member, then apply the discount” the only way the discount gets applied, rather than a manual price override.
- Ask at the register whether the guest is a member, even if they don’t bring it up.
- Spot-check a week of shop transactions each month and share the linked-versus-anonymous ratio with shop staff. People tend to follow a process more closely once they know it’s measured.
Keeping the report current
Once the queries return what you expect, you can save them and rerun them each month, or export to a spreadsheet for charts. If someone outside Altru needs the numbers regularly, our Data Bridge can sync an Altru query to Google Sheets or a marketing tool, so the shop manager or membership team works from current data without a manual export each time. The query logic itself still lives in Altru.
Quick answers
Can I report on member merchandise sales without linking sales to constituents?
Only partially. A member discount filter shows how many sales got the discount, but not who bought, so you can’t break it down by membership level or individual.
Why does my query show fewer member purchases than the shop staff expect?
Usually because many purchases weren’t attached to a constituent at the register. Check the linked-versus-anonymous mix before assuming the query is broken.
