Customer insights AI: explain changes in your own data

Customer insights AI is useful when it explains checked changes in customer revenue, repeat orders and product breadth from your own sales file. Define the question and calculate those measures before asking for an interpretation. A fluent explanation cannot repair a customer total that does not match the sales ledger.

For a sales manager, a useful question is: "Why did customer revenue fall between two comparable quarters, and which account changes deserve review?" The file can establish where the decline occurred and what buying patterns changed. Establishing the business cause takes evidence beyond the arithmetic.

Define the comparison before uploading the file

Choose complete periods with the same reporting rules. This example compares the second and third quarters of a US industrial distributor's fiscal year, with all amounts in USD. It uses posted net revenue, including credits, and treats a purchase as an order containing a qualifying purchase line.

Keep stable customer IDs even when display names change. The working sales table, named Sales, needs these fields:

Exclude blank order IDs and family labels from the qualifying purchase population before counting. A credit still changes revenue even if it is excluded from purchase counts. Record the export date and retain the source rows so an account owner can inspect an unexpected movement.

Apply your organization's data-sharing requirements before bringing customer files to any service. The customer-data upload checklist covers the questions to settle first.

Calculate the measures from transaction rows

Build a customer summary with the union of customer IDs from both periods. Omitting accounts with no current sales would remove the very losses you want to understand.

In the summary, column A holds Customer ID. Columns B and C hold prior and current revenue. With the ID in A2, current revenue is:

=SUMIFS(Sales[Net revenue],Sales[Customer ID],$A2,Sales[Period],"Current")

Replace "Current" with "Prior" for the earlier column. Microsoft documents SUMIFS as summing values that meet multiple criteria, which here are customer and period.

Do not count sales lines as orders. In Excel 365, count distinct qualifying current orders with:

=IF(COUNTIFS(Sales[Customer ID],$A2,Sales[Period],"Current",Sales[Purchase],1)=0,0,ROWS(UNIQUE(FILTER(Sales[Order ID],(Sales[Customer ID]=$A2)*(Sales[Period]="Current")*(Sales[Purchase]=1)))))

The guard uses COUNTIFS to test matching rows. FILTER selects those rows, and UNIQUE returns their distinct identifiers. For product breadth, use the same formula with Sales[Family] replacing Sales[Order ID]; it counts families purchased, regardless of the number of lines.

Covirage is not affiliated with Microsoft.

Work the customer revenue bridge

Here is the resulting summary. Counts are whole numbers; percentages below are rounded to one decimal place.

With this table in A1:G5, customer change in H2 is =C2-B2, filled down. The bridge is:

The proof is starting revenue plus the movements: $24,000 minus $2,000 equals $22,000. In Excel:

=SUM(B2:B5)+SUM(H2:H5)-SUM(C2:C5)

The result must be zero. Separately, tie each period's revenue to the agreed sales-export control total; an internally consistent table can still omit source rows. Total revenue change is =SUM(H2:H5)/SUM(B2:B5), or -8.3%.

Explain what changed without inventing the cause

All active customers placed multiple orders in each period. Prior repeat-buyer rate is three qualifying repeat buyers divided by three active customers, or 100.0%; current rate is also three divided by three, or 100.0%. In the summary layout, current repeat-buyer rate is:

=COUNTIFS(E2:E5,">=2")/COUNTIFS(E2:E5,">0")

Yet only Aster and Birch bought in both periods. Customer retention is two divided by the three prior buyers, or 66.7%. Retaining frequent ordering among current buyers did not prevent the revenue decline.

The existing-customer cohort generated $18,000 in the current quarter: $12,000 plus $6,000 plus zero. Against its prior $24,000, that is 75.0% revenue retention. For a recurring-contract business, use the separately defined net revenue retention method.

Aster's family count increased, while Birch's decreased. That supports account review, but it does not prove cross-selling caused Aster's growth or that dissatisfaction caused Birch's decline. Ask the account owners to check order timing, product availability and contract scope. If the next step is organizing accounts into explicit groups, use AI customer segmentation.

Where customer explanations go wrong

• Changing the population. Removing inactive accounts makes revenue retention and customer retention look stronger.
• Counting lines as purchases. A large order with many product lines can look like frequent buying unless order IDs are deduplicated.
• Adding customer breadth counts. Families overlap across customers. Their sum is not the number of distinct families sold by the company.
• Calling an observation a cause. Lower revenue and narrower buying occurred together at Birch; the export does not establish which caused which.
• Letting the display define the metric. Set the denominator before presenting the answer in an AI dashboard.

Explain your own customer movements in Covirage

Bring a sales export with customer IDs, periods, order IDs, product families and net revenue; the AI analytics overview explains the broader approach. Covirage maps the columns, its tools compute and check the customer comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See sales insights for the supporting business use case.