• Replies 4 replies
  • Subscribers 962 subscribers
  • Views 78 views
  • Users 0 members are here
Related

Ticket History for Individuals Affiliated with Businesses

Michael Dorsey
$organization

Hello everyone!

Does anyone have a formula for getting the ticket history of only people who are affiliated with businesses? I feel like this is one of those things that is probably pretty easy and I'm overthinking it -- usually turns out to be the case most times. When I try using list manager, I get what I feel is way too low a number. I've tried w/ SQL but my query must not be right because I'm getting a lot of repeated rows.

Our development team wants to know who has purchased tickets of their own accord that are affiliated with corporate sponsors or businesses that may want to consider sponsorship in the future.

Any insight is appreciated, as always!

Thanks,

Michael

Parents
  • $organization

    Hey Michael,

    I get asked these questions and I like to answer them by translating the statement into data objects.  In fact teaching people to ask questions in this way can help them to understand the underlying question. It really helps when you keep asking "Contribution/transaction/posting date?" to every philanthropy question. (I wonder why I never get asked to staff drinks?)

    Original ask: Our development team wants to know who has purchased tickets of their own accord that are affiliated with corporate sponsors or businesses that may want to consider sponsorship in the future.

    Translation: People with relationship affiliations of Employee ... with Corporate Constituencies ... that have been an initiator or owner of a ticket order.

    INPUT (List Criteria): 

    • Businesses  
    • Only people who are affiliated 
    • Purchased tickets of their own accord

    OUTPUT (Output set criteria/Analytics rows etc:

    • Ticket History fields - NB/ lots of repeated rows returned

    So for the list criteria

    • To get Businesses: Constituent Type - Corporation (too broad? Constituencies/Attributes?)
    • To get Only people affiniated: the relationship tab in lists will - use relationship type Employee (any others?)
    • To get Purchased of their oen free will: Ticket History Role: things with Initiator or Owner

    Output is as detailed as you want it.

    To be clear the List will never have repeated rows but your SQL will if it's doing the IN and OUT at the same time. You could get each persons ticket history into a single string by using XML stuff expression ... but I'd save that for when you need to display that history to someone in a WordFly email.

  • $organization in reply to Heath Wilder

    Heath, you've done it again! I figured there was some way to do it in List Manager, but I often find myself struggling with the right "Advanced Relationship Options" settings. Thanks for taking the time to explain it clearly; it makes perfect sense when you break it down this way. I'll also be employing your strategy of translating their ask into data objects. This could save a lot of back-and-forth and the inevitable headaches that come with it.

    Thanks again, again!

    Michael

Reply
  • $organization in reply to Heath Wilder

    Heath, you've done it again! I figured there was some way to do it in List Manager, but I often find myself struggling with the right "Advanced Relationship Options" settings. Thanks for taking the time to explain it clearly; it makes perfect sense when you break it down this way. I'll also be employing your strategy of translating their ask into data objects. This could save a lot of back-and-forth and the inevitable headaches that come with it.

    Thanks again, again!

    Michael

Children