About Services Tools Blog Contact us

Booking Export Contact File

Turns a booking export with every answer packed into one column into a tidy contact file, adds children's dates of birth, and builds text-message lists including people who have not booked again.

  • Data Merge & Cleaning
  • Footwear retail
  • Pipeline
  • Productised
  • Local/private
  • 2025

The problem

Appointment exports hide name, phone and email inside a single form field, and the child's date of birth sits in another file. A reminder list also has to leave out people who already booked again.

What it does

The process finds the form questions in the packed column and writes one column per question. It adds dates of birth by matching the parent's email, and rewrites phone numbers to UK international format. A cutoff date can keep only the people who have not booked again, and it saves a text-message file and a lapsed-booker file.

Outcomes

  • Name, phone and email are proper columns.
  • Matched rows gain a date of birth and unmatched rows stay blank.
  • The text-message file uses international numbers and reports a count of rejects.
  • The lapsed list carries a non-responder tag and the last booking date.

Simplified mockup

Interactive preview with sample data. Nothing is sent to a server.

Booking export

Load bookings.csv
Load children.csv
Packed form column
RefWhenForm
B-10412 Jun 2025~1^name^Helen~2^secondname^Marsh~3^email^[email protected]~4^phone^07700 900214~5^notes^First fitting~6^rangetime^10:30 - 10:45~
B-104218 May 2025~1^name^Owen~2^secondname^Croft~3^email^[email protected]~4^phone^07700 900442~5^notes^First fitting~6^rangetime^11:00 - 11:15~
B-104311 Jun 2025~1^name^Priya~2^secondname^Nair~3^email^[email protected]~4^phone^home TBC~5^notes^First fitting~6^rangetime^11:30 - 11:45~
B-104422 Mar 2025~1^name^Nina~2^secondname^Okonkwo~3^email^[email protected]~4^phone^07700 900019~5^notes^First fitting~6^rangetime^14:00 - 14:15~
B-10453 Aug 2025~1^name^Nina~2^secondname^Okonkwo~3^email^[email protected]~4^phone^07700 900019~5^notes^First fitting~6^rangetime^09:20 - 09:35~
  1. Read the booking export
  2. Find the packed form column
  3. Collect the field names
  4. Join dates of birth on email
  5. Keep rows with email and phone
Load both files to start.

Children file

Joined on the parent email, lower case
ChildParent emailDate of birth
Amelie Marsh[email protected]12 Mar 2016
Jonah Marsh[email protected]4 Sep 2018
Leela Nair[email protected]2 Nov 2015
Ife Okonkwo[email protected]8 Jun 2019

Owen Croft has no row in the children file, so his date of birth stays blank.

Ready to write one column per field.

UK numbers

As exported
NamePhoneEmail
Helen Marsh07700 900214present
Owen Croft07700 900442present
Priya Nairhome TBCpresent
Nina Okonkwo07700 900019present
Rows need both an email and a phone.

Lapsed bookers

Cutoff

Anyone whose phone booked again after the cutoff is left out. The rest are tagged non-responder, with the last booking date.

Priya Nair never reaches this file: the number was rejected.

How it works

  1. Read the booking export and find the packed form column.
  2. Collect the distinct questions, then write one column for each.
  3. Add dates of birth from the children's file by matching email addresses, keeping several children on one row.
  4. Keep rows that have both an email and a phone, and rewrite the phone to UK international format.
  5. Reject numbers that do not fit, and tag the kept rows.
  6. For a lapsed list, drop anyone who booked again after the cutoff.

Pipeline

flowchart LR
  A[PackedCSV] --> B[Fields]
  B --> C[FlatFile]
  C --> D[DobJoin]
  D --> E[Phones]
  E --> F[SmsFile]
  E --> G[LapsedFile]