Deduplication in Reporting

Hi ​@Hollie C. ! This is a great question, and something I imagine others have experienced when trying to consolidate activity across different forms.

Background:

First, within Native Reporting, for most data needs, it is recommended that you use separate report sections for different forms that are not related to each other (aside from being tied to the same Participant Tier 1, like your Services Tier 2 and Event Attendance Tier 2). The setup you are describing is best practice, but it does have the drawback of not allowing for deduplication across sections because the sections don’t talk to each other.

You may have already tried this and noticed that if you pulled into a section fields from a Tier 1 and two unrelated Tier 2s (not linked to each other), you will see too many rows of data. Without a link between different Tier 2s, Apricot will show a row for every combination. This is why we recommend linking an Enrollment Tier 2 to a Services Tier 2 if you plan to report on them together. This allows Apricot to know which Enrollment the Services belong under as an example.

Solutions:

Option 1 - Native Reporting

With that said, there is a way to build a section that deduplicates Participant Profiles, but I recommend only using it for that data point. You would still need your separate sections for other details on Services and Event Attendance because the rows in this example will be unreliable. Here are the steps:

1. Create a new section and pull in at least one field from the Participant Profile (Record ID for example). Edit the column properties to rename to select the “Count Distinct” Summary option. 
2. Pull in at least one data point from the Services Tier 2, anything you need to filter on like Date of Service, etc.
3. Pull in at least one data point from the Event Attendance Tier 2, again, anything you need to filter. Ideally you would not include the Event Tier 1 in this case unless you needed it for filtering.
4. Add any of the filters you need for the time range / program / etc. you are reporting on. At least one filter for Tier 2, but you can have multiple and use Filter Logic.
5. Within the Forms list in the section, click on each of the Tier 2s and change the Required setting to “Sometimes.” This tells Apricot to include a Participant if they have either a Service Tier 2 or an Attendance Tier 2 (instead of limited to only people who have both).
6. When you publish, scroll to the bottom for the unduplicated count.

Option 2 - Impact Hub

The first option is more of a workaround within Native Reporting, but if you haven’t already explored our new reporting experience, it has much more robust options like aggregating separate datasets. There are a few steps required to get started like connecting your Apricot and creating Data Standards, but then you should be able to do more complex analysis. Here are some resources to review:

Hi Annie!

The setup you describe is basically what I have done. Unfortunately, we need the Event Tier 1 to be included because that is the form that tracks the various Workshops’ topics, which we do need to differentiate between for our funders. 

I am currently working on a redesign of my organization’s Apricot system, so I am hesitant to set up Impact Hub prior to our migration to a new set of forms. However, we still need to report on things while I am working on the redesign, which is why I am using Native Reporting. I am excited to start using Impact Hub once we are using our new forms, though!

I have my report set up to count distinct youth profiles like you suggested. I do have a quick question, though. If I bring in two unrelated Tier 2s to the same report, I always see that it says “Using Field” with options “Record ID” and “Enrollment” - Our organization is not currently using the built-in enrollment tool, but I do plan on utilizing it in the new system that I am working on. I am wondering what choosing “Enrollment” instead of “Record ID” actually does there? 

I have an idea of what it might do, based on the answer that you just provided. Please let me know if I am understanding correctly: If we don’t use the built-in program enrollment tool, and a participant got X Service (shown in a “Services Provided” Tier 2) while they were in Program 1 (shown in a “Program Enrollment” Tier 2 that isn’t linked to the system’s built-in tool) and Y Service while in Program 2, it would show 4 rows, because it would show each service with each program, but if we use the built-in tool, and switch the “Using Field” option to “Enrollment” then it would only show 2 rows, with each service correctly linked to the program they were in when they received the service. Is that correct?

Also, I have been reading through a lot of the Help Center resources on Impact Hub, and I am wondering if it’s a better practice to pull in Data Standards that are more tailored to specific reports, or if it is better to pull data in more broadly in the Data Standards, so they are almost more like the Universes in ARR? 

Thank you for your support on this! I guess for now I’ll just export to Excel and deduplicate in spreadsheets until I am able to set up our redesigned system.

Best,

Hollie

Hey ​@Hollie C. , Thank you for the additional context! I am happy to share more thoughts.

1. While I advised against pulling in the Events Tier 1 (for Option 1), that’s mainly because I am cautious about including too many forms in one report section. It’s important to be strategic when adding this level of complexity. For this purpose, I think it would be ok. Any of the forms under the root form would need to be set to “Sometimes” for the Required setting.

2. How you set up your filters for this report section is important with so many forms included; Filter Logic is a must to ensure you don’t unexpected exclude records you need to see. For the Services Tier 2 and Attendance Tier 2, if you want to include Participants that have either, then those filters will need to use OR logic, for example: (Date of Service filter OR Date of Attendance filter). Depending on how many filters you need to pull in the right records for each Tier 2, you may want multiple sets of parentheses. 

3. With point 1 above in mind, when you say you need the Event Tier 1 information like Topic, are you filtering by this to get unduplicated counts for specific sets of events that tie to specific funders? If so, then that should be fine, but again, use Filter Logic. While it makes sense to filter for only certain Event Topics for one unduplicated count, we have to be strategic to avoid limiting to only people who have Event Attendance since it’s my understanding that you want to see people with Attendance or Services. You also have to keep in mind that if you are filtering for different Topics, you will only be deduplicating within that group. Someone who also attended other topics will show up in another group as well. 

While I don’t know exactly how you are trying to set this up, here is an example of what this might look like. You can reach out to our Support team with a link to the report if you want them to double check.

Example Filter Logic:  (Date of Service filter or (Date of Attendance AND Topic))

4. Good question about the “Using Field” setting. Because we are talking about unrelated Tier 2s, Apricot is trying to figure out what connection they have. I would stick with the “Record ID” default because that’s saying keep Tier 2s associated with the Participant Profile Tier 1 they live under. I am pretty sure you are seeing “Enrollment” as an option if you have your Program Enrollment Tier 2 linked to Services. While you aren’t pulling in Enrollments data to this report, Apricot still sees that as a possible connection for the other Tier 2 forms. This shouldn’t be referring to the Enrollments feature. Your explantation of how changing to “Enrollments” is correct, but that only works if you are pulling from two Tier 2s that are linked. Enrollments linking to Services is one of the most common applications so you only see services delivered under a specific program. That doesn’t apply here since the Attendance and Services are not linked or related. This is also why we are using the “Sometimes” setting and why we can’t use this report section for any other details because the rows will be off. The “Count Distinct” option applied to Record ID for the Participant Profile will still give you the unduplicated count you want.

5. I’m glad you are considering Impact Hub. It’s a very robust tool, but I understand wanting to wait until you’ve made some configuration updates. I highly recommend our free training to get more familiar with the best practices. I am still learning the tool myself, but I do believe Data Standards are a lot like Universes in ARR. Here is a good resource to explore: 

6. I wasn’t sure if you had Results Reporting in your package, but if you do, that’s another possible solution as it’s more flexible and robust than Native Reporting. I don’t have the exact setup to recommend here, but our Support team may be able to help.