Usage History download truncated: a Workaround

[John-Q-Public]

Well, this is disappointing… Two days ago, I wrote the following post (which I have modified here in the hopes of avoiding deletion). After receiving several bravos and positive responses, it was flagged as S.P.A.M.! No reason given. I messaged the Moderator, but did not receive a response. I also wrote another post referring to this… This morning, both were deleted! Of course, so were the helpful responses, and my Bravos.

As I put a lot of thought and time into this post in an effort to help my fellow PM’ers, I will attempt to offer it again, this time rewording a bit, and removing some parts which may have annoyed the sp’m bots.

OK, so here is a workaround to enable you to download, and properly order, up to 90 days of Usage History, while waiting for PM to resolve the issue of downloads containing only a small amount of history, regardless of the time-span selected.

First, a clarification: Some people say it only downloads 6 days at a time, some 3, etc... In fact, after some experimentation, I have found that downloads always contain exactly 60 records (i.e. 'activities', such as calls, texts, data) at a time, no more, no less. So, if you have lots of 'activities' in your days, you will have less days per download.

It seemed, at first, that the days downloaded were random, but after I discovered the ‘60 activities’ aspect, I also realised that every download would start at the most recent (To) date requested, and go back sixty activities. The technique, therefore, is to download 60 activities, note the earliest date downloaded, and repeat the process from there, using that date as the “To” date (your last download will probably have less than 60). Note: a download may stop anywhere within a day, which is why you need to include that day in the next download; duplicates will be removed later. 

The following workaround will produce a series of Excel (.xls) spreadsheets, which can later be combined, the duplicates removed, then sorted and used properly. (PM: it would be better if they were csv, as they would be more compatible with other apps, and manipulatable as text. If it were csv/text, I could simply concantenate all of the files with one command, instead of the tedious process in Step 14.)

Note, it is not possible to go back more than 90 days in history. More than that will require a ticket with PM.

Steps to download Public Mobile Usage Data

1. Go to the Usage History page
2. Click "Filters"
3. Under "Date Range", select "Custom" (the last one, after 90 days)
4. Start with the most recent date you want to download (usually 'today'); that's the "To" date (the one on the right).
5. For the "From" date, on the left, select the earliest date you want to download. You can only go back 90 days, but you may want less than that.
   NOTE: As mentioned previously, you will only be downloading 60 records (i.e. 'activities', such as calls, texts, data) at a time; if you have lots of 'activities' in your days, you will have less days per download. I have relatively little activity, yet to download 90 days (SEP 8 to JUN 10 in my case) took 14 downloads! What a pain…  
6. Click "Apply Filter". Once the page refreshes (PM servers are ssslllooowwww... wait for it!), click "Download", in the upper-right.
7. Give your file a name. By default, the file name will be today; what I do is add consecutive numbers, i.e. append a "1" to the end of the first file, a "2" to the next, etc.
8. Open the file.
9. Note the date of the LAST record, on line 61 (there are 60 records, plus the header). Pay no attention to the time, as the download may (and probably does) cut off anywhere within that day.
10. Now, for the next file: Repeat the previous steps, but in step #4, enter as your "To" date the last date in the previous file, i.e. as noted in step #9.
11. Lather
12. Rinse
13. Repeat.
    Depending on your usage, you may need to do this many, many times (as noted in step 5, my relatively infrequent activity took 14 downloads).
14. Now, in Excel (this may work in other spreadsheet apps; YMMV): Start a new spreadsheet and, one by one, copy/paste the contents of every spreadsheet you downloaded (it’s best if you leave them open as you download them; I should have mentioned that earlier), then closing it (the downloaded sheet) when you’re done.
15. You will end up with a sheet with all of the records from the downloaded sheets, including multiple headers.
16. “Freeze” the first line (which should be headers), and “Filter” the whole thing, making the header at the top.
17. Go to Data, and Remove Duplicates.
18. Sort, and remove all of the other header lines. (after Step 17, there’ll probably be only one left)  
19. Sort again, and you should be good to go!

One last little problem to address: The “Date/Time” column will be textual, e.g. “Sep 08, 2023 09:17”, so sorting on this line will do so alphabetically, i.e. APR, AUG, JAN, etc., regardless of day, year, or time.

If you edit (F2) each one, then just press Enter, it should convert to a date, but that’s a pain with hundreds or thousands, of entries, so instead I added a column to the right (column F, in the current download format) with the following formula:

=DATEVALUE(LEFT(A2,12))+TIME(MID(A2,14,2),RIGHT(A2,2),0)

This assumes, of course, that you are on the first record, whose date is in cell A2. Copy this formula down, also adding it to subsequent downloads.

Now, you can sort on this column, and everything should sort properly, chronologically.

One annoying issue PM did resolve near the end of May, I think, (thank you, PM!) was that in earlier downloads, it just repeated the date twice, instead of giving the time (I know how this can happen, programmatically, but won’t go into that here). Now, they do show the time. 

That’s it… If you’ve read this far, Bravo to you! 🙂 If this was helpful to you, a Bravo from you would be appreciated!

[John-Q-Public]

Thanks for the feedback, @hTideGnow, it's appreciated!

Yes, the Excel formula could have looked strange to the machine. How ironic.

Yeah, I stuck with PM to get this [re-re-]published, because a) I hoped it could help, and I'd put a lot of time into it, and b) as a matter of principle... I can be pretty stubborn that way 😉

[hTideGnow]

HI @John-Q-Public 

oh yeah, the date was from Sept. I was wondering why no one comment too

I think the Excel formula fooled the system.  But usually once it is here for 5 or 10 mins, it won't be a spam anymore

thanks for the sharing,  you put in extra effort to have this shared

[John-Q-Public]

Side-note: This post, too, was flagged as spam; no reason given. Again, I wrote to the moderator(s), and again, no reply. Again, I opened a ticket with PM which was at first greeted with the formulaic "we're so sorry, we're happy to help, we'll get this fixed soon", then relative silence. I followed up regularly, and occasionally received another formulaic answer like the one above, but nothing more.

FINALLY, 35 days later, "Andu" (Thank you, Andu!) replied, first saying sorry it had been deleted, and then an hour later saying they'd found it and it has been marked as not spam. Phew; let's hope that this time it holds!

[hTideGnow]

HI @John-Q-Public 

it's helpful and worth a bravo.  Great work, Great Tips  🙂