# Looker & Looker Studio

Looker and Looker Studio don't consume REST APIs directly — they connect to databases, warehouses, and spreadsheets. So an OmniCart → Looker setup always has the same shape:

```
OmniCart API ──(scheduled pull)──▶ storage Looker can read ──▶ Looker / Looker Studio
```

This guide covers both paths: the lightweight one (Looker Studio + Google Sheets) and the production one (a warehouse + Looker/Looker Studio).

## Path A — Looker Studio via Google Sheets (fastest)

Good for a daily KPI dashboard with no infrastructure.

**1. Schedule a daily metrics pull into a Sheet.** In Google Sheets, add an Apps Script (Extensions → Apps Script) with a daily time-driven trigger:

```javascript
function pullDailyMetrics() {
  // Secret API key (Settings → API Key Management). Store it in
  // Script Properties rather than hard-coding it in the script.
  const apiKey = PropertiesService.getScriptProperties().getProperty('OMNICART_API_KEY');
  const day = new Date(Date.now() - 24 * 3600 * 1000);
  const start = Utilities.formatDate(day, 'UTC', "yyyy-MM-dd'T'00:00:00'Z'");
  const end = Utilities.formatDate(day, 'UTC', "yyyy-MM-dd'T'23:59:59'Z'");

  const resp = UrlFetchApp.fetch(
    `https://your-store.omnicart.cc/admin/metrics?startDate=${start}&endDate=${end}&comparison=none`,
    { headers: { Authorization: 'Basic ' + Utilities.base64Encode(apiKey + ':') } }
  );
  const cards = JSON.parse(resp.getContentText()).metrics.summary.cards;
  const get = id => (cards.find(c => c.id === id) || {}).value;

  SpreadsheetApp.getActive().getSheetByName('daily_kpis').appendRow([
    start.slice(0, 10),
    get('total_revenue'),
    get('total_orders'),
    get('average_order_value'),
    get('total_refunded'),
  ]);
}
```

**2. Connect Looker Studio** to the Sheet (Create → Data source → Google Sheets), set the date column's type to Date, and build your charts. Set money columns to Currency — values are already decimal dollars.

For order-level detail in Sheets, do the same with `GET /admin/orders` (one row per order) — but past a few tens of thousands of rows, move to Path B.

## Path B — Warehouse + Looker (production)

For real modeling (LookML), order-level exploration, and joins against ad-spend or other sources, land OmniCart data in a warehouse (BigQuery, Snowflake, Postgres, Redshift) and connect Looker to that.

**1. Build the sync job.** A script on any scheduler (cron, Cloud Functions, Airflow) that:

- Authenticates with a secret API key (`-u "sk_...:"` — see [Data Access Overview](/tutorials/overview)); if using JWTs instead, re-authenticate each run.
- Pulls `GET /admin/orders` with an `updated_at[$gte]=<cursor>` filter, `order=updated_at`, `limit=100`, paging by `offset` — full pattern in [Pulling Orders Data](/tutorials/orders-data).
- Requests the fields you'll model: `fields=id,display_id,status,email,customer_id,total,currency_code,created_at,updated_at,sales_channel_id,*items` (drop `*items` if you only need order-level grain).
- Upserts into the warehouse by `id`, then advances the cursor to the max `updated_at` seen.

**Suggested orders table schema:**

| Column | Type | Source |
|--------|------|--------|
| `order_id` | STRING (PK) | `id` |
| `display_id` | INTEGER | `display_id` |
| `status` | STRING | `status` |
| `email` | STRING | `email` |
| `customer_id` | STRING | `customer_id` |
| `total` | NUMERIC(12,2) | `total` — decimal dollars, load as-is |
| `currency_code` | STRING | `currency_code` |
| `sales_channel_id` | STRING | `sales_channel_id` |
| `created_at` | TIMESTAMP (UTC) | `created_at` |
| `updated_at` | TIMESTAMP (UTC) | `updated_at` |

Line items, if you need product-level grain, go to a child table (`order_id`, `sku`, `title`, `quantity`, `unit_price`) fed from `*items`.

**2. Backfill history.** For the initial load, either loop the same pull with `created_at` windows month by month, or use the async bulk CSV export (`POST /admin/orders/export`) and load the delivered file — details and its Eastern-calendar-day date semantics in [Pulling Orders Data](/tutorials/orders-data).

**3. Model in Looker.** Connect the warehouse, then define a view over the orders table. Typical starting measures:

```lookml
view: orders {
  dimension: order_id { primary_key: yes  sql: ${TABLE}.order_id ;; }
  dimension_group: created { type: time  timeframes: [date, week, month]  sql: ${TABLE}.created_at ;; }
  dimension: status { sql: ${TABLE}.status ;; }
  dimension: sales_channel_id { sql: ${TABLE}.sales_channel_id ;; }

  measure: total_orders { type: count }
  measure: total_revenue { type: sum  sql: ${TABLE}.total ;;  value_format_name: usd }
  measure: average_order_value { type: average  sql: ${TABLE}.total ;;  value_format_name: usd }
}
```

Filter `status` to exclude canceled orders in revenue measures to match the OmniCart dashboard's numbers.

## Reconciliation

Whichever path you use, validate the pipeline by comparing a fixed day against OmniCart's own numbers: `GET /admin/metrics` for that day (`total_revenue`, `total_orders` cards — see [Metrics & Reports API](/tutorials/metrics-data)). Small deltas usually mean timezone bucketing (your warehouse day vs. the query window) or canceled-order handling; a 100× delta means a cents conversion was applied to already-decimal totals.