How to create periodic reports

Last update:

Audience: Developers building Sapho micro apps

Periodic reports are used to regularly deliver information based on your aggregated data over a specified timeframe.

For example, you can create a report about the number of new leads in Salesforce for the previous week and have it delivered to Sales managers every Monday via a Slack channel.

Basic setup

To create a periodic report, navigate to the Events section within your micro app in Builder and create a new event.

  1. Select Periodic Report and the database table you want to pull data from for the report’s aggregations.
  2. You will need to configure how often you want a report to be executed. For example, if you want a report sent every Monday with data aggregated from the previous week, select Weekly on Monday.
  3. Next you will set up how your data will be aggregated. Define which date field should be used to aggregate data in your selected timeframe. So if you wanted to aggregate new leads in Salesforce, you would select the created_date database column in the For the date section.
  4. Set how your report will group aggregated data. If you want to aggregate data for the whole week, select Week By Week. If you want to get notified about new leads every day, select Day By Day, etc.
  5. Select what period of time should be used to gather the data for your report in the For the date section. This period is related to both the grouping you set and also the frequency of report delivery. If you group your data Day By Day and set the frequency of delivery to Daily, then In the Same Period will aggregate data for today and In the Last Period will aggregate data from yesterday. If you choose to group your data Week By Week and set your delivery frequency to Weekly, then In the Same Period will aggregate data for this week and In the Last Period will aggregate data for last week.

If you need more specific grouping, select By Custom Interval. This lets you specify the number of days of data that will be used for the report relative to the date that the event is executed. For instance, if you want to receive a daily report based on data from the past 3 days, set your frequency to Daily and set the period for By Custom Interval to minus 3 days to minus 0 days.

Conditions

You can set limiting conditions to query only certain records set. There are two types of conditions for periodic reports:

  • Value of - a condition applied to the initial data set to limit the list of pulled records, e.g. fetch only leads with a particular status.
  • Sum of - a condition applied to the data grouping, e.g. leads’ annual revenue sum exceeds a certain financial threshold when compared to the previous period’s data set. If this condition is not met, the report is not triggered.

Variables

As is the case with other event types, you can insert variables into your notification text. However, since the report is based on aggregated data, periodic reports only allow you to insert aggregated values of numeric database columns.

So for each numeric column, you can insert a sum of its values for the selected timeframe in several variations:

  • Current value - the sum of the value for the actual timeframe.
  • Previous value - the sum of the value for the previous timeframe.
  • Absolute change - the absolute difference between the sum of the value for the actual timeframe and the sum of the value for the previous timeframe.
  • Relative change - the percentual difference between the sum of the value for the actual timeframe and the sum of the value for the previous timeframe.

Examples

Below are some examples of reports you may want to create and how the corresponding settings would be set up in Sapho.

Example Run this In the Group data
Send a daily report summarizing completed JIRA tickets for the day. Daily Same period Day By Day
Send a daily report about expense reports ready for reimbursement in Expensify on the previous day. Daily Last period Day By Day
Send a report on Friday evening about closed tickets in Zendesk for the whole week.  Weekly Same period Week By Week
Send a report on Monday morning summarizing new leads in Salesforce for the previous week. Weekly  Last period Week By Week 
Send a report on the last day of the month about closed opportunities in Dynamics CRM.  Monthly Same period Month By Month
Send a Google Analytics report on the first day of the month about basic web stats for the previous month.  Monthly Last period  Month By Month 
Send a daily overview of upcoming meetings for the next 3 days from Google Calendar.  Daily Same period Custom Interval