How to build a marketing attribution dashboard

by

TL;DR

You can build a working marketing attribution dashboard using GA4, BigQuery, and Looker Studio for free in about half a day. The end result is a live report that shows you which channels drive conversions under your chosen attribution model. You need basic spreadsheet skills and a tagged media plan before you start.

What You Need Before You Start

  • A Google Analytics 4 property with at least 30 days of conversion data
  • GA4 linked to BigQuery (free sandbox tier covers up to 10 GB/month of queries)
  • Google Sheets for data assembly and intermediate modelling
  • Looker Studio (free, formerly Data Studio) for the visual layer
  • A consistent UTM tagging convention already in place across your paid and owned channels
  • A conversion event defined in GA4 (purchase, lead_form_submit, sign_up, or equivalent)
  • Optional: OWOX BI Pipeline if you want automated streaming exports without writing SQL yourself
  • Optional: Python 3.10+ with the google-cloud-bigquery library if you prefer scripted exports

If your UTM tagging is a mess, fix that first. The rest of this guide assumes utm_source, utm_medium, and utm_campaign are consistently applied across every paid touchpoint. A dirty tagging scheme produces a broken attribution model no matter how good your dashboard looks.

Step 1: Decide Your Attribution Model Before You Touch a Spreadsheet

Attribution models are not a dashboard feature. They are a business decision. Picking one before you build means you only build once.

The four models worth understanding are last-click, first-click, linear, and data-driven. Last-click is the GA4 default and overstates the value of retargeting. First-click understates it. Linear spreads credit equally across every touchpoint in a conversion path. Data-driven uses GA4’s ML model and requires at least 400 conversions per month to produce reliable weights.

For most growth marketers running under $50k/month in ad spend, linear attribution is the honest starting point. It does not over-reward any single channel and it is straightforward to calculate manually.

Write your model choice in a shared doc and get buy-in from your media buyer and CFO before the dashboard exists. Changing the model after people have made budget decisions based on the numbers causes political problems, not just technical ones.

You should now see: a one-sentence attribution model statement you can quote when someone asks why a channel looks over- or under-credited.

Step 2: Audit and Standardize Your UTM Parameters

Open your GA4 property. Navigate to Reports > Acquisition > Traffic acquisition. Look at the Session source/medium breakdown.

If you see values like google / cpc, Google / CPC, and google / CPC as separate rows, your tagging is inconsistent. GA4 is case-sensitive for UTM values.

Build a UTM master sheet with these five columns: source, medium, campaign, content, term. Every active ad campaign gets a row. Lock down the naming convention and share it with everyone who creates ad links.

A clean naming pattern looks like this:

utm_source=google
utm_medium=cpc
utm_campaign=brand-search-us-q2-2026
utm_content=rsa-headline-v2
utm_term=attribution+dashboard

Run a quick Sheets formula to flag inconsistencies in your historical data:

=COUNTIF(A:A, A2) > 1

Apply it to your source column after you pull a raw session export. Any TRUE result means a duplicate variant exists somewhere.

You should now see: a clean UTM master reference with zero duplicate source/medium combinations.

Step 3: Link GA4 to BigQuery and Run Your First Export

In GA4, go to Admin > Product Links > BigQuery Links and connect your GCP project. Enable daily exports. The free BigQuery sandbox gives you 10 GB of query processing per month, which is enough for a site doing under 500k sessions.

Once linked, BigQuery populates a dataset called analytics_XXXXXXXXX with an events_* table. Daily partitions appear within 24 hours.

Run this query to pull a flat conversion path table:

SELECT
  user_pseudo_id,
  event_date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
  event_name
FROM
  `your-project.analytics_XXXXXXXXX.events_*`
WHERE
  event_name IN ('purchase', 'generate_lead', 'sign_up')
  AND _TABLE_SUFFIX BETWEEN '20260401' AND '20260430'

Replace your-project and the event names with your actual values. Save the query. It costs roughly 0.5 MB per 10k events.

You should now see: a flat table of user IDs, dates, channel touchpoints, and conversion events ready for attribution calculation.

Step 4: Export the Conversion Path Data to Google Sheets

BigQuery lets you export query results directly to Sheets via the Explore Data button or by scheduling a query to a Sheets-connected export. For a monthly pull, clicking Save results > Google Sheets from the query editor is fast enough.

For automated refreshes, install the BigQuery connector in Sheets under Extensions > Connected Sheets. Paste your query and set a refresh schedule of daily or weekly.

Rename the resulting sheet tab raw_paths. Do not apply any formatting here. This is your source-of-truth layer and you never edit it by hand.

You should now see: a raw_paths tab in Sheets with one row per conversion event per user, refreshing on a schedule.

Step 5: Build the Channel Mapping Table

Create a second tab called channel_map. This table translates your raw source/medium combinations into clean channel buckets that make sense to a non-technical stakeholder.

source medium channel_bucket
google cpc Paid Search
facebook cpc Paid Social
instagram cpc Paid Social
newsletter email Email
(direct) (none) Direct
google organic Organic Search

Back in your raw_paths tab, add a channel_bucket column using VLOOKUP or XLOOKUP:

=XLOOKUP(A2&"|"&B2, channel_map!A:A&"|"&channel_map!B:B, channel_map!C:C, "Other")

This concatenates source and medium for an exact match lookup. Any unmatched combination falls into Other, which tells you what tagging gaps still exist.

You should now see: every conversion row labelled with a clean channel bucket, and an Other bucket that highlights remaining UTM gaps.

Step 6: Calculate Attribution Credit Per Channel

Create a third tab called attribution_model. For linear attribution, every touchpoint in a conversion path gets equal credit.

First, count how many touchpoints each converting user had:

=COUNTIF(raw_paths!D:D, A2)

Where column D holds user_pseudo_id and A2 is a unique user ID. Then calculate the credit share:

=1 / touchpoint_count

Multiply that fraction by the conversion value (or 1 if you are counting conversions, not revenue).

Summarise by channel bucket with a SUMIF:

=SUMIF(attribution_model!B:B, "Paid Search", attribution_model!C:C)

Build a summary table with these columns: channel_bucket, attributed_conversions, attributed_revenue, cost (pulled from your ad platforms manually or via a connector), CPA, ROAS.

You should now see: a summary table showing each channel’s attributed share of conversions and revenue for the date range you queried.

Step 7: Connect Google Sheets to Looker Studio

Open Looker Studio and create a new report. Click Add data > Google Sheets and select your workbook. Connect both the attribution_model summary tab and the raw_paths tab as separate data sources.

Set the date field to event_date and configure it as a date dimension. Looker Studio auto-detects most column types but double-check that numeric columns like attributed_revenue are set to Number, not Text.

You should now see: a blank Looker Studio canvas with two data sources available in the right-side panel.

Step 8: Build the Core Attribution Visuals

Add four components to the first page of your dashboard:

  1. Scorecard row across the top: total attributed conversions, total attributed revenue, blended CPA, blended ROAS
  2. Bar chart showing attributed conversions by channel bucket
  3. Stacked bar chart showing attributed revenue by channel over the last 12 weeks (use event_date aggregated by week on the X axis)
  4. Table with columns: channel, conversions, revenue, cost, CPA, ROAS, and conditional formatting that turns CPA red when it exceeds your target

For the CPA conditional formatting in the table, use Looker Studio’s Manage data panel to create a calculated field:

CASE
  WHEN attributed_revenue / attributed_conversions > 50 THEN "above_target"
  ELSE "on_target"
END

Replace 50 with your actual CPA target. Apply a style rule that colours the row red when the field equals above_target.

You should now see: a one-page dashboard showing channel performance with colour-coded CPA alerts.

Step 9: Add a Date Filter and a Channel Drilldown

Add a Date range control at the top of the report. Set the default to the last 30 days. This single control updates every chart on the page.

Add a second page called Channel Detail. On this page, add a Filter control populated with channel_bucket. When a user clicks a channel in the filter, all charts on the page scope to that channel only.

Add a breakdown table on this page showing campaign as a dimension with the same conversion and cost columns as your summary table. This lets you go from channel to campaign in two clicks.

Share the report with view-only access using a link. Never give edit access to stakeholders unless they understand data source configurations.

You should now see: a two-page report where the first page gives the channel overview and the second page shows campaign-level breakdown for any selected channel.

Step 10: Schedule a Weekly Refresh and Add a Changelog Tab

Attribution dashboards go stale fast. Set your Connected Sheets query to refresh every Monday at 6am. Add a note in the Sheets workbook header row with the last refresh timestamp using:

=NOW()

Format it as Last updated: [date] and reference it in a Looker Studio text box.

Create a fifth tab called changelog in your Sheets file. Log every model change, date range shift, or new channel mapping with a date and your initials. When someone asks why the numbers changed between last month and this month, you have an audit trail.

You should now see: a fully operational dashboard that refreshes automatically and has a documented change history.

Common Mistakes To Avoid

  • Mixing attribution models mid-flight. Switching from last-click to linear halfway through a quarter makes month-over-month comparisons meaningless. Pick a model and stick to it for at least one full quarter before evaluating it.
  • Forgetting offline and dark social touchpoints. Direct traffic is not the same as truly unattributed traffic. If you run podcast ads or out-of-home campaigns, create a manual touchpoint entry process or your data dramatically undersells those channels.
  • Using GA4’s default channel grouping without customising it. GA4 buckets referral traffic from partner newsletters as Referral, not Email. Your channel map in Step 5 needs to account for this or your email numbers will be understated.
  • Not excluding internal traffic. If your team visits the site frequently, define an internal IP filter in GA4 under Admin > Data Streams > Configure tag settings > Define internal traffic. Unfiltered internal sessions inflate direct and organic counts.
  • Treating attributed ROAS as the single source of truth. Attribution models are approximations. Use this dashboard to identify directional trends and budget shifts, not to make precision decisions about individual keywords.
  • Building the dashboard before agreeing on the conversion event. If your CFO thinks the goal is purchase and your media buyer optimises for add_to_cart, your dashboard will always look wrong to at least one of them.

When To Level Up

This Sheets and Looker Studio setup works well for teams spending under $100k/month across five or fewer channels. Once you cross that threshold, a few things start to break.

BigQuery query costs increase, manual cost uploads from ad platforms become a bottleneck, and the Sheets data model becomes fragile when multiple people are updating the channel_map tab at the same time.

At that point, consider a purpose-built attribution tool or a proper data warehouse layer. Tools like Northbeam and Triple Whale handle multi-touch attribution natively with direct ad platform integrations. dbt on top of BigQuery gives you a version-controlled, testable data model that can scale to 10 channels and $1M+/month without spreadsheet debt.

If you are running a SaaS business with a long sales cycle, look into tools that handle offline conversion imports and CRM data joins, because web analytics alone will misattribute a huge share of your pipeline.

For a broader look at tools in this space, browse the options on /category/growth/ where we review the major players side by side.

Related reading: Best BI tools for small business and startups and UTM tracking: the complete guide for 2026.

Frequently Asked Questions

Do I need a paid tool to build a marketing attribution dashboard?
No. GA4, BigQuery (free tier), Google Sheets, and Looker Studio are all free for most small and mid-size operations. You only need paid tools when your data volume or channel complexity outgrows the free tier limits, which typically means more than 10 GB of BigQuery queries per month or more than 10 active paid channels.

How is linear attribution different from data-driven attribution in GA4?
Linear attribution splits conversion credit equally across every touchpoint in the path. Data-driven attribution uses a machine learning model trained on your actual conversion data to assign weights, which means high-intent touchpoints near the conversion typically receive more credit. Data-driven requires a minimum of 400 conversions per month per conversion event to produce stable results.

Can I use this dashboard for ecommerce and lead generation at the same time?
Yes, but track them as separate conversion events and build separate summary tables for each. Blending ecommerce revenue attribution and lead gen form submissions into a single table produces numbers that are hard to act on.

How often should I update the cost data in my attribution model?
Weekly is the practical minimum if you are making budget decisions based on the CPA and ROAS columns. If you are on a tight ad spend cycle, pull cost exports from Google Ads, Meta, and any other platforms every Monday and paste them into a costs reference tab in your Sheets workbook.

My GA4 data looks different from my ad platform data. Which one is right?
Neither is definitively right. GA4 undercounts due to ad blockers, cookie consent drop-off, and cross-device gaps. Ad platforms overcount because they use view-through attribution and their own click models. The truth is somewhere in between. Build your dashboard on GA4 data for consistency, and flag the variance to stakeholders so they understand the data is a model, not a census.

Bottom Line

Building a marketing attribution dashboard from scratch takes one focused half-day if your UTM tagging is clean. You define a model, pull conversion path data from GA4 via BigQuery, map it to channel buckets in Sheets, calculate credit shares with simple formulas, and connect the whole thing to Looker Studio for a live visual layer. The result is not a perfect picture of reality but it is an honest, consistent, and repeatable way to allocate budget across channels. The biggest risk is not technical, it is organizational: picking the wrong model or switching it mid-quarter. Once you have a working version, run it for a full quarter before adjusting anything. For the next step in growing your analytics stack, explore the tools and guides in /category/growth/.