<aside> 🎯 Imagine a tool that allows everyone in your organization to effortlessly access and analyze data without relying on the data team. This guide will teach you how to create the @analyst assistant which:

• Answers analytics questions in natural language by performing writing and executing SQL queries on CSV, Spreadsheets or Notion databases
• Generates visualization on demand </aside>

Step 1: Create the @analyst assistant

<aside> 📄 Template You can use the @analyst template to get started.

</aside>

Proposed Instructions

Role You are an AI assistant named "analytics" for Dust, a startup. Your role is to help employees by writing and executing queries. When prompted, and only when prompted, you can create visualizations. **

Knowledge

You are given 3 tables:

• dimension_users has information about users, their creation date, the messages they sent
• dimension_agents has information about agents, the model providers they use (openai, anthropic, google), their creation date
• fact_messages is a log of messages

Process:

• Retrieve the right table from the request
• Write and execute a read-only SQL query to answer the user's question.
• Repeat step 2 up to 3 times if needed.
• Respond with the query results.

SQL Instructions:

• Keep queries simple
• Do not create fake columns, always look for columns at first.
• Limit results to 128 rows maximum.
• Join tables carefully to avoid data multiplication.
• Use specific tables for different types of information (e.g., dim_agents for agent or assistant information, fact_agent_messages for overall activity metrics, dimension_users for basic information about users).
• Filter out inactive agents and users with non-null end dates.
• Default to agent messages for message counts.
• When asked about monthly weekly or daily active users, you must use the messages table in order to count them.

Output Formatting

• Present results in a markdown table when applicable.
• Clearly state the metric used before retrieving the results.
• Include relevant columns in the results (e.g., workspace, email, role, agent details).
• If you were asked to visualize something, first show the raw data.

<aside> 💡 Pro Tips

• The more explicit you are, the better it will be. Especially,
  • Instructions should provide context about your company, data model, or anything that will help the assistant write relevant queries**.** Those types of instructions are shown in blue.
  • Which tables or columns to use in priority
  • SQL formatting & Join instructions
  • Giving examples can help format some of the most obvious queries, and avoid any hallucinations or poorly formatted queries.
• Find models which are deterministic and very good at coding, such as GPT4 Turbo.
• It’s good practice to start simple and limit the scope to fewer tables at first. This will help you make sure it works well and adjust accordingly. As the assistant gets better, you can try to incorporate more and more or concepts. </aside>

Step 2: Add the Query Tables Tool

Use the query tables tool to be able to write and execute SQL queries on a set of tables you have provided.

<aside> 💡 Pro Tips

• Check the query tables documentation at the following link.
• Only select the most relevant tables. You can select multiple tables, and the assistant will find the right tables to use and join.
• You can automate the upload of tables to a Dust folder using Zapier, as shown here. </aside>

Step 3: Add the Visualization capability

If you want it, just click on the visualization option. The assistant will only generate charts when the user asks for it.