SEO-Dashboard aus heterogenen Datenquellen – FAQ für Entscheider, die Klartext wollen

Warum überhaupt ein SEO-Dashboard?

Du kennst das Spiel.

Die Agentur erzählt dir, die Rankings steigen. Der Social-Media-Manager sagt, die Klicks nehmen zu. Der Content-Schreiber meint, die Artikel performen gut.

Das Problem: Keiner zeigt dir harte Zahlen. Alle reden. Niemand beweist.

Du willst ein Dashboard. Eines, das dir zeigt:

• Wo stehen wir bei Google?
• Welche Keywords ranken?
• Wie entwickelt sich der Traffic?
• Was bringen die einzelnen Maßnahmen?

Und zwar nicht in fünf verschiedenen Tools, sondern an einem Ort.

Genau darum geht’s hier.

Welche Datenquellen brauche ich für ein SEO-Dashboard?

Ein vernünftiges SEO-Dashboard zapft mehrere Quellen an.

Die wichtigsten Datenquellen:

Google Search Console

Zeigt dir:

• Suchanfragen, über die User zu dir kommen
• Klicks, Impressionen, CTR, Position
• Indexierungsstatus
• Mobile Usability

API-Zugang: Ja, über Google API

Datenbasis: Google Cloud SQL (intern), du bekommst nur Lesezugriff

Google Analytics (GA4)

Zeigt dir:

• Traffic-Quellen
• Nutzerverhalten
• Conversions
• Sitzungsdauer, Absprungrate

API-Zugang: Ja, über Google Analytics Data API

Datenbasis: Google BigQuery (Export möglich)

SEO-Tools (Sistrix, Ahrefs, SEMrush)

Zeigen dir:

• Sichtbarkeitsindex
• Backlink-Profil
• Keyword-Rankings
• Wettbewerber-Analysen

API-Zugang: Ja, gegen Aufpreis

Datenbasis: Proprietäre Crawler-Datenbanken

Dein CMS (WordPress, Typo3, etc.)

Zeigt dir:

• Veröffentlichte Artikel
• Interne Verlinkung
• Meta-Daten (Title, Description)

Datenbasis: Meist MySQL oder PostgreSQL

Dein CRM/ERP

Zeigt dir:

• Anfragen aus organischem Traffic
• Conversions
• Umsatz pro Traffic-Quelle

Datenbasis: SQL Server, Access, oder was auch immer du hast

Wie hole ich Daten aus der Google Search Console?

Google bietet eine API. Die ist kostenfrei, aber limitiert.

Rate Limits (Stand 2024):

• 1.200 Abfragen pro Minute
• Max. 25.000 Zeilen pro Abfrage

Python-Script für den Datenabruf:

from google.oauth2 import service_account
from googleapiclient.discovery import build
import pyodbc
import json
from datetime import datetime, timedelta

# Authentifizierung
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SERVICE_ACCOUNT_FILE = 'path/to/service-account.json'

credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('searchconsole', 'v1', credentials=credentials)

# Daten abrufen
site_url = 'https://www.deine-website.de/'
start_date = (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d')
end_date = datetime.now().strftime('%Y-%m-%d')

request = {
    'startDate': start_date,
    'endDate': end_date,
    'dimensions': ['query', 'page', 'date'],
    'rowLimit': 25000
}

response = service.searchanalytics().query(
    siteUrl=site_url, body=request).execute()

# In SQL Server schreiben
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=DEIN_SERVER;'
    'DATABASE=SEO_DWH;'
    'Trusted_Connection=yes;'
)
cursor = conn.cursor()

for row in response.get('rows', []):
    query = row['keys'][0]
    page = row['keys'][1]
    date = row['keys'][2]
    clicks = row.get('clicks', 0)
    impressions = row.get('impressions', 0)
    ctr = row.get('ctr', 0)
    position = row.get('position', 0)
    
    cursor.execute("""
        INSERT INTO dbo.GSC_RawData 
        (Query, Page, Date, Clicks, Impressions, CTR, Position, ImportedAt)
        VALUES (?, ?, ?, ?, ?, ?, ?, GETUTCDATE())
    """, query, page, date, clicks, impressions, ctr, position)

conn.commit()
cursor.close()
conn.close()

SQL Server: Tabelle für Google Search Console Daten

-- Rohdaten-Tabelle
CREATE TABLE dbo.GSC_RawData (
    RecordID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Query NVARCHAR(500) NOT NULL,
    Page NVARCHAR(1000) NOT NULL,
    Date DATE NOT NULL,
    Clicks INT NOT NULL,
    Impressions INT NOT NULL,
    CTR FLOAT NOT NULL,
    Position FLOAT NOT NULL,
    ImportedAt DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Date_Query (Date DESC, Query),
    INDEX IX_Page_Date (Page, Date DESC)
);

