See how to export data from ChurnIQ.
Each table represents a single file export. Each column has two names, one is the "source" name, which will be visible when no formatting is applied in the export (this is an option when scheduling - if you require exports with or without formatting). The second is our source of data which was used for a particular report.
Note: All dates and times are in CET.
Transactions Report
| Report column name | Table.column_name | type | description |
| Transaction ID | transactions.transaction_id | varchar(50) | A unique transaction identifier (ID) that each transaction has. |
| Transaction Country | transactions.transaction_country | varchar(255) | The country assigned to where the transaction was made. |
| Date |
transactions.transaction_date |
date | The day on which the transaction was completed. |
| Date / Time |
transactions.transaction_date |
time | The day with the specific time on which the transaction was completed. |
| Month |
transactions.transaction_date |
int | The month in which the transaction was completed. |
| Week |
transactions.transaction_date |
int | The week in which the transaction was completed. |
| Customer ID | transactions.customer_id | bigint | Customer identification in Cleeng. A unique reference ID for each customer that has a Cleeng account. |
| Customer email | customer.email | varchar(128) | Customer email |
| First name | customer.first_name | varchar(255) | Customer first name, if provided through Cleeng Capture |
| Last name | customer.first_name | varchar(255) | Customer last name, if provided through Cleeng Capture |
| Customer country | customer.country | varchar(3) | This is the country setting of a customer. The initial country setting is automatically selected based on their IP detection. |
| Customer IP | transactions.user_IP | decimal(35,6) | IP decimal of the last known customer IP address before the transaction was completed. |
| Customer currency | customer.currency | varchar(3) | Customer currency |
| Offer ID | transactions.offer_id | varchar(16) |
Cleeng’s offer identifier of the transaction. The structure is as follows: the first letter defines the type of offer. It is followed by 9 numbers, underscore, and country code, e.g. S123456789_PL. |
| Offer title | offer.offerid_title | varchar(255) | The name/title of an offer |
| Offer Group ID Title | offer.offer_group_id_title | varchar(255) | The name/title of the whole group of offers in case there are multilingual titles for offers dedicated for particular countries. |
| Offer type | transactions.offer_type | varchar(16) |
A parameter of the transaction offer. The Cleeng framework supports multiple offer types. They are all accessible via API and can be integrated within any CMS or platform. - Live PPV (Event offer, Live offer) - Pass (Season Pass) - Subscription offer (deprecated term: subscription plan) - TVOD (Single offer, Rental offer) |
| Offer period | offer.offer_period | varchar(10) | Defines how long your customer will have access to your offer after they make a purchase. It is applicable to rental offers that give access for an exact period of time and subscription offers that indicate the recurring period of a purchase (monthly, annual, etc). |
| Offer currency | transactions.offer_currency | varchar(3) | The currency in which the offer is set by the broadcaster. |
| Offer price (excl. TAX) | transactions.offer_price | decimal(35,6) | Value of the offer price without tax in offer currency. |
| Campaign ID | campaign.campaign_id | int | A unique identifier of the specific campaign, if a coupon was applied to discount the transaction. |
| Campaign name | campaign.campaign_name | varchar(255) | The name of the coupon campaign, if a coupon was applied to discount the transaction. |
| Coupon code | transactions.coupon_code | varchar(65) | A code used for promotions or support purposes that can be generated and distributed by a broadcaster. A customer can enter this to receive free access or a discount. See more here. |
| Discount rate | transactions.discount_rate | decimal(35,6) | The percentage of a discount applied on your offer price. |
| Discount value | transactions.discount_value | decimal(35,6) | Value of the discount applied on an offer price |
| Discounted offer price | transactions.offer_total_price | decimal(35,6) | Price after subtracting the discount (without tax) in offer currency. |
| Publisher currency |
transactions.publisher |
varchar(3) | Broadcaster's currency, usually the same as the ‘offer currency’ but not necessarily. |
| Transaction price in publisher currency (excl. TAX) |
transactions.publisher_revenue |
decimal(35,6) | Value of the transaction in the broadcaster currency without tax. |
| Payment method |
transactions.payment_method |
varchar(30) | The method used by a customer to make a purchase - Mastercard, Visa, AMEX, iDEAL, sms, ivr, direct banking, etc. |
| Payment type |
payments.type |
varchar(10) | Type of the payment. ‘Initial’ for first transactions, ‘recurring’ for each non-first transaction of each subscription. |
| Transaction currency |
transactions.transaction |
varchar(3) | Currency in which the transaction was completed. |
| Transaction price (excl. TAX) |
transactions.transaction |
decimal(35,6) | Value of the transaction without tax in the transaction currency. |
| Applicable tax |
transactions.payment |
decimal(35,6) | Value of the tax |
| Transaction price (incl. TAX) |
transactions.transaction |
decimal(35,6) | Value of the transaction with tax in the transaction currency. |
|
Merchant of Record |
transactions.merchant_of_record |
varchar(255) | Legal entity that handles all payments, takes on the liability related to a transaction. It's Cleeng or the Broadcaster. |
| Payment external ID |
transactions.payment |
string | External payment ID, if populated in the payment process through the APIs and webhooks. |
| Transaction external data |
transactions.transaction |
string | Additional information about the transaction delivered by the broadcaster. |
| Customer External ID |
customer.customer |
varchar(255) | External Customer unique identifier, if populated in the payment process. |
| Subscription ID |
transactions.subscriptionId |
Int | Unique identifier of the subscription |
| Subscription Switch Direction |
transactions.subscription |
string | Upgrade or downgrade (based on your switch settings). |
| Subscription Switch From Offer ID |
transactions.subscription |
string | ID of the subscription that was changed (switched from). |
| Subscription Switch ID |
transactions.subscription |
string | A unique reference ID for each switch. |
User Report
| Report column name | Table.column_name | type | description |
| Customer ID | customer.id | bigint | A unique reference ID for each customer. |
| customer.email | varchar(128) | Customer email | |
| First name | customer.first_name | varchar(255) | Customer first name, if provided through Cleeng Capture |
| Last name | customer.last_name | varchar(255) | Customer last name, if provided through Cleeng Capture |
| Date of birth | customer.date_of_birth | date | Customer date of birth, if provided through Cleeng Capture |
| Country | customer.country | varchar(3) | Country setting of a customer. The initial country setting is automatically selected based on their IP detection. |
| Company name | customer.company_name | varchar(255) | Customer company name, if provided through Cleeng Capture |
| Phone number | customer.phone_number | varchar(30) | Customer phone number, if provided through Cleeng Capture |
| Address line 1 | customer.address_line_1 | varchar(155) | Customer address, if provided through Cleeng Capture |
| Address line 2 | customer.address_line_2 | varchar(155) | Customer address, if provided through Cleeng Capture |
| City | customer.city | varchar(70) | Customer’s city, if provided through Cleeng Capture |
| State | customer.state | varchar(100) | Customer’s state, if provided through Cleeng Capture |
| Postal code | customer.postal_code | varchar(60) | Customer’s postal code, if provided through Cleeng Capture |
| Account activation date | customer.account_activation _date |
date | Date of the account creation |
| Last login date | customer.last_login_date | date | Date of the customer's last login |
| No. of transactions | transactions_latest.count _transactions_per_customer |
number | The number of transactions that a given customer has completed |
| Payment method | transactions.payment_method | varchar(30) | The method used by a customer to make the last purchase - Mastercard, Visa, AMEX, iDEAL, sms, ivr, direct banking, etc. |
| Payment method expires | customer.payment_method_ expiry_date |
date | Date of the customer payment method expiration |
| Cleeng terms | customer.cleeng_terms_state | varchar(10) | Customer consent to Cleeng terms. Binary variable: accepted or declined |
| Broadcaster terms | customer.broadcaster_terms _state |
varchar(10) | Customer consent to broadcaster terms. Binary variable: accepted or declined |
| Marketing terms | customer.marketingconsentstate | varchar(10) | Customer consent to marketing terms. It can take one of three values: accepted, declined, or not specified when a customer does not provide an answer |
| Custom answer date | customer.question_answered_at | time | Date on which customer answered questions |
| Custom question 1 | customer.question1 | varchar(400) |
Question number 1 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 1 | customer.answer1 | varchar(400) |
Customer’s answer to question 1 if provided source: Cleeng Capture |
| Custom question 2 | customer.question2 | varchar(400) |
Question number 2 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 2 | customer.answer2 | varchar(400) |
Customer’s answer to question 2 if provided source: Cleeng Capture |
| Custom question 3 | customer.question3 | varchar(400) |
Question number 3 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 3 | customer.answer3 | varchar(400) |
Customer’s answer to question 3 if provided source: Cleeng Capture |
| Custom question 4 | customer.question4 | varchar(400) |
Question number 4 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 4 | customer.answer4 | varchar(400) |
Customer’s answer to question 4 if provided source: Cleeng Capture |
| Last subscription start date | subscriptions.start_dt_date | date |
Date on which the last subscription for the particular subscriber started |
| Last subscription end date | subscriptions.end_dt_date | date |
Date on which the last subscription for the particular subscriber ended |
| Last Login Formatted | customer.lastLoginDate | time |
Date and time of last login of a customer formatted into 'YYYY-MM-DD HH24:MI:SS' |
| Account Activation Formatted | customer.account ActivationDate | time |
Date and time of customers’ account activation formatted into 'YYYY-MM-DD HH24:MI:SS' |
| User Updated At |
customer.user_updated_at |
time |
Date and time of last update of customers’ data formatted into 'YYYY-MM-DD HH24:MI:SS'. Usually that’s also Last Login Date. |
| Customer External ID | customer.customer_external_id | varchar(255) |
External Customer unique identifier, if populated in the payment process. |
Subscription Report
| Report column name | Table.column_name | type | description |
| Subscription ID | vis__lkr__subscription_report.”Subscription ID” |
bigint | It is a unique identifier of a subscription (recurring offer purchased by a specific customer) at Cleeng. |
| Customer ID | vis__lkr__subscription_report."CUSTOMER ID" | bigint | Customer identification in Cleeng. A unique reference ID for each customer that has a Cleeng account. |
| First name | vis__lkr__subscription_report."FIRST NAME" | varchar(255) |
Customer first name source: Cleeng Capture |
| Last name | vis__lkr__subscription_report."LAST NAME" | varchar(255) |
Customer last name source: Cleeng Capture |
| vis__lkr__subscription_report."EMAIL" | varchar(128) |
Customer email |
|
| Country | vis__lkr__subscription_report."COUNTRY" | varchar(3) |
This is the country setting of a customer. The initial country setting is automatically selected based on their IP detection. |
| Account activation date |
vis__lkr__subscription_report."ACCOUNT ACTIVATION DATE" |
date (YYYY-MM-DD) |
Date of the account activation |
| Last login date |
vis__lkr__subscription_report."LAST LOGIN DATE" |
date (YYYY-MM-DD) |
Date of the customer last login |
| Offer title | vis__lkr__subscription_report."OFFER TITLE" | varchar(255) |
The title of the subscription offer |
| Offer ID |
vis__lkr__subscription_report."OFFER ID " |
varchar(16) |
Cleeng’s offer identifier of the subscription. The structure is as follows: the first letter defines the type of offer. It is followed by 9 numbers, underscore, and country code, e.g. S123456789_PL. |
| Offer group ID |
vis__lkr__subscription_report."OFFER GROUP ID" |
varchar(16) |
The identifier of the whole group of offers in case there are multilingual offers dedicated for particular countries. |
| Offer price |
vis__lkr__subscription_report."OFFER PRICE" |
decimal(35,6) |
Value of the offer price without tax in ‘offer currency’ currency |
| Offer currency |
vis__lkr__subscription_report."OFFER CURRENCY |
varchar(3) |
The currency in which the offer is set by the broadcaster |
| Offer period |
vis__lkr__subscription_report."OFFER PERIOD" |
varchar(10) |
Subscription renewal period (ex. monthly, 3-months, 6-months) |
| Subscription status |
vis__lkr__subscription_report."SUBSCRIPTION STATUS" |
string |
Current status of the subscription. Binary variable: ‘active’, ‘churned’ |
| Payment method |
vis__lkr__subscription_report."PAYMENT METHOD" |
varchar(30) |
The method used by a customer to make a purchase - Mastercard, Visa, AMEX, iDEAL, sms, ivr, direct banking, etc. |
| Last transaction date |
vis__lkr__subscription_report."LAST TRANSACTION DATE" |
date (YYYY-MM-DD) |
Day on which the last transaction related to the subscription was completed |
| No. of transactions |
vis__lkr__subscription_report."NO. OF TRANSACTIONS" |
int |
Number of transactions completed for the particular subscription |
| Subscription lifetime revenue |
vis__lkr__subscription_report."SUBSCRIPTION LIFETIME REVENUE" |
decimal(36,6) |
Overall net revenue from the particular subscription in the broadcaster's currency |
| Campaign |
vis__lkr__subscription_report."CAMPAIGN" |
varchar(255) |
The name of a campaign, if a coupon was used on the last transaction |
| Campaign (last transaction) |
vis__lkr__subscription_report."CAMAPIGN (LAST TRANSACTIONS)" |
varchar(255) |
The name of the campaign, if a coupon was used on the last transaction |
| Subscription start date |
vis__lkr__subscription_report."SUBSCRIPTION START DATE" |
datetime (YYYY-MM-DD HH:MM:SS) |
Date with the precise time in which subscription started |
| Cancellation date |
vis__lkr__subscription_report."CANCELLATION DATE" |
date (YYYY-MM-DD) |
Date on which customer canceled a subscription |
| Cancellation reason |
vis__lkr__subscription_report."CANCELLATION REASON" |
varchar(140) |
Reason for the subscription cancellation if provided. "Cancellation reason" is provided by the customer at the moment of cancellation. Cancellation reason is a free text field in our APIs, so it's up to the broadcaster how it's configured on the front end (for better data consistency we recommend configuring a dropdown list of predefined values in order to get more consistent insights on the cancellation reasons). |
| Churn reason |
vis__lkr__subscription_report."CHURN REASON" |
varchar(140) |
Reason for the subscription churn or cancellation reason if provided. "Churn reason" is assigned programmatically and: - if churn was involuntary it will contain the feedback from the payment gateway(ex. payment failed, card expired, not enough credit, etc.), - if churn was caused by manual cancellation it will contain the reason provided by the user during cancellation (if any). Churn reason is not editable (values from payment gateway, or the ones from cancellation reason as explained above).
|
| Expiry date |
vis__lkr__subscription_report."EXPIRY DATE" |
date (YYYY-MM-DD) |
Date on which the subscription expired or will expire. |
| Marketing terms | vis__lkr__subscription_report."MARKETING TERMS" | varchar(10) |
Customer consent to marketing terms. It can take one of three values: accepted, declined, or not specified when the customer does not provide an answer. |
| Acquisition channel | vis__lkr__subscription_report."ACQUISITION CHANNEL" | varchar(20) |
Store or method that the subscription was bought through (Apple, Android, Web) |
| Latest subscription |
vis__lkr__subscription_report."LATEST SUBSCRIPTION" |
int |
Binary variable, 1 - if the subscription is the latest for a particular customer, 0 - if not. It's a subscription that, regardless of its current status, has the most recent start date. |
| Latest timestamp in subscription history | vis__lkr__subscription_report."LATEST TIMESTAMP IN SUBSCRIPTION HISTORY" |
datetime (YYYY-MM-DD HH:MM:SS) |
Date and time of the latest event for particular subscription e.g. renewed. This parameter can be used to find the last updated subscriptions. |
| Ssoh Latest Timestamp Formatted | vis__lkr__subscription_report."SSOH LATEST TIMESTAMP FORMATTED" | datetime (DD/MM/YY HH:MM) |
Date and time (including seconds) of the latest event for particular subscription e.g. renewed. This parameter can be used to find the last updated subscriptions. |
| Customer External ID | vis__lkr__subscription_report."CUSTOMMER EXTERTNAL DATE" |
varchar(255) |
External Customer unique identifier, if populated in the payment process. |
| Subscription lifetime (days) | vis__lkr__subscription_report."SUBSCRIPTION LIFETIME (DAYS)" | number |
Number of days particular subscription is/was active |
| External properties | vis__lkr__subscription_report." EXTERTNAL PROPERTIES" | varchar(16777216) |
External properties in JSON that the broadcaster has set for the pass. |
Refunds Report
| Report column name | Table.column_name | type | description |
| Cancellation ID |
vis__lkr__refunds_report."Cancellation ID" |
bigint | An identifier of the cancellation of payment (refund or chargeback) |
| Offer ID |
vis__lkr__refunds_report."Offer ID" |
string |
Cleeng’s identifier of the canceled offer. The structure is as follows: the first letter defines the type of offer. It is followed by 9 numbers, underscore, and country code, e.g. S123456789_PL. |
| Offer Title | vis__lkr__refunds_report."Offer Title" | string | The name/title of the canceled (refunded) offer |
| Timestamp Time |
vis__lkr__refunds_report."Timestamp Time" |
time | Date and time of the cancellation |
| Cancellation Reason | vis__lkr__refunds_report."Cancellation Reason" | string | Reason why the cancellation was granted |
| Cancellation Type | vis__lkr__refunds_report."Cancellation Type" | string | Type of the cancellation. Binary variable: refund or chargeback |
| Cancelled Revenue |
vis__lkr__refunds_report."Cancelled Revenue" |
number |
Value of the refund in the currency displayed in the ‘Cancellation currency’ column |
| Cancellation Currency |
vis__lkr__refunds_report."Cancellation Currency" |
string | Currency in which refund was completed |
| Cancellation Fee |
vis__lkr__refunds_report."Cancellation Fee" |
number | Value of the cancellation fee |
| Reversed Transaction ID |
vis__lkr__refunds_report."Reversed Transaction ID" |
string | A unique identifier (ID) of the transaction which is refunded (reversed). Cancellations always refer to the initial transaction. |
| Customer Email |
vis__lkr__refunds_report."Customer Email" |
string | Customer email |
| Customer External ID |
vis__lkr__refunds_report."Customer External ID" |
varchar(255) | External Customer unique identifier, if populated in the payment process. |
Gift Report
| Report column name | Table.column_name | type | description |
| Gift ID | vis__lkr__gifts_report."Gift ID" | number(38,0) |
A unique gift identifier (ID) that each gift has. |
| Gift Code | vis__lkr__gifts_report."Gift Code" | varchar (16777216) |
Code that is used to verify and redeem the gift. |
| Gifter ID | vis__lkr__gifts_report."Gifter ID" | number(38,0) | A unique identifier (ID) of a customer, who buys the gift. |
| Offer ID | vis__lkr__gifts_report."Offer ID" |
varchar (16777216) |
Cleeng’s offer identifier of the transaction. |
| Offer Price |
vis__lkr__gifts_report."Offer Price" |
decimal (35,6) | Value of the offer price without tax in offer currency. |
| Expiration Date |
vis__lkr__gifts_report."Expiration Date" |
timestamp_ ntz(9) | The expiration date of the subscription filed only after the redemption of the gift. |
| Recipient Email | vis__lkr__gifts_report."Recipient Email” | varchar (16777216) | Email address of the gift recipient (as provided by the gifter). |
| Personal Note |
vis__lkr__gifts_report."Personal Note" |
varchar (16777216) | (optional) Note provided by the gifter, that will be delivered via email with the gift. |
| Delivery At Date |
vis__lkr__gifts_report."Delivery At Date" |
timestamp_ ntz(0) |
The date on which the recipient receives the email with the gift code. |
| Sent At Date |
vis__lkr__gifts_report."Sent At Date" |
timestamp_ ntz(0) | The date on which the email with the gift was sent to the gift recipient. |
| Redeemed At Date |
vis__lkr__gifts_report."Redeemed At Date" |
timestamp_ ntz(0) | The date on which the user redeemed the gift. |
| Redeemer ID |
vis__lkr__gifts_report."Redeemer ID" |
number(38,0) | A unique gift identifier (ID) of a customer who redeems the gift. |
|
Created At Date |
vis__lkr__gifts_report."Created At Date" |
timestamp_ ntz(9) | The date with the precise time in which the gift was purchased by the gifter. |
Segments - displayed columns
| Report column name | Table.column_name | type | description |
| Dataset generated at Time |
segmentation.data_generated |
time | Date and time in which particular dataset for segment was generated |
| Customer ID |
segmentation.customerid |
bigint | Customer identification in Cleeng. A unique reference ID for each customer that has a Cleeng account. |
| Subscription start date |
segmentation.start_dt_date |
date | The date on which subscription started |
| First name |
segmentation.firstname |
varchar(255) |
Customer’s first name source: Cleeng Capture |
| Last name |
segmentation.lastname |
varchar(255) |
Customer’s last name source: Cleeng Capture |
| Email address |
segmentation.email |
varchar(128) | Customer’s email address |
| Country |
segmentation.fltr_country |
varchar(60) | Country setting of a customer. The initial country setting is automatically selected based on their IP detection. |
| Marketing terms |
segmentation.fltr_ |
varchar(10) | Customer consent to marketing terms. It can take one of three values: accepted, declined, or not specified when the customer does not provide an answer |
| Subscription status |
segmentation.subscription_status |
varchar(30) | Current status of the subscription. Binary variable: ‘active’, ‘churned’ |
| Offer group title |
segmentation.offer_group_id_title |
varchar(255) | The name/title of the whole group of offers in case there are multilingual titles for offers dedicated for particular countries. |
| Free or paid subscription |
segmentation.fltr_offer |
varchar(30) | Type of the subscription. Binary variable: free, paid |
| Subscription lifetime revenue |
segmentation.subscription_ |
decimal(35,6) | Overall revenue from the particular subscription |
| Custom answer 1 |
segmentation.answer1 |
varchar(400) |
Customer’s answer to question one if provided source: Cleeng Capture |
| Custom answer 2 |
segmentation.answer2 |
varchar(400) |
Customer’s answer to question two if provided source: Cleeng Capture |
| Customer External ID |
customer.customer_external_id |
varchar(255) |
External Customer unique identifier, if populated in the payment process. |
Segments - all columns (some only available as filters)
| Report column name | Table.column_name | type | description |
| Dataset generated at Time | segmentation.data_generated _at_ts_time |
time | Date and time in which particular dataset for segment was generated |
| Customer ID | segmentation.customerid | bigint | A unique reference ID for each customer |
| First name | segmentation.firstname | varchar(255) |
Customer first name source: Cleeng Capture |
| Last name | segmentation.lastname | varchar(255) |
Customer last name source: Cleeng Capture |
| Email address | segmentation.email | varchar(128) | Customer email address |
| Account Activation Date Date | segmentation.accountactivationdate _date |
date | Date of the account activation |
| Last login date Date | segmentation.fltr_lastlogindate | date | Date of the customer last login |
| Country | segmentation.fltr_country | varchar(60) | Country setting of a customer. The initial country setting is automatically selected based on their IP detection. |
| Broadcaster terms | segmentation.fltr_broadcaster _terms_state |
varchar(10) | Customer consent to broadcaster terms. Binary variable: accepted or declined |
| Broadcaster terms version | segmentation.fltr_broadcaster _terms_version |
varchar(20) | Version of the broadcaster terms |
| Marketing terms | segmentation.fltr_marketing _terms_state |
varchar(10) | Customer consent to marketing terms. It can take one of three values: accepted, declined, or not specified when the customer does not provide an answer |
| Marketing terms version | segmentation.fltr_marketing _terms_version |
varchar(20) | Version of the marketing terms |
| Subscription status | segmentation.subscription_status | varchar(30) | Current status of the subscription. Binary variable: ‘active’, ‘churned’ |
| Subscription start date Date | segmentation.start_dt_date | date | The date on which subscription started |
| Subscription end date (if inactive) Date | segmentation.end_dt_date | date | The date on which subscription ended |
| Renewal/Expiry date Date | segmentation.expiration_date | date | The date on which subscription will expire |
| Subscription lifetime (days) | segmentation.subscription_ lifetime_days |
int | Number of days a particular subscription is/was active |
| Offer group ID | segmentation.offer_group_id | varchar(16) | The identifier of the whole group of offers in case there are multilingual offers dedicated for particular countries. |
| Offer group title | segmentation.offer_group_id_title | varchar(255) | The name/title of the whole group of offers in case there are multilingual titles for offers dedicated for particular countries. |
| Offer period | segmentation.offer_period | varchar(10) | Subscription renewal period (ex. monthly, 3-months, 6-months) |
| Campaign (last transaction) | segmentation.campaign_name | varchar(255) | The name of the campaign from which the last used coupon came from |
| Free or paid subscription | segmentation.fltr_offer_payment _type_period |
varchar(30) | Type of the subscription.Binary variable: free, paid |
| Conversion date (to paid) Date | segmentation.fltr_free_to_paid _conversion_date |
date | Date on which customer converted from free to paid subscription |
| Last transaction value | segmentation.fltr_price | decimal(35,6) | Value of the customers last transaction for the particular subscription |
| Last transaction currency | segmentation.fltr_currency | varchar(3) | Currency in which the last transaction was completed |
| Last transaction date Date | segmentation.transactiondate_date | date | Day in which the last transaction was completed |
| Subscription lifetime revenue | segmentation.subscription_offer_ltv | decimal(35,6) | Overall revenue from the particular subscription |
| Customer lifetime revenue | segmentation.customer_ltv | decimal(35,6) | Overall revenue from the particular customer |
| Payment method | segmentation.paymentmethod | varchar(30) | The method used by a customer to make a purchase - Mastercard, Visa, AMEX, iDEAL, sms, ivr, direct banking, etc. |
| Channel (last transaction) | segmentation.distributionchannel | varchar(20) | Store or method that was used for the last transaction |
| Number of subscriptions ever | segmentation.subscriptions_count_ ever_per_subscriber |
bigint | Number of all subscriptions that particular customer had |
| Number of active subscriptions | segmentation.fltr_active_subscriptions _count_per_subscriber |
bigint | Number of active subscriptions that particular customer has |
| Engagement level | segmentation.fltr_number_of_days _active_prior_to_now_group |
varchar(20 | The no. of days of the last 30 on which the customer has attempted to access premium content. |
| Churn risk | segmentation.fltr_churn_risk | The risk of churn assigned to the customer's subscription (*available to clients using Predict beta) | |
| Churn risk factor | segmentation.fltr_the_most_likely _reason_to_churn |
varchar(20) | The risk factor assigned to the customer's subscription (*available to clients using Predict beta) |
| Card details on file (web only) | segmentation.payment_details_ on_file |
varchar(30) | Information if the payment details are on file. For web channel distribution only. Variable can takes three values: yes, no, N/A - when there is no data |
| Payment method expiry date Date | segmentation.fltr_payment_method _expiry_date |
date | Date on which customer payment method will expire |
| Churn date Time | segmentation.churn_date_ts_time | time | Date and time when customer churned particular subscription |
| Churn reason | segmentation.churn_reason | varchar(140) | Reason of the customer churn if provided |
| Cancellation date Time | segmentation.intention_date_ts_time | time | Date with time in which customer canceled subscription |
| Cancellation reason | segmentation.intention_reason | varchar(140) | Reason of the customer cancellation if provided |
| Engagement before churning | segmentation.number_of_days _active_prior_to_churn_group |
varchar(20) | Applies only to churned subscriptions. Like engagement level, it tracks the number of days the customer accessed premium content in the 30 days before churning. |
| Custom answer at | segmentation.question_answered _at_time |
time | Date with time in which customer answered questions in Cleeng Capture |
| Custom question 1 | segmentation.question1 | varchar(400) |
Question number 1 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 1 | segmentation.answer1 | varchar(400) |
Customer’s answer to question 1 if provided source: Cleeng Capture |
| Custom question 2 | segmentation.question2 | varchar(400) |
Question number 2 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 2 | segmentation.answer2 | varchar(400) |
Customer’s answer to question 2 if provided source: Cleeng Capture |
| Custom question 3 | segmentation.question3 | varchar(400) |
Question number 3 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 3 | segmentation.answer3 | varchar(400) |
Customer’s answer to question 3 if provided source: Cleeng Capture |
| Custom question 4 | segmentation.question4 | varchar(400) |
Question number 4 provided by the broadcaster in the template source: Cleeng Capture |
| Custom answer 4 | segmentation.answer4 | varchar(400) |
Customer’s answer to question four if provided source: Cleeng Capture |
Pass Report
| Report column name | Table.column_name | type | description |
| Pass ID | vis__lkr__pass_report.pass_id | number(38,0) | The unique identifier of the pass. |
| Customer ID | vis__lkr__pass_report.customer_id | number(38,0) | The unique identifier of the customer who purchased the pass. |
| Publisher ID | vis__lkr__pass_report.publisher_id | number(38,0) |
The unique identifier of the publisher. |
| First name | vis__lkr__pass_report.first_name | varchar(16777216) | The first name of the customer. |
| Last name | vis__lkr__pass_report.last_name | varchar(16777216) | The last name of the customer. |
| vis__lkr__pass_report.email | varchar(16777216) | The email address of the customer. | |
| Country | vis__lkr__pass_report.country | varchar(16777216) | The country of the customer. |
| Account Activation Date Time |
vis__lkr__pass_report.account_activation _date_time |
varchar(16777216) | The date and time when the customer's account was activated. |
| Last Login Date Time |
vis__lkr__pass_report.last_login _date_time |
varchar(16777216) | The date and time of the customer's last login. |
| Offer Title | vis__lkr__pass_report.offer_title | varchar(16777216) | The title of the offer associated with the pass. |
| Offer ID | vis__lkr__pass_report.offer_id | varchar(16777216) | The unique identifier of the offer. |
| Offer group ID | vis__lkr__pass_report.offer_group_id | varchar(16777216) | The unique identifier of the offer group. |
| Offer price | vis__lkr__pass_report.offer_price | number(38,4) | The price of the offer. |
| Offer currency | vis__lkr__pass_report.offer_currency | varchar(16777216) | The currency of the offer price. |
| Offer period | vis__lkr__pass_report.offer_period | varchar(16777216) | The period of the offer. |
| Pass status | vis__lkr__pass_report.pass_status | varchar(16777216) | The status of the pass. |
| Payment method | vis__lkr__pass_report.payment_method | varchar(16777216) | The payment method used for the pass purchase. |
| Last Transaction Date Time | vis__lkr__pass_report.last_transaction_date_time | varchar(16777216) | The date and time of the last transaction associated with the pass. |
| Campaign | vis__lkr__pass_report.campaign_name | varchar(16777216) | The name of the campaign associated with the pass. |
| Coupon campaign ID | vis__lkr__pass_report.coupon_campaign_id | number(38,0) | The unique identifier of the coupon campaign. |
| Coupon ID | vis__lkr__pass_report.coupon_id | number(38,0) | The unique identifier of the coupon. |
| Pass Start Date Time | vis__lkr__pass_report.pass_start_date_time | varchar(16777216) | The date and time when the pass starts. |
| Pass End Date Time | vis__lkr__pass_report.pass_end_date_time | varchar(16777216) | The date and time when the pass ends. |
| Marketing Terms | vis__lkr__pass_report.marketing_terms_state | varchar(16777216) | The state of the marketing terms. |
| Acquisition Channel | vis__lkr__pass_report.acquisition_channel | varchar(19) | The channel through which the pass was acquired. |
| Transaction ID | vis__lkr__pass_report.transaction_id | varchar(16777216) | The unique identifier of the transaction associated with the pass. |
| Transaction value | vis__lkr__pass_report.transaction_value | float | The value of the transaction. |
| Transaction currency | vis__lkr__pass_report.transaction_currency | varchar(16777216) | The currency of the transaction value. |
| Customer External ID | vis__lkr__pass_report.customer_external_id | varchar(16777216) | The external identifier of the customer. |
| External Properties | vis__lkr__pass_report.external_properties | varchar(16777216) | External properties in JSON that the broadcaster has set for the pass. |
Related content
Please refer to How to export data to other tools to find out more about exporting data.