Client Data Mapping to Existing System

Overview

This document maps the client's provided data structure to our existing Airtable tables and identifies new tables that need to be created.

Existing Tables in the System

1. Users
2. Tasks
3. Comments
4. Briefs
5. Articles
6. Backlinks
7. KPI Metrics
8. URL Performance
9. Keyword Performance
10. Monthly Projections

Client Data Tables

1. Growth Model
2. Clusters
3. CVR% Metrics
4. Guide Chapter
5. Target Personas
6. Client Specific Tags
7. Clients
8. Target KPI's

Mapping

1. Growth Model → SEO Growth Models (New/Expanded)

The client's Growth Model table contains detailed SEO growth modeling data that should be stored in a dedicated table. This appears to be similar to the existing SEO Growth Models table in our templates but with more fields.

2. Clusters → Content Clusters (Existing/Expanded)

The client's Clusters table maps to our existing Content Clusters table but may need additional fields.

3. CVR% Metrics → New Table

This is a new table for tracking conversion rate metrics by different page types and signup types.

4. Guide Chapter → New Table

This is a new table for organizing content by chapters or guides.

5. Target Personas → New Table

This is a new table for tracking target audience personas.

6. Client Specific Tags → New Table

This is a new table for client-specific tagging and categorization.

7. Clients → Clients (Existing/Expanded)

The client's Clients table maps to our existing Clients table but may need additional fields.

8. Target KPI's → KPI Metrics (Existing/Expanded)

The client's Target KPI's table maps to our existing KPI Metrics table but may need additional fields.

Field Mapping Details

Growth Model Table

