SQL Views as a Reporting Layer: Designing BigQuery Views for Slack

SQL Views as a Reporting Layer: Designing BigQuery Views for Slack

Getting BigQuery data into Slack is a two-part problem. The first part, the technical setup of Connected Sheets or Scheduled Queries, is well-documented. The second part is less discussed: what SQL do you actually write?

A poorly structured query produces charts nobody can read. A well-structured reporting view produces charts that your sales lead, your CFO, and your CEO can interpret without a data analyst in the room. That's the goal.

This guide covers the patterns that make BigQuery views work well as a Slack reporting layer.

---

The Core Principle: A Reporting View Is Not a Raw Table

Raw tables are optimised for storage and query efficiency. Reporting views are optimised for human understanding. They're different things.

Your reporting view applies the business logic, cleans the presentation, aggregates to the right granularity, and produces something a human can look at and immediately understand.

---

Pattern 1: The Time-Series Breakdown

The most common Slack chart shape: a metric over time, broken down by a dimension.

CREATE OR REPLACE VIEW `your-project.reporting.v_weekly_revenue_by_channel` AS

SELECT
  -- Time dimension: aggregate to the week boundary your team uses
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS week_starting,

  -- Breakdown dimension: keep cardinality low (< 8 values ideal)
  COALESCE(acquisition_channel, 'direct') AS channel,

  -- Metrics: rename to plain English
  COUNT(DISTINCT order_id)              AS orders,
  SUM(net_revenue_usd)                  AS revenue_usd,
  ROUND(AVG(net_revenue_usd), 2)        AS avg_order_value

FROM `your-project.sales.orders`

WHERE
  -- Rolling window: enough history for trend context, not too much to be noisy
  order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 16 WEEK)
  -- Exclude incomplete current week so charts don't show a misleadingly low bar
  AND order_date < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  -- Only completed orders
  AND status = 'completed'

GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC

Key decisions in this view:

• WEEK(MONDAY), anchors week starts to Monday, matching how most teams talk about weeks
• COALESCE(..., 'direct'), fills nulls so they appear in the chart with a readable label
• Excludes the current incomplete week, prevents a misleadingly partial bar at the right end of the chart
• 16 weeks of history, enough context for trends, not so much that the chart is dense

---

Pattern 2: Period-over-Period Comparison

Slack charts are most useful when they show change. A single number is forgettable. A number with a comparison is actionable.

CREATE OR REPLACE VIEW `your-project.reporting.v_monthly_revenue_comparison` AS

WITH monthly AS (
  SELECT
    DATE_TRUNC(order_date, MONTH) AS month,
    SUM(net_revenue_usd)          AS revenue_usd,
    COUNT(DISTINCT order_id)      AS orders,
    COUNT(DISTINCT customer_id)   AS customers
  FROM `your-project.sales.orders`
  WHERE
    order_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), MONTH)
    AND status = 'completed'
  GROUP BY 1
)
SELECT
  m.month,
  m.revenue_usd,
  m.orders,
  m.customers,
  -- Month-over-month change — useful for AI analysis context
  m.revenue_usd - LAG(m.revenue_usd) OVER (ORDER BY m.month) AS revenue_mom_change,
  ROUND(
    SAFE_DIVIDE(
      m.revenue_usd - LAG(m.revenue_usd) OVER (ORDER BY m.month),
      LAG(m.revenue_usd) OVER (ORDER BY m.month)
    ) * 100,
    1
  ) AS revenue_mom_pct
FROM monthly m
ORDER BY m.month DESC

The revenue_mom_change and revenue_mom_pct columns let Chartcastr's AI say things like "March revenue was up 14% versus February" without needing to compute it at delivery time.

---

Pattern 3: Funnel / Conversion Tracking

For product and marketing teams who need to see drop-off across stages.

CREATE OR REPLACE VIEW `your-project.reporting.v_weekly_funnel` AS

