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.

dqlcopydownload
fetch bizevents, from:now()-24h, to:now()
| filter event.type == "com.easytrade.sell-assets"
| summarize dollar_volume =  sum(toDouble(amount)*toDouble(price))

Results table

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.

dqlcopydownload
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

Example 3: Average asset price

• Assets can be bought and sold through EasyTrade, captured by the com.easytrade.buy-assets and com.easytrade.sell-assets event types.
• Without filtering by event.type, this query returns the average asset price for both buy and sell orders.

dqlcopydownload
fetch bizevents
| filter event.provider == "www.easytrade.com"
| filter isNotNull(price)
| summarize average_price_assets = avg(price)

Results table

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

dqlcopydownload
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

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.

dqlcopydownload
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

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.

dqlcopydownload
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

  dqlcopydownload
  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

  dqlcopydownload
  | filter event.provider == "www.easytrade.com"

  The filter provides the business events records based on the defined provider of the event

• Line 3

  dqlcopydownload
  | 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

  dqlcopydownload
  | 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

  dqlcopydownload
  | 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

  dqlcopydownload
  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

  dqlcopydownload
  | 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

  dqlcopydownload
  | 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

  dqlcopydownload
  | 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

  dqlcopydownload
  | 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

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.

dqlcopydownload
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

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.

dqlcopydownload
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

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, and com.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.

dqlcopydownload
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