How to Export License Usage and Calculate License Consumption in Excel

Author: Katie Huckett, Product Manager

Date: September 2022

Audience: Everyone

In a previous article, Monitoring Float License Usage, we have detailed several ways to analyze license metrics between the Admin section of Connect and the exported raw data. This article will expand on a more advanced analysis of the raw license data exported to Excel to evaluate license consumption.

Exporting License Usage from Connect

The Admin license page stores data for up to 90 days. Therefore, it is recommended that license usage data be exported every 90 days for all license types to maintain a historical record locally for year-over-year analysis and identify longer-term trends.

You will start by performing these steps on the Connect Admin License page:

• Log in to Connect > Admin > License
• Set the license type dropdown to 'All.'
• Set the timeframe dropdown to 'last 90 days.'
• Select the 'Export' button and save the Excel file locally
• Open the Excel file and perform the following steps necessary for performing a license consumption analysis:
  • NOTE: If you have completed the analysis from the Monitor Float License Usage article mentioned above, you may add this analysis to your existing spreadsheet instead of exporting a second copy.
  • Label the original worksheet from the export 'RawData' to identify the original data from Connect.
  • Right-click on the worksheet > Select Move or Copy... > Select the RawData sheet and check the Create a copy box > Select OK.
  • Right-click the new worksheet and rename it 'CreatorFloat.'
    • Repeat the above steps to copy the RawData worksheet for all unique license types you would like to analyze - in this example, a worksheet has been created for both Creator and CreatorFloat license types.

Perform data sanitization and cell formatting.

• Starting on the CreatorFloat worksheet, perform the following data sanitization steps:
  • Remove the rows for any data that is not for a Creator (float) license
    • With your cursor somewhere in the worksheet, select the Sort & Filter dropdown > select Custom Sort...
    • In Sort by select LicenseAssigned > leave Sort On and Order as the default settings > select OK
    • Highlight and Delete any rows that contain data for a license type other than 'Creator (float)'
  • Insert a new column to calculate the Duration in Milliseconds into an HH:MM:SS format
    • Place your cursor in the second row of the new column and enter the following formula: =DurationInMilliseconds/86400000
    • Copy the formula down the entire column
    • Highlight the column and set the cell format to Time as hh:mm:ss

• Prepare the LoggedInAt column for the proper date and time formatting and analysis
  • Highlight the entire LoggedInAt column > select Find & Select > Replace... and complete the following two Find/Replace steps: 
    • Find what = " AM UTC" (be sure to include the space before AM) and Replace All with = " AM" with a space preceding
    • Find what = " PM UTC" (be sure to include the space before PM) and Replace All with = " PM" with a space preceding
    • Highlight the entire column > select the dropdown to format the cell and select More Number Formats... > select the Category 'Custom' and choose the m/d/yyyy hh:mm:ss option > select OK.
    • (Optional) You may wish to rename the column 'LoggedInAtUTC' as a reminder that the time is UTC and not your local time zone
      • If you wish to convert to your local time zone, please see the instructions in the Monitor Float License Usage article.

• Calculate the Logged Out time by adding the LoggedInAtUTC time to the Duration.
  • Create a new column next to the LoggedInAtUTC column and label it 'LoggedOutAtUTC'
  • Enter the formula: =LoggedInAtUTC+DurationTime - in the example shown this is =G2+E2
  • Copy the formula down the remainder of the column.
  • IF the LoggedOutAtUTC column does not follow the same formatting as the LoggedInAtUTC column, then apply the same Custom formatting as before (m/d/yyyy hh:mm:ss)

Highlight the LoggedOutAtUTC column > Copy the data > Paste Values only to replace the formula with the values alone (this is so that we can delete unnecessary columns, such as DurationTime, from the worksheet in the next step)

• Remove the following columns which are no longer needed to clean up the worksheet:
  • LastActivityAt
  •  LicenseAssigned
  •  DurationInMilliseconds
  •  DurationTime
  •  Downgraded
  • You should only have three columns remaining: UserName, LoggedInAtUTC, and LoggedOutAtUTC
• Convert the LoggedInAtUTC and the LoggedOutAtUTC columns into text so that Excel may do the correct COUNTIF function calculations
  • In the next empty column, create a label 'TEXT_LoggedInAtUTC' -- in this example, this is column D
  • In the next empty column, create a label 'TEXT_LoggedOutAtUTC' -- in this example, this is column E
  • With your cursor in the first empty cell under TEXT_LoggedInAtUTC enter the following formula
    • =TEXT('TEXT_LoggedInAtUTC',"m/d/yyyy hh:mm:ss") -- in this example, the formula is =TEXT(B2,"m/d/yyyy hh:mm:ss")
  • Copy the formula down the remainder of the column
  • Repeat the formula for the TEXT_LoggedOutAtUTC column -- in this example, the formula is =TEXT(C2,"m/d/yyyy hh:mm:ss")
  • Copy the formula down the remainder of the column.

