Enhanced Reports

Overview

Enhanced Reports is a powerful feature that gives you direct SQL query access to your PostGrid data. With Enhanced Reports, you can analyze your mail campaigns, track performance metrics, generate custom exports, and gain deep insights into your print and mail operations—all through standard SQL queries.

What Enhanced Reports Offers

Unlimited Analysis Capabilities

Enhanced Reports provides you with unprecedented flexibility to analyze your data:

• Custom Analytics: Create any report you can imagine using SQL — from simple status summaries to complex multi-table analysis
• Real-time Insights: Query your most up-to-date data with incremental synchronization
• Parameterized Queries: Build reusable reports with dynamic parameters for different date ranges, statuses, or campaigns
• Export Capabilities: Download full result sets as CSV files for further analysis in Excel, Google Sheets, or your BI tools
• Preview Mode: Test and validate queries with sample results (up to 1000 rows) before running full exports
• Saved Reports: Save frequently-used queries and run them anytime with just a few clicks
• Flexible Filtering: Filter by any field combination—dates, statuses, campaigns, tracking numbers, and more
• Aggregation Power: Calculate totals, averages, counts, and other aggregates across your entire dataset
• Join Multiple Tables: Combine data from letters, postcards, contacts, and tracking information in a single query

Why Use Enhanced Reports?

Traditional reporting tools often force you into predefined templates with limited customization. Enhanced Reports breaks these constraints by giving you:

1. Complete Control: Write any SQL query to answer your specific business questions
2. Speed: Query optimized data structures built specifically for analytical workloads
3. Flexibility: No need to wait for new features—if you can express it in SQL, you can report on it
4. Integration Ready: Export data in CSV format for seamless integration with your existing tools
5. Scalability: Handle large datasets efficiently with DuckDB’s columnar storage engine
6. Version Control: Save and version your queries as you refine your analysis over time

Getting Started

Initial Setup

When Enhanced Reports is first enabled for your account, there’s an initial provisioning period:

⏱️ Initial Provisioning Time: ~60 minutes

During this time, your data lake is being created. This is a one-time setup process that:

• Extracts data from your PostGrid account
• Structures it into optimized SQL tables
• Prepares the query engine for high-performance analytics

After the initial provisioning, your data lake will be kept up-to-date automatically through incremental synchronization.

Note: If you try to run a query before provisioning is complete, you’ll see a message: “Your data is still being synchronized. Please try again in an hour.”

Accessing Enhanced Reports

1. Log into your PostGrid Dashboard
2. Navigate to the Reports section in the main menu
3. Click “Run Query” to create a new ad-hoc query
4. Write your SQL query in the editor
5. Click “Run” to preview results or “Create Export” to generate a full CSV export

Your First Query

Here’s a simple example to get you started:

SELECT
    id,
    status,
    sendDate,
    to_id
FROM letters
WHERE sendDate > '2024-01-01'
ORDER BY sendDate DESC

This query will show you all letters sent after January 1st, 2024, with their IDs, statuses, send dates, and recipient contact IDs.

Available Data Tables

Your data lake contains the following tables with your PostGrid data. All tables are automatically kept in sync with your live data.

Each heading below is the name of the database table as it should be spelled in your queries.

Every table except organizations also carries an organization column holding the ID of the organization that owns the row. If your data lake includes sub-organization data, that column is how you tell each sub-organization’s records apart — see Querying Across Sub-Organizations.

The data lake schema reference covers the same tables with their primary and foreign keys, if you need to work out how they join.

Mail Item Tables

letters

Complete data for all letter mailings.

Column	Type	Description
id	VARCHAR	Unique letter identifier.
createdAt	TIMESTAMP	When the letter was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
sendDate	TIMESTAMP	The order transitions from ready to printing the day after this date. Defaults to the current time if omitted.
from_id	VARCHAR	Sender contact ID (foreign key into contacts).
to_id	VARCHAR	Recipient contact ID (foreign key into contacts).
user	VARCHAR	User that created the record (dashboard/API).
status	VARCHAR	Order status in the delivery pipeline: ready → printing → processed_for_delivery → completed; cancelled possible.
campaign	VARCHAR	Associated campaign ID, if this order was created as part of a campaign.
cancellation	JSON	Cancellation details (reason, cancelledByUser, note) if this order was cancelled.
pageCount	INTEGER	Number of pages in the generated mail piece.
trackingNumber	VARCHAR	Carrier tracking number. Populated after an express or certified order has been processed for delivery; other orders are tracked via imbStatus instead.
imbStatus	VARCHAR	Intelligent Mail Barcode status (US-only): entered_mail_stream, out_for_delivery, returned_to_sender.
imbZIPCode	VARCHAR	ZIP code from IMB scan metadata (US-only).
imbDate	TIMESTAMP	Timestamp of the latest IMB scan (US-only).
express	BOOLEAN	Express shipping flag (expedited printing/shipping; extra charge).
mailingClass	VARCHAR	Selected mail class (e.g. first_class, express, usps_first_class, ca_post_lettermail).
mergeVariables	JSON	JSON used to populate {{ }} placeholders in templates at render time.
color	BOOLEAN	Whether printed in color.
doubleSided	BOOLEAN	Whether printed double-sided.
size	VARCHAR	Print size: us_letter, us_legal, or a4.
paper	VARCHAR	Premium paper stock used for this collateral, if applicable.
envelope	VARCHAR	The envelope (ID) used for this mailing (defaults to the standard envelope).
returnEnvelope	VARCHAR	Return envelope ID if included.
addressPlacement	VARCHAR	Address window placement: top_first_page (default) or insert_blank_page.
seededFromOrder	VARCHAR	ID of the original order this seed/sample copy was generated from, if this is a seed mail piece.
seedDeliveryInfo_dropDate	TIMESTAMP	Expected postal drop date for this seed mail copy.
seedDeliveryInfo_arrivalDate	TIMESTAMP	Expected arrival date for this seed mail copy.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

postcards

Complete data for all postcard mailings.

Column	Type	Description
id	VARCHAR	Unique postcard identifier.
createdAt	TIMESTAMP	When the postcard was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
sendDate	TIMESTAMP	The order transitions from ready to printing the day after this date. Defaults to the current time if omitted.
from_id	VARCHAR	Sender contact ID (foreign key into contacts).
to_id	VARCHAR	Recipient contact ID (foreign key into contacts).
user	VARCHAR	User that created the record (dashboard/API).
status	VARCHAR	Order status in the delivery pipeline: ready → printing → processed_for_delivery → completed; cancelled possible.
campaign	VARCHAR	Associated campaign ID, if this order was created as part of a campaign.
cancellation	JSON	Cancellation details (reason, cancelledByUser, note) if this order was cancelled.
pageCount	INTEGER	Number of pages in the generated mail piece.
trackingNumber	VARCHAR	Carrier tracking number. Populated after an express or certified order has been processed for delivery; other orders are tracked via imbStatus instead.
imbStatus	VARCHAR	Intelligent Mail Barcode status (US-only): entered_mail_stream, out_for_delivery, returned_to_sender.
imbZIPCode	VARCHAR	ZIP code from IMB scan metadata (US-only).
imbDate	TIMESTAMP	Timestamp of the latest IMB scan (US-only).
express	BOOLEAN	Express shipping flag (expedited printing/shipping; extra charge).
mailingClass	VARCHAR	Selected mail class (e.g. first_class, express, usps_first_class, ca_post_lettermail).
mergeVariables	JSON	JSON used to populate {{ }} placeholders in templates at render time.
size	VARCHAR	Postcard size: 6x4, 9x6, or 11x6 (orgs with custom sizing enabled may also see 9x6_reduced or 11x6_reduced).
paper	VARCHAR	Premium paper stock used for this collateral, if applicable.
seededFromOrder	VARCHAR	ID of the original order this seed/sample copy was generated from, if this is a seed mail piece.
seedDeliveryInfo_dropDate	TIMESTAMP	Expected postal drop date for this seed mail copy.
seedDeliveryInfo_arrivalDate	TIMESTAMP	Expected arrival date for this seed mail copy.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

cheques

Complete data for all check mailings.

Column	Type	Description
id	VARCHAR	Unique check identifier.
createdAt	TIMESTAMP	When the check was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
sendDate	TIMESTAMP	The order transitions from ready to printing the day after this date. Defaults to the current time if omitted.
from_id	VARCHAR	Sender contact ID (foreign key into contacts).
to_id	VARCHAR	Recipient contact ID (foreign key into contacts).
user	VARCHAR	User that created the record (dashboard/API).
status	VARCHAR	Order status in the delivery pipeline: ready → printing → processed_for_delivery → completed; cancelled possible.
campaign	VARCHAR	Associated campaign ID, if this order was created as part of a campaign.
cancellation	JSON	Cancellation details (reason, cancelledByUser, note) if this order was cancelled.
pageCount	INTEGER	Number of pages in the generated mail piece.
trackingNumber	VARCHAR	Carrier tracking number. Populated after an express or certified order has been processed for delivery; other orders are tracked via imbStatus instead.
imbStatus	VARCHAR	Intelligent Mail Barcode status (US-only): entered_mail_stream, out_for_delivery, returned_to_sender.
imbZIPCode	VARCHAR	ZIP code from IMB scan metadata (US-only).
imbDate	TIMESTAMP	Timestamp of the latest IMB scan (US-only).
bankAccount	VARCHAR	Bank account ID used to issue the cheque.
amount	INTEGER	Check amount in cents.
currencyCode	VARCHAR	Currency for the cheque amount: USD or CAD.
size	VARCHAR	Check size: us_letter or us_legal.
envelope	VARCHAR	The envelope (ID) used for this mailing (defaults to the standard envelope).
digitalOnly	JSON	Digital-only check configuration.
express	BOOLEAN	Express shipping flag (expedited printing/shipping; extra charge).
mailingClass	VARCHAR	Selected mail class (e.g. first_class, express, usps_first_class, ca_post_lettermail).
mergeVariables	JSON	JSON used to populate {{ }} placeholders in templates at render time.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

selfmailers

