https://www.gooddata.ai/ logo
Join Slack
Powered by
we have data like Customer Number, OrderID, Order ...
# gooddata-cloud
k
we have data like Customer Number, OrderID, Order Type Order Type has values (preorder, reorder). For a customer one orderID can be order type with reorder, then for same customer another order ID can be preorder. I have to count customer with reorder only, preorder only. And then customer with both. What should be goodData KPI for both Summary Table KPILogic Re-order Only Has Re-order, no Pre-order Pre-order Only Has Pre-order, no Re-order BothHas Re-order AND Pre-order
k
Here's how you can define these three KPIs in GoodData using MAQL metrics. The core idea is to use conditional filtering with 
WHERE
clauses and combine metrics to isolate each customer segment. ------------------------- Building the KPIs Step 1: Define Base Count Metrics First, create two helper metrics that count distinct customers per order type: Customers with Re-order:
Copy code
SELECT COUNT({attribute/customer_number}) WHERE {label/order_type} = "reorder"
Customers with Pre-order:
Copy code
SELECT COUNT({attribute/customer_number}) WHERE {label/order_type} = "preorder"
------------------------- Step 2: Define the Three KPI Metrics Re-order Only (has reorder, no preorder):
Copy code
SELECT COUNT({attribute/customer_number}) 
WHERE {label/order_type} = "reorder" 
AND {label/customer_number} NOT IN (
    SELECT {label/customer_number} WHERE {label/order_type} = "preorder"
)
> Note: MAQL supports 
WHERE
with `IN`/`NOT IN` for attribute values [Relational Operators], but nested subqueries filtering on customer membership are complex. A more practical approach in GoodData is described below. ------------------------- Recommended Practical Approach in GoodData Since MAQL metrics are context-aware and multidimensional [MAQL Multidimensionality], the cleanest way to build these KPIs is: 1. Create two base metrics (count of customers per order type as above). 2. Use them in a Pivot Table sliced by 
Customer Number
to see, per customer, whether they have reorders, preorders, or both. 3. For summary KPIs, define metrics using 
BY
and 
ALL OTHER
to lock aggregation grain [BY keyword]. For the "Both" KPI (customers with both order types), you can leverage the fact that a customer appears in both base metrics:
Copy code
-- Customers with BOTH: count customers where reorder count > 0 AND preorder count > 0
SELECT COUNT({attribute/customer_number}) 
WHERE {label/order_type} = "reorder" 
AND {label/order_type} = "preorder"
> ⚠ī¸ The knowledge sources do not explicitly cover multi-value set membership checks (e.g., "customer has both values of an attribute") in a single MAQL expression. For complex segmentation like this, GoodData's AI Assistant Metric Skill can help generate the appropriate MAQL [Metric Skill], or you may need to pre-compute customer segments in your data source (e.g., add a 
customer_segment
column with values 
reorder_only
, 
preorder_only
, 
both
) and then use simple 
WHERE
filters:
Copy code
-- Re-order Only
SELECT COUNT({attribute/customer_number}) WHERE {label/customer_segment} = "reorder_only"

-- Pre-order Only
SELECT COUNT({attribute/customer_number}) WHERE {label/customer_segment} = "preorder_only"

-- Both
SELECT COUNT({attribute/customer_number}) WHERE {label/customer_segment} = "both"
This pre-computation approach is the most reliable and performant path for this type of customer segmentation in GoodData. 👍 0 button 👎 0 button ℹī¸ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.
k
Copy code
SELECT COUNT({attribute/customer_number}) 
WHERE {label/order_type} = "reorder" 
AND {label/customer_number} NOT IN (
    SELECT {label/customer_number} WHERE {label/order_type} = "preorder"
)
This is not a valid syntax - SELECT {label/customer_number} WHERE {label/order_type} = "preorder"
j
Hello Krishti, If you could provide a link to the workspace you are working on here we can take a look at the model and better advise on how to build this. You can DM if you would prefer.
Hi Krishti, is it possible to share a link so we can assist further?
k
Hi Joseph, We resolve it on our own. Thanks for reply. Number of Buyers by Reorder only 
Select ({metric/total_number_of_buyers}-(SELECT {metric/total_number_of_buyers} WHERE {label/orders_summary.order_type}="Pre-order"))
Number of Buyers by Reorder only 
Select ({metric/total_number_of_buyers}-(SELECT {metric/total_number_of_buyers} WHERE {label/orders_summary.order_type}="Pre-order"))
Number of Buyers by Preorder Reorder Both 
Select ({metric/total_number_of_buyers}-{metric/number_of_buyers_by_preorder_only}-{metric/number_of_buyers_by_reorder_only})
🙌 1
5 Views
Open in Slack
PreviousNext
Powered by