In the previous article, we covered the basics of real-time reporting and introduced Omni Reporting, a real-time reporting layer within our Omni CDI system, which is Datomni’s customer data infrastructure designed for deployments in the client’s private clouds using a collection of Dockerized applications. today we’re going to build upon that!
In our previous article, we introduced Omni Reporting, a real-time reporting layer within the Omni CDI system, Datomni’s customer data infrastructure made for private cloud deployments using Dockerized components. Today, we’re going to build on that foundation.
In this article, we’ll guide you through a case study of our reporting pipeline in action. We’ll present a real-time Pipedrive report template, available for free with any plan in our Pipedrive Booster 2.0 implementation package, and provide a detailed breakdown of how this dashboard is built. This serves as a demonstration of how our reporting system works in practice. Let’s dive into it!
Step 0: The real-time Omni Pipedrive dashboard
Let’s begin with the end result. Below is the Omni Reporting Pipedrive dashboard (version 1.03) that we’ll walk you through. This is an embedded dashboard, designed to scroll in place, especially if you’re viewing on mobile.
As a side note, this dashboard is available for free to all users who signed up for our Pipedrive implementation package, regardless of their selected plan.
The most important aspect of the dashboard is that it relies on a real-time event pipeline. We are essentially capturing various changes in your Pipedrive account and extracting knowledge from them to build and update deep metrics on your dashboard in real time.
To achieve this result, the dashboard leverages several Omni CDI components, including real-time data warehousing, and highlights key metrics and reports. While most metrics and charts are straightforward, it is crucial to note that the dashboard integrates deal, lead, and contact data in real time. For example, reports like “Won Deals with Total and Average Deal Value by Source” require cross-referencing data from deals, leads, and contacts. This showcases the real-time data integration and enrichment pipeline, as Pipedrive’s event stream provides only single-entity updates (e.g., either deal or contact updates).
Step 1: Capture, enrich, and structure the raw Pipedrive event stream using Omni Analytics
As detailed in our introduction to Omni Reporting, the first step for our real-time dashboard is capturing raw data from Pipedrive webhooks using dedicated collectors built into Omni Analytics.
As detailed in our article on the launch of Omni Analytics, a data collection and enrichment platform within the Omni customer data infrastructure (CDI), we consider raw data streams inherently untrustworthy. Therefore, Omni Analytics performs validations, enrichments, and transformations before the events affect downstream business processes. In particular, each Omni Analytics collector has built-in event prevalidation to handle this. As the first step in validating incoming raw data for our real-time dashboard, we ensure that incoming events meet key Pipedrive property requirements, including:
| Event property | Data type | Description |
v | String | Webhook’s version (currently, it’s version 1). |
action | String | One of supported event actions: added, deleted, merged, updated. |
object | String | One of supported event object types or entities: activity, activityType, deal, note, organization, person, pipeline, product, stage, user. |
change_source | String | Only two values are valid for this field:app: Triggered by the Pipedrive web app.api: Triggered via the API. |
id | String | ID of the object. |
company_id | String | ID of the company where the webhook was triggered in. |
user_id | String | ID of the user who triggered the webhook. |
host | String | company.pipedrive.com, where the company represents the company name. |
timestamp | String | 10 character timestamp. |
timestamp_micro | String | 16 character timestamp. |
permitted_user_ids | Array | IDs of users who can see or have access to the object. |
trans_pending | Boolean | The parameter indicates the status of the database transaction’s commit. Values are represented as follows:false: Transaction is completed.true: Transaction is pending completion. |
is_bulk_update | Boolean | The values are boolean:true: The trigger event originated from the List view with a bulk operation.false: The trigger event occurred after a single object was affected, and no bulk operations were involved. |
pipedrive_service_name | String | If the webhook trigger came from Pipedrive’s service, the response will include the service name (e.g., “Import”). If it came from another source, the response will be false. |
matches_filters | Object | Contains the ID of the filter if the event was triggered from a view with an applied filter. If no filter is applied or the object doesn’t match the filter, the parameter will be empty. |
webhook_id | String | ID of the webhook. |
send_activity_notifications | Boolean | Appears only when the activity object is triggered. Values are shown as boolean:false: The transaction is completed.true: The transaction is pending completion. |
activity_notifications_language | String | Appears only when the activity object is triggered. Displays the language for the activity notification. If send_activity_notifications is false, then activity_notifications_language will be null. |
Once the raw Pipedrive event is validated and approved by the dedicated Omni Analytics collector, the remaining key processes such as filtering, enrichment, and aggregation begin.
Pipedrive webhook events typically include only entity-specific data, such as deal-related fields. This limited data is insufficient for our Pipedrive reporting template, which requires full context, including contact and marketing details. Therefore, during the enrichment step within Omni Analytics, we pull additional context from the Pipedrive API, particularly for Contacts or Organizations associated with a deal. We also use other APIs, such as email validation and scoring systems. Omni Analytics enriches raw webhook events with data from these sources to enhance reporting context. Below is the Pipedrive event payload after the first sequence of enrichments (mock data).
{
"request": {
"v": 1,
"matches_filters": {
"current": [126]
},
"meta": {
"action": "updated",
"change_source": "app",
"company_id": 13310617,
"host": "somecompany22220.pipedrive.com",
"id": 12044,
"is_bulk_update": false,
"object": "deal",
"permitted_user_ids": [
13523679,
20550613,
20673857,
20673879,
20673890,
20689081,
21090328,
21180880,
21303024
],
"pipedrive_service_name": false,
"timestamp": 1725294540,
"timestamp_micro": 1725294540402495,
"prepublish_timestamp": 1725294540469,
"trans_pending": false,
"user_id": 20689081,
"webhook_id": "317871"
},
"current": {
"stage_id": 2,
"probability": null,
"undone_activities_count": 0,
"active": true,
"stage_order_nr": 2,
"status": "open",
"currency": "GBP",
"products_count": 0,
},
"personal_properties": {
"email": "jack@datomni.com",
"first_name": "Jack",
"last_name": "Datomni",
"phone_number": "+1234567890"
},
"email_validation": {
"status": "valid",
"score": "deliverable",
"email": "jack@datomni.com",
"regexp": true,
"gibberish": false,
"disposable": false,
"webmail": true,
"mx_records": true,
"smtp_server": true,
"smtp_check": true,
"accept_all": false,
"block": false,
"sources": [
{
"domain": "beta.paganresearch.io",
"uri": "http://beta.paganresearch.io/details/stripe",
"extracted_on": "2020-06-17",
"last_seen_on": "2020-06-17",
"still_on_page": true
},
{
"domain": "icloudnewz.blogspot.com",
"uri": "http://icloudnewz.blogspot.com/2017/11/follow-patrick-collison-mike-birbiglia.html",
"extracted_on": "2020-03-25",
"last_seen_on": "2020-06-29",
"still_on_page": true
}
]
},
"meta": {
"params": {
"email": "jack@datomni.com"
}
},
"type": "updated.deal",
"platform": "PipedriveRequest"
}
}
If you request a custom dashboard as part of your Pipedrive implementation with us (including custom metrics or a custom data model), the real-time event enrichment processes will be adapted to generate a customized event payload, ensuring that the event data is rich enough to accurately calculate your custom metrics.
Once we have all the additional data points for the event, we process it through aggregation to generate initial metrics for the dashboard. For example, if a contact has multiple deals, we calculate the average deal value.
At this stage, the enriched event is not yet in a format that Omni Activation’s GTM Server, AWS, or Lambda Data Clients can understand. Therefore, in the final processing step, Omni Analytics restructures the event into the format expected by Omni Data Clients. This common data format ensures no nested keys are present in the event payload. The final payload, reflecting all previous steps of enrichment, filtering, and aggregation, differs significantly from the raw event received from Pipedrive.
{
"event_action": "updated",
"event_change_source": "app",
"event_id": 12044,
"event_is_bulk_update": false,
"event_object": "deal",
"event_permitted_user_ids": [
13529,
2013,
20857,
2073879,
2673890,
2689081,
21092328,
21184880,
213024
],
"event_pipedrive_service_name": false,
"event_timestamp": 1725294540,
"event_timestamp_micro": 1725294540402495,
"event_prepublish_timestamp": 1725294540469,
"event_trans_pending": false,
"event_user_id": 20689081,
"deal_stage": "contact made",
"deal_value": 22944,
"deal_currency": "USD",
"deal_status": "open",
"marketing_source": "facebook",
"marketing_campaign": "brand",
"contact_email": "jack@datomni.com",
"contact_first_name": "Jack",
"contact_last_name": "Datomni",
"contact_phone_number": "+1234567890",
"contact_position": "manager",
"organization_company_size": "small",
"organization_industry": "health",
"metrics_average_deal_value": 18700,
"metrics_total_deals": 5,
"email_validation_status": "valid",
"email_validation_score": "deliverable",
"email_validation_email": "jack@datomni.com",
"email_validation_regexp": true,
"email_validation_gibberish": false,
"email_validation_disposable": false,
"email_validation_webmail": true,
"email_validation_mx_records": true,
"email_validation_smtp_server": true,
"email_validation_smtp_check": true,
"email_validation_accept_all": false,
"email_validation_block": false,
"email_validation_sources_1_domain": "beta.paganresearch.io",
"email_validation_sources_1_uri": "http://beta.paganresearch.io/details/somecompany",
"email_validation_sources_1_extracted_on": "2020-06-17",
"email_validation_sources_1_last_seen_on": "2020-06-17",
"email_validation_sources_1_still_on_page": true,
"email_validation_sources_2_domain": "icloudnewz.blogspot.com",
"email_validation_sources_2_uri": "http://icloudnewz.blogspot.com/2017/11/follow-patrick-collison-mike-birbiglia.html",
"email_validation_sources_2_extracted_on": "2020-03-25",
"email_validation_sources_2_last_seen_on": "2020-06-29",
"email_validation_sources_2_still_on_page": true
}
Step 2: Capture the business-ready event in the activation layer
The enriched, well-structured event is dispatched from the Omni Analytics platform to the data activation stage managed by Omni Activation. As discussed in our article on lessons learned in building customer data infrastructures, to us activation encompasses any method of extracting utility from events, such as warehousing, reporting, or performance marketing.
Once the GTM Server or AWS Lambda Data Client receives the event, it restructures it for the data container, ingests it into the warehousing pipeline, and then into reporting. Our Omni Activation layer includes a real-time Pipedrive reporting script (tag) that formats these enriched events for real-time metrics and reports.
By separating the validation, enrichment, and aggregation from the standard processes, we can quickly adapt to custom metrics or reporting styles, provided the data model remains consistent. This functionality is part of our reporting activation tag, one of two activation tag types at Datomni (the other being performance activation tags). We plan to release this Pipedrive reporting tag to the public soon.
Step 3: Ingest the final event with metrics into the warehousing pipeline
At this stage, the event has been reformatted and dispatched to the warehousing layer (part of the activation layer), where it is processed. The duration of this process is approaching zero, as things happen in real time in our pipeline. The most critical aspect of this process is event validation.
Since Omni Reporting pipelines and dashboards operate in real time, there’s no post-processing and data cleaning step before displaying data on the dashboard. Therefore, cleaning and refining must occur before the event updates the dashboard. This requires strict event schemas to ensure that only accurate information enters the reporting layer.
Each dashboard template we offer is based on a specific warehousing schema that validates data unique to that template. Below is the schema validation used for this specific dashboard, corresponding directly with the metrics displayed.
{
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "Pipedrive Event",
"type": "object",
"properties": {
"event_action": {
"type": "string",
"enum": ["updated"]
},
"event_change_source": {
"type": "string",
"enum": ["app"]
},
"event_id": {
"type": "integer"
},
"event_is_bulk_update": {
"type": "boolean"
},
"event_object": {
"type": "string",
"enum": ["deal"]
},
"event_permitted_user_ids": {
"type": "array",
"items": {
"type": "integer"
}
},
"event_pipedrive_service_name": {
"type": ["boolean", "null"]
},
"event_timestamp": {
"type": "integer"
},
"event_timestamp_micro": {
"type": "integer"
},
"event_prepublish_timestamp": {
"type": "integer"
},
"event_trans_pending": {
"type": "boolean"
},
"event_user_id": {
"type": "integer"
},
"deal_stage": {
"type": "string"
},
"deal_value": {
"type": "integer"
},
"deal_currency": {
"type": "string",
"enum": ["USD", "GBP", "EUR"] // Assuming a few possible currencies, can be extended.
},
"deal_status": {
"type": "string",
"enum": ["open", "closed", "lost", "won"]
},
"marketing_source": {
"type": "string"
},
"marketing_campaign": {
"type": "string"
},
"contact_email": {
"type": "string",
"format": "email"
},
"contact_first_name": {
"type": "string"
},
"contact_last_name": {
"type": "string"
},
"contact_phone_number": {
"type": "string",
"pattern": "^\\+\\d{1,15}$" // Validates international phone numbers with country code.
},
"contact_position": {
"type": "string"
},
"organization_company_size": {
"type": "string",
"enum": ["small", "medium", "large"] // Assuming these as possible values, can be extended.
},
"organization_industry": {
"type": "string"
},
"metrics_average_deal_value": {
"type": "number"
},
"metrics_total_deals": {
"type": "integer"
},
"email_validation_status": {
"type": "string",
"enum": ["valid", "invalid", "accept_all", "webmail", "disposable", "unknown"]
},
"email_validation_score": {
"type": "string",
"enum": ["deliverable", "undeliverable", "risky"]
},
"email_validation_email": {
"type": "string",
"format": "email"
},
"email_validation_regexp": {
"type": "boolean"
},
"email_validation_gibberish": {
"type": "boolean"
},
"email_validation_disposable": {
"type": "boolean"
},
"email_validation_webmail": {
"type": "boolean"
},
"email_validation_mx_records": {
"type": "boolean"
},
"email_validation_smtp_server": {
"type": "boolean"
},
"email_validation_smtp_check": {
"type": "boolean"
},
"email_validation_accept_all": {
"type": "boolean"
},
"email_validation_block": {
"type": "boolean"
},
"email_validation_sources_1_domain": {
"type": "string"
},
"email_validation_sources_1_uri": {
"type": "string",
"format": "uri"
},
"email_validation_sources_1_extracted_on": {
"type": "string",
"format": "date"
},
"email_validation_sources_1_last_seen_on": {
"type": "string",
"format": "date"
},
"email_validation_sources_1_still_on_page": {
"type": "boolean"
},
"email_validation_sources_2_domain": {
"type": "string"
},
"email_validation_sources_2_uri": {
"type": "string",
"format": "uri"
},
"email_validation_sources_2_extracted_on": {
"type": "string",
"format": "date"
},
"email_validation_sources_2_last_seen_on": {
"type": "string",
"format": "date"
},
"email_validation_sources_2_still_on_page": {
"type": "boolean"
}
},
"required": [
"event_action",
"event_change_source",
"event_id",
"event_is_bulk_update",
"event_object",
"event_permitted_user_ids",
"event_timestamp",
"event_timestamp_micro",
"event_user_id",
"deal_stage",
"deal_value",
"deal_currency",
"deal_status",
"marketing_source",
"marketing_campaign",
"contact_email",
"contact_first_name",
"contact_last_name",
"contact_phone_number",
"organization_company_size",
"organization_industry",
"metrics_average_deal_value",
"metrics_total_deals",
"email_validation_status",
"email_validation_score",
"email_validation_email",
"email_validation_regexp",
"email_validation_gibberish",
"email_validation_disposable",
"email_validation_webmail",
"email_validation_mx_records",
"email_validation_smtp_server",
"email_validation_smtp_check",
"email_validation_accept_all",
"email_validation_block",
"email_validation_sources_1_domain",
"email_validation_sources_1_uri",
"email_validation_sources_1_extracted_on",
"email_validation_sources_1_last_seen_on",
"email_validation_sources_1_still_on_page",
"email_validation_sources_2_domain",
"email_validation_sources_2_uri",
"email_validation_sources_2_extracted_on",
"email_validation_sources_2_last_seen_on",
"email_validation_sources_2_still_on_page"
]
}
Step 4: Feed the dashboard and reporting model with clean, validated data
Processed events are stored in the warehouse using a schema employed in the data model underlying the Pipedrive dashboard we shared at the beginning. From that point, every dashboard we create is essentially a user interface for a series of SQL queries that filter and organize event data from our warehouse, generating the specific reports and metrics displayed on the dashboard. This model incorporates the metrics created during the pre-aggregation stage as the event passes through the Omni Analytics collectors and Omni Activation layer.
More dashboard templates on the way
Omni Reporting is one of the most active developments we lead at Datomni. We are working on new reporting variants and templates for various data sources, including cross-data source integrations.
Photo attribution
As usual, the featured image of the article is a photograph that corresponds with the article’s topic. This time, the shoutout goes to Jason Leung via Unsplash.
