Skip to main content

Car Dealer Example

In an Automotive Retail (Car Dealer) setup, BPP unifies CRM leads, test drive bookings, web interactions, and sales transactions to power:

  • Action Prediction: likelihood a lead will convert into a car sale, request a test drive, or sign a financing plan.
  • pcLTV: expected lifetime value (vehicle + accessories + aftersales).
  • RFM Segmentation: recency, frequency, and monetary of purchases or service visits.
  • Interest Analysis: affinity for brands, car models, or categories.
  • Signals & Audiences: activating CRM leads or website visitors into ad platforms.

IDs & identity resolution​

Car dealers often work with multiple IDs across CRM, website, and offline systems. BPP reconciles these into a single Bytek ID.

  • CRM Lead ID (primary in lead-management system).
  • Hashed email (Google-style normalization + SHA-256).
  • Hashed phone (E.164 + SHA-256).
  • First-party cookie / GA4 user_pseudo_id from web analytics.
  • Dealer Management System (DMS) Customer ID for aftersales.

See Identifiers & Hashing for normalization rules.

Core tables​

1. User table — Leads / Customers​

CREATE TABLE my_dataset.crm_contacts (
  crm_contact_id     STRING,   -- CRM lead/customer ID
  hem                STRING,   -- hashed email
  hphone             STRING,   -- hashed phone
  fp_cookie_id       STRING,   -- GA4 user_pseudo_id if available
  first_name         STRING,   -- optional cleartext (hashed by BPP if flagged)
  last_name          STRING,   -- optional cleartext
  job_role           STRING,   -- if B2B fleet lead
  company_name       STRING,   -- if fleet lead
  acquisition_channel STRING,  -- website, referral, campaign
  lead_source        STRING,   -- UTM or CRM source
  lead_medium        STRING,   -- UTM medium
  lead_campaign      STRING,   -- campaign name
  preferred_brand    STRING,   -- declared interest (BMW, Audi...)
  preferred_model    STRING,   -- declared model interest
  budget_range       STRING,   -- declared price range
  financing_interest BOOL,     -- interested in financing/leasing
  service_customer   BOOL,     -- true if also aftersales customer
  consent_marketing  BOOL,
  consent_timestamp  DATETIME,
  created_at         DATETIME,
  updated_at         DATETIME
);

2. Event table — Deal Stage History​

Tracks how a lead moves across pipeline stages: New Lead → Contacted → Test Drive → Negotiation → Closed Won/Lost.

CREATE TABLE my_dataset.deal_stage_history (
  row_id          STRING,
  event_timestamp DATETIME,  -- UTC
  crm_contact_id  STRING,
  deal_id         STRING,
  old_stage       STRING,
  new_stage       STRING,    -- e.g. Test Drive, Proposal Sent, Closed Won
  car_model       STRING,    -- model linked to the deal
  car_category    STRING,    -- SUV, Sedan, EV...
  deal_value      FLOAT64,   -- quoted car value
  currency        STRING,
  financing_type  STRING,    -- lease, financing, cash
  dealer_location STRING,
  created_at      DATETIME
);

3. Event table — Test Drive Bookings​

CREATE TABLE my_dataset.test_drive_events (
  booking_id      STRING,
  event_timestamp DATETIME,  -- booking time
  crm_contact_id  STRING,
  hem             STRING,
  car_model       STRING,
  car_category    STRING,
  dealer_location STRING,
  booking_status  STRING,    -- booked, attended, no_show, cancelled
  created_at      DATETIME
);

4. Event table — Website Behavioural Data​

CREATE TABLE my_dataset.web_events (
  event_id        STRING,
  event_timestamp DATETIME,
  fp_cookie_id    STRING,
  hem             STRING,
  crm_contact_id  STRING,
  event_type      STRING,    -- pageview, brochure_download, form_submit
  page_url        STRING,    -- absolute URL
  utm_source      STRING,
  utm_medium      STRING,
  utm_campaign    STRING,
  gclid           STRING,
  gbraid          STRING,
  wbraid          STRING,
  device_class    STRING,
  browser         STRING,
  country         STRING,
  created_at      DATETIME
);

5. Event table — Aftersales Transactions (optional)​

CREATE TABLE my_dataset.service_transactions (
  transaction_id  STRING,
  event_timestamp DATETIME,
  crm_contact_id  STRING,
  hem             STRING,
  service_type    STRING,    -- maintenance, repair, parts
  service_value   FLOAT64,
  currency        STRING,
  dealer_location STRING,
  created_at      DATETIME
);

Do & don't summary​

✅ Do❌ Don't
Store pipeline as history (deal_stage_history)Overwrite a single deal_stage field
Track test drive bookings as eventsJust log "last_test_drive_date" in CRM
Keep absolute URLs and attribution fields in web eventsTruncate URLs or drop query params
Store transaction_id in sales/aftersalesRely only on mutable revenue fields
Hash PII consistently (SHA-256)Hash without trimming/lowering/normalizing

How BPP uses these tables​

  • Action Prediction — Positive = Closed Won deal; Negative = Closed Lost. Features from CRM (budget, brand, financing interest) + web behaviours (brochure downloads, test-drive booking).
  • pcLTV — Transaction sources = car sales + aftersales service. Product hierarchy = car_category||car_model.
  • RFM — Recency = last purchase or service. Frequency = number of purchases + services. Monetary = sum of sales + service values.
  • Interest — From web browsing (page_url) → IAB/Custom Interest; from declared preferred_brand/preferred_model.

Why this design works​

  • Deal history supports precise target derivation for Action Prediction.
  • Test drive events improve early-stage behavioural features.
  • Web behavioural data enables attribution, Interest, and better ad match rates.
  • Aftersales transactions extend pcLTV to the full customer lifecycle.
  • Multiple IDs (hem, hphone, cookie, CRM ID) maximize identity resolution & ad activation.
Last updated on