Median ads Case

Median Ads is an international performance-based online advertising agency that develops comprehensive solutions to scale its clients’ businesses both domestically and globally.

At the time of contacting us, the client lacked a comprehensive understanding and the ability to monitor how potential customers moved through the funnel — from initial engagement to purchase. The business uses Pipedrive CRM, collecting data on lead sources (including both first-touch and last indirect interactions) and has call tracking enabled. However, like any template-based system, Pipedrive has limitations when it comes to extracting the necessary information for analyzing lead journeys.

In addition, the client invests in advertising and wants to track the return on ad campaigns down to individual keywords. As this is a B2B business, the sales cycle can be very long, and in such cases, preserving the chain from the first click to revenue can be difficult. One of the requests was to provide the ability to view data, revenue, and deal statuses by both the creation date and the status change date. The former helps evaluate the return on investment using cohort logic, while the latter provides insights into the workload of the sales department.

Another request was to build a dashboard that, in addition to the above, would identify which entry point types (lead forms, call tracking, chat, etc.) bring in the best leads and which ones mostly generate spam.

Since our client is an online advertising agency, there was no need to explain the importance of proper UTM tagging for all traffic sources or the need to configure and store this information in the CRM to build a full-funnel dashboard. One of the first requests from the client was to create a guide with our best practices for standardizing UTM parameters across all current and future channels — so that everyone involved in traffic generation follows a unified logic.

Additionally, we set up data export from GA4 and Google Ads to BigQuery and, with the help of the client’s developer, exported data from the CRM.

After collecting the necessary data and verifying it against source systems to identify any extraction errors, we began processing. The data had to include as much detail as possible about the deals. Key parameters included: saved lead sources (both manually recorded and received from the call tracking service), entry points, statuses and their change dates, current deal status, creation date, and revenue.

We also processed data from advertising accounts, which eventually grew to two. We combined and aggregated this data at a level that allowed both a high-level overview and keyword-level analysis.

Additionally, we set up currency rate exports from PrivatBank to standardize reporting in a single currency. The overall data scheme looked as follows:

Based on lead creation date:

The dashboard allows you to analyze the following:

• The number of leads in each funnel status as of the current morning. The funnel is custom and fully aligned with the steps defined by the client in Pipedrive.
• The bar chart covers several aspects at once:
  • It displays the trend of total lead inflow over time.
  • It visually breaks down leads by their statuses as of the current morning. For example, if 20 new leads came in on Monday, by Friday those 20 leads will be shown broken down by their statuses as of Friday morning. Looking retrospectively, it becomes easy to identify how many leads got stuck at early funnel stages and stopped progressing.
  • Hovering over each bar displays a tooltip with percentage values, showing the share of leads in each status. By switching the date hierarchy from daily to monthly, the cohort logic becomes fully visible: after running a campaign, you can later filter it using the panel on the left and see how successful it was based on how its leads converted over time, at which stages they are currently stuck, assess lead quality, and identify spam rates.
• The table below has four levels of breakdown: the entry point (where the lead came from), contact type (which specific form on the website captured the lead), CRM lead ID (to quickly find it in the database and interact with a specific potential customer if needed), and the source and channel through which the lead was acquired. The table provides essential information about the quality of lead acquisition points — how many and which ones become qualified, which become actual customers, and which generate spam.
• The final chart visualizes the share of leads, revenue, or spam by entry point.
• Filters help segment the data by source, channel, ad campaign, reasons for lost leads, and specific funnel stages.

Based on the lead status update date:

This report shows the data for the day the lead status changed and helps analyze the performance and workload of the sales team.

This report is also divided into two dashboards – one using cohort logic (based on deal creation date), and the other using historical logic (based on status update date). Both include only data from ad campaigns, as the client needed to assess their effectiveness and ROI.

Based on lead creation date:

This report provides the following insights:

• How many leads were acquired via each ad channel and how they progressed through the sales funnel
• Trends in lead inflow and current lead statuses
• Advertising stats: impressions, clicks, spend, click-through rate, cost per lead
• Funnel breakdown by each ad campaign, both in absolute and relative terms
• Keyword-level performance — which keywords bring leads and which generate spam
• Top-performing entry points in each campaign and their stage in the funnel — whether they’re meant for top-of-funnel acquisition or late-stage conversion
• Ability to filter by lead entry point, ad campaigns, keywords, and reasons for lost opportunities

Based on status update date:

This dashboard, in addition to the above, lets you track exactly when leads from advertising campaigns changed status and moved through the funnel. It offers an alternative lens for analysis — for instance, seeing a spike in leads by status update date might suggest the company has many potential clients, but when viewed through the cohort dashboard, you might notice a decline in new leads. Pipedrive uses this kind of historical logic by default. With both views available, our dashboard provides a comprehensive understanding of lead dynamics.

In addition to the dashboards described above, we added a summary board at the client’s request to help "keep a finger on the pulse" — showing how many leads came in over the last 7/14 days (user-selectable), how many became qualified, and presenting a top-7 list of keywords with funnel details. This allows for quick decisions like increasing bids on top-performing keywords when auction win rates drop.

As often happens during analytics setup, we also identified inconsistencies in CRM usage by managers. This helped reveal and resolve previously unnoticed issues in business processes due to the absence of data in a format suitable for analysis.