WISE Market-Location Prospect-Conversion Report — Design Spec

# WISE Market-Location Prospect-Conversion Report — Design Spec

Date: 2026-07-07 · Owner: Pierre Samson · Audience: WISE (UK) Lead Gen (Daniel Shu / Tiago), Lucas Status: design — pending review before planning

1. Goal

Measure the effectiveness of the WISE market-location cold-email prospecting (the firmographic cohorts built via Companies House / firmocology and sent through Eloqua). For every company addressed in the send files, answer: did they become a Lyréco customer, on which journey (old webshop vs new HubSpot onboarding), how long each leg took, how much they spent on 1st/2nd purchase, what they bought, and where they sat on our prospect levels before converting. Deliver as a WISE-only, refreshable custom report (per-company table + cohort rollups + journey-map visual).

2. Scope & phasing

• v1 (this spec): the purchase/activation spine — buildable entirely on existing local

• v2 (deferred, separate spec): GA4 pre-purchase web activity (UTM landing attribution via

• Tenant scope: WISE / UK only (source_country = 'GB'). Report exists only for WISE.
• Grain: one row per company = lyreco_118_id (rolls up multiple contacts/sends).

3. Data sources (all local; no external API)

| Source | DB | Role | Key | |---|---|---|---| | imports/marketlocation/*.xlsx | file | daily send files (118 ID, campaign, sent date, firmographics, open/click) | 118 ID | | eloqua_campaign_responders | PG | ingest target; prospect firmographics + engagement | lyreco_118_id, campaign_label | | eloqua_responder_bridge | PG | 118 ID → payer_number (BvD-matched) + match_method/bvd_sim | lyreco_118_id | | companies_house_profiles, ecom_bvd_companies/_payers, prospect_community_match | PG | CH + community match to lift linkage past BvD-only | company / CRO | | prospect_profile, prospect_world_propensity | PG | prospect model level (fit_tier, warmth_score, world, predicted_pct) | lyreco_118_id | | ecom_accounts | PG | old-journey activation: creation_date, creation_channel, ecom_creation_source_campaign/_medium/_ad_content | account_number | | hubspot_downstream_funnel | PG | new-journey activation + pre-computed order summary | account_number, email | | hubspot_contacts | PG | HubSpot registration detail (lifecyclestage, createdate) | account_number, email | | ecom_order_lines | TS | 1st/2nd purchase date, amount, product mix | soldto_number (= account), source_country | | prospect_tiers | TS | (v2) GA4 online behavioral tier | visitor_id |

Cross-DB: responders/bridge/accounts/hubspot/prospect-model in Postgres (leadcontagion); ecom_order_lines + prospect_tiers in TimescaleDB (lc_timeseries). No cross-DB SQL — pull matched account_numbers from PG, then query TS with WHERE soldto_number = ANY(%s).

4. Linkage / matching (company → account)

1. Primary: lyreco_118_id → eloqua_responder_bridge.payer_number → ecom_accounts.account_number (LPAD-10 both sides). Carries match_method + bvd_sim → match_confidence (confident ≥0.70 / likely / weak / no_match), reusing the logic in scripts/export_eloqua_responder_priority.py. 2. Enrichment (Pierre's ask): where the BvD bridge yields no_match (~78% of responders), fall back to the Companies House / community match used to build the cohorts — companies_house_profiles + prospect_community_match + community_match_soft.py — matching on CRO number / company name + post code. Every matched row records how it matched (match_source ∈ {bvd, companies_house, community}) and its confidence, so match quality is auditable and never silently inflated. 3. Files carry email domain not full email, so prospect→HubSpot linkage runs 118 ID → account_number → hubspot_downstream_funnel.account_number (not on email).

5. Per-company data model (report row)

Identity / firmographics (from eloqua_campaign_responders): lyreco_118_id, company, market_sector, sic_07_2digit_desc, employees_band, post_code, town. Campaign: campaign_label, email_version (derived from UTM version token e.g. V1_EMAIL_F_ML_OFFICE_FINANCE), first_sent_date (MIN sent), n_sends. Email engagement: open_count, click_count, engagement_tier (T1_super…T5_none / X_optout). Prospect model level: world, predicted_pct (prospect_world_propensity); fit_tier, warmth_score, engagement_tier (prospect_profile) — joined on lyreco_118_id. Match: account_number, match_source, match_confidence. Activation (dual-journey):

• webshop/old: ecom_creation_date, creation_channel, campaign_attributed (= ecom_creation_source_campaign matches a market-loc UTM).
• HubSpot/new: funnel_stage (1_hubspot_only→4_repeat_customer), lifecyclestage, hubspot_created.
• activation_journey ∈ {webshop, hubspot, both, none}; activated flag; net_new (activation date > first_sent_date) vs pre-existing customer.

• 1st: first_order_date, days_to_first_order (hubspot_downstream_funnel, cross-checked vs order_lines MIN), first_order_amount (Σ sales_amount of the first order_number), first_order_top_families.
• 2nd: second_order_date (2nd distinct order_number by order_date), days_after_first, second_order_amount, second_order_top_families.
• totals: total_orders, lifetime_orders, monetary_12mo (hubspot_downstream_funnel).

6. Metric rules

• Purchase hygiene (order_lines): order_channel_code = 'W' (web) + exclude the known corrupt

• Product mix ("most popular products in cart"): rank product_reference → product family via

• 1st vs 2nd order = the two earliest distinct order_number by order_date for the account.
• Averages are cohort-level (avg first-order amount, avg second-order amount) over converters

• Net-new vs pre-existing: a company whose account/HubSpot record predates first_sent_date is

7. Deliverable

A refreshable WISE custom report (the script-backed Report pattern: script prints ===RESULT_MARKDOWN=== + writes an XLSX; registered under logs/reports/):

1. Per-company table — all §5 fields, filterable by email version/sector, engagement tier, prospect level, match confidence, activation journey. 2. Cohort rollups — conversion rate, median leg-times, avg 1st/2nd amounts, top product families, sliced by: email version/sector · engagement tier · prospect level (fit_tier / world) · activation journey · match confidence. 3. Cohort journey-map viz (v1 = data-driven wave, image-1/3 style): stages Reached → Engaged → Activated (webshop|HubSpot) → 1st purchase → 2nd purchase → Repeat, each carrying count, conversion %, median days-to-next, avg amount. Rendered as inline SVG/HTML (CSP-safe). v2 layers real GA4 web touchpoints + Sankey.

8. Components (build units)

1. scripts/ingest_market_location.py — load imports/marketlocation/*.xlsx → eloqua_campaign_responders (map columns, derive campaign_label from Campaign Name + email_version from UTM token, WISE tag, dedupe on (lyreco_118_id, campaign_label, sent_date); idempotent for daily reruns). 2. scripts/refresh_marketloc_match.py (or extend _build_eloqua_bridge.sql) — run BvD + CH + community match for the newly-ingested 118 IDs, populate eloqua_responder_bridge + match_source. 3. scripts/compute_marketloc_conversion.py — the cross-DB spine (§3–6); emits per-company + cohort result + the journey-map viz; registers the WISE report. 4. Report registration JSON under logs/reports/.

9. Gotchas (carry into the plan)

• Cross-DB 2-step (§3); soldto_number = account_number (LPAD-10); WISE = source_country='GB'.
• VPN-on localhost→IPv6 DB hang → use 127.0.0.1 URIs (already in .env).
• Files have email domain not full email → link via account_number, never email.
• funnel_stage numeric lifecyclestage values exist (SAP account ids as strings) — treat any

• Do not inflate match: every row records match_source + confidence; no_match companies are

10. Success criteria

• Per-company table covers 100% of ingested market-loc companies (matched + unlinked).
• 1st/2nd purchase timing, amounts, and top families populate for converters, cross-checked between

• Cohort rollups + journey-map render in a WISE-only refreshable report.
• Dual-journey (webshop vs HubSpot) activation is distinguished and counted per company.

11. Open / v2

• GA4 pre-purchase web activity (UTM landing via eloqua_contact_id) + prospect_tiers online tier.
• Sankey / experience-map viz (image-4 style) with real web touchpoints.
• If a funnel_stage/lineage ambiguity arises, brain-ask the swarph brain as tiebreak.