All Data Join

Query

--Join data from daily_sales, daily_subscriptions, and daily_subscription_revenue by date

WITH base_all_completed_days AS (
  SELECT
      id.dt as date,
      id.period_start_date as period_start,

      s.units_sold,
      s.UNITS_REFUNDED,
      s.net_units_sold,
      s.gross_sales,
      s.REFUNDS,
      s.discounts,
      s.net_revenue,

      sub.new_subs,
      sub.new_subs_paid,
      sub.new_subs_annual,
      sub.new_subs_monthly,
      sub.new_subs_free,
      sub.trial_to_paid_conversions,
      sub.trial_to_paid_conversions_monthly,
      sub.trial_to_paid_conversions_annual,
      sub.cancellations,
      sub.cancellations_monthly,
      sub.cancellations_annual,
      sub.cancellations_free,
      sub.active_subs_eod,
      sub.active_paid_subs_eod,
      sub.active_paid_monthly_subs_eod,
      sub.active_paid_annual_subs_eod,
      sub.active_free_subs_eod,
      sub.active_trials_eod,

      subrev.monthly_sub_revenue,
      subrev.annual_sub_revenue

  FROM included_dates id
  LEFT OUTER JOIN daily_sales s
    ON id.dt = s.dt
  LEFT OUTER JOIN daily_subscriptions sub
    ON id.dt = sub.dt
  LEFT OUTER JOIN daily_subscription_revenue subrev
    ON id.dt = subrev.dt
),

--Incorporate user selection for displaying or hiding current period

base AS (
  SELECT *
  FROM base_all_completed_days
  WHERE
    {{ include_current_period }} <> 'No'
    OR date <
      CASE
        WHEN {{ time_grain }} = 'Day' THEN CURRENT_DATE
        WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
        WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
        WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
      END
),

--Rank daily metrics by date (only used for snapshot metrics, so that we can display the end-of-period values only)

ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY period_start
      ORDER BY date DESC
    ) AS period_day_rank
  FROM base
),

--Incoprporate user selection for trailing period used in forecasts, if shown

forecast_window AS (
  SELECT
    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN 7
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN 14
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN 28
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN 56
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('week', CURRENT_DATE), CURRENT_DATE), 1)
          WHEN {{ time_grain }} = 'Month' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('month', CURRENT_DATE), CURRENT_DATE), 1)
          WHEN {{ time_grain }} = 'Quarter' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('quarter', CURRENT_DATE), CURRENT_DATE), 1)
          ELSE 1
        END
    END AS lookback_days,

    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN CURRENT_DATE - INTERVAL 7 DAY
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN CURRENT_DATE - INTERVAL 14 DAY
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN CURRENT_DATE - INTERVAL 28 DAY
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN CURRENT_DATE - INTERVAL 56 DAY
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
          WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
          WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
          ELSE CURRENT_DATE - INTERVAL 1 DAY
        END
    END AS window_start_date,

    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN CURRENT_DATE - INTERVAL 8 DAY
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN CURRENT_DATE - INTERVAL 15 DAY
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN CURRENT_DATE - INTERVAL 29 DAY
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN CURRENT_DATE - INTERVAL 57 DAY
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE) - INTERVAL 1 DAY
          WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE) - INTERVAL 1 DAY
          WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL 1 DAY
          ELSE CURRENT_DATE
        END
    END AS snapshot_anchor_date
),

--Simplify forecast window output

forecast_window_single AS (
  SELECT
    MAX(lookback_days) AS lookback_days,
    MAX(window_start_date) AS window_start_date,
    MAX(snapshot_anchor_date) AS snapshot_anchor_date
  FROM forecast_window
),

forecast_constants AS (
  SELECT
    lookback_days,
    window_start_date,
    snapshot_anchor_date
  FROM forecast_window_single
),

--Calculate average daily values for summable metrics over trailing period used as basis for forecast

