Marketo Field Cleanup with Python

Unused custom fields in Marketo can create clutter and increase the risk of data errors. Although identifying these fields can be tedious, a combination of export lists and a Python script can simplify the cleanup process. Here's how to do it.

Marketo Field Use Process

Create Export Lists

Start by creating static lists for export:

1. Find the total number of leads in your database in Marketo’s Database section.
2. In Field Management (Admin), export a list of all fields.
3. Calculate the required number of lists with the formula: Number of lists = (Total Leads x Total Fields) / 70,000,000.

Smart Campaign to Add Leads to List

Use a smart campaign to distribute leads across your lists:

• Smart List: Filter for “Email Address” set to ‘is not empty’ to capture all leads.
• Flow: Use 'Add to List' with choices for each list, setting conditions based on a “Random Sample” percentage.

Export Leads in Marketo

Export each list with ‘All Columns’ selected. Save these files for the Python script. Note that exports may take several minutes each.

Run Python Script

Use this Python script to analyze exported lists and count field usage:

import csv, os

def field_counter():
    folder_path = 'I:/Marketo Export Folder/'  # Update with your folder path
    files = os.listdir(folder_path)
    csv_create = open('Your folder location/file.csv','w',newline='',encoding='utf-8')
    csv_writer = csv.DictWriter(csv_create, fieldnames=['Field','Count'])
    csv_writer.writeheader()
    field_json = {}
    for file in files:
        open_csv = open(f'{folder_path}{file}','r',encoding='utf-8')
        csv_reader = csv.DictReader(open_csv)
        for row in csv_reader:
            for mkto_field, value in row.items():
                if mkto_field in field_json:
                    if len(value.strip()) > 0:
                        field_json[mkto_field] += 1
                else:
                    field_json[mkto_field] = 1 if len(value.strip()) > 0 else 0
        open_csv.close()

    for field, count in field_json.items():
        csv_writer.writerow({'Field': field, 'Count': count})
    csv_create.close()
    
field_counter()

Once the script completes, open the CSV file in Excel or Sheets to assess field usage. Consider consolidating or deleting fields used by less than 25% of leads.