Telegram Bot Customer Service Chat Export to Google Sheets Tutorial: Build a Simple Operations Dashboard

When running a Telegram Bot customer service system, chat data is scattered across the Bot backend, Telegram groups, and team members’ personal chat logs, making it hard to gain a global view. Exporting Telegram Bot customer service data to Google Sheets allows you to quickly build a lightweight operations dashboard to track user sources, conversation volume trends, and agent efficiency. Using TG-Staff as an example, this article details the complete process from traffic attribution to report building, suitable for B2B SaaS, Web3, and cross-border teams.

Why Export Telegram Bot Customer Service Data to Google Sheets?

Common Pain Points in Customer Service Operations Dashboards

• Data Fragmentation: User sources (ads, communities, official website) and chat records are scattered across different places, making it impossible to analyze the conversion funnel uniformly.
• Heavy Reporting Tools: CRM or BI tools have high learning costs and complex maintenance for small and medium teams, while most teams only need to look at a few core metrics.
• Inefficient Manual Statistics: Manually counting messages and response times daily is time-consuming and error-prone.

Advantages of Google Sheets as a Lightweight Reporting Tool

• Zero Cost: Just need a Google account, no additional fees.
• Strong Flexibility: Supports QUERY, FILTER, PIVOT functions, and with charts, you can quickly generate trend lines and pie charts.
• Collaboration and Sharing: Team members can edit simultaneously, ideal for remote teams.
• Automation Potential: Use Google Apps Script to periodically pull data for near real-time updates.

Prerequisites: What Tools and Permissions Do You Need?

Before starting, ensure the following conditions are met:

• TG-Staff Console Account: Register at app.tg-staff.com, free trial for 3 days.
• Connected Telegram Bot Project: At least one Bot configured and live via TG-Staff.
• Google Account: For accessing Google Sheets.
• Plan Confirmation: Standard plan and above support diversion links and chat export (free trial includes basic export).
• (Optional) Diversion Links: Standard plan required for traffic attribution.

Step 1: Capture User Source Data via Diversion Links

Diversion Links are official domain short links provided by TG-Staff (e.g., https://app.tg-staff.com/{code}). When a user clicks the link, they are redirected to your Telegram Bot, and the following information is captured:

• Visitor IP address
• Browser User-Agent
• URL parameters (e.g., utm_source, campaign, medium)

Steps:

1. Log in to TG-Staff Console, go to the “Diversion Links” module.
2. Click “Create Diversion Link”, select the target Bot project.
3. Under “URL Parameters”, add tracking parameters, for example:
   • utm_source=twitter
   • utm_campaign=product_launch
4. Embed the generated short link in ads, social media posts, or website buttons.
5. Users click and are redirected to the Bot, entering the customer service flow. Source data is automatically recorded in the chat details.

Diversion Links are the core tool for traffic attribution. When exported to Google Sheets, these fields appear as separate columns, helping you analyze conversion effectiveness across different channels.

Step 2: Configure Session Routing Rules to Ensure Data Integrity

Session routing rules determine how user messages are assigned to agents. If the rules are not set properly, some sessions may go unrecorded (e.g., messages are lost when all agents are offline), affecting the completeness of exported data.

Recommended Configuration:

• Project-Level Routing Rule: Select “Online First” mode. When agents are online, sessions are preferentially assigned to online agents; if all are offline, fall back to “Round Robin” to ensure every session is handled by an agent.
• Agent Scope: If your team has multiple members, it is recommended to select “All Agents” to avoid missing new sessions.
• Timeout Handling: Set a reasonable agent response timeout (e.g., 5 minutes). Sessions will be automatically reassigned after timeout.

With this configuration, TG-Staff will generate complete records for each session, including assignment history, transfer history, and message timestamps, ensuring no data loss during export.

Step 3: Export Session Records and User Profile Data

Export via Console

1. Go to the “Sessions” or “Data Export” module in the TG-Staff console.
2. Select the time period to export (e.g., last 30 days).
3. Click “Export CSV”, and the system will generate a file with the following fields:

Field	Description	Plan Limit
Session ID	Unique identifier	All plans
User Telegram ID	Unique user identifier	All plans
Session Start/End Time	Precise to seconds	All plans
Total Messages	Combined messages from both parties	All plans
Assigned Agent	Name of the agent handling the session	All plans
Routing Source URL Parameters	e.g., utm_source, campaign	Standard and above
User Tags	Tags manually added by agent	Pro
Engagement Score	User participation metric	Pro

Export via API (Suitable for Technical Teams)

TG-Staff provides API endpoints to programmatically fetch session data. Ideal for teams requiring regular automated synchronization.

// 示例：使用 fetch 调用 API 获取会话列表
fetch('https://api.tg-staff.com/v1/conversations?limit=100', {
  headers: {
    'Authorization': 'Bearer YOUR_API_KEY'
  }
})
.then(res => res.json())
.then(data => console.log(data));

The API returns JSON format data, which can be parsed directly with Google Apps Script and written to Sheets.

Step 4: Import Data into Google Sheets and Clean

1. Open Google Sheets and create a new spreadsheet.
2. Select “File” → “Import” → “Upload” and choose the CSV file you exported.
3. After import, clean the data:

Common Cleaning Operations:

• Deduplicate: Use =UNIQUE(A:A) to remove duplicate session IDs.
• Format Timestamps: If timestamps are in Unix format, use =TEXT(A2/86400 + DATE(1970,1,1), "yyyy-mm-dd hh:mm:ss") to convert.
• Extract Date: Use =DATE(YEAR(B2), MONTH(B2), DAY(B2)) to extract session dates for daily statistics.
• Categorize Sources: If routing link parameters contain utm_source, use =IF(ISNUMBER(SEARCH("twitter", C2)), "Twitter", "其他") to categorize.

Step 5: Build a Simple Operations Dashboard

Key Metrics and Corresponding Formulas

Metric	Example Formula	Chart Type
Daily Sessions	=COUNTIF(日期列, 今天日期)	Line Chart
Source Channel Distribution	=COUNTIF(来源列, "Twitter") etc.	Pie Chart
Average Response Time	=AVERAGE(响应时间列)	Single Value Card
Agent Workload	=COUNTIF(坐席列, "张三")	Bar Chart
Session Completion Rate	=COUNTIF(状态列, "已关闭")/COUNTA(状态列)	Percentage Gauge

Steps to Build:

1. Create a new sheet named “Dashboard”.
2. Use =QUERY(清洗后数据!A:Z, "select A, count(B) group by A label count(B) ''") to aggregate data.
3. Select the aggregated results and insert charts (recommend line charts for trends, pie charts for proportions).
4. Add data validation dropdowns to allow filtering by date range or channel.

From Data to Action: How to Optimize Customer Service with a Dashboard

• Source Analysis: If sessions from Twitter have a significantly higher conversion rate than other channels, consider increasing ad spend on that channel.
• Peak Hours: From the daily session line chart, if you find Tuesday afternoons are peak times, schedule more agents during that period.
• Agent Efficiency: If an agent’s average response time is much higher than the team average, training or workflow adjustments may be needed.
• Session Tags: User tags exported in the Pro version help identify frequent issue types, allowing you to optimize FAQs or bot auto-replies.

Frequently Asked Questions

Q: What fields are included in the data exported from TG-Staff?
A: The basic export includes session ID, user Telegram ID, session start/end time, total messages, assigned agent, and source URL parameters (e.g., utm_source); the Pro version adds user tags, activity score, and session tags.

Q: Can data be synced to Google Sheets in real time?
A: Currently, TG-Staff supports manual CSV export or API data retrieval. Combined with Google Apps Script, near real-time sync (every 15 minutes) is possible. For real-time dashboards, consider connecting via BI tools.

Q: Can I export data during the free trial?
A: The 3-day free trial includes export functionality, but the fields are limited by the plan (Standard and above support referral link attribution and full session logs).

Q: How can I use referral link attribution data in Google Sheets?
A: Use PIVOT or COUNTIF functions in Sheets to count sessions from different utm_source values and calculate conversion rates (e.g., from referral link to human agent engagement).

Q: Does data export include user privacy information?
A: Exported user data includes only Telegram public information (e.g., user ID, nickname) and message content sent in sessions. Be mindful of GDPR and other privacy regulations; consider anonymizing sensitive fields.

---

If you want to experience the full process of exporting Telegram Bot customer service data to Google Sheets, we recommend signing up for TG-Staff free trial to test referral links and session export within 3 days. For help, contact @tgstaff_robot or check the official documentation.