Purchase Frequency

Date semantics

When a template uses start_date, both start_date and end_date are inclusive. end_date may equal today. The previous day's data becomes available overnight, so today's rows first appear after the next daily run.

The Purchase Frequency template analyzes how often customers make purchases within specified time periods. This helps partners understand customer engagement patterns and purchase behaviors over time.

The analysis calculates average deliveries per customer, grouped by time periods (monthly or quarterly), and can be filtered by brands, categories, and customer segments (all customers vs. repeat buyers only).

You can perform the following actions with this template:

• Aggregate data at monthly or quarterly intervals.
• Use filter options for both brands and categories.
• View repeat buyer insights.
• View multiple levels of data aggregation.

note

A minimum of 100 users is required for enforcement.

Run the query

Use the following query to calculate the purchase frequency:

set cleanroom_name = 'CLEANROOM_NAME';
call samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name, -- cleanroom name
  'purchase_frequency',
  [], -- Consumer tables (none needed)
  [], -- Provider tables (none needed)
  object_construct(
    'date_aggregation', 'MONTH',
    'start_date', '2025-01-01',
    'end_date', '2025-03-01',
    'brands', ['BRAND_NAME_1'],
    'categories', ['CATEGORY_NAME_1'],
    'aggregation_level', ['delivered_entity_brand'],
    'repeat_buyer_only', true
  )
);

Parameters

Parameter	Description	Data type	Allowed values	Required or optional
date_aggregation	Specifies the time bucket for analysis.	string	"MONTH", "QUARTER"	required
start_date	Sets the start of the analysis period (inclusive).	string	Must be no earlier than 3 years ago. The inclusive range from start_date through end_date must be at least 30 days (end_date is at least start_date plus 29 days). Use the following format: YYYY-MM-DD.	required
end_date	Sets the end of the analysis period (inclusive).	string	Must be no earlier than 3 years ago and no later than today. Use the following format: YYYY-MM-DD. Data through the previous day is available. If end_date is today, the query runs, but today's data is empty until the next daily pipeline run.	required
aggregation_level	Determines how results are grouped.	array	"delivered_entity_brand", "delivered_entity_department", "delivered_entity_super_category", "delivered_entity_category"	optional
brands	An array of brand names to apply to a filter, for example ["BRAND_NAME_1", "BRAND_NAME_2"]	array	Brand names as they appear in the catalog	optional
categories	An array of categories to apply to a filter, for example ["CATEGORY_NAME_1", "CATEGORY_NAME_2"]	array	Category names as they appear in the catalog	optional
repeat_buyer_only	A boolean flag to include customers who have placed multiple orders. When true, filters to only customers with more than one delivery.	boolean	true, false	required

Result

Result	Description
PARAMS_JSON	A JSON object detailing all input parameters
AGGREGATION_LEVEL	The specified dimensions for aggregation
DATE_AGG	The time period
PURCHASE_FREQUENCY	The average number of deliveries per customer during the period

Example result

PARAMS_JSON	DATE_AGG	DELIVERED_ENTITY_BRAND	PURCHASE_FREQUENCY
{"date_aggregation":"MONTH", "start_date" … }	2025-01-01	BRAND_NAME_1	2.34
{"date_aggregation":"MONTH", "start_date" … }	2025-02-01	BRAND_NAME_1	1.89

Data collection and classification

The Purchase Frequency template organizes and categorizes the data using the following methods:

• Minimum user threshold. Results are only returned when at least 100 unique users are present in the dataset.
• Date validation. Requires dates no earlier than 3 years ago and no later than today (both inclusive). The inclusive range must be at least 30 days (end_date is at least start_date plus 29 days).
• Aggregated results. Individual customer purchase data is never exposed; only aggregated metrics.
• Parameter validation. Invalid date ranges or aggregation levels result in empty result sets.
• Secure processing. All calculations are done in a protected clean room setting.

Use cases

The following use cases are examples of how this template works:

• Monthly purchase analysis. Track customer engagement patterns monthly to identify seasonal trends.
• Quarterly business reviews. Analyze purchase frequency quarterly for strategic business planning.
• Repeat customer analysis. Focus on loyal customers by setting repeat_buyer_only to true.
• Brand performance tracking. Monitor how frequently customers purchase specific brands over time.

Best practices

Consider the following best practices when running this query:

• Choose aggregation periods that align with your business reporting cycles.
• Select date ranges that capture full business cycles (avoid partial months/quarters).
• Use repeat_buyer_only when analyzing customer loyalty and retention.
• Account for seasonal variations when interpreting frequency changes.
• Apply brand filters to understand category-specific engagement patterns.

Methodology

This template employs various methodologies to gather, analyze, and interpret data.

Time-series approach

The Purchase Frequency calculation breaks customer activity into time periods to show how often they engage.

1. Time bucketing. Organize orders by month or quarter, depending on the chosen date setting.
2. User aggregation. Count how many deliveries each user made during each time period.
3. Frequency calculation. Work out the average number of deliveries each user makes in a given time period.

Calculate purchase frequency

The Purchase Frequency template adds up all deliveries made by each customer during a specific time period, then divides this total by the number of active customers in that time period.

The following image shows the Purchase Frequency formula:

Where:

• n is the number of active customers in the period.
• Deliveries i,period is deliveries for customer i in the period.

Analyze Purchase Frequency results

When interpreting the results, consider the following factors:

• Seasonality. Monthly/quarterly patterns may reflect seasonal trends and business cycles.
• Customer lifecycle. New customers and repeat customers often have different buying behaviors.
• Category effects. Some products have higher or lower purchase frequencies.
• Repeat buyer analysis. You can optionally focus on customers who make repeat purchases to understand loyalty better.

Analysis capabilities

The template supports flexible analysis.

• Multiple time granularities. Choose to group data by month or quarter.
• Customer segmentation. Analyze all customers or focus only on repeat buyers.
• Multi-dimensional filtering. Apply brand and category filters for more detailed insights.
• Aggregation levels. Group data by brand, department, or product categories.

Limitations and considerations

Keep in mind the following limitation and considerations when using this template:

• Business context. Align results with marketing efforts and seasonal trends to ensure accurate interpretation.
• Data completeness. Accurate results rely on having the full order history for the time period being analyzed.
• Market dynamics. Be aware of external factors that might affect customer behavior.
• Time period selection. Using incomplete months or quarters can distort frequency calculations.