Setting Up End-to-End Analytics for Your Website
End-to-end analytics links advertising spend to actual sales. You see: the Yandex.Direct ad campaign brought 50 leads, of which 15 made purchases totaling 450,000 RUB with 30,000 RUB spent. ROAS = 15. Without end-to-end analytics, you only see clicks or lead counts, not revenue.
Components of End-to-End Analytics
Ad spend tracker — retrieves cost data from Yandex.Direct, Google Ads, VK, Facebook via their APIs.
Call tracking — replaces phone numbers with unique ones for each source to track inbound calls.
CRM — stores deals linked to lead source.
BI/Dashboard — combines everything into one view: spend → visits → leads → sales → revenue.
Ready-Made Solutions
Roistat — Russian end-to-end analytics platform. Embedded via script, integrates with Direct, Google Ads, amoCRM, Bitrix24. Suitable for most small/medium businesses.
Calltouch — call tracking + basic end-to-end analytics.
CoMagic — similar solution with call tracking.
Custom implementation — needed if ready-made solutions don't cover your specific requirements or are too expensive for large volumes.
Custom End-to-End Analytics: Architecture
Ad Systems (API)
→ ETL Service (Python/Go)
→ Data Warehouse (ClickHouse / PostgreSQL / BigQuery)
→ BI Tool (Redash / Metabase / Superset)
→ Dashboard
Website (UTM + cookies)
→ Backend
→ Data Warehouse
CRM
→ Data Warehouse
Retrieving Data from Ad Systems
# Yandex.Direct: get costs for period
import requests
headers = {'Authorization': f'Bearer {YANDEX_TOKEN}', 'Client-Login': CLIENT_LOGIN}
report_body = {
'params': {
'SelectionCriteria': {
'DateFrom': '2024-03-01', 'DateTo': '2024-03-31'
},
'FieldNames': ['Date', 'CampaignName', 'Impressions', 'Clicks', 'Cost'],
'ReportName': 'Cost Report',
'ReportType': 'CAMPAIGN_PERFORMANCE_REPORT',
'DateRangeType': 'CUSTOM_DATE',
'Format': 'TSV',
'IncludeVAT': 'YES'
}
}
response = requests.post(
'https://api.direct.yandex.com/json/v5/reports',
headers=headers,
json=report_body
)
Linking Leads to Ad Sources
-- Table for storing sessions with UTM parameters
CREATE TABLE sessions (
session_id UUID PRIMARY KEY,
user_id BIGINT REFERENCES users(id),
utm_source VARCHAR(100),
utm_medium VARCHAR(100),
utm_campaign VARCHAR(200),
referrer TEXT,
created_at TIMESTAMP
);
-- Leads linked to session
CREATE TABLE leads (
id BIGINT PRIMARY KEY,
session_id UUID REFERENCES sessions(session_id),
phone VARCHAR(20),
type VARCHAR(50), -- 'form' | 'call' | 'chat'
created_at TIMESTAMP
);
-- Orders linked to lead
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
lead_id BIGINT REFERENCES leads(id),
total INTEGER,
status VARCHAR(20),
created_at TIMESTAMP
);
Main End-to-End Analytics Report
SELECT
s.utm_source,
s.utm_campaign,
COUNT(DISTINCT s.session_id) as visits,
COUNT(DISTINCT l.id) as leads,
COUNT(DISTINCT o.id) as orders,
SUM(o.total) / 100.0 as revenue,
SUM(rc.cost) / 100.0 as ad_spend,
ROUND(SUM(o.total) / NULLIF(SUM(rc.cost), 0), 2) as roas
FROM sessions s
LEFT JOIN leads l ON l.session_id = s.session_id
LEFT JOIN orders o ON o.lead_id = l.id AND o.status = 'completed'
LEFT JOIN ad_costs rc ON rc.utm_source = s.utm_source
AND rc.utm_campaign = s.utm_campaign
AND DATE_TRUNC('day', rc.date) = DATE_TRUNC('day', s.created_at)
WHERE s.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY s.utm_source, s.utm_campaign
ORDER BY revenue DESC NULLS LAST;
Call Tracking in Custom Implementation
To track phone calls, integrate with Calltouch or CoMagic. They dynamically replace the phone number on your site based on traffic source. When a call is made, they send UTM parameters via webhook.
An alternative is custom call tracking via SIP provider: a pool of virtual numbers where each visitor gets a unique number from the pool.
Dashboard in Metabase
Metabase connects directly to PostgreSQL and builds dashboards without code. For non-technical marketers, it's the optimal choice.
Development time: 4–6 weeks for custom implementation with ETL, data warehouse, and dashboard. Using Roistat — 1–2 weeks for integration.