Set up a unified marketing dashboard in a day

What does a unified marketing dashboard include?

A unified marketing dashboard is one overview page that shows every channel your team runs (organic social, search, paid media, email and earned media) against the same business outcomes, with a drill-down page per channel. By the end of the day you will have:

• All marketing sources connected and syncing on a schedule.
• A channel mapping table that assigns every campaign to one of about ten channels.
• Three normalized models: marketing_spend_daily, social_content_daily and web_outcomes_daily.
• Shared measures for spend, sessions, key events, revenue, MER, CAC and engagement rate.
• An overview page with six blocks and drill-downs for social, search and paid.
• Alerts on spend pacing, tracking gaps and unmapped campaigns.

If you want the reasoning behind each design choice, read the whitepaper One View of Marketing. This guide is the hands-on companion. You can also start from the Marketing command center template, which ships the models and layout described here, and adapt it as you go.

Before you start: access and decisions

Most delays on setup day come from missing permissions, not technical problems. Collect access the day before, and make three decisions in writing so you do not relitigate them mid-build.

Hour 1: Connect every source

Connect sources in order of business impact: revenue first, then paid media, then analytics and search, then social, email and earned media. Revenue comes first because every other number is eventually checked against it.

1. 1
   Step 1:

   Connect the revenue source

   In Connectors, add your CRM or shop. If revenue lives in your own Postgres or MySQL database and should not leave your network, connect it through Kimo Bridge so queries run in place.

2. 2
   Step 2:

   Connect ad platforms with 25 months of history

   Add Google Ads, Meta Ads, LinkedIn Ads and TikTok Ads. Request at least 25 months so you can compare year over year and, later, fit a media mix model.

3. 3
   Step 3:

   Connect GA4 and Search Console

   Add GA4 and Search Console. The Search Console setup doc explains property types. Search Console retains 16 months of data⁴Source 4 · Google Analytics HelpConnect Search Console to Google Analyticssupport.google.com, so backfill as soon as possible and keep the history in Kimo from then on.

4. 4
   Step 4:

   Connect social, email and earned media

   Add each organic social account, your email platform and your media monitoring export. These sources rarely carry revenue, but they explain changes in branded search and direct traffic.

5. 5
   Step 5:

   Set sync schedules

   Daily syncs are enough for most marketing data; ad spend can sync hourly if you pace budgets intraday. See sync schedules for options.

Hour 2: Map every campaign to one channel list

Spend lives in ad platforms, sessions in analytics and revenue in your CRM. The only bridge between them is campaign naming, usually the utm_source, utm_medium and utm_campaign values on your links. Start by agreeing on a short channel list, then write rules that assign every source and medium pair to exactly one channel.

Align your rules with GA4’s default channel groups wherever possible, so your dashboard and GA4 tell the same story. For example, GA4 classifies traffic as Paid Social when the source matches a known social site and the medium matches the regular expression ^(.*cp.*|ppc|retargeting|paid.*)$, and as Email when the source or medium is email, e-mail, e_mail or e mail¹Source 1 · Google Analytics Help[GA4] Default channel groupsupport.google.com. Keep your extensions (for example, separating partner newsletters from owned email) explicit.

12345678910111213141516171819202122232425select
    lower(trim(source)) as source,
    lower(trim(medium)) as medium,
    case
        when lower(medium) in ('email', 'e-mail', 'e_mail', 'e mail')
            or lower(source) in ('email', 'e-mail', 'e_mail', 'e mail')
            then 'Email'
        when lower(source) in ('google', 'bing')
            and regexp_like(lower(medium), '^(.*cp.*|ppc|retargeting|paid.*)$')
            then 'Paid search'
        when lower(source) in ('facebook', 'instagram', 'linkedin', 'tiktok', 'x', 'pinterest', 'youtube')
            and regexp_like(lower(medium), '^(.*cp.*|ppc|retargeting|paid.*)$')
            then 'Paid social'
        when lower(medium) = 'organic' then 'Organic search'
        when lower(medium) in ('social', 'social-network', 'social-media', 'sm')
            or lower(source) in ('facebook', 'instagram', 'linkedin', 'x', 't.co', 'pinterest', 'youtube', 'threads')
            then 'Organic social'
        when lower(medium) in ('display', 'banner', 'cpm') then 'Display'
        when lower(medium) in ('pr', 'press', 'earned') then 'Earned media'
        when lower(medium) = 'referral' then 'Referral'
        when source = '(direct)' then 'Direct'
        else 'Unmapped'
    end as channel
from web_sessions_raw
group by 1, 2

Note that the rule order matters: the email rule runs first so a newsletter sent from a social scheduling tool is not misclassified, and paid rules run before organic rules so facebook / cpc lands in Paid social. Test the mapping by listing the twenty largest source and medium pairs and checking each one by eye.

Hour 3: Normalize platform metrics

Each platform exports different field names and defines engagement differently. LinkedIn’s organization statistics compute engagement as clicks, likes, comments and shares over impressions²Source 2 · Microsoft Learn (LinkedIn Marketing API)Organization Share Statisticslearn.microsoft.com. Instagram’s media insights count total interactions as likes, saves, comments and shares, and mark impressions as deprecated for media created after July 2, 2024 in favor of views³Source 3 · Meta for DevelopersInstagram Media Insights (API reference)developers.facebook.com. The fix is to import components, rename them to canonical columns and recompute every ratio yourself.

1234567891011121314151617181920212223242526272829303132-- Canonical columns: views (delivery), reach, engagements (no clicks), link_clicks
select date, 'instagram' as platform, account_id,
       views,
       reach,
       likes + comments + shares + saves            as engagements,
       coalesce(link_clicks, 0)                     as link_clicks
from instagram_media_daily
union all
select date, 'linkedin-pages', organization_id,
       impression_count                             as views,
       unique_impressions_count                     as reach,
       like_count + comment_count + share_count     as engagements,
       click_count                                  as link_clicks
from linkedin_share_stats_daily
union all
select date, 'facebook', page_id,
       views, reach,
       reactions + comments + shares                as engagements,
       link_clicks
from facebook_page_posts_daily
union all
select date, 'x', account_id,
       impressions, null                            as reach,
       likes + replies + reposts + bookmarks        as engagements,
       url_clicks
from x_posts_daily
union all
select date, 'youtube', channel_id,
       views, null,
       likes + comments + shares                    as engagements,
       0
from youtube_channel_daily

Two details are easy to miss. First, reach is set to null where a platform does not report distinct accounts at the grain you need; a null is honest, a zero is wrong. Second, reach is never summed across platforms in later steps, because each platform de-duplicates only its own audience.

1234567891011121314151617181920select s.date,
       s.platform,
       s.campaign_name,
       m.channel,
       s.spend * fx.rate                                as spend,
       s.impressions,
       s.clicks
from (
    select date, 'google-ads' as platform, campaign_name, currency, cost as spend, impressions, clicks from google_ads_campaign_daily
    union all
    select date, 'meta-ads', campaign_name, currency, spend, impressions, clicks from meta_ads_campaign_daily
    union all
    select date, 'linkedin-ads', campaign_name, currency, cost_in_local_currency, impressions, clicks from linkedin_ads_campaign_daily
    union all
    select date, 'tiktok-ads', campaign_name, currency, spend, impressions, clicks from tiktok_ads_campaign_daily
) s
left join fx_rates_daily fx
    on fx.date = s.date and fx.from_currency = s.currency and fx.to_currency = 'USD'
left join campaign_channel_map m
    on m.campaign_name = lower(s.campaign_name)

The third model, web_outcomes_daily, joins GA4 sessions and key events by date, source and medium to the channel map, and adds orders or pipeline from the revenue system by the same keys. If you export GA4 to BigQuery, use the daily tables rather than the intraday streaming tables for attribution fields; Google notes that streaming export omits user-attribution fields (campaign, source, medium) for new users, and that user-attribution data for existing users needs about 24 hours to fully process⁵Source 5 · Google Analytics Help[GA4] BigQuery Exportsupport.google.com.

Hour 4: Define measures once

Measures are the numbers people will quote. Define each once, with a description and an owner, so the overview, the drill-downs and every Ask Kimo answer use the same formula. Eight measures cover most needs.

123456789101112131415161718192021222324252627282930313233343536373839404142model: marketing_overview
owner: marketing-ops
grain: date, channel
measures:
  spend:
    type: sum
    sql: spend
    format: currency
    description: Media spend in USD, all paid platforms
  sessions:
    type: sum
    sql: sessions
  key_events:
    type: sum
    sql: key_events
    description: Sign-ups and demo requests (GA4 key events)
  revenue:
    type: sum
    sql: revenue
    format: currency
    description: Net revenue from the system of record, refunds removed
  new_customers:
    type: count_distinct
    sql: customer_id
    filters: [is_first_order = true]
  mer:
    type: ratio
    numerator: revenue
    denominator: spend
    format: multiplier
    description: Total revenue / total marketing spend, all channels
  cac:
    type: ratio
    numerator: spend
    denominator: new_customers
    format: currency
  engagement_rate:
    type: ratio
    numerator: engagements
    denominator: views
    format: percent
    description: Reactions, comments, shares, saves over views. Clicks excluded

Read the glossary entries for MER, CAC and engagement rate if your team uses different variants. The important part is not which variant you choose but that you choose one and write it into the measure description. If paid CAC and blended CAC both matter, define both with distinct names.

Hour 5: Build the overview and drill-downs

Build one overview page that answers "what did marketing produce this week, and at what cost?" in under a minute, then one drill-down per channel family for depth. Use the same date filter and comparison period on every page.

For drill-downs, the social media overview and SEO funnel templates are ready-made. The paid drill-down needs only a campaign table with spend, clicks, key events and CPA, sortable by spend. You can see the finished result on simulated data in the command center.

Hour 6: Add alerts and share

Alerts turn the dashboard from something people check into something that tells them when to look. Start with four, each routed to the person who can act.

Configure them in Alerts (see the alerts docs), then share the overview with a scheduled weekly snapshot to the team channel. Pin the definitions page so newcomers can see how each number is built.

How do you check the numbers before sharing?

Reconcile three totals before anyone outside the build team sees the dashboard. Small gaps are expected; large ones point to a mapping or currency bug.

Search Console clicks and GA4 organic sessions will never match exactly: Search Console counts clicks on Google, while GA4 counts sessions only when the tag fires and includes other search engines in Organic Search. Our guide to SEO reporting with Search Console and GA4 covers the join in detail.

Troubleshooting

Paid social shows up as organic social

Your ad links are missing a paid medium, or the medium value does not match the paid pattern. Check the Meta and LinkedIn URL parameters fields, and make sure the medium is something like paid_social or cpc, not social.

Google Ads traffic appears as (not set)

Check that auto-tagging is enabled and that the Google Ads account is linked to the GA4 property. When both auto-tagging and manual UTM tags are present, GA4 prioritizes auto-tagging⁷Source 7 · Google Ads HelpAbout auto-taggingsupport.google.com, so manual tags on Google Ads links are optional.

The sum of channel revenue is larger than total revenue

You are summing platform-attributed revenue. On the overview, channel revenue must come from one attribution rule applied to orders in your system of record. Keep platform-reported conversions on the paid drill-down only.

Engagement rate jumped overnight for one network

Check for a platform definition change, such as a metric being renamed or deprecated. Add an annotation with the effective date, and if possible keep both the old and new series for a few weeks.