-- Aggregierte Ansicht: Top Keywords nach Klicks
CREATE VIEW dbo.vw_TopKeywordsByClicks
AS
SELECT 
    Query,
    SUM(Clicks) AS TotalClicks,
    SUM(Impressions) AS TotalImpressions,
    AVG(CTR) AS AvgCTR,
    AVG(Position) AS AvgPosition,
    COUNT(DISTINCT Date) AS DaysTracked
FROM dbo.GSC_RawData
WHERE Date >= DATEADD(MONTH, -3, GETDATE())
GROUP BY Query
HAVING SUM(Clicks) > 0;

-- Stored Procedure: Top 20 Keywords mit Entwicklung
CREATE PROCEDURE dbo.GetTopKeywordTrends
    @DaysBack INT = 30
AS
BEGIN
    WITH CurrentPeriod AS (
        SELECT 
            Query,
            SUM(Clicks) AS CurrentClicks,
            AVG(Position) AS CurrentPosition
        FROM dbo.GSC_RawData
        WHERE Date >= DATEADD(DAY, -@DaysBack, GETDATE())
        GROUP BY Query
    ),
    PreviousPeriod AS (
        SELECT 
            Query,
            SUM(Clicks) AS PreviousClicks,
            AVG(Position) AS PreviousPosition
        FROM dbo.GSC_RawData
        WHERE Date >= DATEADD(DAY, -(@DaysBack * 2), GETDATE())
            AND Date < DATEADD(DAY, -@DaysBack, GETDATE())
        GROUP BY Query
    )
    SELECT TOP 20
        c.Query,
        c.CurrentClicks,
        c.CurrentPosition,
        ISNULL(p.PreviousClicks, 0) AS PreviousClicks,
        ISNULL(p.PreviousPosition, 0) AS PreviousPosition,
        (c.CurrentClicks - ISNULL(p.PreviousClicks, 0)) AS ClickDelta,
        CASE 
            WHEN ISNULL(p.PreviousClicks, 0) = 0 THEN NULL
            ELSE ((c.CurrentClicks - p.PreviousClicks) * 100.0 / p.PreviousClicks)
        END AS ClickChangePercent,
        (c.CurrentPosition - ISNULL(p.PreviousPosition, 0)) AS PositionDelta
    FROM CurrentPeriod c
    LEFT JOIN PreviousPeriod p ON c.Query = p.Query
    ORDER BY c.CurrentClicks DESC;
END;

Wie integriere ich Google Analytics 4?

GA4 hat die Datenstruktur komplett umgekrempelt.

Die alte Universal Analytics API funktioniert nicht mehr.

GA4 Data API (Python):

from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import (
    RunReportRequest,
    DateRange,
    Dimension,
    Metric,
)
import pyodbc
from datetime import datetime, timedelta

# Authentifizierung
property_id = 'YOUR_GA4_PROPERTY_ID'
client = BetaAnalyticsDataClient()

# Report abrufen
request = RunReportRequest(
    property=f"properties/{property_id}",
    date_ranges=[DateRange(
        start_date=(datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d'),
        end_date=datetime.now().strftime('%Y-%m-%d')
    )],
    dimensions=[
        Dimension(name="date"),
        Dimension(name="sessionSource"),
        Dimension(name="sessionMedium"),
        Dimension(name="landingPage"),
    ],
    metrics=[
        Metric(name="sessions"),
        Metric(name="totalUsers"),
        Metric(name="conversions"),
        Metric(name="averageSessionDuration"),
    ],
)

response = client.run_report(request)

# In SQL Server schreiben
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=DEIN_SERVER;DATABASE=SEO_DWH;Trusted_Connection=yes;'
)
cursor = conn.cursor()

for row in response.rows:
    date = row.dimension_values[0].value
    source = row.dimension_values[1].value
    medium = row.dimension_values[2].value
    landing_page = row.dimension_values[3].value
    sessions = int(row.metric_values[0].value)
    users = int(row.metric_values[1].value)
    conversions = float(row.metric_values[2].value)
    avg_duration = float(row.metric_values[3].value)
    
    cursor.execute("""
        INSERT INTO dbo.GA4_RawData 
        (Date, Source, Medium, LandingPage, Sessions, Users, 
         Conversions, AvgSessionDuration, ImportedAt)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, GETUTCDATE())
    """, date, source, medium, landing_page, sessions, users, 
         conversions, avg_duration)

conn.commit()
cursor.close()
conn.close()

SQL Server: GA4-Tabellen

-- GA4 Rohdaten
CREATE TABLE dbo.GA4_RawData (
    RecordID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Date DATE NOT NULL,
    Source NVARCHAR(255),
    Medium NVARCHAR(255),
    LandingPage NVARCHAR(1000),
    Sessions INT,
    Users INT,
    Conversions FLOAT,
    AvgSessionDuration FLOAT,
    ImportedAt DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Date_Source (Date DESC, Source, Medium),
    INDEX IX_LandingPage (LandingPage, Date DESC)
);

-- Organischer Traffic nach Landing Page
CREATE VIEW dbo.vw_OrganicTrafficByPage
AS
SELECT 
    LandingPage,
    SUM(Sessions) AS TotalSessions,
    SUM(Users) AS TotalUsers,
    SUM(Conversions) AS TotalConversions,
    AVG(AvgSessionDuration) AS AvgDuration,
    COUNT(DISTINCT Date) AS DaysTracked
FROM dbo.GA4_RawData
WHERE Medium = 'organic'
    AND Date >= DATEADD(MONTH, -3, GETDATE())
GROUP BY LandingPage;

-- Combined View: GSC + GA4
CREATE VIEW dbo.vw_SEO_Performance_Combined
AS
SELECT 
    gsc.Page,
    gsc.Date,
    SUM(gsc.Clicks) AS GSC_Clicks,
    SUM(gsc.Impressions) AS GSC_Impressions,
    AVG(gsc.Position) AS GSC_AvgPosition,
    SUM(ga.Sessions) AS GA_Sessions,
    SUM(ga.Conversions) AS GA_Conversions
FROM dbo.GSC_RawData gsc
LEFT JOIN dbo.GA4_RawData ga 
    ON gsc.Page = ga.LandingPage 
    AND gsc.Date = ga.Date
    AND ga.Medium = 'organic'
WHERE gsc.Date >= DATEADD(MONTH, -3, GETDATE())
GROUP BY gsc.Page, gsc.Date;

Wie zapfe ich SEO-Tools wie Sistrix oder Ahrefs an?

Die meisten SEO-Tools bieten APIs. Aber nicht alle sind gleich gut.

Sistrix API

Datenbasis: Eigene Crawler-Datenbank, deutscher Markt gut abgedeckt

Funktionsumfang:

• Sichtbarkeitsindex
• Keyword-Rankings
• Backlink-Daten
• Konkurrenz-Analysen

API-Zugang: Ja, gegen Aufpreis (ca. 100-200 EUR/Monat extra)

Aktuell: Ja, wird aktiv weiterentwickelt

Nachfolger: Keine absehbar

Sistrix API-Abfrage (VBA für Access):

Public Function GetSistrixVisibility( _
    strDomain As String, _
    strAPIKey As String) As Double
    
On Error GoTo ErrorHandler
    
    Dim objHTTP As Object
    Dim strURL As String
    Dim strResponse As String
    Dim dblVisibility As Double
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    
    strURL = "https://api.sistrix.com/visibility.index" & _
             "?domain=" & strDomain & _
             "&api_key=" & strAPIKey & _
             "&format=json"
    
    objHTTP.Open "GET", strURL, False
    objHTTP.Send
    
    strResponse = objHTTP.responseText
    
    ' JSON parsen (vereinfacht)
    ' In Produktion: JSON-Parser wie VBA-JSON verwenden
    dblVisibility = ExtractVisibilityFromJSON(strResponse)
    
    Set objHTTP = Nothing
    
    GetSistrixVisibility = dblVisibility
    Exit Function
    
ErrorHandler:
    GetSistrixVisibility = 0
End Function

' Historische Sichtbarkeit in SQL Server speichern
Public Sub ImportSistrixHistory( _
    strDomain As String, _
    strAPIKey As String)
    
    Dim cn As Object
    Dim cmd As Object
    Dim objHTTP As Object
    Dim strURL As String
    Dim strResponse As String
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    
    strURL = "https://api.sistrix.com/visibility.history" & _
             "?domain=" & strDomain & _
             "&api_key=" & strAPIKey & _
             "&format=json"
    
    objHTTP.Open "GET", strURL, False
    objHTTP.Send
    
    strResponse = objHTTP.responseText
    
    ' In SQL Server schreiben
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=SQLOLEDB;Data Source=DEIN_SERVER;" & _
            "Initial Catalog=SEO_DWH;Integrated Security=SSPI;"
    
    ' JSON parsen und einfügen
    ' (hier vereinfacht, in Produktion robuster)
    
    cn.Close
    Set cn = Nothing
    Set objHTTP = Nothing
End Sub

SQL Server: Sistrix-Daten

-- Sichtbarkeitsindex-Historie
CREATE TABLE dbo.Sistrix_VisibilityHistory (
    RecordID INT IDENTITY(1,1) PRIMARY KEY,
    Domain NVARCHAR(255) NOT NULL,
    Date DATE NOT NULL,
    VisibilityIndex FLOAT NOT NULL,
    ImportedAt DATETIME2 DEFAULT GETUTCDATE(),
    UNIQUE (Domain, Date),
    INDEX IX_Domain_Date (Domain, Date DESC)
);

-- Keyword-Rankings
CREATE TABLE dbo.Sistrix_KeywordRankings (
    RecordID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Domain NVARCHAR(255) NOT NULL,
    Keyword NVARCHAR(500) NOT NULL,
    Date DATE NOT NULL,
    Position INT,
    SearchVolume INT,
    Competition FLOAT,
    ImportedAt DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Domain_Date (Domain, Date DESC),
    INDEX IX_Keyword (Keyword, Date DESC)
);

