From Ad Spend to Bookings: A Web and Marketing Analytics Case Study for a Local Service Business
| Type | Self-directed portfolio project |
|---|---|
| Scenario | 2loos, a hypothetical portable toilet rental business around Roodepoort, Soweto and greater Johannesburg |
| Data | 21,000+ web sessions and a daily ad spend log across 6 campaigns, 6 months |
| Tools | Python (Pandas) · Looker Studio · Google Ads and Meta Ads data structures |
| Focus | Web analytics, marketing attribution, conversion rate optimisation |
| Outcome | 3-page Looker Studio dashboard and a plain-language owner report |
The Brief
Most small local service businesses in South Africa already spend money on Google and Meta ads, but very few can answer a simple question: is it actually working?
To reflect that everyday problem, I modelled a hypothetical portable toilet rental business, 2loos, operating around Roodepoort, Soweto and the greater Johannesburg area. Like many businesses in this space, 2loos runs paid ads, posts on social media and has a functional but unoptimised website, with no one watching whether any of it turns into actual bookings.
The goal: take six months of realistic website and ad platform data, find out what is working, and turn that into a dashboard and report a real business owner could use to make decisions.
The Data
I built two datasets to mirror what a real GA4 and ad platform export looks like:
- A session-level web analytics log of 21,000+ rows covering traffic source, campaign, device, landing page, session behaviour and conversion outcome (a phone call or an online booking).
- A daily ad spend log with spend, clicks and impressions across six live campaigns on Google Ads and Meta Ads.
I deliberately built in the mess real exports contain: inconsistent date formats, inconsistent capitalisation in campaign names, and missing values. Starting from clean data would have skipped the part of the job that takes the most time.
The Cleaning Process, and Why It Mattered
This is the part I think matters most, because it is usually invisible in a finished dashboard.
1. Inconsistent names created phantom campaigns
Six real campaigns appeared as 18 distinct entries because of capitalisation differences across exports (Google_Search_JHB vs GOOGLE_SEARCH_JHB vs google_search_jhb). Left unfixed, this would have produced a false insight: "we are running too many underperforming campaigns", when there were only six with a normal spread of performance.
2. A blank Campaign field does not always mean "organic"
An early cleaning pass filled every missing campaign with "Organic". That quietly relabelled genuine paid clicks (rows where Medium was cpc) as organic, creating a direct contradiction in the data.
3. Date ambiguity is real, and only partly solvable
Dates were mixed across DD/MM/YYYY, MM/DD/YYYY and ISO formats. Roughly a third of all date values were genuinely ambiguous: 03/01/2026 could be 3 January or 1 March, with no way to know from the string alone.
Rather than guess and hope, I applied a per-row, priority-ordered parser that resolves every unambiguous case automatically, flagged the remaining ambiguous rows explicitly, and excluded any row provably wrong (outside the known campaign window) rather than letting a silent guess corrupt a trend line.
The Findings
Not all ad spend is equal. Cost per lead ranged from R27.61 (Facebook Retargeting) to R108.62 (Google Performance Max) across the six campaigns, nearly a 4x difference for the same outcome. Reallocating a portion of underperforming spend toward the most efficient channels could realistically generate 15 to 20 additional leads a month at no extra cost.
The funnel has one dominant leak. Only 17% of sessions ever reached a pricing or booking page, the single largest drop-off in the funnel and larger than the drop-off to conversion. The concrete fix: a persistent quote or call call-to-action on every service page, instead of a detour through a separate pricing page.
Mobile and desktop behave differently, not just at different volumes. Mobile drives the most traffic and the most phone calls; desktop drives more completed online bookings. That is a signal that the mobile booking form may have friction pushing people to call instead of booking directly.
The Deliverables
| Deliverable | What it is |
|---|---|
| 3-page Looker Studio dashboard | Overview, Ad Spend and Website Sessions pages. Built with native calculated fields and data blending for cross-source campaign analysis, with no scripting or add-ons beyond what a free Looker Studio account supports. |
| Stakeholder report | Written for a business owner, not a fellow analyst: plain language, prioritised recommendations, and an honest disclosure of the data quality issues found along the way. |
What This Project Demonstrates
- Building and cleaning a realistic, messy multi-source dataset (web analytics plus ad platform data) the way it arrives in the real world
- Diagnosing data quality issues that would otherwise have produced confidently wrong conclusions
- Marketing attribution and cost-efficiency analysis across paid channels
- Funnel and behavioural analysis turned into specific, low-cost recommendations
- Explaining technical findings to a non-technical audience, in both dashboard and written form
- Working within a real tool's constraints (Looker Studio) rather than designing something a small business could not maintain
What This Means for Your Business
If you spend on ads and your data sits in separate exports with no clear answer to "is it working?", this is the analytics layer I build: clean source data, honest attribution, and a dashboard your team can open without me. This was a self-initiated project modelling the kind of business I intend to work with, local service businesses and online stores that already generate traffic and ad spend but lack the analytics to know whether it is paying off.