Business Analytics examples
powered by Grail
These examples help illustrate how to query and analyze business event data.
The owner of the EasyTrade trading application—a tool that supports trading in deposit funds, fund withdrawal, and buy/sell transactions in shares—needs to calculate daily dollar trading volume, money transfers, the number of trade events, and more.
Basic examples
- Example 1
Calculate the total trading dollar volume generated by EasyTrade in the last 24 hours. - Example 2
Calculate the total trading dollar volume generated by EasyTrade in the last 24 hours in 5-minute intervals. - Example 3
Find out what the average asset price is. - Example 4
Find out how much money was deposited in the EasyTrade accounts per day, within the last 30 days. - Example 5
Find out how many trade events happen per day.
Example 1: Trading dollar volume
Calculate the total trading dollar volume generated by EasyTrade in the last 24 hours.
- Assets sold through EasyTrade are captured by the
com.easytrade.sell-assets
event type. - The total trading dollar volume is calculated as the number of assets sold multiplied by the asset price.
- The query returns a value of dollar volume for the sell-asset event type.
fetch bizevents, from:now()-24h, to:now()
| filter event.type == "com.easytrade.sell-assets"
| summarize dollar_volume = sum(toDouble(amount)*toDouble(price))
Results table
dollar_volume |
---|
4165261.3316477016 |
Example 2: Trading dollar volume in intervals
- Assets sold through EasyTrade are captured by the
com.easytrade.sell-assets
event type. - Trading dollar volume is calculated as the number of assets sold multiplied by their price.
- This query returns a value representing the dollar volume in intervals of five minutes.
fetch bizevents, from:now()-24h, to:now()
| filter event.type == "com.easytrade.sell-assets"
| summarize dollar_volume= sum (toDouble(amount)*toDouble(price)), by: {selltime=bin(timestamp, 5m)}
Results table
dollar_volume | selltime |
---|---|
8619.235775120002 | 2022-07-31T13:15:00.000000000Z |
81992.52102789 | 2022-07-31T13:20:00.000000000Z |
129259.07642331999 | 2022-07-31T13:25:00.000000000Z |
Example 3: Average asset price
- Assets can be bought and sold through EasyTrade, captured by the
com.easytrade.buy-assets
andcom.easytrade.sell-assets
event types. - Without filtering by event.type, this query returns the average asset price for both buy and sell orders.
fetch bizevents
| filter event.provider == "www.easytrade.com"
| filter isNotNull(price)
| summarize average_price_assets = avg(price)
Results table
average_price_assets |
---|
16.974404027940075 |
Example 4: Daily deposit value in the last 30 days
Find out how much money was transferred to EasyTrade accounts per day, within the last 30 days.
- Money transferred to EasyTrade is captured by the
com.easytrade.deposit-money
event type. - The query logic involves:
- Summarizing the amount of each event to provide the total amount of money transferred to EasyTrade
- Splitting this amount into days
fetch bizevents, from: now()-30d, to: now()
| filter event.type == "com.easytrade.deposit-money"
| summarize moneyTransfered= sum(toDouble(amount)), by: {daily=bin(timestamp, 1d)}
Results table
daily | moneyTransfered |
---|---|
2022-07-12T00:00:00.000000000Z | 1820108.88138907 |
2022-07-13T00:00:00.000000000Z | 2598216.419 |
2022-08-05T00:00:00.000000000Z | 2199207.386 |
Example 5: Number of trade events per day
Find out how many trade events happen per day. Trade events include both buy and sell events, so the com.easytrade.buy-assets
and com.easytrade.sell-assets
event types are combined.
fetch bizevents
| filter event.provider == "www.easytrade.com"
| filter event.type == "com.easytrade.sell-assets" OR event.type == "com.easytrade.buy-assets"
| summarize count = count(), by: {numberOfTrades}
Results table
numberOfTrades | count |
---|---|
384 |
Advanced examples
- Advanced Example 1
Measure time elapsed between events.
- Advanced Example 2
Get business events occuring only within the business hours. - Advanced Example 3
Report the five accounts with the greatest decrease in account value in the last 30 days. - Advanced Example 4
Missing transactions.
Example 1: Time elapsed between events.
You need to know how long it takes for a customer to decide to start trading. To achieve this, you need to find out the time elapsed between events related to a specific transaction, namely the time between the first deposit of money in EasyTrade and the first acquisition of assets in the application.
fetch bizevents, from:now()-30d, to:now()
| filter event.provider == "www.easytrade.com"
| sort timestamp, direction:"descending"
| filter event.type == "com.easytrade.buy-assets" OR event.type == "com.easytrade.deposit-money"
| fieldsAdd deposit_ts = if(event.type == "com.easytrade.deposit-money", toLong(timestamp))
| fieldsAdd buy_asset_ts = if(event.type == "com.easytrade.buy-assets", toLong(timestamp))
| summarize first_deposit_ts = takeFirst(deposit_ts), first_buy_asset_ts = takeFirst(buy_asset_ts), by:accountId
| fieldsAdd time_from_deposit_to_first_buy = (first_buy_asset_ts - first_deposit_ts)/(1000000000.0)
| filter time_from_deposit_to_first_buy > 0
| fields accountId, time_from_deposit_to_first_buy
Query steps explained:
-
Line 1
fetch bizevents, from:now()-30d, to:now()
The business events table is fetched. In addition, the optional
from:
parameter specifies the query start timestamp. -
Line 2
| filter event.provider == "www.easytrade.com"
The filter provides the business events records based on the defined provider of the event
-
Line 3
| sort timestamp, direction:"descending"
To get the most recent events first, you can sort the results by timestamp from the most recent ones.
-
Line 4
| filter event.type == "com.easytrade.buy-assets" OR event.type == "com.easytrade.deposit-money"
A new filter combines two different types of events: a deposit of money in EasyTrade and the buy transaction in the application.
-
Line 5
| fieldsAdd deposit_ts = if(event.type == "com.easytrade.deposit-money", toLong(timestamp))
The fieldsAdd command creates a new field: a timestamp, formatting it into a long integer. Long integer is required to perform math operations between timestamps . This is your deposit timestamp.
-
Line 6
fieldsAdd buy_asset_ts = if(event.type == "com.easytrade.buy-assets", toLong(timestamp))
The fieldsAdd command creates a new field: a timestamp, formatting it into a long integer. Long integer is required to perform math operations between timestamps. This is your buy-assets timestamp.
-
Line 7
| summarize first_deposit_ts = takeFirst(deposit_ts), first_buy_asset_ts = takeFirst(buy_asset_ts), by:accountId
The summarize command groups by account:
- The first timestamp for a deposit event
- The first timestamp for a buy-assets event
-
Line 8
| fieldsAdd time_from_deposit_to_first_buy = (first_buy_asset_ts - first_deposit_ts)/(1000000000.0)
Creates a new field to calculate the difference between two timestamps, in order to calculate the time elapsed between the first deposit-money and buy-assets actions per account (in seconds)
-
Line 9
| filter time_from_deposit_to_first_buy > 0
This filter command removes all 0 and negative values, so any buy assets event that happened before a deposit money event is removed from the results.
-
Line 10
| fields accountId, time_from_deposit_to_first_buy
The fields command restricts the output to account id and the time elapsed between the first deposit and the first purchase of assets.
Results table
accountId | time_from_deposit_to_first_buy |
---|---|
6 | 0.601 |
7 | 0.5504 |
8 | 0.6002 |
Example 2: Business events during office hours
To get business events occurring only within the business hours, choose the business hours to monitor and then remove the non-working days (for example, Saturdays and Sundays) and non-working hours (for example, from 5 pm to 6 am).
Be conscious of the time zone: if you are based in the UK and analyze events during office hours in a Singapore-based company, timestamps need to be adjusted.
fetch bizevents
| filter isNotNull(amount)
| fieldsAdd hour=toLong(formatTimestamp(timestamp,format:"H")), day_of_week = formatTimestamp(timestamp,format:"EE")
| filterOut day_of_week == "Sat" or day_of_week == "Sun" // Remove weekend days
| filterOut hour <= 6 or hour >= 17 // Remove everything that is not between 6 am and 5 pm
| fields accountId, event.type, amount, cardType, event.category, event.kind
Results table
accountId | event.type | amount | cardType | event.category | event.kind |
---|---|---|---|---|---|
63 | com.easytrade.withdraw-money | 696.6776555764262 | Visa Credit | /api/creditcard/WithdrawMoney | BIZ_EVENT |
85 | com.easytrade.sell-assets | 46 | American Express | /broker/api/trade/SellAsset | BIZ_EVENT |
96 | com.easytrade.deposit-money | 640.5978171890785 | Mastercard | /broker/api/creditcard/DepositMoney | BIZ_EVENT |
Example 3: Five accounts with the greatest decrease in account value
Find the top five accounts with the highest decrease in the account value in the last 30 days. This query compares total deposits with total withdrawals.
fetch bizevents| filter event.provider == "www.easytrade.com"
| filter event.type == "com.easytrade.withdraw-money" or event.type == "com.easytrade.deposit-money"
| fieldsAdd amount_withdrawal = if(event.type=="com.easytrade.withdraw-money", toDouble(amount), else:0.0)
| fieldsAdd amount_deposit = if(event.type=="com.easytrade.deposit-money", toDouble(amount), else:0.0)
| summarize total_withdrawals = sum(amount_withdrawal), total_deposits = sum(amount_deposit), by:accountId
| fieldsAdd balance_change = total_deposits - total_withdrawals
| filter balance_change < 0.0
| fields accountId, balance_change
| sort balance_change, direction:"ascending"
| limit 5
Results table
accountId | balance_change |
---|---|
31 | -4072.6706527509714 |
78 | -3852.5574527783924 |
66 | -3198.595229052105 |
61 | -2897.5683210647585 |
81 | -2768.1265797864867 |
Example 4: Missing transactions
A retail company commits to same-day shipping, accepting orders from 8:00 AM until 5:00 PM. They track orders placed, orders processed, and orders shipped. Shipping is complete by 9:00 p.m. each day, at which point the number of events recorded for each event type should be the same; any discrepancies are flagged as an anomaly to be investigated. They run this query at 9:00 p.m. each day.
- Important event types for this query are
com.bigsale.place-order
,com.bigsale.pack-order
, andcom.bigsale.ship-order
. - The query generates a series of counts from the three event types.
- The query creates a new field with a condition that should normally be fulfilled. Specifically, the condition is fulfilled when the counts for all three event types are identical.
fetch bizevents, from:now()-15h
| filter event.provider == "www.bigsale.com"
| summarize event_A = countIf(event.type=="com.bigsale.place-order"), event_B = countIf(event.type=="com.bigsale.pack-order"), event_C = countIf(event.type=="com.bigsale.ship-order"),by :accountId
| fieldsAdd fulfilled = (event_A == event_B and event_A == event_C)
Results table
accountId | event_A | event_B | event_C | fulfilled |
---|---|---|---|---|
123 | 2 | 2 | 1 | false |
136 | 1 | 1 | 1 | true |
201 | 3 | 3 | 3 | true |
404 | 0 | 1 | 1 | false |