Explore
Categories
- authentication
- calls
- data sync
- messages
- partners
- webhooks
- workflow

In this tutorial, we will discuss how we can leverage Aircall's Public API for building your own custom Activity Tracker to display a summary of Aircall user availabilities, call activities, and KPIs from a single view or dashboard!

To ensure you are able to focus on the content that suits your needs, we have divided this tutorial into two parts. If you are interested in developing your own activity tracker application from scratch, please review the Developer tutorial. If you are interested in installing a pre-built activity tracker within Google Sheets, please consult the Business tutorial.

Table of contents
Business	Use the activity tracker script for my company
Developer	Implementing activity tracking as a developer

Business

To demonstrate the power and flexibility of Aircall’s Public API, the Aircall team has built an Activity Tracker on top of a tool that is utilized by business users everywhere: Google Spreadsheets! Combining the simplicity and universality of Google Sheets with Aircall’s classic REST API, we have created an Activity Tracker wallboard that is intuitive to use and experiment with.

Our Google Sheets Activity Tracker implements features important from a "supervisor"-role perspective, allowing these types of users to:

View Aircall teams' user availability, along with key call activity metrics, at a high-level
Review historical call activities (that matches particular search criteria) with just a few clicks

DISCLAIMER: Please note that this is an unofficially supported tool. Our Activity Tracker should simply be taken as an example of a useful tool that can be built on top of Aircall with our public API. Please use it at your own discretion.

Table of contents

Before Getting Started
- Technology stack
- Setup Requirements
Installation
Using the activity tracker
- Understanding the tracker
- Advanced Setup
Features
Tips & Tricks
Get Started!

Before Getting Started

Let us first review the pre-requirements for the Activity Tracker before we install, configure, and use it.

Technology Stack

The Activity Tracker essentially relies on two main components:

Aircall Public API
- Communicating with Aircall via the classic REST API is critical to the functionality of the tool — we query Aircall for an update on all Aircall user availabilities and call activities, then we use Google Sheets to organize and display the data

This is primarily a read-only tool — that is, we only ask Aircall for information (we never create or update information within Aircall itself), with the exception of the “Switch User Status” feature.

In addition, since we are primarily using a REST API, please note that this Activity Tracker will not show user availabilities and call activities in real-time.

Google Sheets
- We leverage Google Sheets to organize and display Aircall user availabilities and call activities
- Google Apps Script (GAS) allows us to programmatically create (using Javascript-like syntax) custom menus, dialogs, sidebars, as well as manipulate cells within Google Sheets, extending the capabilities of a standard vanilla spreadsheet program
- We also make use of third-party Javascript modules within some of our Google scripts
- select2: A module that allows us to build more advanced (and user-friendly) search boxes
- flatpickr: A module that allows us to build lightweight and powerful date-time pickers
Google Chrome
- For the best results, please use the latest version of the Google Chrome browser

For more technical information about Google Apps Script for Google Sheets, please review their public documentation by clicking here.

Setup Requirements

Please follow these steps to set up a base environment from which we will then install the Activity Tracker.

1. Create a new Google Sheet

Within Google Sheets, please first create a brand new (blank) spreadsheet and rename it as you wish.

If you do not have a Google account, you will need to register for one first before you can access the Google suite of products.

2. Create a new Google Apps Script Project

From the menu at the top, select Tools → Script editor to create a new GAS Project for this spreadsheet. A GAS Project represents a collection of files and resources in Google Apps Script, such as code files (having a .gs extension) or HTML files (a .html extension). You can also include JavaScript and CSS in HTML files.

Once a new project is created, give it a new name.

Installation

1. Copy Scripts from GitHub into Google Apps Script Project

Now that you have set up your base environment, we can move on to installing the Activity Tracker within Google Sheets. The crux of the installation will require you to manually copy a few scripts from a centralized location (i.e. a GitHub repository) into Google — however, don’t worry: you do not need to understand the code; you simply and only need to copy it from one location to another.

Please navigate to our public GitHub repository by clicking here.

You will find five scripts in total within the GitHub repo, and each one will need to be copied over to your Google Apps Script Project:

Aircall_ActivityTracker.gs: This contains the code that does the bulk of the work (i.e. querying Aircall via REST API and organizing the data for the wallboard)
SetConfig.html: This contains the code that displays a sidebar for configuring the Activity Tracker (e.g. setting your API key, determining the activity period, etc.)
UserCallHistory.html: This contains the code that displays the search result of call activities for a particular Aircall user (within the specified activity period)
AllCallHistory.html: This contains the code that displays the search result of call activities for all Aircall users (within the specified activity period)
AdvancedSearch.html: This contains the code that displays the search result of all call activities that match a group of specific search criteria (e.g. Aircall users, Aircall tags, call direction, missed call reason, etc.)

To view a script’s code within GitHub, simply click on the name of any of the scripts listed. The script’s code itself always starts at the first numbered line and ends with the last numbered line.

In general, you are aiming for your Google Apps Script Project interface to look like the below screenshot once you are done:

Please follow the below instructions to copy all five scripts into your Google Apps Script Project.

In the GAS Project, click on the “+” button and choose “Script”, then name the file “Aircall_ActivityTracker”.
Go to the GitHub repository and click on the link for Aircall_ActivityTracker.gs.
Copy the code text of Aircall_ActivityTracker.gs in GitHub and paste it into the Aircall_ActivityTracker.gs in the GAS Project. Make sure to replace any existing code text that existed in the Google file already (i.e. overwrite the previous code).
Save the file in the GAS Project (by clicking on the “floppy disk” icon).
In the GAS Project, click on the “+” button again and choose “HTML” this time, then name the file “AdvancedSearch”.
Go to the GitHub repository and click on the link for AdvancedSearch.html.
Copy the code text of AdvancedSearch.html in GitHub and paste it into the AdvancedSearch.html file in the GAS Project. Make sure to replace any existing code text that existed in the Google file already (i.e. overwrite the previous code).
Save the file in the GAS Project (by clicking on the “floppy disk” icon).
Repeat Steps 5 - 8 three more times, once for each of the remaining three scripts:
- AllCallHistory.html
- SetConfig.html
- UserCallHistory.html
In the GAS Project, right-click on the three dots beside the original “Code.gs” file and select “Delete”. As a reminder, once you have copied all five scripts into the GAS Project, your interface should look similar to this screenshot below. Note that the order of the files in your GAS Project file list does not matter.

2. Authorize Scripts To Access Data

Return back to the tab of the original spreadsheet and refresh the page. Wait a few seconds (i.e. 10s - 30s) for two custom menu items to appear: Calls and Activity Tracker.

Select Activity Tracker → Setup Activity Tracker.

You will be prompted to authorize these scripts to perform certain actions:

Modify the content of this spreadsheet (i.e. to display the Activity Tracker)
Connect to an external service (i.e. to call Aircall Public REST API)
Display external information in sidebars in this spreadsheet (i.e. to display call history results from searches)

Press the Continue button. Next, Google will ask you to select which Google account you want to grant permissions to, so select an account.

If you receive a warning page with the title “Google hasn’t verified this app”, simply click on the “Advanced” link and then click on “Go to ”. Google is simply warning you to trust the owner of the script (in this case, yourself) before granting the script permissions.

You will then be prompted to review the authorization requests as mentioned above — select Allow to move forward.

3. Create and Configure API Key

In order to communicate with Aircall through our Public REST API, you will need to have an API key.

An API key is essentially a string of text that helps Aircall identify and authenticate who or what is calling for its services via REST API. Aircall will use this “key” to track and control how Aircall information is utilized by external third-party users or programs (such as our Google Sheets Activity Tracker).

To create a new API key, navigate to your Aircall admin dashboard and go to the Account section. Search for the word “Add an API key” and click on the link to create a new one.

You will be given an API ID and an API token. Please note that this will be your only time to copy the API ID — once you close the window, Aircall will not be able to reveal the API ID anymore — so please take note of both the ID and token and store them in a safe place in case you need to reconstruct the API key in the future.