forecast_run_rate_summed AS (
  SELECT
    AVG(COALESCE(units_sold, 0)) AS avg_daily_gross_units_sold,
    AVG(COALESCE(UNITS_REFUNDED, 0)) as avg_daily_units_returned,
    AVG(COALESCE(net_units_sold, 0)) AS avg_daily_net_units_sold,
    AVG(COALESCE(gross_sales, 0)) as avg_daily_gross_sales_revenue,
    AVG(COALESCE(REFUNDS, 0)) as avg_daily_refund_amount,
    AVG(COALESCE(discounts, 0)) as avg_daily_discount_amount,
    AVG(COALESCE(net_revenue, 0)) AS avg_daily_net_revenue,


    AVG(COALESCE(new_subs, 0)) AS avg_daily_new_subs,
    AVG(COALESCE(new_subs_paid, 0)) AS avg_daily_new_subs_paid,
    AVG(COALESCE(new_subs_annual, 0)) AS avg_daily_new_subs_annual,
    AVG(COALESCE(new_subs_monthly, 0)) AS avg_daily_new_subs_monthly,
    avg(coalesce(new_subs_free, 0)) as avg_daily_new_subs_free,
    AVG(COALESCE(trial_to_paid_conversions, 0)) AS avg_daily_trial_to_paid_conversions,
    AVG(COALESCE(trial_to_paid_conversions_monthly, 0)) AS avg_daily_trial_to_paid_conversions_monthly,
    AVG(COALESCE(trial_to_paid_conversions_annual, 0)) AS avg_daily_trial_to_paid_conversions_annual,
    AVG(COALESCE(cancellations, 0)) AS avg_daily_cancellations,
    AVG(COALESCE(cancellations_monthly, 0)) AS avg_daily_cancellations_monthly,
    AVG(COALESCE(cancellations_annual, 0)) AS avg_daily_cancellations_annual,
    AVG(COALESCE(cancellations_free, 0)) AS avg_daily_cancellations_free,

    AVG(COALESCE(monthly_sub_revenue, 0)) AS avg_daily_monthly_sub_revenue,
    AVG(COALESCE(annual_sub_revenue, 0)) AS avg_daily_annual_sub_revenue

  FROM base_all_completed_days
  CROSS JOIN forecast_constants fc
  WHERE date >= fc.window_start_date
),

--Calculate average daily diffs for snapshop metrics over trailing period used as basis for forecast

snapshot_points AS (
  SELECT
    fc.lookback_days,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_subs_eod END) AS active_subs_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_subs_eod END) AS active_subs_eod_start,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_paid_subs_eod END) AS active_paid_subs_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_paid_subs_eod END) AS active_paid_subs_eod_start,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_paid_monthly_subs_eod END) AS active_paid_monthly_subs_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_paid_monthly_subs_eod END) AS active_paid_monthly_subs_eod_start,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_paid_annual_subs_eod END) AS active_paid_annual_subs_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_paid_annual_subs_eod END) AS active_paid_annual_subs_eod_start,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_free_subs_eod END) AS active_free_subs_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_free_subs_eod END) AS active_free_subs_eod_start,

    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_trials_eod END) AS active_trials_eod_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_trials_eod END) AS active_trials_eod_start
  FROM base_all_completed_days b
  CROSS JOIN forecast_constants fc
  GROUP BY fc.lookback_days
),

