# How we use PostHog's built-in data warehouse

-   [

    ![](https://res.cloudinary.com/dmukukwp6/image/upload/c_scale,w_50/ian_31bf16ef7d_c3339bc255)

    Ian Vanagas](/community/profiles/29296.md)

Jul 30, 2025

-   [data warehouse](/blog/data-warehouse.md),
-   [Product engineers](/blog/product-engineers.md)

PostHog's [data warehouse](/data-warehouse.md) is our most powerful feature. It lets you sync data from the tools you already use like [Stripe](/docs/cdp/sources/stripe.md), [Salesforce](/docs/cdp/sources/salesforce.md), and [Hubspot](/docs/cdp/sources/hubspot.md), query it alongside your existing product data using [SQL](/docs/data-warehouse/query.md), and visualize it natively.

We built it because the [modern data stack sucks](/blog/modern-data-stack-sucks.md). What starts as a handful of business critical tools devolves into dozens of tools, many specifically built to capture, clean, format, load, query, and visualize data.

We knew it didn't have to be this way, so we built the data warehouse to get rid of all this complexity and give you a single source of truth for all your business data.

![PostHog data warehouse rows synced](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_16_at_11_04_28_2x_905d242fe0.png)![PostHog data warehouse rows synced](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_16_at_11_04_02_2x_690da5bd8b.png)

We sync hundreds of millions of rows from Postgres, Stripe, Temporal, and more on top of the tens of millions of events we capture each day.

We've created **over 1,600** SQL insights and visualizations using our data warehouse so far. It's our second most-used insight type behind [trends](/docs/product-analytics/trends/overview.md), which was around long before we had the data warehouse.

![PostHog SQL insights](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_10_at_11_47_15_2x_9b179c988a.png)![PostHog SQL insights](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_10_at_11_47_58_2x_19e2267c12.png)

To help you get started, we're sharing how teams at PostHog use our built-in data warehouse and custom SQL insights to answer critical business questions, like:

-   Which customers churned and how did it impact revenue?
-   Which customers are increasing their spend?
-   Who are our biggest customers and what products are they using?
-   Are we achieving our customer support goals?
-   What are the biggest sales opportunities in our pipeline?

We're also sharing the actual SQL queries we use to answer these questions as well as the insights and dashboards we use to visualize the data (we've faked the data for the screenshots though).

**Modifying queries to your data**

Because our data structure is unique, the SQL queries included here likely won't work out-of-the-box for you. Luckily, [PostHog AI](/ai.md) makes modifying SQL easy. Just paste these queries in as context and ask it to use your data instead.

## Problem #1: Understanding growth and churn

-   Sources:
    -   [Postgres](/docs/cdp/sources/postgres.md) for billing data
    -   [PostHog](/docs/new-to-posthog/understand-posthog.md) for usage and activation data
    -   [Salesforce](/docs/cdp/sources/salesforce.md) for sales context (e.g. ICP scores)
-   Tables: `postgres_invoice_with_annual_view`, `postgres_billing_customer`, `events`, `salesforce.contact`, `postgres.posthog_team`, `postgres.posthog_organization`, `postgres_billing_usagereport`

[Engineers make the product decisions](/newsletter/product-management-is-broken.md) at PostHog, but they'd be lost without the context product managers provide.

One of the ways PMs give them this context is through monthly growth reviews, where they explore:

-   Who churned, how much was their churn, and why (so we can prevent future churn)
-   Product-specific activation and retention rates, using [our custom definitions](/product-engineers/activation-metrics.md).
-   Metrics for their growth reviews, like revenue expansion and contraction.

This analysis is only possible when you combine product and revenue data, so the data warehouse and SQL insights are their weapon of choice.

Most companies would use our [Stripe source](/docs/cdp/sources/stripe.md) to import revenue data, but we don't. Since we have multiple different products with different usage-based pricing, we need a custom billing service to handle everything. The data from this service goes into Postgres, which then gets synced and used in PostHog.

![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_01_at_13_39_25_2x_9cd97c333c.png)![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_01_at_13_40_29_2x_88ae2be58f.png)

We import millions of rows from our billing tables in our Postgres database into PostHog.

Product managers combine this billing data from Postgres with product analytics event and property data collected by PostHog, and some additional supplementary customer info imported from Salesforce.

From here they create specific SQL insights and combine them into a single dashboard like this one for error tracking:

![PostHog error tracking dashboard](https://res.cloudinary.com/dmukukwp6/image/upload/error_tracking_light_fbad414c9c.png)![PostHog error tracking dashboard](https://res.cloudinary.com/dmukukwp6/image/upload/error_tracking_dark_0e4274f50e.png)

It includes insights like:

### Top 50 error tracking customers by volume

![Top 50 error tracking customers by volume](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_23_at_08_22_45_2x_a12460726c.png)![Top 50 error tracking customers by volume](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_23_at_08_23_06_2x_246a482e8d.png)

SQL query for top 50 error tracking customers by volume

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT%0A++++JSONExtractString%28properties%2C+'organization_name'%29+AS+organization_name%2C%0A++++sum%28JSONExtractInt%28properties%2C+'exceptions_captured_in_period'%29%29+AS+exception_events%0AFROM+events%0AWHERE+event+%3D+'organization+usage+report'+AND+%7Bfilters%7D+AND+JSONExtractInt%28properties%2C+'exceptions_captured_in_period'%29+%3E+0%0Agroup+by+organization_name%0Aorder+by+exception_events+desc%0Alimit+50)

PostHog AI

```sql
SELECT
    JSONExtractString(properties, 'organization_name') AS organization_name,
    sum(JSONExtractInt(properties, 'exceptions_captured_in_period')) AS exception_events
FROM events
WHERE event = 'organization usage report' AND {filters} AND JSONExtractInt(properties, 'exceptions_captured_in_period') > 0
group by organization_name
order by exception_events desc
limit 50
```

### Organizations regularly capturing exceptions (monthly)

![Organizations regularly capturing exceptions (monthly)](https://res.cloudinary.com/dmukukwp6/image/upload/regular_d6527dba5b.png)![Organizations regularly capturing exceptions (monthly)](https://res.cloudinary.com/dmukukwp6/image/upload/regular_dark_9def980458.png)

SQL query for organizations regularly capturing exceptions (monthly)

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH+daily_exceptions+AS+%28%0A++++SELECT+%0A++++++++JSONExtractString%28properties%2C+'organization_id'%29+as+org_id%2C%0A++++++++toStartOfMonth%28timestamp%29+as+month%2C%0A++++++++toDate%28timestamp%29+as+report_date%2C%0A++++++++JSONExtractInt%28properties%2C+'exceptions_captured_in_period'%29+as+exceptions%0A++++FROM+events+%0A++++WHERE+event+%3D+'organization+usage+report'%0A++++++++AND+JSONExtractInt%28properties%2C+'exceptions_captured_in_period'%29+%3E+0%0A++++++++AND+%7Bfilters%7D++--+Same+filters+as+daily+customers+query%0A++++++++AND+timestamp+%3E%3D+toDateTime%28'2025-03-01'%29%0A%29%2C%0A%0Amonthly_activity+AS+%28%0A++++SELECT+%0A++++++++org_id%2C%0A++++++++month%2C%0A++++++++count%28DISTINCT+report_date%29+as+days_with_exceptions%2C%0A++++++++--+Calculate+weeks+in+the+month+to+get+proper+average%0A++++++++dateDiff%28'day'%2C+min%28report_date%29%2C+max%28report_date%29%29+%2F+7.0+as+weeks_active%2C%0A++++++++count%28DISTINCT+report_date%29+%2F+GREATEST%28dateDiff%28'day'%2C+min%28report_date%29%2C+max%28report_date%29%29+%2F+7.0%2C+1%29+as+avg_days_per_week%0A++++FROM+daily_exceptions%0A++++GROUP+BY+org_id%2C+month%0A%29%2C%0A%0Aregular_users+AS+%28%0A++++SELECT+%0A++++++++org_id%2C%0A++++++++month%0A++++FROM+monthly_activity%0A++++WHERE+avg_days_per_week+%3E+4%0A%29%0A%0ASELECT+%0A++++month%2C%0A++++count%28DISTINCT+org_id%29+as+orgs_regularly_capturing_exceptions%0AFROM+regular_users%0AGROUP+BY+month%0AORDER+BY+month)

PostHog AI

