|
Analytics Warehouse - Returns Analysis
|
|
Use the OAC MCP connector. Do not use a dataset.
Before running the analysis, confirm that both required subject areas are available: NetSuite - Order to Cash - Return Authorization and NetSuite - Sales Order. If either is unavailable, notify the user which subject area is missing and that it must be activated or made available in OAC before this analysis can run. Do not substitute a dataset or a different subject area without telling the user.
For the current calendar month:
1. From NetSuite - Order to Cash - Return Authorization, identify item lines created from Sales Orders using Return Authorization - Is Created From Sales Order = 'T'.
Exclude records where Return Authorization - Main Line or Return Authorization - Tax Line is not 'F'. Calculate Total Returns by Item Identifier using Line Amount in Analytics Currency from the Analytics Currency Amounts folder.
2. Calculate previous-calendar-month Total Returns using the same criteria.
3. Rank items by current-month Total Returns in Analytics Currency, descending, and retain the top 10.
4. From NetSuite - Sales Order, calculate Total Sales in Analytics Currency for those same Item Identifiers during the current calendar month. Use Transaction Lines - Analytics Currency Amounts and exclude records where Sales Order - Main Line, Sales Order - Tax Line, or Sales Order - Do Not Display Line is not 'F'.
Use Gregorian Calendar Year and Month Number for the monthly filters; do not use Time.Date. Return a table with Item, Total Sales in Analytics Currency, Total Returns in Analytics Currency, Previous Month Total Returns in Analytics Currency, Return Change in Analytics Currency, and Return Direction: Higher, Lower, or No change.
Use Item Identifier not Item Name to combine the subject-area results. Do not substitute a return amount for sales. If there are no sales-order-linked return lines in the current month, state that no qualifying returns were found and do not fabricate a top-10 table.
|
|
Analytics Warehouse - Inventory Snapshot
|
|
Use the OAC MCP connector. Do not use a dataset.
Before running the analysis, confirm that NetSuite - Inventory Snapshot is available. If it is unavailable, notify the user that this subject area must be activated or made available in OAC before the analysis can run. Do not substitute a different subject area without telling the user.
For the current calendar quarter, use Average On Hand Quantity at the Item Identifier and calendar-quarter level to find the 10 items with the highest average on-hand quantity.
When resolving snapshot dates, require a matching On Hand Quantity fact value and exclude sentinel or placeholder dates, including 4712-12-31 and 2099-12-31. Do not use a bare max of Snapshot Calendar Full Date or count(*) on the inventory snapshot fact table.
For those 10 items:
1. Retrieve daily On Hand Quantity using the Snapshot Calendar date.
2. Sort each item's daily snapshots chronologically.
3. Calculate a 20-day moving average of On Hand Quantity using mavg(On Hand Quantity, 20).
4. Determine the On Hand Trend by comparing the most recent complete 20-day moving average with the complete 20-day moving average from 20 snapshot days earlier: Up when the most recent moving average is higher, Down when it is lower, No change when it is equal, and Insufficient history when fewer than 40 daily snapshots are available.
Return a table with Item Identifier, Item, Calendar Quarter, Average On Hand Quantity, Latest 20-Day Moving Average, Prior 20-Day Moving Average, and On Hand Trend.
Rank the table by Average On Hand Quantity, descending. Do not use Item Name as the key for calculations; use Item Identifier.
|
|
Analytics Warehouse - Gross Margin by Customer
|
|
Use the OAC MCP connector. Do not use a dataset.
Use the subject area DW_NS_AA_APP_ACCOUNT_ANALYSIS_F only. Do not use NS Account Analysis Insights, NetSuite - Account Analysis Insights, NetSuite - Account Analysis Template, NetSuite - Account Analysis, or another similarly named subject area.
Before running the analysis, confirm that DW_NS_AA_APP_ACCOUNT_ANALYSIS_F is available and queryable. If it is unavailable, cannot be queried, or is not activated, notify the user that it must be made available in OAC. Do not substitute another subject area.
Calculate gross margin by customer for the current calendar quarter. If the user specifies a different date range, use that range instead.
1. Use Entity Internal Identifier as the customer grain and display Entity Full Name. Include only records where Entity - Is Customer = 'Y'; do not treat 'N', '~', or null as customers.
2. Use Account Type from the Account folder and Accounting Book Credit Amount and Accounting Book Debit Amount from Transaction Lines - Accounting Book Currency Amounts. Treat null amounts as zero. Use the semantic measures directly and let OAC aggregate them at the selected customer grain; do not add an explicit SQL GROUP BY.
3. For accounts where Account Type = Income, calculate Total Revenue as Accounting Book Credit Amount minus Accounting Book Debit Amount.
4. For accounts where Account Type = Cost of Goods Sold, calculate Total COGS as Accounting Book Debit Amount minus Accounting Book Credit Amount.
5. Calculate Gross Margin as Total Revenue minus Total COGS.
6. Before ranking customers, validate the same formulas at the portfolio level for the selected date range. If Total Revenue or Total COGS is negative, show the underlying debit and credit totals and notify the user of the sign anomaly; do not silently reverse the formulas.
7. Rank customers by Gross Margin descending and return the top 10.
Return Rank, Customer Internal ID, Customer, Total Revenue, Total COGS, and Gross Margin. State the date range used and the accounting-book currency. When defaulting to the current calendar quarter, state the quarter and its exact start and end dates.
|
|
Analytics Warehouse - Historical AR Analysis
|
|
Use the OAC MCP connector. Do not use a dataset.
Use NetSuite - Customer Invoice for current accounts-receivable balances, overdue amounts, overdue invoice counts, and days outstanding. Use NetSuite - Customer Invoice Snapshot for historical unpaid-balance analysis.
Before beginning, confirm that both subject areas are available and queryable. If either is unavailable, identify it by its exact name and notify the user that it must be activated or made available in OAC. Do not silently substitute another subject area or continue with an incomplete analysis.
Use Analytics Currency for every monetary value. Run separate queries against the two subject areas and combine the results outside OAC using Customer Internal Identifier. Do not mix the subject areas in one Logical SQL query, join on Customer Full Name, or reconstruct historical balances from the current subject area. Respect the subject areas' predefined aggregation rules.
Current AR analysis
Using NetSuite - Customer Invoice, calculate total current Unpaid Amount in Analytics Currency, Remaining Amount Overdue in Analytics Currency, Transaction.Total Number of Overdue Invoices, and AR Average Days Outstanding.
Use the predefined semantic measures directly and let OAC aggregate them at Customer Internal Identifier; do not add an explicit SQL GROUP BY.
Return the top 10 customers with Remaining Amount Overdue in Analytics Currency greater than zero. Apply this filter before ranking, rank by that amount descending, use Customer Internal Identifier as the stable key, and display Customer Full Name.
Historical AR analysis
Using NetSuite - Customer Invoice Snapshot:
1. Identify the latest available Snapshot Calendar Full Date.
2. Identify the last available snapshot on or before each of the five preceding completed calendar month-ends.
3. Retrieve the portfolio-level Unpaid Amount in Analytics Currency for those dates.
4. Retrieve the latest and prior-calendar-month-end snapshot unpaid balances for each current top-10 customer.
Calculate Historical Change as Latest Snapshot Unpaid minus Prior Month-End Snapshot Unpaid. Label Historical Direction as Increase, Decrease, or No change. Calculate Current-vs-Latest Snapshot Difference as Current Unpaid minus Latest Snapshot Unpaid.
Do not describe Customer Invoice - Days Open as days overdue. Days open represents invoice age, not due-date-based delinquency.
First provide a concise executive summary of current unpaid, current overdue, overdue invoice count, AR Average Days Outstanding, and latest historical change.
Then provide:
1. A historical portfolio table with Snapshot Date, Total Unpaid in Analytics Currency, Change from Previous Available Month-End, and Direction.
2. A current-priority table with Rank, Customer Internal Identifier, Customer, Current Unpaid in Analytics Currency, Current Overdue in Analytics Currency, Overdue Invoice Count, AR Average Days Outstanding, Latest Snapshot Date, Latest Snapshot Unpaid in Analytics Currency, Prior Month-End Snapshot Date, Prior Month-End Unpaid in Analytics Currency, Historical Change in Analytics Currency, Historical Direction, and Current-vs-Latest Snapshot Difference in Analytics Currency.
State the current analysis run date and every snapshot date used. If sufficient historical snapshots are unavailable, identify the missing periods rather than estimating them. Treat a missing customer snapshot balance as zero only after confirming that the selected snapshot date exists and that the customer has no open invoice snapshot record for that date; otherwise report it as unavailable.
|
|
Analytics Warehouse - Historical AP Analysis
|
|
Use the OAC MCP connector. Do not use a dataset.
Use NetSuite - Vendor Bill for current accounts-payable balances, overdue amounts, overdue bill counts, and days outstanding. Use NetSuite - Vendor Bill Snapshot for historical unpaid-balance analysis.
Before beginning, confirm that both subject areas are available and queryable. If either is unavailable, identify it by its exact name and notify the user that it must be activated or made available in OAC. Do not silently substitute another subject area or continue with an incomplete analysis.
Use Analytics Currency for every monetary value. Run separate queries against the two subject areas and combine the results outside OAC using Vendor Internal Identifier. Do not mix the subject areas in one Logical SQL query, join on Vendor Full Name, or reconstruct historical balances from the current subject area. Respect the subject areas' predefined aggregation rules.
Current AP analysis
Using NetSuite - Vendor Bill, calculate total current Unpaid Amount in Analytics Currency, Overdue Amount in Analytics Currency, Total Number of Overdue Vendor Bills, and AP Average Days Outstanding.
Return the top 10 vendors with Overdue Amount in Analytics Currency greater than zero. Apply this filter before ranking so that null overdue amounts do not appear ahead of valid amounts. Rank by overdue amount descending, use Vendor Internal Identifier as the stable key, and display Vendor Full Name.
Historical AP analysis
Using NetSuite - Vendor Bill Snapshot:
1. Identify the latest available Snapshot Calendar Full Date.
2. Identify the last available snapshot on or before each of the five preceding completed calendar month-ends.
3. Retrieve the portfolio-level Unpaid Amount in Analytics Currency for those dates.
4. Retrieve the latest and prior-calendar-month-end snapshot unpaid balances for each current top-10 vendor.
Calculate Historical Change as Latest Snapshot Unpaid minus Prior Month-End Snapshot Unpaid. Label Historical Direction as Increase, Decrease, or No change. Calculate Current-vs-Latest Snapshot Difference as Current Unpaid minus Latest Snapshot Unpaid.
Do not describe Vendor Bill - Days Open as days overdue. Days open represents bill age, not due-date-based delinquency.
First provide a concise executive summary of current unpaid, current overdue, overdue vendor bill count, AP Average Days Outstanding, and latest historical change.
Then provide:
1. A historical portfolio table with Snapshot Date, Total Unpaid in Analytics Currency, Change from Previous Available Month-End, and Direction.
2. A current-priority table with Rank, Vendor Internal Identifier, Vendor, Current Unpaid in Analytics Currency, Current Overdue in Analytics Currency, Overdue Vendor Bill Count, AP Average Days Outstanding, Latest Snapshot Date, Latest Snapshot Unpaid in Analytics Currency, Prior Month-End Snapshot Date, Prior Month-End Unpaid in Analytics Currency, Historical Change in Analytics Currency, Historical Direction, and Current-vs-Latest Snapshot Difference in Analytics Currency.
State the current analysis run date and every snapshot date used. If sufficient historical snapshots are unavailable, identify the missing periods rather than estimating them. Treat a missing vendor snapshot balance as zero only after confirming that the selected snapshot date exists and that the vendor has no open bill snapshot record for that date; otherwise report it as unavailable.
|