Complete data for all self-mailer mailings.

Column	Type	Description
id	VARCHAR	Unique self-mailer identifier.
createdAt	TIMESTAMP	When the self-mailer was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
sendDate	TIMESTAMP	The order transitions from ready to printing the day after this date. Defaults to the current time if omitted.
from_id	VARCHAR	Sender contact ID (foreign key into contacts).
to_id	VARCHAR	Recipient contact ID (foreign key into contacts).
user	VARCHAR	User that created the record (dashboard/API).
status	VARCHAR	Order status in the delivery pipeline: ready → printing → processed_for_delivery → completed; cancelled possible.
campaign	VARCHAR	Associated campaign ID, if this order was created as part of a campaign.
cancellation	JSON	Cancellation details (reason, cancelledByUser, note) if this order was cancelled.
pageCount	INTEGER	Number of pages in the generated mail piece.
trackingNumber	VARCHAR	Carrier tracking number. Populated after an express or certified order has been processed for delivery; other orders are tracked via imbStatus instead.
imbStatus	VARCHAR	Intelligent Mail Barcode status (US-only): entered_mail_stream, out_for_delivery, returned_to_sender.
imbZIPCode	VARCHAR	ZIP code from IMB scan metadata (US-only).
imbDate	TIMESTAMP	Timestamp of the latest IMB scan (US-only).
express	BOOLEAN	Express shipping flag (expedited printing/shipping; extra charge).
mailingClass	VARCHAR	Selected mail class (e.g. first_class, express, usps_first_class, ca_post_lettermail).
mergeVariables	JSON	JSON used to populate {{ }} placeholders in templates at render time.
size	VARCHAR	Self-mailer size: 8.5x11_bifold, 8.5x11_trifold, or 9.5x16_trifold.
paper	VARCHAR	Premium paper stock used for this collateral, if applicable.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

snappacks

Complete data for all snap pack mailings.

Column	Type	Description
id	VARCHAR	Unique snap pack identifier.
createdAt	TIMESTAMP	When the snap pack was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
sendDate	TIMESTAMP	The order transitions from ready to printing the day after this date. Defaults to the current time if omitted.
from_id	VARCHAR	Sender contact ID (foreign key into contacts).
to_id	VARCHAR	Recipient contact ID (foreign key into contacts).
user	VARCHAR	User that created the record (dashboard/API).
status	VARCHAR	Order status in the delivery pipeline: ready → printing → processed_for_delivery → completed; cancelled possible.
campaign	VARCHAR	Associated campaign ID, if this order was created as part of a campaign.
cancellation	JSON	Cancellation details (reason, cancelledByUser, note) if this order was cancelled.
pageCount	INTEGER	Number of pages in the generated mail piece.
trackingNumber	VARCHAR	Carrier tracking number. Populated after an express or certified order has been processed for delivery; other orders are tracked via imbStatus instead.
imbStatus	VARCHAR	Intelligent Mail Barcode status (US-only): entered_mail_stream, out_for_delivery, returned_to_sender.
imbZIPCode	VARCHAR	ZIP code from IMB scan metadata (US-only).
imbDate	TIMESTAMP	Timestamp of the latest IMB scan (US-only).
express	BOOLEAN	Express shipping flag (expedited printing/shipping; extra charge).
mailingClass	VARCHAR	Selected mail class (e.g. first_class, express, usps_first_class, ca_post_lettermail).
mergeVariables	JSON	JSON used to populate {{ }} placeholders in templates at render time.
size	VARCHAR	Snap pack size: 8.5x11_bifold_v or 8.5x11_trifold_c.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

Contact Table

contacts

All contact records (both return addresses and recipients).

Column	Type	Description
id	VARCHAR	Unique contact identifier.
createdAt	TIMESTAMP	When the record was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
firstName	VARCHAR
lastName	VARCHAR
companyName	VARCHAR
email	VARCHAR
phoneNumber	VARCHAR
addressLine1	VARCHAR
addressLine2	VARCHAR
city	VARCHAR
provinceOrState	VARCHAR
postalOrZip	VARCHAR
countryCode	VARCHAR	ISO country code (e.g. US, CA).
addressStatus	VARCHAR	Verification status: verified, corrected, or failed.
addressErrors	JSON	Validation errors if any.
skipVerification	BOOLEAN	Whether verification was skipped.
forceVerifiedStatus	BOOLEAN	Whether status was manually forced.
mailingLists	JSON	Mailing lists this contact belongs to.
addressChange	JSON	Pending address change details, if any.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

Document Table

documents

Documents you have uploaded.

Column	Type	Description
id	VARCHAR	Unique document identifier.
createdAt	TIMESTAMP	When the record was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

Virtual Mailbox Tables

virtualmailboxes

One row per virtual mailbox.

Column	Type	Description
id	VARCHAR	Unique virtual mailbox identifier.
createdAt	TIMESTAMP	When the record was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
name	VARCHAR	Name of the virtual mailbox.
status	VARCHAR	Provisioning status of the virtual mailbox.
capabilities	JSON	What the mailbox can do (e.g. scanning, forwarding).
countryCode	VARCHAR	ISO country code of the mailbox address.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

