TargetPlus (bi_metrics)
- Chris Laun
Owned by Chris Laun
Feb 28, 2023
4 min read
Loading data...
Overview: This is data that is generated by the D&A team. The data is aggregated by Affiliate at the super customer number level and then the same values are pushed into Lytics for all of the associated email addresses. Often the same person can wind up with multiple Advantage customer numbers, and use multiple email addresses. Super customer number is an identifier that allows the joining of data across those different identifiers. Because of this, active counts using these fields will be higher than reporting (or active counts using fields in other D&A streams that don't use super customer number (e.g. bi_subscriptions). Email addresses will only be included in this stream if there is an established business connection between it and the Affiliate.
Example: A person has 3 affiliate signups with 3 different email addresses. They've also purchased 2 pub codes with one of those email addresses which they spent $100 on. This stream will send a row for each of the 3 email addresses reporting each of them as active on all 3 lists and 2 publications, and with a Lifetime Value of $100.
Frequency: Daily
Data Source: Advantage
Delivery Method: SFTP to csv import work
Filename: TargetPlus_<LyticsGroup>_<Date>_<Time>.csv
LQL Advice: All of these attributes are unique to the affiliate. If a Lytics account contains multiple Affiliate values, that field will need to
| Field Name | Description | Sample Value |
|---|---|---|
| LyticsGroup | D&A field used to group multiple Affiliates (based on reporting definition of Affiliate) to the same Lytics instance | Agora Financial |
| Affiliate | The affiliate who has a business relationship with the customer | International Living |
| EmailAddress | Email Address of the customer | sample@example.com |
| CTM_NBR | Advantage identifier of a customer | 000012345678 |
| Active_on_List_Codes | Comma separated list of all list code values that the customer is active on (across all customer numbers and email addresses) | ILPOSTCA,ILPPAID,ILVDED,ILVMARKE |
| Unsub_on_List_Codes | Comma separated list of all list code values that the customer has unsubscribed from (across all customer numbers and email addresses) | ILOSINCO,PATHFIND |
| Spam_on_List_Codes | Comma separated list of all list code values that have marked the customer as spam (across all customer numbers and email addresses) | ILOSINCO,PATHFIND |
| Bounced_on_List_Codes | Comma separated list of all list code values that have bounced for this customer (across all customer numbers and email addresses) | ILOSINCO,PATHFIND |
| Active_on_Back_End_Items | Comma separated list of all backend pub codes (CIR) belonging to this affiliate that the customer is active on (across all customer numbers and email addresses) | CRI,RET |
| Active_on_Front_End_Items | Comma separated list of all frontend pub codes (CIR) belonging to this affiliate that the customer is active on (across all customer numbers and email addresses) | ILV,INA |
| Active_on_Mega_Items | Comma separated list of all mega/lifetime pub codes (CIR) belonging to this affiliate that the customer is active on (across all customer numbers and email addresses) | RND,TWA |
| Active_on_All_Items | Comma separated list of all pub codes (CIR) belonging to this affiliate that the customer is active on (across all customer numbers and email addresses) | CRI,ILV,INA,RET,TVP |
| Active_Item_Count | Count of active pub codes (CIR) belonging to this affiliate that the customer is active on (across all customer numbers and email addresses) | 2 |
| Active_on_Frontend_Count | Count of total active frontends (CIR) belonging to this affiliate for this customer (across all customer numbers and email addresses) | 3 |
| Active_on_Backend_Count | Count of total active backends (CIR) belonging to this affiliate for this customer (across all customer numbers and email addresses) | 1 |
| Active_on_Free_List_Count | Count of total active free list codes (CIR) belonging to this affiliate for this customer (across all customer numbers and email addresses) | 6 |
| Remaining_Liability | Sum of all remaining liability across all pub codes (CIR) belonging to this affiliate | 79.000000 |
| FirstPurchaseDate | The date of the customer's first purchase with this affiliate | 11/17/2005 12:00:00 AM |
| Total_LTV | Total net spend for this customer with this affiliate | 1005 |
| Initial_LTV | Total amount of the first purchase made with this affiliate | 85 |
| Three_Month_LTV | Total net spend that this customer made in the first 3 months after their initial purchase | 249 |
| Six_Month_LTV | Total net spend that this customer made in the first 6 months after their initial purchase | 298 |
| Twelve_Month_LTV | Total net spend that this customer made in the first year after their initial purchase | 298 |
| NTA_Item | The first item purchased with this affiliate | ILV |
| NTA_Channel | The channel code of the NTA item that indicates where the order was placed (same as the first character of the promo code) | W |
| NTA_Vendor | The vendor list code source for the NTA item | ILPOST |
| Buyer_Flag | Indicates whether this customer has made a purchase with the current affiliate (1 = made a purchase, 0 = not made a purchase) | 1 |
| Net_Spend_Past_30_Days | Total spend by this customer, after refunds (across all customer numbers and email addresses) in the last 30 days | 49 |
| Net_Spend_Past_90_Days | Total spend by this customer, after refunds (across all customer numbers and email addresses) in the last 90 days | 49 |
| Net_Spend_Past_6_Months | Total spend by this customer, after refunds (across all customer numbers and email addresses) in the last 6 months | 89 |
| Net_Spend_Past_12_Months | Total spend by this customer, after refunds (across all customer numbers and email addresses) in the last year | 249 |
| Net_Spend_Past_24_Months | Total spend by this customer, after refunds (across all customer numbers and email addresses) in the last 2 years | 249 |
| Max_Spend | The highest value of an item purchased by this customer with the current affiliate | 199 |
| Max_Spend_Agora | The highest value of an item purchased by this customer with any Agora company (including the current affiliate) | 4500 |
| Other_Affiliate_Purchase_Flag | Indicates whether the customer has made a purchase with another Agora company (1 = has a purchase with another company, 0 = does not) | 0 |
| last_purchase_date_affiliate | The date of the last purchase this customer has made with the current affiliate | 2021-01-01 |
| last_purchase_date_financial | The date of the last purchase this customer has made with any financial Agora company | 2021-01-01 |
| last_purchase_date_non_financial | The date of the last purchase this customer has made with any non-financial Agora company (e.g. health, lifestyle) | 2021-01-01 |
com.atlassian.confluence.content.render.xhtml.migration.exceptions.UnknownMacroMigrationException: The macro 'html-macro' is unknown.