-- Sichtbarkeits-Trend
CREATE PROCEDURE dbo.GetVisibilityTrend
    @Domain NVARCHAR(255),
    @MonthsBack INT = 12
AS
BEGIN
    SELECT 
        Date,
        VisibilityIndex,
        LAG(VisibilityIndex, 1) OVER (ORDER BY Date) AS PreviousIndex,
        VisibilityIndex - LAG(VisibilityIndex, 1) OVER (ORDER BY Date) AS Delta,
        CASE 
            WHEN LAG(VisibilityIndex, 1) OVER (ORDER BY Date) = 0 THEN NULL
            ELSE ((VisibilityIndex - LAG(VisibilityIndex, 1) OVER (ORDER BY Date)) 
                  * 100.0 / LAG(VisibilityIndex, 1) OVER (ORDER BY Date))
        END AS PercentChange
    FROM dbo.Sistrix_VisibilityHistory
    WHERE Domain = @Domain
        AND Date >= DATEADD(MONTH, -@MonthsBack, GETDATE())
    ORDER BY Date ASC;
END;

Ahrefs API

Datenbasis: Eigene Crawler-Datenbank, weltweit größter Backlink-Index

Funktionsumfang:

• Backlink-Profil
• Keyword-Rankings (begrenzt)
• Domain Rating (DR)
• URL Rating (UR)

API-Zugang: Ja, ab Advanced Plan (ca. 200 USD/Monat)

Aktuell: Ja, sehr aktiv

Nachfolger: Keine absehbar

Ahrefs via T-SQL (mit SQLCLR oder Python Integration):

-- Backlink-Daten von Ahrefs
CREATE TABLE dbo.Ahrefs_Backlinks (
    BacklinkID BIGINT IDENTITY(1,1) PRIMARY KEY,
    TargetDomain NVARCHAR(255) NOT NULL,
    SourceURL NVARCHAR(1000) NOT NULL,
    TargetURL NVARCHAR(1000) NOT NULL,
    AnchorText NVARCHAR(500),
    DomainRating INT,
    URLRating INT,
    DoFollow BIT,
    FirstSeen DATE,
    LastSeen DATE,
    ImportedAt DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_TargetDomain (TargetDomain, LastSeen DESC),
    INDEX IX_DomainRating (DomainRating DESC)
);

-- Backlink-Qualität bewerten
CREATE PROCEDURE dbo.AnalyzeBacklinkQuality
    @Domain NVARCHAR(255)
AS
BEGIN
    SELECT 
        COUNT(*) AS TotalBacklinks,
        SUM(CASE WHEN DoFollow = 1 THEN 1 ELSE 0 END) AS DoFollowCount,
        SUM(CASE WHEN DoFollow = 0 THEN 1 ELSE 0 END) AS NoFollowCount,
        AVG(CAST(DomainRating AS FLOAT)) AS AvgDomainRating,
        SUM(CASE WHEN DomainRating >= 50 THEN 1 ELSE 0 END) AS HighQualityLinks,
        SUM(CASE WHEN DomainRating < 20 THEN 1 ELSE 0 END) AS LowQualityLinks,
        COUNT(DISTINCT LEFT(SourceURL, CHARINDEX('/', SourceURL, 9) - 1)) AS UniqueDomains
    FROM dbo.Ahrefs_Backlinks
    WHERE TargetDomain = @Domain;
END;

Wie integriere ich WordPress-Daten?

WordPress läuft meist auf MySQL.

Du willst wissen: Welche Artikel sind publiziert? Welche Meta-Daten haben sie?

Direkter MySQL-Zugriff via Linked Server (SQL Server):

-- Linked Server zu WordPress MySQL einrichten
EXEC sp_addlinkedserver 
    @server = 'WP_MySQL',
    @srvproduct = 'MySQL',
    @provider = 'MSDASQL',
    @datasrc = 'WORDPRESS_SERVER',
    @catalog = 'wordpress_db';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'WP_MySQL',
    @useself = 'FALSE',
    @locallogin = NULL,
    @rmtuser = 'wp_user',
    @rmtpassword = 'wp_password';

-- WordPress-Posts abrufen
SELECT 
    ID,
    post_title,
    post_name, -- Slug
    post_date,
    post_status,
    post_type
FROM OPENQUERY(WP_MySQL, 
    'SELECT ID, post_title, post_name, post_date, post_status, post_type 
     FROM wp_posts 
     WHERE post_status = ''publish'' 
       AND post_type = ''post''')
WHERE post_date >= DATEADD(MONTH, -6, GETDATE());

-- WordPress Meta-Daten (Yoast SEO)
SELECT 
    p.ID,
    p.post_title,
    MAX(CASE WHEN pm.meta_key = '_yoast_wpseo_title' THEN pm.meta_value END) AS SEO_Title,
    MAX(CASE WHEN pm.meta_key = '_yoast_wpseo_metadesc' THEN pm.meta_value END) AS SEO_Description,
    MAX(CASE WHEN pm.meta_key = '_yoast_wpseo_focuskw' THEN pm.meta_value END) AS Focus_Keyword
FROM OPENQUERY(WP_MySQL, 'SELECT * FROM wp_posts WHERE post_status = ''publish''') p
LEFT JOIN OPENQUERY(WP_MySQL, 'SELECT * FROM wp_postmeta') pm ON p.ID = pm.post_id
WHERE p.post_type = 'post'
GROUP BY p.ID, p.post_title;

