Back to prompts
Code20.11.2026
Write a Python Script That Calculates Return on Ad Spend for Your Black Friday and Cyber Monday Campaigns From Your Own CSV Exports
#python#black friday#cyber monday#roas#ad spend#ecommerce#analytics#small business
You are a careful Python developer who helps small online stores understand whether their holiday ads actually paid off. After Black Friday and Cyber Monday, I want a script that compares what I spent on each ad campaign with the orders and revenue it brought in, and shows return on ad spend and profit after costs, so I can decide what to repeat in December. The script must work only on CSV files I export myself. It must not call any ad platform or store API, must not need passwords or keys, and must never change the original files. Attribution numbers from ad platforms are estimates, so the report must say which numbers come from the platform and which come from my store orders. If anything about my file format is unclear, mark it [NEEDS INPUT] instead of guessing column names. Ad platforms and the campaign report CSV columns I have (date, campaign name, spend, currency, clicks, platform reported conversions and conversion value): [AD CSV] Store orders CSV columns I have (order id, created time, total, currency, discount code, refunds, UTM source, UTM campaign, or landing page): [ORDERS CSV] How I link orders to campaigns (UTM campaign values, discount codes per campaign, or only platform reported conversions): [MATCH METHOD] Date range to analyze, and the time zone each file uses: [DATE RANGE AND TIME ZONES] My average product cost or gross margin percentage, plus shipping and payment fee estimates: [COSTS] Currencies involved and the exchange rate I want to use: [CURRENCIES] Python version and whether I can install pandas: [PYTHON SETUP] Requirements: 1. Write a single Python script with clear functions, type hints, and comments, that reads both CSVs, validates required columns, and stops with a readable error listing missing columns. 2. Normalize dates to one time zone from [DATE RANGE AND TIME ZONES], filter to the date range, and convert currencies using the rate from [CURRENCIES]. 3. Match orders to campaigns using [MATCH METHOD]. Keep unmatched orders in a separate group labeled "not attributed" instead of forcing them into a campaign. 4. Subtract refunds from revenue, and calculate per campaign: spend, attributed orders, attributed revenue, ROAS (revenue divided by spend), estimated gross profit after [COSTS], and profit after ad spend. 5. Show platform reported conversion value side by side with store matched revenue, and the difference, so I can see where the platform claims more than my store recorded. 6. Write the results to a new CSV and print a short summary table sorted by profit after ad spend, flagging campaigns that lost money. 7. Include a small sample dataset with fake but clearly labeled example rows and a simple test that checks ROAS and refunds are calculated correctly. 8. Add a short README section explaining how to export each file, how to run the script, and the limits of attribution, including that some buyers see several ads before ordering. Deliverables: 1. Complete Python script 2. Sample CSVs and test 3. Example output summary 4. README with run steps and attribution limits
How to use this prompt
- 1Copy the prompt using the button above.
- 2Open your AI tool (ChatGPT, Claude, Midjourney, etc.)
- 3Paste and customize any variables in the prompt.
- 4Generate your result.
Tip
Fill every placeholder before you run the prompt. Keep one clear goal per run, then refine with a follow-up if needed.
When to use this prompt
- •Good for first drafts in Code
- •Keep variables filled so the model has enough context
- •Review AI output before sending or publishing