Merging Explores allows you to bring data from two different datasets, such as Donations and Volunteering, into a single table. While this is a powerful tool for side-by-side comparison, it functions differently from a standard report.
Before building, it is critical to understand the "left-join" logic and the technical limitations of this feature to ensure your data is accurate.
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: If you need to see both Donors and Volunteers without losing data, try starting your report with the Participation Explore. This Explore is designed to catch all participants, which you can then supplement with specific Donation and Volunteering metrics.
- 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.