Alternativer Weg: WordPress REST API (Access VBA):

Public Function GetWordPressPosts(strSiteURL As String) As ADODB.Recordset
On Error GoTo ErrorHandler
    
    Dim objHTTP As Object
    Dim strURL As String
    Dim strResponse As String
    Dim rs As ADODB.Recordset
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    Set rs = New ADODB.Recordset
    
    ' Recordset-Struktur definieren
    rs.Fields.Append "PostID", adInteger
    rs.Fields.Append "Title", adVarWChar, 500
    rs.Fields.Append "Slug", adVarWChar, 200
    rs.Fields.Append "PublishedDate", adDate
    rs.Fields.Append "URL", adVarWChar, 1000
    rs.Open
    
    strURL = strSiteURL & "/wp-json/wp/v2/posts?per_page=100"
    
    objHTTP.Open "GET", strURL, False
    objHTTP.Send
    
    strResponse = objHTTP.responseText
    
    ' JSON parsen (mit VBA-JSON Library)
    Dim objJSON As Object
    Set objJSON = ParseJson(strResponse)
    
    Dim i As Long
    For i = 1 To objJSON.Count
        rs.AddNew
        rs("PostID") = objJSON(i)("id")
        rs("Title") = objJSON(i)("title")("rendered")
        rs("Slug") = objJSON(i)("slug")
        rs("PublishedDate") = CDate(Replace(objJSON(i)("date"), "T", " "))
        rs("URL") = objJSON(i)("link")
        rs.Update
    Next i
    
    rs.MoveFirst
    Set GetWordPressPosts = rs
    
    Set objHTTP = Nothing
    Exit Function
    
ErrorHandler:
    MsgBox "Fehler beim Abrufen: " & Err.Description
    Set GetWordPressPosts = Nothing
End Function

Wie baue ich ein Data Warehouse für SEO-Daten?

Verschiedene Quellen, unterschiedliche Strukturen.

Du brauchst eine zentrale Stelle.

Dimensional Model (Kimball-Style):

-- Dimensions-Tabellen

-- Dim_Date
CREATE TABLE dbo.Dim_Date (
    DateKey INT PRIMARY KEY,
    Date DATE NOT NULL,
    Year INT NOT NULL,
    Quarter INT NOT NULL,
    Month INT NOT NULL,
    Week INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(20),
    MonthName NVARCHAR(20),
    IsWeekend BIT
);

-- Dim_Page
CREATE TABLE dbo.Dim_Page (
    PageKey INT IDENTITY(1,1) PRIMARY KEY,
    PageURL NVARCHAR(1000) NOT NULL UNIQUE,
    PageTitle NVARCHAR(500),
    ContentType NVARCHAR(50), -- 'Article', 'Product', 'Category'
    PublishedDate DATE,
    LastModified DATE,
    WordCount INT,
    IsCurrent BIT DEFAULT 1,
    EffectiveFrom DATE,
    EffectiveTo DATE
);

-- Dim_Keyword
CREATE TABLE dbo.Dim_Keyword (
    KeywordKey INT IDENTITY(1,1) PRIMARY KEY,
    Keyword NVARCHAR(500) NOT NULL UNIQUE,
    SearchVolume INT,
    Competition FLOAT,
    CPC FLOAT,
    Intent NVARCHAR(50), -- 'Informational', 'Commercial', 'Transactional'
    Category NVARCHAR(100)
);

-- Dim_Source
CREATE TABLE dbo.Dim_Source (
    SourceKey INT IDENTITY(1,1) PRIMARY KEY,
    SourceName NVARCHAR(100) NOT NULL UNIQUE,
    SourceType NVARCHAR(50), -- 'Organic', 'Direct', 'Referral', 'Social', 'Paid'
    Medium NVARCHAR(50)
);

-- Fact Table: SEO Performance
CREATE TABLE dbo.Fact_SEO_Performance (
    FactID BIGINT IDENTITY(1,1) PRIMARY KEY,
    DateKey INT NOT NULL,
    PageKey INT NOT NULL,
    KeywordKey INT,
    SourceKey INT,
    -- Measures from GSC
    GSC_Clicks INT,
    GSC_Impressions INT,
    GSC_CTR FLOAT,
    GSC_Position FLOAT,
    -- Measures from GA4
    GA_Sessions INT,
    GA_Users INT,
    GA_Conversions FLOAT,
    GA_AvgSessionDuration FLOAT,
    GA_BounceRate FLOAT,
    -- Calculated
    ConversionRate AS (CASE WHEN GA_Sessions > 0 THEN GA_Conversions * 100.0 / GA_Sessions ELSE 0 END),
    FOREIGN KEY (DateKey) REFERENCES Dim_Date(DateKey),
    FOREIGN KEY (PageKey) REFERENCES Dim_Page(PageKey),
    FOREIGN KEY (KeywordKey) REFERENCES Dim_Keyword(KeywordKey),
    FOREIGN KEY (SourceKey) REFERENCES Dim_Source(SourceKey),
    INDEX IX_Date_Page (DateKey, PageKey),
    INDEX IX_Keyword (KeywordKey, DateKey)
);

