Calculated Attributes

Calculated Attributes (formerly Customer Attributes) are customer-level KPIs derived automatically from transaction and event data. They represent aggregated metrics per customer and are available as filter conditions in the audience builder.

• Examples: average cart size, purchase frequency, preferred category, lifetime value, last purchase date.
• Created and managed in Customers → Calculated Attributes.
• Automatically recalculated on a configured schedule as new data arrives.
• Can also be used to power tier systems — any numeric Calculated Attribute can serve as the qualifying metric for a tier.

Use Cases

Use Case
→ Segment customers by average cart size: target those below 30 EUR with an upsell challenge.
→ Use purchase frequency to identify lapsed customers (< 1 visit/month) for reactivation campaigns.
→ Calculate preferred category per customer and serve personalized category-level discount campaigns.
→ Identify customers with high wallet balance but low redemption rate for a points-burning nudge.
→ Use lifetime value to define VIP tiers and unlock exclusive rewards for top-spending customers.
→ Predict churn by calculating days since last purchase and flag customers above threshold for re-engagement.

How It Works

1. You define a calculated attribute with a formula (average, sum, count, last value) applied over a time window.
2. The platform runs the calculation for every active customer on a configured schedule (e.g., daily) using their transaction and event history.
3. The result is stored as a customer-level metric and immediately available in the audience builder.
4. Any audience using that attribute is updated on the next sync cycle.

note

Calculated attributes are only computed for customers with active status, regardless of whether the SQL condition includes non-active customers.

Configuration

Go to Customers → Calculated Attributes → New Attribute in the Owner App:

Field	Description
Name	Label displayed in the audience builder
Formula	Average, sum, count, last value, or custom
Source data	Transactions, custom events, or profile fields
Time window	Rolling period (last 30 days, last year, lifetime)
Recalculation frequency	Daily, weekly, or on-event

Creating a Calculated Attribute

note

Only users with the required access can create, edit, and manually execute calculated attributes. Other users may be able to view them but not modify them, depending on company access configuration.

1. Go to Customers → Calculated Attributes.
2. Click New Customer Attribute in the upper right.
3. Fill in the following fields:

Basic Fields

Field	Description
Name	Short display name for the attribute (used in the audience builder and tier setup)
Description	Longer text describing the purpose and formula logic
Attribute type	Currently only Calculation is available — derived from ingested platform data

Data Type

After selecting Calculation, choose the data type for the output value:

Data Type	Use for
Number	Any numeric value including decimals (e.g., average cart size: 42.50)
Integer	Whole numbers only (e.g., purchase count: 12)
String	Text values (e.g., preferred category: "Fresh")
Boolean	True/False flags (e.g., has_redeemed_reward: true)
Date & time	Date and time values (e.g., last_purchase_date)

Advanced Fields

Field	Description
Expose calculation results via API	When enabled, the latest calculated value is available via the Streaming API in real time. Useful for loyalty apps and customer service systems that need live attribute data. Note: additional charges may apply.
SQL Condition	The SQL query that computes the attribute value per customer. Write or paste the query here.
Profiling consent	If checked, the attribute is calculated for all active customers regardless of whether they accepted profiling consent.

SQL Condition examples

The SQL condition defines how Loyal Guru calculates one value per customer. Use it to transform cart history, rewards usage, category behavior, or custom events into a reusable customer-level attribute.

Each query should return at least:

Column	Purpose
customer_id	Identifies which customer receives the calculated value
value	The attribute value stored on the customer profile

The Owner App SQL placeholder uses %<dataset>s.activities and date placeholders such as %<date_from>s and %<date_to>s. Adapt table names and fields to the company's data model before using a query in production.

Use case	Data type	Business use
Average cart amount	Number	Detect low average cart customers and trigger upsell campaigns
Purchase frequency	Integer	Identify active, occasional, or lapsed customers
Last purchase date	Date & time	Build win-back audiences based on days since last purchase
Lifetime value	Number	Feed VIP tiers and high-value customer audiences
Preferred category	String	Personalize category-level discounts and recommendations
Has purchased in period	Boolean	Exclude recent buyers from reactivation campaigns

Average cart amount

Use this when you want to segment customers by their average spend per cart.

SELECT
  customer_id AS customer_id,
  ROUND(AVG(total), 2) AS value
FROM %<dataset>s.activities
WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
GROUP BY customer_id;

Purchase frequency

Use this to count how many carts each customer made during the selected calculation period.

SELECT
  customer_id AS customer_id,
  COUNT(DISTINCT id) AS value
FROM %<dataset>s.activities
WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
GROUP BY customer_id;

Last purchase date

Use this to identify recency and build win-back audiences.

SELECT
  customer_id AS customer_id,
  MAX(created_at) AS value
FROM %<dataset>s.activities
WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
GROUP BY customer_id;

Lifetime value

Use this to calculate total customer spend over the selected period. For a lifetime value attribute, configure a broad calculation period.

SELECT
  customer_id AS customer_id,
  ROUND(SUM(total), 2) AS value
FROM %<dataset>s.activities
WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
GROUP BY customer_id;

Has purchased in the period

Use this boolean attribute to exclude customers who already purchased recently, or to trigger campaigns only for active customers.

SELECT
  customer_id AS customer_id,
  COUNT(DISTINCT id) > 0 AS value
FROM %<dataset>s.activities
WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
GROUP BY customer_id;

Preferred category

Use this pattern when cart line/category data is available in the company's dataset. It returns the category with the highest spend per customer in the selected period.

WITH category_spend AS (
  SELECT
    customer_id,
    product_category_name,
    SUM(line_total) AS category_total
  FROM %<dataset>s.activity_lines
  WHERE DATE(created_at) BETWEEN %<date_from>s AND %<date_to>s
  GROUP BY customer_id, product_category_name
),
ranked_categories AS (
  SELECT
    customer_id,
    product_category_name,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY category_total DESC
    ) AS rank
  FROM category_spend
)
SELECT
  customer_id AS customer_id,
  product_category_name AS value
FROM ranked_categories
WHERE rank = 1;

note

The examples are templates. Validate SQL syntax, table names, category fields, and date logic with your implementation team or Customer Success Manager before enabling the attribute in production.

Calculation Schedule

Field	Description
Calculation period	The time window from which data is pulled (e.g., last 30 days, current month, lifetime). Not to be confused with when the attribute is executed.
Execution type	Manual (triggered by an admin at any time) or Scheduled (runs automatically at a set time)

For Scheduled execution, configure:

Field	Description
Start date	The day when scheduled execution begins
Start time	The daily execution time
Recurrence	Daily, Monthly, or Custom months (select specific months from a dropdown)

info

Attributes with Scheduled execution type can still be triggered manually at any time from the main Calculated Attributes page.

4. Click Save. The attribute appears in the Calculated Attributes dashboard and becomes visible in customer profiles and the audience builder.

Calculation history retention

Each calculation run stores one snapshot per Customer. Audiences, the customer profile, tiers, challenges, and the Streaming API always use the latest value. The platform also keeps a limited history of past snapshots and deletes older ones automatically.

This does not delete the attribute definition, source carts or custom events, or the current value used in the product.

Recurrence	Snapshots kept	Approximate window if the schedule is regular
Daily	Last 60 executions	About 2 months
Monthly	Last 24 executions	About 2 years
Custom months	Last 5 executions	Last 5 runs
Manual	Last 5 executions	Last 5 runs

Downstream exports or BigQuery queries that join historical calculated-attribute rows only see this retained window. You cannot reconstruct a full time series of past calculations beyond it.