Cross-Analysing BigQuery and CRM Data in Slack

Cross-Analysing BigQuery and CRM Data in Slack

Your product data is in BigQuery. Your sales pipeline is in HubSpot. Your revenue is in Shopify. Your ad performance is in Google Ads. Each platform tells part of the story. None of them tells the whole story.

The question isn't "how do I get data from X to Slack." It's "how do I get the right combination of data to Slack so my team sees the full picture."

This guide covers two approaches: joining sources in SQL upstream, and letting Chartcastr's AI layer correlate them at delivery time.

---

Why Cross-Source Analysis Matters

When metrics are viewed in isolation, teams make worse decisions.

Example 1: Revenue is flat, but why?

Your BigQuery revenue view shows flat week-over-week numbers. Without CRM context, you don't know if this is a pipeline problem (HubSpot shows fewer deals closing), a product problem (BigQuery events show lower activation), or a marketing problem (Google Ads ROAS dropped). All three would produce flat revenue. The fix is completely different in each case.

Example 2: Activation is up, so what?

Your product analytics view shows a 20% spike in user activations. Exciting. But HubSpot shows deal velocity actually slowing. Chartcastr AI connecting those two data points produces a more useful insight: "Activation is up, driven by a surge in free trial signups from a promotion last week. Paid deal velocity hasn't improved, pipeline may need attention."

Cross-source analysis turns "here's what happened" into "here's what it means."

---

Approach 1: Join in BigQuery (Single Combined View)

If you want a single chart that shows multiple sources together, join them upstream in SQL. This requires syncing your CRM/commerce data into BigQuery first.

Step 1: Get your data sources into BigQuery

Use a managed connector to sync your SaaS tools into BigQuery:

• HubSpot: Fivetran HubSpot connector → BigQuery destination
• Shopify: Fivetran or Airbyte Shopify connector → BigQuery
• Google Ads: Google Ads Data Transfer Service (free, native GCP) → BigQuery
• Meta Ads: Fivetran Meta Ads connector → BigQuery

Most of these sync in under an hour. Once synced, your warehouse becomes the single source of truth.

Step 2: Write the cross-source join view

-- Example: pipeline-to-revenue view
-- Joins BigQuery product events with HubSpot deals and Shopify orders

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

WITH weekly_activations AS (
  SELECT
    DATE_TRUNC(event_date, WEEK(MONDAY)) AS week_starting,
    COUNT(DISTINCT user_id)              AS activated_users
  FROM `your-project.product.user_events`
  WHERE event_name = 'account_activated'
    AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
  GROUP BY 1
),

weekly_deals AS (
  SELECT
    DATE_TRUNC(PARSE_DATE('%Y-%m-%d', close_date), WEEK(MONDAY)) AS week_starting,
    COUNT(*)                             AS deals_closed,
    SUM(amount)                          AS pipeline_closed_usd
  FROM `your-project.hubspot.deals`
  WHERE deal_stage = 'closedwon'
    AND close_date >= FORMAT_DATE('%Y-%m-%d', DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK))
  GROUP BY 1
),

weekly_revenue AS (
  SELECT
    DATE_TRUNC(created_at, WEEK(MONDAY)) AS week_starting,
    SUM(total_price)                     AS shopify_revenue_usd,
    COUNT(DISTINCT order_id)             AS orders
  FROM `your-project.shopify.orders`
  WHERE financial_status = 'paid'
    AND created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
  GROUP BY 1
)

SELECT
  COALESCE(a.week_starting, d.week_starting, r.week_starting) AS week_starting,
  COALESCE(a.activated_users, 0)       AS product_activations,
  COALESCE(d.deals_closed, 0)          AS deals_closed,
  COALESCE(d.pipeline_closed_usd, 0)   AS crm_revenue_usd,
  COALESCE(r.shopify_revenue_usd, 0)   AS ecommerce_revenue_usd,
  COALESCE(r.orders, 0)                AS orders
FROM weekly_activations a
FULL OUTER JOIN weekly_deals d USING (week_starting)
FULL OUTER JOIN weekly_revenue r USING (week_starting)
ORDER BY 1 DESC

This produces one row per week with columns from all three sources. Connect this view via Connected Sheets or Scheduled Queries, then deliver it to Slack with Chartcastr.

