At one point or another, we may find ourselves swamped in work. This was me a couple of weeks ago on a particularly busy Wednesday: running on 4 hours of sleep and my fourth cup of tea as the clock ticked toward 5pm, with two client weekly performance reports still staring me in the face from my ‘to do’ list.

I strapped down, and did things the manual, old-fashioned way, which no doubt most are familiar with—by fiddling with filters in my dynamic Excel file and typing the figures into a Word document.

I found myself saying, “I wish these bullets would just write themselves…” and then I thought, “Well… why not?” After all, 1) the weekly report was already dynamic, so why can’t the bullets based on the report also be dynamic, and 2) due to the goals of the client, the text by and large stays unchanged week to week, and pretty much only the numbers and the actual analysis are what is different.

And thus the idea of automated bullets was conceived. I quickly scrapped together an idea, and within 30 minutes of befuddling myself with Excel formulas, spacing, misplaced commas and miscounted parentheses, I had the beginnings of what I knew could be a great timesaver. I invested about an hour to customize my formulas to mimic the layout of my performance notes, and by the Friday of that week, I was done. In the next few days came the test runs with actual client reports. On Monday I was astonished to find that in just 20 minutes I accomplished what normally took me 2-3 hours to laboriously and painstakingly type.

What I mean by automating the writing of performance bullets is that you will write these bullets once—and never have to write them again. What you are seeing here are the results of my past week of labor: performance notes that will automatically update based on data in your weekly report, and will continue to update each time you change the data. Imagine notes that literally write themselves. Ready to save yourself buckets of time?

Here’s a quick tutorial.

Here are the primary Excel functions I used, and a short description of what each one does:

 

CONCATENATE – Fuses strings of text together

TEXT – Formats values into text. “0” denotes a digit that must appear, and “#” denotes a digit that is optional.

IF – Does one thing if a statement is true, or another if a statement is false

ISNUMBER – Takes an input and outputs true if input is a number, and false if not a number

SEARCH – Searches for a string or value, and outputs the number position to denote where the match is located if a match is found

 

Now that you are equipped with these handy functions, let’s walk through a generic example, and you’ll see how you can apply this to your own reporting process.

For this example, I will reference the Week over Week table in our 3Q Digital Dynamic Reporting Template using these functions to output what will be coherent performance bullets, which looks like this:

wow

However, any table containing weekly data and week over week changes, such as a pivot table, will also do the trick.

So let’s say your bullets normally go something like this:

“-US Brand Search WoW conversions have decreased by -11.1% totaling 1,111 for the week, cost has decreased by -11.1% to $11,111, and CPA has increased by 11% to $11.11.

-US Non Brand Search WoW conversions have decreased by -22% totaling 2,222 for the week, cost has decreased by -22% to $22,222, and CPA has increased by 22% to $22.22.”

And so on and so forth.

Here’s what would go into a cell to recreate the above bullets. I have broken it out into digestible chunks for greater readability. Any “ “ just means insert a space in between the separate strings.

 

=CONCATENATE(

Cell containing value for Geo, “ ” ,

Cell containing value for Targeting, “ “ ,

Cell containing value for Media Type, “ “,

“WoW conversions have”,

IF( ISNUMBER ( SEARCH (“-“, Cell containing the delta value for conversions)), “ decreased “, “ increased “),

“by” ,

TEXT (Cell containing delta value for conversions, “0.0%”),

“ totaling “,

TEXT (Cell containing most recent week’s conversions, “###,###”),

“ for the week, cost has ”

IF( ISNUMBER ( SEARCH (“-“, Cell containing the delta value for cost)), “ decreased “, “ increased “),

“by ”,

TEXT (Cell containing delta value for cost, “0.0%”),

“to ”,

TEXT (Cell containing most recent week’s cost, “$###,###”),

“ , and CPA has “,

IF( ISNUMBER ( SEARCH (“-“, Cell containing the delta value for CPA)), “ decreased “, “increased “),

“by “,

TEXT(Cell containing the delta value for CPA, “0.0%”),

“ to ”,

TEXT(Cell containing most recent week’s CPA, “$##.00”)

“.” )

 

Feeling overwhelmed? I sure was after writing this.

Here’s a breakdown of what this really says:

 

CONCATENATE () – Fuse together these strings of words

TEXT () – Format these values that are not words into strings (so I can fuse them with other strings) and make them look a certain way (ex. 11.1% versus $0.11)

IF (ISNUMBER (SEARCH (“-“,Cell containing delta value)) , “decreased”, “increased”) – If you find a negative sign in the delta value, say “decreased”, and if you don’t see a negative sign in the delta value, say “increased”.

Finally, here’s an example of what this will look like in Excel:

The formula refers to the Week over Week tab shown below:

wow2

And gives this output:

wow3

One thing to note is that if any cells are merged, that is, if you click onto a cell and it gives you a range as a reference (E7:F7 for example), then just use the first cell in the range rather than the entire range. This is because the value isn’t actually in all of the cells, but rather in the very first cell.

If you have multiple bullets to write, here’s a friendly tip: changing the filters in the Week over Week tab will cause your bullets to refresh with the new data that is displayed (and so will changing the filters in a pivot table. You can then copy and paste the data into a Word document as text, and voila! You have a complete set of performance bullets. You can add formatting and further analysis afterward if you like. Each week you can input your refreshed data, and the bullet will automatically update. How cool is that?

And that’s it! Just make sure you keep track of your commas and parentheses while writing the formula, and everything will be fine. Feel free to edit the formula and experiment as you please. They are, after all, your bullets!

Happy automating!

1 Comment

  1. bryan July 25th, 2016

    Excellent article; it was very clear and well put together. Please do write more on this topic!

Leave a Comment

Anna Shen
Anna has been an Account Associate at 3Q Digital since August 2014. She has a background in public policy, inferential statistics, and data analytics. In her spare time, she enjoys learning, creating new solutions, and optimizing absolutely everything.