-- ETL: GSC-Daten ins DWH laden
CREATE PROCEDURE dbo.ETL_LoadGSCData
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Neue Keywords in Dimension einfügen
    INSERT INTO dbo.Dim_Keyword (Keyword)
    SELECT DISTINCT Query
    FROM dbo.GSC_RawData
    WHERE Query NOT IN (SELECT Keyword FROM dbo.Dim_Keyword);
    
    -- Neue Pages in Dimension einfügen
    INSERT INTO dbo.Dim_Page (PageURL)
    SELECT DISTINCT Page
    FROM dbo.GSC_RawData
    WHERE Page NOT IN (SELECT PageURL FROM dbo.Dim_Page);
    
    -- Fact-Daten laden
    INSERT INTO dbo.Fact_SEO_Performance (
        DateKey, PageKey, KeywordKey, 
        GSC_Clicks, GSC_Impressions, GSC_CTR, GSC_Position
    )
    SELECT 
        CAST(CONVERT(VARCHAR(8), gsc.Date, 112) AS INT) AS DateKey,
        p.PageKey,
        k.KeywordKey,
        SUM(gsc.Clicks) AS GSC_Clicks,
        SUM(gsc.Impressions) AS GSC_Impressions,
        AVG(gsc.CTR) AS GSC_CTR,
        AVG(gsc.Position) AS GSC_Position
    FROM dbo.GSC_RawData gsc
    INNER JOIN dbo.Dim_Page p ON gsc.Page = p.PageURL
    INNER JOIN dbo.Dim_Keyword k ON gsc.Query = k.Keyword
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Fact_SEO_Performance f
        WHERE f.DateKey = CAST(CONVERT(VARCHAR(8), gsc.Date, 112) AS INT)
            AND f.PageKey = p.PageKey
            AND f.KeywordKey = k.KeywordKey
    )
    GROUP BY gsc.Date, p.PageKey, k.KeywordKey;
END;

Wie erstelle ich aussagekräftige Reports?

Daten hast du jetzt. Reports fehlen noch.

Top-Seiten nach Performance:

CREATE PROCEDURE dbo.Report_TopPerformingPages
    @StartDate DATE,
    @EndDate DATE,
    @TopN INT = 20
AS
BEGIN
    SELECT TOP (@TopN)
        p.PageURL,
        p.PageTitle,
        SUM(f.GSC_Clicks) AS TotalClicks,
        SUM(f.GSC_Impressions) AS TotalImpressions,
        AVG(f.GSC_Position) AS AvgPosition,
        SUM(f.GA_Conversions) AS TotalConversions,
        SUM(f.GA_Sessions) AS TotalSessions,
        CASE 
            WHEN SUM(f.GA_Sessions) > 0 
            THEN SUM(f.GA_Conversions) * 100.0 / SUM(f.GA_Sessions)
            ELSE 0 
        END AS ConversionRate
    FROM dbo.Fact_SEO_Performance f
    INNER JOIN dbo.Dim_Date d ON f.DateKey = d.DateKey
    INNER JOIN dbo.Dim_Page p ON f.PageKey = p.PageKey
    WHERE d.Date BETWEEN @StartDate AND @EndDate
    GROUP BY p.PageURL, p.PageTitle
    ORDER BY TotalClicks DESC;
END;

Keyword-Kannibalisierung erkennen:

CREATE PROCEDURE dbo.Detect_KeywordCannibalization
    @MinPages INT = 2
AS
BEGIN
    -- Finde Keywords, die von mehreren Seiten ranken
    WITH KeywordPageCount AS (
        SELECT 
            k.Keyword,
            COUNT(DISTINCT p.PageURL) AS PageCount,
            STRING_AGG(p.PageURL, '; ') WITHIN GROUP (ORDER BY AVG(f.GSC_Position) ASC) AS RankingPages,
            AVG(f.GSC_Position) AS AvgPosition
        FROM dbo.Fact_SEO_Performance f
        INNER JOIN dbo.Dim_Keyword k ON f.KeywordKey = k.KeywordKey
        INNER JOIN dbo.Dim_Page p ON f.PageKey = p.PageKey
        INNER JOIN dbo.Dim_Date d ON f.DateKey = d.DateKey
        WHERE d.Date >= DATEADD(MONTH, -1, GETDATE())
            AND f.GSC_Clicks > 0
        GROUP BY k.Keyword
        HAVING COUNT(DISTINCT p.PageURL) >= @MinPages
    )
    SELECT 
        Keyword,
        PageCount,
        RankingPages,
        AvgPosition,
        CASE 
            WHEN PageCount > 3 THEN 'High Risk'
            WHEN PageCount = 3 THEN 'Medium Risk'
            ELSE 'Low Risk'
        END AS RiskLevel
    FROM KeywordPageCount
    ORDER BY PageCount DESC, AvgPosition ASC;
