How to Track Paid Ad ROI in Zoho CRM and Zoho Analytics

This tutorial walks through how to connect Google Ads spend to Zoho CRM outcomes using UTM campaign tracking, Zoho Analytics, a SQL union table, and aggregate formulas. The end result is a reporting model that lets you measure leads, deals, revenue, profit, and true return on ad spend from your paid campaigns.

Full Tutorial Video: Google Ads & Zoho CRM+Analytics // Calculate your Return on Ad Spend

A Google Ads ROI reporting workflow in Zoho is not just about counting clicks or form submissions. When it is built correctly, it gives your team a clean way to compare ad spend against real CRM outcomes such as leads created, deals created, closed won count, closed won revenue, and overall campaign profit.

Part 1: Capture UTM Campaign Data in Your Form

Why does UTM campaign tracking matter?

The entire reporting model depends on preserving the campaign identifier from the original ad click all the way into Zoho CRM. In most cases, the most important field is UTM Campaign, which often stores the ad platform’s campaign ID.

How do I capture UTM data in Zoho Forms?

1. Open Zoho Forms.
2. Go to Share.
3. Open the UTM Tracking settings.
4. Enable the relevant UTM parameters.
5. Copy the provided tracking JavaScript.
6. Place that script at the end of the body section of your webpage.

That setup ensures the form submission includes the campaign data needed for later reporting in Zoho CRM and Zoho Analytics.

Can I use a different form tool?

Yes. Zoho Forms is used in this walkthrough, but the same approach works with other form tools as long as they capture the campaign parameter and pass it into Zoho CRM correctly.

Part 2: Map UTM Campaign into Zoho CRM

Where should the UTM campaign value be stored in Zoho CRM?

At minimum, you should create a field in the Leads module to store the UTM campaign value. That value needs to land on the lead record when the form is submitted.

Should the UTM campaign also be mapped to Contacts and Deals?

Yes. During lead conversion, the field should pass to both Contacts and Deals. For this style of ROI reporting, the Deal record is especially important because that is where revenue and pipeline outcomes are measured.

Why does lead conversion mapping matter?

If the UTM campaign only exists on the original lead, attribution breaks once the lead converts. Proper conversion mapping is what allows ad-origin data to follow the record into the sales pipeline.

Part 3: Sync CRM and Google Ads Data into Zoho Analytics

Which CRM tables need to sync into Zoho Analytics?

For this reporting model, make sure the following modules sync into Zoho Analytics with the UTM campaign field included:

• Leads
• Contacts
• Deals

What Google Ads data should be imported?

The two most important Google Ads tables are:

• Campaigns
• Campaign Performance

The Campaigns table gives you the campaign name and campaign ID. The Campaign Performance table gives you spend by date, which is the core input for paid advertising ROI analysis.

Does this process only work for Google Ads?

No. The same reporting pattern can generally be adapted for LinkedIn Ads, Facebook Ads, and Instagram Ads because the data structure is similar enough for this kind of attribution model.

Part 4: Why Standard Reports Are Not Enough

What is the reporting problem this solves?

If you only look at lead counts by campaign, you still do not know whether the campaign was profitable. A campaign can generate a large number of low-quality leads and still perform badly if those leads never become real opportunities or revenue.

What you actually need is a single model that connects:

• Ad Spend
• Leads Created
• Deals Created
• Deals Closed Won (Count)
• Deals Closed Won (Revenue)

That is where the SQL union table comes in.

Part 5: Create a SQL Union Table in Zoho Analytics

Why use a SQL union table?

A SQL union table lets you combine multiple source tables into one master reporting table with a consistent structure. Instead of reporting separately on ad platform data and CRM data, you create one data set that can be used in pivot tables, charts, and dashboards.

What fields should the master table include?

In this tutorial, each block of SQL outputs the same four core columns:

• Type
• Campaign Name
• Date
• Amount

The Type column labels the row as Ad Spend, Leads Created, Deals Created, Closed Won Revenue, or Closed Won Count. The Amount column stores either a count value of 1 or a revenue/spend value depending on the row type.

SQL template used in the walkthrough

You can also view the code-share version here: Template SQL Script for Managing Google Ads Data in Zoho Analytics

SELECT
     'Ad Spend' as 'Type',
     "Campaigns (Google Ads)"."Campaign" as 'Campaign Name',
     "Campaign Performance (Google Ads)"."Day" as 'Date',
     "Campaign Performance (Google Ads)"."Costs" as 'Amount'
FROM  "Campaign Performance (Google Ads)"
JOIN "Campaigns (Google Ads)" ON "Campaign Performance (Google Ads)"."Campaign ID"  = "Campaigns (Google Ads)"."Campaign ID"  
UNION ALL
 SELECT
     'Leads Created' as 'Type',
     "Campaigns (Google Ads)"."Campaign" as 'Campaign Name',
     "Leads (Zoho CRM)"."Lead Created Date" as 'Date',
     1 as 'Amount'
FROM  "Leads (Zoho CRM)"
JOIN "Campaigns (Google Ads)" ON "Leads (Zoho CRM)"."UTM Campaign"  = "Campaigns (Google Ads)"."Campaign ID"  
UNION ALL
 SELECT
     'Deals Created' as 'Type',
     "Campaigns (Google Ads)"."Campaign" as 'Campaign Name',
     "Leads (Zoho CRM)"."Lead Created Date" as 'Date',
     1 as 'Amount'
