Osiguranik.com - BI Dashboard

Smart Insights

Loading executive metrics...

Total Premium - Putno

€0

(organic + paid)

Total Provision

€0

33% of Total Premium

Total Cost - Putno

€0

From campaigns with Putno revenue

ROAS Provision - Putno

0.00x

Provision ÷ Cost Putno

Total Cost - All

€0

All campaigns spend (all insurance types)

Revenue & Spend Trend

Revenue Attribution Split

Paid Traffic

€0

Organic Traffic

€0

Other/Unknown

€0

Total Revenue

€0

Putno osiguranje

Total Leads

Kasko, DZO, Životno

Purchases

Putno osiguranje

AOV

€0

Average Order Value

🛫 Putno Osiguranje

Revenue-based

🚗 Kasko Osiguranje

Lead-based

🏥 DZO

Lead-based

💼 Životno Osiguranje

Lead-based

Putno Osiguranje - Campaign Breakdown

Top 10 campaigns by ROAS Provision

Insurance Campaign Breakdown

Detaljni pregled performansi kampanja po insurance tipu

Total Spend

€0

Total Conversions

Total Revenue

€0

Campaigns

Campaign	Overall				🛫 PUTNO					🚗 KASKO				🏥 DZO					❤️ ŽIVOTNO
Campaign	Spend	Conv	ROAS	CPC	Conv	Revenue	Spend	ROAS	P/O/Oth	Conv	Spend	CPL	P/O/Oth	Conv	Spend	CPL	P/O/Oth	F/P	Conv	Spend	CPL	P/O/Oth

📊 TOTALS

📖 Legend:

P/O/Oth: Paid / Organic / Other

F/P: Fizička / Pravna lica

Conv: Conversions

CPL: Cost Per Lead

📊 Napomena o CPC metrici

Business Intelligence vs. Google Ads platformska metrika

✓ BI Dashboard Finansijska analitika

CPC u ovom izveštaju računa se na osnovu GA4 purchase događaja (stvarnih kupovina) i ukupnog Google Ads troška alociranog po kampanji. Ovaj pristup daje realan trošak po stvarnoj prodaji i koristi se za business analizu, jer predstavlja naše stvarne troškove i potvrđene transakcije u BI sistemu. Zbog toga je ova metodologija primerenija za praćenje ROI i ROAS pokazatelja.

⚡ Google Ads Algoritamska optimizacija

Vrednosti u Google Ads platformi mogu odstupati jer Google trošak deli sa brojem konverzija prema sopstvenom attribution modelu (data-driven, cross-device, view-through i fractional conversions).

Google Ads može prikazivati veći broj konverzija jer koristi modeliranu atribuciju i šire vremenske prozore, uključujući delimične (decimalne) konverzije, dok BI sistem broji isključivo stvarne purchase događaje kao cele transakcije.

🎯 Ključna razlika:

Google Ads metodologija je namenjena optimizaciji kampanja i bidding strategija unutar platforme, dok BI pristup služi za finansijsko izveštavanje i precizno merenje profitabilnosti.

Zbog razlike u metodologiji merenja, CPC u BI izveštaju i CPC u Google Ads platformi mogu se razlikovati.

Revenue Decomposition

Revenue Formula:

Revenue = Sessions × CVR × AOV

Change vs Previous Period

Top Campaigns by Revenue

Device Performance

Top Countries

Campaign Performance

Sorted by revenue (highest first)

Campaign	Spend	Revenue	ROAS	CPA	CVR	Clicks	Status

Campaign Status Guide

SCALE

ROAS ≥ 3x, CPA ≤ €10 - Increase budget

OPTIMIZE

ROAS ≥ 2x, CPA ≤ €15 - Improve targeting

MONITOR

ROAS ≥ 1.5x - Watch closely

PAUSE

ROAS < 1.5x - Consider pausing

Conversion Funnel Overview

Landing Page Performance

Sorted by revenue (highest first)

Filter:

Sort by:

Campaign Deep Dive Analysis

Paid vs Organic breakdown with profitability analysis

Performance Grading System

ROAS ≥ 4x, Profit > €1000 - Excellent performance

ROAS ≥ 3x, Profit > €500 - Good performance

ROAS ≥ 2x, Profit > €0 - Average performance

ROAS ≥ 1.5x - Below average

ROAS < 1.5x - Unprofitable

ROI Analysis

Business ROI with all costs included (employees, agency, taxes, processor fees)

Real Revenue Adjustment

Dodati razliku iz izveštaja prihodima u EUR

Analytics Revenue

€0

From GA4 & Google Ads

Real Revenue

€0

No adjustment +€0 €0

Total Provision

€0

