How To Create A Simple Automated Brand/Generic/Shopping Report In Excel

Whether you do it monthly, weekly or even daily, reporting is something that all us PPC experts and indeed digital marketers in general have to deal with. I always say if there’s something that you are going to do more than once, try to automate it – even if you can’t automate it all. That’s why I’ve decided to put this post together.

If you’re a whiz with Excel, you’ll know that there’s a lot more functionality you can add to your reports to make them more insightful but for the rest of us, I’m going to show you how to create a simple monthly report that splits out your data between brand/generic and shopping. I may build on this report in the future but in the mean-time this is the perfect report to get you started on creating an automated report in Excel.

Don’t worry if you fall behind or if something doesn’t make sense to you because I’ve added the finished report for you to download and use. Before we get started, there are some things to bear in mind with this report:

  • Campaign names need to include Brand/Generic/Shopping (or whatever you want to split your data into).
  • You will still need to dump the updated report data into a particular section in your report. If you want to try to automate this step as well then you may want to look at (the totally awesome) SupermetricsAdwords Automated Reports or Analytics Edge.
  • You’ll also need to drag down the formula in Column A of the ‘Data’ tab (if you want to automate this, then look into using an Excel Table).

Set up the formatting of your report

First you need to visualise what you want your report to look like, the tabs you’re going to have in the report and the data that you want to report on. For the purposes of this post, I’ve set up a tab for ‘Brand’, ‘Generic’, ‘Shopping’ and ‘All’. You’ll also need a ‘Data’ tab which is going to include all of your raw data.

Screen Shot 2017-04-24 at 21.55.36

Now create the tables and formatting that you want to use in your report. The columns and formatting you set up will ultimately depend on your individual requirements but I’ve created a basic example below.

Screen Shot 2017-04-24 at 22.09.07

Now that we have the formatting right, we can start building out the automation for our report!

Set up the ‘Data’ tab

Starting from Column B, add in the column headings for the raw data that you’re going to be pulling. You need the ‘date’, ‘Campaign’ and then any additional attributes that you want to report on. Note that you don’t need performance related data such as CTR, CVR or CPC as we can calculate these with the existing data in the reporting tabs. Seeing as though this is a monthly report, you’ll want to segment your data by month. You can now go ahead and add in the data within the appropriate columns, in the ‘Data’ tab.

OK, so now that we have the shell of our report all set up and the backend data present, we can start adding in our formulas. In Column A We’re going to add in a helper column that works out whether the campaign is a Brand, Generic or Shopping campaign. To do this, we’re going to combine an IF statement with a SEARCH function and the ISNUMBER function. Basically the below formula is saying search for the word Brand/Generic/Shopping in the campaign name and if you find it, then say Brand/Generic/Shopping, otherwise say ‘problem’. I like to add in something that appears if none of the conditions have been met because it helps to diagnose problems find and errors.

IF(ISNUMBER(SEARCH(“Shopping”,C3))=TRUE,”Shopping”,“Problem”)

To find the Brand, Generic or Shopping text in the campaign names, we can nest the above formula as many times as we need and create something that looks like the below. If you’re looking at the below formula and feeling like running for the hills, then there really is no need. If you inspect the formula, you’ll notice that to add a new search criteria, all I’ve done is replaced the part of the above formula that says “Problem” with the above formula. I’ve also replaced the word ‘shopping’ with the second search criteria. We can do this as many times as we need to and add in other groupings such as Display, Remarketing, DSA etc. We could even have groupings for Men’s, Women’s and Kid’s or Jumpers, jeans, skirts and dresses etc which means we can segment our data in so many different ways.

IF(ISNUMBER(SEARCH(“Brand”,C3))=TRUE,”Brand”,IF(ISNUMBER(SEARCH(“Generic”,C3))=TRUE,”Generic”,IF(ISNUMBER(SEARCH(“Shopping”,C3))=TRUE,”Shopping”,“Problem”)))

Now drag the formula down to every column that has some data in it. Adding in the data and dragging down the formula is the only manual step that will be required. If you’ve done everything correctly, you should have something that looks like the below:

Screen Shot 2017-04-24 at 22.45.28

 

Almost Done – Using SUMIFS to retrieve your data

Well done for making it this far, you’re nearly done! We just need to populate our tables now. Filling in the ‘All’ tab is easier than the other tabs so we will start with this one. It requires adding in SUMIF formulas that add up all the data where the date column matches the date in the our table. Here is an example of what we would include in the SUMIF formula as well as a completed example for the ‘Brand’ tab

SUMIF(Data!B:B,All!B6,Data!D:D)

SUMIF(Date Column in Data tab,specific date in reporting tab,data being summed up from data tab)

We need to match up two sets of criteria for the remaining tabs which is why we will be using a SUMIFS formula. Below is the formatting that we will be using in our SUMIFS formula:

SUMIFS(Data!D:D,Data!B:B,All!B6,Data!A:A,“Brand”)

SUMIFS(Data being summed up by the data tab,Date Column in Data tab,specific date in reporting tab,Grouping column from Data tab,The grouping name that you want to look for)

You’ll need to do this for all the data columns in your ‘Data’ tab. SO for in this example, we need to use the SUMIF and SUMIFS formulas to retrieve the ‘impressions’, ‘clicks’, ‘cost’ and ‘conversions’ from the ‘Data’ tab

Final Easy Step – Add in remaining metrics

Now that you’ve retrieved your data and presented it in a format that you’re happy with, you can add in any existing formulas that you may require. Some examples of metrics that you may want to add in are:

  • CTR – IFERROR(Clicks/Impressions,”0″)
  • CPC – IFERROR(Cost/Clicks,”0″)
  • CVR – IFERROR(Conversions/Clicks,”0″)
  • ROAS – IFERROR(Revenue/Cost,”0″)

Well done! You’re all set up now! All you need to do now is to paste your data into the Data tab whenever you update your report and drag down the grouping formula. Oh, make sure you hide the ‘Data’ tab to make the report look tidier. This is just a starting point and there’s a lot more that can be done with this but it’s enough to get you started on creating a killer report in Excel and saving yourself loads of reporting annoying reporting time.

You can download a completed example of the report here: Basic Automated Reporting Example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: