Anchor

	top
	top

Section

Column

width	80%

Overview

Segmentation is the process of creating groups of records (segments) based on common business characteristics. Segments can be created by using CRMQL (CRM Query Language), a business query language developed by CRM.COM. They are used for statistical calculations and also as identification criteria by business processes, for example, to group newly activated subscriptions and send them a welcome message.

Creating and Using Segments

Info
Foundation > Segmentation

Segments group records based on common characteristics by using conditions defined using CRMQL (CRM.COM Query Language).

By using the Included in Segments and Excluded Segments attributes found under the 'Segments' or 'Conditions' tab of some modules, the system can effectively select which records should be affected by (or excluded from) an action.

Segments are also used in Pricing Strategy to determine whether discounts can be applied to the selected group of records based on the conditions specified, and in processes to apply actions on specific groups of records in one flow.

Creating Segments

Specify the search criteria that match the segment you are interested in or click on NEW to create a new segment. Enter a name for the new segment, and in the Criteria area enter the criteria based on which the information will be selected, this can be done using CRMQL query or alternatively select the Use Graphical Syntax button for assistance in creating the criteria - refer to the CRM Query Language manual for further details. SAVE the segmentation definition when it's complete, or select SAVE AS DRAFT which allows the CRMQL query to be saved without it being correct so that it can be edited at a later stage.

Back to top

Modifying a segment

Use EDIT from the Segments summary screen to enter edit mode and update the segment definition. Segments can be edited in both 'draft' or 'posted' life cycle states, but the entity cannot be modified in any case.

Including specific records in the segment

You can define specific records that should be included in the segment regardless of the specified criteria. The type of information that can be added to the static items depends on the selected entity. Use the Add Static Items action available from the 'Actions' menu to Add static items manually or Add static items through csv file. The successfully added static items can be viewed from the 'Static Items' section at the bottom of the screen. To remove any static items just select Delete next to the item.

Previewing and exporting segments

You can preview a number of the records that will be included in a segment through the Matched Items section.

It is also possible to view a comprehensive list of the records included in a segment by exporting them as an Excel file through the Export button in the Matched Items section.

Segment preview information includes:

Entity ID
Entity number (where applicable)
Entity name
Entity alternative code (where applicable)
Entity description (where applicable)

Preview information additionally displays either the Accounts Name or the Contact Name depending on whether the entity is an account-based or a contact-based entity.

The Accounts Name is available when previewing segments for:
- Bills
- Buy In Advance Requests
- Credit Notes
- Invoices
- Invoice Cancellations
- Payments
- Payment cancellations
- Payment gateway cards
- Payment gateway requests
- Refunds
- Rewards Participants
- Award Transactions
- Spend Transactions
- Award Expiration Transactions
- Service Requests
- Subscriptions
- Wallets
- Write Offs
- Subscription Actions
The Contact Name is available when previewing segments for:
- Activities
- Leads

Back to top

Using segments throughout the system

As shown in the table below, several system processes use segments.

Entity	How segments are used by the module
Rewards	Used by reward offers as award conditions. Participants from a specific segment can be eligible to be awarded or can be excluded.
Provisioning	Segments are included in provisioning provider fingerprinting run criteria to include or exclude subscribers from being sent a fingerprinting message.
Pricing Strategy	Segments are included in conditions to allow (or prevent) customers from being eligible for the discount.
Notifications	Segments are included in notification types to include or exclude customers from being notified.

Back to top

global

Ui expand

title	CRMQL Examples

Contacts

Panel

id	2
title	Aluxsat Co. needs to create segments based on contacts

Retrieve Contacts of type 'Person' living in Nicosia.

Code Block

language	sql

contact_information.type = "Person" 
and exist(contact_information.address.area = "Nicosia")

Retrieve Contacts of type 'Person' that have their birthday in the current month.

Code Block

language	sql

contact_information.type="Person" 
and contact_information.month_of_birth = current_month()

Code Block

language	sql