```sql
WITH daily_exceptions AS (
    SELECT
        JSONExtractString(properties, 'organization_id') as org_id,
        toStartOfMonth(timestamp) as month,
        toDate(timestamp) as report_date,
        JSONExtractInt(properties, 'exceptions_captured_in_period') as exceptions
    FROM events
    WHERE event = 'organization usage report'
        AND JSONExtractInt(properties, 'exceptions_captured_in_period') > 0
        AND {filters}  -- Same filters as daily customers query
        AND timestamp >= toDateTime('2025-03-01')
),
monthly_activity AS (
    SELECT
        org_id,
        month,
        count(DISTINCT report_date) as days_with_exceptions,
        -- Calculate weeks in the month to get proper average
        dateDiff('day', min(report_date), max(report_date)) / 7.0 as weeks_active,
        count(DISTINCT report_date) / GREATEST(dateDiff('day', min(report_date), max(report_date)) / 7.0, 1) as avg_days_per_week
    FROM daily_exceptions
    GROUP BY org_id, month
),
regular_users AS (
    SELECT
        org_id,
        month
    FROM monthly_activity
    WHERE avg_days_per_week > 4
)
SELECT
    month,
    count(DISTINCT org_id) as orgs_regularly_capturing_exceptions
FROM regular_users
GROUP BY month
ORDER BY month
```

### Error tracking churn for June

![Error tracking churn for June](https://res.cloudinary.com/dmukukwp6/image/upload/churn_e335f7cafa.png)![Error tracking churn for June](https://res.cloudinary.com/dmukukwp6/image/upload/churn_dark_e064750849.png)

SQL query for error tracking churn for June

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH+customers_per_month+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C%0A++++++++dateTrunc%28'month'%2C+toDateTime%28period_end%2C+'UTC'%29%29+as+month%2C%0A++++++++JSONExtractString%28data%2C+'customer_email'%29+AS+customer%2C%0A++++++++sum%28JSONExtractFloat%28mrr_per_product%2C+%7Bvariables.product%7D%29%29+AS+mrr%0A++++FROM+postgres_invoice_with_annual_view%0A++++WHERE+dateTrunc%28'month'%2C+toDateTime%28period_end%2C+'UTC'%29%29+IN+%28'2025-04-01'%2C+'2025-05-01'%2C+'2025-06-01'%29%0A++++++++AND+JSONExtractFloat%28mrr_per_product%2C+%7Bvariables.product%7D%29+%3E+0%0A++++GROUP+BY+customer_id%2C+customer%2C+month%0A%29%2C%0A%0Aapril_customers+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C%0A++++++++customer%2C%0A++++++++mrr+as+april_mrr%0A++++FROM+customers_per_month%0A++++WHERE+month+%3D+'2025-04-01'%0A%29%2C%0A%0Amay_customers+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C%0A++++++++customer%2C%0A++++++++mrr+as+may_mrr%0A++++FROM+customers_per_month%0A++++WHERE+month+%3D+'2025-05-01'%0A%29%2C%0A%0Ajune_customers+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C%0A++++++++customer%2C%0A++++++++mrr+as+june_mrr%0A++++FROM+customers_per_month%0A++++WHERE+month+%3D+'2025-06-01'%0A%29%2C%0A%0Aexception_events+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C++--+Assuming+this+field+exists+to+join+with+invoice+data%0A++++++++sum%28toInt%28org_usage_summary.exceptions%29%29+as+exception_count%2C%0A++++++++count%28DISTINCT+date%29+as+days_with_exceptions_june%0A++++FROM+postgres.prod.billing_usagereport%0A++++WHERE+org_usage_summary.exceptions+IS+NOT+NULL%0A++++++++AND+toInt%28org_usage_summary.exceptions%29+%3E+0++--+Only+count+days+with+actual+exceptions%0A++++++++AND+date+%3E%3D+'2025-06-01'%0A++++++++AND+date+%3C+'2025-07-01'++--+June+only%0A++++GROUP+BY+customer_id%0A%29%0A%0ASELECT+%0A++++m.customer+as+_customer%2C%0A++++m.customer_id%2C%0A++++round%28COALESCE%28a.april_mrr%2C+0%29%2C+0%29+as+april_mrr%2C%0A++++round%28m.may_mrr%2C+0%29+as+may_mrr%2C%0A++++round%28COALESCE%28j.june_mrr%2C+0%29%2C+0%29+as+june_mrr%2C%0A++++round%28m.may_mrr+-+COALESCE%28j.june_mrr%2C+0%29%2C+0%29+as+mrr_change%2C%0A++++COALESCE%28e.exception_count%2C+0%29+as+exception_events%2C%0A++++COALESCE%28e.days_with_exceptions_june%2C+0%29+as+days_with_exceptions_june%2C%0A++++'CHURNED+IN+JUNE'+as+churn_status%0AFROM+may_customers+m%0ALEFT+JOIN+april_customers+a+ON+m.customer_id+%3D+a.customer_id%0ALEFT+JOIN+june_customers+j+ON+m.customer_id+%3D+j.customer_id%0ALEFT+JOIN+exception_events+e+ON+m.customer_id+%3D+e.customer_id%0AWHERE+j.customer_id+IS+NULL++--+Had+May+MRR+but+no+June+MRR+%3D+June+churn%0A++++AND+m.customer_id+IS+NOT+NULL%0AORDER+BY+m.may_mrr+DESC%2C+days_with_exceptions_june+DESC%2C+exception_events+DESC)

PostHog AI

```sql
WITH customers_per_month AS (
    SELECT
        customer_id,
        dateTrunc('month', toDateTime(period_end, 'UTC')) as month,
        JSONExtractString(data, 'customer_email') AS customer,
        sum(JSONExtractFloat(mrr_per_product, {variables.product})) AS mrr
    FROM postgres_invoice_with_annual_view
    WHERE dateTrunc('month', toDateTime(period_end, 'UTC')) IN ('2025-04-01', '2025-05-01', '2025-06-01')
        AND JSONExtractFloat(mrr_per_product, {variables.product}) > 0
    GROUP BY customer_id, customer, month
),
april_customers AS (
    SELECT
        customer_id,
        customer,
        mrr as april_mrr
    FROM customers_per_month
    WHERE month = '2025-04-01'
),
may_customers AS (
    SELECT
        customer_id,
        customer,
        mrr as may_mrr
    FROM customers_per_month
    WHERE month = '2025-05-01'
),
june_customers AS (
    SELECT
        customer_id,
        customer,
        mrr as june_mrr
    FROM customers_per_month
    WHERE month = '2025-06-01'
),
exception_events AS (
    SELECT
        customer_id,  -- Assuming this field exists to join with invoice data
        sum(toInt(org_usage_summary.exceptions)) as exception_count,
        count(DISTINCT date) as days_with_exceptions_june
    FROM postgres.prod.billing_usagereport
    WHERE org_usage_summary.exceptions IS NOT NULL
        AND toInt(org_usage_summary.exceptions) > 0  -- Only count days with actual exceptions
        AND date >= '2025-06-01'
        AND date < '2025-07-01'  -- June only
    GROUP BY customer_id
)
SELECT
    m.customer as _customer,
    m.customer_id,
    round(COALESCE(a.april_mrr, 0), 0) as april_mrr,
    round(m.may_mrr, 0) as may_mrr,
    round(COALESCE(j.june_mrr, 0), 0) as june_mrr,
    round(m.may_mrr - COALESCE(j.june_mrr, 0), 0) as mrr_change,
    COALESCE(e.exception_count, 0) as exception_events,
    COALESCE(e.days_with_exceptions_june, 0) as days_with_exceptions_june,
    'CHURNED IN JUNE' as churn_status
FROM may_customers m
LEFT JOIN april_customers a ON m.customer_id = a.customer_id
LEFT JOIN june_customers j ON m.customer_id = j.customer_id
LEFT JOIN exception_events e ON m.customer_id = e.customer_id
WHERE j.customer_id IS NULL  -- Had May MRR but no June MRR = June churn
    AND m.customer_id IS NOT NULL
ORDER BY m.may_mrr DESC, days_with_exceptions_june DESC, exception_events DESC
```

### Using views to create reusable queries

Because product managers are slicing and dicing the data in multiple similar ways, they end up using a lot of [views](/docs/data-warehouse/views.md). These are saved queries that can be reused easily and can also be [materialized](/docs/data-warehouse/views/materialize.md) for a speed up.

Views enable them to build a query to get the data they want in the format they want then use it to build multiple other queries. Some examples include:

-   `org_icp_scores`: Gets ICP scores for customers from Salesforce where they are calculated.
-   `feature_flags_activation_base`: Builds a funnel of product intent → activation → retention, all centered on [feature flags](/feature-flags.md) and enriched with an ICP score.
-   `source_of_truth_for_dp_pricing_model`: Data on which customers are using [pipelines](/cdp.md), what volume they are doing, and more to help improve our data pipeline pricing.

## Problem #2: Tracking revenue

-   Sources:
    -   [Postgres](/docs/cdp/sources/postgres.md) for billing data
    -   [Salesforce](/docs/cdp/sources/salesforce.md) for sales context (e.g. ICP scores)
-   Tables: `postgres_invoice_with_annual_view`, `postgres_billing_customer`, `postgres_billing_usagereport`, `salesforce_opportunity`

As I hinted at above, tracking revenue at PostHog is complicated. We have multiple products with usage-based and tiered pricing with discounts and add-ons, so we can't just query Stripe and get our revenue data. We have a specific billing service that generates invoices and reports, and adds them to Postgres.

For multiple years, we used Metabase to query and visualize all this revenue data from Postgres. All of the cleanup and reformatting needed to make it usable was in there too. Moving all this to PostHog was a lot of work (it had its own project Slack channel), but we've done it now and it looks like this:

![Revenue dashboard](https://res.cloudinary.com/dmukukwp6/image/upload/revdashlight_8069258cd9.png)![Revenue dashboard](https://res.cloudinary.com/dmukukwp6/image/upload/revdashdark_16d7a23dab.png)

Rebuilding (and improving) these insights required us to build views that standardize the data, connect Salesforce to better project revenue, create SQL visualizations we were missing, and more. Overall, the project was a success both in terms of making our revenue data more accessible and improving our data warehouse as a product.

Our PostHog revenue dashboard now includes insights like the following (powered by the data warehouse):

### US/EU revenue split

![US/EU revenue split](https://res.cloudinary.com/dmukukwp6/image/upload/revsplitlight_12dda53107.png)![US/EU revenue split](https://res.cloudinary.com/dmukukwp6/image/upload/revdashdark_16d7a23dab.png)

SQL query for US/EU revenue split

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH%0A++++eu_customers+as+%28select+id+from+postgres_billing_customer+where+license_id+%3D+1%29%2C%0A++++us_customers+as+%28select+id+from+postgres_billing_customer+where+license_id+%3D+2%29%0A%0Aselect%0A++++round%28sumIf%28mrr%2C+customer_id+in+%28select+id+from+us_customers%29%29%29+*+12+as+us_ARR%2C%0A++++round%28sumIf%28mrr%2C+customer_id+in+%28select+id+from+eu_customers%29%29%29+*+12+as+eu_ARR%2C%0A++++coalesce%28round%28sumIf%28mrr%2C+customer_id+is+null+OR+%28customer_id+not+in+%28select+id+from+eu_customers%29+and+customer_id+not+in+%28select+id+from+us_customers%29%29%29%29+*+12%2C+0%29+as+other_ARR%2C%0A++++dateTrunc%28'month'%2C+toDateTime%28period_end%2C+'UTC'%29%29+as+period%0Afrom+postgres_invoice_with_annual_view%0Awhere+toDateTime%28period_end%2C+'UTC'%29+%3C+dateTrunc%28'month'%2C+now%28%29%29+%2B+interval+2+month%0Aand+toDateTime%28period_end%2C+'UTC'%29+%3E+dateTrunc%28'month'%2C+now%28%29+-+interval+12+month%29%0A%0Agroup+by+period+order+by+period+asc)

PostHog AI

```sql
WITH
    eu_customers as (select id from postgres_billing_customer where license_id = 1),
    us_customers as (select id from postgres_billing_customer where license_id = 2)
select
    round(sumIf(mrr, customer_id in (select id from us_customers))) * 12 as us_ARR,
    round(sumIf(mrr, customer_id in (select id from eu_customers))) * 12 as eu_ARR,
    coalesce(round(sumIf(mrr, customer_id is null OR (customer_id not in (select id from eu_customers) and customer_id not in (select id from us_customers)))) * 12, 0) as other_ARR,
    dateTrunc('month', toDateTime(period_end, 'UTC')) as period
from postgres_invoice_with_annual_view
where toDateTime(period_end, 'UTC') < dateTrunc('month', now()) + interval 2 month
and toDateTime(period_end, 'UTC') > dateTrunc('month', now() - interval 12 month)
group by period order by period asc
```

### Revenue lifecycle

![Revenue lifecycle](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/revlifelight_8a4e73b8b3.png)![Revenue lifecycle](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/revlifedark_b88f1e5eb7.png)

SQL query for revenue lifecycle

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH+date_range+AS+%28%0A++++SELECT+%0A++++++++toStartOfMonth%28now%28%29+-+INTERVAL+number+MONTH%29+as+month%0A++++FROM+numbers%2835%29++--+33+months+back+%2B+2+forward%0A++++WHERE+month+%3E%3D+toStartOfMonth%28now%28%29+-+INTERVAL+33+MONTH%29%0A++++++++AND+month+%3C%3D+toStartOfMonth%28now%28%29+%2B+INTERVAL+2+MONTH%29%0A%29%2C%0Aall_customers+AS+%28%0A++++SELECT+DISTINCT+id+as+customer_id%0A++++FROM+iwa_summary_customer_month%0A++++WHERE+month+%3C+toStartOfMonth%28now%28%29+%2B+INTERVAL+2+MONTH%29+%0A++++AND+month+%3E+toStartOfMonth%28now%28%29+-+INTERVAL+33+MONTH%29%0A%29%2C%0Acustomer_month_grid+AS+%28%0A++++SELECT+%0A++++++++ac.customer_id%2C%0A++++++++dr.month%0A++++FROM+all_customers+ac%0A++++CROSS+JOIN+date_range+dr%0A%29%2C%0Acustomers_per_month+AS+%28%0A++++SELECT+%0A++++++++cmg.customer_id%2C%0A++++++++cmg.month%2C%0A++++++++COALESCE%28sum%28iscm.total_mrr%29%2C+0%29+as+mrr%0A++++FROM+customer_month_grid+cmg%0A++++LEFT+JOIN+iwa_summary_customer_month+iscm+%0A++++++++ON+cmg.customer_id+%3D+iscm.id+%0A++++++++AND+cmg.month+%3D+iscm.month%0A++++WHERE+cmg.month+%3C+toStartOfMonth%28now%28%29+%2B+INTERVAL+2+MONTH%29+%0A++++AND+cmg.month+%3E+toStartOfMonth%28now%28%29+-+INTERVAL+33+MONTH%29%0A++++GROUP+BY+cmg.customer_id%2C+cmg.month%0A%29%2C+%0Achurn_calc+AS+%28%0A++++SELECT%0A++++++++customer_id%2C%0A++++++++month+AS+churn_accounting_month%2C%0A++++++++%0A++++++++mrr+AS+curr_mrr%2C%0A++++++++%0A++++++++lagInFrame%28mrr%2C+1%2C+0%29+OVER+%28%0A++++++++PARTITION+BY+customer_id%0A++++++++ORDER+BY+month%0A++++++++%29+AS+prev_mrr%0A++++FROM+customers_per_month%0A%29%2C%0Achurned+AS+%28%0A++++SELECT%0A++++++++churn_accounting_month%2C%0A++++++++sum%28prev_mrr+-+curr_mrr%29+AS+churned_mrr%2C%0A++++++++count%28*%29+as+churned_customers%0A++++FROM+churn_calc%0A++++WHERE+prev_mrr+%3E+0%0A++++++++AND+curr_mrr+%3C%3D+0%0A++++GROUP+BY+1%0A%29%2C%0Acustomer_status+AS+%28%0A++++SELECT+%0A++++++++customer_id%2C%0A++++++++month%2C%0A++++++++mrr%2C%0A++++++++CASE+WHEN+MIN%28month%29+OVER+%28PARTITION+BY+customer_id%29+%3D+month+THEN+TRUE+ELSE+FALSE+END+as+first_month_active%2C%0A++++++++CASE+WHEN+lagInFrame%28mrr%2C+1%2C+0%29+OVER+%28PARTITION+BY+customer_id+ORDER+BY+month%29+%3E+0+THEN+TRUE+ELSE+FALSE+END+as+active_in_previous_month%2C%0A++++++++lagInFrame%28mrr%2C+1%2C+0%29+OVER+%28PARTITION+BY+customer_id+ORDER+BY+month%29+as+prev_month_mrr%0A++++FROM+customers_per_month%0A%29%2C%0Alifecycle+AS+%28%0A++++SELECT%0A++++++++month%2C%0A++++++++CASE+%0A++++++++++++WHEN+NOT+active_in_previous_month+THEN+'NEW'%0A++++++++++++WHEN+active_in_previous_month+AND+prev_month_mrr+%3D+mrr+THEN+'FLAT'%0A++++++++++++WHEN+active_in_previous_month+AND+prev_month_mrr+%3E+mrr+THEN+'SHRINKING'%0A++++++++++++WHEN+active_in_previous_month+AND+prev_month_mrr+%3C+mrr+THEN+'GROWING'%0A++++++++++++ELSE+'UNCATEGORIZED'%0A++++++++END+AS+growth_lifecycle_stage%2C+%0A++++++++mrr%2C+%0A++++++++prev_month_mrr%0A++++FROM+customer_status%0A++++WHERE+mrr+%3E+0%0A%29%2C+%0Agrowth_base+AS+%28%0A++++SELECT+%0A++++++++month%2C+%0A++++++++SUM%28CASE+WHEN+growth_lifecycle_stage+%3D+'NEW'+THEN+mrr+ELSE+0+END%29+as+new%2C+%0A++++++++SUM%28CASE+%0A++++++++++++WHEN+growth_lifecycle_stage+%3D+'FLAT'+THEN+mrr%0A++++++++++++WHEN+growth_lifecycle_stage+IN+%28'GROWING'%2C+'SHRINKING'%29+THEN+prev_month_mrr+%0A++++++++++++ELSE+0+END%29+as+retained%2C%0A++++++++SUM%28CASE+WHEN+growth_lifecycle_stage+%3D+'GROWING'+THEN+mrr+-+prev_month_mrr+ELSE+0+END%29+as+grown_from_existing%2C%0A++++++++SUM%28CASE+WHEN+growth_lifecycle_stage+%3D+'SHRINKING'+THEN+mrr+-+prev_month_mrr+ELSE+0+END%29+as+shrunk_from_existing%2C%0A++++++++SUM%28CASE+WHEN+growth_lifecycle_stage+%3D+'UNCATEGORIZED'+THEN+mrr+ELSE+0+END%29+as+uncategorized%2C%0A++++++++--+Calculate+the+base+MRR+that+was+%22at+risk%22+%28from+continuing+customers%29%0A++++++++SUM%28prev_month_mrr%29+as+at_risk_mrr%0A++++FROM+lifecycle++++%0A++++GROUP+BY+month%0A%29%0ASELECT+%0A++++growth_base.month+as+month%2C+%0A++++growth_base.new%2C%0A++++growth_base.retained%2C%0A++++growth_base.grown_from_existing%2C%0A++++growth_base.shrunk_from_existing%2C+%0A++++growth_base.uncategorized%2C+%0A++++-churned.churned_mrr+as+churned%2C%0A++++churned.churned_customers%2C%0A++++++++--+Add+period+start+and+end+MRR%0A++++lagInFrame%28%0A++++++++growth_base.new+%2B%0A++++++++growth_base.retained+%2B+%0A++++++++growth_base.grown_from_existing+%2B+%0A++++++++growth_base.shrunk_from_existing+%2B%0A++++++++growth_base.uncategorized%0A++++%29+OVER+%28ORDER+BY+growth_base.month%29+as+period_start_mrr%2C%0A++++%0A++++--+Period+end+MRR+%3D+current+month's+total%0A++++growth_base.new+%2B%0A++++growth_base.retained+%2B+%0A++++growth_base.grown_from_existing+%2B+%0A++++growth_base.shrunk_from_existing+%2B%0A++++growth_base.uncategorized+as+period_end_mrr%2C%0AFROM+growth_base%0ALEFT+JOIN+churned%0AON+growth_base.month+%3D+churned.churn_accounting_month%0AWHERE+growth_base.month+%3C+today%28%29+%2B+interval+1+month%0AORDER+BY+month+ASC)

PostHog AI

```sql
WITH date_range AS (
    SELECT
        toStartOfMonth(now() - INTERVAL number MONTH) as month
    FROM numbers(35)  -- 33 months back + 2 forward
    WHERE month >= toStartOfMonth(now() - INTERVAL 33 MONTH)
        AND month <= toStartOfMonth(now() + INTERVAL 2 MONTH)
),
all_customers AS (
    SELECT DISTINCT id as customer_id
    FROM iwa_summary_customer_month
    WHERE month < toStartOfMonth(now() + INTERVAL 2 MONTH)
    AND month > toStartOfMonth(now() - INTERVAL 33 MONTH)
),
customer_month_grid AS (
    SELECT
        ac.customer_id,
        dr.month
    FROM all_customers ac
    CROSS JOIN date_range dr
),
customers_per_month AS (
    SELECT
        cmg.customer_id,
        cmg.month,
        COALESCE(sum(iscm.total_mrr), 0) as mrr
    FROM customer_month_grid cmg
    LEFT JOIN iwa_summary_customer_month iscm
        ON cmg.customer_id = iscm.id
        AND cmg.month = iscm.month
    WHERE cmg.month < toStartOfMonth(now() + INTERVAL 2 MONTH)
    AND cmg.month > toStartOfMonth(now() - INTERVAL 33 MONTH)
    GROUP BY cmg.customer_id, cmg.month
),
churn_calc AS (
    SELECT
        customer_id,
        month AS churn_accounting_month,
        mrr AS curr_mrr,
        lagInFrame(mrr, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY month
        ) AS prev_mrr
    FROM customers_per_month
),
churned AS (
    SELECT
        churn_accounting_month,
        sum(prev_mrr - curr_mrr) AS churned_mrr,
        count(*) as churned_customers
    FROM churn_calc
    WHERE prev_mrr > 0
        AND curr_mrr <= 0
    GROUP BY 1
),
customer_status AS (
    SELECT
        customer_id,
        month,
        mrr,
        CASE WHEN MIN(month) OVER (PARTITION BY customer_id) = month THEN TRUE ELSE FALSE END as first_month_active,
        CASE WHEN lagInFrame(mrr, 1, 0) OVER (PARTITION BY customer_id ORDER BY month) > 0 THEN TRUE ELSE FALSE END as active_in_previous_month,
        lagInFrame(mrr, 1, 0) OVER (PARTITION BY customer_id ORDER BY month) as prev_month_mrr
    FROM customers_per_month
),
lifecycle AS (
    SELECT
        month,
        CASE
            WHEN NOT active_in_previous_month THEN 'NEW'
            WHEN active_in_previous_month AND prev_month_mrr = mrr THEN 'FLAT'
            WHEN active_in_previous_month AND prev_month_mrr > mrr THEN 'SHRINKING'
            WHEN active_in_previous_month AND prev_month_mrr < mrr THEN 'GROWING'
            ELSE 'UNCATEGORIZED'
        END AS growth_lifecycle_stage,
        mrr,
        prev_month_mrr
    FROM customer_status
    WHERE mrr > 0
),
growth_base AS (
    SELECT
        month,
        SUM(CASE WHEN growth_lifecycle_stage = 'NEW' THEN mrr ELSE 0 END) as new,
        SUM(CASE
            WHEN growth_lifecycle_stage = 'FLAT' THEN mrr
            WHEN growth_lifecycle_stage IN ('GROWING', 'SHRINKING') THEN prev_month_mrr
            ELSE 0 END) as retained,
        SUM(CASE WHEN growth_lifecycle_stage = 'GROWING' THEN mrr - prev_month_mrr ELSE 0 END) as grown_from_existing,
        SUM(CASE WHEN growth_lifecycle_stage = 'SHRINKING' THEN mrr - prev_month_mrr ELSE 0 END) as shrunk_from_existing,
        SUM(CASE WHEN growth_lifecycle_stage = 'UNCATEGORIZED' THEN mrr ELSE 0 END) as uncategorized,
        -- Calculate the base MRR that was "at risk" (from continuing customers)
        SUM(prev_month_mrr) as at_risk_mrr
    FROM lifecycle
    GROUP BY month
)
SELECT
    growth_base.month as month,
    growth_base.new,
    growth_base.retained,
    growth_base.grown_from_existing,
    growth_base.shrunk_from_existing,
    growth_base.uncategorized,
    -churned.churned_mrr as churned,
    churned.churned_customers,
        -- Add period start and end MRR
    lagInFrame(
        growth_base.new +
        growth_base.retained +
        growth_base.grown_from_existing +
        growth_base.shrunk_from_existing +
        growth_base.uncategorized
    ) OVER (ORDER BY growth_base.month) as period_start_mrr,
    -- Period end MRR = current month's total
    growth_base.new +
    growth_base.retained +
    growth_base.grown_from_existing +
    growth_base.shrunk_from_existing +
    growth_base.uncategorized as period_end_mrr,
FROM growth_base
LEFT JOIN churned
ON growth_base.month = churned.churn_accounting_month
WHERE growth_base.month < today() + interval 1 month
ORDER BY month ASC
```

### Revenue per product

![Revenue per product](https://res.cloudinary.com/dmukukwp6/image/upload/revperprolight_5ff8e7c9a4.png)![Revenue per product](https://res.cloudinary.com/dmukukwp6/image/upload/revperprodark_a97adcfe47.png)

SQL query for revenue per product

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT%0A++++dateTrunc%28'month'%2C+toDateTime%28period_end%2C+'UTC'%29%29+AS+period_month%2C%0A++++tupleElement%28kv%2C+1%29+AS+product%2C%0A++++SUM%28toFloat%28tupleElement%28kv%2C+2%29%29%29+AS+revenue%0AFROM+postgres_invoice_with_annual_view%0AARRAY+JOIN+JSONExtractKeysAndValuesRaw%28assumeNotNull%28mrr_per_product%29%29+AS+kv%0AWHERE+%0A++++toDateTime%28period_end%2C+'UTC'%29+%3C+dateTrunc%28'month'%2C+now%28%29+%2B+interval+1+month%29%0A++++AND+toDateTime%28period_end%2C+'UTC'%29+%3E%3D+dateTrunc%28'month'%2C+now%28%29+-+interval+12+month%29%0AGROUP+BY+period_month%2C+product%0AORDER+BY+period_month%2C+product%0A%0ALIMIT+500)

PostHog AI

```sql
SELECT
    dateTrunc('month', toDateTime(period_end, 'UTC')) AS period_month,
    tupleElement(kv, 1) AS product,
    SUM(toFloat(tupleElement(kv, 2))) AS revenue
FROM postgres_invoice_with_annual_view
ARRAY JOIN JSONExtractKeysAndValuesRaw(assumeNotNull(mrr_per_product)) AS kv
WHERE
    toDateTime(period_end, 'UTC') < dateTrunc('month', now() + interval 1 month)
    AND toDateTime(period_end, 'UTC') >= dateTrunc('month', now() - interval 12 month)
GROUP BY period_month, product
ORDER BY period_month, product
LIMIT 500
```

## Problem #3: Creating quarterly sales and customer success reports

-   Sources:
    -   [Salesforce](/docs/cdp/sources/salesforce.md) for sales context (e.g. ICP scores)
    -   [Postgres](/docs/cdp/sources/postgres.md) for billing data
    -   [Vitally](/docs/cdp/sources/vitally.md) for account ownership details
-   Tables: `invoice_with_annual_plans_shifted`, `vitally_all_managed_accounts`, `salesforce_opportunity`, `salesforce_user`

Our sales and [customer success](/blog/customer-success-at-posthog.md) teams do reporting through PostHog. They pull from Salesforce mostly, but also billing data in Postgres and account ownership details from Vitally. These are best combined in the **Sales and CS Quarter Tracker** dashboard which covers details like:

-   Revenue from customers managed by sales and customer success
-   Details on managed customers
-   Salesforce opportunity pipeline and how much they're worth
-   Won opportunities and how much they're worth

Some examples include:

### Salesforce opportunities by quarter

![Salesforce opportunities by quarter](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/openoppslight_b77896aaa0.png)![Salesforce opportunities by quarter](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/openoppsdark_a2a1ba29c9.png)

SQL query for Salesforce opportunities by quarter

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++u.email+AS+owner%2C%0A++++opp.name%2C%0A++++opp.type%2C%0A++++opp.close_date+AS+close_date%2C%0A++++opp.forecast_category_name%2C%0A++++opp.stage_name%2C%0A++++opp.amount%2C%0A++++opp.discount_rate_c+AS+discount%2C%0A++++opp.amount_discounted_c+AS+discounted_amount%0AFROM+%0A++++salesforce_opportunity+opp%0AJOIN+%0A++++salesforce_user+u+ON+opp.owner_id+%3D+u.id%0AWHERE+%0A++++toDateTime%28opp.close_date%29+%3E%3D+%7Bvariables.start_of_quarter%7D%0A++++AND+toDateTime%28opp.close_date%29+%3C+%7Bvariables.start_of_quarter%7D+%2B+INTERVAL+3+MONTH%0A++++AND+opp.is_closed+%3D+FALSE%0A++++AND+opp.self_serve_no_interaction_c+%3D+FALSE%0A++++AND+opp.self_serve_post_engagement_c+%3D+FALSE%0A++++AND+u.email+LIKE+CONCAT%28'%25'%2C+%7Bvariables.account_owner%7D%29%0AORDER+BY+%0A++++opp.close_date+ASC%3B)

