SFCC Commerce Intelligence Platform Analytics Client

A TypeScript/Node.js client library and CLI tool for querying Salesforce Commerce Cloud (SFCC) Commerce Intelligence Platform (CIP) analytics data through the use of the JDBC protocol. This client abstracts the underlying Avatica protobuf protocol and provides both programmatic and command-line interfaces for accessing your commerce analytics data.

See https://developer.salesforce.com/docs/commerce/b2c-commerce/guide/jdbc_intro.html for the details of the JDBC driver used with Java applications as well as the business object and schema reference.

• SFCC Commerce Intelligence Platform Analytics Client
  • Installation
    • Global CLI Installation
  • CLI Usage
    • Configuration
      • Environment Variables
      • Command-line Options
    • Commands
      • sql - Execute custom SQL queries
      • query - Execute predefined business object queries
    • Basic Query
    • With Date Placeholders
    • Different Output Formats
    • Using Heredocs (Multi-line SQL)
    • From Files
  • API Usage
    • Low Level Client
    • Type-Centric Parameterized Queries
    • High Level Business Use Case Queries
  • Examples
  • Development Setup
    • Prerequisites
    • Getting Started
  • API Documentation
    • CIPClient
      • Constructor
      • Methods
  • Todo
  • License
    • Support

Installation

npm install sfcc-cip-analytics-client

Global CLI Installation

npm install -g sfcc-cip-analytics-client
# Now you can use: cip-query "SELECT * FROM table"

CLI Usage

Configuration

The CLI can be configured using environment variables or command-line options. Your Account Manager API client must have the SALESFORCE_COMMERCE_API:[instance] role and tenant filter.

Environment Variables

export SFCC_CLIENT_ID="your-client-id"
export SFCC_CLIENT_SECRET="your-client-secret"
export SFCC_CIP_INSTANCE="your-instance-name" # example abcd_prd

# Optional: Enable debug logging
export SFCC_DEBUG=true

Command-line Options

You can also provide credentials directly via CLI options (these override environment variables):

cip-query sql --client-id "your-client-id" \
              --client-secret "your-client-secret" \
              --instance "your-instance-name" \
              "SELECT * FROM ccdw_aggr_ocapi_request LIMIT 10"

Commands

The CLI supports two main commands:

sql - Execute custom SQL queries

cip-query sql "SELECT * FROM ccdw_aggr_ocapi_request LIMIT 10"

query - Execute predefined business object queries

cip-query query --list
cip-query query --name ocapi-requests --from "2024-01-01" --to "2024-01-31"

Basic Query

cip-query sql "SELECT * FROM ccdw_aggr_ocapi_request LIMIT 10"

With Date Placeholders

cip-query sql --from "2024-07-01" --to "2024-07-31" \
  "SELECT * FROM ccdw_aggr_ocapi_request WHERE request_date >= <FROM> AND request_date <= <TO>"

Different Output Formats

# JSON output
cip-query sql --format json "SELECT api_name, COUNT(*) as count FROM ccdw_aggr_ocapi_request GROUP BY api_name"

# CSV output for spreadsheet analysis
cip-query sql --format csv --from "last week" \
  "SELECT * FROM ccdw_aggr_ocapi_request WHERE request_date >= <FROM>"

Using Heredocs (Multi-line SQL)

cip-query sql --format json --from "2024-01-01" --to "2024-01-31" <<SQL
  SELECT 
    api_name,
    "method",
    AVG(response_time) as avg_response_time,
    COUNT(*) as request_count
  FROM ccdw_aggr_ocapi_request 
  WHERE request_date >= <FROM> 
    AND request_date <= <TO>
  GROUP BY api_name, "method"
  ORDER BY request_count DESC
SQL

From Files

# Save complex queries in .sql files
cip-query sql --format csv < my-analytics-query.sql

API Usage

Low Level Client

import { CIPClient } from 'sfcc-cip-analytics-client';

const client = new CIPClient(
  process.env.SFCC_CLIENT_ID!,
  process.env.SFCC_CLIENT_SECRET!,
  process.env.SFCC_CIP_INSTANCE!
);

async function queryData() {
  // direct statement execution
  try {
    await client.openConnection();
    const statementId = await client.createStatement();
    
    const result = await client.execute(
      statementId, 
      "SELECT * FROM ccdw_aggr_ocapi_request LIMIT 10"
    );
    
    // see example/basic.ts for more details on result structure
    console.table(result);
    
    await client.closeStatement(statementId);
  } finally {
    await client.closeConnection();
  }
}

