Shane Walker Managing large data sets in Clay can be challenging, but I’ve developed a formula-based method to Condense, Move, and Expand data effortlessly. If you’re looking for a source of truth for your enrichment column data, this method is perfect for you.
With this approach, you can…
Retain ALL data in as few as 8 formula columns (or more, depending on the number of LinkedIn Experience results you need).
Compress data into JSON blocks for easy storage.
Unpack the data later into new columns using additional formulas.
IMPORTANT NOTES
Use this method only if you need to retain all enriched data.
If you don’t need everything, “Write to table” is simpler but won’t support creating columns for all fields in one table.
GETTING STARTED
- 1.
Add the formulas below to your table with the “Enrich Person from Profile” data.
- 2.
Use “Write to table” on the resulting ~8 formula columns.
- 3.
Use additional formulas to unpack the JSON blocks into separate columns (as needed).
If you need help at any step, let me know!
FORMULAS
[STEP 1] Current Experience + Latest Experience
JSON.stringify([{{Enrich Person from Profile}}?.current_experience, {{Enrich Person from Profile}}?.latest_experience])
[STEP 2] Volunteering + Education
JSON.stringify([{{Enrich Person from Profile}}?.volunteering, {{Enrich Person from Profile}}?.education])
[STEP 3] Experience Columns
Create one column per experience! Continue increasing the ending index [x] for additional experiences.
JSON.stringify([{{Enrich Person from Profile}}?.experience?.[0]])
JSON.stringify([{{Enrich Person from Profile}}?.experience?.[1]])
JSON.stringify([{{Enrich Person from Profile}}?.experience?.[2]])[continued…]
[STEP 4] Summary
JSON.stringify([{{Enrich Person from Profile}}?.summary])
[STEP 5] Slug, Person ID, Picture URLs, Followers, URL, etc.
JSON.stringify([{{Enrich Person from Profile}}?.slug, {{Enrich Person from Profile}}?.person_id, {{Enrich Person from Profile}}?.picture_url_copy, {{Enrich Person from Profile}}?.picture_url_orig, {{Enrich Person from Profile}}?.profile_id, {{Enrich Person from Profile}}?.num_followers, {{Enrich Person from Profile}}?.connections, {{Enrich Person from Profile}}?.url, {{Enrich Person from Profile}}?.user_id, {{Enrich Person from Profile}}?.last_refresh])
[STEP 6] Name, Org, Title, Headline, Location, Country, etc.
JSON.stringify([{{Enrich Person from Profile}}?.name, {{Enrich Person from Profile}}?.first_name, {{Enrich Person from Profile}}?.last_name, {{Enrich Person from Profile}}?.org, {{Enrich Person from Profile}}?.title, {{Enrich Person from Profile}}?.headline, {{Enrich Person from Profile}}?.location_name, {{Enrich Person from Profile}}?.dob, {{Enrich Person from Profile}}?.country, {{Enrich Person from Profile}}?.jobs_count, {{Enrich Person from Profile}}?.languages])
[STEP 7] Others Named + People Also Viewed
JSON.stringify([{{Enrich Person from Profile}}?.others_named, {{Enrich Person from Profile}}?.people_also_viewed])
[STEP 8] Projects, Publications, Courses, Awards, etc.
JSON.stringify([{{Enrich Person from Profile}}?.projects, {{Enrich Person from Profile}}?.publications, {{Enrich Person from Profile}}?.courses, {{Enrich Person from Profile}}?.awards, {{Enrich Person from Profile}}?.certifications, {{Enrich Person from Profile}}?.patents]).png){kind=logo}
