How to use Google Sheets to count advertising inventory easily

In the last blog post, I went over how to create a Google Forms document to track sales for a print magazine. Today, I will show you what you can do with the data collected via the Forms. 

Related post: How to use Google Forms to track your sales easily

Form responses

So, if you connected your Forms document to a new Google Sheets spreadsheet as detailed in the last post, your spreadsheet should have a worksheet called Form responses 1 that looks something like this below:

Image 1

Image 1

Now I can use the data to figure out:

  • How many ads are sold and how many more need to be sold for each issue.
  • Who bought an ad(s) for which issue(s) and for how much. The info will be used to prepare invoices, and to connect with accounts who haven't bought from us in a while.

Counting advertising inventory

I needed a quick way to figure out how many ad spaces we sold and how many still need to be sold. What I wanted was a graphic representation of how many spaces are available based on current sales status. But unfortunately, I'm lousy at coding and math. So I came up with a crude solution

The magazine offers the following ad sizes (and weight for each size in the bracket): 

  • 2-page spread (12)
  • full page (6)
  • 2/3 page (4)
  • 1/2 page (3)
  • 1/3 page (2)
  • 1/4 page (1.5)
  • 1/6 page (1)
  • Inside back (no weight assigned)
  • Inside front (no weight assigned)
  • Outside cover (no weight assigned)
Image 2

Image 2

Basically, I divided each page as having six ad spaces. After conversing with the chief editor of the magazine, I was able to find out that we have about 74 ad spaces (12.3 pages) plus the three static pages (inside & outside back, outside cover).

All I need to do now is to sum up all the ad spaces sold based on the weight assigned, and divide them by 74 to see the percentage of sales goal met. 

But not so fast. Remember the ad size data are collected in the text format [image 2] meaning, they can't be added up. I need to convert them into number format first. 

Writing your own function

*Disclaimer*
I have very limited coding skills, and I'm self-taught. By using my codes, you're assuming any risks that go with them. 

OK, now, Google Sheets, like other spreadsheet programs, come with a set of standard formula/functions such as "today()". And you can also create a custom formula. Specifically, I need a formula to convert ad size sold to its corresponding weight. 

To create a custom formula, open your spreadsheet and select Script editor under Tools menu. A new window opens up. 

Image 3

Image 3

Select all of the text and delete. Now copy the following script and paste it into the area. 


function inventory(issue) {
    var area = 0 ;
    switch (issue) {
        case "Outside cover":
            area = "Outside Cover";
            break;
        case "Inside Front":
            area = "Inside Front";
            break;
         case "Inside Back":
            area = "Inside Back";
            break;
         case "2 Page Spread":
            area = 12;
            break;
         case "Full Page":
            area = 6;
            break;
         case "2/3 v page":
            area = "2/3 v page";
            break;
         case "2/3 h page":
            area = 4;
            break;
         case "1/2 page":
            area = 3;
            break;
         case "1/3 v page":
            area = 2;
            break;
         case "1/3 h page":
            area = 2;
            break;
         case "1/4 page":
            area = 1.5;
            break;
         case "1/6 page":
            area = 1;
            break;
         case " ":
            area = 0;
            break;
         default:
            area = 0;
   }
  return (area);
}


Click on the disc icon, and save the script as inventory [image 4 below]

Image 4

Image 4

Now run the script by clicking on the ">" icon.

Image 5

Image 5

To write the script, I used the following pages as my reference:

Using the function you just created

In your workbook, add another worksheet called inventory and enter the following formulae in the specified cells:

A1  
=FILTER('Form responses 1'!E:E , LEN(TRIM('Form responses 1'!E:E))>0)

This formula copies non-empty cells under Column E from the Form Responses 1 sheet. 

B1  
=FILTER('Form responses 1'!F:F , LEN(TRIM('Form responses 1'!F:F))>0)

This formula copies non-empty cells under Column F from the Form Responses 1 sheet. 

And add a column titled inventory

Image 6

Image 6

Now, enter the following formula in C2:

=inventory(A2)

This is the custom formula you just created to convert text value to number value, for example, "1/2 page" in A2 to "3" in C2. Copy the same formula for the rest of the cells in column C. 

Calculating the current sales

Add another worksheet called sales

Enter the following formulae:

A3   
=74-(sum(Inventory!C2:C100))

This formula sums up cells in C2 to C100 in the Inventory sheet and then subtract the sum from the maximum ad spaces that can be sold (74). 5.5 spaces left means that I can sell more ads in any of the following combinations:

  • one 2/3 page (weight of 4) and one 1/4 page (weight of 1.5),
  • two 1/3 pages (weight of 2 each) and one 1/4 page (weight of 1.5),
  • one 1/2 page (weight of 3), one 1/4 page (weight of 1.5) and one 1/6 page (weight of 1),
  • one 1/4 page (weight of 1/5) and four 1/6 pages (weight of 1) and so on. 

C3
=1-(A3/74)

How many ad spaces have been sold so far expressed as a percentage.

D3, D4, D5
=query(Inventory!A2:A100,"Select A where A='Outside cover'")
=query(Inventory!A3:A100,"Select A where A='Inside Front'")
=query(Inventory!A4:A100,"Select A where A='Inside Back'")

Instead of assigning weight to unique pages, I am simply counting them. If any of these pages are sold, the page name will show up. #N/A means that the page is unsold. 

Image 7

Image 7

Info for preparing invoices

Finally, add another worksheet called artwork/invoice.

Enter the following formulae:

A1
=FILTER('Form responses 1'!D:D , LEN(TRIM('Form responses 1'!I:I))>0) 

B1
=FILTER('Form responses 1'!I:I , LEN(TRIM('Form responses 1'!I:I))>0)

C1
=FILTER('Form responses 1'!J:J , LEN(TRIM('Form responses 1'!J:J))>0)

And add two additional columns: Ready to invoice? (Y/N) and Invoiced? (Y/N).

I use this sheet to track who has been invoiced already and who hasn't. 

Image 8

Image 8



You can also use record in Form responses 1 to create a pivot table to see who is your best client, add a column to calculate commission earned and so on. 

While it is not the most elegant solution, it will do until we are ready to invest in a custom software coded by a real professional. Let me know if you have any question regarding specific formulae.