Calculated KPI Formulas and Common SQL Functions
You can include calculated KPI formulas in your queries, such as ‘push open rate’ using the SQL tool in Queries.
Required Editions
| Available in: Intelligence Reports Advanced |
This table includes examples of common SQL functions and formulas for calculated KPIs.
| Output | Function | Category | Example |
|---|---|---|---|
| Email Bounce Rate | sum(email_bounces)/sum(email_sends) as email_bounce_rate | Calculated Measurement | 3.02% |
| Email Click Rate | sum(email_unique_clicks)/(sum(email_sends)-sum(email_bounces)) as email_click_rate | Calculated Measurement | 2.66% |
| Email Click To Open Rate | sum(email_unique_clicks)/sum(email_unique_opens) as email_click_to_open_rate | Calculated Measurement | 9.17% |
| Email Deliveries | sum(email_sends)-sum(email_bounces) as email_deliveries | Calculated Measurement | 74 |
| Email Delivery Rate | (sum(email_sends)-sum(email_bounces))/sum(email_sends) as email_delivery_rate | Calculated Measurement | 96.98% |
| Email Open Rate | sum(email_unique_opens)/(sum(email_sends)-sum(email_bounces)) as email_open_rate | Calculated Measurement | 29.00% |
| Email Unsubscribe Rate | sum(email_unique_unsubscribes)/(sum(email_sends)-sum(email_bounces)) as email_bounce_rate | Calculated Measurement | 0.54% |
| Event Month | date(date_trunc('month', event_date)) | Date | 01 Aug 2021 |
| Event Quarter | date(date_trunc('quarter', event_date)) | Date | 01 Jul 2021 |
| Event Week | date(date_trunc('week', event_date)) | Date | 02 Aug 2021 |
| Event Weekday | format_datetime(event_date, 'EEEE') | Dimension | Friday |
| Event Year | date(date_trunc('year', event_date)) | Date | 01 Jan 2021 |
| Push Bounce Rate | sum(push_bounces)/sum(push_sends) as push_bounce_rate | Calculated Measurement | 3.02% |
| Push Deliveries | sum(push_sends)-sum(push_bounces) as push_deliveries | Calculated Measurement | 74 |
| Push Delivery Rate | (sum(push_sends)-sum(push_bounces))/sum(push_sends) as push_delivery_rate | Calculated Measurement | 96.98% |
| Push Open Rate | sum(push_opens)/(sum(push_sends)-sum(push_bounces)) as push_open_rate | Calculated Measurement | 29.00% |
| Send Month | date(date_trunc('month', send_date)) | Date | 01 Aug 2021 |
| Send Quarter | date(date_trunc('quarter', send_date)) | Date | 01 Jul 2021 |
| Send Week | date(date_trunc('week', send_date)) | Date | 02 Aug 2021 |
| Send Weekday | format_datetime(send_date, 'EEEE') | Dimension | Friday |
| Send Year | date(date_trunc('year', send_date)) | Date | 01 Jan 2021 |
Here is an example of how to use Event Weekday and Email Delivery Rate formulas from the calculated KPI formula table, in order to retrieve the email delivery rate per subscriber and weekday.
select subscriber_key,format_datetime(event_date, 'EEEE') as event_weekday, sum(email_sends) as email_sends, sum(email_bounces) as email_bounces, (sum(email_sends)-sum(email_bounces))/sum(email_sends) as email_delivery_rate
from engagement_data
Where event_date between (now()- INTERVAL '7' DAY) and now()
group by subscriber_key, format_datetime(event_date, 'EEEE')
having sum(email_sends)>0
Here you can see the results of the email delivery rate per subscriber and weekday:
Did this article solve your issue?
Let us know so we can improve!
