Query Replo analytics
query_replo_analyticsRead-onlyIdempotent
Run read-only ClickHouse SQL over a project's Replo analytics for traffic, conversion, revenue, and session-level questions. No saved Insights report is required. Use this for ad hoc questions or when list_insights_reports finds no relevant report; an empty report list does not mean analytics are unavailable. Queryable public tables are session_attribution, events_computed, currency_exchange_rates, daily_page_rollups, daily_namespace_rollups, daily_namespace_purchase_rollups, benchmark_comparison, benchmark_trend, daily_category_benchmarks, daily_namespace_benchmark_profiles, benchmark_brand_metrics, daily_namespace_benchmark_rollups, and benchmark_namespace_domain. Only SELECT/WITH queries are accepted, and project-scoped tables are automatically restricted to the requested project and its authorized same-workspace, same-store siblings. Table columns and external metric views are listed in the query input schema. Also supports connected external metrics through those parameterized views. Check get_integration_status before querying; external data can be cached for eight hours and an empty result does not prove zero activity. Use provider tools for discovery or provider-native reports.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | Run a read-only analytics query for a project. Rules: - Use SELECT or WITH queries only. - Do not add project or namespace filters; project scoping is enforced server-side. - Use session_attribution(since, until) for cross-project session and attributed revenue; stored computed metrics and page rollups are project-local. - Session revenue repeats across visited pages: deduplicate by subject for totals. Attributed revenue splits credit across eligible pageviews before domain filters. - Aggregate-state rollup columns must use the matching ClickHouse merge function, such as countMerge, uniqMerge, or sumMerge, and queries must group by selected dimensions. - Maximum execution time: 60 seconds. - Maximum result rows: 100000. Queryable tables, their columns, and the external metric views for connected integrations are listed at https://docs.replo.app/api-reference/analytics-tables. External metric views take named since/until dates (YYYY-MM-DD, inclusive); the views that also need settings JSON or another argument say so on that page. Credentials and project scope are injected server-side: never supply auth_token or project_id. In MCP, call get_integration_status first for connection IDs, setup links, and supported views. Missing or misconfigured integrations can return zero rows: do not report that as measured zero activity. Use provider-specific tools for metric/property discovery and detailed provider-native reports. External results can be cached for up to eight hours, and report views cap provider results at 10,000 rows; narrow dates or dimensions if exceeded. Match currencies, reporting timezones, and attribution definitions before combining sources. Queryable tables: Parameterized view: session_attribution(since='YYYY-MM-DD', until='YYYY-MM-DD'), inclusive UTC dates. Computes USD purchase, session, and equal-pageview attributed revenue across authorized sibling projects within that window, before domain filters. Cart/checkout views receive no attribution. Deduplicate sessionRevenueUsd by subject before summing. Inherits source row policies. Project scoped: yes. ```sql session_attribution ( subject UUID, date DateTime64(3), name LowCardinality(String), domain String, path String, title String, purchaseRevenueUsd Float64, sessionRevenueUsd Float64, sessionPurchaseCount UInt64, attributedRevenueUsd Float64 ) ``` Raw events with project-local computed session metrics. Use session_attribution for revenue across linked projects. Project scoped: yes. ```sql events_computed ( subject UUID, date DateTime64(3), name LowCardinality(String), path String, domain String, currencyCode String, subjectPurchaseSum Float64, subjectPurchaseCount Float64, subjectPurchaseFraction Float64, subjectPurchaseFractionalSum Float64, title String ) ``` Currency exchange rates for multi-currency reporting. Project scoped: no. ```sql currency_exchange_rates ( date Date, code String, rate Float32 ) ``` Pre-aggregated daily page metrics by day, domain, path, and UTM source. Revenue/conversion attribution is project-local; use session_attribution across linked projects. Project scoped: yes. ```sql daily_page_rollups ( day Date, domain String, path String, utm_source String, views_state AggregateFunction(count), unique_sessions_state AggregateFunction(uniq, UUID), converting_sessions_state AggregateFunction(uniq, UUID), fractional_conversions_state AggregateFunction(sum, Float64), fractional_revenue_usd_state AggregateFunction(sum, Float64), session_conversions_state AggregateFunction(sum, UInt64), session_revenue_usd_state AggregateFunction(sum, Float64) ) ``` Pre-aggregated daily project traffic metrics by day and UTM source. Project scoped: yes. ```sql daily_namespace_rollups ( day Date, utm_source String, views_state AggregateFunction(count), unique_sessions_state AggregateFunction(uniq, UUID), converting_sessions_state AggregateFunction(uniq, UUID) ) ``` Pre-aggregated daily purchase metrics by day and UTM source. Project scoped: yes. ```sql daily_namespace_purchase_rollups ( day Date, utm_source String, purchase_count_state AggregateFunction(count), total_revenue_usd_state AggregateFunction(sum, Float64), unique_purchasing_sessions_state AggregateFunction(uniq, UUID) ) ``` Parameterized view: benchmark_comparison(window_days=1|7|30|90, since='YYYY-MM-DD', until='YYYY-MM-DD', channel='all'|'affiliate'|'email_sms'|'ai_assistant'|'paid_social'|'paid_search'|'organic_social'|'other_tagged'|'organic_search'|'direct'|'referral', device='all'|'mobile'|'tablet'|'desktop', by_location=0|1, by_price_tier=0|1). One row per day and metric: this project's own value over the window next to anonymized peers in the deepest cohort that passes (10+ brands, not low confidence). It keeps the asked-for axes and the narrowest category it can, trying country, then sub-region, then region at each category level, then drops price tier, then location. Price tiers are p0_25, p25_50, p50_75, p75_100: quartiles of peer brands' 90-day AOV in the root category; a brand with under 30 orders in 90 days has none. A brand in the top 10% or top 1% is compared within p90_100 or p99_100 when that band has enough brands. Metrics: sessions, pages_per_session, add_to_cart_rate, checkout_rate, conversion_rate, orders, revenue_usd, aov_usd, revenue_per_session_usd, and, only when channel='all' and device='all', the traffic shares channel_share_<channel> (one per channel above) and device_share_<device> (mobile, tablet, desktop): the share of this brand's sessions from that channel or device, next to peers' own shares, where a peer with none counts as 0. They need no checkout data. A brand with no category and no orders is compared on sessions, pages_per_session and the shares with all brands in every vertical (comparison_level 'all brands'), and on nothing else. days_covered is how many of the window's days have data: fewer than window_days means history is shorter than the window or the project had no sessions on some days. suspected_bot_sessions is how many sessions were taken out of my_value as suspected bot traffic (a channel and device with 3 times its usual sessions that view one page and don't add to cart or buy); peers are cleaned the same way, and Insights still counts these sessions. Peer columns are NULL when no cohort passes. Inherits source row policies. Project scoped: yes. ```sql benchmark_comparison ( date Date, metric String, window_days UInt16, days_covered UInt16, suspected_bot_sessions UInt64, channel String, device String, my_value Nullable(Float64), peer_blended Nullable(Float64), peer_p25 Nullable(Float64), peer_median Nullable(Float64), peer_p75 Nullable(Float64), peer_p90 Nullable(Float64), peer_brands UInt32, confidence String, compared_category String, compared_location String, compared_price_tier String, comparison_level String, my_price_tier String, my_aov_90d_usd Float64, my_location String ) ``` Parameterized view: benchmark_trend(since, until, channel, device, by_location, by_price_tier, ma_days, ema_days). benchmark_comparison over 1-day windows plus moving averages per metric: my_sma and peer_sma over the last ma_days days, my_ema and peer_ema with an ema_days half-life. Days without a value are skipped, not counted as 0. Inherits source row policies. Project scoped: yes. ```sql benchmark_trend ( date Date, metric String, my_value Nullable(Float64), peer_blended Nullable(Float64), peer_median Nullable(Float64), peer_p25 Nullable(Float64), peer_p75 Nullable(Float64), peer_brands UInt32, compared_category String, my_sma Nullable(Float64), peer_sma Nullable(Float64), my_ema Nullable(Float64), peer_ema Nullable(Float64) ) ``` Anonymized daily peer statistics per cohort, readable across projects. One row per snapshot_date, window_days (1, 7, 30, 90), vertical (ecommerce, saas, services, or all: every vertical together, traffic metrics only, with no categories or price tiers), category_key (an industry path such as 'Home & Garden > Kitchen & Dining', or '' for the whole vertical), channel, device (mobile, tablet, desktop), location_key (the brand's home location (location path such as '/Europe/Western Europe/Germany', or '' for everywhere), price_tier (p0_25, p25_50, p50_75, p75_100, plus the overlapping top bands p90_100 and p99_100) and metric; 'all' (or '') on an axis means not cut by it. blended is pooled across brands and one large brand can move it; p25 to p90 are brand-level quantiles. Rows need 10 brands. Traffic shares (channel_share_<channel>, device_share_<device>) are on channel='all', device='all' rows only. confidence is high, medium or low from the relative standard error, or for shares from the standard error in share points: never show low rows to customers. Query with FINAL. Project scoped: no. ```sql daily_category_benchmarks ( snapshot_date Date, window_days UInt16, vertical LowCardinality(String), category_level UInt8, category_key String, channel LowCardinality(String), device LowCardinality(String), location_level UInt8, location_key String, price_tier LowCardinality(String), metric LowCardinality(String), brands UInt32, blended Float64, p25 Float64, p50 Float64, p75 Float64, p90 Nullable(Float64), mean Float64, stddev Float64, rse Float64, confidence LowCardinality(String) ) ``` This project's brand (root domain) and its value on every benchmark axis per day: vertical, category_path, location_path, 90-day AOV and orders, price tier, top bands (p90_100, p99_100) it's in, and its root category's tier boundaries (tier_p25_usd to tier_p99_usd, NULL until the category has enough priced peers). Query with FINAL. Project scoped: yes. ```sql daily_namespace_benchmark_profiles ( snapshot_date Date, brand String, vertical LowCardinality(String), category_path Array(String), country_code LowCardinality(String), location_path Array(String), aov_90d_usd Float64, orders_90d UInt64, price_tier LowCardinality(String), top_bands Array(String), tier_p25_usd Nullable(Float64), tier_p50_usd Nullable(Float64), tier_p75_usd Nullable(Float64), tier_p90_usd Nullable(Float64), tier_p99_usd Nullable(Float64) ) ``` Parameterized view: benchmark_brand_metrics(start='YYYY-MM-DD', end='YYYY-MM-DD'). This project's brand totals per as_of day, 1/7/30/90-day window, channel and device ('all' included on both), as used for its benchmarks. days_covered is how many of the window's days have data. Suspected bot traffic is taken out of sessions and page_views, and suspected_bot_sessions counts it. Prefer benchmark_comparison. Inherits source row policies. Project scoped: yes. ```sql benchmark_brand_metrics ( as_of Date, window_days UInt16, days_covered UInt16, brand String, channel String, device String, sessions UInt64, page_views UInt64, add_to_cart_sessions UInt64, checkout_sessions UInt64, purchase_sessions UInt64, orders UInt64, revenue_usd Float64, suspected_bot_sessions UInt64 ) ``` This project's daily benchmark inputs by session channel and device, built from each session 3 hours after it starts (bots excluded). Each session is on exactly one row, so sum() the counts across rows and days. revenue_usd is converted at the purchase day's rate. Project scoped: yes. ```sql daily_namespace_benchmark_rollups ( day Date, channel LowCardinality(String), device LowCardinality(String), sessions UInt64, page_views UInt64, add_to_cart_sessions UInt64, checkout_sessions UInt64, purchase_sessions UInt64, orders UInt64, revenue_usd Float64 ) ``` The root domain this project is benchmarked as: the one its pages got the most views on in the last 30 days. Query with FINAL. Project scoped: yes. ```sql benchmark_namespace_domain ( root_domain String, views_30d UInt64, updated_at DateTime ) ``` External metric views: triplewhale_metrics_daily, triplewhale_metrics_totals, triplewhale_metrics_list, contentsquare_metrics_daily, contentsquare_metrics_totals, contentsquare_metrics_list, mixpanel_metrics_daily, mixpanel_metrics_totals, mixpanel_metrics_list, lifetimely_metrics_daily, lifetimely_metrics_totals, lifetimely_metrics_list, faire_metrics_daily, faire_metrics_totals, faire_metrics_list, alia_metrics_daily, alia_metrics_totals, alia_metrics_list, intelligems_metrics_daily, intelligems_metrics_totals, intelligems_metrics_list, intelligems_experience_metrics_daily, intelligems_experience_metrics_totals, intelligems_experience_metrics_list, meta_ads_ad_daily, meta_ads_campaign_daily, replo_roi_monthly, shopify_sales_monthly, google_analytics_report, google_search_console_report. | |
| projectId | Yes |