33% of Real Revenue

Total Costs

€0

All business expenses

Net Profit

€0

Provision - Costs

Business ROI

🎉

😊

😐

😢

Net Profit ÷ Total Costs × 100

Profit Margin

Net Profit ÷ Provision × 100

Cost Breakdown

Detailed view of all business expenses

Cost Type	Amount (EUR)	% of Total
Google Ads Spend	€0	0%
PDV (20% of Ads)	€0	0%
Payment Processor (1%)	€0	0%
Employee Costs	€0	0%
Marketing Agency	€0	0%
Unlocated Cost (67% PUTNO) Avg. share of PUTNO in unattributed spend	€0	0%
TOTAL COSTS	€0	100%

Breakeven Analysis

Needed Revenue (ROI = 0%)

€0

Revenue Gap

€0

Days to Breakeven

0 days

Based on avg daily revenue

90-Day Forecast

Projected Revenue

€0

Projected Costs

€0

Projected Profit

€0

Projected ROI

Based on current avg daily revenue and costs

Campaign Performance Deep Dive

ROI analysis per campaign with prorated business costs

Sort by:

ROI > 50% ROI 20-50% ROI < 20%

Campaign	Revenue	Google Ads	Provision (33%)	Total Costs	Net Profit	ROI %	Conversions

No campaign data available

Costs include prorated Google Ads spend, PDV (20%), Payment Processor (1%), Employees, and Agency costs

ROI Trend Over Time

Daily ROI % trend showing profitability changes over the selected period

Green line shows daily ROI percentage. Positive ROI indicates profit, negative indicates loss.

Data Sources & Calculation Methods

Complete technical documentation of data pipeline, storage, and calculations

Version: 3.0 | Last Updated: 2026-02-20 | Features: USD/EUR Dual Currency + Exploration Methodology

System Architecture

┌────────────────────┐
│  DATA SOURCES      │
├────────────────────┤
│ • Google Ads API   │──┐
│ • GA4 Exploration  │  │
│ • GA4 Data API     │  │
└────────────────────┘  │
                        ▼
                ┌──────────────────┐
                │  SYNC SCRIPTS    │
                ├──────────────────┤
                │ sync-batch.ts    │◄──── GitHub Actions (daily 3 AM UTC)
                │ sync-yesterday.ts│◄──── Manual button
                │ sync-last7days.ts│◄──── Manual button
                └──────────────────┘
                        │
                        ▼
                ┌──────────────────────────┐
                │  EXPLORATION TRANSFORMER │
                ├──────────────────────────┤
                │ • Match campaigns        │
                │ • Allocate spend (USD/EUR)│
                │ • Calculate metrics      │
                └──────────────────────────┘
                        │
                        ▼
                ┌──────────────────────────┐
                │  CLOUDFLARE D1 DATABASE  │
                ├──────────────────────────┤
                │ google_ads_campaigns     │
                │ insurance_performance    │
                │ ga4_sessions             │
                │ ga4_events               │
                │ ga4_purchase_events      │
                └──────────────────────────┘
                        │
                        ▼
                ┌──────────────────────────┐
                │  ANALYTICS API (HONO)    │
                ├──────────────────────────┤
                │ /api/analytics/executive │
                │ /api/analytics/insurance │
                │ /api/insurance-campaign- │
                │   breakdown              │
                └──────────────────────────┘
                        │
                        ▼
                ┌──────────────────────────┐
                │  BI DASHBOARD (THIS UI)  │
                └──────────────────────────┘

Data Sources

• Google Ads API (spend, clicks)
• GA4 Exploration (conversions)
• GA4 Data API (sessions, events)

Processing

• Campaign matching
• Spend allocation (proportional)
• Currency conversion (USD→EUR via historical rates)

Storage

• Cloudflare D1 (SQLite)
• 5 tables (campaigns, insurance, GA4)
• Daily upserts (INSERT OR REPLACE)

Dual Currency System

💵 USD (Primary)

Source:	Google Ads API (cost_micros)
Formula:	cost_usd = cost_micros ÷ 1,000,000
Storage:	google_ads_campaigns.cost_usd insurance_performance.spend_usd

💶 EUR (Converted)

Source:	USD × daily_rate (Frankfurter.app API)
Formula:	cost_eur = cost_usd × exchange_rate
Storage:	google_ads_campaigns.cost insurance_performance.spend_eur
Rate Source:	Frankfurter.app (ECB official data)
Example:	Jan 1, 2025: 1 USD = €0.96256

Example (01.01.2025):

Source	USD	EUR (rate: 0.96256)
putno_osiguranje_search	$123.73	€119.09
osiguranik_brend_search	$33.51	€32.25
Total Allocated	$233.85	€225.15

Note: Exchange rates vary daily. Backend fetches historical rates for accurate conversion.

Exploration Methodology

Problem: Google Ads gives spend PER CAMPAIGN, GA4 gives conversions PER INSURANCE TYPE.

Solution: Allocate campaign spend to insurance types proportionally by conversions.

Fetch Google Ads spend (per campaign)

SELECT campaign, cost_usd, cost_eur FROM google_ads_campaigns WHERE date = ?

Fetch GA4 Exploration data (per insurance type)

Dimensions: [sessionCampaignName, customEvent:insurance_type] | Metrics: [conversions]

Match spend to campaigns

For each GA4 campaign, find matching Google Ads campaign and assign spend_usd/spend_eur

Allocate spend proportionally

If campaign has multiple insurance types, split spend by conversion %

PUTNO.spend_usd += campaign.spend_usd × (putno_conversions / total_conversions)

Upsert to insurance_performance

INSERT OR REPLACE (date, insurance_type, spend_usd, spend_eur, conversions, revenue_eur)

Data Sync Automation

Daily Sync (GitHub Actions)

Schedule:	0 3 * * * (3 AM UTC)
Script:	scripts/sync-last7days.ts
Period:	Last 7 days (rolling window)
Workflow:	.github/workflows/daily-sync.yml

Manual Sync (UI Buttons)

Sync Yesterday:	scripts/sync-yesterday.ts
Sync Last 7:	scripts/sync-last7days.ts
Auth:	Bearer token (localStorage)

Sync Process (per date):

1. Fetch Google Ads (cost_usd, cost_eur) → upsert google_ads_campaigns
2. Fetch GA4 Exploration (conversions per insurance_type) → Transformer
3. Run Transformer (allocate spend) → upsert insurance_performance
4. Fetch GA4 Tables (sessions, events) → upsert ga4_* tables

Database Schema (Cloudflare D1)

google_ads_campaigns

Column	Type
date	TEXT
campaign	TEXT
cost_usd	REAL (NEW)
cost	REAL (EUR)
clicks	INTEGER
impressions	INTEGER
conversions	REAL

PK: (date, campaign)

insurance_performance

Column	Type
date	TEXT
insurance_type	TEXT
conversions	INTEGER
spend_usd	REAL (NEW)
spend_eur	REAL
revenue_eur	REAL
provision_eur	REAL

PK: (date, insurance_type)

ga4_sessions

Column	Type
date	TEXT
channel_group	TEXT
device	TEXT
country	TEXT
sessions	INTEGER
purchasers	INTEGER
revenue_eur	REAL

For device/geo analysis

ga4_events, ga4_purchase_events

Raw event data from GA4 Data API

• ga4_events: all events (page_view, etc.)
• ga4_purchase_events: purchase transactions

API Endpoints (Hono Backend)

/api/analytics/executive - Executive metrics

/api/analytics/insurance - Insurance performance

/api/insurance-campaign-breakdown - Campaign breakdown

/api/analytics/growth-drivers - Growth analysis

/api/analytics/paid-efficiency - Paid campaign efficiency

/api/analytics/funnel - Conversion funnel

Version: 3.0

Last Updated: 2026-02-20

Coverage: 2025-01-01 to present

Built with Hono Framework

Smart Insights

Revenue & Spend Trend

Revenue Attribution Split

🛫 Putno Osiguranje

Campaign Breakdown - Putno Osiguranje

🚗 Kasko Osiguranje

🏥 DZO

💼 Životno Osiguranje

Putno Osiguranje - Campaign Breakdown

Insurance Campaign Breakdown

📖 Legend:

📊 Napomena o CPC metrici

Revenue Decomposition

Change vs Previous Period

Top Campaigns by Revenue

Device Performance

Top Countries

Campaign Performance

Campaign Status Guide

Conversion Funnel Overview

Landing Page Performance

Campaign Deep Dive Analysis

Performance Grading System

ROI Analysis

Cost Breakdown

Breakeven Analysis

90-Day Forecast

Campaign Performance Deep Dive

ROI Trend Over Time

Data Sources & Calculation Methods

System Architecture

Data Sources

Processing

Storage

Dual Currency System

💵 USD (Primary)

💶 EUR (Converted)

Example (01.01.2025):

Exploration Methodology

Data Sync Automation

Daily Sync (GitHub Actions)

Manual Sync (UI Buttons)

Sync Process (per date):

Database Schema (Cloudflare D1)

google_ads_campaigns

insurance_performance

ga4_sessions

ga4_events, ga4_purchase_events

API Endpoints (Hono Backend)

Manage Business Costs

Current Business Costs