Pages List
List view
Marketing AI Performance Leaderboard - June 2025 Results
Data Analysis Prompts
Customer Journey Analysis
**Prompt to the LLM (Customer Journey Trend Analysis):** You are a data-savvy marketing analyst. A CSV (30 rows) will be provided with two columns: - `customer_id` - `journey` (a “ → ”-delimited list of channel events with ISO dates) **CSV Input:** ```csv customer_id,journey 1,"Retargeting Ad (2025-03-25) → Product Page (2025-03-26) → Referral (2025-03-28) → Blog (2025-03-30) → Demo (2025-03-31) → Email (2025-04-02) → Whitepaper Download (2025-04-03) → Paid Social (2025-04-05) → Conversion (2025-04-06)" 2,"Organic Search (2025-03-25) → Paid Search (2025-03-27) → Product Page (2025-03-28) → Email (2025-03-29) → Webinar (2025-03-30) → Blog (2025-04-01) → Demo (2025-04-02) → Referral (2025-04-03) → Conversion (2025-04-04)" 3,"Paid Search (2025-03-25) → Referral (2025-03-26) → Blog (2025-03-28) → Organic Social (2025-03-30) → Webinar (2025-04-01) → Product Page (2025-04-02) → Retargeting Ad (2025-04-04) → Email (2025-04-05) → Conversion (2025-04-06)" 4,"Direct (2025-03-25) → Paid Social (2025-03-25) → Webinar (2025-03-26) → Demo (2025-03-28) → Email (2025-03-29) → Product Page (2025-03-30) → Blog (2025-03-31) → Referral (2025-04-01) → Conversion (2025-04-02)" 5,"Paid Search (2025-03-25) → Direct (2025-03-26) → Blog (2025-03-27) → Webinar (2025-03-29) → Product Page (2025-03-30) → Email (2025-04-01) → Retargeting Ad (2025-04-02) → Organic Social (2025-04-04) → Conversion (2025-04-05)" 6,"Referral (2025-03-25) → Organic Social (2025-03-27) → Product Page (2025-03-28) → Email (2025-03-29) → Blog (2025-03-30) → Paid Social (2025-04-01) → Webinar (2025-04-03) → Demo (2025-04-04) → Conversion (2025-04-05)" 7,"Webinar (2025-03-25) → Blog (2025-03-26) → Paid Search (2025-03-27) → Email (2025-03-28) → Product Page (2025-03-30) → Organic Search (2025-04-01) → Demo (2025-04-03) → Direct (2025-04-04) → Conversion (2025-04-05)" 8,"Blog (2025-03-25) → Webinar (2025-03-26) → Email (2025-03-27) → Referral (2025-03-29) → Demo (2025-03-30) → Paid Search (2025-04-01) → Product Page (2025-04-03) → Organic Social (2025-04-04) → Conversion (2025-04-06)" 9,"Email (2025-03-25) → Paid Social (2025-03-26) → Webinar (2025-03-27) → Referral (2025-03-28) → Product Page (2025-03-30) → Demo (2025-04-01) → Blog (2025-04-02) → Direct (2025-04-03) → Conversion (2025-04-04)" 10,"Direct (2025-03-25) → Referral (2025-03-26) → Paid Search (2025-03-28) → Webinar (2025-03-29) → Product Page (2025-03-30) → Organic Social (2025-04-01) → Email (2025-04-02) → Demo (2025-04-03) → Conversion (2025-04-04)" 11,"Paid Social (2025-03-25) → Organic Search (2025-03-26) → Product Page (2025-03-28) → Blog (2025-03-29) → Email (2025-03-30) → Webinar (2025-04-01) → Demo (2025-04-02) → Referral (2025-04-04) → Conversion (2025-04-05)" 12,"Referral (2025-03-25) → Blog (2025-03-26) → Product Page (2025-03-27) → Direct (2025-03-29) → Email (2025-03-30) → Paid Social (2025-04-01) → Webinar (2025-04-02) → Demo (2025-04-03) → Conversion (2025-04-04)" 13,"Email (2025-03-25) → Product Page (2025-03-27) → Referral (2025-03-28) → Organic Social (2025-03-29) → Blog (2025-03-30) → Webinar (2025-04-01) → Demo (2025-04-03) → Paid Search (2025-04-04) → Conversion (2025-04-05)" 14,"Organic Social (2025-03-25) → Webinar (2025-03-27) → Blog (2025-03-28) → Email (2025-03-30) → Direct (2025-03-31) → Paid Social (2025-04-01) → Demo (2025-04-02) → Referral (2025-04-03) → Conversion (2025-04-04)" 15,"Whitepaper Download (2025-03-25) → Email (2025-03-27) → Paid Search (2025-03-28) → Organic Social (2025-03-29) → Webinar (2025-03-30) → Product Page (2025-04-01) → Demo (2025-04-02) → Direct (2025-04-03) → Conversion (2025-04-05)" 16,"Webinar (2025-03-25) → Email (2025-03-26) → Organic Social (2025-03-27) → Product Page (2025-03-28) → Demo (2025-03-30) → Referral (2025-04-01) → Blog (2025-04-02) → Direct (2025-04-03) → Conversion (2025-04-04)" 17,"Referral (2025-03-25) → Demo (2025-03-26) → Product Page (2025-03-28) → Email (2025-03-29) → Paid Social (2025-03-30) → Organic Search (2025-04-01) → Webinar (2025-04-02) → Blog (2025-04-03) → Conversion (2025-04-04)" 18,"Direct (2025-03-25) → Paid Social (2025-03-27) → Email (2025-03-28) → Webinar (2025-03-29) → Product Page (2025-03-30) → Referral (2025-04-01) → Demo (2025-04-02) → Blog (2025-04-03) → Conversion (2025-04-04)" 19,"Blog (2025-03-25) → Webinar (2025-03-26) → Product Page (2025-03-27) → Organic Search (2025-03-29) → Email (2025-03-30) → Demo (2025-04-01) → Referral (2025-04-02) → Paid Social (2025-04-03) → Conversion (2025-04-04)" 20,"Whitepaper Download (2025-03-25) → Direct (2025-03-26) → Blog (2025-03-28) → Paid Social (2025-03-29) → Webinar (2025-03-30) → Product Page (2025-04-01) → Demo (2025-04-02) → Referral (2025-04-03) → Conversion (2025-04-04)" 21,"Referral (2025-03-25) → Email (2025-03-26) → Paid Search (2025-03-28) → Organic Social (2025-03-29) → Product Page (2025-03-30) → Demo (2025-04-01) → Webinar (2025-04-02) → Blog (2025-04-03) → Conversion (2025-04-05)" 22,"Email (2025-03-25) → Referral (2025-03-26) → Product Page (2025-03-27) → Blog (2025-03-29) → Organic Social (2025-03-30) → Webinar (2025-04-01) → Paid Social (2025-04-02) → Demo (2025-04-03) → Conversion (2025-04-04)" 23,"Webinar (2025-03-25) → Organic Search (2025-03-27) → Email (2025-03-28) → Referral (2025-03-30) → Blog (2025-03-31) → Product Page (2025-04-01) → Demo (2025-04-02) → Paid Search (2025-04-03) → Conversion (2025-04-04)" 24,"Paid Social (2025-03-25) → Direct (2025-03-26) → Product Page (2025-03-27) → Blog (2025-03-28) → Email (2025-03-30) → Webinar (2025-04-01) → Referral (2025-04-02) → Demo (2025-04-03) → Conversion (2025-04-04)" 25,"Organic Search (2025-03-25) → Paid Search (2025-03-26) → Blog (2025-03-27) → Email (2025-03-28) → Referral (2025-03-30) → Webinar (2025-03-31) → Demo (2025-04-01) → Product Page (2025-04-02) → Conversion (2025-04-03)" 26,"Demo (2025-03-25) → Blog (2025-03-26) → Product Page (2025-03-28) → Email (2025-03-29) → Webinar (2025-03-30) → Referral (2025-03-31) → Organic Social (2025-04-01) → Paid Social (2025-04-02) → Conversion (2025-04-03)" 27,"Whitepaper Download (2025-03-25) → Blog (2025-03-26) → Organic Social (2025-03-28) → Product Page (2025-03-29) → Email (2025-03-30) → Webinar (2025-03-31) → Paid Social (2025-04-01) → Referral (2025-04-02) → Conversion (2025-04-03)" 28,"Referral (2025-03-25) → Product Page (2025-03-26) → Blog (2025-03-28) → Paid Social (2025-03-29) → Email (2025-03-30) → Webinar (2025-03-31) → Demo (2025-04-01) → Organic Search (2025-04-02) → Conversion (2025-04-03)" 29,"Webinar (2025-03-25) → Blog (2025-03-26) → Email (2025-03-27) → Referral (2025-03-28) → Organic Social (2025-03-30) → Product Page (2025-03-31) → Demo (2025-04-01) → Paid Search (2025-04-02) → Conversion (2025-04-03)" 30,"Email (2025-03-25) → Organic Search (2025-03-26) → Referral (2025-03-28) → Blog (2025-03-29) → Product Page (2025-03-30) → Webinar (2025-03-31) → Paid Social (2025-04-01) → Demo (2025-04-02) → Conversion (2025-04-03)" ``` ### Tasks 1. **Parse & Structure** - Convert each `journey` into a list of events: ```json { "customer_id": 1, "events": [ { "channel": "Email", "date": "2025-04-01" }, … ] } ``` 2. **Trend Summary** - Count frequency of first-touch, mid-funnel, and last-touch channels. - Present as a markdown table. 3. **Key Conversion Moments** - Identify which channel most often immediately precedes “Conversion.” - Compute average days between that touch and conversion. 4. **Sub-Pattern Detection** - Highlight any sequence of two channels that appears in ≥30% of journeys. 5. **Recommendations** - Provide 2–3 concrete marketing actions (e.g., “Add an Email drip 24 hrs after Paid Search”) based on your findings. ### Output Format - **Journey Breakdown:** JSON list as above - **Trend Table:** Markdown table with channel vs. counts - **Key Conversion Moments:** Text + numeric result - **Sub-Patterns:** Bullet list - **Recommendations:** Numbered list
Marketing ROI Attribution
**Prompt to the LLM (Regression‐Based Channel Attribution & ROI Analysis with Multi‐Channel Simulation):** You are an expert data scientist and marketing analyst. You will be provided with: 1. **Customer Data CSV**: customer‐level channel touch counts and revenue. 2. **Channel Spend CSV**: total spend per channel for the same period. Your task is to: - Build a regularized regression model to estimate each channel’s marginal contribution to revenue. - Simulate “turning off” each channel one by one, quantify the revenue impact, and recalculate ROI given the channel spends. Please write executable Python code (using pandas and scikit-learn), and output the requested metrics. --- ## Input 1. **Customer Data CSV**: ```csv customer_id,paid_search_count,organic_search_count,email_count,social_count,revenue 1,5,3,5,5,614 2,0,1,3,6,969 3,0,3,4,2,565 4,5,4,0,1,1146 5,2,2,3,2,1321 6,6,6,5,2,1154 7,0,3,2,4,1133 8,1,2,4,3,939 9,5,6,4,5,1766 10,5,1,0,1,1942 11,4,3,3,3,592 12,5,5,0,3,805 13,0,3,4,0,1941 14,2,3,6,6,553 15,2,0,2,6,1762 16,6,0,5,0,1751 17,1,1,4,0,968 18,4,5,2,1,1085 19,6,4,2,0,1703 20,1,0,3,1,1180 21,6,2,2,6,1257 22,3,4,4,1,1749 23,0,0,5,5,1091 24,2,2,2,2,1124 25,4,2,4,6,1650 26,5,4,1,2,953 27,6,2,2,1,1774 28,2,5,6,4,1085 29,2,3,3,3,1450 30,3,2,0,0,1211 ``` 2. **Channel Spend CSV** (`channel_spend.csv`): ```csv channel,spend paid_search,10000 organic_search,5000 email,2000 social,1000 ``` - *_count columns* are counts of touches per customer. - *revenue* is total revenue attributed per customer. - *spend* is the total channel investment over the same period. --- ## Tasks 1. **Load & Prepare Data** - Read `customer_data.csv` into a pandas DataFrame `df_cust`. - Read `channel_spend.csv` into `df_spend`. - Define feature matrix **X** from the four `*_count` columns, and target vector **y** = `revenue`. 2. **Fit Regularized Regression** - Use scikit-learn’s `RidgeCV` l2-norm on **X** → **y** to train the model. 3. **Report Coefficients & Baseline Metrics** - Print each channel’s coefficient (`β_paid_search`, etc.). - Predict baseline revenue `y_pred_base = model.predict(X)` and compute **Baseline Revenue** = `sum(y_pred_base)`. - Compute **Baseline Total Spend** = `df_spend['spend'].sum()`. - Calculate **Baseline ROI** = Baseline Revenue − Baseline Total Spend. 4. **Simulate Turning Off Each Channel** For each channel `ch` in `['paid_search','organic_search','email','social']`: - Create `X_no = X.copy()` and set `X_no[f"{ch}_count"] = 0`. - Predict revenue `y_pred_no = model.predict(X_no)` and compute **Revenue without ch** = `sum(y_pred_no)`. - **Spend Saved** = spend for that channel from `df_spend` (where `channel == ch`). - **Revenue Loss** = Baseline Revenue − Revenue without ch. - **ROI without ch** = Revenue without ch − (Baseline Total Spend − Spend Saved). - **ΔROI for ch** = ROI without ch − Baseline ROI. 5. **Output Results** - Print a summary table: | Channel | Coefficient | Baseline Rev | Rev w/o Channel | Rev Loss | Spend Saved | ROI w/o Channel | ΔROI | |----------------|-------------|--------------|-----------------|----------|-------------|-----------------|--------| | Paid Search | β_paid_search | \$R_base | \$R_no_ps | \$ΔR_ps | \$S_ps | \$ROI_no_ps | \$ΔROI_ps | | Organic Search | β_organic_search | \$R_base | \$R_no_os | \$ΔR_os | \$S_os | \$ROI_no_os | \$ΔROI_os | | Email | β_email | \$R_base | \$R_no_em | \$ΔR_em | \$S_em | \$ROI_no_em | \$ΔROI_em | | Social | β_social | \$R_base | \$R_no_sm | \$ΔR_sm | \$S_sm | \$ROI_no_sm | \$ΔROI_sm | - Include clear comments in your code and a brief interpretation of which channel’s removal most improves or harms net ROI. --- Please generate the complete Python code to perform this analysis, with comments, and then print the resulting table and key takeaways.
PPC Campaign Analysis
**Prompt to the LLM (Paid‐Ads Performance Analysis & Optimization):** > You are a senior paid‑media analyst. You will be provided with two inputs: > 1. **Search‑term Export (CSV):** A Google Ads (or other platform) export listing campaigns, search terms (or keywords), impressions, clicks, conversions, and cost metrics. ```csv Search term,Match type,Added/Excluded,Campaign,Ad group,Clicks,Impr.,CTR,Currency code,Avg. CPC,Cost,Campaign type,Conv. rate,Conversions,Cost / conv. tax agent australia,Broad match,None,Accounting - Search,Ad group 1,1,3,33.33%,AUD,0.96,0.96,Search,0.00%,0.00,0.00 how to find sponsor in australia,Broad match,None,General BPO - Search,Ad group 1,1,4,25.00%,AUD,0.62,0.62,Search,0.00%,0.00,0.00 jobs in australia for indian,Broad match,Excluded,General BPO - Search,Ad group 1,3,12,25.00%,AUD,0.60,1.79,Search,0.00%,0.00,0.00 agency in australia for jobs,Broad match,None,General BPO - Search,Ad group 1,2,8,25.00%,AUD,0.87,1.73,Search,0.00%,0.00,0.00 va australia,Exact match (close variant),None,VA - Search,Ad group 1,1,3,33.33%,AUD,0.70,0.70,Search,0.00%,0.00,0.00 femia accountants,Broad match,None,Accounting - Search,Ad group 1,1,6,16.67%,AUD,0.98,0.98,Search,0.00%,0.00,0.00 australia mines jobs,Broad match,None,General BPO - Search,Ad group 1,1,7,14.29%,AUD,0.71,0.71,Search,0.00%,0.00,0.00 australia it jobs,Broad match,None,General BPO - Search,Ad group 1,1,11,9.09%,AUD,0.63,0.63,Search,0.00%,0.00,0.00 jobaroo australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.67,0.67,Search,0.00%,0.00,0.00 agency for australia,Phrase match (close variant),None,General BPO - Search,Ad group 1,2,7,28.57%,AUD,0.73,1.46,Search,0.00%,0.00,0.00 jobsearch australia,Broad match,Excluded,General BPO - Search,Ad group 1,2,6,33.33%,AUD,0.61,1.21,Search,0.00%,0.00,0.00 trabalhos australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.67,0.67,Search,0.00%,0.00,0.00 scale x,Broad match,None,General BPO - Search,Ad group 1,1,5,20.00%,AUD,0.74,0.74,Search,0.00%,0.00,0.00 adecco australia contact,Broad match,None,General BPO - Search,Ad group 1,1,2,50.00%,AUD,0.91,0.91,Search,0.00%,0.00,0.00 how to get jobs in australia from india,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.63,0.63,Search,0.00%,0.00,0.00 executive search australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.75,0.75,Search,0.00%,0.00,0.00 office job in australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.63,0.63,Search,0.00%,0.00,0.00 konnecting,Broad match,None,IT - Search,Ad group 1,1,63,1.59%,AUD,0.62,0.62,Search,0.00%,0.00,0.00 guichet emploi australie,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.58,0.58,Search,0.00%,0.00,0.00 php laravel developer jobs in australia,Broad match,None,IT - Search,Ad group 1,1,2,50.00%,AUD,0.90,0.90,Search,0.00%,0.00,0.00 it works in australia,Broad match,None,IT - Search,Ad group 1,1,2,50.00%,AUD,0.73,0.73,Search,0.00%,0.00,0.00 kh accounting,Broad match,None,Accounting - Search,Ad group 1,1,1,100.00%,AUD,1.00,1.00,Search,0.00%,0.00,0.00 michael page australia,Broad match,None,General BPO - Search,Ad group 1,1,16,6.25%,AUD,0.49,0.49,Search,0.00%,0.00,0.00 jobs in australia for indians,Broad match,Excluded,General BPO - Search,Ad group 1,9,32,28.13%,AUD,0.58,5.19,Search,0.00%,0.00,0.00 submitready pty ltd,Broad match,Excluded,Accounting - Search,Ad group 1,16,588,2.72%,AUD,0.72,11.51,Search,0.00%,0.00,0.00 australia job circular 2025,Broad match,Excluded,General BPO - Search,Ad group 1,2,2,100.00%,AUD,0.40,0.80,Search,0.00%,0.00,0.00 trabajo en australia para mexicanos 2025,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.58,0.58,Search,0.00%,0.00,0.00 hiring in australia,Broad match,None,General BPO - Search,Ad group 1,1,14,7.14%,AUD,0.63,0.63,Search,0.00%,0.00,0.00 ac technician job in australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.61,0.61,Search,0.00%,0.00,0.00 offshore job australia,Broad match,None,General BPO - Search,Ad group 1,1,2,50.00%,AUD,0.59,0.59,Search,0.00%,0.00,0.00 how to apply for fifo australia,Phrase match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.70,0.70,Search,0.00%,0.00,0.00 ozstaff australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,1.18,1.18,Search,0.00%,0.00,0.00 shopify expert gold coast,Broad match,None,IT - Search,Ad group 1,1,2,50.00%,AUD,0.61,0.61,Search,0.00%,0.00,0.00 rock it melbourne,Broad match,None,IT - Search,Ad group 1,1,6,16.67%,AUD,0.66,0.66,Search,0.00%,0.00,0.00 fifo australia mines,Phrase match,None,General BPO - Search,Ad group 1,1,14,7.14%,AUD,0.55,0.55,Search,0.00%,0.00,0.00 mechanical engineering jobs in australia for indian,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.49,0.49,Search,0.00%,0.00,0.00 australia va hiring,Exact match (close variant),None,General BPO - Search,Ad group 1,2,8,25.00%,AUD,0.68,1.36,Search,0.00%,0.00,0.00 how to apply for a job in australia,Broad match,None,General BPO - Search,Ad group 1,1,1,100.00%,AUD,0.54,0.54,Search,0.00%,0.00,0.00 companies in australia looking for foreign workers,Broad match,None,General BPO - Search,Ad group 1,1,4,25.00%,AUD,0.48,0.48,Search,0.00%,0.00,0.00 jobs outsourcing angel,Phrase match (close variant),None,VA - Search,Ad group 1,1,1,100.00%,AUD,0.95,0.95,Search,0.00%,0.00,0.00 ``` > 2. **Leads Table:** A table mapping active leads back to their originating PPC campaign and search term. | Month | Count of Contacts | |------------|-------------------| | Jan 2025 | 1 | | Feb 2025 | 6 | | Mar 2025 | 8 | | Apr 2025 | 2 | | May 2025 | 0 | | Create Date | Lifecycle Stage | Original Traffic Source | Drill-Down 1 | Drill-Down 2 | Associated Deals | |-------------|------------------|--------------------------|----------------------|-------------------------------------|------------------| | 13/3/2025 | Opportunity | Paid Search | legal - search | legal outsourcing services company | 1 | | 17/3/2025 | Opportunity | Paid Search | general bpo - search | offshore outsourcing | 1 | > Your task is to produce a unified performance analysis and optimization plan that is channel‑agnostic. Specifically, deliver: > > **A. Conversion Lift Actions** > 1. Identify the top 5 search terms (across all campaigns) with the highest cost‑per‑conversion. Recommend 3 concrete optimizations (e.g. bid adjustments, negative‑keyword additions, ad‑copy tests) to improve their conversion rates. > 2. Find any search terms or campaigns generating clicks but zero conversions. Propose 2 remedial actions for each (e.g. landing‑page tweaks, audience refinements). > > **B. ROI Calculation** > 1. Calculate total spend, total conversions, and overall cost‑per‑conversion. > 2. Using an assumed average lead value (you may infer from the leads table or use a placeholder), estimate total campaign ROI. > 3. Highlight any campaigns or terms with negative ROI (cost > value) and quantify the loss. > > **C. Budget Reallocation Strategy** > 1. Rank campaigns (or search‑term clusters) by ROI and by conversion rate. > 2. Recommend a reallocation of 20% of budget from the bottom‑performing channel/term(s) to the top‑performing ones. Show the projected change in conversions and ROI after reallocation (assume linear performance). > > **Instructions & Format** > - **Data Parsing:** Read the CSV; extract metrics. OCR or visually parse the leads‑table image to link leads to campaign/term performance. > - **Tables & Charts:** Present a summary table of key metrics (spend, conversions, cost/conv, ROI) and a bar chart placeholder for ROI by campaign. > - **Action Plan:** Under each section (A, B, C) list numbered findings and recommendations. > - **Channel‑Agnostic Language:** Use neutral terms (“PPC channel,” “search term”) so it works for Google, Bing, LinkedIn, etc. > - **Assumptions:** Clearly state any assumptions (e.g. average lead value). > > **Output Structure:** > ``` > ## A. Conversion Lift Actions > 1. [Term X] – cost/conv \$Y → Action: … > 2. … > > ## B. ROI Calculation > | Campaign/Channel | Spend | Conversions | Cost/Conv | Avg Lead Value | ROI | > |------------------|-------|-------------|-----------|----------------|-----| > | … | … | … | … | … | … | > > **Overall ROI:** … > > ## C. Budget Reallocation Strategy > - Move \$Z from [low‑ROI term] to [high‑ROI term]. > - Projected +N conversions, +M% ROI. > > **Assumptions:** > - Average lead value = \$… > > **Chart Placeholders:** >  > ``` > > Use this prompt to analyze any paid‑ads export CSV and corresponding leads image, then deliver clear, actionable recommendations.
Made with Bullet