Environment requirements

Before we get started, here are the technical requirements you’ll need going into this example:

• A MotherDuck account with access to prompt() and embedding() functions. These are part of the Standard plan.
• Some text data in a CSV file.
• A Fabi.ai account for dashboard creation. You can use the Free Tier of the product for this.
• Basic SQL knowledge.
• Basic Python knowledge.

1. Create our sentiment analysis pipeline

This example will use synthetic data for our fictional company, which you can download yourself here if you’d like to follow along exactly. Otherwise, you can simply ask your favorite AI to generate some data for you with the following fields:

• product_category: Categories of products (e.g. shoes, t-shirts, swimwear)
• review: A text field containing some review data ranging from positive to negative
• rating: A score from 0 to 10

Once you have your data, go ahead and upload it to your MotherDuck instance.

Copy code
SELECT
  product_category as category,
  review,
  rating,
  prompt('Classify sentiment as "Positive", "Negative", "Neutral". Just use those simple terms: ' || Review ) as sentiment,
  prompt('Extract keywords from review as a comma separated list: ' || Review ) as keywords
FROM my_db.main.superdope_product_reviews;

2. Analyze your data in Fabi.ai

Now that we have our data loaded in MotherDuck and our query in hand, let’s conduct our analysis in Fabi.ai. We’ll create a table and a pie chart with some filters so your stakeholders can adjust the view on their own.

Follow these steps:

Step 1: Log in to Fabi.ai and create your account
Go to https://app.fabi.ai/ and log in with your corporate Gmail account.

Step 2: Connect MotherDuck to Fabi.ai
When you create your account, the system will prompt you to connect your data source.. Simply follow those steps and enter your MotherDuck access token. Or, in a blank Smartbook in the Schema browser, click on the “Add Data Source” option on the left hand side and follow those same steps.

Step 3: Query the data

In a blank Smartbook, create a new SQL cell and copy/paste the SQL query we wrote above. Run the cell. You should see the results in the output. Note: This data is now cached as a pandas DataFrame. This is important for the following steps.

Step 4: Chain a new SQL cell and create filters

In this step, we’re going to query the DataFrame generated by the SQL cell. Under the first SQL cell, create another new SQL cell and query the DataFrame:

Copy code
    select * from dataframe1

This step may seem redundant, but it helps when creating filters. Since dataframe1 is now cached, we can create dynamic filters based on the values in the result.

In your second SQL cell, we can adjust the query to add a dynamic variable:

Copy code
select * 
from dataframe1
where sentiment in {{sentiment}}

Now let’s create the filter for sentiment. Above the second SQL cell, click “Insert a new cell” and create a Filters & Inputs of type Pick List. Follow the steps using the following parameters:

• Input Name: sentiment
• Options Type: dynamic
• Dataframe: dataframe1
• Column: sentiment
• Allow multiple selections: True

In our example, we added two filters, but this is what you should now see (below). If you change the filter or rerun the cell, it will pick up the values from the dropdown. You can create many more types of filters and inputs in Fabi.ai.

Step 5: Create a pie chart

Finally, let’s create a pie chart. It will show the distribution of sentiment for the filtered DataFrame.

At the bottom of the Smartbook, insert a new Python cell. Use Plotly to create a pie chart with dataframe2 (the DataFrame generated by your second SQL cell):

Copy code
import plotly.express as px

sentiment_counts = dataframe2['sentiment'].value_counts()

# Create a dictionary to map sentiments to specific colors
color_map = {'Positive': '#A5D6A7', 'Negative': '#FF8A80', 'Neutral': '#BCAAA4'}  
colors = [color_map[sentiment] for sentiment in sentiment_counts.index]

fig = px.pie(values=sentiment_counts.values,   
             names=sentiment_counts.index,
             width=800,  
             height=450,
             color_discrete_sequence=colors)

fig.update_layout(title='Distribution of Review Sentiments')  
fig.show()

Run that cell, and there you have it! Your pie chart will dynamically adjust as you change the filters above.

Pro-tip: Fabi.ai has an integrated AI assistant that can write both SQL and Python and understands the full context of your Smartbook. Rather than writing the code manually, you can simply ask the AI.

3. Build and publish the report and share with stakeholders

Congrats, you’ve successfully categorized product review sentiment using MotherDuck! We’ve also built a basic sentiment analysis. Now we need to convert this to a shareable report for your teammates.

In the top header of the Smartbook, click “Report.” This will take you to the report building staging area. There, you can add, remove, or rearrange elements as you wish. In our case, you can remove the first SQL cell output. It's a duplicate of the second one but without the filter. In the right-hand configuration pane, you can schedule this report to refresh as well.

When you’re ready to publish this, click Publish in the right hand panel, which will bring you to the report.

And that’s it! Now you can share this URL with your coworkers. They’ll be able to slice and dice product reviews by sentiment on their own.

Bonus: Use MotherDuck’s vector embedding for advanced review search

If you’re building a sentiment analysis report, you may also want to let your users search reviews by content. Keyword and term matching using things like Regex or even fuzzy matching can be quite limiting. Say, for example, you want to search for reviews that mention “great quality.” It would be great if that search could return a review that says “The materials were top notch,” which is clearly a commentary on the quality.

MotherDuck’s vector embedding can offer a quick and easy way to build a clever search engine.

In the same Smartbook we created above, create a new SQL cell and add the following MotherDuck query:

Copy code
WITH embedded_reviews AS (
  SELECT
    product_category AS category,
    review,
    rating,
    embedding(review) AS review_embedding
  FROM my_db.main.superdope_product_reviews
),
search_query AS (
  SELECT embedding('great quality') AS query_embedding
)
SELECT
  er.category,
  er.review,
  er.rating,
  array_cosine_similarity(er.review_embedding, sq.query_embedding) AS similarity_score
FROM embedded_reviews er, search_query sq
ORDER BY similarity_score DESC

The embedding() function will create an embedding for each review. It does this as a new column called review_embedding in the CTE. Then we use cosine similarity to match that embedding with the embedding for the string ‘great quality’.

Now, to create a search function for your users in the dashboard, replace the ‘great quality’ string with a parameter:

Copy code
WITH embedded_reviews AS (
  SELECT
    product_category AS category,
    review,
    rating,
    embedding(review) AS review_embedding
  FROM my_db.main.superdope_product_reviews
),
search_query AS (
  SELECT embedding('{{search_term}}') AS query_embedding
)
SELECT
  er.category,
  er.review,
  er.rating,
  array_cosine_similarity(er.review_embedding, sq.query_embedding) AS similarity_score
FROM embedded_reviews er, search_query sq
ORDER BY similarity_score DESC
LIMIT 10

For this to run, we’ll create a new input above this cell like we did previously for the filter. Select “Insert a new cell” above the SQL cell and select Text. Call the input “search_term” and insert some default value. After creating this input, you can search for any term in it. It will then perform a semantic search on the review field.