PostHog AI

```sql
SELECT
    u.email AS owner,
    opp.name,
    opp.type,
    opp.close_date AS close_date,
    opp.forecast_category_name,
    opp.stage_name,
    opp.amount,
    opp.discount_rate_c AS discount,
    opp.amount_discounted_c AS discounted_amount
FROM
    salesforce_opportunity opp
JOIN
    salesforce_user u ON opp.owner_id = u.id
WHERE
    toDateTime(opp.close_date) >= {variables.start_of_quarter}
    AND toDateTime(opp.close_date) < {variables.start_of_quarter} + INTERVAL 3 MONTH
    AND opp.is_closed = FALSE
    AND opp.self_serve_no_interaction_c = FALSE
    AND opp.self_serve_post_engagement_c = FALSE
    AND u.email LIKE CONCAT('%', {variables.account_owner})
ORDER BY
    opp.close_date ASC;
```

### Salesforce open pipeline by quarter (annual only)

![Salesforce open pipeline by quarter (annual only)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/openpipelight_4839830a14.png)![Salesforce open pipeline by quarter (annual only)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/openpipedark_c08527c47d.png)

SQL query for Salesforce open pipeline by quarter (annual only)

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++SUM%28opp.amount_discounted_c%29%0AFROM+%0A++++salesforce_opportunity+opp%0AJOIN+%0A++++salesforce_user+u+ON+opp.owner_id+%3D+u.id%0AWHERE+%0A++++toDateTime%28opp.close_date%29+%3E%3D+%7Bvariables.start_of_quarter%7D%0A++++AND+toDateTime%28opp.close_date%29+%3C+%7Bvariables.start_of_quarter%7D+%2B+INTERVAL+3+MONTH%0A++++AND+opp.forecast_category_name+IN+%28'Commit'%2C+'Best+Case'%2C+'Pipeline'%29%0A++++AND+opp.self_serve_no_interaction_c+%3D+FALSE%0A++++AND+opp.self_serve_post_engagement_c+%3D+FALSE%0A++++AND+opp.type+%3D+'Annual+Contract'%0A++++AND+u.email+LIKE+CONCAT%28'%25'%2C+%7Bvariables.account_owner%7D%29%3B)

PostHog AI

```sql
SELECT
    SUM(opp.amount_discounted_c)
FROM
    salesforce_opportunity opp
JOIN
    salesforce_user u ON opp.owner_id = u.id
WHERE
    toDateTime(opp.close_date) >= {variables.start_of_quarter}
    AND toDateTime(opp.close_date) < {variables.start_of_quarter} + INTERVAL 3 MONTH
    AND opp.forecast_category_name IN ('Commit', 'Best Case', 'Pipeline')
    AND opp.self_serve_no_interaction_c = FALSE
    AND opp.self_serve_post_engagement_c = FALSE
    AND opp.type = 'Annual Contract'
    AND u.email LIKE CONCAT('%', {variables.account_owner});
```

### Sales and CS managed accounts start of quarter ARR

![Sales and CS managed accounts start of quarter ARR](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/salesarrlight_8fe5d4b706.png)![Sales and CS managed accounts start of quarter ARR](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/salesarrdark_ad2b0cfc92.png)

SQL query for Sales and CS managed accounts start of quarter ARR

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH+start_of_quarter+AS+%28%0A++++SELECT+%0A++++++++inv.organization_id+AS+organization_id%2C%0A++++++++acc.organization_name%2C%0A++++++++acc.account_executive%2C%0A++++++++acc.customer_success_manager%2C%0A++++++++ROUND%28SUM%28inv.mrr+*+12%29%2C+2%29+AS+arr%2C%0A++++++++CASE+%0A++++++++++++WHEN+arrayExists%28x+-%3E+x+LIKE+'%25annual%25'%2C+groupArray%28type%29%29+%0A++++++++++++THEN+'annual'+%0A++++++++++++ELSE+'monthly'+%0A++++++++END+AS+type%0A++++FROM+%0A++++++++invoice_with_annual_plans_shifted+inv%0A++++JOIN+%0A++++++++vitally_all_managed_accounts+acc+%0A++++++++ON+inv.organization_id+%3D+acc.organization_id%0A++++WHERE+%0A++++++++period_end+%3C+toDateTime%28%7Bvariables.start_of_quarter%7D%29%0A++++++++AND+period_end+%3E%3D+toDateTime%28%7Bvariables.start_of_quarter%7D%29+-+INTERVAL+1+MONTH%0A++++GROUP+BY+%0A++++++++organization_id%2C+organization_name%2C+account_executive%2C+customer_success_manager%0A++++ORDER+BY+%0A++++++++arr+DESC%0A%29%0A%0ASELECT+%0A++++ROUND%28SUM%28arr%29%2C+0%29%0AFROM+%0A++++start_of_quarter%0AWHERE+%0A++++%28%0A++++++++account_executive+LIKE+CONCAT%28'%25'%2C+%7Bvariables.account_owner%7D%29+%0A++++++++OR+customer_success_manager+LIKE+CONCAT%28'%25'%2C+%7Bvariables.account_owner%7D%29%0A++++%29%0A++++AND+CASE+%0A++++++++WHEN+%7Bvariables.segment%7D+%3D+'All'+THEN+1%0A++++++++WHEN+%7Bvariables.segment%7D+%3D+'AE+Managed'+AND+account_executive+IS+NOT+NULL+THEN+1%0A++++++++WHEN+%7Bvariables.segment%7D+%3D+'CSM+Managed'+AND+customer_success_manager+IS+NOT+NULL+THEN+1%0A++++++++ELSE+0%0A++++END+%3D+1%3B)

