Interactive Queries

customersummary

Overview

Generally, Acquia CDP promotes data utilizing the “last non-null” paradigm, where the most recent data received is the data promoted and surfaced in summary tables at the master customer level. In some instances, Acquia CDP employs more complex promotion logic because “last non-null” can have undesirable results. This complex promotion logic can be overwritten per tenant if you require adjustments to our standard logic. Rank conditions that begin with START_DYNAMIC_RANK__ and end with END_DYNAMIC_RANK__ can be overwritten for a specific client.

Customer summary attributes

Attribute Name

Description

Data Type

MasterCustomerID

The unique identifier of the de-duplicated master customer. It can change daily. Therefore, it cannot be consistent or persist across days.

string

FirstTransactionDate

The date of the first transaction of the customer.

timestamp

FirstTransactionDateDigital

The date of the first transaction that the customer made in the digital sales channel.

timestamp

FirstTransactionDatePhysical

The date of the first transaction that the customer made in the physical sales channel.

timestamp

PreviousTransactionDate

The date of the penultimate (second to last) transaction of the customer.

timestamp

LastTransactionDate

The date of the last transaction of the customer.

timestamp

LastTransactionDateDigital

The date of the last transaction that the customer made in the digital sales channel.

timestamp

LastTransactionDatePhysical

The date of the last transaction that the customer made in the physical sales channel.

timestamp

FirstTransactionRevenue

The revenue generated from the first transaction of the customer.

decimal

TotalRevenue

The total revenue generated from the transactions that the customer made in their lifetime.

decimal

TotalRevenue_Last12Months

The total revenue generated from the transactions that the customer made in the last 12 months.

decimal

TotalRevenue_Last13_24Months

The total revenue generated from the transactions that the customer made between the last 13 and 24 months.

decimal

TotalRevenuedecile_Last12Months

The total revenue generated from the transactions that the customer made in the last 12 months, grouped by deciles. Decile 1 being the highest spenders and decile 10 being the lowest spenders.

int

TotalRevenuedecile_Last13_24Months

The total revenue generated from the transactions that the customer made between the last 13 and 24 months, grouped by deciles. Decile 1 being the highest spenders and decile 10 being the lowest spenders.

int

TotalTransactionCount

The total number of transactions that the customer made in their lifetime.

int

TotalTransactionCount_Last12Months

The total number of transactions that the customer made in the last 12 months.

int

TotalTransactionCount_Last13_24Months

The total number of transactions that the customer made between the last 13 and 24 months.

int

TotalTransactionCountWithDiscount

The total number of transactions that the customer made in their lifetime, with a discount amount greater than $0.

int

TotalListPrice

The total amount of MSRP that the customer purchased before markdown or discount.

decimal

TotalDiscount

The total amount of the discount that the customer used in their lifetime.

decimal

TotalProductcategoryCount

The total number of distinct product categories that the customer purchased in their lifetime.

int

TotalProductCount

The total number of distinct products that the customer purchased in their lifetime.

int

LastOpenDate

The date when the customer last opened an email campaign.

timestamp

LastSendDate

The date of the last email that you sent to the customer.

timestamp

LastClickDate

The date when the customer last clicked an email campaign.

timestamp

FirstVisitDate

The date when the customer first visited your website.

timestamp

LastVisitDate

The date when the customer last visited your website.

timestamp

TotalVisitCountLast30Days

The total number of times the customer visited the website in the last 30 days.

int

TotalVisitCount31_60Days

The total number of times the customer visited the website between the last 31 and 60 days.

int

TotalClickCountLast30Days

The total number of times the customer clicked the links of an email campaign in the last 30 days.

int

TotalClickCount31_60Days

The total number of times the customer clicked the links of an email campaign between the last 31 and 60 days.

int

TotalOpenCount31_60Days

The total number of emails that the customer opened between the last 31 and 60 days.

int

TotalOpenCountLast30Days

The total number of emails that the customer opened in the last 30 days.

int

TotalSendCount31_60Days

The total number of emails sent to the customer between the last 31 and 60 days.

int

TotalSendCountLast30Days

The total number of emails sent to the customer in the last 30 days.