What Chartcastr AI does with this: It sees activation, CRM revenue, and ecommerce revenue in a single dataset. When activations are up but CRM revenue is lagging, it can call that out directly: "Product activations improved this week (+34%) but CRM closed revenue is flat, the top-of-funnel improvement hasn't reached deal close yet."

Cross-source views for common combinations

Marketing spend vs revenue attribution:

-- Join Google Ads spend with Shopify revenue and GA4 attribution
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_starting,
  SUM(cost)                       AS ad_spend_usd,
  SUM(conversions)                AS attributed_conversions,
  SUM(conversion_value)           AS attributed_revenue_usd,
  ROUND(SAFE_DIVIDE(SUM(conversion_value), SUM(cost)), 2) AS roas
FROM `your-project.google_ads.campaign_stats`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY 1
ORDER BY 1 DESC

Customer health: product engagement + support tickets:

SELECT
  DATE_TRUNC(w.week_start, WEEK) AS week_starting,
  COUNT(DISTINCT pe.account_id)  AS accounts_active,
  AVG(pe.health_score)           AS avg_health_score,
  COUNT(st.ticket_id)            AS support_tickets_opened,
  COUNT(CASE WHEN st.priority = 'high' THEN 1 END) AS high_priority_tickets
FROM `your-project.reporting.weekly_account_activity` w
LEFT JOIN `your-project.product.engagement_scores` pe USING (account_id, week_start)
LEFT JOIN `your-project.support.tickets` st
  ON st.account_id = pe.account_id
  AND DATE_TRUNC(st.created_at, WEEK) = w.week_start
GROUP BY 1
ORDER BY 1 DESC

---

Approach 2: Separate Sources, AI Cross-Analysis

If you don't have all your data in BigQuery, or you want the setup speed without a join query, connect each source separately to Chartcastr. The AI analysis layer reads across all connected sources when generating each pulse.

Setup:

1. Connect BigQuery (via Connected Sheets) as a Chartcastr source, product/revenue data
2. Connect Shopify directly as a Chartcastr source
3. Connect HubSpot directly as a Chartcastr source
4. Connect Google Ads directly if available

Each connection posts its own chart to Slack. But the AI that writes the summary for each chart has context from all connected sources, so it can notice cross-source patterns.

What this looks like in Slack:

• #product-weekly receives the BigQuery activation chart at 9am Monday
• AI summary: "Activations up 28% week-over-week. Noteworthy: Shopify data shows a 15% increase in first-time purchases the same week, the new onboarding flow may be converting trial users to paid more effectively. HubSpot shows 3 enterprise deals moved to 'proposal sent' this week, up from 1 the prior week."

This is cross-source analysis without a data join. The AI is doing the correlation work that a human analyst would otherwise do manually.

Limitation: You can't chart data from two sources in a single chart. For combined bar charts, you need the SQL join approach.

---

Setting Up the Slack Channels

For cross-source analysis to be useful, the right people need to see it. A few channel patterns that work:

---

The ROI of Cross-Source Visibility

The value of getting this right isn't the time saved on screenshots, it's the decisions that happen because someone saw two metrics in the same place at the same time.

When your head of sales sees HubSpot pipeline declining in the same Slack message where your BigQuery shows product engagement dropping, they don't wait for the quarterly review. They ask a question in the thread. Chartcastr answers. The conversation happens on Tuesday morning, not at the quarterly review in six weeks.

That's the outcome cross-source analysis in Slack produces: faster decisions, with better context, without needing a data analyst to prepare a slide deck.

---

Getting Started

1. Connect BigQuery to Slack, the base layer, usually set up first
2. Add your CRM or commerce source, Shopify and HubSpot connect directly to Chartcastr without going through BigQuery
3. Write a cross-source view in BigQuery if you want combined charts
4. Let Chartcastr's AI surface cross-source patterns automatically across separate connections

---

Related Reading

• SQL Views as a Reporting Layer: BigQuery to Slack, designing the SQL views that power cross-source joins
• BigQuery Connected Sheets to Slack: Setup Guide, the technical setup for the BigQuery connection
• Cross-Analyse Klaviyo, Shopify, and Google Ads in One Place, same cross-analysis pattern without BigQuery
• Turn Your BigQuery Views into Slack Reports, use case overview