PostHog AI

```sql
WITH start_of_quarter AS (
    SELECT
        inv.organization_id AS organization_id,
        acc.organization_name,
        acc.account_executive,
        acc.customer_success_manager,
        ROUND(SUM(inv.mrr * 12), 2) AS arr,
        CASE
            WHEN arrayExists(x -> x LIKE '%annual%', groupArray(type))
            THEN 'annual'
            ELSE 'monthly'
        END AS type
    FROM
        invoice_with_annual_plans_shifted inv
    JOIN
        vitally_all_managed_accounts acc
        ON inv.organization_id = acc.organization_id
    WHERE
        period_end < toDateTime({variables.start_of_quarter})
        AND period_end >= toDateTime({variables.start_of_quarter}) - INTERVAL 1 MONTH
    GROUP BY
        organization_id, organization_name, account_executive, customer_success_manager
    ORDER BY
        arr DESC
)
SELECT
    ROUND(SUM(arr), 0)
FROM
    start_of_quarter
WHERE
    (
        account_executive LIKE CONCAT('%', {variables.account_owner})
        OR customer_success_manager LIKE CONCAT('%', {variables.account_owner})
    )
    AND CASE
        WHEN {variables.segment} = 'All' THEN 1
        WHEN {variables.segment} = 'AE Managed' AND account_executive IS NOT NULL THEN 1
        WHEN {variables.segment} = 'CSM Managed' AND customer_success_manager IS NOT NULL THEN 1
        ELSE 0
    END = 1;
```

### Using variables

The sales dashboard is the best example of the power of [variables](/docs/data-warehouse/sql/variables.md). Nearly every insight uses variables to set the account owner, quarter, and whether they are managed by sales or customer success.

This makes all of the insights much more reusable by being able to reuse them across quarters or for looking at individual performance.

![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_09_at_13_52_43_2x_d99d5c2158.png)![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_09_at_13_52_20_2x_a3d79b8b95.png)

We use variables for the account owner, quarter, and whether an account is managed by sales or customer success to make our sales and CS dashboard more reusable.

## Problem #4: Creating support reports (SLA, first response time, and more)

-   Sources:
    -   [Zendesk](/docs/cdp/sources/zendesk.md) for ticket data
-   Tables: `zendesk_ticket_events`, `zendesk_tickets`, `zendesk_ticket_metric_events`, `zendesk_groups`

We have high standards for our support experience. Where do those standards get judged? The data warehouse, of course.

Similar to the sales and customer success teams, support does reporting through PostHog. It used to all be done in Zendesk, but since we added the Zendesk source, doing it in PostHog has become easier and better.

Unlike other teams, support almost always uses exclusively Zendesk. This means less complicated queries and more opportunities to use trend insights on top of SQL insights.