Calculate the Licenses Consumed Using the Login/LogOut Data

• Create a new column to the right of your current data (you may choose to leave one blank column in between) and label it 'DateTime.'
  • Starting in the second cell of the TEXT_LoggedInAtUTC column (following the header), highlight the entire column down (Ctrl+Shift+Down Arrow) > Copy the data > Paste Values only under the DateTime column header -- this is so you copy the values over and not the formula.
  • Repeat the copy instructions for all the data in the TEXT_LoggedOutAtUTC column (excluding the header) > Paste Values only in the DateTime column UNDER the LoggedIn data you just pasted.
  • Highlight the entire DateTime column > select the Data tab > Remove Duplicates. 
  • In the Remove Duplicates modal > ensure the only column selected is DateTime > ensure the 'My data has headers' box is checked  > Select OK.
  • Highlight the DateTime column and select Sort (still under the Data tab)
  • In the Sort modal, Sort by 'DateTime'; Sort On 'Cell Values'; and Order 'A to Z' > ensure 'My data has headers' is checked > select OK (only the DateTime column should have sorted and not the rest of your worksheet data)
  • Your worksheet should resemble the example pictured below at this point

• Create the following columns to the left of the DateTime column:
  • LogInCount  -- was there a login at this moment
  • LogOutCount -- was there a log out at this moment
  • RSumLogIn -- the running sum of the logins
  • RSumLogOut -- running sum of the logouts
  • LicenseConsumption -- count of licenses consumed
• Highlight all five columns you have just created, set the format to 'Number,' and remove any decimals
• Under LogInCount, enter the following formula:
  • =COUNTIF(the range of data in the TEXT_LoggedInAtUTC,the value in the DateTime field)
  • In this example, the formula is: =COUNTIF($D$2:$D$72089,G2)
  • Copy the formula down the column (this may take a few seconds if you have many rows of data)

• Under LogOutCount, enter the following formula:
  • =COUNTIF(the range of data in the TEXT_LoggedOutAtUTC,the value in the DateTime field)
  • In this example, the formula is: =COUNTIF($E$2:$E$72089,G2)
  • Copy the formula down the column (this may take a few seconds if you have many rows of data)

• Under RSumLogIn, enter the following formula:
  • =SUM(the LogInCount range)
  • In this example, the formula in cell J2 is: =SUM($H$2:$H2)

