Hospital marketing KPI dashboard: free Google Sheets template

The hospital marketing KPIs that matter follow the patient funnel: impressions, clicks, qualified enquiries, appointments booked, honoured appointments, treated patients and revenue. The most useful ratios are enquiry-to-appointment rate, show rate, cost per honoured appointment and cost per treated patient, supported by response time, reviews and Business Profile actions. Review them weekly for operations, monthly by channel and service line, and quarterly at board level.

Most hospital marketing reports answer the wrong question. They show impressions, clicks, followers and leads, because those numbers are easy to pull from ad platforms. The questions a hospital owner or CEO actually asks are different: how many patients did marketing bring in, what did each one cost, and which service lines and channels are worth more money next quarter?

This article is part of the healthcare digital growth series, best read in order from the pillar.

A good hospital marketing KPI dashboard answers those questions in one place. This guide explains which KPIs matter, how to define and calculate each one, what belongs in a weekly view versus a monthly or board view, where the data comes from, how to build it in Google Sheets or Looker Studio, how to set targets, and the data problems you will run into. A free Google Sheets and Excel template is included.

What is in the free hospital marketing dashboard template?

Download the hospital marketing KPI dashboard template (works in Google Sheets and Excel). It has eight tabs:

1. Instructions: how to fill it in, definitions of every KPI, and the order to work in each month.
2. Settings and targets: your units, service lines, channels and monthly targets, which feed the dropdowns and the red-amber-green colouring.
3. Inputs, monthly by channel: one row per month, unit, service line and channel, with spend, impressions, clicks, enquiries, appointments, honoured appointments, treated patients and revenue. Ratios calculate automatically.
4. Inputs, local and organic: Google Business Profile actions, reviews, organic sessions and doctor page views by location.
5. Funnel: pick a month and a unit; see every stage from impressions to revenue with conversion rates between stages.
6. Channel scorecard: each channel’s cost per qualified enquiry, enquiry-to-appointment rate, cost per honoured appointment and cost per treated patient against target.
7. Unit and service-line view: the same metrics by unit and service line, so you can see where marketing money produces patients.
8. Board summary: eight numbers, the last three months, the quarter and the same month last year, with trend sparklines.

The template uses only formulas (SUMIFS, IFERROR, SPARKLINE in Google Sheets), so there are no scripts or add-ons, and no patient-identifiable data goes into it. You enter aggregated monthly counts only.

Which hospital marketing KPIs actually matter?

The KPIs that matter are the ones that follow a patient from first exposure to treatment. A hospital marketing funnel has seven stages, and every useful KPI either counts a stage or measures the conversion between two stages.

1. Impressions: people who saw an ad, a search result or your Business Profile.
2. Clicks and sessions: people who visited your website or landing page.
3. Enquiries: calls, form submissions and WhatsApp chats. Split these into total and qualified.
4. Appointments booked: enquiries that resulted in a confirmed OPD slot, test or procedure date.
5. Honoured appointments: patients who actually turned up.
6. Treated patients: patients who went on to the service you were marketing (a procedure, an admission, a package, or a consultation if that is the product).
7. Revenue: billed revenue from those treated patients.

Everything above enquiries is a marketing activity metric. Everything below it is a business outcome. Most reports stop at enquiries. A useful dashboard goes all the way down, even if the lower stages are only available monthly and with some delay.

The core KPIs and their formulas

Two of these deserve particular attention. The enquiry-to-appointment rate is where most hospitals lose the most money, because it sits between marketing and operations and nobody owns it. And cost per treated patient is the only cost metric that is comparable across channels with very different lead quality. A channel with a cheap cost per lead and a poor treatment rate often turns out to be the most expensive.

Supporting KPIs

• Channel mix: the share of qualified enquiries and treated patients from each channel. Useful for spotting dependence on a single paid channel.
• Google Business Profile actions: calls, direction requests, website clicks and bookings from each listing. Google lists the available Business Profile performance metrics in its help centre.
• Reviews: new reviews per month, average rating and response rate per listing. Velocity matters as much as the average; see this guide to patient review volume, velocity and response.
• Organic traffic: non-brand organic sessions to service and condition pages, which shows whether SEO is building demand beyond people who already know your name.
• Doctor page views: views of doctor profile pages, and enquiries started from them. For many specialties, patients choose the doctor first. These guides explain doctor profile pages that get booked.
• Enquiry leakage: enquiries with no recorded outcome, such as missed calls never returned or chats left unanswered. The glossary entry on lead leakage explains how to count it.

How do you define each KPI so the numbers are trusted?

Dashboards fail more often from unclear definitions than from bad formulas. Agree these definitions in writing with marketing, the contact centre and finance before you build anything.

Qualified enquiry

A unique patient or caregiver contact, with a valid phone number, asking about a service you offer at a location you serve. Exclude job seekers, vendors, wrong numbers, and repeat contacts from the same number within 30 days (count those as one enquiry). Write the rule down and apply it the same way every month.

Appointment booked

A confirmed slot recorded in the HIS or appointment system with a date and doctor or service. A callback promise is not an appointment.

Honoured appointment

The patient was registered or billed on the appointment date or within an agreed window (for example, seven days, to allow rescheduling).

Treated patient

Define this by service line. For orthopaedics it might be a surgery or a physiotherapy package. For a health check programme it is a completed package. For a general OPD campaign it might simply be a billed consultation. The key is that each service line has one definition that does not change mid-year.

If you are working on this now, read it alongside Patient feedback and NPS survey template for hospitals (free), Google Business Profile vs website and Google Ads vs Meta Ads for hospital service lines.

Marketing cost

Media spend plus agency fees and production costs allocated to that channel and service line. Decide whether to include internal team salaries. Most hospitals exclude them from channel-level cost per patient and show them separately at board level.

Attribution

Decide how an enquiry gets its channel. A simple and defensible rule is: tracked phone numbers and UTM-tagged forms decide the channel; untracked walk-ins and calls to the main board number go to “Direct or unknown”. Do not try to be perfect. This guide explains what attribution in healthcare can and cannot tell you.

Which view do you need: weekly, monthly or board?

One dashboard cannot serve everyone. The marketing manager needs to fix problems this week. The CEO needs to decide budgets this quarter. Build three views from the same data.

The board view should fit on one screen. If it needs scrolling, it has too much in it. Eight numbers with a trend is usually enough.

Where does the data come from?

Hospital marketing data sits in at least six places, and no single one holds the whole funnel.

• Google Analytics 4: sessions, landing page performance, form submissions and click-to-call or WhatsApp clicks set up as key events. Use UTM parameters consistently on every paid and social link.
• Google Ads: spend, impressions, clicks, calls from ads, and conversions. If you import offline outcomes, Google Ads can also show appointments or treated patients per campaign.
• Meta Ads Manager: spend, reach, clicks, lead form submissions and WhatsApp conversations started.
• Google Business Profile: views, calls, direction requests, website clicks, bookings and reviews per listing.
• Call tracking: calls per tracked number, answered versus missed, call duration, and the source mapped to each number. Without call tracking, phone enquiries from digital are mostly invisible. See this guide to call tracking and offline conversion import for hospitals.
• CRM and HIS: enquiry status, appointments, attendance, procedures and billing. This is where the bottom half of the funnel lives. If you do not have a CRM, a disciplined shared enquiry log can work at small scale; this explainer covers what a hospital CRM is actually for.

The join between marketing data and HIS data is usually the patient’s phone number. Keep that join inside the CRM or HIS. The dashboard itself should only receive aggregated counts by month, unit, service line and channel. Patient phone numbers and health information are personal data under the Digital Personal Data Protection Act, and the DPDP Rules, 2025 phase in security and access obligations for data fiduciaries over 18 months from November 2025. Do not paste patient lists into shared spreadsheets. This is general guidance, not legal advice.

How do you build it in Google Sheets or Looker Studio?

Option 1: Google Sheets (start here)

For a single hospital or a small group, Google Sheets is enough, and it is what the template uses. The workflow each month is:

1. Export last month’s channel data from Google Ads, Meta, GA4 and GBP.
2. Export last month’s enquiry and appointment outcomes from the CRM or HIS, aggregated by channel, unit and service line.
3. Paste or type the counts into the Inputs tabs, one row per month, unit, service line and channel.
4. Check the Funnel tab for obvious errors: more appointments than enquiries, zero spend with paid enquiries, or a sudden jump in one channel.
5. Review the Channel scorecard and Unit view, add three to five comments on what changed and why, then share the Board summary.

Every calculated tab uses SUMIFS on the Inputs tab, with a month selector and a unit selector. Ratios are always calculated from summed counts, never by averaging rates, which is a common error. For example, the group enquiry-to-appointment rate is total appointments divided by total qualified enquiries, not the average of each unit’s rate.

Option 2: Looker Studio on top of Sheets

Once the Sheets version is stable for two or three months, you can connect Looker Studio to the Inputs tabs and to Google Ads, GA4 and Search Console directly. That gives you date filters, drill-downs and automatic refresh for the top of the funnel. Keep the CRM and HIS outcomes flowing through Sheets (or a database) as monthly aggregates, so the bottom of the funnel still lands in the same report.

Option 3: Automated pipelines

Larger groups eventually move to scheduled exports from the CRM and HIS into a data warehouse, with Looker Studio or another BI tool on top. Only do this once definitions are agreed and the manual version has been trusted for at least a quarter. Automating a dashboard nobody trusts just produces untrusted numbers faster.

How should you set targets?

Targets should start from capacity and business goals, not from last month’s lead numbers.

1. Start with capacity. How many extra OPD slots, procedures or admissions can each service line handle per month? There is no point targeting more patients than doctors can see.
2. Work backwards through the funnel. If cardiology wants 40 more treated patients a month (an illustrative figure), and your current treatment rate is 50%, show rate 70% and enquiry-to-appointment rate 40%, you need roughly 40 / 0.5 / 0.7 / 0.4, which is about 286 qualified enquiries.
3. Set a cost ceiling. Decide the maximum cost per treated patient you can afford for each service line, based on average revenue and margin. The cost per treated patient calculator and the guide to setting a hospital marketing budget by service line help here.
4. Set conversion targets separately. Improving enquiry-to-appointment rate from 30% to 40% is often cheaper than buying 33% more enquiries. Put a target on it and give it an owner. The enquiry-to-appointment funnel calculator shows the effect.
5. Review quarterly. Use the first three months as a baseline before holding anyone to targets.

What are the common data problems, and how do you fix them?

Many hospitals also find a large gap in after-hours enquiries, where calls and chats come in when the contact centre is closed and are never returned. Track enquiries by hour and response time by hour. This article on the after-hours enquiry problem covers the fixes, and the enquiry leakage calculator estimates what it costs you.

Mistakes to avoid with a hospital marketing dashboard

• Reporting leads as the headline. Leads are an input. Put treated patients and cost per treated patient at the top.
• Building the dashboard before agreeing definitions. You will rebuild it, and lose trust each time the numbers change.
• Too many KPIs. If a number does not lead to a decision, remove it from the main view.
• Letting the agency own the dashboard. The dashboard and its data sources should sit in accounts the hospital owns.
• Ignoring the contact centre. If response time and enquiry-to-appointment rate are not on the dashboard, operational problems will be blamed on marketing, or the other way round.
• Comparing channels on cost per lead. Compare them on cost per honoured appointment or cost per treated patient.
• Putting patient-level data in shared sheets. Aggregate before it reaches the dashboard.
• Changing definitions mid-year without restating history. If you change a definition, recalculate previous months or mark the break clearly.

What good looks like

A hospital marketing dashboard is working when:

• The monthly review spends more time on decisions than on arguing about whether the numbers are right.
• Every channel and service line has a cost per treated patient, even if some are estimates with a stated method.
• At least 80% of digital enquiries have a recorded outcome (booked, not booked with reason, or not reachable). The 80% is a sensible internal goal, not an industry benchmark.
• Budget moves between channels and service lines at least once a quarter, based on the dashboard.
• The contact centre head reviews response time and enquiry-to-appointment rate with the same seriousness as the marketing team reviews cost per enquiry.
• The board summary is read in under two minutes and leads to one or two clear questions.

Getting started this month

Do not wait for perfect tracking. Download the template, agree the five definitions (qualified enquiry, appointment, honoured appointment, treated patient, marketing cost), fill in last month’s numbers with whatever data you have, and mark the gaps. The gaps themselves are useful: they tell you exactly which tracking or CRM fix to make first. After three months you will have a baseline, and the conversation with your leadership will move from leads to patients.

Frequently asked questions