virtualmailboxitems

One row per piece of mail received in a virtual mailbox.

Column	Type	Description
id	VARCHAR	Unique item identifier.
createdAt	TIMESTAMP	When the record was created.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
virtualMailbox	VARCHAR	Virtual mailbox this item arrived in (foreign key into virtualmailboxes).
matchedLetter	VARCHAR	Letter this returned item was matched back to, if any (foreign key into letters).
returnReason	VARCHAR	Why the mail piece came back (e.g. undeliverable, moved).
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

Organization Table

organizations

Your organization, plus every sub-organization whose data is included in your data lake. Join it on any other table’s organization column to label rows with the organization that owns them.

Column	Type	Description
id	VARCHAR	Organization ID. Matches the organization column on every other table.
name	VARCHAR	Organization name, as shown in the dashboard.
updatedAt	TIMESTAMP	When the organization was last updated.

Tracking Table

trackervisits

Records of tracker link visits (when tracking URLs are clicked).

Column	Type	Description
id	VARCHAR	Unique visit identifier.
createdAt	TIMESTAMP	When the link was clicked.
updatedAt	TIMESTAMP	Last modification time.
organization	VARCHAR	Organization ID that owns this record.
tracker	VARCHAR	Tracker ID (QR code / PURL definition) that generated this visit.
orderID	VARCHAR	Order ID (letter/postcard/cheque/self-mailer) associated with this tracker visit.
device	VARCHAR	Free-form device string parsed from the visitor’s user agent (e.g. iPhone, Windows NT 10.0); Unknown Device when no user agent is present. Not a fixed set of values.
ipAddress	VARCHAR	Visitor IP captured for the visit.
description	VARCHAR	Optional description.
metadata	JSON	Custom metadata object.

Querying Across Sub-Organizations

If your organization has sub-organizations, PostGrid can include their data in your data lake so one query covers your whole hierarchy. Reach out to support@postgrid.com to have it enabled — it is an organization-wide setting that applies to every sub-organization under you, and it requires sub-organizations and impersonation to already be enabled for your organization.

If you have sub-organizations with Enhanced Reports enabled, the Reports page and the query editor show a picker — Viewing data for and Query data for respectively — with two kinds of choices:

• All orgs queries your own data lake. With the consolidated setting on, that lake holds your sub-organizations’ rows alongside your own: use the organization column to tell them apart, and join the organizations table to get names:

  SELECT o.name, COUNT(*) AS letters
  FROM letters l
  JOIN organizations o ON o.id = l.organization
  WHERE l.sendDate > CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY o.name
  ORDER BY letters DESC

• A specific sub-organization switches to that sub-organization’s own data lake, so the query — and any report or export you save while it’s selected — belongs to that sub-organization rather than to you. Only sub-organizations that have Enhanced Reports enabled themselves appear in the list.

With the consolidated setting off, the first choice reads My organization instead, and your own data lake contains only your own records — you can still pick a sub-organization to query its data lake individually.

SQL Query Syntax (DuckDB)

Enhanced Reports uses DuckDB, a high-performance analytical database engine. DuckDB supports standard SQL with some powerful extensions.

Basic Query Structure

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number

Common SQL Operations

Filtering by Date

-- Letters sent in the last 30 days
SELECT * FROM letters
WHERE sendDate >= CURRENT_DATE - INTERVAL '30 days'

-- Letters sent in a specific month
SELECT * FROM letters
WHERE sendDate >= '2024-01-01'
  AND sendDate < '2024-02-01'

Filtering by Status

-- All completed letters
SELECT * FROM letters
WHERE status = 'completed'

-- Letters returned to sender (US-destined orders only, tracked via IMB scans)
SELECT * FROM letters
WHERE imbStatus = 'returned_to_sender'

Aggregations

-- Count letters by status
SELECT status, COUNT(*) as count
FROM letters
GROUP BY status
ORDER BY count DESC

-- Average page count by campaign
SELECT campaign, AVG(pageCount) as avg_pages
FROM letters
WHERE campaign IS NOT NULL
GROUP BY campaign

Joining Tables

-- Letters with recipient contact information
SELECT
    l.id,
    l.status,
    l.sendDate,
    c.firstName,
    c.lastName,
    c.city,
    c.provinceOrState
FROM letters l
JOIN contacts c ON l.to_id = c.id
WHERE l.sendDate >= '2024-01-01'

Working with JSON Fields

DuckDB provides powerful JSON functions:

-- Extract a specific metadata field
SELECT
    id,
    json_extract_string(metadata, '$.customField') as custom_value
FROM letters
WHERE metadata IS NOT NULL

-- Query merge variables
SELECT
    id,
    json_extract_string(mergeVariables, '$.name') as recipient_name
FROM letters
WHERE mergeVariables IS NOT NULL

Time-Based Analysis

-- Letters by week
SELECT
    DATE_TRUNC('week', sendDate) as week,
    COUNT(*) as letter_count
FROM letters
GROUP BY week
ORDER BY week DESC

