Fragmented ATM and mobile signals stalled branch plans — BigQuery & Looker align teams

Overview

Branch transformation at a regional bank dragged because insights into ATM usage and mobile adoption were fragmented and hard to compare. ATM telemetry lived in vendor tools, mobile app analytics refreshed on a different cadence, and demographic context arrived from separate sources with mismatched geographies. Intelligex built a governed pipeline that merged ATM telemetry, mobile analytics, and demographic enrichment into BigQuery, applied compliance-approved masking for personally identifiable information (PII), and surfaced a single executive dashboard with clear definitions and drill paths. Leaders redeployed resources with fewer debates and clearer backing because every view referenced the same governed metrics, evidence, and approvals.

Client Profile

• Industry: Banking and financial services (retail and small business)
• Company size (range): Regional bank with multi-state branch and ATM network
• Stage: Active branch modernization and digital adoption push
• Department owner: Strategy, Analytics & Executive Leadership (Enterprise Strategy / Retail Banking)
• Other stakeholders: Retail Operations, Digital Banking, ATM/Payments Operations, Compliance & Privacy, Information Security, Finance/FP&A, Marketing/CRM, Data Engineering, Contact Center

The Challenge

Resource decisions required a consistent picture of channel behavior: ATM withdrawal patterns by daypart and location, mobile deposit and bill-pay adoption, and local demographic trends that shaped demand. In practice, ATM monitoring tools tracked device-level health and transactions, mobile analytics captured usage in a separate platform, and demographics came from external data with inconsistent geographies. Each source refreshed on its own schedule and used different identifiers. Analysts exported extracts, aligned them by hand, and produced decks with differing cut-offs and definitions.

Compliance concerns added friction. PII appeared in raw telemetry and app logs, which limited sharing across teams. Legal and Privacy reviewed redactions late in the process, creating delays and inconsistent masking. Leaders wanted to keep existing ATM monitoring and mobile analytics tools, improve governance and comparability, and see a single view that made redeployment choices straightforward and defensible.

Why It Was Happening

Identity and calendars were fragmented. ATM IDs did not map cleanly to branch or market hierarchies, mobile events used app device/user keys without a shared account map, and demographic feeds used census geographies that did not align to branch trade areas. Refresh cadences and cut-offs varied by tool, which produced defensible but incompatible answers.

Governance arrived late. PII masking and disclosure checks were applied in slides rather than in the pipeline. Metric definitions such as “active ATM,” “digitally active customer,” or “mobile-first household” varied across teams, and there was no audit trail tying an executive view back to the exact datasets, filters, and approvals used.

The Solution

We implemented a permissions-aware analytics layer that unified ATM telemetry, mobile app analytics, and demographic data, standardized calendars and geographies, and embedded compliance controls upstream. ATM logs and device transactions were ingested, mobile app events flowed in from the analytics platform, and demographic enrichments were added from the American Community Survey. BigQuery hosted the conformed model, Cloud Data Loss Prevention (DLP) masked PII, and dbt encoded definitions and validations. Looker delivered executive and operational views with role-based access. A lightweight approval step captured Compliance and Privacy sign-off on masking and sharing scopes. Existing systems remained; the orchestration aligned data, definitions, and governance around them.

• ATM telemetry ingestion mapped to device, location, and branch hierarchies from vendor monitoring (for example, NCR ATM)
• Mobile analytics events and cohorts from the existing app analytics platform (for example, Adobe Analytics)
• Demographic enrichment from the U.S. Census Bureau’s American Community Survey with tract-level attributes (ACS 5-Year API)
• Centralized storage and modeling in BigQuery with shared calendars and geospatial joins
• PII detection and masking at ingestion using Google Cloud Data Loss Prevention, with policy-based suppression for small cohorts
• Metric definitions, transformations, and tests in dbt for identity stitching, channel metrics, and accepted values
• Executive and practitioner dashboards in Looker with drill-through to governed snippets and market context
• Role-based access via identity groups so Strategy, Retail, Digital, and Compliance see appropriate detail (for example, Okta Groups)
• Approval workflow for masking scopes and sharing levels using an existing review tool (for example, ServiceNow)
• Audit log linking dashboards to dataset versions, definitions, masking policies, and approvers; alignment to supervisory expectations in the FFIEC IT Examination Handbook

Implementation

• Discovery: Cataloged ATM device and transaction fields, mobile analytics events and cohorts, and demographic sources. Mapped branch and market hierarchies, ATM-to-branch relationships, and existing privacy controls. Reviewed prior branch transformation decks to identify recurring reconciliation issues and masking gaps.
• Design: Defined a shared identity model across devices, branches, markets, and app cohorts; authored a common calendar and refresh cut-offs; designed geospatial joins from branches to census tracts; encoded metric definitions in dbt; and drafted masking policies and approval steps with Compliance and Privacy.
• Build: Stood up ingestion to BigQuery for ATM telemetry and mobile events; implemented dbt transformations for identity stitching, channel metrics, and demographic joins; configured Cloud DLP for PII masking and small-cell suppression; built Looker dashboards with role-based filters; and wired the approval workflow and audit logging.
• Testing and QA: Replayed recent periods to reconcile counts with legacy reports; validated ATM-to-branch mappings and tract assignments; verified DLP masking behavior across edge cases; tested dbt checks for accepted values and stale feeds; and dry-ran approval flows with Compliance and Privacy.
• Rollout: Launched dashboards in read-only mode alongside existing spreadsheets and decks. After validating consistency and masking behavior, enabled the approval step for sharing scopes and made the governed dashboards the source of truth for branch transformation reviews.
• Training and hand-off: Delivered quick guides for Strategy and Retail on reading channel metrics and demographic overlays, for Digital on cohort definitions, and for Compliance on audit logs and masking policies. Assigned stewardship for metric definitions, identity mappings, and policy templates with a change-control cadence and human-in-the-loop review.

Results

Executives reviewed branch and market choices from a single dashboard that aligned ATM usage patterns, mobile adoption, and local demographics under shared definitions. Debates over cut-offs and identities subsided because data arrived on a set cadence with visible lineage, and every tile respected masking and access rules. Branch redeployments and service adjustments moved faster because channel behaviors and market context were comparable and current.

Operationally, analysts shifted from stitching extracts to monitoring exceptions and refining definitions. Compliance and Privacy saw policies enforced upstream with an audit trail, which reduced late-stage edits. Retail and Digital coordinated actions based on the same signals, aligning staffing, ATM fleet updates, and marketing nudges to local needs with clearer rationale.

What Changed for the Team

• Before: ATM, mobile, and demographics lived in separate decks with mismatched cut-offs. After: Looker delivered a single view with shared calendars, geographies, and definitions.
• Before: PII masking was applied in slides. After: Cloud DLP enforced masking at ingestion with approval steps for sharing scopes.
• Before: Analysts reconciled identities by hand. After: dbt stitched devices, branches, and cohorts with tested mappings and lineage.
• Before: Compliance weighed in late. After: An approval workflow captured Compliance and Privacy sign-off before distribution.
• Before: Decisions reopened basics each cycle. After: Dashboards carried consistent metrics with drill-through to governed evidence and demographic context.

Key Takeaways

• Unify channel telemetry and market context under a shared identity and calendar so branch choices start from comparable facts.
• Build privacy into the pipeline with PII detection and masking; approvals should happen before insights are shared.
• Encode metric definitions and validations in transformations; governance in code reduces reconciliation and drift.
• Use role-based access and audit trails to satisfy oversight while keeping insights broadly usable.
• Keep existing ATM monitoring and mobile analytics; layer ingestion, normalization, masking, and approvals around them.

FAQ

What tools did this integrate with?
We ingested ATM telemetry from vendor monitoring platforms (for example, NCR ATM), mobile app analytics from the client’s existing platform (for example, Adobe Analytics), and demographics from the U.S. Census Bureau’s ACS. Data landed in BigQuery, transformations and definitions ran in dbt, PII masking used Cloud DLP, dashboards were delivered in Looker, and access was governed by identity groups (for example, Okta Groups).

How did you handle quality control and governance?
We defined shared identities and calendars, encoded metric definitions and geospatial joins in dbt with validations for accepted values and stale feeds, and applied Cloud DLP masking at ingestion with small-cell suppression. A lightweight approval workflow captured Compliance and Privacy sign-off on sharing scopes. Audit logs linked dashboards to dataset versions, masking policies, and approvers, aligning with supervisory expectations in the FFIEC IT Examination Handbook.

How did you roll this out without disruption?
Dashboards launched in read-only mode alongside existing reports while teams validated mappings, metrics, and masking. After trust was established, we enabled the approval step and made the governed views the standard source for branch transformation reviews. Core systems stayed in place; the new layer orchestrated ingestion, normalization, masking, and access control around them.

How did you protect PII while analyzing channel behavior?
PII detection and masking occurred upstream with Cloud DLP, and small cohorts were suppressed to prevent re-identification. Role-based access limited exposure to detailed records, and Looker enforced row and column policies. Compliance reviewed masking scopes and received a full audit trail of policy applications and approvals.

How did you align ATM and mobile metrics with demographics?
ATM locations and branch trade areas were geocoded and joined to census tracts. Mobile cohorts were mapped to markets using available location and account attributes under masking policies. Shared calendars and cut-offs ensured each channel’s metrics aligned to the same periods, and dashboards allowed drill-through to confirm mappings and context.