Easy experiments support for Chargebee or Stripe data warehouse tables

[danielbachhuber]

As a follow-up to #26332, it would be nice to be able to connect your Chargebee or Stripe data warehouse tables to an experiment. At the moment, it's not easily possible — collating all of your data into one table is a requirement.

Might be blocked by #26598, or maybe there's a way around that.

Done is:

• There's some straightforward UX and backend logic to configure the tables for an experiment.
• Docs are updated.

[samunderwood]

Following up on our email conversation, it would be beneficial if I could execute some HogQL queries on the Stripe data warehouse and subsequently utilize the resulting metrics to create a goal or secondary metric for the experiment.

For instance, trial conversion rate holds significant importance for us. The only way we can calculate trial CVR is through the Stripe data warehouse. Currently, we employ the following HogQL query to determine trial CVR:

WITH 
trials AS (
    SELECT
        toStartOfWeek(trial_start) AS week_start,
        count(*) AS trials_started
    FROM stripe_subscription
    WHERE trial_start IS NOT NULL
    GROUP BY week_start
),
conversions AS (
    SELECT
        toStartOfWeek(trial_start) AS week_start,
        count(*) AS trials_converted
    FROM stripe_subscription
    WHERE trial_start IS NOT NULL
      AND trial_end IS NOT NULL
      AND (
           current_period_start >= trial_end
           OR (canceled_at IS NOT NULL AND canceled_at > trial_end)
      )
    GROUP BY week_start
),
cvrs AS (
    SELECT
        t.week_start,
        t.trials_started,
        COALESCE(c.trials_converted, 0) AS trials_converted,
        CASE WHEN t.trials_started = 0 THEN 0 
             ELSE (100.0 * COALESCE(c.trials_converted, 0) / t.trials_started) 
        END AS trial_conversion_rate
    FROM trials t
    LEFT JOIN conversions c ON t.week_start = c.week_start
)

SELECT
    week_start,
    trials_started,
    trials_converted,
    trial_conversion_rate,
    AVG(trial_conversion_rate) OVER (ORDER BY week_start ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_cvr_2w,
    AVG(trial_conversion_rate) OVER (ORDER BY week_start ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS avg_cvr_4w,
    AVG(trial_conversion_rate) OVER (ORDER BY week_start ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS avg_cvr_8w
FROM cvrs
ORDER BY week_start

If we could run HogQL to do calculations on warehouse tables for experiment goals/secondary metrics, it'd be super handy!