BigQuery Scheduled Queries → Slack: Automate Your Weekly Data Pulse

BigQuery Scheduled Queries → Slack: Automate Your Weekly Data Pulse

BigQuery Scheduled Queries solve a specific problem: you want data pre-computed and ready before your team needs it, not computed on demand. Instead of triggering a refresh when someone checks a dashboard, a Scheduled Query runs at 6am, writes the latest rollup into a Google Sheet, and by the time Chartcastr delivers it to Slack at 9am, the data is already fresh.

This guide covers how to set that up end-to-end.

---

When to Use Scheduled Queries vs Connected Sheets

Both approaches result in a Google Sheet that Chartcastr can read from. The difference is when the BigQuery query runs.

Use Scheduled Queries when:

• Your rollup query is slow or expensive (> a few seconds)
• You want data pre-computed before delivery, not at delivery time
• You're using a flat-rate BigQuery reservation and want predictable query consumption
• You need to write to a permanent table for historical comparison

Use Connected Sheets when:

• Your query is fast and cheap (< a few seconds, < a few hundred MB scanned)
• You want the freshest possible data at delivery time

---

Step 1: Create a Rollup Query

Write the query that produces the output you want in Slack. Scheduled Queries write the results of a SELECT query into a destination, so your query should return exactly the rows and columns you want to chart.

-- Weekly marketing performance: last 16 weeks, by channel
SELECT
  DATE_TRUNC(event_date, WEEK(MONDAY)) AS week_start,
  traffic_source_medium               AS channel,
  SUM(sessions)                       AS sessions,
  SUM(new_users)                      AS new_users,
  SUM(transactions)                   AS transactions,
  ROUND(SUM(transaction_revenue), 2)  AS revenue_usd
FROM `your-project.analytics.weekly_sessions`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 16 WEEK)
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC

Test this in the BigQuery Console first. Check that:

• Column names are human-readable (they'll appear as chart labels)
• The row count is manageable (under 1,000 rows for a sheet destination)
• The date column is a proper DATE or DATETIME type, not a string

---

Step 2: Create the Scheduled Query

1. In the BigQuery Console, run your query once to verify results.
2. Click Schedule (top toolbar) → Create new scheduled query.
3. Query name: something descriptive like weekly_marketing_to_sheets
4. Schedule type: choose your cadence
   • For weekly Monday reports: Repeating → Custom → Monday 6am UTC
   • For daily morning reports: Repeating → Daily → 6:00 AM
5. Destination for query results:
   • Select Google Sheets
   • Paste the URL of your destination Google Sheet
   • Enter the tab name (e.g. bq_marketing_weekly)
   • Set Write preference to Overwrite destination (so old data is replaced, not appended)
6. Service account: use a service account with BigQuery Data Viewer and Google Sheets write access, or use your own Google account.
7. Click Save.

The scheduled query will now run on the schedule you set and write results directly into the specified Google Sheet tab.

Testing it immediately

To verify the setup without waiting for the next run:

• In the Scheduled Queries list, find your query
• Click the three-dot menu → Run now
• Check your Google Sheet, the results should appear within 30–60 seconds

---

Step 3: Connect the Sheet to Chartcastr

With the Scheduled Query writing results on a recurring schedule, the Google Sheet tab now contains your latest rollup data. Connect it to Chartcastr:

1. In Chartcastr → Sources → Add source → Google Sheets
2. Authenticate with Google
3. Paste the sheet URL, select the tab containing your Scheduled Query output
4. Confirm the data range (Chartcastr auto-detects headers)
5. Save the source

---

Step 4: Time Your Chartcastr Schedule

The key to fresh data is ensuring Chartcastr delivers after the Scheduled Query has completed.

If your Scheduled Query runs at 6:00am Monday, set Chartcastr to deliver at 8:30am Monday. That gives:

• 6:00am: Scheduled Query starts
• ~6:05am: Query completes, results written to Sheets
• 8:30am: Chartcastr reads the sheet and posts to Slack

Your team gets fresh weekly data in Slack before standup, with no manual intervention.

---

Step 5: Chartcastr Slack Delivery

1. In Chartcastr → Connections → New connection
2. Select your Google Sheets source
3. Pick the Slack channel (e.g. #marketing-weekly)
4. Confirm the chart type, for a time-series breakdown, a grouped bar chart usually works well
5. Set the schedule: e.g. every Monday at 8:30am
6. Activate

Done. Your pipeline is:

Monday 6:00am  →  BigQuery Scheduled Query runs
                        ↓ results written to Google Sheet
Monday 8:30am  →  Chartcastr reads sheet → renders chart
                        ↓ posts to Slack with AI summary
Monday 8:30am  →  #marketing-weekly receives weekly pulse

---

Advanced: Write to a BQ Table First, Then to Sheets

For large datasets or historical tracking, write the Scheduled Query results to a native BigQuery table instead of directly to Sheets. Then use a second, lightweight query to read the latest week from that table and write it to Sheets for Chartcastr.

-- Scheduled Query 1: compute and store weekly rollups
-- Destination: your-project.reporting.weekly_marketing_rollup
-- Schedule: every Monday at 5am
SELECT
  DATE_TRUNC(event_date, WEEK(MONDAY)) AS week_start,
  traffic_source_medium AS channel,
  SUM(sessions) AS sessions,
  ROUND(SUM(transaction_revenue), 2) AS revenue_usd
FROM `your-project.analytics.weekly_sessions`
WHERE event_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), WEEK)
  AND event_date < DATE_TRUNC(CURRENT_DATE(), WEEK)
GROUP BY 1, 2

-- Scheduled Query 2: export latest 12 weeks to Sheets for Chartcastr
-- Destination: Google Sheet tab 'bq_marketing_weekly'
-- Schedule: every Monday at 6am (runs after Query 1)
SELECT *
FROM `your-project.reporting.weekly_marketing_rollup`
ORDER BY week_start DESC
LIMIT 100

This pattern keeps your historical data in BigQuery (queryable, versioned) while the Sheets tab stays small and Chartcastr reads quickly.

---

Scheduling Patterns for Different Reports

Pick whichever timezone your team works in.

---

Related Reading

• BigQuery Connected Sheets to Slack: Step-by-Step Setup, the simpler, on-demand alternative to Scheduled Queries
• SQL Views as a Reporting Layer: BigQuery to Slack, how to design your BigQuery views for great Slack charts
• Create a Chart from BigQuery and Post it to Slack, comparison of all BQ → Slack approaches including Python
• Turn Your BigQuery Views into Slack Reports, use case overview