END;

SEO-ROI berechnen:

CREATE PROCEDURE dbo.Calculate_SEO_ROI
    @StartDate DATE,
    @EndDate DATE,
    @InvestmentAmount FLOAT
AS
BEGIN
    -- Traffic-Value berechnen (vereinfacht)
    DECLARE @OrganicConversions FLOAT;
    DECLARE @ConversionValue FLOAT = 50; -- Durchschnittswert pro Conversion
    
    SELECT @OrganicConversions = SUM(f.GA_Conversions)
    FROM dbo.Fact_SEO_Performance f
    INNER JOIN dbo.Dim_Date d ON f.DateKey = d.DateKey
    INNER JOIN dbo.Dim_Source s ON f.SourceKey = s.SourceKey
    WHERE d.Date BETWEEN @StartDate AND @EndDate
        AND s.SourceType = 'Organic';
    
    DECLARE @Revenue FLOAT = @OrganicConversions * @ConversionValue;
    DECLARE @ROI FLOAT = ((@Revenue - @InvestmentAmount) / @InvestmentAmount) * 100;
    
    SELECT 
        @StartDate AS PeriodStart,
        @EndDate AS PeriodEnd,
        @InvestmentAmount AS Investment,
        @OrganicConversions AS Conversions,
        @Revenue AS EstimatedRevenue,
        @ROI AS ROI_Percent,
        CASE 
            WHEN @ROI > 100 THEN 'Profitable'
            WHEN @ROI BETWEEN 0 AND 100 THEN 'Break-even Zone'
            ELSE 'Loss'
        END AS Assessment;
END;

Wie automatisiere ich die Datenaktualisierung?

Manuell ist Quatsch. Das muss automatisch laufen.

SQL Server Agent Jobs:

-- Job 1: Täglich GSC-Daten abrufen (Python-Script)
USE msdb;
GO

EXEC dbo.sp_add_job
    @job_name = N'Daily_GSC_Import',
    @enabled = 1,
    @description = N'Import Google Search Console data daily';

EXEC sp_add_jobstep
    @job_name = N'Daily_GSC_Import',
    @step_name = N'Run Python Import',
    @subsystem = N'CmdExec',
    @command = N'python C:\Scripts\import_gsc_data.py',
    @retry_attempts = 3,
    @retry_interval = 5;

EXEC sp_add_schedule
    @schedule_name = N'DailyAt3AM',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @active_start_time = 030000; -- 03:00:00

EXEC sp_attach_schedule
    @job_name = N'Daily_GSC_Import',
    @schedule_name = N'DailyAt3AM';

EXEC sp_add_jobserver
    @job_name = N'Daily_GSC_Import';

-- Job 2: ETL ins DWH
EXEC dbo.sp_add_job
    @job_name = N'Daily_DWH_ETL',
    @enabled = 1;

EXEC sp_add_jobstep
    @job_name = N'Daily_DWH_ETL',
    @step_name = N'Load GSC Data',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.ETL_LoadGSCData';

EXEC sp_add_jobstep
    @job_name = N'Daily_DWH_ETL',
    @step_name = N'Load GA4 Data',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.ETL_LoadGA4Data';

EXEC sp_attach_schedule
    @job_name = N'Daily_DWH_ETL',
    @schedule_name = N'DailyAt3AM';

EXEC sp_add_jobserver
    @job_name = N'Daily_DWH_ETL';

Access: Automatisierung via VBA und Task Scheduler:

' In Access: Modul für automatischen Import
Public Sub AutomatedDailyImport()
On Error GoTo ErrorHandler
    
    ' 1. GSC-Daten abrufen
    Call ImportGSCData
    
    ' 2. GA4-Daten abrufen
    Call ImportGA4Data
    
    ' 3. Sistrix-Daten aktualisieren
    Call UpdateSistrixData
    
    ' 4. Reports aktualisieren
    Call RefreshAllReports
    
    ' 5. Log-Eintrag
    Call LogImportSuccess(Now())
    
    Exit Sub
    
ErrorHandler:
    Call LogImportError(Now(), Err.Description)
End Sub

' Access-DB als Scheduled Task ausführen
' Batch-Datei: run_import.bat
' "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" ^
' "C:\SEO_Dashboard\SEO_Dashboard.accdb" ^
' /x AutomatedDailyImport

Wie visualisiere ich die Daten?

Du hast jetzt ein DWH. Jetzt brauchst du ein Frontend.

Option 1: Power BI

Anbindung: Direkt an SQL Server

