• Replies 1 reply
  • Subscribers 415 subscribers
  • Views 71 views
  • Users 0 members are here
Related

Voucher upon visiting through NSCAN

Mina Kobayashi
$organization

We're creating a new donor welcome email series, and we're giving those people a concessions voucher when they arrive to the theatre for a show as a "surprise and delight" thank you. We're using the messaging feature of NSCAN to prompt our ushers to gift the new donor a voucher through CSIs. However, we're running into some issues such as:  1. How to mass import CSIs for a large group of constituents 2. How to track if they've received a voucher through NSCAN/CSI and how to use that tracking to suppress them in an extraction

Has anyone done/is doing a similar project? Or does anyone have any ideas on how we might be able to do this better? 

Thank you!

Parents
  • $organization

    HI Mina

    I have just written a piece of sql that adds a CSI daily for customers attending today's performances who are using the free companion ticket and so need to show an Access Members card.

    declare @message_dt datetime,
    @NEXT_CUSTOM_ID INT,
    @loop int,
    @maxloop int

    declare @T_CUST_ACTIVITY as table (
            id int primary key,
            activity_type int,
            customer_no int,
            contact_type int,
            notes varchar(4000),
            urg_ind char(1),
            issue_dt datetime,
            perf_no int
    )

    select @message_dt=getdate()

    insert into @T_CUST_ACTIVITY(id, activity_type,customer_no,contact_type,notes,urg_ind,issue_dt,perf_no)
    select row_number() over (order by x.perf_no, x.customer_no ),
    x.*
    from (
             select distinct
                         activity_type=66,
                         customer_no=o.customer_no,
                         contact_type=11,
                         notes='Personal Assistant Ticket issued - Check Access Card',
                         urg_ind='N',
                         issue_dt=getdate(),
                         perf_no=sli.perf_no
             from t_perf p with (NOLOCK)
             join T_SUB_LINEITEM sli with (NOLOCK) on sli.perf_no=p.perf_no and sli.price_type=657 and sli.sli_status in (12)
            join T_ORDER o with (NOLOCK) on sli.order_no=o.order_no
            where convert(varchar(10),p.perf_dt,102)=convert(varchar(10),@message_dt,102)
            and not exists (
                                    select 1
                                    from T_CUST_ACTIVITY ca with (NOLOCK)
                                    where o.customer_no=ca.customer_no
                                    and ca.perf_no=sli.perf_no
                                    and ca.activity_type=66
                                    and ca.contact_type=11
                            )
    ) as x

    select @maxloop=max(id),
             @loop=1
    from @T_CUST_ACTIVITY

    while @loop<=@maxloop
    begin

       EXEC @NEXT_CUSTOM_ID = DBO.AP_GET_NEXTID_FUNCTION 'AC'

       insert into T_CUST_ACTIVITY(activity_no, activity_type,customer_no,contact_type,notes,urg_ind,issue_dt,perf_no)
       select activity_no=@NEXT_CUSTOM_ID,
                  activity_type,
                  customer_no,
                  contact_type,
                  notes,
                  urg_ind,
                  issue_dt,
                  perf_no
       from @T_CUST_ACTIVITY
       where id=@loop

       select @loop=@loop+1

    end

    The CSI tracking report will show you which people still have the message open, which you should be able use to save as a list of customers. 
    However in order for that to work you need to change my sql above so that you add created_by with a valid username to the insert statement
    (my sql creates them as dbo which is not tracked by the report as it filters by user name)

    Mark

Reply
  • $organization

    HI Mina

    I have just written a piece of sql that adds a CSI daily for customers attending today's performances who are using the free companion ticket and so need to show an Access Members card.

    declare @message_dt datetime,
    @NEXT_CUSTOM_ID INT,
    @loop int,
    @maxloop int

    declare @T_CUST_ACTIVITY as table (
            id int primary key,
            activity_type int,
            customer_no int,
            contact_type int,
            notes varchar(4000),
            urg_ind char(1),
            issue_dt datetime,
            perf_no int
    )

    select @message_dt=getdate()

    insert into @T_CUST_ACTIVITY(id, activity_type,customer_no,contact_type,notes,urg_ind,issue_dt,perf_no)
    select row_number() over (order by x.perf_no, x.customer_no ),
    x.*
    from (
             select distinct
                         activity_type=66,
                         customer_no=o.customer_no,
                         contact_type=11,
                         notes='Personal Assistant Ticket issued - Check Access Card',
                         urg_ind='N',
                         issue_dt=getdate(),
                         perf_no=sli.perf_no
             from t_perf p with (NOLOCK)
             join T_SUB_LINEITEM sli with (NOLOCK) on sli.perf_no=p.perf_no and sli.price_type=657 and sli.sli_status in (12)
            join T_ORDER o with (NOLOCK) on sli.order_no=o.order_no
            where convert(varchar(10),p.perf_dt,102)=convert(varchar(10),@message_dt,102)
            and not exists (
                                    select 1
                                    from T_CUST_ACTIVITY ca with (NOLOCK)
                                    where o.customer_no=ca.customer_no
                                    and ca.perf_no=sli.perf_no
                                    and ca.activity_type=66
                                    and ca.contact_type=11
                            )
    ) as x

    select @maxloop=max(id),
             @loop=1
    from @T_CUST_ACTIVITY

    while @loop<=@maxloop
    begin

       EXEC @NEXT_CUSTOM_ID = DBO.AP_GET_NEXTID_FUNCTION 'AC'

       insert into T_CUST_ACTIVITY(activity_no, activity_type,customer_no,contact_type,notes,urg_ind,issue_dt,perf_no)
       select activity_no=@NEXT_CUSTOM_ID,
                  activity_type,
                  customer_no,
                  contact_type,
                  notes,
                  urg_ind,
                  issue_dt,
                  perf_no
       from @T_CUST_ACTIVITY
       where id=@loop

       select @loop=@loop+1

    end

    The CSI tracking report will show you which people still have the message open, which you should be able use to save as a list of customers. 
    However in order for that to work you need to change my sql above so that you add created_by with a valid username to the insert statement
    (my sql creates them as dbo which is not tracked by the report as it filters by user name)

    Mark

Children
No Data