Introduction

In this example we’ll be demonstrating an autonomous process that utilizes data from the database, to generate a deep, actionable analysis of marketing campaign performance. This specific example is using television metrics data focused on TV show campaigns to uncover insights about campaign engagement, audience reach, and cost efficiency.

Below you can find the structure of an autonomous process and where it says “User Prompt you can copy and paste the structure for reference.

​

User Prompt For The Example Above

​

You can copy and paste the structure below for reference.

Step 1: Use the _run_query function to run the SQL note marty-4x9g3v_dcr_report_results_d9pq3i.

Step 2: Parse metrics (impressionsCount, clicksCount, conversionsCount) from RESULTS and calculate:
- Click-Through Rate (CTR)
- Conversion Rate

Step 3: Join parsed data with "GENESIS_BOTS_ALPHA"."MARTY_4X9G3V_WORKSPACE"."TELEVISON_METRICS" to get TV metrics.

Step 4: Sort results by report_name and highlight the best-performing campaigns.

Step 5: Include TOTAL_VIEWERSHIP, TARGET_DEMOGRAPHIC_REACH, TIME_SLOT_RATINGS, CPM, and TV_SHOW_NAMES and then sort the final output by report_name in ascending order and show it as a list.
- Share the results and always return the results as a list.

Step 6: Objective: Using the analysis, compose a concise, engaging email summarizing the campaign performance of TV shows, and offer actionable insights.
- Execution: Draft the email using the following structure:
  - Subject line: Craft a catchy subject line with Unicode emojis for TV show campaigns (e.g., "TV Show Campaign Highlights: What’s Winning and What’s Not").
  Body: 
  - Start with a brief intro, summarizing the purpose of the email. 
  - Present a data-driven analysis: include TOTAL_VIEWERSHIP, TARGET_DEMOGRAPHIC_REACH, TIME_SLOT_RATINGS, CPM, and TV_SHOW_NAMES. 
  - List Top 3 performing campaigns: Include their key metrics and why they performed well using the data. 
  - List the 3 underperforming campaigns: Analyze what went wrong and provide suggested improvements using the data. 
  - Include proactive next steps based on the data analysis, such as optimizing the budget, testing different audience segments, or adjusting creative content. 
  - Call-to-action: Invite the user to discuss the details via Streamlit or Slack, referencing the Process ID for easy tracking. 
  - Line Breaks: Ensure clarity by including line breaks (\n) where needed.
  Closing: 
  Always end with your name or signature.

Step 7: Send the Email 
- Objective: Ensure the email reaches the recipient. 
- Execution: Send the drafted email to kevin.jong@genesiscomputing.ai 

​

Key Objectives of the Workflow

The process showcases the potential of leveraging Genesis as a central hub to execute, analyze, and summarize complex marketing campaigns—saving time and improving outcomes.

​

Step-by-Step Walkthrough

​

Retrieving and Analyze Campaign Data From The Database

The first step is accessing campaign metrics stored in a Data Clean Room (DCR) environment. These metrics served as the foundation for subsequent analysis.

Action:

1. A prebuilt SQL Note, marty-4x9g3v_dcr_report_results_d9pq3iwas executed using the _run_query tool.

2. This query fetched impressions, clicks, and conversions for campaigns tagged by their respective quarters (e.g., 2024Q2, 2024Q1).

3. To evaluate campaign performance, two critical metrics were calculated Click-Through Rate (CTR) and Conversion Rate (CR) they were processed through Python to include quarter-wise CTR and Conversion Rate for easy comparison.

Reasoning: This ensures that key marketing performance indicators are aggregated across multiple quarters for trend analysis.

​

Enriching Campaign Data with TV Metrics

Objective: To contextualize marketing performance, campaign data from the Data Clean Room was enriched by joining it with TV viewership metrics. This provided a richer dataset exploring audience reach and engagement alongside campaign metrics.

Action:

1. Integrated campaign data with GENESIS_BOTS_ALPHA.MARTY_4X9G3V_WORKSPACE.TELEVISON_METRICS, which contained: - TOTAL_VIEWERSHIP: Number of viewers per quarter. - TARGET_DEMOGRAPHIC_REACH: Percentage of target audience reached. - TIME_SLOT_RATINGS: Performance of TV time slots. - CPM: Cost-per-thousand impressions (cost efficiency). - TV_SHOW_NAMES: Associated TV campaigns.

2. Used SQL to execute a JOIN, linking campaign performance with TV metrics via the QUARTER attribute.

