Merging Explores lets you bring data from two different datasets, such as Donations and Volunteering, into a single table for side-by-side comparison. It is not a way to combine two datasets into one unified list. Before building, it's critical to understand the left-join logic and limitations below, since misreading them is the most common cause of an inaccurate merged report.
Understanding left-join logic
Reporting Studio uses left-join logic for all merged results. This means the first Explore you select (the Primary) dictates which rows appear in the final report.
- How it works: All records from the Primary dataset will be shown. Data from the Secondary dataset will only appear if it find a match for a shared field (like User ID or Transaction Year).
- Risk: If you select Donations as your Primary and Volunteering as your Secondary, your report will only show users who donated. Users who only volunteered (but did not donate) will be excluded from the report entirely.
Key limitations to consider
To maintain report performance and data integrity, merged results have the following technical constraints:
-
5,000 Row Maximum: Merged reports are capped at 5,000 rows. Unlike standard Looks, you cannot "Export All" beyond this limit. If your dataset is larger than 5,000 rows, your data will be truncated (cut off).
- This limit also applies to the datasets before they are combined. For example, if your Primary dataset has 4,000 rows and your Secondary dataset has 7,000 rows, only the first 5,000 rows of the Secondary dataset are available to be merged. Any matching data located in rows 5,001–7,000 will be ignored, leading to missing data and inaccurate blanks.
- No cross-explore distinct counts: You cannot perform a "Unique Count" of users or causes across the two datasets. For example, you cannot calculate a single "Total Unique Participants" count that looks at both Donations and Volunteering simultaneously.
- Restricted downloads: Individual tiles created from merged queries cannot be downloaded as XLSX files. To get this data into Excel, you may need to export the individual datasets separately and combine them manually.
- Visualization & formatting: Merged queries do not support sub-total rows. Additionally, if you use the Table (Report) visualization, you cannot manually reorder columns; they will follow the order of the source queries.
- Blank vs. Zero: If the Secondary dataset does not find a match in the Primary, the cell will appear blank rather than showing a 0.
Step-by-step: How to merge results
Step 1: Set up your Primary Query
- In Explore, select the dataset that contains the broadest group of records you want to see. (e.g., If you want to see everyone who participated in any way, consider using the Participation Explore as your Primary).
- Add your dimensions (e.g., User Name, Year) and your measures (e.g., Total Donation Amount).
- Select Run.
Step 2: Initiate the Merge
- Select the Gear icon (⚙️) in the top right of the Explore window.
- Choose Merge Results.
- Select your Secondary Explore (e.g., Volunteering).
Step 3: Build the Secondary Query
- Add the same dimensions you used in the first query (e.g., User Name, Year). Shared fields are required to align the data.
- Add your new measures (e.g., Volunteer Hours).
- Select Run, then click Save.
Step 4: Review Mappings
- Ensure the "Merge Rules" correctly link your shared dimensions (e.g., Donations User ID matches Volunteering User ID).
- Select Run to view your consolidated table.
Tips for success
- Aggregate your data first: Merging Explores is safest when you're using aggregated data with a common field like Cause ID or Date, rather than transaction-level detail. This approach groups and summarizes the data first, which avoids the data integrity risks that come with merging at the transaction level.
- Date range alignment: Ensure the filters in both the Primary and Secondary queries use the exact same date range to prevent misleading comparisons.
- Participation Explore workaround, program-wide only: Starting your report with the Participation Explore as your Primary works if you need all Donors and Volunteers together program-wide, since it's built to catch all participants regardless of activity. However, the Participation Explore has no Cause or Campaign fields, so there's no way to build a combined, cause-specific participation report (for example, "donated OR volunteered for our Giving Tuesday campaign") in Reporting Studio.
- Manual export for large data: If your report is likely to exceed 5,000 rows, the safest way to ensure data integrity is to create two separate reports using just one Explore, and then combine them manually outside of the reporting tool.
- No true OR/union logic: Because of left-join, a merge can't produce one deduplicated list of "everyone who did A or B" unless your Primary Explore alone already contains everyone you need (Participation, for program-wide totals, is the one case where this works). Anyone missing from the Primary dataset is dropped, even if they matched in the Secondary.