VIA Data Exports
Vimond VIA Data Exports is a standard push delivery option for getting daily delta and snapshots of all your data from the Vimond Platform. The data is delivered as UTF-8 encoded CSV files.
📘
VIA Data Exportsis an optional service. Please reach out to your Account Manager for more details on how to enable it and get started.For near-real-time data please see VIA Data Event Stream
Features
Section titled “Features”- Daily data export from Vimond databases.
- File format: UTF-8 encoded CSV files.
- Delivery: Push to configured S3 bucket, Google Storage and/or (S)FTP folder.
Entity Relationship Diagram
Section titled “Entity Relationship Diagram”
How is an Asset linked to a Category?
Assets are linked to a Category through the entity Asset Type assets.asset_type_id
Channel and Shows are also linked to the entity Asset Type categories.asset_type_id
How is a Show linked to a Channel? And a Channel to a Genre?
A Show is linked to Channel via categories.parent_id
A Channel is linked to a Genre via categories.parent_id
Category type (genre, show, channel etc) is defined via categories.level_type
How can I tell if a Channel or Show is published on a Platform?
If a given Channel or Show is published on a Platform is defined via
categories_platforms.category_id + categories_platforms.platform_id + categories_platforms.published
How can I tell if an Asset is published on a Platform?
If a given Asset is published on a Platform is defined via
assets_platforms.asset_id + assets_platforms.platform_id + assets_platforms.published + assets_platforms.live_published
How to tell if an Asset has a limited viewing windows (scheduled publishing / un-publishing) on a platform
assets_platforms.asset_id + assets_platforms.platform_id + assets_platforms.publish_start + assets_platforms.publish_end
How is a Category linked to a Publisher?
Categories are linked to Publishers through Category Metadata
categories_metadata.name = "publisher-id" + categories_metadata.value = {publisher Id} + categories_metadata.category_id = {category id}
How is a Asset linked to an Publisher?
Assets are linked to a Publisher via their Category.
What data is included?
All relevant data from our database can be exported. See above entity diagram for more info.
How often can I get the data?
By default you will get a daily delivery of “snapshot” and “delta” exports.
What is the difference between the snapshot and delta data?
The “snapshot” exports are snapshots of the database state at the time of the query being ran and contains all data from the database.
Incremental reports contains only the delta (new or changed) data from previous day.
Can I customize the date range for delta data extraction?
| Option | Default | Description |
|---|---|---|
| DATA_DELTA_DAYS | 1 | How many days of data should be included in ‘delta’ exports |
| DATA_DELTA_MIDNIGHT_TO_MIDNIGHT | 0 | Should delta queries be run from midnight to midnight (“00:00 to 23:59”)? Or from “[start of job execution hour] to [start of job execution hour-DATA_DELTA_DAYS]”? |
| DATA_DELTA_TIME_ZONE | UTC | When using DATA_DELTA_MIDNIGHT_TO_MIDNIGHT what time zone should be used? |
Can I customize what entities are exported and at what time?
Yes you can for each entity override the below defaults for when/if the job should be run. By setting a job to "" (empty value) it will not be sent at all.
We internally use cron to schedule export jobs, and all timestamps are in UTC. For more info on cron job scheduling please see https://en.wikipedia.org/wiki/Cron
CRON_SCHEDULE_ASSET_CATEGORIES="10 0 * * *" CRON_SCHEDULE_ASSET_TYPES="10 0 * * *" CRON_SCHEDULE_ASSETS_ITEMS="10 0 * * *" CRON_SCHEDULE_ASSETS_METADATA="10 0 * * *" CRON_SCHEDULE_ASSETS_PLATFORMS="10 0 * * *" CRON_SCHEDULE_ASSETS_RELATIONS="10 0 * * *" CRON_SCHEDULE_ASSETS_VIEWS="10 0 * * *" CRON_SCHEDULE_ASSETS="10 0 * * *" CRON_SCHEDULE_AUDITS="30 0 * * *" CRON_SCHEDULE_CATEGORIES_METADATA="10 0 * * *" CRON_SCHEDULE_CATEGORIES_PLATFORMS="10 0 * * *" CRON_SCHEDULE_CATEGORIES="10 0 * * *" CRON_SCHEDULE_CHURN_PREDICT="" CRON_SCHEDULE_CHURN_TRAINING="" CRON_SCHEDULE_PAYMENT_PROVIDERS="15 0 * * *" CRON_SCHEDULE_PAYMENT_STATUS_LOG="15 0 * * *" CRON_SCHEDULE_PAYMENT_TRANSACTION_CUTS="15 0 * * *" CRON_SCHEDULE_PAYMENT_TRANSACTION_TYPES="15 0 * * *" CRON_SCHEDULE_PAYMENT_TRANSACTIONS="15 0 * * *" CRON_SCHEDULE_PAYMENT_TYPES="15 0 * * *" CRON_SCHEDULE_PLATFORMS="10 0 * * *" CRON_SCHEDULE_PRODUCT_GROUP_ACCESS="15 0 * * *" CRON_SCHEDULE_PRODUCT_GROUPS="15 0 * * *" CRON_SCHEDULE_PRODUCT_PERIODS="15 0 * * *" CRON_SCHEDULE_PRODUCT_PROVIDERS="15 0 * * *" CRON_SCHEDULE_PRODUCTS="15 0 * * *" CRON_SCHEDULE_PUBLISHERS="15 0 * * *" CRON_SCHEDULE_SUBTITLES="10 0 * * *" CRON_SCHEDULE_TAGS="10 0 * * *" CRON_SCHEDULE_TENANTS="10 0 * * *" CRON_SCHEDULE_USERS_ACCESS_COMMENTS="20 0 * * *" CRON_SCHEDULE_USERS_ACCESS_CRM="20 0 * * *" CRON_SCHEDULE_USERS_ACCESS="20 0 * * *" CRON_SCHEDULE_USERS_LOGIN_LOGOUT="20 0 * * *" CRON_SCHEDULE_USERS_METADATA="20 0 * * *" CRON_SCHEDULE_USERS_PAYMENT_METHODS="20 0 * * *" CRON_SCHEDULE_USERS_PROFILES="" CRON_SCHEDULE_USERS="20 0 * * *" CRON_SCHEDULE_VIDEO_FILES_DELETED="15 0 * * *" CRON_SCHEDULE_VIDEO_FILES_PUBLISHED="15 0 * * *" CRON_SCHEDULE_VIDEO_FILES="15 0 * * *" CRON_SCHEDULE_VOUCHERS="" CRON_SCHEDULE_ACQUISITION_MANAGER_CONTRACTS="" CRON_SCHEDULE_ACQUISITION_MANAGER_EPISODES="" CRON_SCHEDULE_ACQUISITION_MANAGER_LICENSES="" CRON_SCHEDULE_ACQUISITION_MANAGER_MOVIES="" CRON_SCHEDULE_ACQUISITION_MANAGER_PERMISSIONS="" CRON_SCHEDULE_ACQUISITION_MANAGER_PROVIDERS="" CRON_SCHEDULE_ACQUISITION_MANAGER_SEASONS="" CRON_SCHEDULE_ACQUISITION_MANAGER_SHOWS="" CRON_SCHEDULE_VVS_CAMPAIGN_PRODUCTS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_CAMPAIGN_PRODUCTS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_CAMPAIGNS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_CAMPAIGNS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_DISCOUNTS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_DISCOUNTS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_POOLS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_POOLS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_PRODUCTS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_PRODUCTS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_VOUCHER_REDEMPTIONS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_VOUCHER_REDEMPTIONS_DELTA="15 0 * * *" CRON_SCHEDULE_VVS_VOUCHERS_SNAPSHOT="15 0 * * *" CRON_SCHEDULE_VVS_VOUCHERS_DELTA="15 0 * * *"This looks great, but I need real-time data… what are my options?
Contact your Vimond Technical Account Manager for information and pricing for VIA Data Event Stream .
What file format is the data delivered in?
The data is delivered as CSV files following the rfc4180 CSV standard. (ref https://tools.ietf.org/html/rfc4180)
Can I customize the CSV file format?
Yes. You can have the following options for configuring the CSV file format.
| Option | Default | Description |
|---|---|---|
| CSV_INCLUDE_HEADER | 1 (on) | Should header row be included? |
| CSV_FIELD_SEPARATOR | , | Which character should be used for field separator |
| CSV_TEXT_ROW_SEPARATOR | 0 (off - leave inline line-breaks) | Which character should be used for replacing inline line-breaks |
| CSV_TEXT_ENCLOSURE | ” | Which character should be used for text enclosure |
| CSV_ESCAPE_CHAR | ” | When using text enclosure, which character should be used for escaping |
| CSV_DATE_FORMAT | ”yyyy-MM-dd’T’HH:mm:ssZ” | ISO 8601 Date format, example: 2018-01-09T14:56:13+0000 |
| CSV_MD5 | 0 (off) | Should we also distribute a sidecar .md5 checksum file for each distributed .csv file? If your file is named assets/assets_yyyy_MM_dd.csv the sidecar will be named assets/assets_yyyy_MM_dd.csv.md5Since version 2.2.0 of vimond-talend the sidecar file also includes the filename of the CSV file so that you can easily verify using md5sum. Example: md5sum -c assets_2018_09_10.csv.md5results in : assets_2018_09_10.csv: OK |
| CSV_SNAPSHOT | all | For jobs that has both ‘delta’ and ‘snapshot’ tasks which jobs should generate snapshot csv files? (comma separated list). Can also be set to “all”, or "" for including everything. |
What timezone are the date fields in?
Your Oracle database will by default be configured to run in UTC timezone. The database does not hold timezone information per field, so all date fields are stored in in the timezone of the database (UTC). The exported Oracle date value is therefore also UTC.
Data from other sources such as PostgreSQL can however hold timezone information per field.
It is therefore recommended to use a CSV_DATE_FORMAT format that includes timezone information.
When will I receive the reports?
It is recommended to run the data export daily some minutes over midnight. This will ensure that you will get everything of yesterday’s data included in the incremental reports. Each report can however be setup with custom CRON patterns for when they should be queued for execution.
How will I receive the data?
The CSV files can be posted to your FTP, S3 bucket, and/or Google Storage bucket of choice.
How can I have the data delivered to Amazon S3?
For this delivery option we need the following information from you
| Option | Default | Description |
|---|---|---|
| S3_BUCKET | Name of S3 bucket | |
| S3_ACCESS_KEY | Optional AWS Access key with access to write to given S3 bucket. Using IAM roles rather than access key / secret are recommended. | |
| S3_SECRET_KEY | Optional AWS secret key. Using IAM roles rather than access key / secret are recommended. | |
| S3_KEY_PREFIX | Optional key prefix if you would like the data further categorized. Useful when getting reports from stage and production environment delivered to the same bucket. | |
| S3_DELIVERY | all | which files should be distributed (comma separated list). Can also be set to “all”, or "" for including everything… |
| S3_ASSUME_ROLE | Optional assume role ARN If exporting to an S3 bucket under another account, this should hold the role ARN to assume. |
How can I have the data delivered to (S)FTP?
For this delivery option we need the following information from you
🚧 Please note that the FTP user is required to have
create directorypermission in addition touploadpermission. It is also recommended to grantoverwritepermission to ensure corrections can be sent in case of initial data transfer failures etc.
| Option | Default | Description |
|---|---|---|
| FTP_HOST | IP or hostname for your FTP server. | |
| FTP_PORT | 21 | Port to use. For SFTP we require using port 22. |
| FTP_USERNAME | Username. | |
| FTP_PASSWORD | Password. | |
| FTP_REMOTE_DIRECTORY | / | Optional directory on FTP server if you would like the data further categorized. Useful when getting reports from stage and production environment delivered to the same FTP server. |
| FTP_DELIVERY | all | which files should be distributed (comma separated list). Can also be set to “all”, or "" for including everything… |
How can I have the data delivered to my Google Storage Bucket?
For this delivery option we need the following information from you
| Option | Default | Description |
|---|---|---|
| GOOGLE_BUCKET | Name of Bucket | |
| GOOGLE_ACCESS_KEY | Access key with access to write to given bucket | |
| GOOGLE_SECRET_KEY | Secret key for above access key | |
| GOOGLE_DELIVERY | all | which files should be distributed (comma separated list). Can also be set to “all”, or "" for including everything… |
For more info on how you can setup developer access keys please see https://cloud.google.com/storage/docs/migrating#keys
User stories
Section titled “User stories”| Requirement | Story | Currently Supported By |
|---|---|---|
| Signing up for service | As a service admin I want to study the sign-up rate on my service So that I can see if we have momentum | users.registration_date |
| Logging in to the service | As a service admin I want to study the sign-in rate on my service So that I can see if we have momentum | users_login_logout.log_time |
| Channel subscription | As a service admin I want to study the channel subscription rate on my service So that I can see if we have momentum | user_access.registered |
| Active Channel subscriptions | As a service admin I want to study the channel subscription count on my service So that I can see how many active subscriptions we have | A subscription is active and renewable if - user_access.STATUS_ID = 0- user_access.ENDDATE has not passed- user_access.STARTDATE has passed- user_access.AUTORENEW_STATUS = 0When the subscription is stopped, user_access.AUTORENEW_STATUS gets set to 2. |
| Unsubscribe | As a service admin I want to study the channel churn rate on my service So that I can see if we have momentum | user_access.user_stop_date + user_access.last_activity_date |
| What is being watched? | As a service admin I want to know what is being watched on the service So that I know what the most popular content is | assets_views.asset_id + assets_views.registered |
| Where is it being watched from | As a service admin I want to know where my users are when they watch content So that I know where I have momentum | assets_views.ip + assets_views.geo_country + assets_views.geo_isp |
| Which platform is being used to watch | As a service admin I want to know which platform my content is consumed from So that I know where I have momentum and where I need to push harder | asset_views.platform |
| Registered vs non-registered access | As a service admin I want to know how many users are logged in while viewing content, and how many are viewing anonymously So that I know how many users are tempted to create an account and find it valuable enough to use it | assets_views.user_idValue -1 for non-authenticated users, while authenticated users will have a valid user_id. |
| Which Show/Channel is being watched | As a service admin I want to know how many views the different shows and channels have So that I know which content is most popular | assets_views.category_id mapped to categories.idTo identify show vs channel, map to categories.level_type |
| Available metadata for an Asset | As a service admin I want to read all available metadata for an asset So that I can enrich my data | assets_metadata.asset_id mapped to assets.id |
| Available metadata for an Category | As a service admin I want to read all available metadata for an category So that I can enrich my data | categories_metadata.category_id mapped to category.id |
| Video files created | As a service admin I want to know which video files are created for an Asset So that I can keep track of them | Several video files can be generated and associated to a given assets. They are linked together through the asset idvideo_files.asset_id |
| Video files distributed | As a service admin I want to know where my video files are distributed So that I can keep track of them | Each generated video files can be distributed and maintained in multiple locations. It is possible to identify where video files are delivered using the file idvideo_files_published.file_id + video_files_published.server_idOnce video files are removed from a remote location, there are also kept into the system for traceability video_files_deleted.file_id + video_files_deleted.server_id |
| Sub profiles | As a service admin I want to know how many profiles were created and gender metrics So that I can keep track of my audience | Each sub-profile as an entry in users_profiles. (users_profiles.subprofileid)These can be linked to a parent account via users_profiles.user_id |