case study / Zoho CRM / consumer packaged goods

Making synced email reportable in Zoho CRM.

The owner wanted to measure how consistently his team followed up: who was emailing customers, how quickly new leads received an answer, and which accounts had gone quiet.

Email sent from Zoho CRM was already available in Analytics. The team's Outlook email, synchronized to CRM through IMAP, was visible on individual records but excluded from those reports. This case study covers the custom activity log we built to bring the two sources together.

Skip to the Solution

The short versionWhat we built and how it works.

The scheduled sweep captures qualifying email from CRM related lists. Analytics then de-duplicates it against native CRM-sent email before combining the complete email history with calls and meetings.

Records are selected by Last_Activity_Time, not Modified_Time. Synced email updates the first field but not the second. Converted leads require a separate pass because COQL excludes them by default. In one test window, the standard query returned 23 records and the converted-lead query returned 16, with no overlap.

The record and email windows are deliberately different sizes. The sweep finds records with activity in the last 24 hours but reads 14 days of email. That overlap allows a later run to recover messages missed during an outage. Email is returned newest first, so paging stops when a full page falls outside the window. Each record costs one request regardless of its email volume.

The source field is the only reliable way to classify the message. The payload key is Source, with a capital S, despite the documentation. Quote autoresponders are marked as sent and carry a real user as the owner, so the owner field does not distinguish them from a salesperson’s email. Two of the five known source values first appeared during the third test run.

The message ID is used as a unique row key. Zoho associates email with records by matching addresses, so the same message can appear on a lead, contact, and deal. The unique key limits it to one log row, while the read order determines which record receives the attribution. A duplicate create returns 202 with DUPLICATE_DATA in the body and is treated as a skip.

The native Analytics email tables contain email sent from CRM, but not mailbox email synchronized through IMAP. The custom Email Logs table supplies the missing IMAP activity but also overlaps with messages already present in the native tables. The All Email query uses the message identifier to remove those duplicates. In the native tables, Owner contains a user ID even though the field is typed as text, so each branch also joins Users to resolve it.

Nineteen-digit record IDs must be handled as text. Treating them as numbers exceeds the available decimal precision, rounds the values, and can join activity to the wrong record. concat(x,'') provides a working conversion in this SQL dialect; CAST and to_char do not.

Hover over a marked step to see its implementation details.

01 / The situationWhy the existing reports were incomplete.

Zoho Analytics received the CRM Emails module, but that module represented only part of the client's email activity.

The client reasonably assumed that email visible on a CRM record would also be available in Analytics. That was true for messages sent from CRM. Those messages synchronized through the Emails module and appeared in the native Deal, Lead, and Contact Emails tables.

The team, however, worked primarily in Outlook. Messages synchronized back to CRM through IMAP appeared in the email related lists on the appropriate records but were not added to the CRM Emails module. They were visible when someone opened a lead, contact, or deal and absent from the corresponding Analytics tables.

The problem was therefore not that Zoho Analytics had no email data. It had the email sent from CRM, while most of this team's email was coming from a different source and was excluded. There is no CRM setting that causes the IMAP-synchronized messages to enter the Emails module.

The available workarounds.

We considered three common approaches before building a custom log. Each addresses part of the problem, but none met the reporting and recovery requirements.

Workaround	Why it falls short
Log a Task for every email, typed "Email"	This produces a reportable number, but it measures manual task logging as much as email activity. It also provides no way to identify messages that were never logged.
BCC a dropbox address	This can place an otherwise unconnected message on a CRM record, but the message still lands in the related list rather than the reportable Emails module.
Associate each message as it arrives, via a workflow	This can capture new email as it arrives, but it depends on the event firing successfully. A disconnected mailbox, workflow error, or platform outage creates a permanent gap unless someone later identifies and backfills it. It also provides no historical data before the workflow was enabled.

We therefore used a scheduled sweep rather than an event trigger. Each run rereads an overlapping window of email, so a later run can recover a message missed during a temporary outage. The record only needs to appear in a later activity query; the capture routine does not need to have been running at the moment the email was sent.

