Data

1024 682 Sophie Howarth

How important will data be in digital PR in 2022?

‘Data’ — say this word to a creative person, and they’ll probably run straight for the hills. But when it comes to digital PR campaigns, some of the most creative concepts start with data. Equally, if you can’t find the correct data to backup your campaign idea, back to the drawing board you go. As a digital PR agency that excels in delivering uniquely creative, data-led campaigns, we’re always thinking about data. Where to find it… new ways we can present it… how to shape it to suit our campaigns… the list goes on.

2021 saw JBH expand our data team to facilitate the sheer amount of research that goes into crafting dependable data-led digital PR campaigns, and we’re excited to grow our team even more throughout the course of this year. With these changes, along with the increase of data-led campaigns throughout the whole digital PR industry, we couldn’t help but wonder… just how important will data in digital PR be in 2022?

We sat down with our data team — Tori and Cindy — and JBH Digital PR Director, Rebecca, to find out.

(Image source: https://www.pinterest.co.uk/pin/46936021095341914/)


Before we look ahead to the future of data in digital PR, let’s look back at this past year. How important was data to your campaigns throughout 2021?

T: Data has stood at the forefront of most of our campaigns this year for one reason — nothing beats a shocking stat. Journalists eat them up, and audiences flock to them. Whether the data comes first or last, it’s the bread and butter of a good campaign.

C: Data-led campaigns have made up much of our output as they’ve  given us extra angles to dig into — as well as increased validation. With a good set of data, readers have the chance to consider things they may not have thought of before.

R: When I’m thinking about campaigns for clients, I’ll often start the idea generation process using data. I find it much easier to take a data set — or group of data sets — and use them as a springboard for my idea. I see many campaigns where data has obviously been the afterthought — retrofitting data sources to suit a campaign idea — whereas I prefer to work the opposite way, letting data dictate my idea. Data-led ideas have been vital to the success of our award-winning campaigns for 2021. 


What have been your biggest data mistakes in data-led campaigns, and how have you learnt from them?

T: My biggest mistake has been not trusting my gut. If something doesn’t look right or make sense, check it and then discuss it with others. No matter how credible and current your sources, they can still be wrong — so use multiple. If something does seem off, do further research into what’s causing it — you may land yourself extra angles.

C: My biggest mistakes have been not extensively checking for many more sources in order to make sure the data is correct. You can never do too much research, and it’s always best to have checked extensively to ensure you’re getting similar results from your sources. 

R: Sense checking the data. You can have as many credible data sources as you like, but if it doesn’t tick the ‘common sense’ box then you’ll really struggle to get cut through — plus, you might even cause journalists or commentators to pick your data apart. We’ve definitely learned to bake a ‘common sense check’ into our data QA process.


Let’s talk sources, methodologies… and dodgy data. What do you think makes data-led campaigns stronger?

T: I think the data is almost as important as the idea. Without strong data, the campaign will not go far. For me, creativity must go beyond ideation — the data must also be captivating. The campaigns we see dominating publications have creative methodologies, that’s what makes them strong.

C: I think what makes  a strong data-led campaign is using strong sources — as well as providing a lot of data in order to further back up what the campaign is saying. When the time and effort used to collect the data shines through the methodology, the campaign becomes even more unique and stands out from the crowd.

R: The hook and the top line. We should always be asking ourselves “What is the story?”, “Why would a journalist click on our email?”, and “Why would a reader read the story?”. These are the three key things I think about when bringing together a data-led campaign. 


Right now, what do you think is the most underused data source when it comes to data-led campaigns? In other words, what can you tap into? 

T: API’s are incredibly useful, and speed things up massively. Tapping directly into a site’s data is a game changing tactic — unfortunately, many of them have restrictions for media use, which is why they don’t appear too often.

C: I think while social media is a largely used data source, there’s the potential for us to collect even more data from these platforms due to their large number of users. There’s tons of information that can be gathered in many unique ways — offering us new angles in the process.

R: Most brands are already sitting on a goldmine of data. From sales data through to anonymised customer information, we’ve had fantastic success using internal client data to predict and comment on pop-culture trends. However, we are sensitive to the fact that brands in highly competitive niches might have reservations around sharing sensitive data. But there are ways around this and as long as we are careful with how and what we present, this data can lead to a hyper-relevant coverage and links for brands in a whole range of verticals.

 

Finally, in digital PR, data is one of the most powerful projection tools we have. What are your biggest projections for the way we’ll use data in 2022?

T: I think data will continue to be used to drive links, but the methodologies will adapt and become more complex, and campaigns more unique. I think the current space is dominated by trending data sources such as search volumes — and this will be replaced with more creative sources, and thus distinctive ideas.

C: I think there will be more innovative ways of collecting data as platforms adapt their services to provide even more material —  allowing us to think even more outside the box when approaching campaign ideation.

R: The data we share will come under more scrutiny — along with the insights we draw from it. As an industry, we need to tighten up on how and where we source data to ensure we’re presenting the truth. Over the last twelve months, JBH has been working hard on this — putting processes in place and seeking external training to ensure the data presented is as robust as possible.

To summarise, when planning your digital PR campaign, don’t forget to fall in love with the data first. More often than not, weaker campaigns are the ones in which data has clearly been an afterthought, and you’ll find that it’s far too easy for journalists to poke holes in methodology that’s been borne of an idea, rather than the other way around. Leading with the data gives you an immediate upper hand — automatically eliminating the “What if there’s no data to back it up?” dread, and consequently saving valuable time and energy for you to channel into campaign angles and outreach strategies.

The best digital PR campaigns are the ones that intrigue… fascinate… and scandalize — and as we discussed above, these reactions tend to be triggered by a shocking stat. What’s more, unlike other typically attention-grabbing content, your data doesn’t have to be a one-hit wonder. If the methodology and its resulting materials are strong, you can always repurpose old data for new campaigns. After all, they say nothing lasts forever; dreams change, trends come and go, but a solid dataset never goes out of style. 

1000 666 Tori Alanis-Saunders

50 Google Sheet Formulas Every Digital PR Should Know

When you think of digital PR you may not think of spreadsheets, however, they are the bread and butter of any campaign.

Whether you’re using spreadsheets as part of a data-led digital PR campaign or simply as a way to track performance, knowing your way around them can really level-up your output.

Not every PR is a data whiz, this is where formulae can come in handy to automate and speed up the process. That’s why we have created the ultimate guide to Google sheet formulas to help maximise the stories that lie within your spreadsheets.

Ultimate Guide to Google Sheets Formulas for Digital PR’s


Formulas for Finance

From currency conversion to the stock market, Google Sheets has a whole range of formulas for you to explore.

The possibilities are endless when it comes to converting currencies. If your campaign isn’t landing in one country, why not try converting to another currency and re-pitching? You might be able to tap into a whole new audience.

1. Currency conversion: =A2*GOOGLEFINANCE(“CURRENCY:USDGBP”)
A great trick when working with money-related data, Google Finance’s currency conversion automatically fetches the most up-to-date conversion rates.

 

2. Companies current stock price: =GOOGLEFINANCE(“Ticker”, “price”)
Working with stocks? This trick allows you to fetch up-to-date stock prices for any company on the stock market.

3. Companies highest stock price in last year: =GOOGLEFINANCE(“Ticker”, “high52”)
If you want to know a company’s highest stock price over the course of the last year, use “high52”.

4. Companies lowest stock price in last year: =GOOGLEFINANCE(“Ticker”, “low52”)
If you want to know a company’s lowest stock price over the course of the last year, use “high52”


Formulas for text formatting

There’s nothing worse than copying and pasting some text into your spreadsheet to find that it’s all in capital letters. With these formulas you can automatically change formatting with a press of a button.

5. Anchor text: =$A$1
Anchoring cells allow you to drag across formulas without altering the cells used for the formula.

6. Capitalise first letter: =proper(A1)
If you’re working with names or places, or have simply been zooming through your data you might have forgotten a capital letter – this trick can automatically capitalise the first letter.

7. Google translate: =GOOGLETRANSLATE(A2, “en”,”es”)
A helpful tool when creating a global campaign, allowing mass translations to happen at the click of a button

 

8. All uppercase letters: =UPPER(A1)
This formula allows you to change all text in selected cells to uppercase.

9. All lowercase letters: =LOWER(A1)
This formula allows you to change all text in selected cells to lowercase.

10. Valid email checker: =ISEMAIL(value)
This trick is perfect for building media lists to prevent outreaching to dead emails

11. Convert number to text: =TEXT(number, format)
This formula will automatically convert any numeric values to written text.

12. Date: =DATE(year, month, day)
Using this formula, Google Sheets will automatically format the dates you’re working with so all dates are fluent.

13. Date if: =DATEDIF(start_date, end_date, unit)
If you need to find the number of days, weeks or months between two dates – this handy formula will work it out for you.

 

14. Date value: =DATEVALUE(date_string)
This formula changes a date string in a known format into Sheet’s accepted date format, this is great if you’re working with Americanised dates.

15. Current date/time: =NOW()
If you need to insert today’s date and/or time into your sheet – this formula will do it automatically without a calendar.

16. Detect language: =DETECTLANGUAGE(text_or_range)
If you’re working with other languages, this formula will detect which language is in a selected cell, allowing you to translate it with ease.

17. Valid URL checker: =ISURL(value)
Perfect for when adding hyperlinks or creating source lists, this formula checks if the link you’ve added is valid.

18. Count Characters in a cell: =LEN(cell)
Brushing up on your social media skills? This formula is perfect for planning out tweets so you can stick to the character limit.


Formulas for common calculations

You don’t have to be a maths expert to be able to use these formulas to generate new angles for your campaigns. Whether you’re creating a ranking index table or trying to find the average; put your phone calculator down and harness the power of Google Sheets.

19. Count: =COUNT(value1, [value2, …])
If you want to see how many cells are in your worksheet, or in a selected range – this formula will count them for you!

20. Count if: =COUNTIF(range, criterion)
If you want to count values in a selected range of cells based on specific criteria, this formula will do it automatically.

21. Percentage increase or decrease: =(New value – Old value) / Old value
Looking for a percentage? This formula allows you to find the percentage increase or decrease of data in your spreadsheet.

 

22. Percentage finder: =Part/Total
If you need to find the percentage of a value, this formula does it automatically for you.

23. Mean average: =average(A1:A10)
This trick fetches the average for a selected range of cells.

24. Median average: =median(A1:A10)
This formula automatically fetches the median average for a selected range of cells.

25. Vlookup: =VLOOKUP(A2,A:M,7,”FALSE”)
This famous formula looks up data from a table organized vertically, returning either exact or partial matches.

26. Hlookup: =HLOOKUP(search_key, range, index, [is_sorted])
Similar to a =VLookup, a =HLookup instead locates data from a table that is organised horizontally.

27. Correlation: =CORREL(data_y, data_x)
Great for when you’re working with statistics, =CORREL calculates the Pearson correlation coefficient between two sets of variable data and tells you whether there is a negative or positive correlation.

28. Net working days: =NETWORKDAYS(start_date, end_date, [holidays])
This formula is handy for working out your remaining holidays – =NETWORKDAYS returns how many whole working days between the specified start and end dates, excluding weekends and identified holidays.

 

29. Find unique data: =UNIQUE(range)
If you’re working with, or cleaning data and need to remove any duplicates – Google’s ‘=Unique’ formula pulls any unique data from a selected range.

30. Count the number of unique values: =COUNTUNIQUE(value1, [value2, …])
If you need to know how many unique values you have in your dataset, this formula will do that for you automatically.

 

31. Divide: =value1/value2
This formula allows you to divide values.

32. Sum: =sum(value1:value50)
This formula lets you add up all the values in the selected cells.

33. Max value: =MAX(value1, [value2, …])
This formula will find the maximum value in a selected range.

34. Min value: =MIN(value1, [value2, …])
This formula will find the minimum value in a selected range.

35. Mode: =MODE(value1, [value2, …])
The =MODE formula will fetch the most commonly occurring value within a selected range.

36. Rank: =RANK(value, data,0/1)
This formula ranks data from ascending or descending from a selected range of data.

 

37. Rank Average: =RANK.AVG(value, data, [is_ascending])
If the data you’re trying to rank contains duplicates, by using =RANK.AVG instead of =RANK will create an average for those duplicated cells.

38. Fixed number of decimal places: =FIXED(number, [number_of_places], [suppress_separator])
If your decimal points are all over the place, this formula will automatically round them to the specified number of decimal places.

39. Import data from URL: =IMPORTDATA(url)
=IMPORTDATA imports data from a given URL into .CSV or .TSV formats – this is great if you’re working with large data sets

40. Import data from table or list within a HTML page: =IMPORTHTML(url, query, index)
=IMPORTHTML automatically imports data into your spreadsheet from either a table or list on a HTML page.

41. Import range of cells from another spreadsheet: =IMPORTRANGE(spreadsheet_url, range_string)
This formula allows you to import a range of data from one spreadsheet to another.

42. If error: =IFERROR(original_formula, value_if_error)
If error messages such as #DIV/0 are overruling up your sheet, the =IFERROR function allows you to return either a different message or empty cell rather than the dreaded #DIV/0 sign

43. Array: =ARRAYFORMULA(array_formula)
=ARRAYFORMULA outputs a range of cells, whereas a regular formula such as ‘=sum’ would only output a single value.

44. If a condition is true or false: =IF(condition, “YES”, “NO”)
This formula performs a test on a value in a specific cell, providing different results – such as ‘Yes’ or ‘No, based on whether the data is true or false.

45. Search Function: =IF(SEARCH(“search phrase”,cell), “YES”)
Looking for a cell that says something specific, this formula will fetch it for you automatically – rather than spending ages sifting through your dataset.

46. Create Single-cell chart: =SPARKLINE(A1:A50,{“charttype”,”column”})
If looking at rows and rows of data is hurting your eyes, this formula whips up a quick single-cell sparkline chart to make visualising data a bit easier.


Formulas for formatting

Quite possibly some of my most-used formulas are based on formatting. From splitting text to columns, to making rows of text into columns (transpose). You’ll save yourself bags of time and speed up your output using these formulas.

47. Split text to columns: =SPLIT(B3,” “)
Great for if you’re working with first and last names in the same cell, =SPLIT will split clearly defined data into separate cells.

 

48. Transpose: =TRANSPOSE(array_or_range)
Is your imported data the wrong way round? =TRANSPOSE can instantly transpose (switch rows, to columns and vice versa) the selected range of data.

49. Merge cells: =CONCATENATE(range)
This formula will automatically merge any cells in the selected range.

50. Sort cells: =SORT(range, sort_column, is_ascending)
The =SORT function allows you to sort rows of a specified range of cells, in either ascending or descending order.


Further resources

Looking for free data sources? We’ve collated 100 free data sources where you can download and try out some of these formulas in real-time!

100 Free Data Sources for Content and Digital PR Campaigns

On the lookout for new data sources? Here are some of the most compelling data sources you can use in your next digital PR campaign.

How to find compelling data for your next digital PR campaign

Want to know what a data journalist thinks about your data? Ashley Kirk, visual projects editor at the Guardian revealed all in our webinar from earlier in the year. Watch again for free, here!