queryData();

Type-Centric Parameterized Queries

import { CIPClient, executeParameterizedQuery } from 'sfcc-cip-analytics-client';

interface OCAPISummary {
  request_date: string;
  site_id: string;
  api_resource: string;
  num_requests: number;
}

try {
  await client.openConnection();
  
  const query = executeParameterizedQuery<OCAPISummary>(
    client,
    `SELECT request_date,site_id,api_resource,SUM(num_requests) as requests FROM
      ccdw_aggr_ocapi_request
    WHERE
      request_date >= '2024-01-01'
    AND request_date <= '2024-01-31'
    GROUP BY request_date,site_id,api_resource`,
    [],
    100 // batch size
  );
  
  for await (const batch of query) {
    console.log(`Processed ${batch.length} ocapi requests`);
    for (const record of batch) {
      // Each record is typed as OCAPISummary
      console.log(record.api_resource);
    }
  }
} finally {
  await client.closeConnection();
}


queryData();

High Level Business Use Case Queries

The client provides specialized query functions for common business analytics use cases. These return simple arrays of plain old JavaScript objects, making it easy to work with the data.

For complete documentation of all available business queries, see Business Queries Reference.

import { CIPClient, querySalesAnalytics } from 'sfcc-cip-analytics-client';

const client = new CIPClient(clientId, clientSecret, instance);

async function analyzeSalesData() {
  await client.openConnection();
  
  const query = querySalesAnalytics(
    client,
    { siteId: 'Sites-RefArch-Site', startDate: new Date('2024-01-01'), endDate: new Date('2024-01-31') },
    100 // batch size
  );
  
  for await (const batch of query) {
    console.log(`Processed ${batch.length} daily sales metrics`);
    // Each record has: date, std_revenue, orders, std_aov, units, aos
  }
  
  await client.closeConnection();
}

Examples

See the examples/ directory for complete working examples:

• examples/basic.ts - Basic usage with result pagination
• examples/ocapi.ts - Using data helper functions for OCAPI analytics

Development Setup

Prerequisites

• Node.js 18+ or Bun
• SFCC Commerce Cloud production access with CIP Analytics enabled

Getting Started

1. Clone and install

   git clone <repository-url>
   # ...
   npm install

2. Build the project

   npm run build

3. Run linting

   npm run lint

API Documentation

CIPClient

Main client class for interacting with CIP.

Constructor

new CIPClient(clientId: string, clientSecret: string, instance: string, options?: CIPClientOptions)

Methods

• openConnection(info?: IConnectionProperties): Promise<void> - Open database connection
• closeConnection(): Promise<void> - Close database connection
• createStatement(): Promise<number> - Create a new SQL statement
• closeStatement(statementId: number): Promise<void> - Close a statement
• execute(statementId: number, sql: string, maxRowCount?: number): Promise<NormalizedExecuteResponse> - Execute SQL query
• fetch(statementId: number, offset: number, fetchMaxRowCount: number): Promise<NormalizedFetchResponse> - Fetch more results
• prepare(sql: string, maxRowsTotal?: number): Promise<IPrepareResponse> - Prepare a SQL statement with parameter placeholders
• executeWithParameters(statementHandle: IStatementHandle, parameters?: any[], firstFrameMaxSize?: number): Promise<IExecuteResponse> - Execute a prepared statement with parameters
• prepareAndExecuteWithParameters(sql: string, parameters?: any[], firstFrameMaxSize?: number): Promise<IExecuteResponse> - Prepare and execute a statement with parameters in one call

Todo

• [ ] support proxy / fetch replacement (for browser use and advanced proxy scenarios)
• [ ] static build of protobuf for web use

License

Licensed under the current NDA and licensing agreement in place with your organization. (This is explicitly not open source licensing.)

Support

This project should not be treated as Salesforce Product. It is a tool B2C instances. Customers and partners implement this at-will with no expectation of roadmap, technical support, defect resolution, production-style SLAs.

This project is maintained by the Salesforce Community. Salesforce Commerce Cloud or Salesforce Platform Technical Support do not support this project or its setup.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED.

For feature requests or bugs, please open a GitHub issue.