Client Field	Type	Maps To	Entity
Main Keyword	string	Keywords.Keyword	Keywords
Target Page URL	string	URL_Performance.URLPath	Target URL
Publication Status	string	URL_Performance.Status	Target URL
Page Type (Main)	string	URL_Performance.PageType	Target URL
Page Type (Sub)	string	URL_Performance.PageSubType (new)	Target URL
Target KW Score	string	Keywords.Score (new)	Keywords
All Clients	string	Clients.Name	Clients
Base URL	string	URL_Performance.BaseURL (new)	Target URL
Country	string	URL_Performance.Country (new)	Target URL
Last Updated	date	URL_Performance.LastUpdated (new)	Target URL
Update Row	boolean	-	-
Update All	boolean	-	-
Cluster	string	Clusters.Name	Keywords
Primary CTA	string	URL_Performance.PrimaryCTA (new)	Target URL
Target Persona	string	Personas.Name	Keywords
YouTube Target ?	boolean	URL_Performance.YouTubeTarget (new)	Target URL
YouTube Video Status	string	URL_Performance.YouTubeVideoStatus (new)	Target URL
Main Keyword Position	number	Keywords.CurrentPosition	Keywords
Clicks Last Month	number	URL_Performance.Clicks	Keywords
Average Traffic Of Top 3	number	Keywords.AverageTrafficTop3 (new)	Keywords
H1 Tag	string	URL_Performance.H1Tag (new)	Target URL
Meta Title	string	URL_Performance.MetaTitle (new)	Target URL
Meta Title Length	number	URL_Performance.MetaTitleLength (new)	Target URL
Meta Description Length	number	URL_Performance.MetaDescriptionLength (new)	Target URL
Current N° Of RDs	number	URL_Performance.CurrentRDs (new)	Target URL
Average DR Of Top 3 Positions	number	Keywords.AverageDRTop3 (new)	Target URL
Average DR Of Bottom 3 Positions	number	Keywords.AverageDRBottom3 (new)	Target URL
Lowest DR In SERP	number	Keywords.LowestDRInSERP (new)	Target URL
N° Of RDs Of Lowest DR In SERP	number	Keywords.RDsOfLowestDR (new)	Target URL
Position Of Lowest DR In SERP	number	Keywords.PositionOfLowestDR (new)	Target URL
Average N° Of RDs Of Lowest 3 DRs	number	Keywords.AvgRDsOfLowest3DRs (new)	Target URL
RDs Gap	number	Keywords.RDsGap (new)	Keywords
Potential Click Uplift Gap	number	Keywords.PotentialClickUpliftGap (new)	Keywords
Sign Up CVR % (Tag)	string	CVR_Metrics.Value	Target URL
Sign Up -> Paid Customer % (Tag)	string	CVR_Metrics.Value	Target URL
Potential Uplift In Signups	number	URL_Performance.PotentialUpliftInSignups (new)	Target URL
New Potential Customers Uplift	number	URL_Performance.NewPotentialCustomersUplift (new)	Target URL
RAW Metrics	string	URL_Performance.RAWMetrics (new)	Target URL
Link Building Status	string	URL_Performance.LinkBuildingStatus (new)	Target URL
Confidence In Uplift ?	number	URL_Performance.ConfidenceInUplift (new)	Target URL
Notes	text	URL_Performance.Notes (new)	Target URL
Extra Content Assets Needed (Formula)	formula	URL_Performance.ExtraContentAssetsNeeded (new)	Target URL
Actual N° RDs Needed	number	Keywords.ActualRDsNeeded (new)	Keywords
Starting N° RDs	number	Keywords.StartingRDs (new)	Keywords
Starting Keyword Position	number	Keywords.StartingPosition (new)	Keywords
N° RDs Built	number	URL_Performance.RDsBuilt (new)	Target URL
Secondary Anchor Text	string	URL_Performance.SecondaryAnchorText (new)	Target URL
Month (Link Building Targets)	string	Months.Name	Months
Adjusted CVR%	number	Keywords.AdjustedCVR (new)	Keywords
Specific Tag	string	Keywords.SpecificTag (new)	Keywords
Month (Keyword Targets)	string	Months.Name	Months
New/Refresh	string	Keywords.NewRefresh (new)	Keywords
In Ahrefs Rank Tracker	boolean	Keywords.InAhrefsRankTracker (new)	Keywords
CPC ($)	number	Keywords.CPC (new)	Keywords
Main Keyword VOL	number	Keywords.Volume	Keywords
Main Keyword KD	number	Keywords.Difficulty	Keywords
H1 Tag (Old)	string	URL_Performance.H1TagOld (new)	Target URL
Meta Title (Old)	string	URL_Performance.MetaTitleOld (new)	Target URL
Meta Title Length (Old)	number	URL_Performance.MetaTitleLengthOld (new)	Target URL
Meta Description	string	URL_Performance.MetaDescription (new)	Target URL
Meta Description (Old)	string	URL_Performance.MetaDescriptionOld (new)	Target URL
Meta Description Length (Old)	number	URL_Performance.MetaDescriptionLengthOld (new)	Target URL
Old URL	string	URL_Performance.OldURL (new)	Target URL
URL Action	string	URL_Performance.URLAction (new)	Target URL
URL Status	string	URL_Performance.URLStatus (new)	Target URL
Internal Link Remap Status	string	URL_Performance.InternalLinkRemapStatus (new)	Target URL
Keyword/Content Status	string	Keywords.ContentStatus (new)	Keywords
Guide Chapters	string	Chapters.Name	Keywords
Sign Up CVR % (Value)	number	URL_Performance.SignUpCVRValue (new)	Target URL
Sign Up -> Paid Customer % (Value)	number	URL_Performance.SignUpToPaidCustomerValue (new)	Target URL
Target Cluster Strength	string	URL_Performance.TargetClusterStrength (new)	Target URL
Missing N° Pages To Achieve High Strength	number	URL_Performance.MissingPagesForHighStrength (new)	Target URL
Collection Page Status (eCom ONLY)	string	URL_Performance.CollectionPageStatus (new)	Target URL
Year	string	URL_Performance.Year (new)	Target URL
Product Demo Embed (To Keep)	boolean	URL_Performance.ProductDemoEmbedToKeep (new)	Target URL
Written Content (G Doc)	string	URL_Performance.WrittenContentGDoc (new)	Target URL
Frase Link	string	URL_Performance.FraseLink (new)	Target URL
Content Brief Link (G Doc)	string	URL_Performance.ContentBriefLinkGDoc (new)	Target URL
Last Modified By	string	URL_Performance.LastModifiedBy (new)	Clients
SEO Assignee	string	URL_Performance.SEOAssignee (new)	Clients
Content Writer	string	URL_Performance.ContentWriter (new)	Clients
Content Editor	string	URL_Performance.ContentEditor (new)	Clients
Client Contact #1	string	URL_Performance.ClientContact1 (new)	Clients
Client Contact #2	string	URL_Performance.ClientContact2 (new)	Clients
Added In SEO Gets	boolean	URL_Performance.AddedInSEOGets (new)	Clients
Final Word Count	number	URL_Performance.FinalWordCount (new)	Target URL
Estimated Word Count	number	URL_Performance.EstimatedWordCount (new)	Target URL
Due Date (Publication)	date	URL_Performance.DueDatePublication (new)	Target URL
Product Demo Embed Link	string	URL_Performance.ProductDemoEmbedLink (new)	Target URL
Month (Product Demo Creation)	string	Months.Name	Months
BoFu Exchange Target	boolean	URL_Performance.BoFuExchangeTarget (new)	Target URL
Visual #1	string	URL_Performance.Visual1 (new)	Target URL
Visual #2	string	URL_Performance.Visual2 (new)	Target URL
To Redirect To (URL)	string	URL_Performance.ToRedirectToURL (new)	Target URL

Clusters Table