FROM  "Deals (Zoho CRM)"
JOIN "Leads (Zoho CRM)" ON "Deals (Zoho CRM)"."Id"  = "Leads (Zoho CRM)"."Converted Deal" 
JOIN "Campaigns (Google Ads)" ON "Leads (Zoho CRM)"."UTM Campaign"  = "Campaigns (Google Ads)"."Campaign ID"  
UNION ALL
 SELECT
     'Deals Closed Won (Revenue)' as 'Type',
     "Campaigns (Google Ads)"."Campaign" as 'Campaign Name',
     "Leads (Zoho CRM)"."Lead Created Date" as 'Date',
     "Deals (Zoho CRM)"."Amount" as 'Amount'
FROM  "Deals (Zoho CRM)"
JOIN "Leads (Zoho CRM)" ON "Deals (Zoho CRM)"."Id"  = "Leads (Zoho CRM)"."Converted Deal" 
JOIN "Campaigns (Google Ads)" ON "Deals (Zoho CRM)"."UTM Campaign"  = "Campaigns (Google Ads)"."Campaign ID"  
WHERE  "Deals (Zoho CRM)"."Stage"  = 'Closed Won'
UNION ALL
 SELECT
     'Deals Closed Won (Count)' as 'Type',
     "Campaigns (Google Ads)"."Campaign" as 'Campaign Name',
     "Leads (Zoho CRM)"."Lead Created Date" as 'Date',
     1 as 'Amount'
FROM  "Deals (Zoho CRM)"
JOIN "Leads (Zoho CRM)" ON "Deals (Zoho CRM)"."Id"  = "Leads (Zoho CRM)"."Converted Deal" 
JOIN "Campaigns (Google Ads)" ON "Deals (Zoho CRM)"."UTM Campaign"  = "Campaigns (Google Ads)"."Campaign ID"  
WHERE  "Deals (Zoho CRM)"."Stage"  = 'Closed Won'

Part 6: Use Aggregate Formulas with sum_if

Why use aggregate formulas instead of raw report math?

Once the SQL master table is built, aggregate formulas make the reporting layer much easier to manage. Rather than dragging the Type field around in every report, you can create reusable formulas that summarize each metric cleanly.

What is an example aggregate formula for Ad Spend?

sum_if("Google Ads + CRM Data Master Table"."Type" = 'Ad Spend',"Google Ads + CRM Data Master Table"."Amount")

You would create similar aggregate formulas for:

• Leads Created
• Deals Created
• Deals Closed Won (Count)
• Deals Closed Won (Revenue)

What can I calculate from those aggregate formulas?

Once the base formulas exist, you can easily create higher-level metrics such as:

• Cost Per Lead = Ad Spend / Leads Created
• Lead Conversion Percent = Deals Created / Leads Created
• Campaign Profit = Closed Won Revenue – Ad Spend
• ROI Percent = Campaign Profit / Ad Spend

Part 7: Build Pivot Tables and Charts in Zoho Analytics

How should I visualize the data?

Once your aggregate formulas are ready, you can use them in Pivot Tables and Charts to build clean campaign reporting.

Common report dimensions include:

• Campaign Name
• Month and Year
• Date Range

What reports are most useful?

• Monthly ad spend by campaign
• Ad spend vs leads created
• Ad spend vs closed won revenue
• Campaign profit by month
• Lead conversion percent by campaign

This gives marketing, sales, and leadership a much clearer picture of what is actually working in paid advertising.

Part 8: Decide How to Attribute Revenue by Date

Should I report revenue based on deal close date or lead creation date?

This is one of the most important reporting decisions in the entire workflow. In the walkthrough, one option shown is attributing deals and revenue back to the lead created date rather than the actual close date.

Why would I use lead creation date?

If your goal is to understand marketing performance, it often makes more sense to tie revenue back to the month when the ad spend generated the lead. That gives a more realistic view of which campaigns created valuable opportunities.

When would close date make more sense?

If your goal is operational sales or finance reporting, you may want to attribute revenue to the month the deal actually closed. Both approaches are valid. The right choice depends on what question the dashboard is supposed to answer.

Part 9: Add Filters and Dashboard Controls

What filters should I include in the dashboard?

Useful dashboard filters for this type of reporting usually include:

• Campaign Name
• Relative Date Range such as Last 6 Months
• Custom Date Range

With those filters in place, users can quickly narrow the view to a specific campaign, time period, or reporting window without rebuilding the report.

Google Ads + Zoho Analytics Reporting Reference Table

Component	Purpose	Example in This Tutorial	Best Practice
UTM Campaign	Stores the campaign identifier from the ad click	Mapped from form submission into Zoho CRM	Make sure it is preserved through lead conversion into Deals
Campaigns Table	Provides campaign names and campaign IDs	Campaigns (Google Ads)	Use campaign ID as the main join key
Campaign Performance Table	Stores spend by campaign and date	Campaign Performance (Google Ads)	Use this as the ad spend source in your union table
SQL Union Table	Combines multiple sources into one reporting model	Ad Spend, Leads Created, Deals Created, Closed Won rows	Standardize the output columns across every SELECT block
Aggregate Formula	Summarizes one metric from the Type column	sum_if(… Type = ‘Ad Spend’ …)	Build reusable formulas before creating dashboards
Dashboard Filters	Lets users explore performance by time and campaign	Campaign + date filters	Include both relative and custom date range options

Code Share Snippet

You can use the SQL template and walkthrough notes here:

Template SQL Script for Managing Google Ads Data in Zoho Analytics

Next, you’ll create some aggregate functions using sum_if statements against the Type column. One example to aggregate Ad Spend would be:

sum_if("Google Ads + CRM Data Master Table"."Type" = 'Ad Spend',"Google Ads + CRM Data Master Table"."Amount")

These can be easily used in Pivot tables and Charts to create consolidated reporting such that you can actually connect your Google Ads spend to CRM outcomes like Leads Created, Deals Created, and Deals Closed Won by both count and revenue amount.

Check out the video for the full walkthrough.

Frequently Asked Questions