Keap To Looker Studio Dashboard Starter

Write the reporting contract before exporting data

Start with an observable decision rather than a list of charts. A useful contract can read: Every Monday, the sales owner should be able to compare created contacts, qualified opportunities, won sales, and collected value for the agreed period using named date, status, source, and exclusion rules. Replace those example measures with the real operating question. Name the audience, reporting period, timezone, refresh rhythm, decision owner, data owner, reviewer, and acceptable delay. A dashboard without this contract often becomes a collection of numbers that nobody can defend.

Define the grain of every source before combining anything. One contact row, one tag-application row, one opportunity row, and one sale row are different observations. A contact with three tags can become three rows after a join; a customer with two sales can become two more. If those rows are counted as contacts, the dashboard inflates. Write down what one row represents, which field is the stable identity, whether the source is a snapshot or event history, and which totals must reconcile before a chart is accepted.

Keep platform evidence and business definitions separate. Official Keap documentation can explain how a report or export behaves, while the business owner still decides what counts as a qualified lead, an active opportunity, a won sale, a refund, an excluded test record, or the reporting timezone. Looker Studio can calculate and display an agreed definition, but it cannot decide which definition the business should trust. Record unknowns honestly and hold the affected metric until its owner approves the rule.

Stage 1: choose one decision and one report grain

Choose the smallest decision that justifies the starter dashboard. Examples include reviewing new-contact volume by source, comparing opportunity movement by owner, or reconciling sales value by period. Do not begin with "show everything in Keap." Name the decision that should change when a measure moves and the person authorized to act on it. This keeps the first dashboard narrow enough to test and reduces the chance that unrelated contact, campaign, ecommerce, and tag logic are mixed into one ambiguous model.

State the reporting grain in plain language. "One row is one exported contact as of the export timestamp" behaves differently from "one row is one tag application event" or "one row is one completed sale." Record the key, expected row count, earliest and latest usable date, export timestamp, timezone, and snapshot boundary. If a source only describes current state, do not present it as complete historical event data. If the decision requires history that the export does not contain, mark the question blocked or scope a different source.

Stage 2: inventory the Keap exports and source boundaries

List every Keap report or export needed, why it is needed, who can produce it, and what it excludes. Keap's current contact-export guidance covers standard and custom contact fields, while tag membership and tag application history may require separate tag-oriented views or reports. Sales reporting has its own fields and grain. Treat each export as a source with a name, owner, generated timestamp, filters, column inventory, row count, and retention rule rather than dropping several files into an unnamed folder.

Use a sanitized structure sample before private data is transferred. Column names, data types, a few labelled synthetic rows, and expected counts are usually enough to confirm scope. Do not put passwords, API keys, payment details, raw customer exports, or private report links into the first service request. Once scope and transfer method are agreed, keep the original export unchanged as evidence. Perform cleaning in a separate working copy so a reviewer can distinguish source behavior from transformation behavior.

Stage 3: define row identity, duplicate handling, and join safety

Choose the most stable available identity for each source. A contact identifier may support contact-level counts, while an order or transaction identifier may support sale-level counts. Email can be useful for review but may be blank, shared, mistyped, or changed, so it should not automatically become the universal key. Record how blank keys, repeated exports, multiple sales, repeated tag applications, and merged contacts are handled. Do not silently remove duplicates based only on identical display values.

Google documents that CSV uploads to Looker Studio append data rather than merging it and do not remove duplicate records. That makes refresh discipline part of the model. Decide whether each refresh replaces a controlled data source, appends only new event rows, or rebuilds a deduplicated prepared file. Store an export timestamp or batch identifier when it helps distinguish snapshots. Before joining sources, calculate the expected relationship: one-to-one, one-to-many, or many-to-many. Hold any join that multiplies the reconciliation total unexpectedly.

Stage 4: create the field, date, status, and filter contract

Create a field dictionary with the source column, reporting label, expected type, allowed values, blank meaning, transformation, owner, and example. Keap custom fields can use different field types; text that looks like a number is not automatically a reliable numeric measure, and a date-like string may not parse consistently. Normalize only agreed fields. Preserve the original value when a transformation could hide source quality, and list values that fail parsing instead of coercing them into a plausible result.

For every time-based chart, name the date field and timezone. Contact-created date, opportunity-created date, sale date, payment date, and export date answer different questions. Define the inclusive or exclusive period boundary and how blanks or future dates are treated. For status-based metrics, list the exact included and excluded values and who approves changes. A filter control should not quietly redefine the denominator. If a dashboard compares sources, verify that their date and status boundaries are genuinely comparable.

Stage 5: write the metric dictionary and denominator rules

Write each metric as a testable sentence and formula. Include its business purpose, source, grain, numerator, denominator, distinct key, aggregation, date field, filters, exclusions, currency or unit, owner, and reconciliation target. "Conversion rate" is incomplete until the start population and successful outcome are explicit. "Revenue" is incomplete until order status, refunds, taxes, fees, currency, and date treatment are defined. Keep unsupported metrics out of the first build instead of filling gaps with assumptions.

Calculated fields in Looker Studio can exist at the data-source or chart level and can behave differently depending on aggregation and scope. Use reusable source-level calculations only when their definition should remain consistent across the report. Use chart-level calculations deliberately and document why they differ. Test totals before ratios, then test denominators with filtered and unfiltered views. If two stakeholders use different definitions, present the disagreement as an owner decision rather than choosing the more attractive number.

Stage 6: prepare a stable, typed, and versioned CSV dataset

Keep three layers: the immutable raw export, the prepared dataset, and the dashboard data source. The prepared file should have stable column names and order, explicit types, documented transformations, a batch or refresh date where useful, and no unreviewed private columns. Use UTF-8 and a consistent delimiter and decimal format. Remove test or internal records only through a named exclusion rule. Record the before and after row counts so cleaning remains auditable.

Google's CSV upload guidance expects a stable structure and field order. Its data-source documentation and field-refresh guidance also make schema changes important: renamed, removed, or newly added fields can require a refresh and can break charts or calculated fields. Treat a schema change as a controlled update. Compare the new header with the approved field contract, identify affected charts and calculations, stage the change, reconcile again, and update the handoff note. Do not overwrite a trusted report with an unreviewed export.

Stage 7: build decision-useful views and reconcile them

Build the smallest set of views needed for the decision: a few headline measures, a time trend, one useful breakdown, and a table that supports record-level review without exposing unnecessary personal data. Use controls only when their scope and effect are clear. Google documents that controls can filter report components and that grouping and field identity affect which components respond. Test each control against an expected result and label any non-obvious scope.

Reconcile in layers. First compare source and prepared row counts. Then compare distinct identities, date ranges, status counts, and excluded records. Next compare dashboard totals without controls. Finally apply each filter and compare a small sample of records. Record pass, mismatch, or blocked, the observed difference, likely cause, owner, and next action. A small unexplained difference is still unexplained; do not call it accurate because the chart looks reasonable. Capture screenshots with synthetic or redacted data only when they are needed for handoff.

Stage 8: document refresh, credentials, sharing, and handoff

Document who exports the next file, where the raw and prepared versions live, how the schema is checked, whether the dataset is replaced or appended, how duplicates are prevented, which totals must reconcile, and who approves publication. Google distinguishes data-source freshness from report viewing and provides credential choices that affect who can query a source. Choose owner or viewer credentials intentionally, use least privilege, and confirm the resulting experience with the intended reviewer.

Share the report and any reusable data source only with the agreed audience. Record the owner, editors, viewers, transfer plan, refresh rhythm, known gaps, unsupported metrics, open holds, and next review date. The handoff should let another responsible person refresh the controlled file and recognize a failed reconciliation without guessing. If the business now needs automatic ingestion, multiple sources, historical event modeling, a database, scheduled transformation, or real-time monitoring, stop treating that as a small extension of the CSV starter and scope a separate reporting system.