forecast_run_rate_snapshots AS (
  SELECT
    (active_subs_eod_end - active_subs_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_subs_eod,
    (active_paid_subs_eod_end - active_paid_subs_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_paid_subs_eod,
    (active_paid_monthly_subs_eod_end - active_paid_monthly_subs_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_paid_monthly_subs_eod,
    (active_paid_annual_subs_eod_end - active_paid_annual_subs_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_paid_annual_subs_eod,
    (active_trials_eod_end - active_trials_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_trials_eod,
    (active_free_subs_eod_end - active_free_subs_eod_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_free_subs_eod
  FROM snapshot_points
),

--Aggregate actuals over reporting period

period_rollup AS (
  SELECT
    period_start,

    CASE
      WHEN {{ time_grain }} = 'Day' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0') || '-' ||
        LPAD(CAST(EXTRACT(DAY FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Week' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0') || '-' ||
        LPAD(CAST(EXTRACT(DAY FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Month' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Quarter' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-Q' ||
        CAST(EXTRACT(QUARTER FROM period_start) AS VARCHAR)
    END AS period,


    SUM(COALESCE(units_sold, 0)) AS gross_units_sold_actual,    
    SUM(COALESCE(units_refunded, 0)) AS units_returned_actual,
    SUM(COALESCE(net_units_sold, 0)) AS net_units_sold_actual,
    SUM(COALESCE(gross_sales, 0)) AS gross_sales_revenue_actual,
    SUM(COALESCE(refunds, 0)) AS refund_amount_actual,
    SUM(COALESCE(discounts, 0)) AS discount_amount_actual,
    SUM(COALESCE(net_revenue, 0)) AS net_revenue_actual,
    SUM(COALESCE(new_subs, 0)) AS new_subs_actual,
    SUM(COALESCE(new_subs_paid, 0)) AS new_subs_paid_actual,
    SUM(COALESCE(new_subs_annual, 0)) AS new_subs_annual_actual,
    SUM(COALESCE(new_subs_monthly, 0)) AS new_subs_monthly_actual,
    SUM(COALESCE(new_subs_free, 0)) AS new_subs_free_actual,
    SUM(COALESCE(trial_to_paid_conversions, 0)) AS trial_to_paid_conversions_actual,
    SUM(COALESCE(trial_to_paid_conversions_monthly, 0)) AS trial_to_paid_conversions_monthly_actual,
    SUM(COALESCE(trial_to_paid_conversions_annual, 0)) AS trial_to_paid_conversions_annual_actual,
    SUM(COALESCE(cancellations, 0)) AS cancellations_actual,
    SUM(COALESCE(cancellations_monthly, 0)) AS cancellations_monthly_actual,
    SUM(COALESCE(cancellations_annual, 0)) AS cancellations_annual_actual,
    SUM(COALESCE(cancellations_free, 0)) AS cancellations_free_actual,

    SUM(COALESCE(monthly_sub_revenue, 0)) as monthly_sub_revenue_actual,
    SUM(COALESCE(annual_sub_revenue, 0)) as annual_sub_revenue_actual,

    MAX(CASE WHEN period_day_rank = 1 THEN active_subs_eod END) AS active_subs_eod_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_paid_subs_eod END) AS active_paid_subs_eod_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_paid_monthly_subs_eod END) AS active_paid_monthly_subs_eod_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_paid_annual_subs_eod END) AS active_paid_annual_subs_eod_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_trials_eod END) AS active_trials_eod_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_free_subs_eod END) AS active_free_subs_eod_actual,

    CASE
      WHEN {{ include_current_period }} <> 'No'
       AND {{ time_grain }} <> 'Day'
       AND period_start =
         CASE
           WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
           WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
           WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
         END
      THEN 'Current Period'
      ELSE NULL
    END AS current_period_flag,

    CASE
      WHEN {{ time_grain }} = 'Week'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('week', CURRENT_DATE) + INTERVAL 6 DAY) + 1
      WHEN {{ time_grain }} = 'Month'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('month', CURRENT_DATE) + INTERVAL 1 MONTH - INTERVAL 1 DAY) + 1
      WHEN {{ time_grain }} = 'Quarter'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL 3 MONTH - INTERVAL 1 DAY) + 1
      ELSE 0
    END AS days_remaining_in_period

  FROM ranked
  GROUP BY 1
),

--Incorporate current period forecasts, if shown

final AS (
  SELECT
    p.period_start,
    case 
        when {{ include_current_period }} = 'Yes (with rest-of-period forecasts)' AND p.current_period_flag = 'Current Period' THEN p.period || ' (Forecast)'
        WHEN {{ include_current_period }} = 'Yes (incomplete data)' AND p.current_period_flag = 'Current Period' THEN p.period || ' (Incomplete)'
        else p.period end as period,
    p.current_period_flag,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.gross_units_sold_actual + (s.avg_daily_gross_units_sold * p.days_remaining_in_period)
      ELSE p.gross_units_sold_actual
    END AS gross_units_sold,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.units_returned_actual + (s.avg_daily_units_returned * p.days_remaining_in_period)
      ELSE p.units_returned_actual
    END AS units_returned,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.net_units_sold_actual + (s.avg_daily_net_units_sold * p.days_remaining_in_period)
      ELSE p.net_units_sold_actual
    END AS net_units_sold,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.gross_sales_revenue_actual + (s.avg_daily_gross_sales_revenue * p.days_remaining_in_period)
      ELSE p.gross_sales_revenue_actual
    END AS gross_sales_revenue,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.refund_amount_actual + (s.avg_daily_refund_amount * p.days_remaining_in_period)
      ELSE p.refund_amount_actual
    END AS refund_amounts,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.discount_amount_actual + (s.avg_daily_discount_amount * p.days_remaining_in_period)
      ELSE p.discount_amount_actual
    END AS discount_amounts,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.net_revenue_actual + (s.avg_daily_net_revenue * p.days_remaining_in_period)
      ELSE p.net_revenue_actual
    END AS net_sales_revenue,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.new_subs_actual + (s.avg_daily_new_subs * p.days_remaining_in_period)
      ELSE p.new_subs_actual
    END AS new_subs,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.new_subs_paid_actual + (s.avg_daily_new_subs_paid * p.days_remaining_in_period)
      ELSE p.new_subs_paid_actual
    END AS new_subs_paid,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.new_subs_annual_actual + (s.avg_daily_new_subs_annual * p.days_remaining_in_period)
      ELSE p.new_subs_annual_actual
    END AS new_subs_annual,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.new_subs_monthly_actual + (s.avg_daily_new_subs_monthly * p.days_remaining_in_period)
      ELSE p.new_subs_monthly_actual
    END AS new_subs_monthly,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.new_subs_free_actual + (s.avg_daily_new_subs_free * p.days_remaining_in_period)
      ELSE p.new_subs_free_actual
    END AS new_subs_free,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.trial_to_paid_conversions_actual + (s.avg_daily_trial_to_paid_conversions * p.days_remaining_in_period)
      ELSE p.trial_to_paid_conversions_actual
    END AS trial_to_paid_conversions,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.trial_to_paid_conversions_monthly_actual + (s.avg_daily_trial_to_paid_conversions_monthly * p.days_remaining_in_period)
      ELSE p.trial_to_paid_conversions_monthly_actual
    END AS trial_to_paid_conversions_monthly,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.trial_to_paid_conversions_annual_actual + (s.avg_daily_trial_to_paid_conversions_annual * p.days_remaining_in_period)
      ELSE p.trial_to_paid_conversions_annual_actual
    END AS trial_to_paid_conversions_annual,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.cancellations_actual + (s.avg_daily_cancellations * p.days_remaining_in_period)
      ELSE p.cancellations_actual
    END AS cancellations,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.cancellations_monthly_actual + (s.avg_daily_cancellations_monthly * p.days_remaining_in_period)
      ELSE p.cancellations_monthly_actual
    END AS cancellations_monthly,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.cancellations_annual_actual + (s.avg_daily_cancellations_annual * p.days_remaining_in_period)
      ELSE p.cancellations_annual_actual
    END AS cancellations_annual,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.cancellations_free_actual + (s.avg_daily_cancellations_free * p.days_remaining_in_period)
      ELSE p.cancellations_free_actual
    END AS cancellations_free,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.monthly_sub_revenue_actual + (s.avg_daily_monthly_sub_revenue * p.days_remaining_in_period)
      ELSE p.monthly_sub_revenue_actual
    END AS monthly_subscription_revenue,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.annual_sub_revenue_actual + (s.avg_daily_annual_sub_revenue * p.days_remaining_in_period)
      ELSE p.annual_sub_revenue_actual
    END AS annual_subscription_revenue,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_subs_eod_actual + (snap.avg_daily_diff_active_subs_eod * p.days_remaining_in_period)
      ELSE p.active_subs_eod_actual
    END AS active_subs_eod,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_paid_subs_eod_actual + (snap.avg_daily_diff_active_paid_subs_eod * p.days_remaining_in_period)
      ELSE p.active_paid_subs_eod_actual
    END AS active_paid_subs_eod,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_paid_monthly_subs_eod_actual + (snap.avg_daily_diff_active_paid_monthly_subs_eod * p.days_remaining_in_period)
      ELSE p.active_paid_monthly_subs_eod_actual
    END AS active_paid_monthly_subs_eod,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_paid_annual_subs_eod_actual + (snap.avg_daily_diff_active_paid_annual_subs_eod * p.days_remaining_in_period)
      ELSE p.active_paid_annual_subs_eod_actual
    END AS active_paid_annual_subs_eod,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_free_subs_eod_actual + (snap.avg_daily_diff_active_free_subs_eod * p.days_remaining_in_period)
      ELSE p.active_free_subs_eod_actual
    END AS active_free_subs_eod,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_trials_eod_actual + (snap.avg_daily_diff_active_trials_eod * p.days_remaining_in_period)
      ELSE p.active_trials_eod_actual
    END AS active_trials_eod

  FROM period_rollup p
  CROSS JOIN forecast_run_rate_summed s
  CROSS JOIN forecast_run_rate_snapshots snap
),

chart_window AS (
  SELECT
    CASE
      WHEN {{ custom_date_start }} IS NOT NULL
        THEN CAST({{ custom_date_start }} AS DATE)

      WHEN {{ time_grain }} = 'Day'
        THEN CURRENT_DATE - INTERVAL 180 DAY

      WHEN {{ time_grain }} = 'Week'
        THEN DATE_TRUNC('week', CURRENT_DATE) - INTERVAL 21 WEEK

      WHEN {{ time_grain }} = 'Month'
        THEN DATE_TRUNC('month', CURRENT_DATE) - INTERVAL 12 MONTH

      WHEN {{ time_grain }} = 'Quarter'
        THEN DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL 8 QUARTER
    END AS start_date,

    CASE
      WHEN {{ custom_date_end }} IS NOT NULL
        THEN CAST({{ custom_date_end }} AS DATE)

      ELSE CURRENT_DATE
    END AS end_date
),

final_filtered AS (
  SELECT f.*, f.net_sales_revenue + f.monthly_subscription_revenue + f.annual_subscription_revenue as total_revenue
  FROM final f
  CROSS JOIN chart_window w
  WHERE f.period_start >= w.start_date
    AND f.period_start <= w.end_date
)

SELECT *
FROM final_filtered f
ORDER BY period_start;

Overview

The central reporting query for the sales and revenue dashboard. Joins daily_sales, daily_subscriptions, and daily_subscription_revenue onto the date spine, rolls daily data up to the selected time grain, and optionally projects the current incomplete period forward using a run-rate forecast.

Depends on: included_dates (date_spine), daily_sales, daily_subscriptions, daily_subscription_revenue

Parameters

Parameter	Accepted Values	Description
`{{ time_grain }}`	`'Day'`, `'Week'`, `'Month'`, `'Quarter'`	Aggregation period for all output metrics
`{{ include_current_period }}`	`'No'`, `'Yes (incomplete data)'`, `'Yes (with rest-of-period forecasts)'`	Controls whether the current in-progress period is included, and if so, whether remaining days are forecast
`{{ forecast_basis }}`	`'Trailing 7 days'`, `'Trailing 14 days'`, `'Trailing 28 days'`, `'Trailing 56 days'`, `'Current period to date'`	Lookback window used to compute the daily run-rate for forecasting
`{{ custom_date_start }}`	Date or `NULL`	Optional override for the chart window start date. If `NULL`, defaults to a grain-appropriate lookback
`{{ custom_date_end }}`	Date or `NULL`	Optional override for the chart window end date. If `NULL`, defaults to `CURRENT_DATE`

Output Columns

Column	Description
`period_start`	Start date of the period
`period`	Human-readable period label (e.g. `"2025-01"`, `"Q1 2025"`). Appended with `" (Forecast)"` or `" (Incomplete)"` for the current period depending on `include_current_period`
`current_period_flag`	`'Current Period'` if the row represents the current in-progress period; otherwise `NULL`
`gross_units_sold`	Total units sold
`units_returned`	Total units refunded
`net_units_sold`	Units sold minus units returned
`gross_sales_revenue`	Total revenue before discounts and refunds
`refund_amounts`	Total refund amounts
`discount_amounts`	Total discount amounts applied
`net_sales_revenue`	Revenue after discounts and refunds (Shopify)
`new_subs`	New subscriptions created
`new_subs_paid`	New paid subscriptions (monthly + annual)
`new_subs_monthly`	New monthly subscriptions
`new_subs_annual`	New annual subscriptions
`new_subs_free`	New free subscriptions
`trial_to_paid_conversions`	Trial-to-paid conversions (monthly + annual)
`trial_to_paid_conversions_monthly`	Trial-to-paid conversions on monthly plan
`trial_to_paid_conversions_annual`	Trial-to-paid conversions on annual plan
`cancellations`	All cancellations
`cancellations_monthly`	Cancellations of monthly subscriptions
`cancellations_annual`	Cancellations of annual subscriptions
`cancellations_free`	Cancellations of free subscriptions
`monthly_subscription_revenue`	Revenue from paid monthly invoices (Stripe)
`annual_subscription_revenue`	Revenue from paid annual invoices (Stripe)
`active_subs_eod`	Active subscriptions at end of period (snapshot)
`active_paid_subs_eod`	Active paid subscriptions at end of period (snapshot)
`active_paid_monthly_subs_eod`	Active monthly subscriptions at end of period (snapshot)
`active_paid_annual_subs_eod`	Active annual subscriptions at end of period (snapshot)
`active_free_subs_eod`	Active free subscriptions at end of period (snapshot)
`active_trials_eod`	Active trials at end of period (snapshot)
`total_revenue`	`net_sales_revenue + monthly_subscription_revenue + annual_subscription_revenue`

CTEs

`base_all_completed_days`

Joins included_dates, daily_sales, daily_subscriptions, and daily_subscription_revenue into a single daily-grain dataset. All joins are left outer, so dates with no activity are retained.

`base`

Filters base_all_completed_days based on {{ include_current_period }}. If set to 'No', rows belonging to the current in-progress period are excluded.

`ranked`

Adds period_day_rank — a row number within each period ordered by date descending. Used to snapshot the most recent value of end-of-day subscription counts at period end.

`forecast_window` / `forecast_window_single` / `forecast_constants`

Computes three scalar values from {{ forecast_basis }} and {{ time_grain }}: - lookback_days — number of days in the run-rate window - window_start_date — start of the run-rate window - snapshot_anchor_date — the start-of-window date used for snapshot metric deltas

These are collapsed to a single row in forecast_constants for cross-joining downstream.

`forecast_run_rate_summed`

Computes average daily values for all summable metrics (sales, subscription events, revenue) over the forecast window. These averages are multiplied by days_remaining_in_period to project the current period forward.

`snapshot_points` / `forecast_run_rate_snapshots`

Computes run-rates for all end-of-day subscription count metrics by taking the difference between their values at CURRENT_DATE - 1 and at snapshot_anchor_date, divided by lookback_days. Unlike in the network growth report, all snapshot metrics here have forecasting fully enabled.

`period_rollup`

Aggregates the daily ranked data to the period level. Summable metrics are SUMmed with COALESCE(..., 0); snapshot (end-of-day) metrics use MAX(CASE WHEN period_day_rank = 1 ...) to capture the most recent value within the period. Also computes current_period_flag and days_remaining_in_period.

`final`

Applies forecast logic. For each metric, if the row is the current period and {{ include_current_period }} = 'Yes (with rest-of-period forecasts)', the actual-to-date value is augmented with avg_daily_rate × days_remaining_in_period. Snapshot metrics use the daily delta method instead. Also applies the " (Forecast)" / " (Incomplete)" suffix to the period label.

`chart_window`

Computes the date range for the final output filter. Uses {{ custom_date_start }} / {{ custom_date_end }} if provided; otherwise falls back to grain-appropriate defaults (e.g. trailing 12 months for Month grain, trailing 8 quarters for Quarter grain).

`final_filtered`

Applies the chart_window filter and computes total_revenue as net_sales_revenue + monthly_subscription_revenue + annual_subscription_revenue.

Notes

Two revenue sources: net_sales_revenue comes from Shopify (hardware sales); monthly_subscription_revenue and annual_subscription_revenue come from Stripe invoices. total_revenue combines all three. Stripe revenue is recorded on the invoice payment date, so annual subscribers contribute a full year's payment as a lump sum on their renewal date — periods with a high concentration of annual renewals will show elevated subscription revenue compared to other periods.
Snapshot vs. summable metrics: Subscription event counts (new subs, cancellations, conversions) are summed across the period. End-of-day subscription counts are snapshots of the last day in the period and use the daily delta method for forecasting.
Default chart windows by grain: Day → trailing 180 days; Week → trailing 21 weeks; Month → trailing 12 months; Quarter → trailing 8 quarters.
days_remaining_in_period at Day grain is always 0, so forecasting has no effect when time_grain = 'Day'.

All Data Join

Query

Overview

Parameters

Output Columns

CTEs

base_all_completed_days

base

ranked

forecast_window / forecast_window_single / forecast_constants

forecast_run_rate_summed

snapshot_points / forecast_run_rate_snapshots

period_rollup

final

chart_window

final_filtered