This article is a step-by-step guide for calculating the number of labs CREATED within a given calendar month, which is the metric we are currently using in our AVL contractual agreements. 

Other resources:

• For more information on what all of the metrics mean when looking at AVL reports, check out this other internal article.
• Confluence page all about overages and calculating these metrics
• You can check out a video of Cody, Harry, Tanya, and Matthew working their way through this together here.

Get the data

Navigate to  http://[customer-avl-site]/reporting/raw_history/csv

This will automatically generate a spreadsheet of every event that has ever happened on a customer’s cluster.

NOTE: Do not trust the data in any of the Reporting sections of customers’ AVL sites. As of this writing (Dec 11, 2019), those numbers are inaccurate.)

Filter the data

The goal of this step is to get rid of any Appsembler data that exists in the spreadsheet. We’ll do this by looking at the User Email column. The users we want to get rid of will fall into one of three categories:

• Appsembler email addresses – These are Appsembler users acting through the LMS
• Appsembler admins
• PriorToLogging – These entries don’t include usernames/emails so we can’t know for sure who they were.

These steps were written using Excel 2011, so your mileage may vary depending on what version of the program you’re using.

1. Open the csv in Excel (NOTE: If you’re like me – Cody – and prefer working in Numbers, you can try, but I found trying to do some of the following annoyingly difficult, so maybe stick with Microsoft for this one.)
2. Select the User Email column
3. Find the filter function in Excel, it usually looks like a funnel 
4. On the User Email  column, you should get a little drop down icon. Click on that to bring up the filtering options menu. 
5. Filter on Contains and then type @appsembler.com. This will filter the spreadsheet so now it only shows entries for Appsembler folks who spun up containers as students from the LMS. 
6. Select all the rows you can now see in Excel. (NOTE: You have now selected not only the visible rows, but ALL of the rows in between that are currently hidden. Do not skip the next step! It will show you how to only select the visible rows.)
7. Find Go to special on your version of Excel. For me, I go to Edit > Go To… then click Special. You should end up with a window that looks like this. 
8. Click the button for Visible cells only, as I’ve done above, then click OK. You will now only have the visible rows selected.
9. Delete the rows. You can do this by right clicking on the row numbers or going to Edit > Delete Row.
10. Yay! You now have deleted all appsembler.com email addresses. Let’s repeat the process for our other groups of users we want to get rid of.
11. Unfilter your spreadsheet by clicking the filter icon at the top of the User Email column, then selecting Clear Filter
12. Reset your filter to get rid of all email addresses. You can do this by setting the filter to Does Not Contain and just typing “@” into the field. You will now only see usernames, which in AVL is only the admin users.
13. Sort the spreadsheet on the User Email column in order to group all the like usernames together.
14. Select all the rows that have either PriorToLogging as the username or an Appsembler user, such as cody, briandant, harry, etc. You can either do all of these at once or one group at a time. 
15. Whatever you choose, make sure you REPEAT STEPS 7 & 8 in order to make sure you’ve only selected the visible rows.
16. Delete the rows. 
17. You should now only see customer admin users.
18. Clear your filters. 

Congrats! Your spreadsheet now only contains entries from customer learners and admins. This is exactly what we want. You can now delete the User Email column. We don’t need it and it contains PII, so the less we have to see it, the better.

Sort the data

1. Select the entire spreadsheet. You can do this by clicking the square in the very top left – above all the rows, and to the left of all the columns. 
2. Find Custom sort. 
3. Sort by Action then by Log ID (descending). Hit OK
4. Now that your sheet is sorted, we’ll get rid of every row that doesn’t include DEPLOYMENT_CREATED in the Action column. 
   1. I do this by using the Find function (CMD+F / CTRL+F) to search for “DEPLOYMENT_DELETED” After I’ve found this first row, I select it and all other rows below it, then delete them. 
   2. You can also use a similar filtering method that we used before. Filter on Does not include “DEPLOYMENT_CREATED” then select all visible rows (don’t forget to Go to Special…select only visible rows), and delete them.

Great job! Now we only have the DEPLOYMENT_CREATED events by customer users (learners & admins) sorted by Log ID, which is a chronologically assigned number.

Create a “Month” column

The default Created column is too much information than we need. Use the following steps to create a simpler column that only shows the month each event occured in. 

1. In the column to the right of the Created (MM/DD/YYYY H:M) column, type Month in the header row. 
2. In the first cell (this is probably K2, but could be different depending on your setup), paste in one of the following two formulae, making sure that the cell it’s referencing is the adjacent cell in the CREATED column. Option b works better in places that don’t understand U.S. dates.
   1. =TEXT(LEFT(M2,FIND(” “,M2)-1), “yyyy-mm”)
   2. =CONCATENATE(MID(J2,7,4),”-“,LEFT(J2,2)) 
3. The first cell should now be showing the date in the YYYY-MM 
4. Copy the cell and paste it into all the empty cells below it in the same column. 

Make the pivot table

This is the easiest way to see how many DEPLOYMENT_CREATED events there are within a given month. Again, the steps to create this may be very different depending on your OS, Excel version, etc. if something here doesn’t line up to your experience…Google is your friend. 

1. Start by selecting the appropriate data range, which is basically every part of the sheet with stuff in it
2. Create a new pivot table. For me, this is under Data > PivotTable…
3. I find it easiest to make sure the pivot table lands on a new sheet, but that’s really up to you
4. Use the PivotTable builder to make sure your rows are Month and values are Count of Action. For me, this is a drag-and-drop interface, so all I have to do is drag a field from “Field name” and drop it into the proper place below. It looks like this:
5. Your pivot table should now look like this: