Case: Sales within the specific date

Purpose: To narrow down the metrics to a specific period, for example year to date (YTD), month to date (MTD), or comparing the sales performance between two date range. Furthermore, analyzed the channel performance and monthly sales by each brand.

Solution:

Find aggregated sales for each month. The user can separate different years by year(Date) and months by month(Date), then sum up both of them. Don't forget to place year(Date) as a condition if the data covers multiple years.

FIND year(Date), month(Date), sum(total_price) FROM sales

To inspect monthly sales across the channels, the user can add the channel_name attribute in the query.

FIND month(Date), channel_name, sum(total_price) FROM sales

To search monthly sales for each brand, add Product.brand in the search.

FIND month(Date), Product.brand, sum(total_price) FROM sales

If the user wants a summary of monthly order number, item sales and entire sales, he can use following steps to achieve.

First, build a new table 'order_item_sales' of order list and how many items purchased in each order.

BUILD TABLE order_item_sales AS (FIND order_id, year(Date), month(Date), sum(qty), sum(total_price) FROM sales)

Then obtain the summary through query from order_time_sales table.

FIND month(Date), count(order_id), sum('SUM(qty)'), sum('SUM(total_price)') From order_item_sales

The user may obtain a summary of how many orders, how many items sold and entire sales for each month as following:

Case: Products with High Repeat Purchase

Purpose: The product or brand loyalty is one of the good indicators of the brand's performance. One method to obtain such information is to measure how frequent a product or band is purchased by a customer.

Solution:

First, find the products that customers bought more than once and place them in a new table, 'return_product'.

BUILD TABLE return_product AS (FIND Product.name, Customer.name, count(qty) > 1 FROM sales)

Then, count for each product, how many people buy more than once.

FIND Product.name, count('COUNT(qty)') FROM return_product

Finally, the user can query the top 10 products with most return buyers.

FIND TOP 10 Product.name, count('COUNT(qty)') FROM return_product

Case: Finding the Most Profitable Products

Purpose: Sometimes, the best seller product is not necessary the most profitable one. Finding the most profitable products may help customers to identify potential cash cows.

Solution:

First, identify and place the high profitable product in a table, 'high_profit_product'.

BUILD TABLE high_profit_product AS (FIND TOP 100 Product.name FROM sales {sortby: Product.profit})

The user then run further analysis on this subset of data by adding a filter table name, 'high_profit_product' after the original table 'sales' in the command.

FIND TOP 100 Product.name, sum(total_price) FROM sales, high_profit_product

The user can get a sense of the data in a graphical way.

Case: Heavy Users

Purpose: For many business, the majority of the revenue is generated from retaining sales. Therefore, business owners would love to keep customers as long as possible.

Solution:

First, define heavy users. For example, if the user defines "heavy users" as purchasing more than twice a month, he can separate records by year and month to get monthly overview using year(Date) and month(Date) for a unique order list.

BUILD TABLE heavy_user_order_list AS (FIND year(Date), month(Date), Customer.name, order_id FROM sales)

Then the user can count how many orders each customer made in a month by building a new table 'heavy_user_count.'

BUILD TABLE heavy_user_count AS (FIND 'year(Date)', 'month(Date)', Customer.name, count(order_id) > 2 FROM heavy_user_order_list)

Save the list of heavy users in table heavy_user for filtering.

BUILD TABLE heavy_user AS (FIND Customer.name FROM heavy_user_count)

Save a new sales table based on the previous list.

BUILD TABLE heavy_user_table AS(FIND Date, order_id, Customer.id, Product.id, channel_name, qty, total_price FROM sales, heavy_user)

Find the popular products among heavy users.

FIND TOP 10 Product.name, sum(qty) FROM heavy_user_table

To check if heavy users have channel preferences for some brands.

FIND TOP 10 significant() Product.brand IN channel_name BY sum(qty) FROM heavy_user_table

Now we know the most significant brand is 'Izze' . It means that the sales among channels are quite different. Heavy users have their preferences on specific channels. Let's check what are those channels.

FIND Product.brand='Izze', channel_name, sum(qty) FROM heavy_user_table