​

Configuring AfterShip as a Source

​

1. Add account access

• API Key: Your AfterShip API key used to authenticate API requests.
• Start Date: The earliest date from which records will be synced based on the last modification date.

​

2. Select streams

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: you can choose between INCREMENTAL and FULL_TABLE.
  • Incremental: every time the extraction happens, we’ll get only the new or updated data based on the updated_at field.
  • Full table: every time the extraction happens, we’ll get the current state of all data.

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Data Model

​

Use Cases for Data Analysis

​

1. Delivery Performance Overview

• Monitor carrier performance and reliability
• Identify delivery delays and their causes
• Optimize carrier selection based on performance data

WITH delivery_metrics AS (
    SELECT
        slug AS carrier,
        tag AS status,
        COUNT(*) AS total_shipments,
        AVG(transit_time) AS avg_transit_days,
        SUM(CASE WHEN on_time_status = 'on_time' THEN 1 ELSE 0 END) AS on_time_count,
        SUM(CASE WHEN tag = 'Delivered' THEN 1 ELSE 0 END) AS delivered_count
    FROM
        nekt_raw.aftership_trackings
    WHERE
        created_at >= CURRENT_DATE - INTERVAL '30' DAY
    GROUP BY
        slug,
        tag
)
SELECT
    carrier,
    status,
    total_shipments,
    ROUND(avg_transit_days, 1) AS avg_transit_days,
    on_time_count,
    ROUND(on_time_count * 100.0 / NULLIF(delivered_count, 0), 1) AS on_time_rate_pct
FROM
    delivery_metrics
ORDER BY
    total_shipments DESC

WITH delivery_metrics AS (
    SELECT
        slug AS carrier,
        tag AS status,
        COUNT(*) AS total_shipments,
        AVG(transit_time) AS avg_transit_days,
        SUM(CASE WHEN on_time_status = 'on_time' THEN 1 ELSE 0 END) AS on_time_count,
        SUM(CASE WHEN tag = 'Delivered' THEN 1 ELSE 0 END) AS delivered_count
    FROM
        `nekt_raw.aftership_trackings`
    WHERE
        DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY
        slug,
        tag
)
SELECT
    carrier,
    status,
    total_shipments,
    ROUND(avg_transit_days, 1) AS avg_transit_days,
    on_time_count,
    ROUND(SAFE_DIVIDE(on_time_count * 100.0, delivered_count), 1) AS on_time_rate_pct
FROM
    delivery_metrics
ORDER BY
    total_shipments DESC

​

2. Shipment Status Distribution

• Monitor shipments in transit
• Identify stuck or problematic shipments
• Track exception rates by carrier

SELECT
    tag AS status,
    subtag AS detailed_status,
    COUNT(*) AS shipment_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM
    nekt_raw.aftership_trackings
WHERE
    active = true
GROUP BY
    tag,
    subtag
ORDER BY
    shipment_count DESC

SELECT
    tag AS status,
    subtag AS detailed_status,
    COUNT(*) AS shipment_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM
    `nekt_raw.aftership_trackings`
WHERE
    active = true
GROUP BY
    tag,
    subtag
ORDER BY
    shipment_count DESC

​

3. Destination Analysis

• Identify top shipping destinations
• Analyze regional delivery performance
• Plan logistics and carrier partnerships by region

SELECT
    destination_country_region AS country,
    destination_state AS state,
    COUNT(*) AS total_shipments,
    AVG(transit_time) AS avg_transit_days,
    SUM(CASE WHEN tag = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
    SUM(CASE WHEN tag = 'Exception' THEN 1 ELSE 0 END) AS exceptions
FROM
    nekt_raw.aftership_trackings
WHERE
    destination_country_region IS NOT NULL
    AND created_at >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
    destination_country_region,
    destination_state
ORDER BY
    total_shipments DESC
LIMIT 20

SELECT
    destination_country_region AS country,
    destination_state AS state,
    COUNT(*) AS total_shipments,
    AVG(transit_time) AS avg_transit_days,
    SUM(CASE WHEN tag = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
    SUM(CASE WHEN tag = 'Exception' THEN 1 ELSE 0 END) AS exceptions
FROM
    `nekt_raw.aftership_trackings`
WHERE
    destination_country_region IS NOT NULL
    AND DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
    destination_country_region,
    destination_state
ORDER BY
    total_shipments DESC
LIMIT 20

​

Implementation Notes

​

Incremental Sync Support

• The Trackings stream supports incremental sync using the updated_at field as the replication key
• Only trackings modified since the last sync will be fetched, improving extraction performance

​

Nested Data Structures

• The checkpoints array contains the full tracking history with timestamps, locations, and status updates
• The customers array may contain multiple customers (buyer and receiver)
• Use JSON functions or UNNEST to analyze nested checkpoint and customer data

​

Tracking Status Tags

• Pending - Tracking created, no updates yet
• InfoReceived - Carrier received shipment information
• InTransit - Shipment is in transit
• OutForDelivery - Out for delivery
• AttemptFail - Delivery attempt failed
• Delivered - Successfully delivered
• AvailableForPickup - Available at pickup location
• Exception - Exception occurred (delay, customs, etc.)
• Expired - Tracking expired with no delivery confirmation