contact_information.type="Person" 
and contact_information.month_of_birth > current_month()
and contact_information.month_of_birth <= (current_month()+1)

Subscriptions

Panel

id	2
title	Aluxsat Co. needs to create segments based on subscriptions

Retrieve Subscriptions of type 'Normal' that have at least one 'Effective' service.

Code Block

language	sql

subscription.type = "Normal" 
and exist(subscription.service.life_cycle_state = "Effective")

Retrieve Subscriptions of type 'Normal' that do not have an 'Effective' movie service.

Code Block

language	sql

subscription.type="Normal" 
and not exist
(subscription.service.life_cycle_state="Effective" 
and subscription.service.product= "movies" )

Retrieve Subscriptions of type 'Normal' that have at least one 'Not Effective' service that was terminated in the last 15 days.

Code Block

language	sql

subscription.type="Normal" 
and exist(subscription.service.life_cycle_state="Not Effective" 
and subscription.service.life_cycle_state_start_date <= day_end() 
and subscription.service.life_cycle_state_start_date >= days_before(day_end(),15) )

Accounts

Panel

id	2
title	Aluxsat Co. needs to create segments based on accounts receivable

Retrieve Accounts with a balance greater than '0', a credit status that is 'Exceeded', and a classification that is not 'VIP'.

Code Block

language	sql

accounts_receivable.balance > 0 
and accounts_receivable.credit_status = "Exceeded" 
and accounts_receivable.classification!= "VIP"

Retrieve Accounts that are not set to receive bills by email.

Code Block

language	sql

accounts_receivable.life_cycle_state="Active"
and not exist(accounts_receivable.presentment_preference.presentment_method="Email"
and accounts_receivable.presentment_preference.active=true)

Bills

Panel

id	2
title	Aluxsat Co. needs to create segments based on bills

Retrieve Bills that are partially paid and their outstanding or unsettled amount is less than '10'.

Code Block

language	sql

bill.status = "Partially_settled" 
and (bill.outstanding_amount < 10
or bill.unsettled_amount < 10)

Retrieve Bills that are due at the end of the month and are yet to be communicated by email.

Code Block

language	sql

bill.due_date=month_end()
and not exist(bill.communication_queue.media="Email"
and bill.communication_queue.life_cycle_state="Completed")

Retrieve Bills that are due at the end of the month and were communicated with errors.

Code Block

language	sql

bill.due_date=month_end()
and exist(bill.communication_queue.media="Email" and bill.communication_queue.life_cycle_state="Rejected")
and not exist(bill.communication_queue.media="Email" and bill.communication_queue.life_cycle_state="Completed")

Activities

Panel

id	2
title	Aluxsat Co. needs to create segments based on activities

Retrieve Activities of type 'Installation' that are still in progress and are past their estimated completion date.

Code Block

language	sql

activity.type="Installation" 
and activity.life_cycle_state = "In progress" 
and activity.estimated_completion_date < day_start()

Rewards Participants

Panel

id	2
title	Aluxsat Co. needs to create segments based on Rewards Participants

Retrieve Rewards Participants who have signed up today.

Code Block

language	sql

rewards_participant.sign_up_date >= day_start()

Retrieve Rewards Participants who signed up to a specific scheme today.

Code Block

language	sql

rewards_participant.sign_up_date >= day_start() 
and exist(rewards_participant.participating_reward_scheme.name="New Customers")

Award Reward Transactions

Panel

id	2
title	Aluxsat Co. needs to create segments based on Award Reward Transactions

Retrieve Award Reward Transactions submitted this week by a specific offer.

Code Block

language	sql

award_reward_transaction.submitted_date >= week_start()
and award_reward_transaction.submitted_date <= week_end()
and award_reward_transaction.reward_offer.number="00034

Spend Reward Transactions

Panel

id	2
title	Aluxsat Co. needs to create segments based on Spend Reward Transactions

Retrieve Spend Reward Transactions with a total amount of more than '10', submitted by specific units.

Code Block

language	sql

