• Replies 10 replies
  • Subscribers 333 subscribers
  • Views 83 views
  • Users 0 members are here
Related

Analysing the number of orders placed

Alison Atkinson
$organization

We want to look at the following:

The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

thanks
Alison Atkinson
London Philharmonic Orchestra

  • $organization

    We are still on v11, so when using our standard ticket history table, there is no order_no column.  We created a custom table to include the order_no which we are then able to query off of in a list using custom list criteria from the new table.

  • $organization

    Hi Alison,

    I would use T-Stats:

    In The Tickets cube - Use Ticket  - Season as the slicer 

    Use Ticket Price Types as the Category

    Use Number of orders and Number of constituents as the Measures under series and filter the order numbers to be more than three.

    You can save this to a list in Tessitura.

    Good Luck

    Susan

  • $organization in reply to Alison Atkinson

    Hi Alison

    Have you tried running it as an Extraction instead of in List Manager it may help separate out the criteria and return the correct results? We've run something similar in the past and it did work in Extractions. 

    Nicola 

  • $organization

    Thanks Susan – much appreciated.

     

    Unfortunately it’s still not getting the results though –  Set up as you suggested, but adding the ‘number of orders’ filter either doesn’t change the results at all, or returns an empty dataset.   Any thoughts on why the filtering might not work?

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 06 August 2015 19:05
    To: Alison Atkinson
    Subject: Re: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Alison,

    I would use T-Stats:

    In The Tickets cube - Use Ticket  - Season as the slicer 

    Use Ticket Price Types as the Category

    Use Number of orders and Number of constituents as the Measures under series and filter the order numbers to be more than three.

    You can save this to a list in Tessitura.

    Good Luck

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/6/2015 12:48:24 PM

    We want to look at the following:

    The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

    We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

    Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

    thanks
    Alison Atkinson
    London Philharmonic Orchestra




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    s    canned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk
  • $organization in reply to Alison Atkinson

    Hey Alison,

     

    Yes - I see that is happening for me as well.  The cube is made up of aggregate data- so pretty much everything would be over three in number of orders.  You will probably need to create a custom field.   

    Sorry -thought I had an easy answer for you,

    Susan

  • $organization

    Hi Susan

     

    Wow – thanks so much for going back and trying this.   Glad it’s not just us....

     

    We’re still searching for the best way to get to the data we need.  Currently deep in extractions!

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 11 August 2015 21:19
    To: Alison Atkinson
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hey Alison,

     

    Yes - I see that is happening for me as well.  The cube is made up of aggregate data- so pretty much everything would be over three in number of orders.  You will probably need to create a custom field.   

    Sorry -thought I had an easy answer for you,

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/10/2015 11:34:25 AM

    Thanks Susan – much appreciated.

     

    Unfortunately it’s still not getting the results though –  Set up as you suggested, but adding the ‘number of orders’ filter either doesn’t change the results at all, or returns an empty dataset.   Any thoughts on why the filtering might not work?

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 06 August 2015 19:05
    To: Alison Atkinson
    Subject: Re: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Alison,

    I would use T-Stats:

    In The Tickets cube - Use Ticket  - Season as the slicer 

    Use Ticket Price Types as the Category

    Use Number of orders and Number of constituents as the Measures under series and filter the order numbers to be more than three.

    You can save this to a list in Tessitura.

    Good Luck

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/6/2015 12:48:24 PM

    We want to look at the following:

    The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

    We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

    Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

    thanks
    Alison Atkinson
    London Philharmonic Orchestra




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    scanned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    s    canned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk
  • $organization

    I have sort of an advanced way to do this.  However you do not have to write this in SQL.  If you are not currently set up for this it will be of no help.

     

    Using MS Excel and the Power Query or Power Piviot plugins:

     

    Use the Ticket_Facts Table and the Performance and Seasons Tables that are on your live T-Stats Server as a data set.  (These are not the cubes.) With this data one should be able to filter just tickets for the needed seasons and Power Piviot or Power Query could be used to aggregate and filter by customer.

     

    This may not help your specific situation, however some organizations are starting to play with approaches like this.

     

    --Tom

    …

    718.724.8135

    tbrown@BAM.org

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Alison Atkinson
    Sent: Wednesday, August 12, 2015 12:45 PM
    To: Thomas Brown <tbrown@bam.org>
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Susan

     

    Wow – thanks so much for going back and trying this.   Glad it’s not just us....

     

    We’re still searching for the best way to get to the data we need.  Currently deep in extractions!

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 11 August 2015 21:19
    To: Alison Atkinson
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hey Alison,

     

    Yes - I see that is happening for me as well.  The cube is made up of aggregate data- so pretty much everything would be over three in number of orders.  You will probably need to create a custom field.   

    Sorry -thought I had an easy answer for you,

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/10/2015 11:34:25 AM

    Thanks Susan – much appreciated.

     

    Unfortunately it’s still not getting the results though –  Set up as you suggested, but adding the ‘number of orders’ filter either doesn’t change the results at all, or returns an empty dataset.   Any thoughts on why the filtering might not work?

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 06 August 2015 19:05
    To: Alison Atkinson
    Subject: Re: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Alison,

    I would use T-Stats:

    In The Tickets cube - Use Ticket  - Season as the slicer 

    Use Ticket Price Types as the Category

    Use Number of orders and Number of constituents as the Measures under series and filter the order numbers to be more than three.

    You can save this to a list in Tessitura.

    Good Luck

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/6/2015 12:48:24 PM

    We want to look at the following:

    The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

    We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

    Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

    thanks
    Alison Atkinson
    London Philharmonic Orchestra




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    scanned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    scanned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization

    Hi Tom

     

    Many  thanks for this suggestion.   It sounds like a really good way of having more control over the data – although not something we’re currently able to do as we’re in a consortium environment.    But definitely food for thought. 

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Tom Brown
    Sent: 12 August 2015 19:15
    To: Alison Atkinson
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    I have sort of an advanced way to do this.  However you do not have to write this in SQL.  If you are not currently set up for this it will be of no help.

     

    Using MS Excel and the Power Query or Power Piviot plugins:

     

    Use the Ticket_Facts Table and the Performance and Seasons Tables that are on your live T-Stats Server as a data set.  (These are not the cubes.) With this data one should be able to filter just tickets for the needed seasons and Power Piviot or Power Query could be used to aggregate and filter by customer.

     

    This may not help your specific situation, however some organizations are starting to play with approaches like this.

     

    --Tom

    …

    718.724.8135

    tbrown@BAM.org

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Alison Atkinson
    Sent: Wednesday, August 12, 2015 12:45 PM
    To: Thomas Brown <tbrown@bam.org>
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Susan

     

    Wow – thanks so much for going back and trying this.   Glad it’s not just us....

     

    We’re still searching for the best way to get to the data we need.  Currently deep in extractions!

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 11 August 2015 21:19
    To: Alison Atkinson
    Subject: RE: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hey Alison,

     

    Yes - I see that is happening for me as well.  The cube is made up of aggregate data- so pretty much everything would be over three in number of orders.  You will probably need to create a custom field.   

    Sorry -thought I had an easy answer for you,

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/10/2015 11:34:25 AM

    Thanks Susan – much appreciated.

     

    Unfortunately it’s still not getting the results though –  Set up as you suggested, but adding the ‘number of orders’ filter either doesn’t change the results at all, or returns an empty dataset.   Any thoughts on why the filtering might not work?

     

    Best wishes

    Alison

     

    From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Susan Farma
    Sent: 06 August 2015 19:05
    To: Alison Atkinson
    Subject: Re: [Tessitura Marketing Forum] Analysing the number of orders placed

     

    Hi Alison,

    I would use T-Stats:

    In The Tickets cube - Use Ticket  - Season as the slicer 

    Use Ticket Price Types as the Category

    Use Number of orders and Number of constituents as the Measures under series and filter the order numbers to be more than three.

    You can save this to a list in Tessitura.

    Good Luck

    Susan

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/6/2015 12:48:24 PM

    We want to look at the following:

    The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

    We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

    Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

    thanks
    Alison Atkinson
    London Philharmonic Orchestra




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    scanned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    scanned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    s    canned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk
  • $organization

    Here's an example of how to do this using the manual SQL edit option in List builder.  This example builds a list of those customers having 2 or more orders when looking at  fiscal year 2015 and 2016 for tickets sold under the TR_PRICE_TYPE.ID value of 18  (Sub Extra here). You may need to further filter the seasons you want to look at by adding a Season ID  to the below query instead of what I use below with the Fiscal Years as a FYear value should be populated for each TR_SEASON.ID.

    You can paste this directly in List Builder using the "manual Edit" option...just supply your own Price Type value and change the seasons you want to include in your search.

    HTH

    -doug

     

    select distinct o.customer_no

    from t_sub_lineitem li

    join t_lineitem l on li.li_seq_no = l.li_seq_no and l.primary_ind = 'Y'

    join t_perf p on li.perf_no = p.perf_no 

    join t_order o on li.order_no = o.order_no

    join TR_PRICE_TYPE pt on li.price_type = pt.id

    JOIN TR_SEASON s on p.season = s.id

    JOIN T_CUSTOMER c on o.customer_no = c.customer_no

    where c.inactive = 1 

    and li.sli_status in (2,3,6,12) --select * from tr_sli_status

    and s.fyear in (2015,2016)

    and o.customer_no > 0

    and pt.id = 18 --Your Price Type Here

    group by o.customer_no

    having count(distinct o.order_no) >= 3

  • $organization
    Wow -thanks Doug!  massively appreciated.  looking forward to trying that.

    Best wishes
    Alison 

    Sent from my iPad

    On 19 Aug 2015, at 03:40, Doug Jones <bounce-dougjones6811@tessituranetwork.com> wrote:

    Here's an example of how to do this using the manual SQL edit option in List builder.  This example builds a list of those customers having 2 or more orders when looking at  fiscal year 2015 and 2016 for tickets sold under the TR_PRICE_TYPE.ID value of 18  (Sub Extra here). You may need to further filter the seasons you want to look at by adding a Season ID  to the below query instead of what I use below with the Fiscal Years as a FYear value should be populated for each TR_SEASON.ID.

    You can paste this directly in List Builder using the "manual Edit" option...just supply your own Price Type value and change the seasons you want to include in your search.

    HTH

    -doug

     

    selectdistinct o.customer_no

    from t_sub_lineitem li

    join t_lineitem l on li.li_seq_no = l.li_seq_no and l.primary_ind = 'Y'

    join t_perf p on li.perf_no = p.perf_no 

    join t_order o on li.order_no = o.order_no

    join TR_PRICE_TYPE pt on li.price_type = pt.id

    JOIN TR_SEASON s on p.season = s.id

    JOIN T_CUSTOMER c on o.customer_no = c.customer_no

    where c.inactive = 1 

    and li.sli_status in (2,3,6,12) --select * from tr_sli_status

    and s.fyear in (2015,2016)

    and o.customer_no > 0

    and pt.id = 18 --Your Price Type Here

    group by o.customer_no

    having count(distinct o.order_no) >= 3

    From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com>
    Sent: 8/6/2015 12:48:24 PM

    We want to look at the following:

    The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.     

    We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder.  Or perhaps two of the criteria are referencing different tables?  

    Has anyone else done any analysis by orders rather than performances booked?    Any tips / help / solutions greatly appreciated.

    thanks
    Alison Atkinson
    London Philharmonic Orchestra




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
    s    canned by Ignite Email Filtering Service - Ignite's comprehensive cloud based email content security solution. For more information please visit www.ignite.co.uk