• Copy the formula down the column (this may take a few seconds if you have many rows of data)
  • As you move down the column, the formula should change the second value to match the current row -- in this example, if I place my cursor in cell J22, the formula should read =SUM($H$2:$H22

• Under RSumLogOut, enter the following formula:
  • =SUM(the LogOutCount range)
  • In this example, the formula in cell K2 is: =SUM($I$2:$I2)
  • Copy the formula down the column (this may take a few seconds if you have many rows of data)
    • As you move down the column, the formula should change the second value to match the current row -- in this example, if I place my cursor in cell K22, the formula should read =SUM($I$2:$I22

• Lastly, under LicenseConsumption, enter the following formula to subtract the RSumLogOut from the RSumLogIn:
  • =RSumLogIn-RSumLogOut
  • In this example, the formula in cell L2 is: =J2-K2
  • Copy the formula down the column (this may take a few seconds if you have many rows of data)

Using the License Consumption Data to Create Charts and Analyze the Information

In the previous section, we used the user Log In and Log Out times to determine how many licenses were consumed during those timestamps. We can now use the LicenseConsumption column to create Charts further to determine peak usage per day and hour. Please Note the peak usage per Minute is available on the License Admin page in Connect. See the Monitoring Float License Usage article for more information.

• With the worksheet open from the previous exercise, highlight all of the following columns:
  • DateTime
  • LicenseConsumption
• Copy the data > create a new Worksheet > paste the Values only into the new Worksheet.
• Rename the new Worksheet "DailyMAXFloat"
• Right-click on the DailyMAXFloat worksheet tab > select Move or Copy... > ensure the DailyMAXFloat sheet is selected and check the Create a copy checkbox > select OK
• Rename the new Worksheet "HourlyMAXFloat"
• Return to the DailyMAXFloat worksheet 
• Because the DateTime column was converted to TEXT previously, you will need to take the following steps to show only the Date without the timestamp:
  • Insert a column before the DateTime column and label it 'DateOnly.'
  • In the first cell after the label, enter the following formula =TEXT(DateTime, "m/dd/yyyy") --- In this example, the formula is =TEXT(B2, "m/dd/yyyy")

• • Copy the formula down the DateOnly column.
  • Highlight the entire DateOnly column > Copy the data > Paste the values only (this will remove the formula so that we can remove the DateTime column for a cleaner worksheet)
  • Delete the DateTime column (you should now only have the DateOnly column and the LicenseConsumption column remaining)
• Place your cursor in the DateOnly cell (it should be cell A1)
• Select the Data tab > select Subtotal under the Outline grouping
• In the Subtotal modal, enter the following:
  • At each change in DateOnly
  • Use function: Max
  • Add subtotal to check LicenseConsumption
  • Check to Replace current subtotals
  • Check the Summary below data
  • Select OK
  • Please note the Max subtotal function may take a few moments to run.

• After the Subtotals finish > select 2 in the Outline section to the left of the worksheet.
  • You will see a MAX total for each day in your data -- this is the maximum number of licenses consumed during that particular day.

• To better see the peak usage, create a chart from the MAX values as follows:
  • With Outline 2 selected, showing the MAX values, place your cursor in the DateOnly label cell above the data
  • Select the Insert tab > select the Scatter with Straight Lines and Markers chart

• The Scatter chart will appear next to your data.
• Hover over the markers at the peaks > review the tooltip to determine the associated Date and Licenses Consumed value.
  • In this example, 5/11/22 is showing 52 licenses consumed as my peak date
  • Please note the last marker on the chart is the Grand Max number - you may ignore this

• Select the HourlyMAXFloat worksheet, and we will repeat most of these steps with a slight alteration to the DateTime formatting:
  • Insert a sheet column before the DateTime column and label it 'DateHourOnly.'
  • In the first cell after the label, enter the following formula =TEXT(DateTime, "m/dd/yyyy hh") --- In this example, the formula is =TEXT(B2, "m/dd/yyyy hh")

• • Copy the formula down the DateHourOnly column.
  • Highlight the entire DateHourOnly column > Copy the data > Paste the values only (this will remove the formula so that we can remove the DateTime column for a cleaner worksheet)
  • Delete the DateTime column (you should now only have the DateHourOnly column and the LicenseConsumption column remaining)
• Place your cursor in the DateHourOnly cell (should be cell A1)
• Select the Data tab > select Subtotal under the Outline grouping
• In the Subtotal modal, enter the following:
  • At each change in DateHourOnly
  • Use function: Max
  • Add subtotal to check LicenseConsumption
  • Check to Replace current subtotals
  • Check the Summary below data
  • Select OK
  • Please note the Max subtotal function may take a few moments to run
• After the Subtotals finish > select 2 in the Outline section to the left of the worksheet
  • You will see a MAX total for each day's hour in your data -- this is the maximum number of licenses consumed during that particular hour in that particular day

• To better see the peak usage, create a chart from the MAX values as follows:
  • With Outline 2 selected, showing the MAX values, place your cursor in the DateHourOnly label cell above the data
  • Select the Insert tab > select the Scatter with Straight Lines and Markers chart
• The Scatter chart will appear next to your data
• Hover over the markers at the peaks > review the tooltip to determine the associated Date, Hour, and Licenses Consumed value.
  • In this example, 5/11/22 in the 1700 hour shows the highest peak of 52 licenses consumed.
  • Please note the last marker on the chart is the Grand Max number - you may ignore this.
  • In this example, as I hover over the peak markers across the chart, the 1600 and 1700 hours tend to be when the most licenses are consumed across the 90 days I am reviewing.

Optional: You may repeat the process above for the Creator-named licenses or any type you wish. 

Additional Note: The horizontal axis in the above charts shows the row count for the series of Max totals. After much troubleshooting, I could not show the DateOnly or the DateHourOnly labels on the horizontal axis instead of the row count. 
If anyone is a super Excel wizard, please comment and share how this may be possible!

Katie Huckett, Senior Product Manager

Questions & Answers:

We received these from users for the "DateTime" column instructions: 

So, after these two instructions, does that mean the DateTime column is twice as long as the initial data? (Since we are pasting all the logged-in and logged-out times into one column, is that correct?)
-- Yes, the column would now be twice as long as the original row count

Then, the next instruction is to remove duplicates from this DateTime column.  This is where I'm not quite following the rationale. What is expected from the result of removing all duplicates? 
-- The expected result is that there is only one unique value for any given login or logout date/time. There is a chance the original data does have multiple people logging in or out at the same date/time, but to obtain a correct calculation, we only want one unique entry for any date/time value.


Will the result be that this column has an equal amount of data (i.e., rows) to the original data?  
-- The result would not have an equal amount of data (i.e., rows) as the original data. This would only occur if you had an exact number of logins/logouts happening simultaneously, which is not very likely.

I do not understand the intent/meaning of this new DateTime column when it is composed of the unique set of logged-in and logged-out times, especially when removing the duplicate values from this column, which will not necessarily result in parity with the original data.  
-- The intent is only to have 1 data entry per date/time. If you had three entries for, say, 4/14/2023 at 10:11 AM, then the corresponding LogInCount or LogOutCount (whichever it falls under) would receive extra counts, which would inflate your Running Sum numbers, making it appear more licenses were consumed than in actuality.
-- It might be rare, but if your users never did overlap in login or logout date/times, there may be none when you go to remove duplicates. But the step is there to ensure there aren't any duplicate values that could inflate your counts.