int

PrimaryBrand

The brand that the customer purchased the most in their lifetime based on the total transaction count. The default value is Unknown.

string

PrimaryOrganization

The organization from which the customer purchased the most in their lifetime. The default value is Unknown.

string

Country

The country of the winning address of the customer. The default value is Unknown. By default, CDP promotes addresses that are DPV, certified, recent, opted-in to receive mails, and complete.

string

CountryCode

The two-letter country code (ISO 3166-1 alpha-2) of the winning address of the customer. The default value is Unknown.

string

State

The state of the winning address of the customer. The default value is Unknown. By default, CDP promotes addresses that are DPV, certified, recent, opted-in to receive mails, and complete.

string

City

The city of the winning address of the customer. The default value is Unknown. By default, CDP promotes addresses that are DPV, certified, recent, opted-in to receive mails, and complete.

string

ZipCode

The 5-digit zip code of the winning address of the customer. The default value is Unknown. By default, CDP promotes addresses that are DPV, certified, recent, opted-in to receive mail, and complete.

string

Gender

The gender of the customer as provided by your standard customer feed or as deduced by CDP’s Identity Resolution Engine based on their first name. The default value is Unknown.

string

FirstPromoID

The marketing campaign through which you acquired the customer, as provided by your analytics data. For example, Google Analytics or Adobe Analytics. This is the most granular level of your marketing campaign categorization, typically the keyword level. The default value is Unknown.

string

FirstPromoCategoryID

The promo category of the first online promo (FirstPromoID).

string

AovGroup

The average value of the transactions that the customer made in their lifetime, grouped into buckets for easier use.

string

AverageDiscountRate

The average rate of discount that the customer used in their lifetime.

string

CategoryMixGroup

The total number of distinct product categories that the customer purchased in their lifetime, grouped into buckets for easier use.

string

DaySinceFirstWebLogin

The number of days since the customer first visited your website.

int

DaySinceFirstWebLoginGroup

The number of days since the customer first visited your website, grouped into buckets for easier use.

string

DaySinceLastWebLogin

The number of days since the customer last visited your website.

int

DaySinceLastWebLoginGroup

The number of days since the customer last visited your website, grouped into buckets for easier use.

string

FirstTransactionRevenueGroup

The revenue generated from the first transaction of the customer, grouped into buckets for easier use.

string

LastTransactionInterval

The number of days between the last and penultimate (second to last) transactions that the customer made.

int

RecencyDaysGroup

The number of days since the last transaction that the customer made, grouped into buckets for easier use.

string

RecencyMonth

The number of months since the last transaction that the customer made.

string

Frequency

The number of transactions that the customer made per year on an average in their lifetime. If the customer is a non-buyer, the customer will have a value of -99.

double

TenureDays

The number of days since the first transaction that the customer made.

int

TenureDaysGroup

The number of days since the first transaction that the customer made, grouped into buckets for easier use.

string

TotalClickCount31_60DaysGroup

The total number of times the customer clicked the links of an email campaign between the last 31 and 60 days, grouped into buckets for easier use.

string

TotalClickCountLast30DaysGroup

The total number of times the customer clicked the links of an email campaign in the last 30 days, grouped into buckets for easier use.

string

TotalOpenCount31_60DaysGroup

The total number of emails that the customer opened between the last 31 and 60 days, grouped into buckets for easier use.

string

TotalOpenCountLast30DaysGroup

The total number of emails that the customer opened in the last 30 days, grouped into buckets for easier use.

string

TotalSendCount31_60DaysGroup

The total number of emails sent to the customer between the last 31 and 60 days, grouped into buckets for easier use.

string

TotalSendCountLast30DaysGroup

The total number of emails sent to the customer in the last 30 days, grouped into buckets for easier use.

string

TotalProductCountGroup

The total number of distinct products that the customer purchased in their lifetime, grouped into buckets for easier use.

string

TotalRevenue_Last13_24MonthsGroup

The total revenue generated from the transactions that the customer made between the last 13 and 24 months, grouped in buckets for easier use.

string

TotalRevenue_Last12MonthsGroup

