If you’ve been following along with this series, you’ll know how to come up with specific, robust questions and how to structure your queries to get down to the exact details you’re looking for.

To wrap things up, we’ll take you through the process of building some queries to help you explore your database. If you want to try this out for yourself, sign into your existing Chata account or sign up to try all our features for free. In this example, we’ll be referencing our demo data––a fully built plumbing and HVAC company dataset––to showcase how to build effective queries that can lead to the derivation of critical business insights.

First, we’re going to dive into some questions we have about product inventory and customer behavior. We’ll start by employing the “tree logic” we introduced in Part 2.

 

Queries Related to Inventory

Step 1) Identify domain theme and associated value labels.

• Domain Theme: Inventory

• Associated Value Labels: Boilers, Compressor, Air Conditioner, Bathroom Fixtures (access this list by asking: “All products”)

Step 2) Identify intended query type, filters and groupables. Draft a query.

• Query Type: Calculation

• Draft Query: Total [calculation] inventory [domain] of boilers [product filter] by month [time groupable] last year [time range filter].

• Plain Text Query: Total inventory of boilers by month last year.

Take a look at the data. To optimize your inventory, you need to think critically about what the data is showing you and what you don’t yet know. Has your boiler inventory remained fairly consistent month-to-month? Were there months where you were particularly low on inventory, or ran out entirely? What does this information tell you?

Step 3) Repeat Step 2 with an additional query or queries to analyze further.

Query Type: Calculation

Draft Query: Total [calculation] boilers [product filter] on hand [groupable]

Plain Text Query: Total boilers on hand

Continue querying your current inventory status regarding your specific product of interest. Now that you can visualize boiler inventory trends over the last year and you’ve seen what you currently have in stock, you might explore further in the context of sales and customer behavior.  This might allow you to assess whether introducing a new service or upsell opportunity, such as offering maintenance services to specific customers, would be feasible and potentially lucrative. Begin by looking at which customers bought boilers within the last year.

 

Queries Related to Customer Behavior

Step 1) Identify domain theme and associated value labels.

• Domain Theme: Sales

• Associated Value Labels: Boilers, Customers [individual customers can be denoted by specific value labels]

Step 2) Identify intended query type, filters and groupables. Draft a query.

• Query Type: List

• Draft Query: Show top five [list] products [domain] by total sales [sales] by month [groupable] last year [time filter]

• Plain Text Query: Show top 5 products by total sales by month last year.

So, you’ve already taken a look at your inventory data. Based on the query above, you might discover that boilers have been one of your most popular sales items throughout the last year. You already know it’s important to optimize inventory management for high-demand products, and even more so given the high price point associated with this particular item. Continue querying to gain further insights.

Step 3) Repeat Step 2 with an additional query or queries to analyze further.

Query Type: List

Draft Query: Show all [list] sales [domain] of boilers [product filter] by customer [groupable] by month [groupable] last year [time filter]

Plain Text Query: Show all sales of boilers by customer by month last year.

Query boiler sales by customer to see customer trends. Does the data show you that most customers tend to buy one boiler at a time? Are there customers who bought multiple boilers in a single purchase, or multiple boilers within a relatively short time period? What do these trends tell you? Does anything stand out? Consider looking more closely at data from your top boiler-purchasers.

• Query Type: List

• Draft Query: Show top five [list] customers [domain] buying boilers [filter] last year [time range filter]

• Plain Text Query: Top 5 customers buying boilers last year.

This query can be used to filter data from a segment of customers over a specific time period. In this example, you can easily see which customers spent the most on boilers within the last year. With a single click, you can drill down to see how boiler sales were distributed throughout the year for each of those customers.

You might decide to continue querying data regarding a particular customer of interest. To do so, be sure to include the appropriate value label for the customer within your query. You could repeat this type of analysis for any number of your top customers (which you identified through your earlier query), or analyze data from a select few, depending what information you’re seeking.

• Query Type: List

• Draft Query: Show all [list] sales [domain] of boilers [filter] to John Smith [customer value label]

• Plain Text Query: Show all sales of boilers to John Smith

This query tells you whether a specific customer (John Smith, in this example) bought one or more boilers and how much they have spent on boilers over time. You could also expand your customer-specific analysis beyond the product you were originally looking at. To see if that customer has made any other product purchases, enter a more general sales query. Here’s how that might look:

Query Type: List

Draft Query: Show all [list] sales [domain] to John Smith [customer value label]

Plain Text Query: Show all sales to John Smith

If a customer has bought more than one boiler from your company or has purchased additional products from you over time, they might be a good candidate for paid maintenance services. You might also consider looking at additional data over recent months to see who has purchased boilers and importantly, who has yet to make payment. Looking at this data is particularly important given that you’ve found that boilers are making up a large percentage of your revenue. This is important to ensure AR is in good standing, and to identify any gaps or opportunities for improvement. 

 

Queries Related to AR

Step 1) Identify domain theme and associated value labels.

• Domain Theme: Accounts Receivables

• Associated Value Labels: Boilers, Customers [individual customers can be denoted by specific value labels]

Step 2) Identify intended query type, filters and groupables. Draft a query.

Query Type: List

Draft Query: All [list] boiler [product filter] sales [domain] last six months [time range filter]

Plain Text Query: All boiler sales last 6 months.

This query return a list of all boiler sales over the past six months.

Step 3) Repeat Step 2 with an additional query or queries to analyze further.

• Query Type: List

• Draft Query: All [calculation] unpaid invoices [groupable] for boilers [filter]

• Plain Text Query: All unpaid invoices for boilers.

This query provides you with a snapshot of all invoices currently outstanding for boilers. Given your established payment terms – – whether that be net 30 days or other – – you can quickly and easily see which invoices are overdue, by how much, and who owes you money. If you find that only a small handful of invoices are overdue, you might reach out to these customers individually with a friendly reminder to make their payment. If you notice that a significant number of invoices have gone unpaid, you might need to think more critically about your AR management strategies and consider what changes you could make to ensure payments are being made on time, more often.

Think critically, query, explore, analyze, and repeat. Continue this iterative process to access specific pieces of information, uncover insights from your data, spot anomalies, and understand important trends over time. Once you’ve got the hang of things, querying becomes intuitive and seamless. Results get returned in mere seconds, so you can spend your valuable time strategically analyzing data, rather than being buried in it.

Remember, if your query could be understood by another human, it can also likely be understood by Chata. With this in mind, you can create infinite queries to suit your needs. Hop into your Chata account or sign up now to get started. 

 

You can come back to this series any time for a refresher on query best-practices and, as always, if you need any support getting started with Chata, our Customer Success Team is a click away!