Client Field	Type	Maps To	Entity
Name	string	Clusters.Name	Clusters
Client	string	Clients.Name	Clients
Current Number Of Pages In Cluster	number	Clusters.CurrentNumberOfPages	Clusters
Target N° Of Pages For High Strength	number	Clusters.TargetPagesForHighStrength	Clusters
Target N° Of Pages For Medium Strength	number	Clusters.TargetPagesForMediumStrength	Clusters
Target N° Of Pages For Low Strength	number	Clusters.TargetPagesForLowStrength	Clusters
Target Cluster Strength	string	Clusters.TargetClusterStrength	Clusters
Missing N° Pages To Achieve High Strength	number	Clusters.MissingPagesForHighStrength	Clusters
Growth Model copy	string	Clusters.GrowthModelCopy	Clusters
Growth Model	string	Clusters.GrowthModel	Keywords

CVR% Metrics Table

Client Field	Type	Maps To	Entity
Name	string	CVR_Metrics.Name	CVR Metrics
Clients	string	Clients.Name	Clients
Value	number	CVR_Metrics.Value	CVR Metrics
Metric Type	string	CVR_Metrics.MetricType	CVR Metrics
Signup Type	string	CVR_Metrics.SignupType	CVR Metrics
Growth Model	string	CVR_Metrics.GrowthModel	Keywords

Guide Chapter Table

Client Field	Type	Maps To	Entity
Client	string	Clients.Name	Chapters
Notes	text	Chapters.Notes	Chapters
Keywords	string	Keywords.Keyword	Chapters

Target Personas Table

Client Field	Type	Maps To	Entity
Notes	text	Persona.Notes	Persona
Client	string	Clients.Name	Clients
Linked Assets	string	Persona.LinkedAssets	Persona
Growth Model	string	Persona.GrowthModel	Keyword

Client Specific Tags Table

Client Field	Type	Maps To	Entity
client	string	Clients.Name	Clients
Notes	text	ClientTags.Notes	ClientTags
Growth Model	string	ClientTags.GrowthModel	ClientTags

Clients Table

Client Field	Type	Maps To	Entity
Name	string	Clients.Name	Clients
General Status	string	Clients.GeneralStatus	Clients
Contact #1 Name	string	Contacts.Name	Contacts
Contact #1 Email	string	Contacts.Email	Contacts
Services	string	Clients.Services	Clients
Project Portal 🔗	string	Clients.ProjectPortalLink	Clients
Growth Model	string	Clients.GrowthModel	Keywords
RAW Metrics	string	Clients.RAWMetrics	Clients
Clusters	string	Clusters.Name	Clients
CRO/UX Audit	string	Clients.CROUXAudit	Clients
Seed Keywords	string	Clients.SeedKeywords	Clients
Competitors	string	Competitors.Name	Competitors
Growth Model copy	string	Clients.GrowthModelCopy	Clients
Target Personas	string	Personas.Name	Personas
Trial	boolean	Clients.Trial	Clients
Specific Tags	string	ClientTags.Name	Clients
Guide Chapter	string	Chapters.Name	Clients
WQA Action Points	string	Clients.WQAActionPoints	Clients
MoM Projection	string	Months.Name	Months
KPIs	string	KPI_Metrics.MetricName	Months
Target KPIs copy	string	Clients.TargetKPIsCopy	Clients
Target KPIs copy	string	Clients.TargetKPIsCopy	Clients

Target KPI's Table

Client Field	Type	Maps To	Entity
Name	string	Target_KPIs.Name	Target KPIs
KPI Type	string	Target_KPIs.KPIType	Target KPIs
KPI Timestamp	date	Target_KPIs.KPITimestamp	Target KPIs
Target KPI Value	number	Target_KPIs.TargetKPIValue	Target KPIs
KPI Status	string	Target_KPIs.KPIStatus	Target KPIs
1st Owner Of KPI	string	Target_KPIs.FirstOwnerOfKPI	Target KPIs
2nd Owner Of KPI copy	string	Target_KPIs.SecondOwnerOfKPI	Target KPIs
Month	string	Months.Name	Months
Year	string	Years.Name	Years
Quarter	string	Quarters.Name	Quarters
Notes	text	Target_KPIs.Notes	Target KPIs
Client	string	Clients.Name	Clients
Archived	boolean	Target_KPIs.Archived	Target KPIs
KPI Value (January)	number	Target_KPIs.KPIValueJanuary	Target KPIs
Current KPI Values	number	Target_KPIs.CurrentKPIValues	Target KPIs

New Tables Required

1. CVR_Metrics - For tracking conversion rate metrics
2. Chapters - For organizing content by chapters or guides
3. Personas - For tracking target audience personas
4. ClientTags - For client-specific tagging
5. Competitors - For tracking competitor information
6. Years - For year references
7. Quarters - For quarter references
8. Contacts - For client contact information

Next Steps

1. Create Airtable CSV templates for the new tables
2. Update existing table templates with new fields
3. Update the application code to handle the new data structure
4. Create or update UI components to display the new data