The total revenue generated from the transactions that the customer made in the last 12 months, grouped into buckets for easier use.

string

TotalRevenueGroup

The total revenue generated from the transactions that the customer made in their lifetime, grouped into buckets for easier use.

string

TotalRevenueSegment_13_24Months

An easier way to use the “Revenue decile”:

  • “1 - High Value” represents your top 10% customers in terms of revenue on this period

  • “2 - Medium Value” represents your 80% average customers, that is, not in the top/bottom 10% in terms of revenue in this period

  • “3 - Low Value” represents your bottom 10% customers in terms of revenue in this period

string

TotalRevenueSegment_Last12Months

An easier way to use the “Revenue decile”:

  • “1 - High Value” represents your top 10% customers in terms of revenue on this period

  • “2 - Medium Value” represents your 80% average customers, that is, not in the top/bottom 10% in terms of revenue in this period

  • “3 - Low Value” represents your bottom 10% customers in terms of revenue in this period

string

TotalTransactionCount_13_24MonthsGroup

The total number of transactions that the customer made between the last 13 and 24 months, grouped into buckets for easier use.

string

TotalTransactionCount_Last12MonthsGroup

The total number of transactions that the customer made in the last 12 months, grouped into buckets for easier use.

string

TotalTransactionCountGroup

The total number of transactions that the customer made in their lifetime, grouped into buckets for easier use.

string

TotalVisitCount31_60DaysGroup

The total number of times the customer visited the website between the last 31 and 60 days, grouped into buckets for easier use.

string

TotalVisitCountLast30DaysGroup

The total number of times the customer visited the website in the last 30 days, grouped into buckets for easier use.

string

Zipconcat

A concatenation of the winning country and zip code, since some zip codes can conflict across countries.

string

Age

The age of the customer. This comes from your standard customer feed.

int

BirthMonth

The birth month of the customer. This comes from your standard customer feed.

string

BirthYear

The birth year of the customer. This comes from your standard customer feed.

string

FrequencyGroup

The number of transactions that the customer made per year on an average in their lifetime, grouped into buckets for easier use.

string

TotalRevenueSegment_SegmentationStatus

A segmentation representing the progression of revenue for your customers between 13-24 months ago and the last 12 months. It characterizes whether they spent less (Downward), the same (Stable), or more (Upward) in the last 12 months when compared to the 13-24 months ago period. If a customer was acquired in the last 12 months, then they are marked as “New Buyer”. “Reactivated” customers are buyers who made a purchase in the last 12 months, but did not make a purchase in the previous 13-24 months, and made a transaction before the last 24 months. “2 Period Inactive” customers are buyers who did not make a purchase in the last 24 months, but made a purchase before 24 months ago. “Lapsed” customers are buyers who made a purchase in the last 13-24 months, but did not make a transaction in the last 12 months.

string

LastTransactionIntervalGroup

The number of days between the last and penultimate (second to last) transactions that the customer made, grouped into buckets for easier use.

string

EmailDomain

The domain of the customer’s email address. The default value is Unknown.

string

ClosestStore

The name of the store closest to the winning zip code of the customer. The default value is Unknown.

string

ClosestStoreDistance

The distance, in miles, between the location of the customer and the closest store. This filter can work only for US, Canada and UK addresses for both stores and customers.

int

ClosestStoreDistanceGroup

The distance, in miles, between the location of the customer and the closest store, grouped into buckets for easier use. The default value is Unknown. This filter can work only for US, Canada and UK addresses for both stores and customers.

string

TotalOrganizationCount

The total number of organizations or transaction channels from which the customer purchased in their lifetime.

int

FirstTransactionOrganization

The organization or transaction channel from which the customer made the first purchase. The default value is Unknown.

string

LastTransactionOrganization

The organization or transaction channel from which the customer made the last purchase. The default value is Unknown.

string

PrimaryStore

The physical store from which the customer purchased the most in their lifetime. The default value is Unknown.

string

Email

The winning email address of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

EmailOptInDate

The date when the customer opted in to email communication.

string

EmailOptOutDate

The date when the customer opted out of email communication.

string

CustomerStatus

