Constituent booking multiple sets of tickets for a show

Sophie –

Try this and see what you get:

Tickets, Production Season = to the show you’re talking about

And then depending on where your ticketing info lives…

# unique performances per household (or just # of unique perfs) greater than or equal to 2

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Sophie Andrews
Sent: Friday, June 20, 2014 12:12 PM
To: Chris Long
Subject: [Tessitura Marketing Forum] Constituent booking multiple sets of tickets for a show

Hi All, 

We have anecdotal comments from our box office team that people are booking to see more than one performance of a show we are about to open. 

We are curious to see how pronounced this booking pattern is and wanted to find out how many people have booked to see more than one performance. 

Has anyone done this and have any advice on the best way to extract this information? 

Thanks

Sophie

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!

Hi, Sophie:

If you have access to SQL Server Management Studio, add two views, a query element parameter, a query element, an output set, and a list, and you’re good to go. After the initial setup, you can create output sets for any production you want.

-----------------------

--STEP ONE: FIRST VIEW—add in SQL SERVER MANAGEMENT STUDIO

-----------------------

USE [impresario]

GO

/****** Object:  View [dbo].[LV_PROD_SEASON_PERFS_BY_CUSTOMER]    Script Date: 06/20/2014 13:56:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE view [dbo].[LV_PROD_SEASON_PERFS_BY_CUSTOMER]

AS

select distinct a.customer_no,

      b.season,

      b.prod_season_no as 'prod_season',

      count(distinct a.perf_no) as 'perf_count'

from VX_PERF_SEAT a

join VS_PERF b on b.perf_no = a.perf_no

where ISNULL(a.customer_no,0) <> 0

group by a.customer_no, b.season, b.prod_season_no

GO

-----------------------

--STEP TWO: SECOND VIEW—add in SQL SERVER MANAGEMENT STUDIO

-----------------------

USE [impresario]

GO

/****** Object:  View [dbo].[LVS_PROD_SEASON_WITH_DESCRIPTION]    Script Date: 06/20/2014 14:51:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE View [dbo].[LVS_PROD_SEASON_WITH_DESCRIPTION]

AS

SELECT a.*, c.description, b.edit_ind

FROM dbo.T_PROD_SEASON a

      JOIN dbo.VRS_SEASON b ON a.season = b.id

      JOIN dbo.T_INVENTORY c ON c.inv_no = a.prod_season_no

GO

-----------------------

--STEP THREE: ASSIGN RIGHTS—in SQL SERVER MANAGEMENT STUDIO

-----------------------

grant delete, insert, references, select on [dbo].[LV_PROD_SEASON_PERFS_BY_CUSTOMER] to impusers

grant delete, insert, references, select on [dbo].[LVS_PROD_SEASON_WITH_DESCRIPTION] to impusers

-----------------------

STEP FOUR: Set up a row in TR_QUERY_ELEMENT_PARAMETER:

id:                           Automatically generated; make a note of the number

Description:        Production Season

Data Type:          Number

End of Day:         [Leave unchecked]

Multi Select:       [Leave unchecked]

Ref Tbl:                 LVS_PROD_SEASON_WITH_DESCRIPTION

Ref Id:                   prod_season_no

Ref Desc:             description

Ref Where:         [Leave blank]

Ref Sort:              season desc, description

--------------------

STEP FIVE: Set up a row in TR_QUERY_ELEMENT:

Id:                           Automatically generated

Description:        Prod Season-Perfs by Customer

Category:            Ticketing [or what you want]

Data Select:        !.perf_count

Data From:          (select a.customer_no, a.prod_season, a.perf_count from LV_PROD_SEASON_PERFS_BY_CUSTOMER a)

Data Where:      a.prod_season = <<p##>> [replace ## with the id from STEP FOUR]

Control Group:  [Use if it is your business practice to use]

Single Row:         [Check]

STEP SIX: Add an output set [Tools > Output Set Builder]

Select the Prod Season-Perfs by Customer that you set up in STEP FIVE. Select your production season (the production). These should be grouped by season, if you have more than one production season with the same name. Leave Include Primary Household unchecked.

STEP SEVEN: Make a list of everyone who had tickets for a particular production.

STEP EIGHT: Run the Execute an Output Set report using your new output set and new list.

This worked for me, anyway. The number returned will be how many performances of a particular production a particular customer purchased.

Good luck!

Lucie

______________________________
Lucie Spieler
IT Development and Training Manager


8390 NW 25th Street
Miami, FL 33122

Direct Line: 305.403.3291
Switchboard: 305.854.1643 x. 1521
Box Office: 800.741.1010
To Give a Gift: 305.403.3320
www.FGO.org

New Subscriptions On Sale Now!

2014-2015 | 74TH SEASON
MADAMA BUTTERFLY | COSÌ FAN TUTTE | THE PEARL FISHERS | THE CONSUL

Thank you Christine. That worked perfectly.

Sophie –

Try this and see what you get:

Tickets, Production Season = to the show you’re talking about

And then depending on where your ticketing info lives…

# unique performances per household (or just # of unique perfs) greater than or equal to 2

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Sophie Andrews
Sent: Friday, June 20, 2014 12:12 PM
To: Chris Long
Subject: [Tessitura Marketing Forum] Constituent booking multiple sets of tickets for a show

Hi All, 

We have anecdotal comments from our box office team that people are booking to see more than one performance of a show we are about to open. 

We are curious to see how pronounced this booking pattern is and wanted to find out how many people have booked to see more than one performance. 

Has anyone done this and have any advice on the best way to extract this information? 

Thanks

Sophie

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!

Chris Long

Manager, Electronic Communications

502.566.5196 p | 502.210.3299 m

The Kentucky Center for the Performing Arts

501 West Main Street, Louisville, KY  40202

www.kentuckycenter.org

Facebook | YouTube | Flicker | Twitter | Blog

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!