The API ID is the username and the API token is the password for each Public API request.

Now, to construct the API key, you will need to use a service to “Base64 encode” both your API ID and token together. This is just a fancy term for converting these unreadable strings of text into a set of characters that is understood by HTTP (the protocol which supports data communication across the internet) and Aircall REST API for basic authentication.

Navigate to a free and secure web service such as Base64Encode (click here) and concatenate your API ID and token together, but separate them by a “:” character (which should generally look like "<API_ID>:<API_TOKEN>", where <API_ID> is the string of text representing your API ID and <API_TOKEN> is the string of text representing your API Token).

Next, click the button to encode the text, which should output a Base64-encoded string representing your API key! Like your API ID and token, please keep this API key in a safe place — we will use it in the next step for our Activity Tracker.

4. Setup the Spreadsheet

Return back to the Google Sheets spreadsheet and select Activity Tracker → Configure from the menu at the top.

This will open a sidebar, which will appear on the right. An explanation of all the configuration parameters for the Activity Tracker will be discussed later in the tutorial.

For now, we will focus on the API Key parameter. Copy your API key (taken from the previous step) and paste it into this field. Then click the Submit button. The sidebar will close.

Now, our Google Sheets Activity Tracker has an identifier (the key) that will allow us to communicate with Aircall via REST API. Next, select Activity Tracker → Setup Activity Tracker again from the menu at the top.

This will activate the process to set up the various spreadsheet tabs that will eventually contain all of the information required to display Aircall user availabilities and call activities.

When the process is complete, you will find four new spreadsheet tabs at the bottom of the interface:

Activity Tracker: This tab is the main tab we will be working out of. It will eventually be used to display Aircall user availabilities and call activities.
UserDB: Metadata for our tool, not particularly useful from a functional perspective.
NumberDB: Metadata for our tool, not particularly useful from a functional perspective.
UserSelection: Metadata for our tool, not particularly useful from a functional perspective.

Feel free to delete the additional “Sheet1” tab from the spreadsheet as it will not be used.

Now we are ready to actually see some data! From the newly created spreadsheet tabs at the bottom, click on the “Activity Tracker” tab.

To do a quick test, select Activity Tracker → Refresh from the menu at the top. This will essentially populate your Activity Tracker with Aircall user availabilities and call activities.

Since we have not yet specified the activity period from which we want to aggregate and summarize call activities, we use a default activity period of the past fifteen minutes. The user availabilities, however, will be current.

Using the Activity Tracker

Understanding the Tracker

In this section we will explore the Google Sheets Activity Tracker itself to help you understand how to interpret the information displayed and make additional configuration changes to the tracker to meet your specific reporting needs.

First, let’s take a look at the information that is displayed. The main components are generally divided into three categories: user availability statuses, user activity summaries, and the inbound calls queue.

User Availability Statuses

The most noticeable part of the Activity Tracker are your Aircall user availabilities, which have been organized under Aircall Teams that exist within your Aircall instance.

The colored cell background of each user allows you to quickly identify their availability status:

Green: User is logged into the phone app and available to take calls
Red: User is logged into the phone app but is in “do not disturb” mode (unable to take calls)
Grey: User is logged out of the phone app (offline)
Orange: User is currently on a phone call (and also unable to receive other phone calls)
- If a user is on a phone call, the Activity Tracker will display high-level information about the call itself (i.e. call direction, external and Aircall phone number involved, and current length of call)
Yellow: User has finished a call and is in “wrap-up” time (if enabled) or in “mandatory call-tagging” time (if enabled)

User Activity Summary

Based on your configured activity period (by default, it will be the past fifteen minutes), the Activity Tracker will aggregate and summarize all call activities related to a specific Aircall user and show it in a dedicated cell under the user’s name. If there are no call activities related to the user within the activity period, we simply do not show any information for that user.

In general, we will track per user:

Total number of inbound calls
Total number of outbound calls
Total number of missed calls
- Only available if “User <> Phone Number” features have been enabled (which will be discussed later in the “Advanced Setup” section of this tutorial)
SLA threshold met
- Whether the user is or is not meeting the configured SLA is determined by comparing the configured SLA to the average time it takes for the user to answer an inbound call within the specified activity period
- If the user is not meeting the SLA, the user’s name will be bolded in red
- Only available if a “SLA” is configured (which will be discussed later in the “Advanced Setup” section of this tutorial)

Since Aircall generally does not relate missed calls to specific users (we relate them to Aircall phone numbers instead), this Activity Tracker infers this data using the following assumption/logic:
- If a missed call is registered under a phone number...
- Find all Aircall users assigned to the phone number and log a missed call to each one

Inbound Calls Queue

To provide a little more context about inbound callers waiting to be picked up by an Aircall user, the Activity Tracker also displays all such calls with additional info describing each one.

Each call waiting in-queue will have the following details displayed:

External phone number calling inbound
Aircall phone number which is being called
Current waiting time of inbound caller
- The background color of the cell will indicate how long the call has been waiting to be picked up: green means the waiting time is within an acceptable range, yellow indicates the waiting time has exceeded the first threshold (warning), and red indicates the waiting time has exceeded the final threshold (unacceptable)

The first and second threshold are 10s and 20s respectively by default, but they can be configured (see the “Advanced Setup” section of the tutorial).

Aircall users that are assigned to the phone number who could potentially pickup the call
- This will be indicated by a matching cell border color, but only if “User <> Phone Number” features have been enabled (which will be discussed later in the “Advanced Setup” section of this tutorial)

Advanced Setup

There are various configurations we can make that will affect the behaviour of our Activity Tracker, allowing you to have more control over the tool.

Configuring Additional Parameters

If you want to configure optional parameters, select Activity Tracker → Configure from the menu at the top of the spreadsheet. This will open a sidebar from which you can customize the behaviour of the tool.

Here, we will describe the effect of each configurable parameter in detail:

Activity Period
- Determines how far back in time (starting now) the Activity Tracker will search for call activities in order to aggregate and summarize them per Aircall user.
- Please input a value in minutes.
- Default Value: 15
API Key
- The API key for your Aircall instance. The Activity Tracker will not work without one as it gives us permission to use Aircall Public REST API.
- Default Value: N/A
User Refresh Interval
- Some information, such as the list of Aircall users within your Aircall instance, does not change often. Hence, to optimize performance of the Activity Tracker, we refresh this information much less often than user availabilities and call activities (which are much more dynamic). This helps improve the performance of the Activity Tracker.
- Please input a value in minutes of how often you want a user refresh to occur.
- Default Value: 240
Team Refresh Interval
- Similar to the User Refresh Interval, but for the membership of Aircall Teams.
- Please input a value in minutes of how often you want a team refresh to occur.
- Default Value: 480
Call Waiting Threshold (Level 1)
- This is the first threshold of acceptable waiting time for inbound calls waiting in-queue (i.e. calls that have not yet been answered by an agent). To calculate the waiting time, we simply find the difference between the current time and the time the call was created/started.
- Below this threshold, the cell background of the call waiting in-queue will be green.
- Please input a value in seconds.
- Default Value: 10
Call Waiting Threshold (Level 2)
- This is the final threshold of acceptable waiting time for inbound calls waiting in-queue (i.e. calls that have not yet been answered by an agent). To calculate the waiting time, we simply find the difference between the current time and the time the call was created/started.
- Below this threshold (but above the first threshold), the cell background of the call waiting in-queue will be yellow — above the threshold, the background will be red.
- Please input a value in seconds.
- Default Value: 20
SLA
- This is the general Service Level Agreement (SLA) applied to all Aircall users, which focuses on tracking the average waiting time for every inbound call per Aircall user. Whether the user is or is not meeting the configured SLA is determined by comparing the configured SLA to the average time it takes for the user to answer an inbound call within the specified activity period.
- If the user’s average waiting time (to answer an inbound call) exceeds the configured SLA, the Aircall user’s name is bolded in red.
- Please input a value in seconds. Note that if it is set to 0, this feature is disabled.
- Default Value: 0
Aircall Teams
- If you would like to only view certain Aircall Teams within the Activity Tracker, you can use this parameter to search and select the specific teams you want to monitor. We have also included a special team called “Single Users” which will contain all users who do not belong to any Aircall Team.
- If this field is empty, the Aircall Team filter is disabled.
- Default Value:

