Turning years of invoices into a clear picture of who's buying, who's slowing down and who's lapsed

At a glance

The project in 30 seconds

Client: PetScript
Industry: Veterinary supply
Customers: Veterinary clinics & hospitals
When: March 2026
My role: Data sync & Zoho Analytics reporting
Source data: Several years of Zoho Invoice history

Zoho InvoiceZoho AnalyticsSQL query tablesPivot reports

The situation

Where PetScript started

PetScript sells to veterinary clinics and animal hospitals. Every order was invoiced in Zoho Invoice, so the history was all there.

What was missing was visibility. Which clinics are the most valuable? Which products drive revenue? How does this year compare with last? And, most importantly, which clinics have quietly stopped ordering?

The challenge

What had to be solved

The reporting needed to:

Use Zoho Invoice as the source of truth, with no manual exports.

Spot lapsed customers automatically, before they're lost for good.

Show revenue and product trends clearly enough to act on.

The approach

How I tackled it, step by step

1
Connect the data
Synced customers, invoices, line items and products from Zoho Invoice into Zoho Analytics.
2
Build the reports
Revenue by month across years, top products, and top customers by lifetime value.
3
Score every customer
SQL query tables that work out days since last order and assign a status.
4
Plan the dashboards
A full map of overview, customer, product and order KPIs.

What I built

Inside the system

6 real screenshots from the build. Click any image to enlarge.

01Who's active, who's at risk, who's lapsed

A SQL query table finds each customer's last purchase and how many days ago it was. Customers who ordered within 90 days are active, 90–180 days are at risk, and anyone older is lapsed. That turns a long invoice history into a ready-made win-back list.

Customer activity pie chart — Customer activity at a glance

SQL query assigning active, at risk or lapsed status — The query behind the status

02Revenue, products and best customers

Reports show revenue by month compared across years, the products that bring in the most revenue, and the customers with the highest lifetime value, average order and order count.

03A dashboard plan to grow into

Every metric was mapped before building: overview KPIs, customers (lifetime value, churn, single-order customers), products (best sellers, declining products, items bought together) and orders (frequency, average order value, timing).

Planned dashboard sections — The dashboard plan

The results

What changed for PetScript

Every clinic is labelled active, at risk or lapsed from its last order date.

Revenue trends, best products and most valuable customers are visible without spreadsheets.

The data stays connected to Zoho Invoice, so the reports reflect real invoices.

Key decisions

The calls that made it work

Good systems come from good decisions. Here's what I chose, and why.

Simple, explainable thresholds

90 and 180 days are easy for a sales team to understand and act on, and easy to change later.

Plan the metrics before the charts

Mapping every KPI first keeps the dashboards focused on decisions, not decoration.

Is this for you?

A similar system could help if…

You invoice in Zoho Invoice or Zoho Books and want to understand your customers.
You suspect customers are drifting away without anyone noticing.
You want reports your sales team can act on.

Customer names are blurred. This case study covers the data sync and Zoho Analytics reporting. Every screenshot on this page comes from the real project; nothing is a mock-up.