Granola Observability

# Zapier: Granola → BigQuery pipeline
Trigger: Granola — Note Added to Folder ("All Meetings")

Step 1 — Code by Zapier (extract metadata):
  const data = {
    meeting_id: inputData.title + '_' + inputData.calendar_event_datetime,
    title: inputData.title,
    date: inputData.calendar_event_datetime,
    creator: inputData.creator_email,
    attendee_count: JSON.parse(inputData.attendees || '[]').length,
    has_action_items: inputData.note_content.includes('- [ ]'),
    action_item_count: (inputData.note_content.match(/- \[ \]/g) || []).length,
    has_decisions: inputData.note_content.includes('## Decision') ||
                   inputData.note_content.includes('## Key Decision'),
    word_count: inputData.note_content.split(/\s+/).length,
    is_external: JSON.parse(inputData.attendees || '[]')
      .some(a => !a.email?.endsWith('@company.com')),
    workspace: inputData.folder || 'unknown',
    captured_at: new Date().toISOString(),
  };
  output = [data];

Step 2 — BigQuery: Insert Row
  Dataset: meeting_analytics
  Table: granola_meetings
  Row: {{metadata from step 1}}

CREATE TABLE meeting_analytics.granola_meetings (
  meeting_id STRING NOT NULL,
  title STRING,
  date TIMESTAMP,
  creator STRING,
  attendee_count INT64,
  has_action_items BOOL,
  action_item_count INT64,
  has_decisions BOOL,
  word_count INT64,
  is_external BOOL,
  workspace STRING,
  captured_at TIMESTAMP
);

-- Weekly meeting volume by workspace
SELECT
  workspace,
  DATE_TRUNC(date, WEEK) AS week,
  COUNT(*) AS meeting_count,
  SUM(action_item_count) AS total_actions,
  AVG(attendee_count) AS avg_attendees
FROM meeting_analytics.granola_meetings
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY workspace, week
ORDER BY week DESC, workspace;

-- Adoption: active users per week
SELECT
  DATE_TRUNC(date, WEEK) AS week,
  COUNT(DISTINCT creator) AS active_users
FROM meeting_analytics.granola_meetings
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
GROUP BY week
ORDER BY week DESC;

-- Meeting efficiency score (has action items + decisions + < 8 attendees)
SELECT
  title,
  date,
  CASE
    WHEN has_action_items AND has_decisions AND attendee_count <= 8 THEN 'Efficient'
    WHEN has_action_items OR has_decisions THEN 'Partially Efficient'
    ELSE 'Low Efficiency'
  END AS efficiency_rating
FROM meeting_analytics.granola_meetings
ORDER BY date DESC
LIMIT 50;

-- External vs internal meeting ratio
SELECT
  DATE_TRUNC(date, MONTH) AS month,
  COUNTIF(is_external) AS external_meetings,
  COUNTIF(NOT is_external) AS internal_meetings,
  ROUND(COUNTIF(is_external) * 100.0 / COUNT(*), 1) AS external_pct
FROM meeting_analytics.granola_meetings
GROUP BY month
ORDER BY month DESC;

Trigger: Schedule by Zapier — Every Friday at 5 PM

Step 1 — BigQuery: Run Query
  Query: "SELECT COUNT(*) as meetings, SUM(action_item_count) as actions,
          COUNT(DISTINCT creator) as active_users
          FROM meeting_analytics.granola_meetings
          WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)"

Step 2 — Slack: Send Message to #leadership
  Message: |
    :bar_chart: *Weekly Granola Report*

    *This Week:*
    - Meetings captured: {{meetings}}
    - Action items created: {{actions}}
    - Active users: {{active_users}}

    [View full dashboard →]

# Check Granola service status
curl -s https://status.granola.ai/api/v2/status.json | python3 -c "
import json, sys
data = json.load(sys.stdin)
status = data.get('status', {}).get('description', 'Unknown')
print(f'Granola Status: {status}')
"