A flag to distinguish between customers who are buyers and non-buyers. Customers who have ever made a purchase are considered buyers. The following are the statuses:

  • Buyer

  • NonBuyer

  • High Volume Buyer

  • 10k+ transactions

  • NA

string

Preferences

This is a deprecated field. Use DoNotEmail, DoNotMail, DoNotCall, and DoNotText instead.

string

BuyerIdentification

A flag to distinguish individuals that are identified with an email address or other contact information, as opposed to anonymous customers. It can be either identified or unidentified.

string

FirstName

The winning first name of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

MiddleName

The winning middle name of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

LastName

The winning last name of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

Title

The title to use for the customer as provided by your standard customer feed. The default value is Unknown.

string

SAL

The salutation deduced by CDP’s Identity Resolution Engine.

string

PrimaryPhone

The winning phone number of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

SecondaryPhone

The winning secondary phone number of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

MobilePhone

The winning mobile phone number of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

Address1

The Address1 of the winning address of the customer. The default value is Unknown. By default, CDP promotes addresses that are DPV, certified, recent, opted-in to receive mail, and complete.

string

Address2

The Address2 of the winning address of the customer. The default value is Unknown. Address2 should not be used for direct mail campaigns. Choose Address 1 and Suite for direct mail campaigns.

string

TotalCost

The total cost of goods of all products that the customer purchased in their lifetime.

decimal

TotalCost_Last12Months

The total cost of goods of all products that the customer purchased in the last 12 months.

decimal

TotalCost_Last13_24Months

The total cost of goods of all products that the customer purchased between the last 13 to 24 months.

decimal

PrimaryDeviceType

The device that the customer used the most for their purchases in their lifetime. The default value is Unknown.

string

RecencyDays

The number of days since the last transaction of the customer.

int

PropensityToBuyDecile

A deprecated machine learning model that has been replaced with a new model.

string

PropensityToEngageDecile

A deprecated machine learning model that has been replaced with a new model.

string

PropensityToConvertDecile

A deprecated machine learning model that has been replaced with a new model.

string

BehaviorBasedClusterID

A deprecated machine learning model that has been replaced with a new model.

string

PurchaseClusterID

A deprecated machine learning model that has been replaced with a new model.

string

BrowseClusterID

A deprecated machine learning model that has been replaced with a new model.

string

ProductClusterID

A deprecated machine learning model that has been replaced with a new model.

string

BrandClusterID

A deprecated machine learning model that has been replaced with a new model.

string

EmailStatus

The validity of the email address of the customer.

  • “V” means “Verified” and the syntax is valid, the domain is good or known and this email address is not a known spam trap

  • “U” means “Unverified” and the syntax is valid, but the domain is unknown/bad

  • “X” means “Invalid” and the syntax is invalid

string

AddressCertified

A flag to mark whether the address is CASS certified by the USPS.

  • “True” means that the address is CASS certified

  • “False” means that the address is not CASS certified and is likely invalid

  • “Unknown” means that there is no address or the address is not in the US or Canada. Currently, these are the only countries that are certified.

string

Suite

The apartment, suite, or room number of the winning address.

string

DpvConfirm

The DPV confirmation results that CDP receives from USPS for US addresses. The new filter is called “DPV Confirmed”, and can take the values:

  • “Y” when the address is DPV confirmed

  • “N” when the address is not certified.

string

FirstOfflinePromoID

The marketing campaign through which you acquired your customers, as provided by your direct mail. This is the most granular level of your marketing campaign categorization. The default value is Unknown.

string

DoNotEmail

The subscription preference of the customer for email campaigns. The possible values are:

  • “Y” indicates that the contacts explicitly opted-out

  • “N” indicates that the contacts explicitly opted-in

  • “U” indicates that the contacts have no explicit values and are usually the contacts that you can/want to contact

string

DoNotCall

The subscription preference of the customer for phone campaigns. The possible values are:

  • “Y” indicates that the contacts explicitly opted-out

  • “N” indicates that the contacts explicitly opted-in

  • “U” indicates that the contacts have no explicit values and are usually the contacts that you can/want to contact

string

DoNotText

