---
schema_version: "1"
id: 88a4c9e76ec766970f049768f1a51edd810170f46863f5458d2dd83fc76ef97f
title: Calculated KPI Formulas and Common SQL Functions
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/mc_dat_queries_funcs.md
canonical_url: https://help.salesforce.com/s/articleView?id=mktg.mc_dat_queries_funcs.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 733f5311d2e36342459765fadf86973a98a2547e75b315c65219c67b95698839
description: You can include calculated KPI formulas in your queries, such as ‘push open rate’ using the SQL tool in Queries.
products:
  - Marketing
  - Marketing Cloud Intelligence
release: "264"
---
# 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

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small edition"><colgroup><col></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: Intelligence Reports Advanced</td></tr></tbody></table>

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:

![Intelligence Reports calculated](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-mktg-mc_data_management_and_analytics-264-0-0-production-enus/7988f60d-cd94-4b42-8836-dc11773564b5/mc_data_management_and_analytics/images/mc_dat_queries_calc.png)