Reasoning: Using the campaign data from the Data Clean Room + the television metrics data from the database allowed for a comprehensive analysis. Breaking down which campaigns reached the desired demographics, Cost-effectiveness via CPM and Popular TV shows, and their time slots.

​

E-mail Insights Evaluating Campaign Highlights

Objective: The enriched data was sorted by Quarter, and the top-performing and underperforming campaigns were analyzed based on CTR and Conversion Rate.

• Top-Performing Campaigns: High CTR, Conversion Rate, and Viewership.
• Underperforming Campaigns: Low engagement rates and time slot inefficiencies.

Finally, the results were summarized in an actionable email tailored for stakeholders. This email provided:

• A concise overview of top and bottom performers.
• Actionable insights for improvement (e.g., focusing budget on demographics, creative testing).
• A call-to-action to discuss detailed results.

Action: Insights were synthesized into a visually engaging email using the following elements: Subject Line: Highlight of successes and room for improvement. Body Content: Data-driven breakdown, high-performance analysis, underperformance insights, and actionable recommendations. Call-to-Action: Invited stakeholders to dive deeper into Slack/Streamlit discussions.

​

Tools Used and Their Purposes

Purpose: 
This tool initiated and followed the process workflow (e.g., "Annual Show Analysis"). It was used to execute a series of predefined steps, ensuring that tasks were completed in order without manual intervention.
Usage: 
1. This tool was used to coordinate all steps of the workflow and ensured dependencies between steps were respected.
2. Managed iterative tasks like retrieving results, enriching data, and reporting by calling other tools. 

Purpose: 
Executed SQL queries against the database to extract campaign data (impressions, clicks, conversions) and later enrich them with TV metrics (viewership, ratings, cost efficiency). Allowing precision in extract-transform-load (ETL) operations.
Usage:
1. Retrieved campaign performance data from a Data Clean Room.
2. Performed an inner join to unify multiple datasets, creating an enriched view of campaign and TV metrics.
3. Automated access to organized datasets, facilitating further calculations and reporting.

Purpose:
Identified relevant tables and schemas (e.g., "TELEVISON_METRICS") to join with smaller campaign datasets.
Usage:
1. Ensured the correct datasets were located and verified when integrating TV metrics.
2. Provided a seamless way to navigate schemas, minimizing errors or incorrect source selection.

Purpose: 
Created and sent a detailed email summarizing the campaign performance to stakeholders.
Usage:
1. Automated the reporting process, eliminating manual summarization.
2. Used processed data to input insights directly into an actionable communication format (email).
3. Ensured stakeholders received structured findings alongside recommendations, complete with drill-down details.

​

How These Tools Worked Together

1. Process Coordination:
   • _run_process was the backbone, dictating the sequence of actions like querying, enriching, analyzing, and reporting. Each step triggered auxiliary tools for specific tasks.
2. Data Extraction:

• _run_query fetched raw performance data from a Snowflake database where campaigns were logged.
  • search_metadata and get_full_table_details ensured that the right companion datasets (like TV metrics) were accurately identified and structured for enrichment.

1. Metric Calculation (Python):
   • Python calculated performance indicators (CTR and Conversion Rate), combining extracted results with campaign metrics for advanced statistical and visual insights.
   • Achieved deep sorting (e.g., by CTR or campaign efficiency) and prepared data for precise presentation.
2. Data Enrichment:
   • A combination of SQL manipulation and extracted data joined multiple datasets (campaign + TV metrics) based on QUARTER.
   • Metrics like viewership, demographic reach, and ratings enhanced the depth of analysis.
3. Reporting (_send_email):
   • All processed data fed into _send_email to auto-compose an informative and engaging report.
   • Seamlessly integrated calculations, enrichment, and key highlights into actionable summaries.

​

How Notebook Tools Were Used and Their Purpose

​

1. Executing Pre-Saved Notes (SQL and Snowpark Python)

• Purpose:
  • The workflow leveraged preconfigured SQL notes to streamline repetitive query execution and Snowpark Python notes for advanced data calculations and transformations.
• How It Contributed:
  • Saved SQL notes eliminated the need to manually write or adjust queries for fetching campaign data. For instance:
    • The SQL note retrieved campaign metrics (impressions, clicks, and conversions) directly from the Data Clean Room.
    • This note was called using the _run_query function, ensuring a consistent and reusable approach for database extraction.
  • These notes enabled programmatic calculations, such as deriving CTR and Conversion Rates, handling sorting, and identifying top-performing or underperforming campaigns. These notes utilized Snowflake’s Snowpark Python to execute in the same environment, ensuring tight integration with the data.