I'm building a recurring process where we check the same prompt set every month and compare brand mentions, citations, and share of voice. I don't want to do this in spreadsheets forever. What's a good setup?

A good setup is usually:

1) Keep the prompts versioned and fixed

Store your monthly prompt set in something like:

• a Git repo
• a database table
• or even a structured JSON/YAML file

Key point: don’t edit prompts in place without versioning.
If you change wording, your month-over-month comparisons get noisy.

Recommended fields:

• prompt_id
• prompt_text
• category / theme
• version
• active
• created_at

---

2) Run each prompt on a schedule

Use a workflow tool to run the same set every month:

• Airflow if you want a proper data pipeline
• Prefect if you want something lighter and easier to start
• dbt + scheduler if the work is mostly SQL/transforms
• cron + Python if it’s simple and internal

The job should:

1. load the prompt set
2. execute the prompt against your model/search workflow
3. save raw outputs
4. extract structured metrics

---

3) Save raw outputs and normalized metrics separately

This is the part that saves you later.

Raw results table

Store the full response for auditability:

• prompt_id
• run_date
• model/version
• raw_output
• input_context
• response_latency
• status

Metrics table

Store parsed, queryable metrics:

• prompt_id
• run_date
• brand_mentions
• citations_count
• citation_sources
• share_of_voice
• rank
• sentiment
• notes

That way you can always:

• reprocess old outputs if your metric logic changes
• inspect weird results
• compare across time cleanly

---

4) Put the data in a real warehouse or DB

Instead of spreadsheets, use:

• Postgres if the volume is moderate
• BigQuery / Snowflake / Redshift if you expect scale or want BI integration

Then connect:

• Metabase
• Looker Studio
• Power BI
• Tableau

This gives you dashboards for:

• month-over-month brand mentions
• citation trends
• share of voice by theme
• prompt-level breakdowns

---

5) Define the metric logic once

You’ll want deterministic rules for things like:

• what counts as a brand mention
• how citations are detected
• how you calculate share of voice
• whether variants/aliases count

Examples:

• “Acme”, “Acme Inc.”, and “ACME” all map to the same brand
• citations = links, footnotes, or explicit source references
• share of voice = brand mentions / total mentions in the prompt set

Keep these in code, not in spreadsheet formulas.

---

6) Add a review layer for edge cases

For brand/citation analysis, some outputs will be ambiguous. A good setup includes:

• a “needs review” flag
• a small QA dashboard
• the ability to override classifications

This helps with:

• false positives in brand detection
• incomplete citations
• model drift over time

---

7) Suggested practical stack

If you want something simple but solid:

Starter stack

• Prompt definitions: YAML in Git
• Orchestration: Prefect or cron
• Storage: Postgres
• Transformations: Python/pandas or SQL
• Dashboard: Metabase

More scalable stack

• Prompt definitions: Git + config service
• Orchestration: Airflow
• Storage: BigQuery/Snowflake
• Transforms: dbt
• Dashboard: Looker/Tableau

---

8) A good table structure

Example:

prompts

• prompt_id
• prompt_text
• category
• version
• active

runs

• run_id
• run_month
• prompt_id
• model_name
• model_version
• raw_output
• created_at

metrics

• run_id
• brand_name
• mentioned
• citation_count
• share_of_voice
• confidence
• review_status

---

Recommendation

If you’re moving off spreadsheets, I’d start with:

• Git/YAML for prompts
• Prefect or cron for monthly runs
• Postgres for storage
• Metabase for dashboards

That’s usually the fastest path to something durable without overengineering.

If you want, I can sketch:

1. a minimal architecture diagram,
2. a database schema, or
3. a Python/Prefect example for the monthly job.