Schema · Entity relationship diagram
TikTok Shop Affiliate tables and relationships
Creator-attributed orders and commissions, sample requests, collaborations, creator outreach and profiles.
ConnectSign in and approve
MarketsUS, UK, EU
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
affiliate_ordershas one row per order × SKU with the credited creator and the estimated and actual commission. Join it to TikTok Shopordersonorder_idandsku_id(the dashed box).- Commission rates ending in
_bpare basis points (1500 = 15%). - Collaboration, creator, outreach and quota tables are snapshots refreshed on every sync.
Primary keyForeign keyReferencesJoins on (not a key)
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
11 tables · click a table for its columnsaffiliate_orders33 columns · Affiliate orders & samplesshow in diagram
Order x SKU grain: creator attribution + commission economics per line. Joins to tiktok_shop.orders on order_id (and sku_id). commission_rate_bp is in basis points as TikTok returns it (1500 = 15%).
Primary key: pk
| Column | Type | Key | Description |
|---|---|---|---|
| pkPrimary key. | varchar(32) | PK | Primary key. |
| order_idJoins to orders.order_id,sku_id (the order line a creator was credited for). | varchar(64) | FK | Joins to orders.order_id,sku_id (the order line a creator was credited for). |
| sku_idJoins to orders.order_id,sku_id (the order line a creator was credited for). | varchar(64) | FK | Joins to orders.order_id,sku_id (the order line a creator was credited for). |
| product_id | varchar(64) | ||
| order_create_timeRedshift SORTKEY. | timestamp | Redshift SORTKEY. | |
| order_delivery_time | timestamp | ||
| creator_usernameJoins to affiliate_creator_profiles.username. | varchar(256) | FK | Joins to affiliate_creator_profiles.username. |
| content_type | varchar(32) | ||
| content_id | varchar(64) | ||
| is_carousel | boolean | ||
| commission_model | varchar(64) | ||
| commission_rate_bpCommission rate in basis points as TikTok returns it (1500 = 15%). | integer | Commission rate in basis points as TikTok returns it (1500 = 15%). | |
| commission_tier_setting | varchar(64) | ||
| open_collaboration_idReferences affiliate_open_collaborations.collaboration_id. | varchar(64) | FK | References affiliate_open_collaborations.collaboration_id. |
| target_collaboration_idReferences affiliate_target_collaborations.target_collaboration_id. | varchar(64) | FK | References affiliate_target_collaborations.target_collaboration_id. |
| campaign_id | varchar(64) | ||
| quantity | integer | ||
| currency | varchar(8) | ||
| price | decimal(18,4) | ||
| estimated_commission_base | decimal(18,4) | ||
| estimated_paid_commission | decimal(18,4) | ||
| estimated_paid_partner_commission | decimal(18,4) | ||
| estimated_paid_shop_ads_commission | decimal(18,4) | ||
| estimated_cofunded_creator_bonus | decimal(18,4) | ||
| actual_commission_base | decimal(18,4) | ||
| actual_paid_commission | decimal(18,4) | ||
| actual_paid_partner_commission | decimal(18,4) | ||
| actual_paid_shop_ads_commission | decimal(18,4) | ||
| actual_cofunded_creator_bonus | decimal(18,4) | ||
| shop_ads_commission_rate_bp | integer | ||
| settlement_status | varchar(32) | ||
| fully_return | varchar(8) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sample_applications20 columns · Affiliate orders & samplesshow in diagram
One row per sample application (snapshot; status evolves: PENDING -> AWAITING_SHIPMENT -> SHIPPED -> CONTENT_PENDING ..., or a *_CANCELLED state). sample_order_id links to orders when a sample shipped.
Primary key: application_id
| Column | Type | Key | Description |
|---|---|---|---|
| application_idPrimary key. | varchar(64) | PK | Primary key. |
| statusRedshift SORTKEY. | varchar(48) | Redshift SORTKEY. | |
| creator_username | varchar(256) | ||
| creator_nickname | varchar(256) | ||
| creator_open_idReferences affiliate_creator_profiles.creator_open_id. | varchar(128) | FK | References affiliate_creator_profiles.creator_open_id. |
| creator_follower_count | bigint | ||
| creator_content_count | integer | ||
| creator_ec_video_view | bigint | ||
| creator_fulfillment_percentage | decimal(10,4) | ||
| creator_gmv | decimal(18,4) | ||
| product_idReferences affiliate_sample_rules.product_id. | varchar(64) | FK | References affiliate_sample_rules.product_id. |
| product_title | varchar(1000) | ||
| sku_id | varchar(64) | ||
| sku_name | varchar(500) | ||
| commission_rate | decimal(10,6) | ||
| available_quantity | integer | ||
| sample_order_idJoins to orders.order_id (set once the sample has shipped). | varchar(64) | FK | Joins to orders.order_id (set once the sample has shipped). |
| approve_expiration_time | timestamp | ||
| is_approvable | boolean | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_open_collaborations15 columns · Open collaborationsshow in diagram
Primary key: collaboration_id
| Column | Type | Key | Description |
|---|---|---|---|
| collaboration_idPrimary key. | varchar(64) | PK | Primary key. |
| product_idRedshift SORTKEY. | varchar(64) | Redshift SORTKEY. | |
| product_title | varchar(1000) | ||
| product_status | varchar(32) | ||
| inventory | integer | ||
| currency | varchar(8) | ||
| price_min | decimal(18,4) | ||
| price_max | decimal(18,4) | ||
| commission_rate_bp | integer | ||
| commission_start_time | timestamp | ||
| commission_end_time | timestamp | ||
| content_creator_count | integer | ||
| showcase_creator_count | integer | ||
| status | varchar(32) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_sample_rules10 columns · Open collaborationsshow in diagram
Primary key: product_id
| Column | Type | Key | Description |
|---|---|---|---|
| product_idPrimary key. Joins to affiliate_open_collaborations.product_id. Redshift SORTKEY. | varchar(64) | PK FK | Primary key. Joins to affiliate_open_collaborations.product_id. Redshift SORTKEY. |
| status | varchar(32) | ||
| sample_quota | integer | ||
| available_quantity | integer | ||
| is_sample_time_unlimited | boolean | ||
| min_follower_count | integer | ||
| min_gmv | decimal(18,4) | ||
| min_avg_ec_video_views | integer | ||
| predicted_fulfillment_rank | varchar(32) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_collab_creators11 columns · Open collaborationsshow in diagram
Creators promoting each open-collaboration product (video/live counts).
Primary key: pk
| Column | Type | Key | Description |
|---|---|---|---|
| pkPrimary key. | varchar(32) | PK | Primary key. |
| product_idJoins to affiliate_open_collaborations.product_id. Redshift SORTKEY. | varchar(64) | FK | Joins to affiliate_open_collaborations.product_id. Redshift SORTKEY. |
| creator_open_idReferences affiliate_creator_profiles.creator_open_id. | varchar(128) | FK | References affiliate_creator_profiles.creator_open_id. |
| creator_username | varchar(256) | ||
| creator_nickname | varchar(256) | ||
| follower_count | bigint | ||
| video_count | integer | ||
| live_count | integer | ||
| promotion_status | varchar(32) | ||
| promotion_end_time | timestamp | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_target_collaborations13 columns · Target collaborationsshow in diagram
Primary key: target_collaboration_id
| Column | Type | Key | Description |
|---|---|---|---|
| target_collaboration_idPrimary key. | varchar(64) | PK | Primary key. |
| name | varchar(500) | ||
| type | varchar(32) | ||
| start_timeRedshift SORTKEY. | timestamp | Redshift SORTKEY. | |
| end_time | timestamp | ||
| update_time | timestamp | ||
| creator_invited_count | integer | ||
| content_creator_count | integer | ||
| showcase_creator_count | integer | ||
| product_count | integer | ||
| free_sample_rule | varchar(2000) | ||
| message | varchar(2000) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_target_collab_creators11 columns · Target collaborationsshow in diagram
Primary key: pk
| Column | Type | Key | Description |
|---|---|---|---|
| pkPrimary key. | varchar(32) | PK | Primary key. |
| target_collaboration_idReferences affiliate_target_collaborations.target_collaboration_id. Redshift SORTKEY. | varchar(64) | FK | References affiliate_target_collaborations.target_collaboration_id. Redshift SORTKEY. |
| creator_open_idReferences affiliate_creator_profiles.creator_open_id. | varchar(128) | FK | References affiliate_creator_profiles.creator_open_id. |
| creator_username | varchar(256) | ||
| creator_nickname | varchar(256) | ||
| collaboration_status | varchar(32) | ||
| product_effective_status | varchar(32) | ||
| content_product_count | integer | ||
| showcase_product_count | integer | ||
| selection_region | varchar(8) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_target_collab_products12 columns · Target collaborationsshow in diagram
Primary key: pk
| Column | Type | Key | Description |
|---|---|---|---|
| pkPrimary key. | varchar(32) | PK | Primary key. |
| target_collaboration_idReferences affiliate_target_collaborations.target_collaboration_id. Redshift SORTKEY. | varchar(64) | FK | References affiliate_target_collaborations.target_collaboration_id. Redshift SORTKEY. |
| product_id | varchar(64) | ||
| collaboration_status | varchar(32) | ||
| commission_rate_bp | integer | ||
| shop_ads_commission_rate_bp | integer | ||
| currency | varchar(8) | ||
| commission_min | decimal(18,4) | ||
| commission_max | decimal(18,4) | ||
| commission_effective_time | timestamp | ||
| commission_effective_status | varchar(32) | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_creator_outreach6 columns · Creators & outreachshow in diagram
Primary key: outreach_id
| Column | Type | Key | Description |
|---|---|---|---|
| outreach_idPrimary key. Redshift SORTKEY. | varchar(64) | PK | Primary key. Redshift SORTKEY. |
| creator_open_idReferences affiliate_creator_profiles.creator_open_id. | varchar(128) | FK | References affiliate_creator_profiles.creator_open_id. |
| has_sent_im_message | boolean | ||
| has_sent_invitation | boolean | ||
| is_paired | boolean | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_outreach_quota12 columns · Creators & outreachshow in diagram
Primary key: snapshot_date
| Column | Type | Key | Description |
|---|---|---|---|
| snapshot_datePrimary key. Redshift SORTKEY. | date | PK | Primary key. Redshift SORTKEY. |
| shop_id | varchar(64) | ||
| gmv_tier | varchar(8) | ||
| quota_type | varchar(32) | ||
| quota_sum | integer | ||
| quota_use | integer | ||
| new_connect_count | integer | ||
| pair_connect_count | integer | ||
| unlimited | boolean | ||
| period_start | timestamp | ||
| period_end | timestamp | ||
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
affiliate_creator_profiles24 columns · Creators & outreachshow in diagram
Marketplace performance profile per creator (trailing-30d metrics from TikTok).
Primary key: creator_open_id
| Column | Type | Key | Description |
|---|---|---|---|
| creator_open_idPrimary key. Redshift SORTKEY. | varchar(128) | PK | Primary key. Redshift SORTKEY. |
| username | varchar(256) | ||
| nickname | varchar(256) | ||
| follower_count | bigint | ||
| bio_description | varchar(1000) | ||
| selection_region | varchar(8) | ||
| avg_commission_rate_bp | integer | ||
| brand_collaboration_count | integer | ||
| gmv_30d | decimal(18,4) | ||
| live_gmv_30d | decimal(18,4) | ||
| video_gmv_30d | decimal(18,4) | ||
| avg_gmv_per_buyer | decimal(18,4) | ||
| avg_ec_live_view_count | bigint | ||
| avg_ec_live_like_count | bigint | ||
| avg_ec_live_comment_count | bigint | ||
| avg_ec_live_share_count | bigint | ||
| avg_ec_video_play_count | bigint | ||
| avg_ec_video_like_count | bigint | ||
| avg_ec_video_comment_count | bigint | ||
| avg_ec_video_share_count | bigint | ||
| category_gmv_distribution | varchar(4000) | ||
| top_follower_demographics | varchar(4000) | ||
| raw_jsonThe full source record as JSON, including fields added later. | varchar(8000) | The full source record as JSON, including fields added later. | |
| _synced_atWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |