What is the functor tensor product?

It is easy to be explicit. Not in full generality, given a (small) closed symmetric monoidal category $\mathcal C$ with coequalizers, a covariant functor $M\colon \mathcal C\to \mathcal C$ and a contravariant functor $N\colon \mathcal C \to \mathcal C$, the tensor product $N\otimes_{\mathcal C} M$ is the coequalizer of the diagram

$$\coprod_{(c,d)} N(d) \otimes \mathcal C(c,d) \otimes M(c) \implies \coprod_{e} N(e)\otimes M(e).$$

Here $c,d,e$ range over the objects of $\mathcal C$ and $\implies$ indicates a pair of arrows; one is given by the evaluation maps $N(d) \otimes \mathcal C(c,d)\longrightarrow N(c)$ of $N$ and the other by the evaluation maps $\mathcal C(c,d)\otimes M(c) \longrightarrow M(d)$ of $M$. The similarity to Mike's special case should be clear. This is of course an example of a coend, but I prefer to use the tensor product notation in this special case to make the intuition clear.

The enriched version of the functor tensor product does literally generalize the tensor product of modules. A ring is the same as an $\mathbf{Ab}$-enriched category with one object, a (covariant or contravariant) $\mathbf{Ab}$-enriched functor from a ring to $\mathbf{Ab}$ is a (left or right) module, and the $\mathbf{Ab}$-enriched tensor product of such functors is exactly the classical tensor product of a left and a right module.

An intuition that I find useful is the following.

Recall that a presheaf $F : \mathcal{C}^\mathrm{op} \to \mathrm{Set}$ can be seen as "gluing specification": If $G : \mathcal{C} \to \mathcal{D}$ is some functor into a cocomplete category $\mathcal{D}$, this gluing specification can be realized as $\operatorname{colim}_{s \in F(X)} G(X)$. This colimit can also be written as the coend $$ \int^{X \in \mathcal{C}} F(X) \cdot G(X), $$ where ${\cdot} : \mathrm{Set} \times \mathcal{D} \to \mathcal{D}$ denotes the copower, i.e. $M \cdot Y = \coprod_{m \in M} Y$. This is precisely the functor tensor product $F \otimes G$!

Summarizing, $F \otimes G$ can be pictured as the $G(X)$'s, glued as specified by $F$. A few examples are interesting.

• Let $Y : \mathcal{C} \to \mathrm{PSh}(\mathcal{C})$ be the Yoneda embedding. Then $F \otimes Y = F$. (This result is also called the ninja Yoneda lemma in some circles.) Intuitively, $\mathrm{PSh}(\mathcal{C})$ is the category of gluing specifications, so "realizing" $F$ in this category just gives $F$.
• Let $\mathcal{C}$ be specifically the simplex category $\Delta$. Then $F$ is just a simplicial set, so the intuition of $F$ as a gluing specification is even more vivid. Let $G : \Delta \to \mathrm{Top}$ be the functor which sends $n$ to the topological $n$-simplex $\Delta^n$. Then $F \otimes G$ interprets the gluing specification $F$ in $\mathrm{Top}$; the result is the geometrical realization $|F|$.