Posted on

How to match back your opens to your original data file

Match back data file

When you want to download the contacts that opened or clicked your email campaign you need to go to ‘Reports’ and find the campaign you want the results for. You will then get an overview of the results and by clicking on any of the breakdowns at the bottom, the relevant contacts will be downloaded.

 

The files downloaded are .csv and contain:
  • Email – the email of the contact who interacted
  • Date – Date and time the interaction took place
  • URL – The link clicked in your email (if relevant)
download data

 

You may then find that you want to match these results back to your original dataset to see more information about your contacts. For example, if you have a telephone number, you may want to call the recipient or if you want to view any other information.

 

The example below shows the steps involved in matching back a list of opens to the original database. Files have been saved as:
  • master_file.csv – Complete database
  • report_opens.csv – The list of email addresses for the openers downloaded from Espiromail

 


Step 1 – Import your data files

Open Microsoft Access & select the ‘External Data’ Tab.

Click ‘Text File’ to import your CSV/ TXT/ XLS files (one at a time).
&nbs
Import

Find the file you are looking to import, select relevant options for the file including ‘First row Contains Field Names’, the correct Text Qualifier, No primary key. Repeat the process for both files you need. You will then see your files imported:

Files Imported

 


Step 2 – Create Query

Next, you want to pair your files together with the relevant information.
Select the ‘Create’ Tab and Select ‘Query Design’

 

create query design

 

Select both files and click ‘Add’ then close the box. Here you will see your 2 files:

 

Files

Step 3 – Perform Match

Pair the field that represents ’email address’ from the Master File to the field that represents ’email address’ on the Opens file.
This is done by clicking the master file and dragging across to the opens:
match
You now want to compile the file you want to export by dragging the ’email’ field from the Open file into the first column in the table below. Then drag all remaining fields from the Master file window into the other columns. (Exclude email address field).

 

Drag relevant fields

Step 4 – Run & save

Click on the red exclamation mark button named ‘Run’

 

run query
Run query

 

Then to export the contacts, save your query then right click to export as a CSV.