Technical Note - Querying Transactions

Technical Note - Querying Transactions

Formulas can query transactions using SQL with or without {{...}}. 
  1. var $rev = {{SELECT [calculated_revenue] FROM Transactions WHERE ...}}
  2. var $rev = SELECT [calculated_revenue] FROM Transactions WHERE ...

With {{...}}
This is an advanced SQL statement. It queries all transactions within your workspace:
  1. Each call is sent to a remote database containing all your workspace's transactions (ex: millions)
  2. This is expensive - 100 calls result in 100 remote database queries over a large data set
  3. You can use a SQL cached query to pre-load the data into a local database, but this is additional work
  4. You must apply complex filters to query transactions for the right user and timeframe 

Without {{...}}
This is a virtual set of credited transactions (after applying filtering and crediting rules) which is automatically generated after crediting each target user / team.
  1. Each call is sent to a local database
  2. This is cheap - 100 calls result in 100 local database queries over a small data set
  3. You do NOT need to pre-load the data to improve performance
  4. You can avoid complex filters since the query is already scoped to virtual set of credited transactions

Example
A plan has the following:
  1. Crediting rules which allow splits
  2. An effective date called "Closed Won Date"
  3. You want to compute the average revenue per credited rep
If you query transactions directly, you need to craft your SQL query to account for crediting splits and the effective date.
  1. First you must manually select transactions in the right timeframe:
    1. var $avg = {{SELECT AVG([calculated_revenue]) FROM Transactions WHERE [Closed Won Date (Imported)] >= @CalculationStartDateUtc AND [Closed Won Date (Imported)] < @CalculationEndDateUtc}}
  2. Then you need more complex logic to filter to each rep and apply split weights to revenue:
    1. var $avg = {{SELECT AVG([calculated_revenue] / (LEN(COALESCE([Owner / Sold By (System)], '')) - LEN(REPLACE(COALESCE([Owner / Sold By (System)], ''), ';', '')) + 1)) FROM Transactions WHERE [Owner / Sold By (System)] LIKE '%$rep%' AND [Closed Won Date (Imported)] >= @CalculationStartDateUtc AND [Closed Won Date (Imported)] < @CalculationEndDateUtc}}

Instead, you can simply the virtual set of transactions credited to each rep, with effective dates and crediting weights already applied:
      var $avg = SELECT AVG([calculated_revenue]) FROM Transactions

That's it!
  1. No need to specify the time frame
  2. No need to filter to a specific rep
  3. No need to manually handle splits

When to use each
Here are some guidelines:
  1. Avoid {{...}} if querying sets of credited transactions is sufficient
  2. Use SQL direct queries if you need to check data from other data sources, query transactions from other periods, etc.

    • Related Articles

    • How Can I Automate Payment Of Commissions?

      Once your commission structure is stable and your calculations run like clockwork, you may want to automate payment of commissions to eliminate the following steps: Navigate to Calculations > All Calculations Export payouts for payroll Upload the CSV ...

    • How Are Billing Charges Calculated?

      Charges are per workspace, and calculated based on: Your subscription (trial vs. paid) The number of active users There are no charges for trials. However, trials expire after 14 days. Should you upgrade to a paid subscriptions, we will charge you ...

    • What Discounts Are Available?

      If you've researched other solutions, you already know that our solution is priced extremely competitively. We also do not want to charge some of our customers more, so that we are able to offer discounts to other customers. We may offer a discount ...

    • How Can I Minimize Billing Charges?

      Nobody wants to be billed for no good reason. Our billing is per user. It is designed to match usage, our own costs performing incentive calculations, and complexity associated with managing various records. Here are our recommendations to avoid ...

    • How Can I View / Print My Invoices?

      To view invoices: Login to your workspace On the left pane, click on "Settings > Billing" Click on the "Invoices" tab (if needed) For more details, click on each invoice To print, click on "Print Invoice" To print your invoice as a PDF, use "Print ...