/**
* TikTok Data Analysis Script (Approach A: Bitable Dashboard)
* Fetches and analyzes TikTok video data from Lark Bitable using MCP proxy
*
* This script uses the lark-mcp MCP server proxy for enhanced reliability
*
* Run with: npm run tiktok:analyze
* Export data: npm run tiktok:analyze:export
*/
import axios from 'axios';
// Configuration
const CONFIG = {
APP_TOKEN: 'C8kmbTsqoa6rBesTKRpl8nV8gHd',
TABLE_ID: 'tblG4uuUvbwfvI9Z',
// Use lark-mcp proxy for all API calls
MCP_PROXY_URL: process.env.LARK_MCP_PROXY_URL || 'https://lark-mcp.hypelive.app',
API_KEY: process.env.LARK_API_KEY || '',
REGION: process.env.LARK_REGION || 'sg',
};
// Field name mappings based on the TikTok data structure
const FIELD_NAMES = {
VIDEO_ID: 'Unique identifier of the video',
DATE_PUBLISHED: 'Date and time the video was published',
VIEWS: 'Total video views',
LIKES: 'Total number of likes the video received',
COMMENTS: 'Total number of comments the video received',
SHARES: 'Total number of times the video was shared',
WATCH_RATE: 'Percentage of video watched completely',
DESCRIPTION: 'Video description',
DURATION: 'Duration of the video in seconds',
};
interface TikTokRecord {
record_id: string;
fields: {
[key: string]: any;
};
}
interface AnalysisResults {
totalRecords: number;
totalViews: number;
totalLikes: number;
totalComments: number;
totalShares: number;
avgWatchRate: number;
avgDuration: number;
topVideos: any[];
dateRange: {
earliest: string;
latest: string;
};
engagementMetrics: {
totalEngagement: number;
avgEngagementRate: number;
likesPerView: number;
commentsPerView: number;
sharesPerView: number;
};
durationBuckets: {
'0-15s': number;
'15-30s': number;
'30-60s': number;
'60-120s': number;
'120s+': number;
};
performanceInsights: string[];
}
/**
* Get API URL - uses MCP proxy for enhanced reliability
*/
function getApiUrl(): string {
return CONFIG.MCP_PROXY_URL;
}
/**
* Fetch all records from the TikTok table via MCP proxy
*/
async function fetchAllRecords(): Promise<TikTokRecord[]> {
const baseUrl = getApiUrl();
const url = `${baseUrl}/api/bitable/records`;
let allRecords: TikTokRecord[] = [];
let pageToken: string | undefined = undefined;
let hasMore = true;
console.log('Fetching TikTok video data via MCP proxy...');
console.log(`Proxy URL: ${baseUrl}`);
console.log(`App Token: ${CONFIG.APP_TOKEN}`);
console.log(`Table ID: ${CONFIG.TABLE_ID}\n`);
try {
while (hasMore) {
const params: any = {
app_token: CONFIG.APP_TOKEN,
table_id: CONFIG.TABLE_ID,
page_size: 500,
};
if (pageToken) {
params.page_token = pageToken;
}
console.log(`Requesting page... (${allRecords.length} records fetched so far)`);
const response = await axios.get(url, {
headers: {
'Content-Type': 'application/json',
},
params,
timeout: 30000,
});
// Handle MCP proxy response format
if (response.data.error) {
throw new Error(`MCP Proxy Error: ${response.data.error}`);
}
const data = response.data.data || response.data;
const items = data.items || [];
allRecords = allRecords.concat(items);
hasMore = data.has_more || false;
pageToken = data.page_token;
console.log(`✓ Fetched ${items.length} records (Total: ${allRecords.length})`);
}
console.log(`\n✓ Successfully fetched ${allRecords.length} total records\n`);
return allRecords;
} catch (error: any) {
console.error('\n✗ Error fetching records:');
if (error.response) {
console.error(` Status: ${error.response.status}`);
console.error(` Message: ${error.response.data?.error || error.response.data?.msg || error.message}`);
if (error.response.data) {
console.error(` Details: ${JSON.stringify(error.response.data, null, 2)}`);
}
} else if (error.request) {
console.error(' No response received from server');
console.error(` Message: ${error.message}`);
} else {
console.error(` Message: ${error.message}`);
}
throw error;
}
}
/**
* Safely get numeric value from field
*/
function getNumericValue(record: TikTokRecord, fieldName: string, defaultValue: number = 0): number {
const value = record.fields[fieldName];
if (value === null || value === undefined) return defaultValue;
const num = typeof value === 'number' ? value : parseFloat(value);
return isNaN(num) ? defaultValue : num;
}
/**
* Safely get string value from field
*/
function getStringValue(record: TikTokRecord, fieldName: string, defaultValue: string = ''): string {
const value = record.fields[fieldName];
return value?.toString() || defaultValue;
}
/**
* Analyze the fetched data
*/
function analyzeData(records: TikTokRecord[]): AnalysisResults {
console.log('Analyzing data...\n');
let totalViews = 0;
let totalLikes = 0;
let totalComments = 0;
let totalShares = 0;
let totalWatchRate = 0;
let totalDuration = 0;
let watchRateCount = 0;
let durationCount = 0;
const durationBuckets = {
'0-15s': 0,
'15-30s': 0,
'30-60s': 0,
'60-120s': 0,
'120s+': 0,
};
const dates: number[] = [];
const videosWithMetrics: any[] = [];
// Process each record
for (const record of records) {
const views = getNumericValue(record, FIELD_NAMES.VIEWS);
const likes = getNumericValue(record, FIELD_NAMES.LIKES);
const comments = getNumericValue(record, FIELD_NAMES.COMMENTS);
const shares = getNumericValue(record, FIELD_NAMES.SHARES);
const watchRate = getNumericValue(record, FIELD_NAMES.WATCH_RATE);
const duration = getNumericValue(record, FIELD_NAMES.DURATION);
const datePublished = getStringValue(record, FIELD_NAMES.DATE_PUBLISHED);
const description = getStringValue(record, FIELD_NAMES.DESCRIPTION);
const videoId = getStringValue(record, FIELD_NAMES.VIDEO_ID);
totalViews += views;
totalLikes += likes;
totalComments += comments;
totalShares += shares;
if (watchRate > 0) {
totalWatchRate += watchRate;
watchRateCount++;
}
if (duration > 0) {
totalDuration += duration;
durationCount++;
// Categorize duration
if (duration <= 15) durationBuckets['0-15s']++;
else if (duration <= 30) durationBuckets['15-30s']++;
else if (duration <= 60) durationBuckets['30-60s']++;
else if (duration <= 120) durationBuckets['60-120s']++;
else durationBuckets['120s+']++;
}
// Track dates
if (datePublished) {
const dateMs = new Date(datePublished).getTime();
if (!isNaN(dateMs)) {
dates.push(dateMs);
}
}
// Store video with metrics for top performers analysis
videosWithMetrics.push({
videoId,
description: description.substring(0, 100), // Truncate long descriptions
views,
likes,
comments,
shares,
watchRate,
duration,
datePublished,
engagementRate: views > 0 ? ((likes + comments + shares) / views) * 100 : 0,
});
}
// Calculate averages
const avgWatchRate = watchRateCount > 0 ? totalWatchRate / watchRateCount : 0;
const avgDuration = durationCount > 0 ? totalDuration / durationCount : 0;
// Find top performers
const topByViews = [...videosWithMetrics]
.sort((a, b) => b.views - a.views)
.slice(0, 10);
const topByEngagement = [...videosWithMetrics]
.sort((a, b) => b.engagementRate - a.engagementRate)
.slice(0, 10);
// Date range
dates.sort((a, b) => a - b);
const dateRange = {
earliest: dates.length > 0 ? new Date(dates[0]).toISOString().split('T')[0] : 'N/A',
latest: dates.length > 0 ? new Date(dates[dates.length - 1]).toISOString().split('T')[0] : 'N/A',
};
// Engagement metrics
const totalEngagement = totalLikes + totalComments + totalShares;
const avgEngagementRate = totalViews > 0 ? (totalEngagement / totalViews) * 100 : 0;
const likesPerView = totalViews > 0 ? totalLikes / totalViews : 0;
const commentsPerView = totalViews > 0 ? totalComments / totalViews : 0;
const sharesPerView = totalViews > 0 ? totalShares / totalViews : 0;
// Generate insights
const insights: string[] = [];
if (avgWatchRate >= 50) {
insights.push(`Strong retention: ${avgWatchRate.toFixed(1)}% average watch rate`);
} else if (avgWatchRate < 30) {
insights.push(`Low retention: ${avgWatchRate.toFixed(1)}% average watch rate - consider shorter or more engaging content`);
}
const mostCommonDuration = Object.entries(durationBuckets).reduce((a, b) => a[1] > b[1] ? a : b)[0] as keyof typeof durationBuckets;
insights.push(`Most videos are ${mostCommonDuration} long (${durationBuckets[mostCommonDuration]} videos)`);
if (likesPerView > 0.05) {
insights.push(`High like rate: ${(likesPerView * 100).toFixed(2)}% of views result in likes`);
}
if (sharesPerView > 0.01) {
insights.push(`Good shareability: ${(sharesPerView * 100).toFixed(2)}% share rate`);
}
const daysSinceFirstVideo = dates.length > 0
? Math.ceil((dates[dates.length - 1] - dates[0]) / (1000 * 60 * 60 * 24))
: 0;
const videosPerDay = daysSinceFirstVideo > 0 ? records.length / daysSinceFirstVideo : 0;
if (videosPerDay > 0) {
insights.push(`Posting frequency: ${videosPerDay.toFixed(2)} videos per day over ${daysSinceFirstVideo} days`);
}
return {
totalRecords: records.length,
totalViews,
totalLikes,
totalComments,
totalShares,
avgWatchRate,
avgDuration,
topVideos: topByViews,
dateRange,
engagementMetrics: {
totalEngagement,
avgEngagementRate,
likesPerView,
commentsPerView,
sharesPerView,
},
durationBuckets,
performanceInsights: insights,
};
}
/**
* Format number with thousands separator
*/
function formatNumber(num: number): string {
return num.toLocaleString('en-US');
}
/**
* Print analysis results
*/
function printResults(results: AnalysisResults): void {
console.log('='.repeat(80));
console.log('TIKTOK VIDEO ANALYTICS SUMMARY');
console.log('='.repeat(80));
console.log();
console.log('OVERVIEW:');
console.log(` Total Videos: ${formatNumber(results.totalRecords)}`);
console.log(` Date Range: ${results.dateRange.earliest} to ${results.dateRange.latest}`);
console.log();
console.log('PERFORMANCE METRICS:');
console.log(` Total Views: ${formatNumber(results.totalViews)}`);
console.log(` Total Likes: ${formatNumber(results.totalLikes)}`);
console.log(` Total Comments: ${formatNumber(results.totalComments)}`);
console.log(` Total Shares: ${formatNumber(results.totalShares)}`);
console.log(` Total Engagement: ${formatNumber(results.engagementMetrics.totalEngagement)}`);
console.log();
console.log('AVERAGE METRICS:');
console.log(` Avg Views per Video: ${formatNumber(Math.round(results.totalViews / results.totalRecords))}`);
console.log(` Avg Watch Rate: ${results.avgWatchRate.toFixed(2)}%`);
console.log(` Avg Engagement Rate: ${results.engagementMetrics.avgEngagementRate.toFixed(2)}%`);
console.log(` Avg Video Duration: ${results.avgDuration.toFixed(0)} seconds`);
console.log();
console.log('ENGAGEMENT RATES:');
console.log(` Like Rate: ${(results.engagementMetrics.likesPerView * 100).toFixed(3)}%`);
console.log(` Comment Rate: ${(results.engagementMetrics.commentsPerView * 100).toFixed(3)}%`);
console.log(` Share Rate: ${(results.engagementMetrics.sharesPerView * 100).toFixed(3)}%`);
console.log();
console.log('VIDEO DURATION DISTRIBUTION:');
Object.entries(results.durationBuckets).forEach(([range, count]) => {
const percentage = (count / results.totalRecords * 100).toFixed(1);
const bar = '█'.repeat(Math.round(count / results.totalRecords * 50));
console.log(` ${range.padEnd(10)}: ${count.toString().padStart(3)} (${percentage.padStart(5)}%) ${bar}`);
});
console.log();
console.log('TOP 10 VIDEOS BY VIEWS:');
results.topVideos.forEach((video, index) => {
console.log(` ${(index + 1).toString().padStart(2)}. ${formatNumber(video.views).padStart(10)} views - ${video.description.substring(0, 60)}...`);
});
console.log();
console.log('INSIGHTS:');
results.performanceInsights.forEach((insight, index) => {
console.log(` ${index + 1}. ${insight}`);
});
console.log();
console.log('='.repeat(80));
console.log('RECOMMENDATIONS FOR DASHBOARD:');
console.log('='.repeat(80));
console.log('1. Focus on top performing videos - analyze what makes them successful');
console.log('2. Track watch rate trends over time to identify content quality changes');
console.log('3. Monitor engagement rates (likes, comments, shares) as key success metrics');
console.log('4. Consider duration analysis - optimize video length based on performance');
console.log('5. Set up alerts for videos that exceed average performance by 2x');
console.log();
}
/**
* Export data for visualization
*/
function exportForVisualization(results: AnalysisResults): void {
const exportData = {
timestamp: new Date().toISOString(),
summary: {
totalVideos: results.totalRecords,
totalViews: results.totalViews,
totalLikes: results.totalLikes,
totalComments: results.totalComments,
totalShares: results.totalShares,
avgWatchRate: results.avgWatchRate,
avgDuration: results.avgDuration,
},
engagement: results.engagementMetrics,
dateRange: results.dateRange,
durationDistribution: results.durationBuckets,
topPerformers: results.topVideos.slice(0, 10),
insights: results.performanceInsights,
};
console.log('EXPORT DATA (JSON):');
console.log(JSON.stringify(exportData, null, 2));
}
/**
* Main execution
*/
async function main() {
try {
console.log('╔═══════════════════════════════════════════════════════════════╗');
console.log('║ TikTok Data Analysis Tool (Approach A) ║');
console.log('║ Using lark-mcp MCP Proxy ║');
console.log('╚═══════════════════════════════════════════════════════════════╝\n');
console.log('Configuration:');
console.log(` App Token: ${CONFIG.APP_TOKEN}`);
console.log(` Table ID: ${CONFIG.TABLE_ID}`);
console.log(` MCP Proxy: ${CONFIG.MCP_PROXY_URL}`);
console.log(` Region: ${CONFIG.REGION}\n`);
// Fetch data
const records = await fetchAllRecords();
if (records.length === 0) {
console.log('No records found in the table.');
return;
}
// Analyze data
const results = analyzeData(records);
// Print results
printResults(results);
// Export data
const exportFlag = process.argv.includes('--export') || process.argv.includes('-e');
if (exportFlag) {
exportForVisualization(results);
} else {
console.log('Tip: Run with --export or -e flag to output JSON data for visualization');
}
} catch (error: any) {
console.error('\nError:', error.message);
process.exit(1);
}
}
// Run if executed directly
if (require.main === module) {
main();
}
export { main, fetchAllRecords, analyzeData };
