Skip to content

Saar Profitability Sheet

Type: Google Sheets to BigQuery Connection (Connected Sheets) Refresh Rate: Scheduled (Every Midnight)

1. Overview

The Saar Profitability Sheet is a Google Sheet that connects directly to the Infinity Plus BigQuery dataset. It provides live profitability analysis for all orders, automatically refreshing every night at midnight.

The sheet pulls merged data (SCD Type 2 history) to accurately calculate the gross profit, quantities, and totals for each order, and then visualizes this data through monthly pivot tables.

2. BigQuery SQL Query

This is the exact custom query used in the Google Sheets BigQuery connection to pull the flat data:

WITH orders_with_key AS (
  SELECT
    o.*,
    COALESCE(
      (SELECT MAX(m2.IN_Z)
       FROM `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_MARKETING_TEAM_MEMBER_MERGED` m2
       WHERE m2.USER_ID = o.MARKETING_PERSON_C
         AND DATE(m2.IN_Z) <= o.ORDER_DATE),
      (SELECT MIN(m3.IN_Z)
       FROM `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_MARKETING_TEAM_MEMBER_MERGED` m3
       WHERE m3.USER_ID = o.MARKETING_PERSON_C)
    ) AS matched_in_z
  FROM
    `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_ORDER_DETAILS_MERGED` o
  WHERE
    o.OUT_Z IS NULL
    AND o.MARG_ORDER_NO_C IS NOT NULL
)

SELECT
  o.MARG_ORDER_NO_C AS Sale_Order_No,
  o.SALE_O_TS,
  o.CLIENT_ID_I,
  c.CLIENT_NAME_C,
  p.PRODUCT_NAME_C,
  o.BRAND_C,
  o.COSTING_I,
  o.RATE_I,
  o.QUANTITY_I,
  o.ORDER_NO_C,
  o.PS_QUANTITY_I,
  o.IN_Z,
  o.OUT_Z,
  o.MARKETING_PERSON_C,
  c.CLIENT_TYPE_C,
  m.TEAM_NAME_C,
  FORMAT_DATE('%d-%b-%Y', o.SALE_O_ENTRY_DATE) AS formatted_date,
  (COALESCE(o.RATE_I, 0) - COALESCE(o.COSTING_I, 0)) * (COALESCE(o.PS_QUANTITY_I, 0) + COALESCE(o.QUANTITY_I, 0)) AS TOTAL_DIFF,
  (COALESCE(o.RATE_I, 0) - COALESCE(o.COSTING_I, 0)) AS Diff,
  COALESCE(o.PS_QUANTITY_I, 0) + COALESCE(o.QUANTITY_I, 0) AS QTY,
  (COALESCE(o.RATE_I, 0)) * (COALESCE(o.PS_QUANTITY_I, 0) + COALESCE(o.QUANTITY_I, 0)) AS TOTAL_AMOUNT,
  REPLACE(o.MARKETING_PERSON_C, '_', ' ') AS SALES_PERSON,
  REPLACE(c.CLIENT_TYPE_C, '_', ' ') AS ORDER_TYPE
FROM
  orders_with_key AS o
LEFT JOIN
  `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_CLIENT_MERGED` AS c
ON
  o.CLIENT_ID_I = c.CLIENT_ID_I
  AND c.OUT_Z IS NULL
LEFT JOIN
  `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_MARKETING_TEAM_MEMBER_MERGED` AS m
ON
  o.MARKETING_PERSON_C = m.USER_ID
  AND m.IN_Z = o.matched_in_z
LEFT JOIN
  `myfirsttestproject-456102`.`INFINITY_DATASET`.`TBL_PRODUCT_MERGED` AS p
ON
  o.COMPOSITION_C = p.PRODUCT_ID_I
  AND p.OUT_Z IS NULL
ORDER BY
  Sale_Order_No;

Key Query Logic:

  • Historical Matching (matched_in_z): The CTE (orders_with_key) uses a subquery to find the correct IN_Z timestamp from the marketing team merged table. This ensures the order is attributed to the team the sales person belonged to at the time the order was placed, rather than their current team.
  • Filtering: Only pulls active orders (o.OUT_Z IS NULL) that have successfully synced to Marg (MARG_ORDER_NO_C IS NOT NULL).
  • Calculations:
  • TOTAL_DIFF: Gross Profit (Rate - Costing) * Total Quantity.
  • Diff: Margin per unit.
  • QTY: Total Quantity (Regular Quantity + Physician Sample Quantity).
  • TOTAL_AMOUNT: Total Sales Value (Rate * Total Quantity).
  • Formatting: Formats the order entry date to DD-MMM-YYYY (e.g., 15-Aug-2026) for easy filtering in Google Sheets, and cleans up underscores in strings.

3. Google Sheet Structure & Layout

The Google Sheet is organized into Monthly Tabs (e.g., Aug 26 infinity).

Every pivot table in a monthly tab is globally filtered where the formatted_date ends with the specific month and year (e.g., ends with Aug-2026).

Each monthly tab contains the following 4 sections:

Table 1: Order Type / Client Type Wise

