Why a CPM calculator alone is not enough
CPM is the price of reach, not the price of a result. Cheap impressions on Instagram or Telegram can produce no leads if nobody clicks, while expensive ones can pay back if they reach exactly the right people. Judging an ad by CPM alone is like judging a shop by its rent without looking at its sales.
The metrics form a chain. Impression price and click-through rate set the price of a click; the price of a click and the conversion rate of your site or bot set the price of a lead. A sheet that shows the whole chain at once tells you which link is leaking money.
Inputs and formulas
You need five numbers for the same period: spend in soums, impressions, clicks, conversions (leads, calls, Click or Payme payments, bot starts) and, if you know it, revenue. Take spend, impressions and clicks from the ad account; take conversions and revenue from your CRM or order records.
Format CTR and conversion rate as percentages and wrap each formula in a check for empty values, otherwise rows with no clicks will show a divide-by-zero error. Calculate the totals row from column sums, not as an average of rows: the average CTR of several campaigns is not the CTR of their combined traffic.
- CPM = spend ÷ impressions × 1000
- CPC = spend ÷ clicks
- CTR = clicks ÷ impressions
- Conversion rate = conversions ÷ clicks
- CPA = spend ÷ conversions
- ROAS = revenue ÷ spend
Finding the cause when leads get expensive
Two relationships are worth remembering. Cost per click equals CPM divided by CTR and by a thousand. Cost per lead equals cost per click divided by conversion rate. That gives you a simple diagnostic order when CPA rises.
This way you repair one specific link instead of changing everything at once. If clicks are cheap but leads are missing, the ad platform is probably not the problem.
- CPM went up: the auction is getting pricier because of season, competition or a very narrow audience.
- CTR dropped: the creative is worn out or the offer does not catch attention.
- Conversion fell while CTR is fine: the problem is after the click, in the site, the form or how fast a manager replies.
Working backwards: a budget for your target
The same sheet works in reverse. Multiply the number of leads you want by your expected CPA to get a budget. Divide a budget by expected CPM and multiply by a thousand to forecast impressions; multiply impressions by CTR for clicks, and clicks by conversion rate for leads.
Take expected values from your own past campaigns. If you are just starting, run a short test and use its results as a baseline. Figures from other people's reports do not transfer: a furniture store in Tashkent and an online course in Samarkand behave very differently.
Add a what-if block. Change one input at a time, such as CTR, conversion rate or CPM, and watch CPA move. It quickly shows whether it is better to rework creatives, improve the website or raise the budget.
From a lead to profit
CPA still does not tell you whether advertising pays off. Add the share of leads that become sales, the average order value and your margin. Profit is then revenue multiplied by margin, minus ad spend.
A cheap lead that never turns into a sale costs more than a pricier lead that does. That is why you should reconcile conversions with your CRM and not only with the ad account: a pixel or a bot may count actions your sales manager would not call a sale.
Common mistakes in the sheet
Most discrepancies in reports come from the spreadsheet, not the advertising. Check yours against this list.
- Averaging percentages across rows instead of calculating from totals.
- Mixing periods, such as monthly spend with weekly leads.
- Using ad account data without checking it against the CRM.
- Putting campaigns with different goals in one row; reach and leads cannot be compared.
- Using planned values from outside sources.
- Editing formulas by hand instead of protecting the calculated cells.
Setting it up for a team, and when you need end-to-end analytics
Split the file into three tabs: data entry, calculations, and plan versus actual. Colour the input cells, protect the formulas and add a short note on where each number comes from and when to update it. Then both the owner and a new employee can use it. It is also a fair way to check a contractor's report: enter their spend, impressions, clicks and conversions and compare the metrics. A mismatch is a reason to ask about attribution or the period, not to start an argument.
Once you run several channels, such as Instagram, Google, Telegram, OLX and a website, a manual sheet starts to lag. At that point revenue from the CRM is linked to each source automatically, which is end-to-end analytics. markdev sets up this kind of reporting for businesses in Uzbekistan. If you want to talk through your own case, get in touch.
FAQ
How do I calculate CPM by hand?
Divide spend by the number of impressions and multiply by a thousand. Spend and impressions must come from the same period and the same campaign or platform.
What is the difference between CPM and CPC?
CPM is the price of a thousand impressions and CPC is the price of one click. CPC depends on CPM and CTR: at the same CPM, a higher CTR makes each click cheaper.
What is a good CPM?
There is no universal value. It depends on the niche, season, audience and platform. Compare against your own campaign history and against what a lead and a sale cost you.
Is a spreadsheet enough, or do I need end-to-end analytics?
For one or two channels a spreadsheet is usually enough. When you have many sources and need revenue by each one, end-to-end analytics fed by CRM data is more practical.
Let’s look at your task for free
Leave your contact — in a 15-minute call we’ll show where leads get lost and what to fix first.