Create Email Schedules using API

Introduction

In Holistics, you can set up schedules sending reports or dashboards to a group of recipients via email, Slack, SFTP, or Azure Blob. This concept is called Data Schedule.

Holistics offers a set of public API endpoints allowing clients to work with Data Schedule. This tutorial shows you how to use the API to create these data schedules programmatically.

The Use Case

Suppose you are a B2B SaaS company that has a feature to email usage statistics to your customers every week. You want to set it up such that:

• When a new customer onboards to your platform, you want Holistics to send a weekly email report to that customer.
• The weekly email reports should be customised to display information regarding the usage data of that customer only

Let's walk through how we can solve this problem using Holistics API.

High-level Approach

We will use the API to create email reports. Specifically, we'll send a POST request to Holistics' create email schedule API.

Before that, we need to prepare the following:

• A dashboard with relevant reports. This dashboard will be emailed to the customer.
• The dashboard should have a "Customer" filter to filter down the data for a particular customer.
• Holistics API Key: We need to setup the API key. Refer to this guide for info.

Step 1: Preparing the Dashboard

You will need to create a master dashboard that contains the reports you want to send to the customers. In this example, we have a Customer Usage Data dashboard which showcases the statistics of jobs that Holistics customers execute in our platform. We also have two filters in this dashboard, Date Range and Customer.

• The Customer ID filter accepts a customer ID, then passes it to the report within the dashboard. The report then replaces the ID with its placeholder in a prepared SQL query, reruns the query, and fetches only data that belongs to that customer. In other words, this filter helps filter down to the usage data of that customer only.
• Similarly, the Date Range filter's value accepts "last 7 days", "last 14 days", "last 30 days", or "any time". This filter helps filter usage data by a date range. Note that this filter can be optional.

If you are not sure on how to create dashboards and filters, refer to Dashboard Docs.

Step 2: Making the API Call

We'll be using the following endpoint:

POST https://secure.holistics.io/api/v2/data_schedules

There are some notable configuration options in this request:

• schedule: Specify how frequent the email schedule should be
• dynamic_filter_presets: Specify what dynamic filters should be applied to the reports. By configuring this, we can customise the report for each recipient.
• dest: Specify configurations regarding recipients' information.
  • Remember to set dest.type to "EmailDest"

Below is a sample request body. Refer to Holistics API docs for more information on these fields.

info

💡 Most of the fields are straightforward which means that you can fill them out by reading our API docs. Refer to this section below for notes on trickier fields.

Common fields

• id (integer): This is used to uniquely identified your object. Holistics automatically generates an id for you if you don't specify this.
• source_id (integer): This tells Holistics which dashboard you want to send email to. To determine this value, view your dashboard link. For example, the link https://secure.holistics.io/dashboards/v3/16076-demo-my-beautiful-dashboard will have the source_id of 16076.
• dest: Specify the information regarding the recipients
  • title (string): Note that Holistics support dynamic variables in email title. For example, the title Sales report for {{$today}} sent on 29/09/2021 will be rendered as Sales report for 2021-09-29 Wed.

Schedule frequency

schedule is used to specify the schedule for your email schedule.

repeat (string): You will need to input a crontab expression here to let Holistics how frequent you want to your email schedule to run.

Dynamic filter values

dynamic-filter-presets is where you define your filter presets here.

dynamic_filter_id (integer): To get your filter_id, send a GET request to Dashboards#Get API to get the dashboard info.

preset_conditions (object): Let's go through our example to better illustrate this field.

We want to set a value for the filter by setting a preset condition. A preset condition consists of an operator and a list of values. Holistics supports various operators and provides several examples here.

In this case, we want to use operator is and set values to customer_id. By this, we mean the filter's default value equals exactly to customer_id.

Finally, we also want to set up another dynamic filter preset to filter data to the whole of last week. Fortunately, Holistics allows a list of dynamic filter presets. For the Date Range filter, we similarly want to use operator is and set values to "last 7 days".

Our final dynamic_filter_presets object will look like this.

dynamic_filter_presets = [
  {
    "dynamic_filter_id": MY_CUSTOMER_FILTER_ID,
      "preset_condition": {
        "operator": "is",
        "values": [
          customer_id
      ]
    }
  },
    {
    "dynamic_filter_id": MY_DATE_RANGE_FILTER_ID,
      "preset_condition": {
        "operator": "is",
        "values": [
          "last 7 days"
      ]
    }
  }
]

Step 3: Double-check to see if your email schedule is created

The request should now be ready. After sending it to Holistics, the server will respond with a HTTP status response code to indicate whether the request was successful / failed.

Lastly, be sure to head over to Holistics page to see if your email schedule is created.

Example script

An example script to send requests written in Ruby is available here:

# install the neccessary dependencies for this script to work
require "bundler/inline"
require "net/http"
require "json"

gemfile do
  source "https://rubygems.org"
  gem "net-http"
end

# this the Holisitics API endpoint that we need to send our POST request to
EMAIL_SCHEDULE_ENDPOINT = URI("https://secure.holistics.io/api/v2/data_schedules")

# Step 1: Create a POST request to the endpoint below
request = Net::HTTP::Post.new(EMAIL_SCHEDULE_ENDPOINT)

# Step 2: Provide your authentication information 
request["X-Holistics-Key"] = "mysecretAPIkey"

# Step 2.1: This line helps the system know that we are sending a JSON request
request.content_type = "application/json"

# Step 3: Provide information for your email schedule in the request body
request.body =
  {
    "data_schedule": {
      "source_type": "Dashboard",
      "source_id": 16076,
      "schedule": {
        "repeat": "* * * * *",
        "paused": false,
      },
      "dest": {
        "type": "EmailDest",
        "title": "Sale report for {{$today}}",
        "recipients": [
          "[email protected]",
        ],
      },
    },
  }.to_json

# Step 4: Send the request to Holistics!
response = Net::HTTP.start(
  EMAIL_SCHEDULE_ENDPOINT.hostname,
  EMAIL_SCHEDULE_ENDPOINT.port,
  :use_ssl => EMAIL_SCHEDULE_ENDPOINT.scheme == "https",
) do |https|
  https.request(request)
end

# Optional: Output the result into the terminal
puts [response.code,response.message].join(' ')