Conversion Rate in Analytics

Hi Jenny,

Might it work to look at daily figures in the Finance cube for counts of constituents with both a ticket and membership purchase? For example, with Days in Transaction Date on Rows, having a Value for 

SUM ( [Constituent ID] , 
  IF ( [Total Ticket Purchase Amount] > 0
    AND ( [Total Contribution Amount] , [Fund IN your membership funds] ) > 0
  , 1 , NULL )
)

If you need to see it at a higher level, like monthly rather than daily, then we need to add Transaction Date in the formula...

SUM ( [Constituent ID] , 
  SUM ( [Days in Transaction Date] ,
    IF ( [Total Ticket Purchase Amount] > 0
      AND ( [Total Contribution Amount] , [Fund IN your membership funds] ) > 0
    , 1 , NULL )
  )
)

Thank you Chris, this is great. And they make total sense. For some reason though it doesn't like the greater than sign, in both cases. The formula is so basic though, I can't see why it's giving me the "unexpected token '>' in FunctionParameters definition" error.

It’s me, hi, I’m the problem.

I'm going to update my original post, but this needs a "> 0" appended to it in both examples...

AND ( [Total Contribution Amount] , [Fund IN your membership funds] ) > 0

That helped the formula and I can save it now, but I'm not getting any results. 


 SMM Conversion Rate In Progress 6.6.23.dash

It's all the CONTRIBUTION DATE fields and filters that are segmenting contributions from ticket purchases. Change the Rows fields for Months and Days to use TRANSACTION DATE, and replace the Cont Dt in Last 2 Mo with TRANSACTION DATE in the Last 2 Months.

Wow, I really fell off on this one!

I changed the type of date I used (cont dt to trxn dt), I didn't even realize I had mixed them, haha! I was still coming up with zeros in the results. Interestingly, when I changed the Total Ticket Purchase Amount to ">=0" in the Value formula instead, THEN I got some results. The problem with these is that it includes orders with just memberships. I've tried adding pieces to the formula with no luck (Price Types, Campaign Category, etc.) with no luck. The error said I can't use 4 parameters in that function. I tried changing that zero to NULL (worth a shot), nope. I added Order Shipment Method PAH to the Widget filters, which helped some but there were some membership-only orders that had used that too. 

I am really stalled out now. Do you have any ideas?

Or is there a way to get at this from Seats & Tickets? Initially I ran into some bumps with this and thought that it wouldn't be possible, but now that I've come a little farther I'm wondering if I was wrong. I backed up to my original question, "how many orders have both a membership purchase and tickets ($0+ tickets)?"  Does that have any potential? So far I haven't had much luck that way either.

Could you make a list of records with an active membership and then use that list (as a filter) in the Tickets & Seats cube to look for those with tickets?

I could do something like that for a daily look but if I'm comparing something like this month to last month, or this July to last July it won't work.

Hi Jennifer,

I gave this some more thought and realized that I didn't suggest that the criteria also explore this by Order ID. You are likely have some constituents that are meeting the criteria across many orders, rather than within the scope of a single order. I went ahead and mocked one of these up in my local...

The [Ticket & Membership] value is still the IF() from what we have above, and it's flagging up a 1 for this order.

IF(
  [Total Ticket Purchase Amount] > 0
  AND ([Total Contribution Amount],[Fund=Membership]) > 0
  ,1,0
 )

But to roll it up, it needs to partition by Order ID somewhere. And if you only want to count constituents, not orders, we also need to partition by Constituent ID... so something like

SUM([Constituent ID],
  MAX([Order ID],
    IF(
      [Total Ticket Purchase Amount] > 0
      AND ([Total Contribution Amount],[Fund=Membership]) > 0
      ,1,0
    )
  )
)

For each constituent, if any of their orders have a ticket purchase amount > 0 and a membership fund contribution amount > 0, then return a 1 for that constituent to the overall SUM().

To turn that into a rate, divide by the total count of constituents.

SUM([Constituent ID],
  MAX([Order ID],
    IF(
      [Total Ticket Purchase Amount] > 0
      AND ([Total Contribution Amount],[Fund=Membership]) > 0
      ,1,0
    )
  )
) / COUNT([Constituent ID])

I couldn't get the ...MAX(Order ID... formula to go. There is an "Error in function definition (Max): Expecting parameter of type 'Set Expression' but found 'Member Expression'." 

None of the formulae have been able to get the member ticket part. They're slippery since they are $0. And they are often in separate orders since you can't buy a membership and member tickets in the same order online (it doesn't see that they're members until that transaction is complete). Below is an example (sorry it's so teeny!).

The $120 is the membership. The tickets are $0 but there are seats and a perf. But I can't get at that in the Finance cube, I can't get at contributions in the Seats & Tickets cube and I can't get at seats and perf info in the Contributions cube. It's starting to look as though I might have to go a different route.

Yes, I'm sorry I didn't catch that the tickets are $0, so as you say, they won't be in the Finance cube. We should be able to get closer in Interactions once that's available, but in the meantime, yes... another route. Enhance the custom report for now maybe?

Yes, I'm sorry I didn't catch that the tickets are $0, so as you say, they won't be in the Finance cube. We should be able to get closer in Interactions once that's available, but in the meantime, yes... another route. Enhance the custom report for now maybe?