Summarizes profitability categorized by ORDER_TYPE (Client Type). - Shows: Gross profit, average cost price (CP), average selling price (SP), total quantity, and overall margins.

Table 2: Team Wise

Summarizes performance by Marketing Team. - Grouped by: TEAM_NAME_C - Shows: Which teams are driving the most volume and highest profitability for the month.

Table 3: Sales Person Wise

Summarizes individual performance. - Grouped by: SALES_PERSON - Shows: Individual contributions to sales value, quantity, and gross profit.

Table 4: All Orders Detail

A flat data table at the end of the sheet. - Shows: Every individual order row for that specific month with full line-item details, acting as the raw ledger backing up the three pivot tables above it.

4. Automated Formatting (Apps Script)

Google Sheets pivot tables natively lack the ability to format different sub-total levels distinctly. To make these dense tables readable, a custom Apps Script runs automatically on the sheet to apply hierarchical color-coding and group-based zebra striping.

What the script does:

  • Scans the active sheet (only if the sheet name ends with "infinity").
  • Level 1 (Grand Total): Formats row in Pastel Purple.
  • Level 2 (Team Total): Formats row in Pastel Blue.
  • Level 3 (Person Total): Formats row in Pastel Green.
  • Level 4 (Data Rows): Applies color chunking (group-based zebra striping). Instead of striping every single row (white/gray/white/gray), it stripes by person. All rows belonging to one salesperson are white, and all rows belonging to the next salesperson are light gray. This creates distinct visual blocks indicating where a person's orders begin and end.
  • Protects Custom Headers: Only modifies rows with colors it explicitly manages, leaving custom header colors untouched.

The Apps Script Code:

/**
 * Scans the currently active sheet (if it ends with "infinity") and applies hierarchical formatting.
 * Differentiates between Grand Totals, Team Totals, and Sales Person Totals 
 * to create a clear visual depth. Also applies zebra striping to data rows.
 */
function formatTotalRows() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Exit if not an infinity sheet
  if (!/infinity$/i.test(sheet.getName())) return;

  // Centralized configuration for hierarchical design (Pastel Palette)
  const GRAND_TOTAL = { bg: "#b4a7d6", font: "#000000", height: 30, size: 12 }; // Pastel Purple
  const TEAM_TOTAL = { bg: "#9fc5e8", font: "#000000", height: 26, size: 12 };  // Pastel Blue
  const PERSON_TOTAL = { bg: "#d9ead3", font: "#000000", height: 24, size: 11 }; // Pastel Green

  // Zebra striping colors for better readability on wide data rows
  const DATA_ROW_1 = { bg: "#ffffff", font: "#000000", height: 21, size: 10 }; // White
  const DATA_ROW_2 = { bg: "#e9ecef", font: "#000000", height: 21, size: 10 }; // Distinct light gray

  const dynamicColors = [GRAND_TOTAL.bg, TEAM_TOTAL.bg, PERSON_TOTAL.bg, DATA_ROW_1.bg, DATA_ROW_2.bg].map(c => c.toLowerCase());

  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const backgrounds = dataRange.getBackgrounds(); 
  const numCols = values[0].length;

  let dataRowCounter = 0;
  let groupCounter = 0; // Tracks groups of data for chunk-based zebra striping

  for (let i = 0; i < values.length; i++) {
    const rowNum = i + 1;
    const rowText = values[i].join(" ").toLowerCase();

    if (!rowText.trim()) continue; 

    const range = sheet.getRange(rowNum, 1, 1, numCols);
    const currentBg = backgrounds[i][0].toLowerCase(); 

    if (rowText.includes("grand total")) {
      range.setBackground(GRAND_TOTAL.bg).setFontColor(GRAND_TOTAL.font).setFontWeight("bold").setFontSize(GRAND_TOTAL.size);
      sheet.setRowHeight(rowNum, GRAND_TOTAL.height);
      dataRowCounter = 0; 
      groupCounter = 0; 

    } else if (rowText.includes("team") && rowText.includes("total")) {
      range.setBackground(TEAM_TOTAL.bg).setFontColor(TEAM_TOTAL.font).setFontWeight("bold").setFontSize(TEAM_TOTAL.size);
      sheet.setRowHeight(rowNum, TEAM_TOTAL.height);
      dataRowCounter = 0; 
      groupCounter = 0;

    } else if (/\btotal\b/.test(rowText)) {
      range.setBackground(PERSON_TOTAL.bg).setFontColor(PERSON_TOTAL.font).setFontWeight("bold").setFontSize(PERSON_TOTAL.size);
      sheet.setRowHeight(rowNum, PERSON_TOTAL.height);
      dataRowCounter = 0; 
      groupCounter++; // Increment group counter when we hit a Person's Total row to flip background for next person

    } else if (dynamicColors.includes(currentBg)) {
      // Normal Data Rows (Zebra Striped by Group)
      const normalStyle = (groupCounter % 2 === 0) ? DATA_ROW_1 : DATA_ROW_2;

      range.setBackground(normalStyle.bg).setFontColor(normalStyle.font).setFontWeight("normal").setFontSize(normalStyle.size);
      sheet.setRowHeight(rowNum, normalStyle.height);
      dataRowCounter++; 
    } 
  }
}