-- Daily completion rate (status = 'completed' means the order has moved
-- through the pipeline, not confirmed delivery; use imbStatus for a more
-- accurate delivery signal on US-destined orders)
SELECT
    DATE_TRUNC('day', sendDate) as day,
    COUNT(*) FILTER (WHERE status = 'completed') * 100.0 / COUNT(*) as completion_rate
FROM letters
GROUP BY day
ORDER BY day DESC

Advanced Features

Window Functions

-- Running total of letters by date
SELECT
    sendDate,
    COUNT(*) as daily_count,
    SUM(COUNT(*)) OVER (ORDER BY sendDate) as running_total
FROM letters
GROUP BY sendDate
ORDER BY sendDate

Common Table Expressions (CTEs)

-- Multi-step analysis
WITH monthly_stats AS (
    SELECT
        DATE_TRUNC('month', sendDate) as month,
        COUNT(*) as total_letters,
        COUNT(*) FILTER (WHERE status = 'completed') as completed
    FROM letters
    GROUP BY month
)
SELECT
    month,
    total_letters,
    completed,
    (completed * 100.0 / total_letters) as completion_rate
FROM monthly_stats
ORDER BY month DESC

CASE Expressions

-- Categorize letters by status
SELECT
    CASE
        WHEN status = 'completed' THEN 'Completed'
        WHEN status = 'cancelled' THEN 'Cancelled'
        ELSE 'In Progress'
    END as status_category,
    COUNT(*) as count
FROM letters
GROUP BY status_category

Parameterized Queries

Parameterized queries let you create reusable reports where values can be changed each time you run the query.

Parameter Formats

Enhanced Reports supports multiple parameter formats:

Positional Parameters

-- Using ? placeholders
SELECT * FROM letters
WHERE sendDate >= ?
  AND status = ?

-- Using $1, $2, etc.
SELECT * FROM letters
WHERE sendDate >= $1
  AND status = $2

Named Parameters

-- Using named parameters
SELECT * FROM letters
WHERE sendDate >= $start_date
  AND sendDate <= $end_date
  AND status = $status

Type Casting Parameters

Since all parameters are passed as strings, you may need to cast them:

-- Cast a date parameter
SELECT * FROM letters
WHERE sendDate >= CAST($start_date AS DATE)

-- Cast to integer
SELECT * FROM cheques
WHERE amount >= CAST($min_amount AS INTEGER)

-- Alternative syntax
SELECT * FROM letters
WHERE sendDate >= $start_date::DATE

Parameter Examples

When you use parameters in your query, the dashboard will automatically show input fields for each parameter. For example:

Query:

SELECT
    status,
    COUNT(*) as count,
    COUNT(*) FILTER (WHERE express = true) as express_count
FROM letters
WHERE sendDate >= $start_date::DATE
  AND sendDate <= $end_date::DATE
  AND ($campaign IS NULL OR campaign = $campaign)
GROUP BY status
ORDER BY count DESC

Parameters to fill in when running:

• start_date: “2024-01-01”
• end_date: “2024-12-31”
• campaign: “summer_2024” (or leave empty for all campaigns)

Example Queries

Campaign Performance Analysis

-- Comprehensive campaign performance report
SELECT
    campaign,
    COUNT(*) as total_sent,
    COUNT(*) FILTER (WHERE status = 'completed') as completed,
    COUNT(*) FILTER (WHERE status IN ('ready', 'printing', 'processed_for_delivery')) as in_progress,
    COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
    (COUNT(*) FILTER (WHERE status = 'completed') * 100.0 / COUNT(*)) as completion_rate,
    AVG(pageCount) as avg_pages,
    MIN(sendDate) as first_send,
    MAX(sendDate) as last_send
FROM letters
WHERE campaign IS NOT NULL
  AND sendDate >= '2024-01-01'
GROUP BY campaign
ORDER BY total_sent DESC

Geographic Distribution

-- Letters by state with completion rates (status = 'completed' is a
-- best-effort estimate, not confirmed delivery)
SELECT
    c.provinceOrState as state,
    COUNT(l.id) as total_letters,
    COUNT(*) FILTER (WHERE l.status = 'completed') as completed,
    (COUNT(*) FILTER (WHERE l.status = 'completed') * 100.0 / COUNT(l.id)) as completion_rate
FROM letters l
JOIN contacts c ON l.to_id = c.id
WHERE l.sendDate >= CURRENT_DATE - INTERVAL '90 days'
  AND c.countryCode = 'US'
GROUP BY c.provinceOrState
ORDER BY total_letters DESC
LIMIT 50

Delivery Time Analysis

-- Average delivery time by mailing class
WITH delivery_times AS (
    SELECT
        l.mailingClass,
        l.sendDate,
        l.imbDate,
        DATE_DIFF('day', l.sendDate, l.imbDate) as days_to_deliver
    FROM letters l
    WHERE l.imbDate IS NOT NULL
      AND l.sendDate >= CURRENT_DATE - INTERVAL '6 months'
)
SELECT
    mailingClass,
    COUNT(*) as sample_size,
    AVG(days_to_deliver) as avg_days,
    MIN(days_to_deliver) as min_days,
    MAX(days_to_deliver) as max_days,
    MEDIAN(days_to_deliver) as median_days
