Retrieving Data via S3

ASAPP customers can securely retrieve application data for their internal consumption from ASAPP via S3, using credentials dedicated to this download function.

The following provides details on how you can retrieve data from ASAPP.

• Data Retrieval Prerequisites: Describes prerequisites for secure data exchange. This includes key exchange and authentication, as well as ASAPP’s standards for bucket naming and S3 folder structure.
• Downloading Data from S3: Describes ASAPP’s conventions for constructing an S3 path to exported data and some general data formatting conventions for ASAPP exports. Additionally, provides a sample download script.

​

Data Retrieval Prerequisites

ASAPP will share data with you via a set of S3 buckets. These S3 buckets will be encrypted at rest using AES256 and access-protected behind AWS authentication.

​

Authentication Credentials

Each run environment (typically preprod and prod) will be allocated a dedicated set of AWS credentials that are authorized to read from that environment’s respective S3 endpoint. These credentials will be different from other AWS credentials ASAPP might have provided you.

ASAPP will provide credentials to you within a GPG encrypted file. To initiate the credential exchange, you must provide ASAPP with a public GPG key that we may use to encrypt the files containing your credentials. It’s safe to send your public GPG key to ASAPP using any available channel. Please do NOT provide ASAPP with your private GPG key.

GitHub provides one of many good available tutorials on GPG key generation here: https://help.github.com/en/articles/generating-a-new-gpg-key .

Once you’ve provided ASAPP with your public GPG key, we will provide your credentials in a file hosted on S3 that you will retrieve using an expiring https link. ASAPPs standard practice is to have those links expire after 24 hours.

The file will be encrypted using your public GPG key. Once you decrypt the provided file using your private GPG key, your credentials will be contained within a tab delimited file with the following structure:

id     secret      bucket     sub-folder (if any)

​

Your Dedicated S3 Buckets

Once you’ve securely received credentials that allow you to read from your dedicated S3 buckets, ASAPP will provide you with URIs representing the set of S3 buckets from which you may securely download data files.

ASAPP can make a broad range of data available to you. You’ll work with your Customer Success and Implementation teams to schedule specific feeds for export.

Each separate feed will have a unique S3 endpoint and, potentially, a unique strategy for data partitioning. While the specific details vary according to the feed at hand, ASAPP follows a consistent set of conventions for naming S3 buckets, paths, and partitions.

The following provides details about the conventions.

​

Bucket Naming

For clarity, ASAPP names buckets using the following convention:

s3://asapp-\{env\}-\{company_name\}-exports-\{aws-region\}

For example, an S3 bucket set up to host exports of pre-production data from ACME would be named:

s3://asapp-preprod-acme-exports-us-east-1

​

Folder Structure

Within the named bucket, ASAPP uses the following conventions to construct an S3 path that corresponds to the name of the exported feed and to partitions of the exported feed:

s3://BUCKET_NAME/FEED_NAME/version=VERSION_NUMBER/format=FORMAT_NAME/ dt=DATE/hr=HOUR/mi=MINUTE/DATAFILE(S)

​

File Naming

File names that correspond to an exported feed partition will have names in the following form:

\{FEED_NAME\}\{FORMAT\}\{SPLIT_NUMBER\}.\{COMPRESSION\}.\{ENCRYPTION\}

​

Downloading Data from S3

​

General Data Formatting Notes

• ASAPP export files are UTF-8 encoded.
• Control characters are escaped.
• Files are formatted with Unix-style line endings.

​

Verifying the Data Export is Complete

Upon completing the upload for a particular partition, ASAPP will upload an EMPTY file named _SUCCESS to the same path as the uploaded file or files. This _SUCCESS file acts as a flag indicating that the upload for the associated partition is complete. A _SUCCESS file will be written even if there is no available data selected for export for the partition at hand.

Until the _SUCCESS file is uploaded, ASAPP’s export/upload is in progress and you should not import the associated data file. You should check for this file before downloading any data partition.

​

Download Example

The example below assumes a shell terminal with python 2.7+ installed.

# install aws cli (assumes python)
pip install awscli
# configure your S3 credentials if not already done
aws configure 
# pull down all the files for 2019-01-20 for the convos_delta_ended export
# for a company named `acme` to your local drive in pre-production
aws s3 cp --recursive s3://asapp-preprod-acme-exports-us-east-1/convos_delta_ended/version=1/format=jsonl/dt=2019-01-20/ /path/to/put/files/
# you should see some files now in the local path.
# NOTE: if the export is hourly (like the above) you will see subfolders
ls -ltr /path/to/put/files/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=01/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=08/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=04/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=03/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=00/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=02/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=06/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=07/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=05/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=09/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=10/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=12/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=11/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=16/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=15/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=13/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=14/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=19/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=18/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=22/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=17/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=23/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=21/
drwxr-xr-x   3 myuser  mygroup    96 Jan 28 08:58 hr=20/

​

Deduping S3 Exports Data

All tables need to be deduped in order to get the most recent data and discard any past data that is being duplicated as part of this process.

To dedupe tables, get the latest instance and the latest run per partition key for each table, with some exceptions which are noted in the table below.

​

Example

In order to dedupe the table convos_metrics ,use this query:

SELECT *
FROM
    (SELECT
    *,
    ROW_NUMBER() OVER (partition by {{ primary_key }} order by {{ logical_timestamp}} DESC, {{ insertion_timestamp }} DESC) as row_idx
    FROM convos_metrics
    )
WHERE row_idx = 1

We partition by the primary_key for that table and get the latest data using order by logical_timestampDESC in the subquery. Then we only select where row_idx = 1 to only pull the latest information we have for each issue_id.

​

Deduping Guide by Table

Open the expandable table to see the full reference to deduping by table, which includes the fields to partition and order by respectively.

​

Deduping Reference

​

Schema Adjustments

You should enable automated schema evolution detection and identify any changes using export_docs.yaml, which is generated each day and sent via the S3 feed. By incorporating this into the workflows, you can maintain a proactive stance, ensuring uninterrupted service and a smooth transition in the event of schema adjustments.

​

Export Schema

The scrolling window below provides detailed information regarding the primary schema for exported data files that ASAPP can make available to customers.

The schema is available as a stand-alone webpage

​

Workforce Management (WFM) Data

In addition to the primary data schema (above), ASAPP also offers separate workforce management data for use in intraday workforce management applications.

The data is structured in two tables and published every 20-30 minutes. This data refresh cadence is intended to support continuous monitoring use cases rather than for historical reporting.

queue_wfm

For each queue in your ASAPP implementation, this table contains queue-related metrics during a 15-minute period:

• Total assignments
• Customer queue abandons
• Average handle time
• Average wrap time
• Average customer wait time in queue
• Customer wait times under different SLA thresholds
• Reps in the queue

queue_membership_wfm

For each queue in your ASAPP implementation, this table contains rep-level information about availability and status of the rep during a 15-minute period (or the period of the 15 minutes during which the rep was logged in):

• Linear logged-in time
• Linear available time
• Linear busy time
• Total linear utilized time
• Linear utilized time while available
• Linear utilized time while busy