Hello all,
I am hoping you can assist with a parameters question that is creating a huge discrepancy in our subscription revenue.
In my subscriptions mega dashboard, I have multiple widgets for sub forecast. The reason has to do with the criteria Original Ticket Paid Amount and Ticket Paid Amount. I use Original Ticket Paid Amount to report on how much package revenue we sold but for finance we need Paid amount for how much we have currently in the bank.
However, when comparing Ticket Original Paid Amount vs Ticket Paid / Reserved I am getting a discrepancy of ~ 300k
The widget in question is looking at the following parameters
On the Rows I have Finance Keyword Category and Finance Keyword
For Values I have:
Total Ticket Original Paid Amount
Total Ticket Paid/ Reserved Amount (for comparison)
The Sub Forecast: SUM([Performance ID],[Max Forecast Subscription Revenue])
The % to Sub Goal: [Total Ticket Paid Amount] / SUM([Performance ID],[Max Forecast Subscription Revenue])
and finally
The Variance to Goal: [Total Ticket Paid Amount] - SUM([Performance ID],[Max Forecast Subscription Revenue])
We want to use Paid Amount for the forecast and variance as this is the money we actually have right now. However, I also want to know how much money we have total on all the payment plans come through.
For Filters I am looking at
Fiscal Year: 2023
Season Type: Concerts and Events
Price Type Category: Subscriptions
Finance Keyword: Everything but "None"
Status: Suppress "Cancelled"
and SLI Status: Suppress Unseated/Unpaid and Returned in Benevolent Mode
As mentioned above, Original Paid Amount and Paid Reserved Amount have a discrepancy of ~300k and I cannot figure it out.
From my understanding Original Paid Value is used to show the value of the package before it was broken down to trades and exchanges and Paid/Reserved is looking at the package after it was broken down. However, Finance keyword should look into line items only and not packages.
Therefore, in theory, the Ticket Paid/Reserved should equal the Ticket Original paid amount but it doesn't. Which creates the biggest question / problem. If we have X amount in Original Ticket Paid Amount and 300k less in Ticket Paid/ Reserved, in a dashboard that is theoretically looking at line items, where did this 300k end up in? Is it counted as an addon if so why?
If Original Ticket Paid Amount is correct then (although we need ~ 400k to reach our sub goal, when calculating with Ticket Paid Amount) this formula below reports that we are currently almost 20k over goal. Can it be trusted?-1*(SUM ( [Performance ID],[Max Forecast Subscription Revenue]) - ( [Total Ticket Original Value], [Price Type Category] )) [This is a separate widget and Price Type Category is filter for Subscriptions only]
Also I would love to hear which criteria you all are using. Knowing that Sub add-ons don't count towards our subscription goal, and that exchanges break down the package integrity, should I be using Original Ticket Paid Amount or Ticket Paid reserved?
I appreciate any and all the help you can provide.
-Alex
Hi Alex,
Can you reproduce this issue without the Finance Keyword fields being involved? That's a custom field specific to your organization. If the issue seems to be related to how that field is reporting in Analytics, we might need to go offline with Kirk McMahon for help.
Hi Chris.
We went ahead and built a basic dashboard with all three formulas from above. to look into individual Const. accounts and I think we now discovered the issue.
I am not sure if the formula is malfunctioning or if it was intended to work this way but Original Ticket Paid Amount was the problem. I thought that Original Ticket paid Amount looks at items that have a package code and only adds the original package values on said package code level. For example I bought a package for $1k traded half of it, (paid more in some cases) but if I were to look at the report then it should say 1k. However, if people exchanged or traded items it seems to add those to the total.
In this same example - (Unless you suppress zero packages) someone bought a $1000.00 package and exchanged half of it (returned or deleted the line items) to other dates of equal value, then analytics reports $1500.00 worth of revenue. The exchanged/ traded lines were all in new orders under perf. code with the same sub price type. As soon as we suppressed zero packages it went back to normal. Which was where we had the $300k discrepancy.
If this is the proper function of Original Ticket Paid Amount then please disregard, we now know to use the parameter with the appropriate filters, but I wanted to share the findings with you in case this is a bug.
Thanks again for your reply,
Original Amount reports the Ticket Paid Amount even after exchanges, as opposed to only after exchanges. So anything that isn't an exchange, and even anything that isn't a package, has an Original Amount that equals the Ticket Paid Amount. I'm really glad you got it figured out for your dashboard!
Thanks for letting me know!Chris