Lists of People Who An Individual May Have Met... at an Event

Tom Klimchak

I've been asked to provide lists of people who may know someone based on attendance at events.  Example:


Robert H. attended three VIP events last year.  Who is a list of all the people he may have met?


The easiest way to do that would probably be to find the 3 events and simply list all the attendees.  I can do that with a simple constituent query... but is there any better/automated way to do that?  Event attendees don't show up on the relationship tree...  


I would really like a way to do that where I didn't have to find all the individual events first...  any ideas?

Alan French

Hi Tom,


This might be a bit of a hairbrained way to do it but I think this could be achieved using 2 event queries.


Query 1: open the Participants node and add a criterion of "Participant Has Attended equals Yes", then expand the Constituent node that's within the Participant Node and add constituent ID as another criterion - enter the constituent's ID or set it to <ask> if you're going to be running it regularly for different people. This query will pick up all the events that the person you're looking at has attended, but you won't be able to get a list of all the other participants here because you've restricted it by constituent ID.


Query 2: create another event query, go to Tools > Query Options > Record Processing > Select From Query and choose the first query that you just created. Then you can open the Participant node and add a criterion of "Participant Has Attended equals Yes" to narrow the list of participants to only those who attended one of the events. Unfortunately you can't use an event query with a participant export, so you might have to export a list of names directly from query (which of course none of us ever do because query is a grouping tool, not a reporting tool )

 

Tom Klimchak

Alan,


Ooo!  Niiiiice!  That works pretty well!  Still getting names duplicated in Query 2 even if I suppress dupes, but guessing that's because some of these people attended multiple events with the same initial person.  A little Excel cleanup can fix that.  


I don't use that "Select from query" option nearly enough.. Thanks!

 

Alan French:

Hi Tom,


This might be a bit of a hairbrained way to do it but I think this could be achieved using 2 event queries.


Query 1: open the Participants node and add a criterion of "Participant Has Attended equals Yes", then expand the Constituent node that's within the Participant Node and add constituent ID as another criterion - enter the constituent's ID or set it to <ask> if you're going to be running it regularly for different people. This query will pick up all the events that the person you're looking at has attended, but you won't be able to get a list of all the other participants here because you've restricted it by constituent ID.


Query 2: create another event query, go to Tools > Query Options > Record Processing > Select From Query and choose the first query that you just created. Then you can open the Participant node and add a criterion of "Participant Has Attended equals Yes" to narrow the list of participants to only those who attended one of the events. Unfortunately you can't use an event query with a participant export, so you might have to export a list of names directly from query (which of course none of us ever do because query is a grouping tool, not a reporting tool )

 

 

Ryan Hyde

This is a great solution, and one I might use for some stuff eventually.


Just a thought:


You're getting dupes (even when suppressing duplicate rows) because those people have attended more than one of the same events as the person you're reporting on. Since each instance of the dupe's name in the final output represents a different occasion on which s/he may have met the person you're reporting on, it might be worthwhile to add a "=COUNTIF" column in your spreadsheet and count the number of instances each name shows up in the list. Then copy and paste-special this column onto itself as values only so that the count is there but the formula is gone, and THEN dedupe your list. Now you've got each name and how many opportunities your reported person had to meet that person. 


If you've got more data, may as well use it. Of course you'll want to spot check a few of the records to make sure that the dupes really are there for the reason we're assuming they're there.

Tom Klimchak

Hmm, yeah, but instead of CountIf I might just use Excel Subtotals for that.  Could sort by name and count each time they show up.  The people with higher counts are obviously more likely to know the individual in question... more chances to meet.  Works for me!


I haven't given my VP this solution yet.  Sometime a solution that is simple for one or two people doesn't scale too well.  My Prospect Research department ends up getting buried in things a lot that way.  I'm afraid of how long this might take to create for a large gala event with hundreds of attendees.  

Christine Cooke 2

Tom Klimchak:

Alan,


Ooo!  Niiiiice!  That works pretty well!  Still getting names duplicated in Query 2 even if I suppress dupes, but guessing that's because some of these people attended multiple events with the same initial person.  A little Excel cleanup can fix that.  


I don't use that "Select from query" option nearly enough.. Thanks!

 

Alan French:

Hi Tom,


This might be a bit of a hairbrained way to do it but I think this could be achieved using 2 event queries.


Query 1: open the Participants node and add a criterion of "Participant Has Attended equals Yes", then expand the Constituent node that's within the Participant Node and add constituent ID as another criterion - enter the constituent's ID or set it to <ask> if you're going to be running it regularly for different people. This query will pick up all the events that the person you're looking at has attended, but you won't be able to get a list of all the other participants here because you've restricted it by constituent ID.


Query 2: create another event query, go to Tools > Query Options > Record Processing > Select From Query and choose the first query that you just created. Then you can open the Participant node and add a criterion of "Participant Has Attended equals Yes" to narrow the list of participants to only those who attended one of the events. Unfortunately you can't use an event query with a participant export, so you might have to export a list of names directly from query (which of course none of us ever do because query is a grouping tool, not a reporting tool )

 

 

 

Take the merged query results and push them through Export, that way you can have one line per person and one column per event.  Query is a grouping tool not a reporting tool. ;-)