The “Aircall Teams” configuration parameter will not appear in the sidebar if you have not yet submitted (configured) an API key via Activity Tracker → Configure.

Clearing the Cache

To improve the performance of the Activity Tracker, we store some information in the Google Apps Script cache so that we do not have to waste time querying Aircall for data via API that does not change often anyways.

These types of “static” data are mainly the list of Aircall users and the relationships between various Aircall objects, such as Users assigned to Teams and Phone Numbers (e.g. users are not often created or deleted, and users do not often change teams or phone number assignments). They are cleared from the cache and refreshed via Aircall API on a much less frequent basis.

However, sometimes there is a need to force the Activity Tracker to refresh this information — for example if a batch of new employees are added as Aircall users to your instance.

If you want to clear the cache in order to force a refresh, select Activity Tracker → Clear Cache from the menu at the top of the spreadsheet.

After you clear the cache, the first run of your Activity Tracker will take some time as it has to refresh more information from your Aircall instance (both dynamic data such as user availabilities/call activities as well as more static data such as the list of users and user-to-team assignments).

You will also need to reconfigure your API key under Activity Tracker → Configure (the key is stored in the cache so that you do not have to input your API key every time you run the Activity Tracker).

Enabling User <> Phone Number Features

Certain features within the Activity Tracker only work if the tool knows which Aircall users are assigned to each Aircall phone number.

User Activity Summary - Missed Calls: Displays the total number of missed calls per user as part of each user’s activity summary (within the activity period)
Inbound Call Waiting - User Highlight: Highlights each user that could potentially pick up an inbound call waiting in-queue

Please remember that these features are only approximations as we make potential inferences based on the assignment of Aircall users to Aircall phone numbers.

To enable these optional features, please select Activity Tracker → Map Users to Numbers from the menu at the top of the spreadsheet.

If you have more than 50 users, this process may take a few minutes to avoid hitting Aircall's API max “queries-per-minute” limit. Generally, you can estimate the time it will take by this formula: ((Total # of Users)/50) x 1 minute

Turning Off Gridlines

To improve the readability of the Activity Tracker on Google Sheets, we recommend turning off the spreadsheet gridlines to clean up the interface.

To disable the gridlines, simply select View from the menu at the top of the spreadsheet and click on the Gridlines option to disable it.

Features

Refreshing the Activity Tracker

Let us now explore all of the features available with our Google Sheets Activity Tracker!

Manual Refresh

To refresh the information displayed on your Activity Tracker, simply select Activity Tracker → Refresh from the menu at the top of the spreadsheet.

Scheduled Refresh

We recommend not scheduling an automatic refresh of the Activity Tracker until you've had a chance to manually use and understand the tool first. Take some time to explore the tracker before enabling this feature.

Using Google Sheets, you can also schedule a refresh of the Activity Tracker — however, the minimum interval is once-per-minute.

To set this, select Tools → Script editor from the menu. You will be taken again to the Google Apps Script Project interface. Select the clock icon on the left side of the UI to go to the Triggers dashboard. On the bottom left-hand corner of the page, click on the “+ Add Trigger” button.

You will be prompted to define a scheduled trigger (i.e. a scheduled execution of code). Copy the values seen in the screenshot and then click the Save button.

Choose which function to run: activityTracker
Choose which deployment should run: Head
Select event source: Time-driven
Select type of time based trigger: Minutes timer
Select minute interval: Every minute

Once it is created, the scheduled trigger will appear in the Triggers dashboard.

If you would like to delete a scheduled refresh, simply delete its associated trigger from the Triggers dashboard.

Please be aware of the performance of the Activity Tracker if it is scheduled for a refresh every minute. Google throttles the resources it dedicates to each Google Sheet, so some refreshes may take <N> seconds while another set of refreshes may take <2 * N> seconds, thus leading to unexpected behaviour.

Scheduling a refresh of the Google Sheets Activity Tracker every minute is best used for smaller Aircall instances (less than a hundred users). However, the scheduled refresh does stabilize in runtime if you set it for every five minutes — implying a 5-min scheduled interval would work even for larger Aircall instances (more than a hundred users).

Reviewing Call History for a Specific User

Imagine a situation where you see that John Doe has made 20 outbound calls within the configured activity period (e.g. the past 15 minutes). As a supervisor, you may want to perform a quick spot-check to review the specifics of the calls John made. Look no further than the User Call History feature.

If you are interested in quickly gathering information about the call activities for a specific user (within the configured activity period), simply select the cell which contains the user’s name. Then select Calls → Select User from the menu at the top of the spreadsheet.

This will open up a sidebar which will contain the metadata of each call the user made (in the form of a JSON object). Although it may look intimidating at first glance, do not worry — if you look closely at the metadata of each call, every line will essentially be split into two components: an attribute of the call and a description of that attribute. For example, you will see an attribute like “direction” with a description like “outbound”. Ultimately, all of these attributes together contextualize the call.

Useful information you will find for each call within the list of attributes:

Call direction [“direction”]
Call duration [“duration”]
Missed call reason (if call was missed) [“missed_call_reason”]
Aircall user involved [“user”]
Aircall phone number involved [“number”]
External phone number involved [“raw_digits”]
Tags [“tags”]
Comments [“comments”]

For an exhaustive list of missed call reasons, please review this Knowledge Base article by clicking here.

Other information you will be able to gather quickly for each call:

Call Recording or Voicemail: If there exists a call recording or voicemail that is less than 10 mins old, we will embed the audio in the sidebar so you can play it
Asset Link Button: Regardless of the age of the call recording or voicemail (if one exists), we provide a button you can click to access the asset securely

Let’s imagine you need to search for a specific attribute description within the list of returned call activities (e.g. you want to search for all calls that were missed due to “no_available_agent”). You can use the quick search feature at the top of the sidebar to dynamically filter out all calls that do not match your search criteria.

We have also included a few other convenient search functions:

Highlight: Click this button to highlight the text (typed into the quick search box) within the list of returned call activities
Auto-Search: Click this button to enable the auto-search feature. Once this is enabled, you can highlight text (with your mouse) within the list of returned call activities which will automatically copy-and-paste the highlighted text into the quick search box to dynamically filter the results

Since the Activity Tracker is not updated in real-time, you may find a slight discrepancy between a User’s Activity Summary and the number of call activities displayed in the search results. For example, a user could have initiated another phone call in-between the time you refreshed the Activity Tracker and the time you used the “User Call History” feature.

Reviewing Call History for All Users

If you are interested in quickly gathering information about the call activities for all users from your Aircall instance (within the configured activity period), simply select Calls → All Users from the menu at the top of the spreadsheet.

This feature is similar to the “User Call History” feature with respect to behaviour and search capabilities.

If you are using an Aircall Team filter (set under Activity Tracker → Configure), the list of returned call activities for this feature will only apply to users that belong to Aircall Teams included in the filter.

Using the Advanced Search for Call History

Let’s imagine as a supervisor, you want to review all call activities within a specific time period (e.g. from April 1st - April 5th) that match a set of search criteria (e.g. outbound calls made by John Doe with tag “Connected”). We have included an “Advanced Search” function that allows you to do just that!

Simply select Calls → Advanced Search from the menu at the top of the spreadsheet. Then click on Configure Search from the sidebar that appears.