Vorteil: Interaktive Dashboards, keine Programmierung nötig

Nachteil: Lizenzkosten (ab 10 USD/User/Monat)

-- View für Power BI: SEO-Overview
CREATE VIEW dbo.PowerBI_SEO_Overview
AS
SELECT 
    d.Date,
    d.Year,
    d.Month,
    d.MonthName,
    p.PageURL,
    p.PageTitle,
    k.Keyword,
    SUM(f.GSC_Clicks) AS Clicks,
    SUM(f.GSC_Impressions) AS Impressions,
    AVG(f.GSC_Position) AS AvgPosition,
    SUM(f.GA_Sessions) AS Sessions,
    SUM(f.GA_Conversions) AS Conversions
FROM dbo.Fact_SEO_Performance f
INNER JOIN dbo.Dim_Date d ON f.DateKey = d.DateKey
INNER JOIN dbo.Dim_Page p ON f.PageKey = p.PageKey
LEFT JOIN dbo.Dim_Keyword k ON f.KeywordKey = k.KeywordKey
GROUP BY d.Date, d.Year, d.Month, d.MonthName, 
         p.PageURL, p.PageTitle, k.Keyword;

Option 2: Access-Frontend mit VBA

' Form: Dashboard_Main
' Charts via MS Graph Control

Public Sub UpdateDashboard()
On Error GoTo ErrorHandler
    
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    ' Traffic-Entwicklung
    rs.Open "SELECT Date, SUM(GSC_Clicks) AS Clicks " & _
            "FROM vw_SEO_Daily_Summary " & _
            "WHERE Date >= DateAdd('m', -3, Date()) " & _
            "GROUP BY Date ORDER BY Date", cn
    
    ' Chart aktualisieren
    Call UpdateChart(Me.ChartTraffic, rs, "Date", "Clicks")
    
    rs.Close
    
    ' Top Keywords
    rs.Open "SELECT TOP 10 Keyword, SUM(GSC_Clicks) AS Clicks " & _
            "FROM vw_Keyword_Performance " & _
            "GROUP BY Keyword ORDER BY SUM(GSC_Clicks) DESC", cn
    
    Call UpdateChart(Me.ChartTopKeywords, rs, "Keyword", "Clicks")
    
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Fehler beim Dashboard-Update: " & Err.Description
End Sub

Option 3: Excel mit Power Query

Anbindung: Power Query zu SQL Server

Vorteil: Vertraut, flexibel

Nachteil: Keine Echtzeit-Updates

' VBA: Export nach Excel mit Formatierung
Public Sub ExportSEOReportToExcel()
On Error GoTo ErrorHandler
    
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    rs.Open "EXEC dbo.Report_TopPerformingPages " & _
            "@StartDate = '" & Format(DateAdd("m", -1, Date), "yyyy-mm-dd") & "', " & _
            "@EndDate = '" & Format(Date, "yyyy-mm-dd") & "'", cn
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Sheets(1)
    
    xlWs.Name = "SEO Report"
    
    ' Header
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        xlWs.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Daten
    xlWs.Range("A2").CopyFromRecordset rs
    
    ' Formatierung
    xlWs.Range("A1:" & Chr(65 + rs.Fields.Count - 1) & "1").Font.Bold = True
    xlWs.Columns.AutoFit
    
    xlApp.Visible = True
    
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Fehler: " & Err.Description
End Sub

Was, wenn die Tools keine API haben?

Manche Tools sind stur. Keine API. Keine Schnittstelle.

Lösung: Power Automate Desktop (RPA)

Du kannst auch UI-Automation machen:

1. Power Automate öffnet das Tool
2. Navigiert zu Reports
3. Exportiert CSV
4. Speichert Datei
5. Import-Script liest CSV ein

Power Automate Desktop Flow (Pseudo-Code):

1. Run application "C:\Programme\SEO-Tool\tool.exe"
2. Wait 5 seconds
3. Click element "Reports" (UI Element)
4. Wait 2 seconds
5. Click element "Export CSV"
6. Wait for download
7. Move file from Downloads to C:\Import\
8. Run Python script "import_csv.py"
9. Close application

Hinweis: RPA ist fehleranfällig. Nur als letzter Ausweg.

Mein Fazit aus der Praxis

Du brauchst kein teures All-in-One-Tool.

Was du wirklich brauchst:

• SQL Server als zentrales DWH
• Python-Scripts für API-Zugriffe
• Automatisierung via SQL Agent
• Power BI oder Access als Frontend

Zeitaufwand: 2-4 Wochen Setup, dann läuft’s.

Kosten: SQL Server Express (kostenlos), Power BI Desktop (kostenlos), Python (kostenlos).

Nur für API-Zugänge zahlst du (Sistrix, Ahrefs).

Wichtigste Lektion: Fang klein an. Erst GSC + GA4. Dann erweitern.

Fragen? Schreib mir.

Ich bin der Datenschäfer. Und ich baue Dashboards für Entscheider.