Results 1 to 4 of 4

Thread: analyzing ticket purchaser correlations

  1. #1

    analyzing ticket purchaser correlations



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a spreadsheet containing the names and email addresses of everyone who has ever bought a ticket to one of our events (5 years worth of concerts). I want to know if there is a way to calculate how many people who bought tickets to a show also bought tickets to each of the other shows. I am trying to determine which concerts have the most overlap in audience. Are there any tricks in excel for doing this? (column A= event name, column B= user's email address)

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    This is called Basket Analysis. I answered a question about it on this forum some time back, at http://www.excelguru.ca/forums/showt...asket+analysis

    That thread has a file attached that you might want to check out, although it's pretty complicated stuff, and very hard to do with formulas alone as I have done in that file. How many different concerts do you have in total? If the number is large, you'll probably need some dedicated software to do this kind of analysis for you. Such as SQL Server: see http://msdn.microsoft.com/en-us/library/dn282369.aspx where the article discusses an addin for SQL Server that you can use in Excel.
    Last edited by JeffreyWeir; 2014-08-25 at 12:17 PM.

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I found a sample file I put together demonstrating how to do something like this. But it's limited to finding the two most common items. Basket analysis 8.xlsb

    You could use VBA to do more, but note that this is a very computationally intensive thing indeed. Excel will quickly run out of resources if the lists involved are long.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Here's another link:
    http://sqlbijourney.wordpress.com/20...amics-gp-data/

    To do anything on big data sets it appears you are going to have to download some serious addins. These guys have a trial cloud offering: http://www.predixionsoftware.com/Tec...sight-Features

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •