This is the technical companion piece to why Telegram belongs in your warehouse. Same architecture, fewer slides, more SQL.
We'll build a small but real pipeline:
- Extract group members and DM contacts to CSV.
- Load into Postgres (or BigQuery — same shape).
- Three dbt models: cleaned, enriched, and CRM-ready.
- The five queries you'll actually run against it.
Total time on a fresh laptop: ~30 minutes. Everything below is copy-pasteable.
1. Extract
Use a local extraction tool that outputs structured CSV. We use the Telegram Data Scraper Chrome extension because it processes data client-side, doesn't need bot tokens, and outputs the schema below directly.
Two CSVs per pull:
groups.csv
group_id,group_name,group_type,member_count,extracted_at
-100123,Founders SF,supergroup,432,2026-04-27T15:00:00Z
-100456,Crypto Allocators,supergroup,1180,2026-04-27T15:02:00Z
members.csv
group_id,user_id,username,display_name,is_bot,last_seen_at,extracted_at
-100123,9001,alice_btc,Alice T,false,2026-04-26T09:12:00Z,2026-04-27T15:00:00Z
-100123,9002,bob_eng,Bob R,false,2026-04-25T14:00:00Z,2026-04-27T15:00:00Z
If you also extract DM contacts, a third CSV:
contacts.csv
user_id,username,display_name,first_message_at,last_message_at,messages_sent,messages_received
9001,alice_btc,Alice T,2025-08-12T11:00:00Z,2026-04-26T18:00:00Z,42,28
That's it. Three flat files, no nesting, ready to load.
2. Load to Postgres
Spin up Postgres locally (or use any managed instance — BigQuery and Snowflake work the same with trivial syntax changes):
createdb network
psql network <<'SQL'
CREATE TABLE raw_telegram_groups (
group_id BIGINT,
group_name TEXT,
group_type TEXT,
member_count INT,
extracted_at TIMESTAMPTZ
);
CREATE TABLE raw_telegram_members (
group_id BIGINT,
user_id BIGINT,
username TEXT,
display_name TEXT,
is_bot BOOLEAN,
last_seen_at TIMESTAMPTZ,
extracted_at TIMESTAMPTZ
);
CREATE TABLE raw_telegram_contacts (
user_id BIGINT,
username TEXT,
display_name TEXT,
first_message_at TIMESTAMPTZ,
last_message_at TIMESTAMPTZ,
messages_sent INT,
messages_received INT
);
SQL
psql network -c "\copy raw_telegram_groups FROM 'groups.csv' CSV HEADER"
psql network -c "\copy raw_telegram_members FROM 'members.csv' CSV HEADER"
psql network -c "\copy raw_telegram_contacts FROM 'contacts.csv' CSV HEADER"
If you already use dlt or Fivetran, point them at the CSVs instead. The schema is small enough that any loader works.
3. The dbt models
Three models. Stage → mart → activation.
stg_telegram_members.sql — clean, dedupe, normalize handles.
{{ config(materialized='table') }}
with deduped as (
select
user_id,
lower(username) as username,
nullif(trim(display_name), '') as display_name,
bool_or(is_bot) as is_bot,
max(last_seen_at) as last_seen_at,
array_agg(distinct group_id) as group_ids,
count(distinct group_id) as group_count
from {{ source('raw', 'raw_telegram_members') }}
where username is not null
group by 1, 2, 3
)
select * from deduped
mart_warm_contacts.sql — apply the warm-score weighting.
{{ config(materialized='table') }}
with members as (
select * from {{ ref('stg_telegram_members') }}
),
ranked_groups as (
select
group_id,
group_name,
case
when group_name ilike '%founder%' then 1.0
when group_name ilike '%crypto%' or group_name ilike '%defi%' then 0.9
when group_name ilike '%dev%' or group_name ilike '%eng%' then 0.8
else 0.4
end as topical_weight
from {{ source('raw', 'raw_telegram_groups') }}
),
scored as (
select
m.user_id,
m.username,
m.display_name,
m.group_count,
avg(rg.topical_weight) as avg_topical_weight,
max(m.last_seen_at) as last_seen_at,
case
when max(m.last_seen_at) > now() - interval '7 days' then 1.0
when max(m.last_seen_at) > now() - interval '30 days' then 0.6
when max(m.last_seen_at) > now() - interval '90 days' then 0.3
else 0.1
end as recency_weight
from members m
cross join lateral unnest(m.group_ids) as g(group_id)
join ranked_groups rg on rg.group_id = g.group_id
group by 1, 2, 3, 4
)
select
*,
round(
(0.4 * least(group_count, 6) / 6.0)
+ (0.4 * avg_topical_weight)
+ (0.2 * recency_weight)
, 3) as warm_score
from scored
order by warm_score desc
mart_crm_enrichment.sql — the table you reverse-ETL into Salesforce/HubSpot.
{{ config(materialized='table') }}
select
user_id,
username as telegram_handle,
display_name as telegram_display_name,
group_count as mutual_groups_count,
round(avg_topical_weight, 2) as topical_overlap_score,
last_seen_at as last_telegram_active_at,
warm_score,
case
when warm_score >= 0.7 then 'A'
when warm_score >= 0.4 then 'B'
else 'C'
end as warm_tier
from {{ ref('mart_warm_contacts') }}
4. The five queries you'll actually run
Once the models are in place, these are the queries that earn the pipeline its keep.
Tier-A prospects you haven't touched in 90 days.
select telegram_handle, mutual_groups_count, last_telegram_active_at
from mart_crm_enrichment e
left join crm_contacts c on c.telegram_handle = e.telegram_handle
where e.warm_tier = 'A'
and (c.last_touched_at is null or c.last_touched_at < now() - interval '90 days')
order by warm_score desc
limit 50;
Group-density by ICP topic — where to sponsor or show up.
select
rg.group_name,
count(distinct e.user_id) as fit_buyers_in_group,
count(distinct e.user_id) filter (where e.warm_tier in ('A','B')) as warm_in_group
from mart_crm_enrichment e
join {{ ref('stg_telegram_members') }} m on m.user_id = e.user_id
cross join lateral unnest(m.group_ids) as g(group_id)
join raw_telegram_groups rg on rg.group_id = g.group_id
group by 1
order by fit_buyers_in_group desc
limit 20;
Recently active warm contacts (the daily call list).
select telegram_handle, warm_score, last_telegram_active_at
from mart_crm_enrichment
where last_telegram_active_at > now() - interval '7 days'
and warm_tier = 'A'
order by warm_score desc;
Account-to-Telegram match coverage.
select
count(*) filter (where telegram_handle is not null)::float / count(*) as match_rate
from crm_accounts;
Reply-rate by warm tier (close the loop).
select
e.warm_tier,
count(distinct s.id) filter (where s.replied) ::float / count(distinct s.id) as reply_rate
from outbound_sends s
join mart_crm_enrichment e on e.telegram_handle = s.telegram_handle
where s.sent_at > now() - interval '30 days'
group by 1;
That last one is the most important. The whole point of the pipeline is to keep the model honest by measuring whether the score actually predicts replies. If reply rate doesn't separate tiers cleanly, retune α/β/γ and rerun.
5. Reverse-ETL it
Whichever reverse-ETL tool you use — Hightouch, Census, custom — point it at mart_crm_enrichment and sync into Salesforce/HubSpot:
telegram_handle(text)mutual_groups_count(number)topical_overlap_score(number, 0–1)last_telegram_active_at(datetime)warm_score(number, 0–1)warm_tier(picklist A/B/C)
Now every account in your CRM has a Telegram presence the same way it has Bombora intent or Clearbit firmographics, and your reps can filter, route, and prioritize against it.
The pipeline took 30 minutes. The data was extracting itself in a Chrome tab while you read this.