Lead Bleed Analysis

In 2016 Zoopla, a property company, had an issue with Paid Search lead analysis as there wasn't any non user interface report that visualised the data, especially when it came the added values the campaigns were generating. 

Problem
Research had shown that some campaign types didn't deliver the expected leads - i.e. our campaigns running to rent campaigns, delivered for sale leads. So the question was - would it benefit us to optimise the to rent campaigns to get more for sale leads?

The Solution
Working with our analytic team, I worked on building an Excel report that not only showed our usual weekly traffic and conversion metrics but also showed what we call lead bleed - how much of the other secondary lead types did a campaign generate. This was done by making full use of some of my favourite formulas - IF, ISBLANK & SUMIFS:

Nesting at it's finest I think. It did take a few days for us to put this template together but once all the formulas were set - it took a mere 15minutes to update on a weekly/monthly basis. 

Result
With regular update done, we were able to see which secondary conversions were being generated for each campaign type and optimise not just towards the lead campaigns but also other campaigns that were driving healthy secondary conversions. With this new level of in-depth analysis we managed to improve on volume by of desired lead types by 20% without needing to push on spend at all.