• Replies 4 replies
  • Subscribers 418 subscribers
  • Views 184 views
  • Users 0 members are here
Related

Custom list elements for order line/subline items for pre-show comms

Krista Tanuwibawa
$organization

Hi everyone, 

I'm looking into refining our process for automated pre-show emails, particularly in the way our lists are pulled. 

We've been using dynamic lists using the Ticket History Performance (relative date) as the only criteria. Our Wordfly email template then pulls the dynamic list when it's scheduled to run, and sends the pre-show email to that list of constituents. 

However, we  noticed some discrepancies between all ticket purchasers and the actual email recipients at the time of sending. This is because Ticket History Performance isn't picking up constituents who purchased tickets on the same day the list is run (I believe it only populates up to the last day). 

Tessitura have suggested that we look at building a custom list element that looks at order line or subline items instead, as this doesn't rely on the overnight job. 

Has anyone done this successfully already, or know how one might go about doing this? 

Any help would be appreciated!  

  • $organization

    Following to do the same thing. I've been experimenting with a number of things and haven't hit the nail on the head yet.

  • $organization

    So this should be doable, but you might have to write a pretty complicated View in order to get all the data that Ticket history provides. The great thing about ticket history is it is populated for the owners, initiators and recipients, so you don't have to do anything special to include all of them. If you or your database admin created a view using the following query, it should provide you with a performance date record associated to all 3 roles anyone could have in an order. It's not the most elegant solution and I bet there are more experienced SQL coders out there who could create a more efficient query. But if you create a view using this, you can setup a custom list element that looks at paid subline items instead of just ticket history.

    SELECT distinct customer_no, perf_no, perf_dt
    FROM
    (SELECT DISTINCT a.customer_no, f.perf_no, f.perf_dt
    FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
    INNER JOIN
    (SELECT o.customer_no, s.perf_no, p.perf_dt
    FROM T_SUB_LINEITEM s join T_ORDER o on s.order_no = o.order_no
    join T_PERF p on s.perf_no = p.perf_no
    WHERE s.sli_status in (3,12)) AS f
    ON f.customer_no = a.expanded_customer_no
    WHERE 1 = 1
    UNION
    SELECT DISTINCT a.customer_no, f.perf_no, f.perf_dt
    FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
    INNER JOIN
    (SELECT o.initiator_no, s.perf_no, p.perf_dt
    FROM T_SUB_LINEITEM s join T_ORDER o on s.order_no = o.order_no
    join T_PERF p on s.perf_no = p.perf_no
    WHERE s.sli_status in (3,12)) AS f
    ON f.initiator_no = a.expanded_customer_no
    WHERE 1 = 1
    UNION
    SELECT DISTINCT a.customer_no, f.perf_no, f.perf_dt
    FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
    INNER JOIN
    (SELECT s.recipient_no, s.perf_no, p.perf_dt
    FROM T_SUB_LINEITEM s 
    join T_PERF p on s.perf_no = p.perf_no
    WHERE s.sli_status in (3,12)) AS f
    ON f.recipient_no = a.expanded_customer_no
    WHERE 1 = 1) as a
    WHERE customer_no > 0

  • $organization

    I recently deployed a set of custom criterias that return up-to-date live paid seats data in Ticket History format for use in either List or Extraction Manager. The data draws from live orders combined with Ticket History to provide a full set of ticket sales data via a custom view. Please let me know if this is will be useful and happy to discuss in more detail if required. Thanks!

  • $organization in reply to Andrew Chew

    I would love to hear more about how you implemented this if you have some time!