SELECT
  DATE_TRUNC(event_date, WEEK(MONDAY)) AS week_starting,

  -- Funnel stages as individual columns (not as rows)
  COUNT(DISTINCT CASE WHEN stage = 'visit'     THEN session_id END) AS visits,
  COUNT(DISTINCT CASE WHEN stage = 'signup'    THEN session_id END) AS signups,
  COUNT(DISTINCT CASE WHEN stage = 'activated' THEN session_id END) AS activations,
  COUNT(DISTINCT CASE WHEN stage = 'converted' THEN session_id END) AS conversions,

  -- Conversion rates as decimals (Chartcastr formats as %)
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN stage = 'signup'    THEN session_id END),
    COUNT(DISTINCT CASE WHEN stage = 'visit'     THEN session_id END)
  ), 3) AS visit_to_signup_rate,

  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN stage = 'converted' THEN session_id END),
    COUNT(DISTINCT CASE WHEN stage = 'signup'    THEN session_id END)
  ), 3) AS signup_to_conversion_rate

FROM `your-project.product.user_events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY 1
ORDER BY 1 DESC

This produces one row per week with all funnel stages as columns, easy to chart as a grouped bar, and the conversion rate columns let Chartcastr AI flag when conversion drops without needing trend logic.

---

Column Naming Rules for Slack Charts

Chart labels come from your column names. These rules make charts readable without explanation:

Numbers that represent percentages should either be:

• Stored as decimals (0.142 = 14.2%), tools auto-format these
• Or stored with _pct suffix to signal their meaning (14.2)

Avoid abbreviations. Avoid underscores at the start of names. Avoid internal system codes as values.

---

Managing Views with dbt or Dataform

For teams managing many reporting views, version control matters. Both dbt and Dataform can create and schedule BigQuery views:

dbt:

-- models/reporting/weekly_revenue_by_channel.sql
-- config: materialized='view'
SELECT
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS week_starting,
  COALESCE(acquisition_channel, 'direct') AS channel,
  COUNT(DISTINCT order_id)  AS orders,
  SUM(net_revenue_usd)      AS revenue_usd
FROM {{ ref('stg_orders') }}
WHERE
  order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 16 WEEK)
  AND order_date < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  AND status = 'completed'
GROUP BY 1, 2

Dataform SQLX:

// definitions/reporting/weekly_revenue_by_channel.sqlx
config { type: "view" }

SELECT
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS week_starting,
  COALESCE(acquisition_channel, 'direct') AS channel,
  COUNT(DISTINCT order_id)  AS orders,
  SUM(net_revenue_usd)      AS revenue_usd
FROM ${ref("stg_orders")}
WHERE
  order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 16 WEEK)
  AND order_date < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  AND status = 'completed'
GROUP BY 1, 2

Both generate the same view in BigQuery. The difference is version control (git), testing assertions, and dependency graphs. If you have more than five reporting views, the investment in either tool pays off quickly.

---

One View, Multiple Audiences

A single well-designed view can power multiple Slack channels with different chart configurations:

• v_weekly_revenue_by_channel → #marketing-weekly (revenue + orders, bar chart, every Monday)
• Same view → #exec-updates (revenue only, line chart, every other Monday)
• Same view → #data-ops (all columns, table view, first Monday of month)

In Chartcastr, each connection can select different columns from the same source and deliver to different channels on different schedules. You write the SQL once. The view is the single source of truth.

---

Related Reading

• BigQuery Connected Sheets to Slack: Step-by-Step Setup, how to connect your view to Sheets and then to Chartcastr
• BigQuery Scheduled Queries → Slack Reporting, scheduling the data refresh before Chartcastr delivers
• Cross-Analysing BigQuery and CRM Data in Slack, joining warehouse data with Shopify, HubSpot, and ad data for richer Slack pulses
• Best Tool to Load Google Sheets to BigQuery, getting manual data into BigQuery to join with warehouse data