FROM delivery_times
GROUP BY mailingClass
ORDER BY avg_days

Cost Analysis (for Checks)

-- Total check values by month
SELECT
    DATE_TRUNC('month', sendDate) as month,
    COUNT(*) as check_count,
    SUM(amount) / 100.0 as total_amount_dollars,
    AVG(amount) / 100.0 as avg_amount_dollars
FROM cheques
WHERE status = 'completed'
  AND sendDate >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY month
ORDER BY month DESC

Tracker Engagement Analysis

-- Engagement rates by campaign
SELECT
    l.campaign,
    COUNT(DISTINCT l.id) as total_sent,
    COUNT(DISTINCT tv.orderID) as engaged,
    COUNT(tv.id) as total_clicks,
    (COUNT(DISTINCT tv.orderID) * 100.0 / COUNT(DISTINCT l.id)) as engagement_rate,
    (COUNT(tv.id) * 1.0 / COUNT(DISTINCT tv.orderID)) as avg_clicks_per_engaged
FROM letters l
LEFT JOIN trackervisits tv ON tv.orderID = l.id
WHERE l.campaign IS NOT NULL
  AND l.sendDate >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY l.campaign
HAVING COUNT(DISTINCT l.id) >= 10
ORDER BY engagement_rate DESC

Address Quality Report

-- Address verification status breakdown
SELECT
    addressStatus,
    COUNT(*) as contact_count,
    COUNT(*) FILTER (WHERE addressErrors IS NOT NULL) as with_errors,
    COUNT(*) FILTER (WHERE skipVerification = true) as skipped_verification
FROM contacts
WHERE updatedAt >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY addressStatus
ORDER BY contact_count DESC

Multi-Format Comparison

-- Compare performance across different mail formats
WITH all_mail AS (
    SELECT
        'Letter' as format,
        id,
        status,
        sendDate,
        pageCount as pages
    FROM letters

    UNION ALL

    SELECT
        'Postcard' as format,
        id,
        status,
        sendDate,
        1 as pages
    FROM postcards

    UNION ALL

    SELECT
        'Self-Mailer' as format,
        id,
        status,
        sendDate,
        pageCount as pages
    FROM selfmailers
)
SELECT
    format,
    COUNT(*) as total_sent,
    COUNT(*) FILTER (WHERE status = 'completed') as completed,
    (COUNT(*) FILTER (WHERE status = 'completed') * 100.0 / COUNT(*)) as completion_rate,
    AVG(pages) as avg_pages
FROM all_mail
WHERE sendDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY format
ORDER BY total_sent DESC

Working with Results

Preview Mode

When you click “Run” on your query:

• Results are limited to 1000 rows by default
• Results appear in a scrollable table below the query editor
• Column headers show all field names from your SELECT clause
• You can download the preview as CSV using the “Download CSV” button
• This is perfect for testing and validating your query before creating a full export

Note: If your query would return more than 1000 rows, you’ll see a warning: “Note that the results may have been truncated to 1000 records. Please use the report exports API if you need to download the full results.”

Creating Exports

When you need the complete result set:

1. Click “Create Export” instead of “Run”
2. The system will process your query in the background
3. The export is polled every 2 seconds until complete (maximum 2 minutes)
4. Once ready, the CSV file downloads automatically
5. The filename format: report-{reportID}-export.csv

Export Specifications:

• Maximum file size: Limited by system configuration
• File format: CSV with headers
• Character encoding: UTF-8
• Date format: ISO 8601 (e.g., “2024-01-15T14:30:00.000Z”)
• NULL values: Represented as “NULL” in the CSV

Saving Reports

To save a query for future use:

1. Write and test your query
2. Click “Save as Report”
3. Enter a descriptive name (e.g., “Monthly Campaign Performance”)
4. The report appears in your Reports list
5. Click any saved report to view, edit, or run it again

Saved reports can be:

• Updated with new SQL queries
• Run with different parameters
• Exported multiple times
• Shared via API (reportID)
• Deleted when no longer needed

Scheduling Reports

A saved report can run itself on a schedule and email your team when each run finishes:

1. Open a saved report and set up its schedule
2. Pick a cadence — every N hours, days, weeks, months, or years — and the time of day it should run (times are in America/Toronto)
3. Optionally add up to 10 email addresses to notify when a run finishes
4. Each run produces a regular export under the report, so you can download past runs from the report’s export list at any time

Two things to keep in mind:

• A scheduled report can’t use parameters. Nothing fills them in when the report runs on its own, so use DuckDB’s date and time functions (CURRENT_DATE, NOW()) for the dynamic parts instead.
• Notification recipients must be users in the same organization as the report. Other addresses are skipped, which keeps report data inside your team.

The smallest cadence is one hour, and PostGrid checks for due reports every 15 minutes, so a run may begin up to 15 minutes after the time you picked.

Query Limits and Best Practices

System Limits