spend_reward_transactions.total_amount > 10
and (spend.reward_transactions.submitted_by_unit="Sales Department 1" or spend.reward_transactions.submitted_by_unit="Sales Department 2" )

Service Requests

Panel

id	2
title	Aluxsat Co. needs to create segments based on Service Requests

Retrieve Service Requests of classification 'Request for Change' that were completed yesterday.

Code Block

language	sql

service_request.classification ="Request for Change" 
and service_request.life_cycle_state = "completed" 
and service_request.actual_completion_date < day_start() 
and service_request.actual_completion_date >= days_before(day_start(),1)

Payment Gateway Cards

Panel

id	2
title	Aluxsat Co. needs to create segments based on Payment Gateway Cards

Retrieve Payment Gateway Cards that are going to expire in the next month.

Code Block

language	sql

payment_gateway_card.payment_gateway_provider="Stripe" 
and (
   payment_gateway_card.expiration_year=current_year() 
   and payment_gateway_card.expiration_month=(current_month()+1)
   or
   (
      payment_gateway_card.expiration_year=(current_year()+1) 
      and payment_gateway_card.expiration_month=1 
      and current_month()=12
    )
)

Retrieve Payment Gateway Cards that failed the security code check.

Code Block

language	sql

payment_gateway_card.payment_gateway_provider="Stripe" 
and payment_gateway_card.security_code_check_result=FAIL

Payment Gateway Requests

Panel

id	2
title	Aluxsat Co. needs to create segments based on Payment Gateway Requests

Retrieve Payment Gateway Requests that were rejected during the last month.

Code Block

language	sql

payment_gateway_request.payment_gateway_provider="Stripe" 
and payment_gateway_request.life_cylce_state="REJECTED" 
and payment_gateway_request.created_date<=month_start()

Retrieve Payment Gateway Requests that were posted successfully during the last month.

Code Block

language	sql

payment_gateway_request.payment_gateway_provider="Stripe" 
and payment_gateway_request.life_cylce_state="POSTED" 
and payment_gateway_request.created_date<=month_start()

Payments

Panel

id	2
title	Aluxsat Co. needs to create segments based on Payments

Retrieve Payments of Type 'Web Payments' posted within the last day.

Code Block

language	sql

payment.type="Web Payments" 
and posted_date <= day_end() 
and posted_date>= day_start()

Retrieve Payments that were posted through Vouchers within the last week.

Code Block

language	sql

posted_date<= day_end() and posted_date >= weeks_before(day_end(),1)
and payment.voucher.life_cycle_state="Used"

Retrieve Payments that were posted within the last day and are yet to be communicated by email.

Code Block

language	sql

posted_date <= day_end() and posted_date>= day_start()
and not exist(payment.communication.direction="Outgoing"
and payment.communication.media="Email"
and payment.communication.life_cycle_state="Completed")

Ui expand

title	Useful Links

Filter by label (Content by label)

showLabels

false

spaces

V4Manual

showSpace

false

cql

label = "global" and space = "V4Manual"

labels

Column

width	20%

Panel

On this page

Table of Contents

maxLevel	3

...

Version	Old Version 1	New Version Current
Changes made by	p_demou (Deactivated)	Spyroulla Ioakim
Saved on	Mar 13, 2020	Mar 16, 2020

Versions Compared

Key

Overview

Creating and Using Segments

Creating Segments

Modifying a segment

Including specific records in the segment

Previewing and exporting segments

Using segments throughout the system

Contacts

Subscriptions

Accounts

Bills

Activities

Rewards Participants

Award Reward Transactions

Spend Reward Transactions

Service Requests

Payment Gateway Cards

Payment Gateway Requests

Payments

Page Comparison

Versions Compared

Key

Overview

Creating and Using Segments

Creating Segments

Modifying a segment

Including specific records in the segment

Previewing and exporting segments

Using segments throughout the system

Contacts

Subscriptions

Accounts

Bills

Activities

Rewards Participants

Award Reward Transactions

Spend Reward Transactions

Service Requests

Payment Gateway Cards

Payment Gateway Requests

Payments