For example, our **SLA Achievement Rate** insight looks at the `ticket_metrics_events` table from `zendesk` to find what percentage of tickets were replied to or updated within the [SLA goals we set out](/handbook/support/customer-support.md#response-targets-slas-and-csat-surveys) (no SQL required).

![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_09_at_10_31_29_2x_5928b4166b.png)![PostHog](https://res.cloudinary.com/dmukukwp6/image/upload/Clean_Shot_2025_07_09_at_10_31_48_2x_73f756a1b4.png)

The support dashboard uses the standard trend insight type and formula mode to calculate our SLA achievement rate.

Beyond this, they use both insight types to track:

-   Service-level agreement (SLA) and first response time goals.
-   Performance and breaches on escalated tickets.
-   Support load on our product teams.
-   Sources of support requests.

This data is combined with [CSAT surveys](/templates/csat-survey.md) (both scores and responses) to give a complete picture of the support we're providing at PostHog. [![](https://res.cloudinary.com/dmukukwp6/image/upload/v1724774726/thumbnail_Abigail_607ecbe510.png)Abigail Richardson![](https://res.cloudinary.com/dmukukwp6/image/upload/v1724774726/thumbnail_Abigail_607ecbe510.png)Abigail Richardson](/community/profiles/31138.md) writes up a summary based on this data and shares it with the exec team weekly.

Abigail's message to the exec team.

Some examples of insights on the support dashboard include:

### Breached non-escalated tickets in the last 7 days

![Breached non-escalated tickets in the last 7 days](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/breachedlight_23c730c12b.png)![Breached non-escalated tickets in the last 7 days](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/breacheddark_104fcc4665.png)

SQL query for Breached non-escalated tickets in the last 7 days

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=with+first_escalated+as%28%0A++++select+%0A++++++++ticket_id%2C%0A++++++++min%28created_at%29+as+timestamp%0A++++from+zendesk_ticket_events%0A++++where+child_events+like+'%25escalated%25'%0A++++group+by+ticket_id%0A%29%2C%0A%0Abefore_escalated_breaches_in_time_range+as%28%0A++++select%0A++++++++ztme.ticket_id+as+ticket_id%2C%0A++++from+zendesk_ticket_metric_events+ztme%0A++++join+first_escalated+fe+on+fe.ticket_id+%3D+ztme.ticket_id%0A++++where+%0A++++++++ztme.type+%3D+'breach'%0A++++++++and+ztme.metric+in+%28'reply_time'%2C+'pausable_update_time'%29%0A++++++++and+ztme.time+%3C%3D+fe.timestamp%0A++++++++and+ztme.time+%3E%3D+%7Bfilters.dateRange.from%7D%0A++++++++and+ztme.time+%3C%3D+%7Bfilters.dateRange.to%7D%0A++++group+by+ztme.ticket_id%0A%29%2C%0A%0Anever_escalated+as%28%0A++++select+%0A++++++++id+as+ticket_id%2C%0A++++from+zendesk_tickets%0A++++where+tags+not+like+'%25escalated%25'%0A++++group+by+id%0A%29%2C%0A%0Anever_escalated_breaches_in_time_range+as%28%0A++++select%0A++++++++ztme.ticket_id+as+ticket_id%2C%0A++++from+zendesk_ticket_metric_events+ztme%0A++++join+never_escalated+ne+on+ne.ticket_id+%3D+ztme.ticket_id%0A++++where+%0A++++++++ztme.type+%3D+'breach'%0A++++++++and+ztme.metric+in+%28'reply_time'%2C+'pausable_update_time'%29%0A++++++++and+ztme.time+%3E%3D+%7Bfilters.dateRange.from%7D%0A++++++++and+ztme.time+%3C%3D+%7Bfilters.dateRange.to%7D%0A++++group+by+ztme.ticket_id%0A%29%2C%0A%0Aselect+%0A++++count%28distinct%28zt.id%29%29+as+num_breaches%2C%0A++++count%28distinct%28IF%28zt.id+in+%28select+ticket_id+from+never_escalated_breaches_in_time_range%29%2C+zt.id%2C+null%29%29%29as+never_escalated_breaches%2C%0A++++count%28distinct%28IF%28zt.id+in+%28select+ticket_id+from+before_escalated_breaches_in_time_range%29%2C+zt.id%2C+null%29%29%29+as+before_escalated_breaches%2C%0A++++zg.name%0Afrom+zendesk_tickets+zt%0Ajoin+zendesk_groups+zg+on+zg.id+%3D+zt.group_id%0Awhere+%0A++++zt.id+in+%28select+ticket_id+from+before_escalated_breaches_in_time_range%29%0A++++or+zt.id+in+%28select+ticket_id+from+never_escalated_breaches_in_time_range%29%0Agroup+by+zg.name%0Aorder+by+num_breaches+desc)

PostHog AI

```sql
with first_escalated as(
    select
        ticket_id,
        min(created_at) as timestamp
    from zendesk_ticket_events
    where child_events like '%escalated%'
    group by ticket_id
),
before_escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id,
    from zendesk_ticket_metric_events ztme
    join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time <= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),
never_escalated as(
    select
        id as ticket_id,
    from zendesk_tickets
    where tags not like '%escalated%'
    group by id
),
never_escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id,
    from zendesk_ticket_metric_events ztme
    join never_escalated ne on ne.ticket_id = ztme.ticket_id
    where
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),
select
    count(distinct(zt.id)) as num_breaches,
    count(distinct(IF(zt.id in (select ticket_id from never_escalated_breaches_in_time_range), zt.id, null)))as never_escalated_breaches,
    count(distinct(IF(zt.id in (select ticket_id from before_escalated_breaches_in_time_range), zt.id, null))) as before_escalated_breaches,
    zg.name
from zendesk_tickets zt
join zendesk_groups zg on zg.id = zt.group_id
where
    zt.id in (select ticket_id from before_escalated_breaches_in_time_range)
    or zt.id in (select ticket_id from never_escalated_breaches_in_time_range)
group by zg.name
order by num_breaches desc
```

### Escalated SLA % last 7 days by team

![Escalated SLA % last 7 days by team](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/Clean_Shot_2025_07_30_at_13_09_01_2x_ef1b32d6ed.png)![Escalated SLA % last 7 days by team](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/Clean_Shot_2025_07_30_at_13_08_44_2x_005d521ba7.png)

SQL query for Escalated SLA % last 7 days by team

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=with+first_escalated+as%28%0A++++select+%0A++++++++ticket_id%2C%0A++++++++min%28created_at%29+as+timestamp%0A++++from+zendesk_ticket_events%0A++++where+child_events+like+'%25%22added_tags%22%3A%5B%22escalated%22%25'%0A++++group+by+ticket_id%0A%29%2C%0A%0Aescalated_breaches_in_time_range+as%28%0A++++select%0A++++++++ztme.ticket_id+as+ticket_id%0A%0A++++from+zendesk_ticket_metric_events+ztme%0A++++++++join+first_escalated+fe+on+fe.ticket_id+%3D+ztme.ticket_id%0A++++where+%0A++++++++ztme.type+%3D+'breach'%0A++++++++and+ztme.metric+in+%28'reply_time'%2C+'pausable_update_time'%29%0A++++++++and+ztme.time+%3E%3D+fe.timestamp%0A++++++++and+ztme.time+%3E%3D+%7Bfilters.dateRange.from%7D%0A++++++++and+ztme.time+%3C%3D+%7Bfilters.dateRange.to%7D%0A++++group+by+ztme.ticket_id%0A%29%2C%0A%0Aescalated_fulfill_events_in_time_range+as+%28%0A++++select%0A++++++++ztme.ticket_id+as+ticket_id%0A%0A++++from+zendesk_ticket_metric_events+ztme%0A++++++++join+first_escalated+fe+on+fe.ticket_id+%3D+ztme.ticket_id%0A++++where+%0A++++++++ztme.type+%3D+'fulfill'%0A++++++++and+ztme.metric+in+%28'reply_time'%2C+'pausable_update_time'%29%0A++++++++and+ztme.time+%3E%3D+fe.timestamp%0A++++++++and+ztme.time+%3E%3D+%7Bfilters.dateRange.from%7D%0A++++++++and+ztme.time+%3C%3D+%7Bfilters.dateRange.to%7D%0A++++group+by+ztme.ticket_id%0A%29%2C%0A%0Abreaches_by_group+as+%28%0A++++select+%0A++++++++count%28distinct%28zt.id%29%29+as+num_breaches%2C%0A++++++++zg.name+as+group_name%0A++++from+zendesk_tickets+zt%0A++++++++join+zendesk_groups+zg+on+zg.id+%3D+zt.group_id%0A++++where+zt.id+in+%28select+ticket_id+from+escalated_breaches_in_time_range%29%0A++++group+by+zg.name%0A++++order+by+num_breaches+desc%0A%29%2C%0A%0Afulfills_by_group+as+%28%0A++++select+%0A++++++++count%28distinct%28zt.id%29%29+as+num_fulfills%2C%0A++++++++zg.name+as+group_name%0A++++from+zendesk_tickets+zt%0A++++++++join+zendesk_groups+zg+on+zg.id+%3D+zt.group_id%0A++++where+zt.id+in+%28select+ticket_id+from+escalated_fulfill_events_in_time_range%29%0A++++group+by+zg.name%0A++++order+by+num_fulfills+desc%0A%29%2C%0A%0Aselect%0A++++zg.name%2C%0A++++IF%28fbg.num_fulfills%3D0%2C+0%2C+%28fbg.num_fulfills-bbg.num_breaches%29%2Ffbg.num_fulfills%29+as+sla_attainment%0Afrom+zendesk_groups+zg%0A++++left+outer+join+breaches_by_group+bbg+on+bbg.group_name+%3D+zg.name%0A++++left+outer+join+fulfills_by_group+fbg+on+fbg.group_name+%3D+zg.name%0Awhere+%28bbg.num_breaches%3E0+or+fbg.num_fulfills%3E0%29%0Aorder+by+sla_attainment+desc)

PostHog AI

```sql
with first_escalated as(
    select
        ticket_id,
        min(created_at) as timestamp
    from zendesk_ticket_events
    where child_events like '%"added_tags":["escalated"%'
    group by ticket_id
),
escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id
    from zendesk_ticket_metric_events ztme
        join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),
escalated_fulfill_events_in_time_range as (
    select
        ztme.ticket_id as ticket_id
    from zendesk_ticket_metric_events ztme
        join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where
        ztme.type = 'fulfill'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),
breaches_by_group as (
    select
        count(distinct(zt.id)) as num_breaches,
        zg.name as group_name
    from zendesk_tickets zt
        join zendesk_groups zg on zg.id = zt.group_id
    where zt.id in (select ticket_id from escalated_breaches_in_time_range)
    group by zg.name
    order by num_breaches desc
),
fulfills_by_group as (
    select
        count(distinct(zt.id)) as num_fulfills,
        zg.name as group_name
    from zendesk_tickets zt
        join zendesk_groups zg on zg.id = zt.group_id
    where zt.id in (select ticket_id from escalated_fulfill_events_in_time_range)
    group by zg.name
    order by num_fulfills desc
),
select
    zg.name,
    IF(fbg.num_fulfills=0, 0, (fbg.num_fulfills-bbg.num_breaches)/fbg.num_fulfills) as sla_attainment
from zendesk_groups zg
    left outer join breaches_by_group bbg on bbg.group_name = zg.name
    left outer join fulfills_by_group fbg on fbg.group_name = zg.name
where (bbg.num_breaches>0 or fbg.num_fulfills>0)
order by sla_attainment desc
```

### Time UTC when tickets are created (last 6 months)

![Time UTC when tickets are created (last 6 months)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/Clean_Shot_2025_07_30_at_13_10_28_2x_ca4c7edfb5.png)![Time UTC when tickets are created (last 6 months)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/Clean_Shot_2025_07_30_at_13_10_45_2x_f14f728d22.png)

Time UTC when tickets are created (last 6 months)

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=with+all_tickets+as%28%0A++++select%0A++++++++count%28%29+as+total_tickets%0A++++from+zendesk_tickets%0A++++where+created_at+%3E%3D+toStartOfDay%28now%28%29%29+-+interval+6+month%0A%29%0A%0Aselect%0A++++toHour%28toTimeZone%28created_at%2C+'UTC'%29%29+as+hour_of_day%2C%0A++++count%28%29+as+count_per_hour%2C%0A++++count_per_hour%2F%28select+total_tickets+from+all_tickets%29+as+percentage_per_hour%0Afrom+zendesk_tickets%0Awhere+created_at+%3E%3D+toStartOfDay%28now%28%29%29+-+interval+6+month%0Agroup+by+hour_of_day)