The subscription preference of the customer for text (SMS) campaigns. The possible values are:

  • “Y” indicates that the contacts explicitly opted-out

  • “N” indicates that the contacts explicitly opted-in

  • “U” indicates that the contacts have no explicit values and are usually the contacts that you can/want to contact

string

DoNotMail

The subscription preference of the customer for direct postal mail campaigns. The possible values are:

  • “Y” indicates that the contacts explicitly opted-out

  • “N” indicates that the contacts explicitly opted-in

  • “U” indicates that the contacts have no explicit values and are usually the contacts that you can/want to contact

string

BirthDate

The birth date of the customer. This comes from your standard customer feed.

timestamp

ZipExt

The 4-digit zip code extension of the winning address of the customer. The default value is Unknown. By default CDP, promotes addresses that are DPV, certified, recent, opted-in to receive mail, and complete.

string

NCOADateUpdate

The date when the record last went through NCOA processing.

string

NCOAMatchFlag

The match flag that was returned by NCOA processing. The possible values are:

  • “M” for Moved

  • “G” for PO Box Closed

  • “K” for Moved, left no forwarding address

  • “F” for Moved to a Foreign Country

string

MoveType

The type of move that was registered within the USPS NCOA database. The possible values are:

  • “I” for Individual Match

  • “F” for Family Match

  • “B” for Business Name Match

string

MoveDate

The date when the move was registered with USPS in the NCOA database. This is in the format “YYYYMM”. For example, “201905” is a move that was registered in January 2014. The actual move could have happened earlier or later, the NCOA database cannot provide that information.

string

NCOADateUpdateBigInt

The date when the move was registered with USPS in the NCOA. database.

timestamp

PrimaryPhoneValidity

Returned by Melissa to indicate the primary phone validity. The possible values are V, X, and Unidentified. By default, phone validation is turned off.

string

SecondaryPhoneValidity

Returned by Melissa to indicate the secondary phone validity. The possible values are V, X, and Unidentified. By default, phone validation is turned off.

string

MobilePhoneValidity

Returned by Melissa to indicate the mobile phone validity. The possible values are V, X, and Unidentified. By default, phone validation is turned off.

string

PropensityToBuyDecile_L1M

A deprecated machine learning model that has been replaced with a new model.

string

PropensityToBuyDecile_L2M

A deprecated machine learning model that has been replaced with a new model.

string

BehaviorBasedClusterID_L1M

A deprecated machine learning model that has been replaced with a new model.

string

BehaviorBasedClusterID_L2M

A deprecated machine learning model that has been replaced with a new model.

string

PropensityToBuyDecile_ThisMonth

A deprecated machine learning model that has been replaced with a new model.

string

BehaviorBasedClusterID_ThisMonth

A deprecated machine learning model that has been replaced with a new model.

string

EmailHash

A SHA-256 hash of the winning email address of the master customer after CDP’s Identity Resolution Engine de-duplicates child customer profiles.

string

RBDI

A flag to indicate whether the address is a business or residential address for the United States only:

  • "R" - Residential Address

  • "B" - Business Address

  • "U" - No address or address unknown by the USPS

string

AddressType

Example Address Types: Firm or Company address, General Delivery address, High Rise or Business complex, PO Box address, Rural Route address, Street or Residential address

string

ProductClusterID_L1M

A deprecated machine learning model that has been replaced with a new model.

string

ProductClusterID_L2M

A deprecated machine learning model that has been replaced with a new model.

string

ProductClusterID_ThisMonth

A deprecated machine learning model that has been replaced with a new model.

string

ClosestStoreID

The unique identifier of the store closest to the winning zip code of the customer.

string

PrimaryStoreID

The unique identifier of the physical store from which the customer purchased the most in their lifetime.

string

ChannelMix

The mix of sales channels from which the customer purchased in their lifetime.

string

ChannelMix_Last12Months

The mix of sales channels from which the customer purchased in the last 12 months.

string

ChannelMix_Last13_24Months

The mix of sales channels from which the customer purchased between the last 13 and 24 months.

string

Did not find what you were looking for?

If this content did not answer your questions, try searching or contacting our support team for further assistance.

Acquia Help

Filter by product:

Interactive Queries common questions