Overview

The SDH Reporting API provides read-only access to your organization's data through a standard PostgreSQL interface. Use it to connect BI tools for analytical querying or ingest data into your own warehouse.

Connection Details

Setting	Value
Host	analytics-sql.seconddoor.app
Port	5432
SSL	Required
Credentials	Provided by the SDH team

Connecting

You can connect using psql or any PostgreSQL-compatible client, driver, or BI tool (e.g., Metabase, Tableau, Power BI, DBeaver).

psql "host=analytics-sql.seconddoor.app port=5432 sslmode=require user=YOUR_USER dbname=YOUR_DB"

Verify Your Connection

Run a quick query to confirm everything is working:

SELECT * FROM export_user LIMIT 5;

You should see a list of user accounts in your organization.

Two Ways to Query Your Data

Semantic Layer

The semantic layer sits between your raw data and your analytics tools, providing a consistent, governed set of metrics and definitions. It exists to solve a common problem: when multiple teams or tools query the same data independently, they often end up with slightly different definitions for the same metric (e.g., what counts as a "converted lead" or how "time to first touch" is calculated). The semantic layer centralizes these definitions so that every query — whether from a BI dashboard, a custom report, or an ad-hoc analysis — uses the same logic and produces consistent results.

You query the semantic layer using dimensions (fields to filter and group by) and measures (pre-built calculations like counts, percentages, and averages). The underlying joins, filters, and aggregation logic are handled for you.

SELECT
  lead.source,
  MEASURE(lead.count),
  MEASURE(lead.percentage_closed_won)
FROM lead
WHERE lead.created_at >= '2024-01-01'
GROUP BY 1;

When querying the semantic layer, measures must be wrapped in an aggregate function. Use MEASURE() as a universal option that works with any measure type, or use a matching SQL aggregate like SUM() or COUNT(). Standard BI tools that generate SQL with SUM()/COUNT() will work automatically for most measures.

If you use an aggregate function that doesn't match the measure's underlying type (e.g., AVG() on a count measure), you'll receive an error: Measure aggregation type doesn't match. If you're writing queries manually, use MEASURE() to avoid this. If you're using a BI tool that auto-generates SQL, change the aggregation function it applies to match the measure's type.

See the Semantic Layer section for available models.

Export Views

Flat tables with all fields pre-joined. Best for data ingestion, syncing to your own warehouse, or when you need raw row-level data.

-- Incremental load: fetch new, updated, and deleted records since your last sync
SELECT * FROM export_lead
WHERE updated_at >= '2024-01-15T00:00:00Z'
   OR deleted_at >= '2024-01-15T00:00:00Z';

See the Export Views section for available views.

Troubleshooting

Problem	Solution
SSL errors	Ensure your client is configured with sslmode=require. All connections require SSL.
Connection refused	Verify the host (analytics-sql.seconddoor.app) and port (5432). Check firewall rules.
Invalid credentials	Double-check your username and password. Contact the SDH team if you need credentials reset.
Query timeout	Add filters (e.g., date ranges) or use LIMIT to reduce the result set size.