Stop Headaches With Merged Data Content Scoring

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Softie

Slide 3

Slide 3 text

Find all the stuff Reach out any time https://ianlurie.me/engage2024

Slide 4

Slide 4 text

History major. Law degree.

Slide 5

Slide 5 text

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

“Interesting”

Slide 8

Slide 8 text

Content marketing Analytics

Slide 9

Slide 9 text

A brief intro Three kinds of content (they all matter)

Slide 10

Slide 10 text

Commercial content

Slide 11

Slide 11 text

We can have AI write our product descriptions!

Slide 12

Slide 12 text

Transactional content

Slide 13

Slide 13 text

Just use “click here”

Slide 14

Slide 14 text

Informational content

Slide 15

Slide 15 text

Our informational content doesn’t generate sales. Shut down the project.

Slide 16

Slide 16 text

I have to justify the cost. We can’t measure this. No more content.

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Traffic is falling!!! Revenue is down!!! Why?!!!

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

Gotcha covered.

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

Combine performance metrics & properties

Slide 25

Slide 25 text

For example: Reading difficulty, internal authority, page authority, time on page, conversion rate

Slide 26

Slide 26 text

That data’s all over the place!!! Augh!!!!

Slide 27

Slide 27 text

Yeah, but it’s all attached to URLs We can merge it!

Slide 28

Slide 28 text

Time for the power of databases and sheets

Slide 29

Slide 29 text

The basic requirements - Screamingfrog spider - Google search console access - Google analytics access - Pagespeed insights

Slide 30

Slide 30 text

Getting fancy - Moz/majestic/similar - Openai/chatgpt - Sql knowledge (for huge sites) - A little javascript

Slide 31

Slide 31 text

Skills - Screamingfrog - Google sheets - Sql (optional)

Slide 32

Slide 32 text

Steps 1. Crawl and segment 2. Grab other data 3. Merge 4. Score 5. test and apply

Slide 33

Slide 33 text

Step 1 crawl & segment

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

No content

Slide 37

Slide 37 text

Need to exclude this from “content”

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

No content

Slide 41

Slide 41 text

Create some segments

Slide 42

Slide 42 text

No content

Slide 43

Slide 43 text

No content

Slide 44

Slide 44 text

At some point you’ll need to merge data from other sources. No problem!

Slide 45

Slide 45 text

Step 2 gather data

Slide 46

Slide 46 text

Step 2a Get GSC data

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

Need more data? Search Analytics For Sheets https://ianlurie.me/search-analytics

Slide 49

Slide 49 text

Step 2B core web vitals data

Slide 50

Slide 50 text

*Written by Claude AI, so don’t blame me

Slide 51

Slide 51 text

For this example, this is all we need

Slide 52

Slide 52 text

https://ianlurie.me/pagespeed-sheet (Also here)

Slide 53

Slide 53 text

Step 2C Get analytics data

Slide 54

Slide 54 text

I use unique page views, but it’s up to you Not just from organic search!!!!

Slide 55

Slide 55 text

And so on

Slide 56

Slide 56 text

Calls generated Nav/other clicks Scroll depth Video plays Video play length

Slide 57

Slide 57 text

Step 2D One must-have calculated field

Slide 58

Slide 58 text

Time on page vs. estimated read time

Slide 59

Slide 59 text

niram.org/read

Slide 60

Slide 60 text

Step 3 merge

Slide 61

Slide 61 text

What does all this data have in common?

Slide 62

Slide 62 text

Urls!

Slide 63

Slide 63 text

=vlookup([url cell],sheet1!A3:C32,1,FALSE) VLOOKUP!!!!

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

No content

Slide 66

Slide 66 text

Step 4 score

Slide 67

Slide 67 text

Percentile rank using weighted scores for each metric.

Slide 68

Slide 68 text

=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) ))

Slide 69

Slide 69 text

No content

Slide 70

Slide 70 text

ianlurie.me/scoring (Also here)

Slide 71

Slide 71 text

No content

Slide 72

Slide 72 text

Assign weights to your metrics

Slide 73

Slide 73 text

I favor these

Slide 74

Slide 74 text

“All this to tell me there’s no set scoring system? C’mere…”

Slide 75

Slide 75 text

Score!

Slide 76

Slide 76 text

Step 5 test & apply the model

Slide 77

Slide 77 text

Go for a distribution. You’re grading on a curve.

Slide 78

Slide 78 text

Move content this way

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

The lesson

Slide 81

Slide 81 text

No content

Slide 82

Slide 82 text

No content

Slide 83

Slide 83 text

Time to act

Slide 84

Slide 84 text

Monitor Repurpose Rewrite Prune Relocate Update Roll up

Slide 85

Slide 85 text

This content is embarrassing. I’m gonna remove it.

Slide 86

Slide 86 text

Before you do that, take a closer look. If it’s decent content, maybe another channel will do? Email? Social?

Slide 87

Slide 87 text

Or maybe it’s old, but it used to perform. So update it.

Slide 88

Slide 88 text

This is solid stuff Add it to the list for later updates Or update it now in your spare time

Slide 89

Slide 89 text

Wow. We should repurpose this.

Slide 90

Slide 90 text

(Pssst) Use ScreamingFrog, ChatGPT to find related content at scale https://ianlurie.me/ipullrank-embeddings Mike King (use with caution, because AI)

Slide 91

Slide 91 text

Three related, solid performers Maybe a rollup

Slide 92

Slide 92 text

Think big

Slide 93

Slide 93 text

Score for specific segments

Slide 94

Slide 94 text

Score zero-click content https://ianlurie.me/amanda-zero-click Amanda Natividad

Slide 95

Slide 95 text

Email performance social posts Score by content type

Slide 96

Slide 96 text

No content

Slide 97

Slide 97 text

Cut the budget. We don’t need this any more!!!!

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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