I think the main use for the AutoOpen attribute is when you want to make some let-bound values available when the user of your library opens a namespace. This is where the attribute is very useful, because I think libraries should generally export all definitions in namespaces, but for some purposes you need to export values and values cannot be defined inside a namespace.
Here is an example from F# async extensions which defines a computation builder and thus it needs to export asyncSeq value (but at the same time, all functionality is wrapped in a namespace):
namespace FSharp.Async
type AsyncSeq<'T> = (* ... *)
type AsyncSeqBuilder() = (* ... *)
[<AutoOpen>]
module GlobalValues =
let asyncSeq = AsyncSeqBuilder()
The user of the library can just write open FSharp.Async and they will see asyncSeq. I think the same pattern is used with various math libraries (where you also want to export simple-named functions.)
For modules (e.g. List and Seq), I think most of the people do not use open and access the functions via a module name (such as List.map), so although you can use this for nested modules, I have not seen that as frequently.