Spotter data insights
Spotter can answer a variety of data questions of the business users. Here are some of the broad categories of questions a user can ask with the help of Spotter:
- Data literacy questions
-
Ask questions about the data model.
- Analytical questions
-
Ask Spotter to query the warehouse and get data.
Data literacy questions
Spotter contains the following data literacy capabilities:
-
Provides an overview of the data source for new users.
-
Answers questions on what columns are available.
-
Suggests follow-up questions to analyze further.
Analytical questions
Spotter brings in the ability to translate natural language instructions to analytical queries on your data model that can help you perform some complex analysis. Let’s take a look at the capabilities of Spotter in more detail by taking a simplified table given below.
Region |
Month |
Total sales |
East |
Jul 2024 |
4,000,000 |
East |
Aug 2024 |
4,000,000 |
East |
Sep 2024 |
4,000,000 |
Midwest |
Jul 2024 |
5,000,000 |
Midwest |
Aug 2024 |
5,000,000 |
Midwest |
Sep 2024 |
4,000,000 |
South |
Jul 2024 |
1,000,000 |
South |
Aug 2024 |
1,000,000 |
South |
Sep 2024 |
1,000,000 |
Southwest |
Jul 2024 |
3,000,000 |
Southwest |
Aug 2024 |
2,000,000 |
Southwest |
Sep 2024 |
2,000,000 |
West |
Jul 2024 |
4,000,000 |
West |
Aug 2024 |
4,000,000 |
West |
Sep 2024 |
3,000,000 |
Currently, Spotter can help you answer questions about your data which can be answered by using a combination of the capabilities mentioned below:
- Mathematical computations
-
Spotter can count things in your dataset (for example,
how many regions are there?) , or it can aggregate metrics (for example,what is the average sales?) or it can perform complex mathematical computations (for example,how does the sales for the east region compare against sales of the west region?). - Grouping
-
Spotter can help you perform the computations for specific groups (for example,
what is the total sales by region?orwhat is the maximum sales for each region?). You can also ask Spotter to create grouping on date columns on the fly (for example,what are the yearly sales?orwhat are the monthly sales?). - Filtering
-
You can instruct Spotter to perform the above operations on a filtered set of data instead of the full table. For example,
what are the total sales for August?orwhat are the sales for this month?orwhat are the sales for South in July 2024?
Here is a quick glance of the kind of questions you can ask using a combination of the above skills.
- Basic Aggregations
-
Spotter supports basic aggregations, which act as building blocks for more complex calculations, using keywords like sum, average, count, and unique count. These can be used on any numerical column, and can be grouped by other columns.
- Examples
-
sum [sales_amount]calculates the total sales.
average [quantity_sold] by [product_category]calculates the average quantity sold per product category.
count [order_id]counts the number of orders.
unique count [customer_id]counts the number of unique customers.
- Growth Calculations
-
Spotter computes percentage change over time with the growth of keyword. This is particularly useful for trend analysis.
- Examples
-
growth of [revenue] by [order_date].yearlyshows the year-over-year revenue growth.
growth of [quantity_sold] by [order_date].monthlyshows the monthly growth in quantity sold.
- Moving/Cumulative Calculations
-
The system can perform moving and cumulative calculations. These are useful for tracking trends over time.
- Examples
-
cumulative_sum ( [sales_amount] , [order_date])calculates the cumulative sum of sales over time.
moving_sum ( [sales_amount] , 0 , 0 , [order_date] )gives the current sales amount.
moving_average ( [sales_amount] , 4 , 0 , [order_date] )calculates the five-month moving average of sales (note the window is of four previous values plus the current value).
- Arithmetic Operations
-
- Example
-
To calculate profit, you might use a formula like
[sales_amount] - [cost_of_goods_sold].
- Conditional Logic
-
- Example
-
To categorize products based on their sales, a formula like
if ( average [sales_amount] > 1000000 ) then 'red' else 'blue'can be used.
- String Manipulation
-
Formulas can include string functions for operations like concatenation.
- Example
-
You can combine the first three letters of product_name with the last three of product_category using a formula like
concat ( substr([product_name] , 0 , 3 ) , '-' , substr([product_category] , length - 3 , 3 )).
- Date Functions
-
- Examples
-
You can use date functions in formulas such as
diff_years([order_date], today())to compute the difference in years from the current date.
To filter for the correct week number, one can use formulas such asdiff_weeks(date, to_date('12/02/2024', '%d/%m/%y')) = 0.
- Aggregations with Conditions
-
- Examples
-
group_aggregate ( sum ( [sales_amount] ) , { }, { [sales_amount] < 50000000 } )calculates the sum of sales from small orders.
To compute the sum of sales_amount for specific products, usesum_if (right([product_name], 6) = 'shirts', [sales_amount]).
- Comparisons
-
The vs keyword is used to compare values, time periods, or attributes.
- Examples
-
[sales_amount] [order_date] = 'this year' vs [order_date] = 'last year'compares this year’s sales to last year’s.
[sales_amount] [region] = 'Midwest' vs [region] = 'East'compares sales in two different regions.
- Change Calculations
-
Spotter can perform calculations to compute the change between values, often over time, or between attribute values. This is often done using formulas involving date and math functions, or with moving sums.
- Example
-
([sales_amount] in q2 2024 - [sales_amount] in q1 2024) / [sales_amount] in q2 2024 * 100calculates the percentage change in sales between two quarters.
- Subqueries
-
Subqueries allow for more complex filtering and analysis by using the results of one query to filter the results of another.
- Example
-
To find the top 10 customers by sales and then show their orders, use
[order_id] [customer_id] in (top 10 [customer_id] by [sales_amount]).
|
Analysts can provide coaching examples to help Spotter learn computation logic specific to their business. Without them, Spotter will generate these based on the details provided in question. Some of the computations listed above will not be supported with aggregated fields. For example, if the analysts creates a group aggregate formula at worksheet level then tries to use the same in creating another group aggregate formula at answer level then those computations are not supported. |
