hologres-ad-campaign

Prerequisites

This skill requires hologres-cli to be installed first:

pip install hologres-cli
export HOLOGRES_SKILL=hologres-ad-campaign

All SQL execution and Dynamic Table operations depend on hologres-cli commands (hologres sql run --write, hologres dt create).

Hologres Ad Creative Generation and Delivery Analysis

All-SQL pipeline: from OSS raw assets to ad video generation, virtual delivery simulation, and real-time ROI analysis.

Core Process

素材准备 → 主题图片 → 分镜脚本 → 视频合成 → 虚拟投放 → 实时ROI → 策略建议
(SQL)     (AI)       (AI)       (AI)       (SQL)      (DT)      (AI)

Models Used：

Model Type	task Field	Purpose	Model Name
Image Generation	image-generation	Theme-style images	qwen-image-2_0-pro
Text Generation	chat/completions	Storyboard script	qwen3_5-plus
Video Generation	video-generation	Ad video	wan2_6-r2v-flash

Prerequisites：

• Hologres instance V3.2+, with the above AI models deployed
• OSS Bucket for storing assets, with RAM role authorization configured
• Raw assets (product images, character images, etc.) have been uploaded to OSS

Step 1: Verify Models

SELECT model_name, model_type, model_provider, task FROM list_external_models();

Confirm that image-generation、chat/completions、video-generation models of the three task types exist.

Step 2: Collect Information

Collect the following information from the user (skip any already provided):

Information Item	Description	Example
Product Name	Product/game/service name	"Arrow Tower Defends Hanzhong"
Product Introduction	2-3 sentence description	"A relaxed and casual Three Kingdoms tower defense game..."
User Motivation	Core selling point	"Strategy tower defense"
Visual Style	Visual style description	"Flat, Chinese style, minimalist figures"
Industry Type	Game/E-commerce/Education/App/Other	Game
OSS Bucket	Bucket for storing assets	"hologres-dataclaw-oss"
OSS Region	Regional endpoint	"oss-cn-hangzhou-internal.aliyuncs.com"
RAM Role ARN	Role for accessing OSS	"acs:ram::role/xxx"
Asset Path List	Asset paths in OSS	oss://bucket/game/hero1.png
Number of Styles	How many styles to generate (default 1, maximum 4)	1
Video Duration	Seconds (default 10, options 5/10/15/30)	10

Output Directory Conventions：

• Asset path:oss://game/base_images/hero1.png
• Generated images:oss://game/generated_images/<style_name>/
• Generated videos:oss://game/generated_videos/<style_name>/

Step 3: Generate SQL and Execute

3.1 Create Business Material Table

CREATE TABLE IF NOT EXISTS product_info(
    name TEXT PRIMARY KEY,
    intro TEXT,
    motivation TEXT,
    art_style TEXT,
    material_list TEXT[]
);

INSERT INTO product_info (name, intro, motivation, art_style, material_list)
VALUES ('产品名称', '产品介绍', '用户动机', '视觉风格',
        ARRAY['素材路径1', '素材路径2'])
ON CONFLICT (name) DO UPDATE SET
    intro = EXCLUDED.intro, motivation = EXCLUDED.motivation,
    art_style = EXCLUDED.art_style, material_list = EXCLUDED.material_list;

3.2 Create Style Prompt Table

Select a style based on the industry type; see references/style-templates.md。

CREATE TABLE IF NOT EXISTS video_style(
    name TEXT PRIMARY KEY,
    prompt TEXT
);

INSERT INTO video_style VALUES
('风格名称', '风格提示词...')
ON CONFLICT (name) DO UPDATE SET prompt = EXCLUDED.prompt;

3.3 Generate Theme Images

For detailed SQL, see references/sql-templates.md。

-- 每个风格单独执行，不要批量
-- 核心调用：ai_gen('qwen-image-2_0-pro', json_build_object(...))
-- 结果存入 generated_images 表

3.4 Generate Storyboard Script + Compose Video

For detailed SQL, see references/sql-templates.md。

-- 分镜 + 视频一次生成（每个风格单独执行）
-- 1. ai_gen('qwen3_5-plus', ...) 生成分镜脚本
-- 2. ai_gen('wan2_6-r2v-flash', ...) 合成视频
-- 视频 URL 在 video_result 的 output.video_url 字段

3.5 Virtual Delivery Simulation

For detailed SQL, see references/virtual-delivery.md。

-- 1. 创建渠道配置表 channel_config（微信/抖音/小红书/B站）
-- 2. 创建投放日志表 ad_campaign_logs
-- 3. 执行虚拟投放：模拟曝光/点击/转化事件

3.6 Real-time ROI Analysis

-- 创建 Dynamic Table（自动刷新，延迟<1分钟）
CREATE DYNAMIC TABLE IF NOT EXISTS dt_campaign_performance
WITH (auto_refresh_enable = true, freshness = '1 minutes', refresh_mode = 'incremental')
AS SELECT channel, style_name,
    COUNT(CASE WHEN event_type = 'impression' THEN 1 END) as impressions,
    COUNT(CASE WHEN event_type = 'click' THEN 1 END) as clicks,
    COUNT(CASE WHEN event_type = 'conversion' THEN 1 END) as conversions,
    SUM(cost) as total_cost, SUM(revenue) as total_revenue,
    CASE WHEN SUM(cost) > 0 THEN ROUND(SUM(revenue)/SUM(cost), 2) ELSE 0 END as roi
FROM ad_campaign_logs GROUP BY channel, style_name;

-- 查询实时指标
SELECT * FROM dt_campaign_performance ORDER BY roi DESC;

3.7 AI Strategy Recommendations

For detailed SQL, see references/sql-templates.md。

-- 调用 qwen3_5-plus 分析投放数据，输出：
-- 1. ROI 冠军组合
-- 2. 潜力股分析
-- 3. 止损建议
-- 4. 预算分配建议

Execution Guidance

⚠️ Rate Limiting Warning：ai_gen Calling cloud models is prone to rate limiting; image and video generation must be called one by one.

⚠️ Tool Isolation: DDL and DML cannot be executed in the same tool call; they must be separated.

Execution order：

1. execute_hg_ddl_sql: CREATE TABLE → execute_hg_dml_sql: INSERT
2. Generate images style by style → compose videos style by style
3. Create delivery table → execute virtual delivery
4. Create Dynamic Table → wait 1 minute → query metrics → AI analysis

Reference documents

Document	Content
sql-templates.md	Complete SQL templates (images/videos/analysis)
virtual-delivery.md	Virtual delivery simulation + ROI analysis
style-templates.md	Industry style prompt templates

Notes

1. ai_gen Calling the cloud-based large model to generate may take a relatively long time
2. wan2_6-r2v-flash Supports 5/10/15/30 second multi-shot videos, optionally with audio
3. When generation fails, check: RAM role permissions, OSS Bucket, model deployment status
4. Model names may vary slightly by version; refer to list_external_models() for the authoritative version
5. Use ON CONFLICT DO UPDATE to implement idempotent insertion
6. The video URL is a signed temporary OSS link; keep it intact when displaying (including the Expires and Signature parameters)
7. Dynamic Table has a maximum delay of 1 minute; wait a moment after simulation before querying