# Create project using gcloud CLI
gcloud projects create my-analytics-project
gcloud config set project my-analytics-project
gcloud services enable bigquery.googleapis.com

-- Create dataset
CREATE SCHEMA `my-project.analytics_dataset`
  OPTIONS(
    description="Analytics dataset",
    location="US"
  );

-- Create example table (GA4 data)
CREATE TABLE `my-project.analytics_dataset.events` (
  event_date DATE,
  event_name STRING,
  user_id INT64,
  event_value FLOAT64,
  event_timestamp TIMESTAMP,
  geo_country STRING,
  device_category STRING
);

SELECT
  event_date,
  event_name,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(event_value) as total_revenue,
  AVG(event_value) as avg_revenue_per_event
FROM `my-project.analytics_dataset.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY event_date, event_name
ORDER BY event_date DESC

SELECT
  e.event_date,
  e.event_name,
  u.user_country,
  u.user_tier,
  COUNT(DISTINCT e.user_id) as unique_users,
  SUM(e.event_value) as revenue
FROM `my-project.analytics_dataset.events` e
LEFT JOIN `my-project.analytics_dataset.users` u
  ON e.user_id = u.user_id
WHERE e.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY e.event_date, e.event_name, u.user_country, u.user_tier

-- Calculate and store aggregated data daily in BigQuery
CREATE OR REPLACE TABLE `my-project.analytics_dataset.daily_summary` AS
SELECT
  CURRENT_DATE() as report_date,
  event_name,
  user_country,
  COUNT(DISTINCT user_id) as daily_users,
  SUM(event_value) as daily_revenue,
  AVG(event_value) as avg_event_value,
  MAX(event_timestamp) as last_event_time
FROM `my-project.analytics_dataset.events`
WHERE event_date = CURRENT_DATE() - 1
GROUP BY event_name, user_country

┌─────────────────────────────────────┐
│ Header: Logo | Filters/Date Picker  │  ← Users see this first
├─────────────────────────────────────┤
│ KPI 1  │ KPI 2  │ KPI 3  │ KPI 4   │  ← Key metrics (3-4)
├─────────────────────────────────────┤
│                                     │
│ Main Chart (time series/comparison) │  ← Deep insights
│                                     │
├─────────────────────────────────────┤
│ Concrete data table                 │  ← Detailed analysis
│ (Drilldown enabled)                 │
├─────────────────────────────────────┤
│ Additional Insights / Map / Heatmap │
└─────────────────────────────────────┘

┌──────────────────────────────────────────────────┐
│ 📊 Jan 2026 Sales Analysis | 🔽 Country | 📅 Date │
├──────────────────────────────────────────────────┤
│ Total Revenue: $125,000  │ Orders: 3,200   │ Conversion: 3.5% │
├──────────────────────────────────────────────────┤
│         Daily Revenue Trend (Line Chart)          │
│    ↗ Upward trend: +15% vs last month             │
├──────────────────────────────────────────────────┤
│ Sales by Category  │  Top 10 Products             │
│ (Bar chart)        │  (Table, sortable)           │
├──────────────────────────────────────────────────┤
│        Revenue Distribution by Region (Map)       │
└──────────────────────────────────────────────────┘

Example: Country selection filter
- All countries
- South Korea
- Japan
- United States
Shows only data for the selected country

-- Show only customers with revenue >= $10,000
WHERE customer_revenue >= 10000

-- 1. Date filter
event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL @date_range_days DAY)

-- 2. Dropdown filter (user input)
WHERE country = @selected_country

-- 3. Composite filter
WHERE event_date >= @start_date
  AND event_date <= @end_date
  AND country IN (@country_list)
  AND revenue >= @min_revenue

-- ❌ Inefficient query
SELECT * FROM events
WHERE DATE(event_timestamp) >= '2026-01-01'

-- ✅ Optimized query (using partition)
SELECT * FROM events
WHERE event_date >= '2026-01-01'  -- use partition key directly

-- Scheduled query running at midnight every day
CREATE OR REPLACE TABLE `my-project.looker_studio_data.dashboard_snapshot` AS
SELECT
  event_date,
  event_name,
  country,
  device_category,
  COUNT(DISTINCT user_id) as users,
  SUM(event_value) as revenue,
  COUNT(*) as events
FROM `my-project.analytics_dataset.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY event_date, event_name, country, device_category;

// Community Connector example (Apps Script)
function getConfig() {
  return {
    configParams: [
      {
        name: 'project_id',
        displayName: 'BigQuery Project ID',
        helpText: 'Your GCP Project ID',
        placeholder: 'my-project-id'
      },
      {
        name: 'dataset_id',
        displayName: 'Dataset ID'
      }
    ]
  };
}

function getData(request) {
  const projectId = request.configParams.project_id;
  const datasetId = request.configParams.dataset_id;

  // Load data from BigQuery
  const bq = BigQuery.newDataset(projectId, datasetId);
  // ... Data processing logic

  return { rows: data };
}

-- Grant table access permission to specific users only
GRANT `roles/bigquery.dataViewer`
ON TABLE `my-project.analytics_dataset.events`
TO "user@example.com";

-- Row-Level Security
CREATE OR REPLACE ROW ACCESS POLICY rls_by_country
ON `my-project.analytics_dataset.events`
GRANT ('editor@company.com') TO ('KR'),
      ('viewer@company.com') TO ('US', 'JP');

## Dashboard Setup Checklist

### Data Source Configuration
- [ ] BigQuery project/dataset prepared
- [ ] IAM permissions configured
- [ ] Scheduled queries configured (performance optimization)
- [ ] Data source connection tested

### Dashboard Design
- [ ] F-pattern layout applied
- [ ] KPI tiles placed (3-4)
- [ ] Main charts added (trend/comparison)
- [ ] Detail table included
- [ ] Interactive filters added

### Performance Optimization
- [ ] Partition key usage verified
- [ ] Query cost optimized
- [ ] Caching strategy applied
- [ ] Chart count verified (20-25 or fewer)

### Sharing and Security
- [ ] Access permissions configured
- [ ] Data security reviewed
- [ ] Sharing link created

-- 1. Create daily summary table
CREATE OR REPLACE TABLE `my-project.looker_data.daily_metrics` AS
SELECT
  event_date,
  COUNT(DISTINCT user_id) as dau,
  SUM(revenue) as total_revenue,
  COUNT(*) as total_events
FROM `my-project.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY event_date;

-- 2. Connect to this table in Looker Studio
-- 3. Add KPI scorecards: DAU, total revenue
-- 4. Visualize daily trend with line chart

-- Prepare data for cohort analysis
CREATE OR REPLACE TABLE `my-project.looker_data.cohort_analysis` AS
WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(event_date), WEEK) as cohort_week
  FROM `my-project.analytics.events`
  GROUP BY user_id
)
SELECT
  uc.cohort_week,
  DATE_DIFF(e.event_date, uc.cohort_week, WEEK) as week_number,
  COUNT(DISTINCT e.user_id) as active_users
FROM `my-project.analytics.events` e
JOIN user_cohort uc ON e.user_id = uc.user_id
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;