• Sample Query Timeout: 30 seconds
• Sample Result Limit: 1000 rows maximum
• Sample Rate Limit: 5 sample queries per minute
• Export Runtime: 13 minutes
• Export File Size: 100 MB — anything past that is truncated
• Export Rate Limit: 5 exports per minute
• Parameter Limits: Maximum 32 parameters per query
• Parameter Length: Maximum 4,096 characters per parameter value
• Query Length: Maximum 128 KB per query

Performance Best Practices

1. Use WHERE Clauses Efficiently

-- Good: Filter early
SELECT * FROM letters
WHERE sendDate >= '2024-01-01'
  AND status = 'completed'

-- Less efficient: Filter after retrieving all data
SELECT * FROM letters
WHERE DATE_PART('year', sendDate) = 2024

2. Limit Results for Testing

-- Add LIMIT when testing
SELECT * FROM letters
WHERE sendDate >= '2024-01-01'
LIMIT 10

3. Use Appropriate JOIN Types

-- Use INNER JOIN when you only want matches
SELECT l.*, c.city
FROM letters l
INNER JOIN contacts c ON l.to_id = c.id

-- Use LEFT JOIN when you want all letters even without contacts
SELECT l.*, c.city
FROM letters l
LEFT JOIN contacts c ON l.to_id = c.id

4. Index-Friendly Queries

The following fields are optimized for filtering:

• All id fields (primary keys)
• sendDate in mail item tables
• createdAt and updatedAt in all tables
• status in mail item tables

5. Aggregate Before Joining

-- Good: Aggregate first
WITH letter_counts AS (
    SELECT campaign, COUNT(*) as count
    FROM letters
    GROUP BY campaign
)
SELECT * FROM letter_counts
WHERE count > 100

-- Less efficient: Aggregate after join
SELECT campaign, COUNT(*)
FROM letters l
LEFT JOIN contacts c ON l.to_id = c.id
GROUP BY campaign
HAVING COUNT(*) > 100

Query Optimization Tips

1. Select only needed columns: Don’t use SELECT * if you only need a few fields
2. Filter early: Apply WHERE clauses to reduce data before JOIN operations
3. Use appropriate data types: Cast parameters to correct types for comparisons
4. Test with LIMIT: Always test complex queries with LIMIT first
5. Break complex queries into CTEs: Use Common Table Expressions for readability and debugging

Troubleshooting

Common Error Messages

”Your data is still being synchronized”

Cause: The data lake hasn’t finished initial provisioning yet.

Solution: Wait approximately 60 minutes after Enhanced Reports was enabled for your account. If the issue persists beyond this time, contact support.

---

”Your query took too long to run”

Cause: The query exceeded the time limit (typically 10-30 seconds for samples).

Solution:

• Add more specific WHERE clauses to filter data
• Reduce the date range you’re querying
• Simplify complex JOINs or aggregations
• Use the export feature instead of sample preview for long-running queries

---

”Binder Error: Referenced column not found”

Cause: You referenced a column that doesn’t exist in the table.

Solution: Check the table schema above and verify column names. Remember that column names are case-sensitive.

---

”Parser Error: syntax error”

Cause: The SQL syntax is invalid.

Solution:

• Check for missing commas, parentheses, or quotes
• Verify SQL keyword spelling (SELECT, FROM, WHERE, etc.)
• Ensure string values are in single quotes: 'value'
• Ensure table and column names are valid

---

”Catalog Error: Table with name [table_name] does not exist”

Cause: You’re trying to query a table that doesn’t exist.

Solution: Check the list of available tables in this documentation. Table names are lowercase and case-sensitive.

---

Getting Help

If you encounter issues not covered here:

1. Check the query syntax carefully
2. Test with a simpler version of your query first
3. Verify table and column names match the documentation
4. Contact support at support@postgrid.com with:
   • Your organization ID
   • The report ID (if using a saved report)
   • The SQL query you’re trying to run
   • The complete error message

Security and Privacy

Data Isolation

• Your data lake contains only data you are entitled to: your own organization’s records, plus your sub-organizations’ records if PostGrid has enabled that for you (see Querying Across Sub-Organizations)
• Test mode and live mode data are kept in separate data lakes
• You can never query data belonging to another PostGrid customer, and a sub-organization can never query its parent’s or its siblings’ data

Query Sandboxing

All queries run in a secure sandbox environment that:

• Prevents access to the file system
• Blocks external network access
• Prohibits persistent storage
• Limits resource usage
• Isolates each query execution

Data Retention

• Exported CSV files are temporarily stored and accessible via signed URLs
• Export files are automatically deleted after 30 days
• Sample query results are not stored persistently
• Saved reports store only the SQL query, not the results

Best Practices for Sensitive Data

1. Don’t export more data than needed: Use specific SELECT columns rather than SELECT *
2. Use appropriate filters: Limit date ranges and row counts to minimize data in exports
3. Secure your exports: Downloaded CSV files are not encrypted—store them securely
4. Limit parameter sharing: If sharing reports via API, be cautious about parameter values containing sensitive information

API Integration

Enhanced Reports can be accessed programmatically via the PostGrid API. This allows you to:

• Create and manage reports programmatically
• Schedule automated report generation
• Integrate reporting data into your applications
• Build custom dashboards

