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. |
| 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. |
| 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. |
Customers
| 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. |
Subscriptions
| Report column name | Table.column_name | type | description |
| Subscription ID | subscriptions.subscription_id | bigint | It is a unique identifier of a subscription (recurring offer purchased by a specific customer) at Cleeng. |
| Customer ID | subscriptions.customerid | bigint | Customer identification in Cleeng. A unique reference ID for each customer that has a Cleeng account. |
| First name | customer.first_name | varchar(255) |
Customer first name source: Cleeng Capture |
| Last name | customer.last_name | varchar(255) |
Customer last name source: Cleeng Capture |
| customer.email | varchar(128) |
Customer email |
|
| 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. |
| Account activation date | customer.account_activation _date |
date |
Date of the account activation |
| Last login date | customer.last_login_date | date |
Date of the customer last login |
| Offer title | offer.offerid_title | varchar(255) |
The title of the subscription offer |
| Offer ID | subscriptions.offerid | 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 | offer.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 | transactions.offer_price | decimal(35,6) |
Value of the offer price without tax in ‘offer currency’ currency |
| Offer currency | transactions.offer_currency | varchar(3) |
The currency in which the offer is set by the broadcaster |
| Offer period | offer.offer_period | varchar(10) |
Subscription renewal period (ex. monthly, 3-months, 6-months) |
| Subscription status | subscriptions.subscription _status |
string |
Current status of the subscription. Binary variable: ‘active’, ‘churned’ |
| 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. |
| Last transaction date | transactions.transaction _date_date |
date |
Day on which the last transaction related to the subscription was completed |
| No. of transactions | transactions_latest.count _transactions_per_subscription |
int |
Number of transactions completed for the particular subscription |
| Subscription lifetime revenue | transactions_latest.subscription _publisherrevenuepcurr _ever | decimal(36,6) |
Overall net revenue from the particular subscription in the broadcaster's currency |
| Campaign | campaign.campaign_name | varchar(255) |
The name of a campaign, if a coupon was used on the last transaction |
| Campaign (last transaction) | campaign_latest_ transaction.campaign_name |
varchar(255) |
The name of the campaign, if a coupon was used on the last transaction |
| Subscription start date | subscription_acquisition. transaction_date_time | time |
Date with the precise time in which subscription started |
| Cancellation date | subscription_renewal. cancellation_date_derived | date |
Date on which customer canceled a subscription |
| Cancellation reason | subscription_renewal.churn _reason_original |
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 | subscription_renewal.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 | subscription_renewal.expiry _date | date |
Date on which the subscription expired or will expire |
| Marketing terms | customer.marketingconsentstate | 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 | subscription_acquisition. distribution_channel | varchar(20) |
Store or method that the subscription was bought through (Apple, Android, Web) |
| Latest subscription | subscriptions.flag_latest _subscription |
int |
Binary variable, 1 - if the subscription is the latest for particular customer, 0 - if not |
| Latest timestamp in subscription history | subscriptions.ssoh_latest_ timestamp_time |
time |
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 | subscriptions.ssoh_latest_ timestamp_time |
time |
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 | customer.customer_external_id |
varchar(255) |
External Customer unique identifier, if populated in the payment process. |
| Subscription lifetime (days) | subscriptions.subscription _lifetime_in_days |
number |
Number of days particular subscription is/was active |
Refunds
| Report column name | Table.column_name | type | description |
| Cancellation ID | refund.cancellation_id | bigint | An identifier of the cancellation of payment (refund or chargeback) |
| Offer ID | refund.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 | refund.offer_title | string | The name/title of the canceled (refunded) offer |
| Timestamp Time | refund.timestamp_time | time | Date and time of the cancellation |
| Cancellation Reason | refund.cancellation_reason | string | Reason why the cancellation was granted |
| Cancellation Type | refund.cancellation_type | string | Type of the cancellation. Binary variable: refund or chargeback |
| Cancelled Revenue | refund.cancelled_revenue | decimal(35,6) | Value of the refund in the currency displayed in the ‘Cancellation currency’ column |
| Cancellation Currency | refund.cancellation_currency | string | Currency in which refund was completed |
| Cancellation Fee | refund.cancellation_fee | decimal(35,6) | Value of the cancellation fee |
| Reversed Transaction ID | refund.reversed_transaction_id | string | A unique identifier (ID) of the transaction which is refunded (reversed). Cancellations always refer to the initial transaction. |
| Customer Email |
refund.customer_email |
string | Customer email |
| Customer External ID |
customer.customer_external_id |
varchar(255) | External Customer unique identifier, if populated in the payment process. |
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 |
Related content
Please refer to How to export data to other tools to find out more about exporting data.