Table of Contents

Business Requirement

Company ZX wants to create a Segment for "Contacts that have their birthday in the next month" to be used on discounts. They must also to include Contacts that have their birthday in one and a half months.

CRM.COM Solution

• Configuration
  
  • Create the following segment:
    • Name: Retrieve contacts that have their birthday in the current month
    • Entity: Contact Information

    • Criteria: 

   

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

   

  • • Click Save

Business Requirement

Company ZX wants to create a Segment Criteria Template for "Contacts that have their birthday this month" to use it for various promotions and notifications.

CRM.COM Solution

• Configuration
  

  The following configurations is required in the System to satisfy the requirement

  • Create the following Segment Criteria Template:
    • Name: Contacts that have their birthday this month
    • Entity: Contact Information

    • Criteria: 

   

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

   

  • • Click Save 

Business Requirement

Company ZX wants to create a Segment for Accounts Receivables that are in Debt, excluding those of VIPs, to use in various segmentations and business processes. 

CRM.COM Solution

• Configuration
  
  • Create the following Segment Criteria Template
    
    • Name:  Accounts Receivable in Debt excluding VIP
    • Criteria:

   

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

  • Create the following Segment using the above template:
    • Template: Accounts Receivable in Debt, excluding VIP 
    • Name: Accounts Receivable in Debt, excluding VIP
    • Entity: Accounts Receivable
    • Click Save 

Business Requirement

Company ZX want to export a Segment for "Contacts that have their birthday in November" for a check-up process.

CRM.COM Solution

• Configuration 
  • Navigate to the corresponding Segment:
    • Actions > Preview
    • Click on Export
    • Click on Download button that is present on the bottom of the Preview screen
    • Navigate to Downloads folder
    • Open the .csv file 

Retrieve Contact Information of Type "Person" living in Nicosia.

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

Retrieve Contact Information of Type "Person" that have their birthday in the current month.

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

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

Retrieve Subscriptions of Type "Normal" having at least one 'Effective' Service.

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

Retrieve Subscriptions of Type "Normal" not having any 'Effective' movie Service.

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

Retrieve Subscriptions of Type "Normal" having at least one 'Not Effective' Service, which was terminated in the last 15 days.

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) )

Retrieve Accounts Receivable having a Balance greater than 0 and a credit status which is "Exceeded" and a Classification which is not "VIP".

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

Retrieve Accounts Receivable which are not set to receive Bills by email.

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

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

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

Retrieve Bills which are due at the end of the month and are not communicated by email yet .

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

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

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")

Retrieve Activities of Type "Installation" which are still in progress and are past their estimated completion date.

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

Retrieve Jobs of Type "Maintenance" which were completed yesterday.

job.type="Maintenance" 
and job.life_cycle_state = "completed" 
and job.actual_completion_date < day_start() 
and job.actual_completion_date >=days_before(day_start(),1)

Retrieve Rewards Participants who have signed up today.

rewards_participant.sign_up_date >= day_start() 

Retrieve Rewards Participants who have signed to a specific Reward Scheme today.

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

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

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

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

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" )

Retrieve Service Requests of Classification Request for Change which were completed yesterday.

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)

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

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 which failed the security code check.

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

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

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

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

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

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.

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, that were not communicated by email yet.

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")

Retrieve Buy in Advance Requests that were submitted today.

buy_in_advance_request.created_date >= day_start and buy_in_advance_request.created_date <= day_end

Retrieve 'Effective' Buy in Advance Request that are becoming 'Effective' for billing tomorrow and have a duration of more than 3 months.

buy_in_advance_request.life_cycle_state="EFFECTIVE" and buy_in_advance_request.billing_effective_date > day_end and  buy_in_advance_request.billing_effective_date < days_after(current_date(),2) and buy_in_advance_request.duration > 3 and buy_in_advance_request.duration_unit_of_time="MONTH"

Retrieve 'Effective' Buy in Advance Requests that were rated and are expiring in the following 10 days.

buy_in_advance_request.life_cycle_state="EFFECTIVE" and buy_in_advance_request.billing_state="RATED"
 and buy_in_advance_request.expiration_date >=day_start and buy_in_advance_request.expiration_date < days_after(current_date(),11)

Related Areas

• Page:
  Understanding Segmentation
• Page:
  Managing Segments
• Page:
  Using CRM Query Language
• Page:
  Configuring Segment Criteria Templates