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:

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); 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.
• 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:

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.

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

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). 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.