02 / The designA custom module for email activity.

The custom module stores one row per qualifying related-list message. Analytics uses the message identifier to reconcile that log with email already present in the native tables.

Each log record contains the send time, sender, related CRM record, message identifier, and the raw source value supplied by Zoho. A scheduled routine runs twice a day, selects records with recent activity, reads their related email, and writes qualifying outbound messages to the custom module. A separate backfill routine processed older email in manageable date ranges. Because the related lists contain both CRM-sent and IMAP-synchronized messages, the custom log overlaps with the native Analytics data.

Two design decisions were particularly important.

First, the message identifier is unique in the log. Zoho associates email with CRM records by matching addresses, so the same message may appear on a lead, a contact, and the deal created from that lead. Using the message identifier prevents those copies from becoming duplicate custom-module rows. In Analytics, the same identifier is also used to exclude custom-log messages already present in the native CRM email tables. The resulting All Email dataset contains one row per message regardless of where it was sent.

Second, the log stores Zoho's raw source value without translating it during capture. The source value determines whether a message represents customer contact, and the classification rules may change as new values are discovered. Keeping the original value allows the reporting logic to be revised without changing the historical log records.

03 / What countsSeparating customer email from automation.

Most messages in the related lists were not useful measures of customer contact. Classifying them required more testing than retrieving them.

In the API response, a quote autoresponder closely resembles a salesperson's reply. Both are marked as sent, and both may carry a real user as the owner. The usable distinction is a source field whose possible values are not documented. Two of the five values identified during testing did not appear until the third test run.

The capture routine therefore treats an unknown source value as a reason to stop and review the message. Once a message identifier has been written to the unique field, that row cannot simply be replaced by rerunning the capture. A skipped message, however, can be captured later. For that reason, unrecognized values are skipped, counted, and reported to us by email rather than written to the log.

This allows new message types to be classified deliberately before they affect the client's reports.

Results from one test run. Of 573 messages read across 95 records, 33 met the rules for outbound customer contact and were written to the log.

Two edge cases found during testing.

Because Zoho associates email with records by address, a contact record containing an employee's own email address can accumulate that employee's mailbox activity over time. We found one such record. Without an exclusion, the routine would have attributed a colleague's customer email to an unrelated account. Records containing an internal address are now excluded before their related email is read.

We also found automated order notifications forwarded between internal addresses. They were marked as sent, carried a real user as the owner, and passed the other classification tests. The routine now rejects any message with no recipient outside the company.

04 / ReliabilitySuppressing alerts after an incomplete run.

A week with no logged email may indicate an inactive salesperson, a disconnected mailbox, or a failed capture run. The system must distinguish among them before notifying the client.

When a user's mailbox connection to CRM drops, the user can continue working normally in Outlook while new sent messages stop appearing in CRM. There may be no visible error to either the user or the owner.

The system sends a short morning notice when a person in a customer-facing role has no recorded email for a week. The notice explains that a disconnected mailbox is the most likely cause and provides instructions for reconnecting it.

That notice is suppressed if the capture run was incomplete. A failed query, a time-limit interruption, or an unrecognized message source clears a reliability flag and prevents all client-facing findings from that run.

Otherwise, an incomplete capture could incorrectly suggest that an employee had stopped following up. Operational failures are sent to us separately so they can be investigated without presenting an unreliable conclusion to the client.

The historical backfill follows the same rule. Its cursor advances only after a complete pass. If a run fails, the cursor remains in place and the same date range is processed again. The unique message key prevents repeated work from creating duplicate rows.

05 / ConclusionOne reportable view of email activity.

Zoho already made CRM-sent email available in Analytics. The custom log adds the IMAP-synchronized email, and the All Email dataset removes the overlap between them.

In one test run, the routine examined 95 records, read 573 messages, and wrote 33 log rows. The difference reflects messages outside the date window, automated messages, inbound email, internal-only email, and messages already logged.

There is no before-and-after percentage because the client did not previously have a complete measurement. The result is simpler: qualifying outbound email from both sources now appears once in a reportable table, alongside the team's calls and meetings.