{"id":1871,"date":"2025-05-13T19:27:05","date_gmt":"2025-05-13T17:27:05","guid":{"rendered":"https:\/\/webmasterei-prange.de\/sea-reporting-dashboards-for-google-microsoft\/"},"modified":"2025-09-23T22:01:22","modified_gmt":"2025-09-23T20:01:22","slug":"sea-reporting-dashboards-for-google-microsoft","status":"publish","type":"post","link":"https:\/\/webmasterei-prange.de\/en\/sea-reporting-dashboards-for-google-microsoft\/","title":{"rendered":"SEA Reporting Dashboards for Google  Microsoft"},"content":{"rendered":"<style>.kb-image1138_364823-15 .kb-image-has-overlay:after{opacity:0.3;}<\/style>\n<figure class=\"wp-block-kadence-image kb-image1138_364823-15 size-full\"><img decoding=\"async\" src=\"https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/09\/googlecloud-aufbau-fuer-lookerstudio-agentur-dashboard-1200x522-1.png\" alt=\"\" class=\"kb-img\"><\/figure>\n\n<p>At SEACAMP in Jena on May 8, 2025, I gave a comprehensive presentation on SEA Reporting Dashboards. For those who couldn&#8217;t attend or would like to deepen their knowledge, I summarize here the most important content and detailed practical examples. <\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Warum_professionelle_Dashboards\">Why Professional Dashboards?<\/h2>\n\n<p>Agency dashboards create a unified communication standard, enable daily performance analysis, and significantly facilitate communication with clients. Individual filters, central data sources, and visually comprehensible presentation eliminate Excel chaos and strengthen the professional appearance. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Anforderungen_an_ein_optimales_Dashboard\">Requirements for an Optimal Dashboard<\/h3>\n\n<p>A good dashboard should meet the following criteria:<\/p>\n\n<ul class=\"wp-block-list\">\n<li>Consistency across clients<\/li>\n\n\n\n<li>Quick orientation for SEA managers<\/li>\n\n\n\n<li>Rapid creation and scalability<\/li>\n\n\n\n<li>Maintenance and operation by multiple people<\/li>\n\n\n\n<li>Controlled data scope and fast loading times<\/li>\n\n\n\n<li>Integration of specific business cases and own branding<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Looker_Studio_fur_schnelle_flexible_Dashboards\">Looker Studio for Fast, Flexible Dashboards<\/h3>\n\n<p>Looker Studio (formerly Data Studio) is Google&#8217;s free BI tool for data visualization. For SEA managers, it offers an uncomplicated way to create reports &#8211; especially in combination with Google data sources such as Google Ads, GA4, or BigQuery. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Vorteile\">Advantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Free:<\/strong> Ideal for agencies with many clients.<\/li>\n\n\n\n<li><strong>Direct integration:<\/strong> Seamless access to GA4, Google Ads, Google Sheets, BigQuery, and more.<\/li>\n\n\n\n<li><strong>Interactive:<\/strong> Users can flexibly choose time periods, filter with a click, and directly control data preparation.<\/li>\n\n\n\n<li><strong>Visualization:<\/strong> Many different chart types, tables, bars, and metric tiles.<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Nachteile\">Disadvantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Performance:<\/strong> With larger data volumes (e.g., &gt;1 million rows), the dashboard becomes slow.<\/li>\n\n\n\n<li><strong>Dependency on Google Account:<\/strong> Users absolutely need a Google login for usage and viewing.<\/li>\n\n\n\n<li><strong>Limited combinability:<\/strong> Not all data fields can be combined arbitrarily.<\/li>\n\n\n\n<li><strong>Ad-hoc connections:<\/strong> Data retrieval can be slow or unstable for non-pre-aggregated sources.<\/li>\n\n\n\n<li><strong>No own storage:<\/strong> The data is not stored in Looker Studio itself, but in the linked sources &#8211; which can lead to latencies.<\/li>\n<\/ul>\n\n<p>Looker Studio is ideal for simple, quick dashboards with Google connections. For larger setups, combining it with BigQuery as a preliminary data basis and structured pre-processing via tools like Dataform is recommended. <\/p>\n\n<ul class=\"wp-block-list\">\n<li>Looker Studio enables flexible, free dashboards with:<\/li>\n\n\n\n<li>Time period selection<\/li>\n\n\n\n<li>Direct filtering by clicking on values<\/li>\n\n\n\n<li>Diverse visualizations<\/li>\n\n\n\n<li>Direct integration of many sources (GA4, Google Ads, BigQuery, Google Sheets)<\/li>\n<\/ul>\n\n<p>However, Looker Studio reaches its limits with larger data volumes and absolutely requires a Google account.<\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Skalierung_mit_Google_BigQuery\">Scaling with Google BigQuery<\/h3>\n\n<p>Google BigQuery is the heart of a scalable agency dashboard. As a high-performance, cloud-based data warehouse, it is ideal for centrally managing large and complex data volumes. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Vorteile__1\">Advantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Scalability:<\/strong> Processing billions of rows at high speed.<\/li>\n\n\n\n<li><strong>Central data storage:<\/strong> All client and campaign data in one place.<\/li>\n\n\n\n<li><strong>SQL queries:<\/strong> Use of a powerful SQL dialect for individual analyses.<\/li>\n\n\n\n<li><strong>Automation:<\/strong> In combination with tools like Dataform and Terraform, data flows can be automated.<\/li>\n\n\n\n<li><strong>Security  rights management:<\/strong> Fine-grained access control via Google Cloud IAM.<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Nachteile__1\">Disadvantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Costs:<\/strong> Billing based on query volume (potentially expensive with poor query optimization).<\/li>\n\n\n\n<li><strong>Complexity:<\/strong> Higher entry barrier for teams without SQL or cloud experience.<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Beispiel_Kombiniertes_Agentur_Dashboard_Skalierbare_Version\">Example: Combined Agency Dashboard (scalable version)<\/h3>\n\n<p>The architecture of a scalable dashboard is based on the following structure:<\/p>\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Data sources:<\/strong> Google Ads  Microsoft Ads (all accounts each)<\/li>\n\n\n\n<li><strong>Import:<\/strong> Via Data Transfer Services to Google BigQuery<\/li>\n\n\n\n<li><strong>Structure:<\/strong> A separate dataset for each client<\/li>\n\n\n\n<li><strong>Transformation:<\/strong> Data preparation via Dataform<\/li>\n\n\n\n<li><strong>Visualization:<\/strong> Dashboards per client in Looker Studio<\/li>\n<\/ol>\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/05\/skalierbare-kundendashboards-schema-scaled.png\" alt=\"Scaling with Google BigQuery\" class=\"wp-image-9919\" title=\"scalable customer dashboards schema\"\/><\/figure>\n\n<p>This setup allows for:<\/p>\n\n<ul class=\"wp-block-list\">\n<li>Client-specific filters and visualizations<\/li>\n\n\n\n<li>Consistent structure and logic per dashboard<\/li>\n\n\n\n<li>Centralized maintenance and scaling for new clients<\/li>\n\n\n\n<li>Version control via SQL and Terraform projects<\/li>\n<\/ul>\n\n<p>BigQuery makes it possible to represent both operational metrics (e.g., clicks, conversions) and strategic analyses (e.g., channel comparisons, long-term evaluations) on a unified data layer. Google BigQuery solves these problems and serves as a central data warehouse: <\/p>\n\n<ul class=\"wp-block-list\">\n<li>Fast integration of diverse data sources<\/li>\n\n\n\n<li>Use of an SQL dialect<\/li>\n\n\n\n<li>Simple automation and reproducibility<\/li>\n<\/ul>\n\n<h2 class=\"wp-block-heading\" id=\"toc_BigQuery_DataTransfer_Service_DTS\">BigQuery Data Transfer Service (DTS)<\/h2>\n\n<p>A central element for efficiently integrating marketing data into BigQuery is the BigQuery Data Transfer Service (DTS). This tool enables automatic and regular import of data from sources such as Google Ads or the Merchant Center. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Vorteile__2\">Advantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Automated data imports:<\/strong> No manual CSV export or API scripting needed<\/li>\n\n\n\n<li><strong>Seamless integration with Google products:<\/strong> Data from Google Ads or Google Analytics can be integrated with just a few clicks<\/li>\n\n\n\n<li><strong>Time-controlled processes:<\/strong> Daily or hourly data updates possible<\/li>\n\n\n\n<li><strong>Combinable with third-party providers:<\/strong> External platforms can also be connected via connectors like Supermetrics or winsor.ai<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Nachteile__2\">Disadvantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Outdated API versions:<\/strong> Some connectors don&#8217;t always use the latest API versions, which can lead to missing fields<\/li>\n\n\n\n<li><strong>Limited control:<\/strong> The transfer process is less flexible than with a custom-developed API integration<\/li>\n\n\n\n<li><strong>Cost factor for third-party connectors:<\/strong> Supermetrics  Co. incur additional license fees<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Beispielhafte_Anwendung\">Example application:<\/h3>\n\n<p>DTS is particularly useful when creating agency dashboards to regularly provide Google Ads data for all clients in BigQuery. These datasets can then be further processed with Dataform and visualized through Looker Studio. This lays the foundation for scalable, automated reporting.  <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Konfiguration_Google_Ads_Data_Transfer\">Google Ads Data Transfer Configuration<\/h3>\n\n<p>To activate the Google Ads Data Transfer in BigQuery, no custom API programming is necessary. Activation is done directly through the Google Cloud Console: <\/p>\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Select BigQuery project<\/strong> \u2013 Ensure that the project has billing set up and BigQuery is activated.<\/li>\n\n\n\n<li><strong>Access the Data Transfer Service<\/strong> \u2013 You can create a new transfer under Transfers in BigQuery.<\/li>\n\n\n\n<li><strong>Select data source<\/strong> \u2013 For example, select \u201cGoogle Ads\u201d as the source.<\/li>\n\n\n\n<li><strong>Link account<\/strong> \u2013 The Google Ads account must be authenticated via OAuth.<\/li>\n\n\n\n<li><strong>Configure transfer<\/strong> \u2013 Set target dataset, update interval, and start date.<\/li>\n<\/ol>\n\n<p>After setup, BigQuery automatically creates tabular data structures such as  <code>Customer<\/code>,  <code>Campaign<\/code>,  <code>AdGroup<\/code>,  <code>Ad<\/code>  etc. These tables form the basis for further processing with SQL tools like Dataform. Note that not all metrics or assignments are included \u2013 for example, campaign or account names are often missing, requiring additional join logic (e.g., via  <code>customer_id<\/code>  and  <code>campaign_id<\/code>).<\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Erweiterte_Konfigurationsoptionen_im_BigQuery_Data_Transfer_Service\">Advanced Configuration Options in BigQuery Data Transfer Service<\/h2>\n\n<p>The BigQuery Data Transfer Service (DTS) offers additional configuration options to control the data import from Google Ads more precisely.<\/p>\n\n<p><strong>Table Filter:<\/strong> This option allows specific tables to be selected or excluded for import. By default, all available tables are imported. By specifying table names (e.g., <code>Campaign<\/code>, <code>AdGroup<\/code>) or prepending a minus sign for exclusion (e.g., <code>-Campaign, AdGroup<\/code>), the import can be precisely controlled. (Source: Google Cloud) <\/p>\n\n<p><strong>Refresh Window:<\/strong> This window determines how many days of data are updated retroactively with each transfer. By default, the refresh window is 7 days but can be extended up to 30 days. This is particularly useful for capturing delayed data updates or closing data gaps. (Sources: Stack Overflow, Supermetrics Support)   <\/p>\n\n<p><strong>Custom Reports:<\/strong> For specific analysis needs, custom reports can be created using Google Ads Query Language (GAQL). For each custom report, a GAQL query is defined that specifies the desired fields and resources. It&#8217;s important to note that certain GAQL clauses such as <code>WHERE<\/code>, <code>ORDER BY<\/code>, <code>LIMIT<\/code>, and <code>PARAMETERS<\/code> are not supported in the context of DTS. Additionally, a condition <code>WHERE segments.date = run_date<\/code> is automatically added when a date segment is included in the query. (Sources: Google Cloud, Swipe Insight, Stack Overflow)    <\/p>\n\n<p>By using these advanced configuration options, data imports from Google Ads can be made more efficient and targeted, improving the quality and relevance of data available in BigQuery.<\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Automatisierung_der_Datenubertragung_mit_Dataform\">Automating Data Transfer with Dataform<\/h2>\n\n<p>Dataform is an open-source tool from Google specifically developed for orchestrating SQL-based transformations in data warehouses like BigQuery. It allows teams to organize SQL code in a structured manner, develop modularly, and version control through Git. Dataform is particularly powerful in projects where many recurring transformations need to be executed consistently and reproducibly.  <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Vorteile_von_Dataform\">Advantages of Dataform:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>SQL-first:<\/strong> No additional programming language necessary \u2013 all transformations are based on SQL.<\/li>\n\n\n\n<li><strong>Incremental Processing:<\/strong> Tables can be efficiently updated without complete recalculations.<\/li>\n\n\n\n<li><strong>Dependency Management:<\/strong> Dataform automatically recognizes the order in which tables need to be created.<\/li>\n\n\n\n<li><strong>Automation:<\/strong> Jobs can be automatically executed via schedules or triggers.<\/li>\n\n\n\n<li><strong>Git Integration:<\/strong> Changes to the code are traceable, testable, and team-friendly.<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Konkrete_Verwendung_in_DashboardProjekten\">Concrete Use in Dashboard Projects:<\/h3>\n\n<p>In SEA dashboards, Dataform is used to process the raw data imported from the BigQuery Data Transfer Service:<\/p>\n\n<ul class=\"wp-block-list\">\n<li>Transformation of raw tables into usable models (e.g., campaign report, daily metrics, channel summaries)<\/li>\n\n\n\n<li>Joining tables to add missing fields such as campaign or account names<\/li>\n\n\n\n<li>Calculation of custom KPIs, such as cost\/revenue ratios or TV attributions<\/li>\n\n\n\n<li>Provision of final reporting tables as a source for Looker Studio<\/li>\n<\/ul>\n\n<p><strong>Example Dataform SQL Definition:<\/strong><\/p>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">config {\n  type: table,\n  schema: reporting,\n  name: daily_campaign_performance,\n  tags: [google_ads]\n}\n\n\u200b\n\nSELECT\n  campaign_id,\n  customer_id,\n  DATE AS report_date,\n  clicks,\n  impressions,\n  cost_micros \/ 1e6 AS cost_eur\n\nFROM\n  raw_google_ads_campaign_stats\nWHERE\n  DATE = CURRENT_DATE() - INTERVAL 30 DAY;<\/pre>\n\n<h2 class=\"wp-block-heading\" id=\"toc_DashboardErstellung_in_wenigen_Minuten_mit_Looker_Studio_Dashboard\">Dashboard Creation in Minutes with Looker Studio Dashboard Cloner<\/h2>\n\n<p>The Looker Studio Dashboard Cloner (lsd-cloner) is an open-source tool from Google that enables efficient duplication of Looker Studio dashboards. It uses the Looker Studio Linking API to copy dashboards including their data sources while making specific adjustments. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Funktionen_und_Einsatzmoglichkeiten\">Functions and Applications<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Interactive Configuration:<\/strong> lsd-cloner guides the user through a series of questions to define the desired adjustments for the dashboard to be cloned.<\/li>\n\n\n\n<li><strong>Automated Link Creation:<\/strong> Based on the inputs, the tool generates a link that, when opened, copies the dashboard with the specified settings.<\/li>\n\n\n\n<li><strong>Adjustment of Data Sources:<\/strong> It allows remapping of data sources so that the cloned dashboard refers to other BigQuery tables or views.<\/li>\n\n\n\n<li><strong>Template-based Work:<\/strong> By using JSON configuration files, recurring cloning processes can be standardized and automated.<\/li>\n\n\n\n<li><strong>Batch Processing:<\/strong> Multiple dashboards can be generated in series \u2013 ideal for a larger number of clients.<\/li>\n\n\n\n<li><strong>CLI-based:<\/strong> Runs on the command line, easily integrable into CI\/CD processes.<\/li>\n<\/ul>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Beispielhafte_Anwendung__1\">Example Application<\/h3>\n\n<p>A typical scenario is the creation of customer-specific dashboards based on a central template. By adjusting the data sources and other specific parameters, customized dashboards for different clients or projects can be created quickly and consistently. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Beispielhafte_JSONKonfiguration\">Example JSON Configuration<\/h3>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"json\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">{\n  project_id: webmasterei-reports,\n  dashboard_id: 456789-d764-4847-ba1a-99d8153a8793,\n  dashboard_name: {company name} Ads Dashboard,\n  dashboard_dataset: report_companyname,\n  dashboard_datasources: {\n  campaigns: campaigns\n  }\n}<\/pre>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Kommandozeilenbefehl_zur_Nutzung\">Command Line Command for Use<\/h3>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">npm install -g lsd-cloner\nlsd-cloner --answers=lookerstudio-ecommerce.json<\/pre>\n\n<p>This approach enables efficient scaling of reporting dashboards and ensures that all dashboards remain up-to-date and consistent with current data sources.<\/p>\n\n<p><strong>Note:<\/strong> lsd-cloner is an unofficial tool and is not officially supported by Google. It is advisable to conduct appropriate tests before using it in productive environments. <\/p>\n\n<p>GitHub Link: <a href=\"https:\/\/github.com\/google\/looker-studio-dashboard-cloner\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/google\/looker-studio-dashboard-cloner<\/a><\/p>\n\n<p>Terraform is an open-source infrastructure management tool by HashiCorp that allows cloud resources to be managed declaratively through code. Unlike manual configuration via user interfaces, Terraform enables the definition of the entire infrastructure in configuration files \u2013 which greatly simplifies maintenance, repeatability, and scaling. <\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Komplexer_Aufbau_der_Google_Cloud\">Complex Structure of Google Cloud<\/h2>\n\n<p>The image illustrates how complex the setup in Google Cloud can be to achieve the desired configuration. This makes the setup prone to errors and difficult to understand. Terraform offers a solution here.  <\/p>\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"445\" src=\"https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/09\/googlecloud-aufbau-fuer-lookerstudio-agentur-dashboard-1200x522-1-1024x445.png\" alt=\"\" class=\"wp-image-1139\" title=\"Google Cloud setup for Looker Studio agency dashboard\"\/><\/figure>\n\n<h2 class=\"wp-block-heading\" id=\"toc__2\">Infrastructure Management with Terraform<\/h2>\n\n<p>Terraform is an open-source infrastructure management tool by HashiCorp that allows cloud resources to be managed declaratively through code. Unlike manual configuration via user interfaces, Terraform enables the definition of the entire infrastructure in configuration files \u2013 which greatly simplifies maintenance, repeatability, and scaling. <\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Anwendung_im_Google_Cloud_Kontext\">Application in the Google Cloud Context<\/h3>\n\n<p>In conjunction with the Google Cloud Platform (GCP), Terraform uses so-called providers to automatically provision resources such as BigQuery datasets, IAM roles, service accounts, transfer jobs, or Looker Studio integrations.<\/p>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Vorteile__3\">Advantages:<\/h3>\n\n<ul class=\"wp-block-list\">\n<li><strong>Automation:<\/strong> Setup and changes are made via <code>terraform apply<\/code>, reducing sources of error.<\/li>\n\n\n\n<li><strong>Versioning:<\/strong> All configurations can be versioned and tracked in Git.<\/li>\n\n\n\n<li><strong>Modularity:<\/strong> Infrastructure can be structured into reusable modules (e.g., one module per customer).<\/li>\n\n\n\n<li><strong>Consistency:<\/strong> All customer instances are based on the same defined structure.<\/li>\n\n\n\n<li><strong>Scalability:<\/strong> New customers can be configured with a single command \u2013 with defined data source, location, time zone, and dashboard ID.<\/li>\n<\/ul>\n\n<p><strong>Terraform Example Configuration:<\/strong><\/p>\n\n<p>This example configuration demonstrates how to use Terraform to automatically create central Google Cloud resources for a Search Console dashboard project. The focus is on activating the BigQuery service and granting permissions to a service account that, for example, should import data from Google Search Console. All settings are defined as code and can therefore be versioned, documented, and automatically deployed.  <\/p>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">variable project_id {\n  description = The ID of your Google Cloud project\n  type = string\n}\n\nvariable region {\n  description = Data location \/ Execution location\n  type = string\n  default = europe-west-3\n}\n\n\u200b\nprovider google-beta {\n  project = var.project_id\n  region = var.region\n}\n\n\u200b\nresource google_project_service bigquery  \n  project = var.project_id\n  service=bigquery.googleapis.com\n  disable_on_destroy = false\n}\n\n\u200b\nresource google_project_iam_member search_console_job_user {\n  project = var.project_id\n  role = roles\/bigquery.jobUser\n  member = serviceAccount:search-console-data-export@system.gserviceaccount.com\n}\n\n\u200b\n\nresource google_project_iam_member search_console_data_editor {\n  project = var.project_id\n  role = roles\/bigquery.dataEditor\n  member = serviceAccount:search-console-data-export@system.gserviceaccount.com\n}<\/pre>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Terraform_Befehle_im_Uberblick\">Terraform Commands Overview:<\/h3>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">terraform apply -var-file='my-var-file.tfvars' -auto-approve \/\/ Applies the configuration (without prompting)\n\nterraform plan \/\/ Shows planned changes\n\nterraform destroy \/\/ Deletes all managed resources\n\nterraform workspace new myproject \/\/ Creates a new workspace named myproject\n\nterraform workspace select \/\/ Switches between existing workspaces<\/pre>\n\n<h3 class=\"wp-block-heading\" id=\"toc_Beispiel_KundenDashboard_automatisiert_anlegen\">Example: Automatically Creating a Customer Dashboard<\/h3>\n\n<p>This example shows how to automatically deploy a complete customer dashboard in Google Cloud using Terraform. Configuration is done via a separate<code>.tfvars<\/code> A file in which all customer-specific parameters are maintained centrally. This allows new customers to be provided with consistent data structures and dashboards within minutes \u2013 without any manual clicks in the Google Cloud Console.  <\/p>\n\n<p>The <code>terraform.tfvars<\/code> file contains, among others, the following parameters:<\/p>\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">{\n\n  customer_name = haarersatz24 \/\/ Unique name or abbreviation of the customer\n\n  gitCommitish = 9264d79b859fa091235cd2c58b4c14a9462b79b28 \/\/ Dataform transform version\n\n  defaultDatabase = my_project_name \/\/ Target dataset in BigQuery\n\n  defaultLocation = europe-west3 \/\/ Region of the dataset (e.g. Frankfurt)\n\n  timezone = Europe\/Berlin \/\/ Time zone for time-based evaluations\n\n  lookback = 30 \/\/ Lookback period in days for data retrieval\n\n  dashboard_id = 12398123-asdkjfb1239-1234bj-askjib \/\/ Reference to a Looker Studio template\n\n}<\/pre>\n\n<p>With this setup, <code>terraform apply<\/code> automatically creates all relevant cloud resources for the customer:<\/p>\n\n<ul class=\"wp-block-list\">\n<li>a BigQuery dataset with the correct location<\/li>\n\n\n\n<li>appropriate permissions<\/li>\n\n\n\n<li>Dataform jobs for data transformation<\/li>\n\n\n\n<li>link to the dashboard template in Looker Studio<\/li>\n<\/ul>\n\n<p>Through this automation, you can not only save time but also significantly increase the consistency and maintainability of your SEA dashboards. The configuration is versionable, verifiable, and can be integrated into CI\/CD processes. <\/p>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Der_Weg_zum_KundenDashboard\">The Path to the Customer Dashboard<\/h2>\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Create Google Cloud Setup:<\/strong> The necessary resources such as BigQuery dataset, service accounts, and IAM roles are defined and deployed with Terraform.<\/li>\n\n\n\n<li><strong>Create Dashboard Template:<\/strong> A generic dashboard with placeholder data sources is designed in Looker Studio, which will be cloned later.<\/li>\n\n\n\n<li><strong>Create Customer-Specific Configuration:<\/strong> Using <code>.tfvars<\/code> files, the target structure is defined for each customer (e.g., dataset name, time zone, dashboard ID).<\/li>\n\n\n\n<li><strong>Automatically Create Dashboards:<\/strong> Using the Looker Studio Cloner (lsd-cloner), an individual dashboard is generated for each customer from the template.<\/li>\n\n\n\n<li><strong>Deliver  Explain Dashboard:<\/strong> The dashboards are handed over to customers, explained, and integrated into ongoing support.<\/li>\n\n\n\n<li><strong>Optimize  Expand:<\/strong> Regular adaptation of the dashboards to new KPIs, data sources, or business models keeps the reports current and useful.<\/li>\n<\/ol>\n\n<h2 class=\"wp-block-heading\" id=\"toc_Weiterfuhrende_Links_fur_detaillierte_Anleitungen\">Further Links for Detailed Instructions<\/h2>\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/webmasterei-prange.de\/google-search-console-bigquery-export-einrichten\/\">Export Google Search Console to BigQuery<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/webmasterei-prange.de\/en\/matomo-bigquery-import\/\">Matomo BigQuery Integration<\/a><\/li>\n<\/ul>\n\n<p>This article summarizes all central aspects of the presentation at SEACAMP 2025 and provides a practical guide for building scalable SEA dashboards with Google and Microsoft data. From the basics of dashboards to integration with Looker Studio, BigQuery, and Dataform, all the way to automated infrastructure management with Terraform, it explains step by step how to implement a professional reporting architecture. Tools like the Looker Studio Dashboard Cloner (lsd-cloner) are also explained in a practical manner and placed within a complete setup process. The article is supplemented with further links and instructions for Google Search Console, Matomo integration, as well as tips for automation. Thus, this contribution offers a compact yet in-depth resource for SEA professionals who want to make reporting not only better but also more efficient.    <\/p>\n","protected":false},"excerpt":{"rendered":"<p>At SEACAMP in Jena on May 8, 2025, I gave a comprehensive presentation on SEA Reporting Dashboards. For those who couldn&#8217;t attend or would like to deepen their knowledge, I summarize here the most important content and detailed practical examples. Why Professional Dashboards? Agency dashboards create a unified communication standard,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":1870,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","slim_seo":{"title":"SEA Dashboards for Google Microsoft","description":"With dashboards for Google Microsoft Ads, you get clear KPIs, avoid data silos, and make better decisions."},"footnotes":""},"categories":[28,29],"tags":[],"class_list":["post-1871","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-query","category-google-data-studio"],"taxonomy_info":{"category":[{"value":28,"label":"Big Query"},{"value":29,"label":"Google Data Studio"}]},"featured_image_src_large":["https:\/\/webmasterei-prange.de\/wp-content\/uploads\/2025\/09\/googlecloud-aufbau-fuer-lookerstudio-agentur-dashboard-1200x522-1-1024x445.png",1024,445,true],"author_info":{"display_name":"admin","author_link":"https:\/\/webmasterei-prange.de\/en\/author\/admin\/"},"comment_info":0,"category_info":[{"term_id":28,"name":"Big Query","slug":"big-query","term_group":0,"term_taxonomy_id":28,"taxonomy":"category","description":"","parent":0,"count":12,"filter":"raw","cat_ID":28,"category_count":12,"category_description":"","cat_name":"Big Query","category_nicename":"big-query","category_parent":0},{"term_id":29,"name":"Google Data Studio","slug":"google-data-studio","term_group":0,"term_taxonomy_id":29,"taxonomy":"category","description":"","parent":0,"count":4,"filter":"raw","cat_ID":29,"category_count":4,"category_description":"","cat_name":"Google Data Studio","category_nicename":"google-data-studio","category_parent":0}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1871","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/comments?post=1871"}],"version-history":[{"count":1,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1871\/revisions"}],"predecessor-version":[{"id":1872,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1871\/revisions\/1872"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/media\/1870"}],"wp:attachment":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/media?parent=1871"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/categories?post=1871"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/tags?post=1871"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}