PostHog AI

```sql
with all_tickets as(
    select
        count() as total_tickets
    from zendesk_tickets
    where created_at >= toStartOfDay(now()) - interval 6 month
)
select
    toHour(toTimeZone(created_at, 'UTC')) as hour_of_day,
    count() as count_per_hour,
    count_per_hour/(select total_tickets from all_tickets) as percentage_per_hour
from zendesk_tickets
where created_at >= toStartOfDay(now()) - interval 6 month
group by hour_of_day
```

## Problem #5: Tracking startup and YC plan growth, costs, and ROI

-   Sources:
    -   [Stripe](/docs/cdp/sources/stripe.md) for startup plan customer data
    -   [Postgres](/docs/cdp/sources/postgres.md) for billing data
-   Tables: `stripe_customer`, `stripe_invoice`, `postgres_billing_usagereport`, `postgres_billing_customer`

Each quarter, hundreds of startups sign up for our [startup plan](/startups.md) (which gives them $50k in free credits), dozens more sign up to our upgraded [YC plan](/handbook/marketing/startups.md#posthog-for-y-combinator). To make sure it is going well, we have a dashboard with details like:

1.  The number of organizations on the startup plan
2.  The cost of the startup plan for us based on credit usage.
3.  The count of startups who “graduate” and pay us money as well as how much money they pay us.

Unlike the revenue, churn, and growth data, we actually do use Stripe for this. We have metadata on Stripe customers saying if they are a startup plan customer and what type of startup plan they are on. This helps us get the customer count as well as the “graduate” details. We can then combine the Stripe data with our billing data in Postgres to get the cost of the startup plan for us based on credit usage.

This is all put together in a **Startup and YC Plan** dashboard with insights like these:

### Startup plan customer count (not YC)

![Startup plan customer count (not YC)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/startupcountlight_9c333baa95.png)![Startup plan customer count (not YC)](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/startupcountdark_d5d8ffcd16.png)

SQL query for startup plan customer count (not YC)

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++COUNT%28*%29+%0AFROM+%0A++++stripe_customer+%0AWHERE+%0A++++metadata.is_current_startup_plan_customer+%3D+'true'+%0A++++AND+metadata.startup_plan_label+!%3D+'YC')

PostHog AI

```sql
SELECT
    COUNT(*)
FROM
    stripe_customer
WHERE
    metadata.is_current_startup_plan_customer = 'true'
    AND metadata.startup_plan_label != 'YC'
```

### Previous startup & YC plan customers revenue per customer

![Previous startup & YC plan customers revenue per customer](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/revlight_1f9edaea10.png)![Previous startup & YC plan customers revenue per customer](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/revdark_8dce9d49b0.png)

SQL query for previous startup & YC plan customers revenue per customer

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=WITH+previous_startup_customers+AS+%28%0A++++SELECT+%0A++++++++sc.id+AS+customer_id%2C%0A++++++++sc.name+as+name%2C%0A++++++++sc.metadata.startup_plan_end_at%0A++++FROM+stripe_customer+AS+sc%0A++++WHERE+%0A++++++++sc.metadata.is_previous_startup_plan_customer+%3D+'true'%0A%29%0ASELECT+%0A++++psc.customer_id%2C%0A++++psc.name%2C%0A++++concat%28'%24'%2C+formatReadableQuantity%28SUM%28si.amount_paid+%2F+100%29%29%29+as+total_amount_paid%2C%0A++++concat%28'%24'%2C+formatReadableQuantity%28SUM%28si.total+%2F+100%29%29%29+as+total_invoice_amount%2C%0A++++concat%28'%24'%2C+formatReadableQuantity%28SUM%28%28si.starting_balance+-+si.ending_balance%29+%2F+100%29*-1%29%29+as+total_credits%2C%0A++++COUNT%28si.id%29+AS+invoice_count%0AFROM+stripe_invoice+AS+si%0AJOIN+previous_startup_customers+AS+psc+ON+psc.customer_id+%3D+si.customer_id%0AWHERE+%0A++++si.total+%3E+0%0A++++AND+%0A++++si.status+%3C%3E+'draft'%0Agroup+by+psc.customer_id%2C+psc.name%0Ahaving+SUM%28si.amount_paid%29+%3E+0%0Aorder+by+SUM%28si.amount_paid%29+desc)

PostHog AI

```sql
WITH previous_startup_customers AS (
    SELECT
        sc.id AS customer_id,
        sc.name as name,
        sc.metadata.startup_plan_end_at
    FROM stripe_customer AS sc
    WHERE
        sc.metadata.is_previous_startup_plan_customer = 'true'
)
SELECT
    psc.customer_id,
    psc.name,
    concat('$', formatReadableQuantity(SUM(si.amount_paid / 100))) as total_amount_paid,
    concat('$', formatReadableQuantity(SUM(si.total / 100))) as total_invoice_amount,
    concat('$', formatReadableQuantity(SUM((si.starting_balance - si.ending_balance) / 100)*-1)) as total_credits,
    COUNT(si.id) AS invoice_count
FROM stripe_invoice AS si
JOIN previous_startup_customers AS psc ON psc.customer_id = si.customer_id
WHERE
    si.total > 0
    AND
    si.status <> 'draft'
group by psc.customer_id, psc.name
having SUM(si.amount_paid) > 0
order by SUM(si.amount_paid) desc
```

### Startup & YC plan customers cohorts by starting month

![Startup & YC plan customers cohorts by starting month](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/cohortlight_72b7521357.png)![Startup & YC plan customers cohorts by starting month](https://res.cloudinary.com/dmukukwp6/image/upload/q_auto,f_auto/cohortdark_4b46c8b336.png)

SQL query for startup & YC plan customers cohorts by starting month

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++COUNT%28*%29+AS+customer_count%2C+%0A++++toStartOfMonth%28fromUnixTimestamp%28toInt%28metadata.startup_plan_start_at%29%29%29+AS+month_start%2C%0A++++SUM%28metadata.startup_plan_label+%3D+'YC'%29+AS+yc_customer_count%2C%0A++++SUM%28metadata.startup_plan_label+!%3D+'YC'%29+AS+non_yc_customer_count%0AFROM+%0A++++stripe_customer+%0AWHERE+%0A++++metadata.is_current_startup_plan_customer+%3D+'true'+%0A++++OR+metadata.is_previous_startup_plan_customer+%3D+'true'%0AGROUP+BY+%0A++++month_start%0AORDER+BY+%0A++++month_start+ASC)

PostHog AI

```sql
SELECT
    COUNT(*) AS customer_count,
    toStartOfMonth(fromUnixTimestamp(toInt(metadata.startup_plan_start_at))) AS month_start,
    SUM(metadata.startup_plan_label = 'YC') AS yc_customer_count,
    SUM(metadata.startup_plan_label != 'YC') AS non_yc_customer_count
FROM
    stripe_customer
WHERE
    metadata.is_current_startup_plan_customer = 'true'
    OR metadata.is_previous_startup_plan_customer = 'true'
GROUP BY
    month_start
ORDER BY
    month_start ASC
```

## How should you get started?

Feeling inspired? Here's how you can get started:

1.  Start by [linking a source](/docs/cdp/sources.md) from a tool you already use.
2.  Go to the [SQL editor](https://us.posthog.com/sql) and start by writing a basic query like `select * from events limit 10`.
3.  Layer more complexity, like filtering, [aggregating](/docs/data-warehouse/sql/useful-functions.md#aggregate-functions), and [joining](/docs/data-warehouse/join.md) data. Use [PostHog AI](/ai.md) to help you with this.
4.  Use our [SQL visualizations](/docs/data-warehouse/query.md#sql-visualizations) to see your data in a new way.
5.  Build a dashboard of related insights and share them with your team.

> PostHog is the leading platform for building self-driving products. With a full suite of developer tools – [AI observability](/ai-observability.md), [product analytics](/product-analytics.md), [session replay](/session-replay.md), [feature flags](/feature-flags.md), [experiments](/experiments.md), [error tracking](/error-tracking.md), [logs](/logs.md), and more – PostHog captures all the context agents need to diagnose problems, uncover opportunities, and ship fixes. A [data warehouse](/data-stack.md) and [CDP](/cdp.md) tie it all together, unifying that context into one source agents can read across. You can steer it all from [Slack](/slack.md), [the web app](/ai.md), the desktop ([PostHog Desktop](/desktop.md)), or your own editor via [the MCP](/mcp.md).

### Community questions

Ask a question