Mandatory Search Criteria:
- Activity Period: Use the date picker tool to specify a start date (and time) AND an end date (and time) — the search will not continue until you specify both. Once the date range is chosen, review the text to make sure it represents the activity period you want to search.
Optional Search Criteria:
- Users: Check whether a set of Aircall users were involved with an of the calls (search box will be pre-populated with all the Aircall users from your instance)
- Numbers: Check whether a set of Aircall phone numbers were involved with any of the calls (search box will be pre-populated with all the Aircall phone numbers from your instance
- Call Direction: Check whether the calls were inbound or outbound
- Asset: Check whether the calls have a call recording or voicemail
- Missed Call: Check whether the calls were missed
- Call Duration: Check whether the calls were greater than or less than a specified duration
- Tags: Check whether any of the calls were tagged with a set of Aircall tag names (search box will not be pre-populated)

If you leave a search criteria empty, we remove that criteria from the search altogether.

Furthermore, if you utilize more than one search criteria, they will be “AND”-ed together (implying all the search criteria have to be met for a call activity to be included in the search results).

However, within each search criteria that accepts multiple options (e.g. Users or Tags), each option will be “OR”-ed with the other options from the same criteria (implying that only one of the options have to be found for that specific search criteria to be met).

Switching a User’s Availability Status

In cases where you would like to force-switch an Aircall user’s availability (from unavailable → available or vice versa), we have included a convenient feature that allows you to do this directly from the Activity Tracker.

Simply select the cell of the Aircall user who you would like to switch their availability for and select Activity Tracker → Switch User Status from the menu at the top of the spreadsheet.

If the user is “Unavailable”, it will switch the user’s availability status to “Available”. If the user is “Available”, it will switch the user’s availability status to “Unavailable”.

If the user’s availability status is set to “Auto” within their phone app, this feature will not work.

Tips & Tricks

Performance Factors

In this section, we will discuss a number of considerations that should help smoothen your experience with the Google Sheets Activity Tracker.

The runtime of the Activity Tracker — or how long it takes for a refresh (of Aircall user availabilities and call activities) to complete — will be dependent on these important factors:

Number of Aircall Users & Teams

Since the Activity Tracker gathers the availabilities and call activities for all Aircall Users and organizes the data using all Aircall Teams from your instance, it is logical that an increase in the number of Aircall users and/or Aircall teams will lengthen the time it takes to refresh the data.

Using the Aircall Teams filter (under Activity Tracker → Configure) may help reduce the runtime.

Call Volume

Since the Activity Tracker aggregates and summarizes all the call activities for each Aircall user (within the configured activity period), it is logical that an increase in call volume will have a negative impact on the time it takes to refresh the data.

This will also affect the performance of all of the additional search features available under the Calls menu.

Configured Activity Period

Since the Activity Tracker aggregates and summarizes all the call activities for each Aircall user within the configured activity period (which can be set under Activity Tracker → Configure), it is logical that increasing the length of the activity period will have a negative impact on the refresh time.

This will also affect the performance of some of the additional search features available under the Calls menu.

User & Team Refresh Interval

Since the Activity Tracker refreshes relatively static data (such as user-to-team assignments) less frequently than dynamic data (such as user availabilities), determining an optimal interval to refresh both your Aircall user and teams information will help reduce the average refresh runtime.

Consider how often changes are made to the number of Aircall users and Aircall team membership within your instance (e.g. every day? Every few days? Almost never?) — use this knowledge to help you define an optimal User & Team Refresh Interval (configured under Activity Tracker → Configure).

Scheduled Triggers

If you have enabled Google Apps Script Project Triggers to schedule the refresh of your Activity Tracker every minute, please be aware of how unstable the refresh runtime may be due to Google’s throttling of resources.

For more information, please see the “Scheduled Refresh” section of the tutorial.

If you would like to implement an Activity Tracker that displays more consistent behaviour, please refer to the developer documentation of this tutorial and evaluate the possibility of deploying your own solution on a dedicated Microservice (e.g. Heroku).

Potential Errors

Here are a few common issues with using the Activity Tracker and how to resolve them:

Request Failed: Returned Code 401 or 403

If you receive the following error message while trying to use the Activity Tracker, you either have not set an API key or have not created an API key with a correct format (Base64-encoded).

Exception: Request failed for https://api.aircall.io returned code 403. Truncated server response: {"error":"Forbidden","troubleshoot":"Check your API key"} (use muteHttpExceptions option to examine full response)

Please clear the cache (under Activity Tracker → Clear Cache) and then reconfigure your API key (under Activity Tracker → Configure). If you are unsure about how to create or configure an API key for Aircall, please consult the “Create and Configure API Key” section of the tutorial for guidance.

User ID Non-Existent Using User Call History Feature

This error usually occurs when you try to use the “User Call History” feature and Google is unsure whether you have given the scripts permission to read and manipulate your spreadsheet (hence why it cannot determine the selected Aircall user’s ID which is needed to query Aircall API). Often, this is because you may have more than one Google account that Google recognizes and it is unsure which Google account to use.

To resolve this issue, select Tools → Script editor from the menu at the top of the spreadsheet. You will most likely be prompted to select which Google account you want to sign-in with to the Google Apps Script Project. Make sure you select the Google Account you used to install the scripts for the Activity Tracker.

Wrong Call History Displayed For Selected User

The root cause for this issue is similar to that of the error “User ID Non-Existent Using User Call History Feature”. The resolution will be the same.

Get Started!

You now have all the information you need to play around with our Google Sheets Activity Tracker. Explore the tool and have fun!

Since the scripts are available for you to use and copy, feel free to share them with your developer resources if you have ideas for improvement or customization!

Developer

Our customers often have the need to extend Aircall’s native Activity Feed to meet their specific requirements for a customized holistic view of all Aircall user availabilities and call activities.

This wallboard, or Activity Tracker, should organize information into groups (e.g. users per team or users per number) and should display various KPIs for each Aircall user, providing supervisors and team leads a high-level perspective of their agents’ performance in a single glance.

Table of contents

Scope & Objectives
Fundamental Concepts
Preparation
- General Architecture
- Important API Endpoints
Technical Steps
Tips and Tricks
- Performance Considerations
- Troubleshooting
Conclusion

Scope & Objectives

The scope of this tutorial will primarily focus on helping you understand how to build a custom Activity Tracker that refreshes information in short intervals, so this type of implementation will not support real-time updates.

This method was chosen because it requires the simplest setup and involves less complex logic to organize (and store) information about activities, availabilities, and their relationship with each Aircall user.

In addition, before you begin your project, ensure that you have gathered the necessary requirements that will help you accurately define what a functional (and useful) Activity Tracker should look like. Below are a few questions that should be answered by the business users:

How should Aircall users be organized on the wallboard?
- Should they be grouped under their respective Aircall teams?
- Should they be grouped under their assigned Aircall phone numbers?
- Should they be organized according to an internal company group definition?
What type of activities should the wallboard display?
- Should we display each user’s current availability status?
- Should we display it in text? Should we use color codes (e.g. green for “available”, red for “do not disturb”)?
- Should we display a summary of call activities (i.e. KPIs) per user? Per phone number? Per team?
- Which KPIs should we focus on (e.g. total number of inbound vs. outbound calls)?
  - Avoid trying to present too much information on the wallboard so as to prevent clutter
- Should we define thresholds for certain KPIs and highlight them when they are exceeded (e.g. average call duration > 1 minute)?
- How long should the activity period be from which to aggregate call activities (e.g. the past 15 minutes)?
  - Take into consideration performance — the longer the defined activity period, the longer the application will take to inspect and organize the information
How often should the wallboard be refreshed?
- What is the maximum amount of time we can wait for an update of information (e.g. user availability, call activities, etc.) before the wallboard loses relevancy?
- This of course has performance implications depending on the refresh frequency

Fundamental Concepts

API Calls

Aircall Public API is a classic REST API. It can be used with any programming language and offers fast, reliable, and secure access to information within an Aircall instance. We will be using the API to gather user availabilities and call activities on a scheduled basis.

The root endpoint of Aircall Public API is https://api.aircall.io/v1.

Please note that only HTTPS requests are valid as requests will communicate over SSL connection. Each Public API request must be authenticated and should not exceed the rate limit, so please check the ‘Authentication’ and ‘Rate Limiting’ sections in our public documentation.

To find or create your API ID and API Token (which you will need to access our API endpoints), please navigate to the Account section of your Aircall dashboard.

Want to learn more? Aircall provides public documentation to help developers understand and use our public API and webhook events. We also provide various guides and tutorials which can be used as references to get you started with your projects. For more info, click here.

Aircall User Availabilities

Aircall users, at any given time, can be in one of the following availability statuses:

offline: Aircall user is disconnected from the phone app (i.e. logged out)
available: Aircall user is connected to the phone app (i.e. logged in) and is able to receive phone calls
do_not_disturb: Aircall user is connected to the phone app (i.e. logged in) but is unavailable and unable to receive phone calls
in_call: Aircall user is currently on a phone call (and also unable to receive other phone calls)
after_call_work: Aircall user has finished a call and is in “wrap-up” time (if enabled) or “mandatory call-tagging” time (if enabled)

Please keep this in mind when building your own custom Activity Tracker.

For more information about availability statuses, please consult this Knowledge Base article.

Aircall Call Statuses (via API)

When querying for a list of calls via Aircall’s API, each call object returned — in JSON format — will contain (where N = the Nth call in the response payload):

A call ID found under calls[N].id
A specific call status found under calls[N].status
A missed call reason (if the call was missed) found under calls[N].missed_call_reason

Each call queried by Aircall’s API will have a unique entry in the payload — identified by the call ID — which will be used to find and update the call status as that specific call’s journey progresses.

For example, when an inbound call (with call ID “1234”) begins and is ringing on an agent, its initial status would be “initial”. If you were to query via Aircall’s API again and that call was now answered, the same inbound call (identified by call ID “1234”) would now have status set to “answered”.

In general, there are three main statuses an Aircall call can be in:

initial: The call is ringing and has not been answered yet
- In this state...
- calls[N].user will be null if it is an inbound call, or populated with the Aircall user involved if it is an outbound call
- calls[N].number will be populated with the Aircall phone number the call is ringing on or from
answered: The call has been answered (but is not finished yet)
- In this state...
- calls[N].user will be populated with the Aircall user involved with the call
- calls[N].number will be populated with the Aircall phone number the user answered on
done: The call is completed
- If call was not missed, in this state...
- calls[N].user will be populated with the Aircall user involved with the call
  - If the call was transferred, this attribute will be populated with the last Aircall user to receive the transfer (you would also find the same user object under calls[N].transferred_to)
- calls[N].number will be populated with the Aircall phone number the user answered on or called from
- calls[N].missed_call_reason will be null
- If call was missed, in this state...
- calls[N].user will be null
- calls[N].number will be populated with the Aircall phone number where the call was missed
- calls[N].missed_call_reason will be populated with the reason

For an exhaustive list of missed call reasons, please consult this Knowledge Base article.

Preparation

General Architecture

To create your Activity Tracker, you will need to setup several important components for the overall architecture:

A back-end application to manage logic of mapping activities to users

On a scheduled basis (e.g. once every 30s), request/query the Aircall Public API to gather user activity and availability within a period of time
- Process the queried data and determine which activities and availability is associated with a specific user
On a scheduled basis (e.g. once every few hours), make calls using Aircall Public API to refresh IDs (for users, teams, and phone numbers) and relationships between Aircall objects
- Store this information in data repository, which can be accessed in the future
- Optimizes performance of the application and reduces the number of Aircall API calls

A data repository to store relationships between various Aircall objects

Helps reduce the number of Aircall API calls and improves performance
Store User ID-to-User Name mappings (1 : 1)
Store Team ID-to-Team Name mappings (1 : 1)
Store User ID-to-Phone Number ID mappings (1 : Many)
Store User ID-to-Team ID mappings (1 : Many)
Store Number ID-to-Number Name mappings (1 : 1)

A front-end application to manage the UI

Communicates with the back-end application to collect Aircall activity information per user
Displays user activity on a wallboard, which can be grouped/organized according to your needs (e.g. users per team, users per phone number, etc.)

Important API Endpoints

Before starting, we must first understand which Aircall API endpoints are going to be the most useful for this project.

Setup & Object Relationships

Within the majority of Aircall API responses, you will find objects (e.g. users, teams, and phone numbers) identified by a unique ID. This ID, although not human-interpretable, will be used to create relationships between Aircall objects.

Both these IDs and relationships will help your application setup the foundation that will allow you to properly organize availability statuses and call activity data for every Aircall user.

Since these IDs and relationships will most likely not change very often, it is recommended to schedule a refresh of this information less frequently (e.g. once every few hours).

List All Users

[GET]    https://api.aircall.io/v1/users

This endpoint will retrieve all Aircall users from your instance. The most important information you will need to collect per user are:

users[N].id: This is the ID which uniquely identifies the user and is often used for relationship management
users[N].name: This is the user’s display name which is human-interpretable (will likely be the text you display on the Activity Tracker)

To reduce the total number of API calls, it is recommended to store user ID-to-username mappings (1 : 1) in your internal repository for later use.

Sample API JSON Response Payload


json

{
   "meta": {
      "count": 3,
      "total": 3,
      "current_page": 1,
      "per_page": 20,
      "next_page_link": null,
      "previous_page_link": null
   },
   "users": [
      {
         "id": 456,
         "direct_link": "https://api.aircall.io/v1/users/456",
         "name": "John Doe",
         "email": "john.doe@aircall.io",
         "available": true,
         "availability_status": "available",
         "created_at": "2019-12-29T10:03:18.000Z",
         "time_zone": "America/New_York",
         "language": "en-US"
      },
      {
         "id": 457,
         "direct_link": "https://api.aircall.io/v1/users/457",
         "name": "Amy Rudd",
         "email": "amy.rudd@aircall.io",
         "available": true,
         "availability_status": "custom",
         "created_at": "2019-12-30T18:10:21.000Z",
         "time_zone": "Etc/UTC",
         "language": "en-US"
      },
      {
         "id": 458,
         "direct_link": "https://api.aircall.io/v1/users/458",
         "name": "Vera Martin",
         "email": "vera.martin@aircall.io",
         "available": false,
         "availability_status": "unavailable",
         "created_at": "20120-01-02T09:01:58.000Z",
         "time_zone": "Europe/Paris",
         "language": "fr-FR"
      }
   ]
}

List all Teams

[GET]    https://api.aircall.io/v1/teams

If you are interested in organizing your users into Aircall teams on the Activity Tracker, this endpoint will retrieve all Aircall teams from your instance.

The most important information you will need to collect per team are:

teams[N].id: This is the ID which uniquely identifies the team
teams[N].name: This is the team's display name which is human-interpretable (will likely be the text you display on the Activity Tracker)
teams[N].users: This is an array of JSON objects, where each object represents an Aircall user that is part of that team

To reduce the total number of API calls, it is recommended to store team ID-to-team name (1 : 1) and user ID-to-team ID mappings (1 : Many) in your internal repository for later use.

Aircall users that do not belong to any Aircall team will not be included in this API response payload.

Sample API JSON Response Payload


json

{
   "meta": {
      "count": 1,
      "total": 1,
      "current_page": 1,
      "per_page": 20,
      "next_page_link": null,
      "previous_page_link": null
   },
   "teams": [
      {
         "id": 678,
         "name": "Global Sales",
         "direct_link": "https://api.aircall.io/v1/teams/678",
         "created_at": "2020-03-10T08:31:43.000Z",
         "users": [
            {
               "id": 456,
               "direct_link": "https://api.aircall.io/v1/users/4c56",
               "name": "John Doe",
               "email": "john.doe@aircall.io",
               "available": true,
               "availability_status": "available",
               "created_at": "2019-12-29T10:03:18.000Z",
               "time_zone": "America/New_York"
            },
            {
               "id": 457,
               "direct_link": "https://api.aircall.io/v1/users/457",
               "name": "Amy Rudd",
               "email": "amy.rudd@aircall.io",
               "available": true,
               "availability_status": "custom",
               "created_at": "2019-12-30T18:10:21.000Z",
               "time_zone": "Etc/UTC"
            }
         ]
      }
   ]
}

Retrieve a User

[GET]    https://api.aircall.io/v1/users/

If you are interested in organizing your users per Aircall phone number (which they are assigned to) on the Activity Tracker, this endpoint will retrieve more detailed information about a specific Aircall user.

The most important information you will need to collect for a user are:

user.id: This is the ID which uniquely identifies the user
user.numbers: This is an array of JSON objects, where each object represents an Aircall phone number this user is assigned to

To reduce the total number of API calls, it is recommended to store user ID-to-number ID mappings (1 : Many) in your internal repository for later use.

Since this endpoint can only be used on a “per-user” basis, you will have to call this endpoint N times (where N is the number of Aircall users in your instance) to retrieve each user’s assigned Aircall phone numbers.

It will most likely be used in conjunction with the “List All Users” API endpoint as you iterate through each Aircall user.

Please keep in mind the API rate limit-per-minute if you plan on using this endpoint to determine user-to-phone number assignments.

Sample API JSON Response Payload


json

{
   "user": {
      "id": 456,
      "direct_link": "https://api.aircall.io/v1/users/456",
      "name": "John Doe",
      "email": "john.doe@aircall.io",
      "available": true,
      "availability_status": "available",
      "created_at": "2019-12-29T10:03:18.000Z",
      "time_zone": "America/New_York",
      "language": "en-US",
      "numbers": [
         {
            "id": 1234,
            "direct_link": "https://api.aircall.io/v1/numbers/1234",
            "name": "French Office",
            "digits": "+33 1 76 11 11 11",
            "created_at": "2020-01-02T11:41:01.000Z",
            "country": "FR",
            "time_zone": "Europe/Paris",
            "open": true,
            "availability_status": "custom",
            "is_ivr": true,
            "priority": null,
            "live_recording_activated": true,
            "messages": {
               "welcome": "https://example.com/welcome.mp3",
               "waiting": "https://example.com/waiting_music.mp3",
               "ivr": "https://example.com/ivr_message.mp3",
               "voicemail": "https://example.com/voicemail.mp3",
               "closed": "https://example.com/closed_message.mp3",
               "callback_later": "https://example.com/callback_later.mp3",
               "unanswered_call": "https://example.com/unanswered_call.mp3",
               "after_hours": "https://example.com/closed_message.mp3",
               "ringing_tone": "https://example.com/waiting_music.mp3"
            }
         }
      ]
   }
}

List All Numbers

[GET]    https://api.aircall.io/v1/numbers

If you are interested in organizing your users per Aircall phone number (which they are assigned to) on the Activity Tracker, this endpoint will retrieve all the Aircall phone numbers in your Aircall instance.

The most important information you will need to collect for a user are:

numbers[N].id: This is the ID which uniquely identifies the Aircall phone number
numbers[N].name: This is the name of the Aircall phone number (easier to remember than the digits themselves)

To reduce the total number of API calls, it is recommended to store number ID-to-number name mappings (1 : 1) in your internal repository for later use.

Sample API JSON Response Payload


json

{
   "meta": {
      "count": 3,
      "total": 2,
      "current_page": 1,
      "per_page": 20,
      "next_page_link": null,
      "previous_page_link": null
   },
   "numbers": [
      {
         "id": 1234,
         "direct_link": "https://api.aircall.io/v1/numbers/1234",
         "name": "French Office",
         "digits": "+33 1 76 11 11 11",
         "created_at": "2020-01-02T11:41:01.000Z",
         "country": "FR",
         "time_zone": "Europe/Paris",
         "open": true,
         "availability_status": "custom",
         "is_ivr": true,
         "live_recording_activated": true,
         "priority": null,
         "messages": {
            "welcome": "https://example.com/welcome.mp3",
            "waiting": "https://example.com/waiting_music.mp3",
            "ivr": "https://example.com/ivr_message.mp3",
            "voicemail": "https://example.com/voicemail.mp3",
            "closed": "https://example.com/closed_message.mp3",
            "callback_later": "https://example.com/callback_later.mp3",
            "unanswered_call": "https://example.com/unanswered_call.mp3",
            "after_hours": "https://example.com/closed_message.mp3",
            "ringing_tone": "https://example.com/waiting_music.mp3"
         }
      },
      {
         "id": 2345,
         "direct_link": "https://api.aircall.io/v1/numbers/2345",
         "name": "UK office",
         "digits": "+44 20 0000 0000",
         "created_at": "2020-01-04T19:28:58.000Z",
         "country": "GB",
         "time_zone": "Europe/London",
         "open": true,
         "availability_status": "custom",
         "is_ivr": true,
         "live_recording_activated": false,
         "priority": null,
         "messages": {
            "welcome": "https://example.com/welcome.mp3",
            "waiting": "https://example.com/waiting_music.mp3",
            "ivr": "https://example.com/ivr_message.mp3",
            "voicemail": "https://example.com/voicemail.mp3",
            "closed": "https://example.com/closed_message.mp3",
            "callback_later": "https://example.com/callback_later.mp3",
            "unanswered_call": "https://example.com/unanswered_call.mp3",
            "after_hours": "https://example.com/closed_message.mp3",
            "ringing_tone": "https://example.com/waiting_music.mp3"
         }
      }
   ]
}

User Availabilities

In order to retrieve the current availability statuses for every Aircall user (one status per user), you will need to leverage another set of endpoints.

Since these availabilities are much more dynamic and subject to change often, it is recommended to collect this information in short intervals (e.g. once every 30s).

Retrieve List of Users Availability

[GET]    https://api.aircall.io/v1/users/availabilities

This endpoint will retrieve all the availability statuses for each Aircall user in your instance.

The most important information you will need to collect per user are:

users[N].id: This is the ID which uniquely identifies the user
users[N].availability: This is the user’s current availability (will likely be the status you display on the Activity Tracker)

Sample API JSON Response Payload


json

{
   "meta": {
      "count": 3,
      "total": 3,
      "current_page": 1,
      "per_page": 20,
      "next_page_link": null,
      "previous_page_link": null
   },
   "users": [
      {
         "id": 456,
         "availability": "available"
      },
      {
         "id": 457,
         "availability": "offline"
      },
      {
         "id": 458,
         "availability": "do_not_disturb"
      }
   ]
}

Phone Call Activities

If you are also interested in showing user call activities for a specific period of time in the Activity Tracker (e.g. number of inbound vs. outbound calls per user in the past 15 minutes), you will need to leverage another set of endpoints.

Since these call activities are much more dynamic and subject to change often, it is recommended to collect this information in short intervals (e.g. once every 30s).

List all Calls

[GET]    https://api.aircall.io/v1/calls?order=desc&per_page=50&from=&to=

This endpoint will retrieve all the phone call activities from all Aircall users between a start date and an end date (in descending order).

The start date and end date must be converted to the UNIX Epoch Time format (in seconds).

In addition, you can only retrieve up to 10,000 call activities (even with pagination turned on). To surpass this limit, you will need to make a separate API call with another “from/to” date range.

The most important information you will need to collect per call are:

calls[N].id: This is the ID which uniquely identifies this specific call
- This ID will only ever appear once in the payload — this implies that this specific entry in the payload (representing the call) will be updated whenever there is an update to the same call (e.g. call ends and duration must be updated, etc.)
calls[N].direction: Displays the direction of the call (inbound vs. outbound)
calls[N].status: The current state of the call along the call journey
calls[N].missed_call_reason: If the call was missed, will display the reason
calls[N].started_at: Timestamp of start of call
calls[N].answered_at: Timestamp of call when it was answered by an agent
- Will be null if call was missed or not answered yet
- Ringing/waiting time can be calculated using: started_at - answered_at
calls[N].ended_at: Timestamp of call when it ended
- Will be null if call has not ended yet
- Talking time can be calculated using: answered_at - ended_at
calls[N].duration: The length of the call (in seconds) from start-to-end (ended_at - started_at)
calls[N].raw_digits: The external phone number associated with the call
calls[N].user: A JSON object that represents the Aircall user associated with this call (i.e. either the agent that picked up the inbound call or initiated the outbound call)
- Will be null if the call was missed or has not been answered yet
calls[N].number: A JSON object that represents the Aircall phone number associated with the call

As you can see, this payload contains a plethora of information that can be used to summarize call activities and calculate KPIs for a specific user, which could then be displayed accordingly in the Activity Tracker.

Sample API JSON Response Payload


json

{
   "meta": {
      "count": 50,
      "total": 2234,
      "current_page": 1,
      "per_page": 50,
      "next_page_link": "https://api.aircall.io/v1/calls?order=desc&page=2&per_page=50&from=&to=",
      "previous_page_link": null
   },
   "calls": [
      {
         "id": 111111234,
         "direct_link": "https://api.aircall.io/v1/calls/111111234",
         "direction": "outbound",
         "status": "answered",
         "missed_call_reason": null,
         "started_at": 1619005321,
         "answered_at": 1619005327,
         "ended_at": null,
         "duration": 0,
         "voicemail": null,
         "recording": null,
         "asset": null,
         "raw_digits": "+33 6 11 11 33 33",
         "user": {
            "id": 123456,
            "direct_link": "https://api.aircall.io/v1/users/123456",
            "name": "Test User",
            "email": "test.user@aircall.io",
            "available": true,
            "availability_status": "available",
            "created_at": "2020-10-14T08:31:52.000Z",
            "time_zone": "Europe/Paris",
            "language": "fr-FR"
         },
         "contact": null,
         "archived": false,
         "assigned_to": null,
         "tags": [],
         "transferred_to": null,
         "teams": [],
         "number": {
            "id": 99991,
            "direct_link": "https://api.aircall.io/v1/numbers/99991",
            "name": "Support Hotline",
            "digits": "+33 1 77 11 22 33",
            "country": "FR",
            "time_zone": "Europe/Paris",
            "open": true,
            "availability_status": "custom",
            "is_ivr": false,
            "live_recording_activated": true,
            "priority": 0,
            "messages": {
               "welcome": "https://example.com/welcome.mp3",
               "waiting": "https://example.com/waiting_music.mp3",
               "ivr": "https://example.com/ivr_message.mp3",
               "voicemail": "https://example.com/voicemail.mp3",
               "closed": "https://example.com/closed_message.mp3",
               "callback_later": "https://example.com/callback_later.mp3",
               "unanswered_call": "https://example.com/unanswered_call.mp3",
               "after_hours": "https://example.com/closed_message.mp3",
               "ringing_tone": "https://example.com/waiting_music.mp3"
            },
            "created_at": "2016-07-05T17:04:02.000Z"
         },
         "cost": "0.0",
         "comments": []
      },
      ...
   ]
}

Technical Steps

In this section, we will walk through the technical steps and logic of how to populate the Activity Tracker. This will generally involve querying our internal data repository for Aircall object relationships and ID assignments, as well as leveraging Aircall API to collect and organize both Aircall user availabilities and call activities within the specified activity period.

Schedule refresh of IDs and relationships between Aircall objects (e.g. users, teams, phone numbers, etc.) These relationships most likely do not change often (relatively static), so refreshing this information should be done occasionally (e.g. once very few hours)
- Leverage the API endpoints found in the “Setup & Object Relationships” section to collect information about Aircall object IDs and their relationships between each other
  - GET Request to https://api.aircall.io/v1/users
  - GET Request to https://api.aircall.io/v1/teams
  - GET Request to https://api.aircall.io/v1/users/
  - GET Request to https://api.aircall.io/v1/numbers
Store IDs and relationships between Aircall objects in an internal data repository for future queries, which will help reduce the total number of API calls to Aircall and improve performance
- This metadata will be queried later when user availability and call activity data needs to be organized for the wallboard
  - User ID-to-Username mappings (1 : 1)
  - User ID-to-Team ID mappings (1 : Many)
  - Only required if you plan on organizing your wallboard using Aircall Teams
  - User ID-to-Number ID mappings (1: Many)
  - Only required if you plan on organizing your wallboard using Aircall Phone Numbers
  - Team ID-to-Team Name mappings (1 : 1)
  - Only required if you plan on organizing your wallboard using Aircall Teams
  - Number ID-to-Number Name mappings (1 : 1)
  - Only required if you plan on organizing your wallboard using Aircall Phone Numbers
Schedule API call to retrieve most up-to-date information about Aircall user availability and call activities. This information is dynamic, so these API calls should be scheduled much more frequently (e.g once every 30s)
- Leverage the API endpoints found in the “User Availabilities” and “Phone Call Activities” sections to collect this information
  - GET Request to https://api.aircall.io/v1/users/availabilities
  - GET Request to https://api.aircall.io/v1/calls?order=desc&per_page=50&from=&to=
  - API call will return all Aircall user call activities within the specified activity period (e.g. past 15 minutes), as well as the current availability statuses of each user
Organize this information by associating call activities and availability status with each Aircall user
- Query to internal data repository will be needed to determine relationships between Aircall objects and IDs
- Groupings can also be implemented (e.g. organize users per team) This step mainly consists of implementing the logic that will ultimately determine how all of this information will be organized and correlated for the Activity Tracker. For your reference, we have provided pseudo-code logic to demonstrate one way of accomplishing this: Retrieve User ID-to-Username mappings from internal data repository

A. Retrieve User ID-to-Username mappings from internal data repository

// Create data struct
userdb = {};

// Retrieve user ID-to-username mappings from internal data repository
db_response = database.query("SELECT ... FROM ... WHERE ...");

// Iterate over each row returned from query...
db_response.foreach((row) => {
   // Key: User ID
   // Value: Username
   userdb[row[0]] = row[1];
});

B. Retrieve the User ID-to-Number ID mappings from internal data repository

// Create data struct
user_numbersdb = {};

// Retrieve user ID-to-number ID mappings from internal data repository
db_response = database.query("SELECT ... FROM ... WHERE ...");

// Iterate over each row returned from query...
db_response.foreach((row) => {
   // Key: User ID
   // Value: Dictionary of Number IDs
   user_numbersdb[row[0]] = {};

   // Create an array of all the numbers assigned to this user ID
   user_numbers = row[1].split(",");

   // Iterate over each number...
   user_numbers.foreach((numberID) => {
      // Key: Number ID
      // Value: 
      user_numbersdb[row[0]][numberID] = "";
   });
});

C. Retrieve Team ID-to-Team Name mappings from internal data repository (optional if you want to organize your Activity Tracker by teams)

// Create data struct
teamsdb = {};

// Retrieve team ID-to-team name mappings from internal data repository
db_response = database.query("SELECT ... FROM ... WHERE ...");

// Iterate over each row returned from query...
db_response.foreach((row) => {
   // Key: Team ID
   // Value: Team Name
   teamsdb[row[0]] = row[1];
});

D. Retrieve the User ID-to-Team ID mappings from internal data repository (optional if you want to organize your Activity Tracker by teams)

If you want to keep track of Aircall users who are not part of any Aircall team, ensure you create a “pseudo-team” to keep track of these single users.

// Create data struct
user_teamsdb = {};

// Retrieve user ID-to-team ID mappings from internal data repository
db_response = database.query("SELECT ... FROM ... WHERE ...");

// Iterate over each row returned from query...
db_response.foreach((row) => {
   // Key: User ID
   // Value: Dictionary of Team IDs
   user_teamsdb[row[0]] = {};

   // Create an array of all the teams assigned to this user ID
   user_teams = row[1].split(",");

   // Iterate over each team...
   user_teams.foreach((teamID) => {
      // Key: Team ID
      // Value: 
      user_teamsdb[row[0]][teamID] = "";
   });
});

E. Retrieve Number ID-to-Number Name mappings from internal data repository (optional if you want to organize your Activity Tracker by phone numbers)

// Create data struct
numbersdb = {};

// Retrieve number ID-to-number name mappings from internal data repository
db_response = database.query("SELECT ... FROM ... WHERE ...");

// Iterate over each row returned from query...
db_response.foreach((row) => {
   // Key: Number ID
   // Value: Number Name
   numbersdb[row[0]] = row[1];
});

F. Create empty data structure (e.g. nested dictionary) that will keep track of each user’s current availability, as well as a summary of call activity KPIs for each Aircall user (e.g. total # of inbound/outbound calls, average call duration, etc.)

G. Create empty data structure (e.g. nested dictionary) that will keep track of all inbound calls waiting in-queue

H. Iterate/loop over the API response of all call activities (from the specified activity period)

// Start date and end date in UNIX Epoch Time format (seconds)
end_date = currentTime(); // NOW
start_date = currentTime() - 54000; // 15 MINUTES AGO

// Form the API endpoint URL
calls_url = “https://api.aircall.io/v1/calls?order=desc&per_page=50&from=” +  
   start_date + “&to=” + end_date;

// Get the response
calls_response = urlapp.fetch(calls_url, get_params);
calls_data = JSON.parse(calls_response.getContent());

// For each call...
for (i = 0; i < calls_data.calls.length; i++) {
   // Determine the potential length of the call
   calltime = currentTime() - calls_data.calls[i].answered_at;

   // Determine the potential waiting time of the call
   waittime = currentTime() - calls_data.calls[i].started_at;

   // If the call is still active (user is on a call)...
   if (calls_data.calls[i].status == "answered") {
      user_summary[calls_data.calls[i].user.id]["current_call"] = {
         "duration": calltime,
         "external_phonenum": calls_data.calls[i].raw_digits,
         "direction": calls_data.calls[i].direction,
         "aircall_phonenum": calls_data.calls[i].number.id
      };

   // ...else, if the call is waiting in-queue...
   } elsif (calls_data.calls[i].status == "initial") {
      calls_waiting[calls_data.calls[i].number.id] = {
         "external_phonenum": calls_data.calls[i].raw_digits,
         "wait_time": waittime,
         "aircall_phonenum": calls_data.calls[i].number.id
      };

   // ...else, if the call is done...
   } elseif (calls_data.calls[i].status == "done") {

      // If the call had a user involved (i.e. not missed)...
      if (calls_data.calls[i].user) {
         user_summary[calls_data.calls[i].user.id][calls_data.calls[i].direction]++;
         user_summary[calls_data.calls[i].user.id]["avg_duration"] +=
            calls_data.calls[i].duration;

      // ...else, if the call was missed...
      } else if (calls_data.calls[i].missed_call_reason) {
         // Do something
      }
   }
}

I. Iterate/loop over the API response of all Aircall users’ current availabilities

// Form the API endpoint URL
avail_url = “https://api.aircall.io/v1/users/availabilities”

// Get the response
avail_response = urlapp.fetch(avail_url, get_params);
avail_data = JSON.parse(avail_response.getContent());

// For each user availability...
for (i = 0; i < avail_data.users.length; i++) {
   userid = avail_data.users[i].id;
   userstatus = avail_data.users[i].availability;

   user_summary[userid]["availability"] = userstatus;
}

J. Populate data structure (e.g. nested dictionary) that will generally represent what your Activity Tracker will look like when organized

// For each team in the Aircall instance...
teamsdb.foreach((teamid) => {
   // Add an entry in the activity tracker data struct
   activity_tracker[teamid] = {};
});

// For each Aircall user...
user_teamsdb.foreach((userid) => {
   // Retrieve all the teams this user is part of...
   user_teams = user_teamsdb[userid];

   // For each team this user is part of...
   user_teams.foreach((teamid) => {
      // Add the user's summary of call activities and availability status
      // to the activity tracker under that team
      activity_tracker[teamid][userid] = user_summary[userid];
   });
});

Display the organized activity and availability information per user on the wallboard
- At this step, we have all the appropriate data structures to logically organize and display information on the wallboard
  - <activity_tracker>: Generally contains all of our Activity Tracker information, organized in groups (either by Aircall team or phone number) per Aircall user
  - <calls_waiting>: Use this data structure if you would like to display a “live” queue (of inbound calls waiting to be picked-up)
  - <userdb>: Translates User IDs to Usernames
  - <teamsdb>: Translates Team IDs to Team Names
  - <numbersdb>: Translates Number IDs to Number Names

Tips and Tricks

Performance Considerations

Refresh Intervals

Not everything needs to be refreshed (scheduled via Aircall API) in short intervals — as mentioned previously, more static data that does not change often (such as the user ID-to-username or user ID-to-team ID mappings) should be scheduled for a refresh every few hours, while dynamic data (such as current user availabilities and call activities) should be scheduled for a refresh much more frequently.

Any static data, such as the relationships between various Aircall objects, should be stored locally to optimize the performance of your Activity Tracker.

Activity Period

When choosing an activity period from which to aggregate and summarize call activities from (e.g. the past 15 minutes), please bear in mind its impact on how long it will take for your Activity Tracker to refresh.

Depending on the amount of Aircall calls made and received (per user per minute) and the complexity of your application’s logic to inspect and organize all call activities (as well as calculate KPIs), increasing the activity period even by five minutes may have a noticeable effect on the performance of your Activity Tracker.

Calculating KPIs

Of course, the more KPIs that you calculate (per user, number, team, etc.), the longer it will take for your Activity Tracker to refresh.

In addition, we suggest to maintain a balance between the information you choose to display and the usability of your Activity Tracker: increasing the number of KPIs you display will keep you and your team more informed about Aircall activity at-a-glance, but there is a threshold where the amount of information displayed begins to clutter the wallboard (making it difficult to quickly find the data you want) and prolongs the refresh for minimal benefit.

Rate Limiting

Be aware that the maximum number of API calls your company can make per minute is 60*. Hence, we have continuously recommended to locally store as much static data from Aircall as possible, and to only refresh this data (via Aircall API) every so often.

Please also keep in mind that if you want to map User IDs to their assigned Phone Number IDs and have more than 50 users in your Aircall instance, you most likely will need to introduce a waiting/sleeping period in-between API calls — this is because you have to make an API call per user to determine which phone numbers they are assigned to. At this moment, there is no “batch” request to collect this information all at once.

Please consult our developer portal for more information about rate limiting. For more info, click here.

*If you have a special use case that requires an increase in the general API rate limit, please speak with your Aircall representative or Aircall Support.

Troubleshooting

Null Objects

When working with Aircall’s API responses for querying call activities, you are most likely going to be using the Aircall user object included in the payload.

Remember that the user object (e.g. calls[N].user) will be null when...

The call has not been answered yet (i.e. calls[N].status == “initial”)
The call was missed (i.e. calls[N].status == “done” and i.e. calls[N].missed_call_reason is not null)

On that note, the “missed call reason” (i.e. calls[N].missed_call_reason) will of course be null if the call was actually answered by an agent. With respect to a call’s journey...

The calls[N].answered_at attribute will be null if the call has not been answered yet or if the call was missed
The calls[N].ended_at attribute will be null if the call has not ended yet

Conclusion

You should now have all the technical knowledge you need to begin scoping and building your own custom Activity Tracker using Aircall Public API!

If you are interested in an example of a custom Activity Tracker that was built on-top of Google Sheets, please consult the following section of the tutorial.

We wrote other tutorials to help you out
building awesome integrations

Discover them now