Create and Optimize Mode Analytics Reports

This skill applies Mode Analytics expertise across query writing, visualization, dashboard design, and Mode's advanced features. For SQL it writes parameterized queries with Mode's {{ parameter_name }} syntax (text and dropdown parameters, including an inclause filter), uses CTEs for multi-step analysis, applies mode_datetime() for time zone handling, and structures queries for reuse across visualizations. Advanced patterns include cohort analysis (grouping users by first-order month and tracking active users per subsequent month) and window-function analytics like seven-day moving averages and week-over-week growth via lag():

WITH user_cohorts AS (
  SELECT 
    user_id,
    date_trunc('month', first_order_date) as cohort_month
  FROM (
    SELECT 
      user_id,
      min(created_at) as first_order_date
    FROM orders
    GROUP BY 1
  ) first_orders
),
user_activities AS (
  SELECT 
    c.cohort_month,
    date_trunc('month', o.created_at) as activity_month,
    count(DISTINCT o.user_id) as active_users
  FROM user_cohorts c
  LEFT JOIN orders o ON c.user_id = o.user_id
  GROUP BY 1, 2
)
SELECT 
  cohort_month,
  activity_month,
  active_users,
  extract(epoch from activity_month - cohort_month) / 2629746 as months_since_cohort
FROM user_activities
WHERE cohort_month IS NOT NULL
ORDER BY 1, 2

For visualization it matches chart type to analysis intent - line charts for time series, bar charts for categorical comparisons, scatter plots for correlation, heatmaps for cohort or day-of-week patterns, funnel charts for step-by-step conversion - and builds advanced multi-axis charts in Mode's Python notebooks with Plotly. For dashboards it structures an executive layout: a KPI summary row with WoW/MoM changes, a trend chart with annotations, breakdown views by geography/channel/segment, and drill-down detail tables, often built from a current-vs-previous-period comparison query. For performance it recommends EXPLAIN ANALYZE to find bottlenecks, filtering early inside CTEs, avoiding SELECT *, aggregating before joins, and leveraging Mode's query cache via consistent parameter naming - plus building reusable dbt or Mode-pipeline views for common metrics like daily active users.

For collaboration it documents each query's business logic, uses meaningful chart titles and axis labels, and records refresh schedules and parameter expectations. For distribution it configures public links, scheduled email reports, and row-level security for sensitive data, and uses Mode's API to trigger report runs programmatically with custom parameters. It also supports custom CSS for branded notebook styling (chart containers, KPI cards).

Use it when building or optimizing Mode Analytics reports - parameterized SQL, cohort/window-function analysis, dashboard layout, or API-driven report automation. It consistently prioritizes data accuracy, clear visualization design, and a coherent data story over decorative complexity - custom CSS and multi-axis charts are tools for clarity, not for their own sake.

Input is the underlying data warehouse tables and the report's parameters. Output is parameterized SQL queries, dashboard layouts with KPI summaries and drill-downs, Plotly-based custom visualizations, reusable dbt/Mode views, and API calls for automated report execution.

It works within Mode's SQL/Liquid templating and Python notebook environment (pandas, Plotly), against a dbt-modeled or Mode-pipeline data warehouse, and integrates with Mode's REST API for triggering scheduled or parameterized report runs.

Data analysts and BI engineers building or maintaining Mode Analytics reports who need efficient parameterized SQL, cohort and trend analysis, well-structured executive dashboards, and automated report distribution.