Skip to main content

Applying Gen AI for Sales Analytics

Scenario​

You are a member of a cloud development team responsible for building core business applications leveraging a modern database such as MongoDB Atlas. It’s Monday morning, and you’re reviewing your backlog. A sales manager has just requested an analysis and critique of store sales performance across locations. The relevant data is stored in a collection with the following structure:

  

This example has an embedded document (customer), an embedded array (items) and within that another embedded array(tags). Such complex data structures are perfectly fine for Qarbine processing. MongoDB, DynamoDB, and many other databases support such complex data structures.

Other tools would require flattening the data which quickly loses the business model completely. Or, a developer could be tasked to write the query, format the output, and locate libraries for all the various formats the sales team is going to want. This is not a welcome scenario for any developer.

Traditional Approach​

MongoDB provides powerful query features through its aggregation pipeline functionality. The first step in your task is to determine the underlying MongoDB query. The desired query would group the sales by location and perform a summing for the price multiplied by quantity fields within the inner items array.

Perhaps MongoDB ninjas might effortlessly write this query, but many developers would first need to Google for examples or consult MongoDB documentation. Even with the knowledge, they then have to map the technique to the sales collection schema with its embedded arrays. This searching and experimenting is a time-consuming maze of trial-and-error. Ouch. But now, Qarbine and GenAI offer a much, much simpler solution. Simply express your query requirements in plain language and get a generated query along with an explanation. No more painful ad hoc attempts—just efficient results!

Compass has a similar feature, but the query is just the first step though toward your assigned task. The second hurdle is nicely formatting the analysis with text plus yearly totals and then there is still the 3rd hurdle of commenting on store performance. Oh, and the sales manager wants this done before her call with the regional director at 3 PM!

Applying Qarbine Generative AI Features​

Query Co-pilot​

From the Data Source Designer (a free form query editor) navigate to the collection of interest. Instead of free form typing to experience just open the “Data Access Query Assistant”. A simple first plain language query intent request is shown below.

  

An Open AI generated response is shown below. For brevity its explanation is not shown.

  

Just by clicking the “. . .” we can easily run the query.

  

The result with this sample data is shown below.

  

Surprise, there are some unexpected values for price or quantity. Let’s adjust the request as shown below and try again.

  

The generated OpenAI response is shown below. It has a query and a detailed explanation!

  

Setting and running this using the “…” option we now get these results. All this in minutes.

  

Let’s be a bit more granular and look at things on a monthly basis for the year 2015. Our request is,

For the year 2015 I want to total the sales for each storeLocations by month. Skip any null price or quantity items. Display the store location, month, and total sales sorted by store location and month.

OpenAI generates a rather complex, but correct, query. Good luck manually typing this one 😉. 

db.sales.aggregate([
  { $unwind: "$items" }, // Unwind the items array to work with individual items
  { $match: { 
    "items.price": { $exists: true }, 
    "items.quantity": { $exists: true },
    "saleDate": {
      $gte: ISODate("2015-01-01T00:00:00Z"), 
      $lt: ISODate("2016-01-01T00:00:00Z")
    }
  } }, // Filter out null price or quantity items and by year 2015
  { 
    $project: { 
      storeLocation: 1,
      month: { $dateToString: { format: "%Y-%m", date: "$saleDate" }},
      totalSale: { $multiply: ["$items.price", "$items.quantity"] }
    }
  }, // Project the necessary fields for grouping
  {
    $group: { 
      _id: { storeLocation: "$storeLocation", month: "$month" },
      totalSales: { $sum: "$totalSale" }
    }
  }, // Group by storeLocation and month, and calculate total sales
  {
    $project: { 
      storeLocation: "$_id.storeLocation",
      month: "$_id.month",
      totalSales: 1,
      _id: 0
    }
  }, // Project the desired output fields
  { $sort: { storeLocation: 1, month: 1 } } // Sort by store location and month in ascending order
])

Running it shows total sales, location, and month dates! Those are the raw values we need.

  

. . .

  

etc.

Sometimes this is all you need to claim success. Congratulations on a job well done in just a few minutes. Generative AI to the rescue!

Formatting an Analysis​

Our task though requires further analysis and a more formatted report for the sales manager. Qarbine data source and template components can be generated from this ad hoc interaction. The template is then easily tweaked with the Template Designer for location grouping and formatting to emit this.

  

The right-hand bars are graphical cues for percentage of sales by month for the store. We also calculate total store sales. There are a total of 6 locations in this dataset. We’ve just jumped over the first 2 hurdles. Onward to the final commentary task. With Qarbine, text from the analysis can be dynamically used to obtain generative AI commentary and included in the final report. One of Qarbine’s over 450 Excel-like macro functions can be easily applied.

=completion(
concat("Below is sales by month summary. Comment on the store location performance", 
@summaryTsv),"myOpenAI", true)

This macro creates a generative AI prompt string using concatenation. It sends it to the Qarbine AI Asssitant with the “myOpenAO” alias and only wants the completion text as the result.


The OpenAI generated commentary is shown below.

  

Moving Ahead at the Speed of AI​

Wow! All of this can be accomplished in about 15 minutes. The sales manager is going to be pleased!

Qarbine has 15+ Generative AI service integrations and also supports various database vector search features. It can even dynamically obtain embeddings for use by queries. There is a large amount of synergy by combining the power of modern databases, GenAI, and Qarbine to further enhance your cloud investments.