9 weeks ago I first delved into Profit ROI. I handed over my work this week, ready to begin my next rotation in data science. In this post I give an overview of the project: some PPC background necessary for understanding, a snapshot of project outcomes, and a review of my learnings and successes.

I’ll begin with some definitions:

ROAS (Return on Advertising Spend): For every £ we spend on PPC advertising, what inbound revenue do we drive?

What happens when we increase our ROAS target? We’re demanding a greater return from the same amount of spend and either revenue per click or cost per click has to change to meet the target. We know that the following levers can impact the ROAS:

Bidding Adjustments (CPC) (Hour of Day / Day of Week, Location, Audience / Demographic / Prior Shopping, Device), Bounce Rate / Landing Page Experience, Conversion Rate, Average Order Value, Click Through Rate (Taking up space, extensions, compelling copy), Data Split (Clustering, size of cluster, content of cluster), Keyword Matching (Broad vs Exact), On page optimisation: Filtering and sequencing, Availability, Network % (Google clicks cost more than network clicks)

ROI (Return on Investment): For every £ we spend on PPC advertising, what profit do we drive?

At a top level, the Profit and PPC Advertising Cost impact ROI. Additionally, the following ROI levers are at play:

Manufacturing and Shipping Cost, Call Centre Cost, Warehouse Cost, Packaging Cost, Delivery (OOA/OOG, courier), Marketing Cost, Cost to Pick (IForce = items per order * Pick cost per item / DSV / Store Labour), Transaction Cost (Card payment commission), Returns (Cost to pick up), Cancellations

For both, ROAS and ROI, targets are set for each category. Day to day, ROAS is the key metric for the PPC team, and the above levers are utilised to meet our targets.

Unlike SEO where the focus is to rank highly in search engines to drive as much traffic as possible, PPC has the pressure of returning a profit on any spend. We track our performance against the above two metrics. Each channel supports the other, but they fundamentally have different aims.

The Pie Chart of Profitability

With ROI established, I’ll now introduce a simple visualisation which I found helpful to my understanding. Considering the revenue driven by a given PPC campaign to be 100% of the pie chart below, we then account various slices to the costs before profit. In the example below, we see that the campaign in question has an ROI of 15/19 = 0.79.

With my newfound understanding of profitability, I set out to answer the following data questions:

What is the relationship between ROI and the percentage of revenue spent on PPC?

Which campaigns aren’t working and should be pulled back?

To Jupyter and Beyond

Armed with 7000 rows of campaign ROI data, I imported my csv into a Jupyter Notebook and got cleaning: checking outliers and missing values, adding columns and converting data types.

Initially, I manipulated my data to output the worst performing campaigns year to date, using pandas functions such as groupby to attain a useful data frame on which to calculate ROI. I exported this table as a csv and categorised actions for different campaigns.

This complete, I turned my focus to exploring the relationship between ROI and the % of revenue spent on PPC. I used the plotly library to produce a scatter plot of these variables, with each point representing the performance of a single campaign for 1 week. I used curve fitting from the scipy.optimise library to add a curve of best fit, and added a horizontal trend line at our target ROI. The x-coordinate of the point of intersection gives the % of revenue we should spend on PPC to expect to meet our ROI target.

In an unexpected twist to the project, this approach results in a data-led method to setting ROAS targets. Taking the inverse of each output percentage gives the ROAS target. As an example, if 18.3% is the intersect to meet the ROI target, then 1/0.183 = £5.46 is the ROAS target.

On Reflection…

As I wrap up my first data project coded in Jupyter, here are some thoughts on how I think it went.

Firstly, this piece of work was a good opportunity to practice coding in Python. I’m enrolled on a Data Science Apprenticeship with Decoded; it was good to apply the skills I’m learning to a business dataset instead of gapminder, iris, or titanic data. My fluency of using the pandas library has improved greatly in the past 4 months; helped in part by coding on the fly in meetings. I’ve been pleasantly surprised by how quickly I’ve become comfortable in the notebook, yet Google is still a valuable resource for jogging the memory.

It was important to add narrative around the code – justifying decisions and challenges for the PPC team audience. I’m working on forming good commenting habits; making notes on what I plan to do before I do it.

I’m particularly proud of presenting back to the Decoded Data Cohort in a lunch and learn. A very different audience to the PPC experts, it was more relevant to drill down into some PPC background and on the tools I used, than the end recommendations. I’m hopeful that others will take their turn to present their own projects.

To Improve:

1. I’ve seen the power of an effective visualisation, and this is an area which I’d like to work on. Improving my knowledge of visualisation types and my capabilities in interactive plotting libraries such as plotly will be a focus for the next few months. Opportunities will arise through the Decoded programme and through my next rotation. For additional support, I could look into an online course.

• Consider how to incorporate more collaboration into projects. Working solo is okay when everything is running smoothly but presents a greater challenge when stuck, or confused with what to try next. Aside from the added sociability, I believe that coding as a team helps when picking up errors, generating new ideas and discussing how the project is going. While the PPC team were helpful with digital marketing insight and understanding, I’d like to try more regular catch ups with others who can ‘speak’ Python next time.

Until next time,

Scott