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.