API endpoints include:

• POST /reports/samples - Run an ad-hoc query
• POST /reports - Create a new saved report
• GET /reports - List your saved reports
• GET /reports/:id - Retrieve a report definition
• POST /reports/:id - Update a report definition or its schedule
• DELETE /reports/:id - Delete a saved report
• POST /reports/:id/samples - Run a report preview
• POST /reports/schedule_previews - Preview a schedule before saving it
• POST /reports/:reportID/exports - Create a full export
• GET /reports/:reportID/exports - List a report’s exports
• GET /reports/:reportID/exports/:exportID - Check export status
• DELETE /reports/:reportID/exports/:exportID - Delete an export

See the Enhanced Reports API guide for worked examples of each.

For complete API documentation, refer to your PostGrid API reference guide.

Frequently Asked Questions

How often is the data updated?

Your data lake is updated incrementally throughout the day. Most changes to your mail items, contacts, and tracking data will appear in the data lake within a few hours.

Can I query data from both test and live mode together?

No. Test mode and live mode maintain separate data lakes. You’ll need to switch modes in the dashboard to query each environment separately.

What happens if I modify a saved report?

When you modify and save a report, the SQL query is updated immediately. Any subsequent runs of that report will use the new query. Previous exports remain unchanged.

Can I schedule reports to run automatically?

Yes. Give a saved report a schedule and PostGrid runs it for you, producing an export each time and optionally emailing your team — no cron job of your own needed. See Scheduling Reports, or set the schedule field through the API.

Is there a limit to how many reports I can save?

There’s no hard limit on the number of saved reports. However, we recommend organizing and cleaning up unused reports periodically.

Can I share reports with team members?

Yes, reports are shared at the organization level. Any user in your organization with access to the Enhanced Reports feature can view, run, and modify saved reports. Reports saved while a specific sub-organization is selected belong to that sub-organization instead.

What if my export gets truncated?

Exports have size limits to ensure system stability. If your export is truncated:

1. Add more specific WHERE clauses to reduce the result set
2. Consider breaking large exports into smaller date ranges
3. Use aggregation to summarize data instead of exporting raw rows
4. Contact support if you regularly need very large exports

Can I export data in formats other than CSV?

Currently, only CSV export is supported. However, CSV is widely compatible with Excel, Google Sheets, database import tools, and programming languages.

How do I see the schema for a table?

Use this query to see all columns in a table:

PRAGMA table_info('table_name');

For example:

PRAGMA table_info('letters');

Can I use database functions like NOW() or CURRENT_DATE?

Yes, DuckDB supports standard SQL functions including:

• CURRENT_DATE - Current date
• CURRENT_TIMESTAMP - Current timestamp
• DATE_TRUNC() - Truncate dates to specific intervals
• DATE_DIFF() - Calculate date differences
• EXTRACT() - Extract parts of dates

For a complete list, refer to the DuckDB SQL functions documentation.

Advanced Topics

Incremental Data Analysis

For analyzing changes over time:

-- Items created or updated in the last 24 hours
SELECT * FROM letters
WHERE updatedAt >= CURRENT_TIMESTAMP - INTERVAL '24 hours'

-- Track daily creation trends
SELECT
    DATE_TRUNC('day', createdAt) as day,
    COUNT(*) as created_count
FROM letters
WHERE createdAt >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY day
ORDER BY day

Complex Aggregations

-- Percentile analysis of delivery times
WITH delivery_data AS (
    SELECT
        DATE_DIFF('day', sendDate, imbDate) as delivery_days
    FROM letters
    WHERE imbDate IS NOT NULL
      AND sendDate >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    quantile_cont(delivery_days, 0.25) as p25,
    quantile_cont(delivery_days, 0.5) as median,
    quantile_cont(delivery_days, 0.75) as p75,
    quantile_cont(delivery_days, 0.95) as p95
FROM delivery_data

Cohort Analysis

-- Monthly cohorts by first send date
WITH first_sends AS (
    SELECT
        user,
        DATE_TRUNC('month', MIN(sendDate)) as cohort_month
    FROM letters
    GROUP BY user
),
monthly_activity AS (
    SELECT
        fs.cohort_month,
        DATE_TRUNC('month', l.sendDate) as activity_month,
        COUNT(DISTINCT l.user) as active_users
    FROM letters l
    JOIN first_sends fs ON l.user = fs.user
    GROUP BY fs.cohort_month, activity_month
)
SELECT
    cohort_month,
    activity_month,
    DATE_DIFF('month', cohort_month, activity_month) as months_since_first,
    active_users
FROM monthly_activity
ORDER BY cohort_month, activity_month

Additional Resources

• DuckDB SQL Documentation: https://duckdb.org/docs/sql/introduction
• PostGrid API Documentation: Available in your dashboard
• Support: support@postgrid.com
• Feature Requests: Share feedback through your dashboard or support channels

---

Ready to unlock the full potential of your PostGrid data? Start exploring Enhanced Reports today and discover insights that drive your business forward. From simple status reports to complex multi-dimensional analysis, the possibilities are truly endless.