Aggregation API

Source: /Aggregation-API/

Overview

AdonisJS 5 application that uses DuckDB in-memory to query legacy Parquet files stored at PARQUET_SUMMARIES. Serves as a read-only analytics query service for the legacy pipeline.

The Backend proxies requests to this service via RelayService with x-auth-token header.

Parquet File Schemas

v3/creative/hourly-buckets

16 metric columns defined in BucketValidator.ts (ALL_METRICS):

impressions, interacted, started, continued, creative_actions, link_clicks, header_clicks, background_clicks, reached_end, plays, watched_25, watched_50, watched_75, watched_100, viewable_impressions, seconds_in_view

v3/flow/hourly-buckets

creative_id, object_id, hits

v3/creative/daily-buckets-with-domain-dsp

Same metrics as hourly-buckets plus domain, dsp, day_bucket columns.

Endpoints

Endpoint	Purpose	Key Query
/v5/counts	Summary counts (impressions, started, interacted)	SUM() from hourly-buckets grouped by resource
/v5/buckets	Time-series data for charts	Time scaffold + LEFT JOIN with COALESCE to 0
/v5/flow	Flow node analytics	SUM(hits) per object_id, reconstructed by FlowParser
/v5/domains	Domain/DSP breakdown	All metrics from daily-buckets-with-domain-dsp
/v5/exports/csv	CSV export with rate columns	Adds computed rate columns
/v5/activity	First/last active dates	MIN/MAX(day_bucket)
/v5/creatives	Filter by impression threshold	HAVING SUM(impressions) > threshold

Rate Formulas (CSV Exports)

Rate	Formula
rate_interacted	interacted / impressions * 100
rate_creative_actions	creative_actions / impressions * 100
rate_plays	plays / impressions * 100
rate_watched_25/50/75/100	watched_X / impressions * 100

Key Files

File	Purpose
app/Validators/BucketValidator.ts	ALL_METRICS array defining valid metric columns
app/Controllers/Http/v5/FlowController.ts	Flow analytics with FlowParser
app/Controllers/Http/v5/DomainsController.ts	Domain/DSP breakdown
app/Controllers/Http/v5/ExportsController.ts	CSV export with computed rates
app/Controllers/Http/v5/ActivityController.ts	First/last active dates
app/Helpers/FlowParser.ts	Reconstructs flow tree from flat hits data