Digital marketing executive to marketing analyst in India

The worked project the answers refer to

Several answers below point to one small project. Build it first, on synthetic data. It joins a campaign spend export to CRM leads by the utm_campaign value and compares cost per lead with cost per qualified lead.

import pandas as pd

# Synthetic ad spend export (one row per campaign for the month)
spend = pd.DataFrame({
    "utm_campaign":   ["da_search_brand", "da_search_generic", "fde_meta_video", "fde_meta_carousel"],
    "platform":       ["Google", "Google", "Meta", "Meta"],
    "spend_inr":      [18000, 42000, 30000, 24000],
    "platform_leads": [30, 70, 120, 60],
})

# Synthetic CRM leads: what actually arrived, with qualification after a counsellor call
crm = pd.DataFrame({
    "lead_id": range(1, 21),
    "utm_campaign": (["da_search_brand"] * 4 + ["da_search_generic"] * 6 +
                     ["fde_meta_video"] * 7 + ["fde_meta_carousel"] * 2 + ["(not set)"]),
    "qualified": [1, 1, 1, 0,  1, 0, 1, 0, 0, 1,  0, 0, 1, 0, 0, 0, 0,  1, 1,  1],
})

crm_summary = crm.groupby("utm_campaign").agg(
    crm_leads=("lead_id", "count"), qualified=("qualified", "sum")).reset_index()

report = spend.merge(crm_summary, on="utm_campaign", how="left").fillna(0)
report["cpl_platform"] = report["spend_inr"] / report["platform_leads"]
report["cost_per_qualified"] = report["spend_inr"] / report["qualified"].where(report["qualified"] > 0)
print(report.round(0).to_string(index=False))

unmatched = crm[~crm["utm_campaign"].isin(spend["utm_campaign"])]
print("\nCRM leads with no matching campaign:", len(unmatched))

In this example the platforms report far more leads than the CRM received, which is common when you compare a platform's own count with a CRM that removes duplicates and incomplete forms. The Meta video campaign has the cheapest platform CPL but the most expensive cost per qualified lead, and one CRM lead has no campaign at all. Scaled down to 20 rows, those are the three things marketing analysts find.

Questions about your background

"You ran campaigns. Why do you want to analyse them instead?"

Model answer: "Running campaigns, I was judged on cost per lead from the ad platform. When I compared it with what counsellors said about lead quality, the ranking of campaigns reversed. I became more interested in that second number than in running more campaigns, so I learned pandas and SQL to join spend to CRM outcomes."

"What did you actually measure in your last role?"

Be specific about your artefacts: weekly campaign reports with spend, impressions, CTR and CPL; the UTM tracking sheet you maintained; any GA4 reports you used. Then say honestly what you did not measure, usually downstream conversion, and that this is the gap you have been working on.

Questions that test analytical judgement

"Meta says 120 leads, our CRM shows 7 for the same campaign. Which is right?"

Model answer: "They count different things, so neither is simply wrong. The platform counts form submissions it attributes to the ad, possibly including duplicates and people who abandoned partway. The CRM counts what arrived and was stored. I would check the lead-form integration first, then duplicates, then attribution windows, and report both figures with the definition next to each rather than pick one."

"Which campaign would you cut?"

Model answer: "Not on platform CPL alone. In my practice data the cheapest-CPL campaign had the highest cost per qualified lead. I would also check whether the qualified count per campaign is large enough to trust, because a campaign with two qualified leads can swing a lot next month."

"What does `(not set)` in the campaign column mean?"

Model answer: "The lead arrived without a campaign tag. Common causes are links without UTMs, redirects that strip parameters, or direct and organic visits. I report the share of untagged leads every month because if it grows, every campaign comparison gets less reliable."

"How would you prove a campaign caused enrolments?"

Model answer: "A join by UTM shows association, not cause. To estimate causal effect I would need a holdout or a geo-split test planned before spending. Without one, I would say the campaign is associated with qualified leads and be careful with the word 'caused'."

Questions that test tools

"Write a query that gives qualified leads per campaign."

CREATE TABLE crm_leads (lead_id INTEGER, utm_campaign TEXT, qualified INTEGER);
INSERT INTO crm_leads VALUES (1,'da_search_brand',1),(2,'da_search_brand',0),
 (3,'fde_meta_video',0),(4,'fde_meta_video',1),(5,'fde_meta_video',0);

SELECT utm_campaign, COUNT(*) AS leads, SUM(qualified) AS qualified_leads
FROM crm_leads
GROUP BY utm_campaign
ORDER BY qualified_leads DESC;

"Why a left join and not an inner join in your project?"

Model answer: "An inner join drops any campaign with spend but no CRM leads. Those are exactly the campaigns that spent money and produced nothing, so I keep them with a left join and fill zero."

The honest gap

Digital marketers often know platforms deeply and data joins barely. Expect to learn SQL properly, the difference between attribution models, basic statistics for comparing conversion rates, and one BI tool. Also learn to stop defending campaigns you ran; an analyst's credibility depends on reporting unwelcome results.

Performance or marketing analyst: which to apply for

Marketing analyst, performance marketing analyst and growth analyst are the natural first targets. A hybrid role where you still manage some spend but own the reporting can be a practical bridge, especially inside your current employer.

One answer to rehearse aloud

"For two years I managed search and social campaigns and reported cost per lead every week. When I matched our spend to CRM outcomes, I found the campaign with the cheapest leads produced the most expensive qualified leads. I built that comparison in pandas on synthetic data, kept unmatched and untagged leads visible instead of dropping them, and wrote down why platform and CRM counts differ. I want to be the person who decides where the next rupee of spend goes based on customers, not clicks."

Next steps

Run your resume through the free skill check. For structured SQL, Power BI and AI-verification practice on marketing data, see the Data Analyst programme; a counsellor can share current batch dates. If your work is closer to organic search, the SEO specialist guide uses Search Console data instead.