Stop Headaches With Merged Data Content Scoring

Learn how to create a consistent content scoring system that measures content against business performance. Have a look, then contact me if you have any questions.

Transcript

2. Softie
3. Find all the stuff Reach out any time https://ianlurie.me/engage2024
4. History major. Law degree.
5. .
7. “Interesting”
8. Content marketing Analytics
9. A brief intro Three kinds of content (they all matter)
10. Commercial content
11. We can have AI write our product descriptions!
12. Transactional content
13. Just use “click here”
14. Informational content
15. Our informational content doesn’t generate sales. Shut down the project.
16. I have to justify the cost. We can’t measure this. No more content.
17. 0 200000 400000 0 70000 140000 Jan-22 Nov-22 Sep-23 Jul-24 Site Performance, Clients A and B Site A Conversions Site B Conversions Client A Stops Client B Stops
18. Traffic is falling!!! Revenue is down!!! Why?!!!
19. On 70% of projects, stopping or limiting content programs reduced overall performance against business goals.* *Based on client data, and no, I can’t share
21. Gotcha covered.
22. 0 60 120 0 70000 140000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Site Performance vs. Scores, Client A Site A Conversions Site A Scores
23. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Site Performance vs. Scores, Client B Site B Conversions Site B Scores
24. Combine performance metrics & properties
25. For example: Reading difficulty, internal authority, page authority, time on page, conversion rate
26. That data’s all over the place!!! Augh!!!!
27. Yeah, but it’s all attached to URLs We can merge it!
28. Time for the power of databases and sheets
29. The basic requirements - Screamingfrog spider - Google search console access - Google analytics access - Pagespeed insights
30. Getting fancy - Moz/majestic/similar - Openai/chatgpt - Sql knowledge (for huge sites) - A little javascript
31. Skills - Screamingfrog - Google sheets - Sql (optional)
32. Steps 1. Crawl and segment 2. Grab other data 3. Merge 4. Score 5. test and apply
33. Step 1 crawl & segment
37. Need to exclude this from “content”
41. Create some segments
44. At some point you’ll need to merge data from other sources. No problem!
45. Step 2 gather data
46. Step 2a Get GSC data
48. Need more data? Search Analytics For Sheets https://ianlurie.me/search-analytics
49. Step 2B core web vitals data
50. *Written by Claude AI, so don’t blame me
51. For this example, this is all we need
52. https://ianlurie.me/pagespeed-sheet (Also here)
53. Step 2C Get analytics data
54. I use unique page views, but it’s up to you Not just from organic search!!!!
55. And so on
56. Calls generated Nav/other clicks Scroll depth Video plays Video play length
57. Step 2D One must-have calculated field
58. Time on page vs. estimated read time
59. niram.org/read
60. Step 3 merge
61. What does all this data have in common?
62. Urls!
63. =vlookup([url cell],sheet1!A3:C32,1,FALSE) VLOOKUP!!!!
64. search console clicks impressions ctr URL analytics pageviews visits conversions time on page URL properties fk score link score moz pa rendered perc time v read segment URL Or SQL
66. Step 4 score
67. Percentile rank using weighted scores for each metric.
68. =IF(Q79="","",( IFERROR((PERCENTRANK.EXC(C:C,C79) * INDEX(weights!$B$1:$B$99, MATCH("fk score", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(D:D,D79) * INDEX(weights!$B$1:$B$99, MATCH("time on page vs read time", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(E:E,E79) * INDEX(weights!$B$1:$B$99, MATCH("link score", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(F:F,F79) * -1 * INDEX(weights!$B$1:$B$99, MATCH("rendered percent", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(G:G,G79) * INDEX(weights!$B$1:$B$99, MATCH("gsc clicks", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(H:H,H79) * INDEX(weights!$B$1:$B$99, MATCH("gsc impressions", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(I:I,I79) * INDEX(weights!$B$1:$B$99, MATCH("gsc ctr", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(J:J,J79) * INDEX(weights!$B$1:$B$99, MATCH("gsc position", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(K:K,K79) * INDEX(weights!$B$1:$B$99, MATCH("cwv score", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(L:L,L79)*-1 * INDEX(weights!$B$1:$B$99, MATCH("ttfb", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(M:M,M79) * INDEX(weights!$B$1:$B$99, MATCH("moz pa", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(N:N,N79) * INDEX(weights!$B$1:$B$99, MATCH("moz linking domains", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(O:O,O79) * INDEX(weights!$B$1:$B$99, MATCH("semrush keywords", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(P:P,P79) * INDEX(weights!$B$1:$B$99, MATCH("unique pageviews", weights!$A$1:$A$99, 0))),0) + IFERROR((PERCENTRANK.EXC(Q:Q,Q79) * INDEX(weights!$B$1:$B$99, MATCH("conversion rate", weights!$A$1:$A$99, 0))),0) ))
70. ianlurie.me/scoring (Also here)
72. Assign weights to your metrics
73. I favor these
74. “All this to tell me there’s no set scoring system? C’mere…”
75. Score!
76. Step 5 test & apply the model
77. Go for a distribution. You’re grading on a curve.
78. Move content this way
79. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Traffic vs. Scores Site B Visits Site B Scores Score over time Prove the model
80. The lesson
83. Time to act
84. Monitor Repurpose Rewrite Prune Relocate Update Roll up
85. This content is embarrassing. I’m gonna remove it.
86. Before you do that, take a closer look. If it’s decent content, maybe another channel will do? Email? Social?
87. Or maybe it’s old, but it used to perform. So update it.
88. This is solid stuff Add it to the list for later updates Or update it now in your spare time
89. Wow. We should repurpose this.
90. (Pssst) Use ScreamingFrog, ChatGPT to find related content at scale https://ianlurie.me/ipullrank-embeddings Mike King (use with caution, because AI)
91. Three related, solid performers Maybe a rollup
92. Think big
93. Score for specific segments
94. Score zero-click content https://ianlurie.me/amanda-zero-click Amanda Natividad
95. Email performance social posts Score by content type
97. Cut the budget. We don’t need this any more!!!!
98. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Traffic vs. Scores Since You Cut Budget (Again) Site B Conversions Site B Scores
99. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Traffic vs. Scores Since You Changed Your Mind (Again) Site B Conversions Site B Scores
100. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Traffic vs. Scores Since You Reduced Informational Content Efforts Site B Conversions Site B Scores
101. 0 60 120 0 210000 420000 Jan-22 Sep-22 May-23 Jan-24 Sep-24 Traffic vs. Scores Since You Reduced Commercial Content Efforts Site B Conversions Site B Scores
102. Oh, OK, I get it. Here’s more resources
106. Merge your data. Score your content.
107. Moo Deng is kind of A butthead. https://ianlurie.me/engage2024